suggest best approach for doing this job..........

[ketanshah123]

1. Connect to database
1.1. If connection fails, write error details to application log, and send e-mail to Operations
1.2. If connection succeeds, invoke DB2 stored procedure:

1.2.1. If the call to DB2 stored procedure fails, write error details to application log, and send e-mail to Operations
2. End job

plz suggest how this job can be done........?
wht stage should be used for db2 procedure call(odbc/stored procedure)

[ray.wurlod]

Create a job sequence. This runs a small job to test the connection, then branches appropriately (your points 1.1 and 1.2).
After the job containing the stored procedure invocation has run, test its exit status and again use Routine activity and Notification activity.
Which stage to use really depends on what the stored procedure does. Read both chapters in the on-line help or Server Job Developer's Guide

[ketanshah123]

hi
thx for reply
i had done the same but confused about how to check the connection with database is established or fialed .How to create a job that will check the connection status?

[thurmy34]

ketanshah123,

I would do your actions in a job control.
Try to connect to your database with this example of code:
status = SQLAllocEnv(hEnv)

status = SQLAllocConnect(hEnv, hConn)

status = SQLConnect(hConn, DSN,USER,PWD)

IF status =SQL.ERROR THEN
call DSLogWarn("Database KO","TRACE")
END

If the database is ok you can launch the job of the procedure.

If this job failed (DSWaitForJob or DSGetJobInfo) use the DSGetLogSummary function to get the warnings, errors ... of the job.

To send an e-mail use DSSendMail.

Hope this help

[chulett]

Why overly complicate it when it is simple to effect from a Sequencer job as explained in other posts? Your approach requires something other than the branded ODBC drivers that ship with DS and the hand code would be difficult for the Average Joe to uderstand and maintain.

[thurmy34]

Chulett,

I don't think my solution is overly complicate i wrote it because in my projects sequencer are forbidden so i have to work with job control and Before/After Routine.

Your reply make me ask this :
Is $INCLUDE UNIVERSE.INCLUDE ODBC.H not ship with DS ?

Thanks

[chulett]

You don't think so because you have been doing it. Trust me, Average Job wants to work only in the GUI and not have to write or maintain hand coded job control.

I'm not in a position where I can check, but those components probably do ship with DataStage. Perhaps I'm incorrect, but if they use the 'branded' drivers, I assumed they fell under the restrictions they have for use outside of the GUI. They work for 30 days and then start failing with a license violation unless you 'cheat' by going under the covers and know how to circumvent that problem.

We see some... interesting... client restrictions here all the time that people are forced to work under. Sequencer jobs are forbidden? Ok.

[ray.wurlod]

Challenge the restriction!!! It's stupid. Demand reasons.

Then post them here - we could do with a laugh.

Will they allow job control routines? A job control routine would be a more appropriate mechanism than a before/after subroutine.

To use the BCI functions your client will need to purchase a licence for the DB2 ODBC driver from Data Direct. The cost of this may well make them reconsider.

[thurmy34]

Ray,
I can't read the end of you message.
The reason of the sequencer restriction is that we want to write log outside the director and produce restart point .
We do this by using routine and job control.

[ray.wurlod]

A job sequence is nothing more than a GUI for writing job control.

That's why the prohibition is stupid.