before job subroutine

Arthur · Post by **Arthur** » Tue Dec 18, 2007 1:04 pm

Hi!

How to create a before job subroutine that will handle bunch of DML statements and stored porcdures.

Thanks in advance!

ray.wurlod · Post by **ray.wurlod** » Tue Dec 18, 2007 3:00 pm

In the Routines branch of your Repository create a new routine and ensure that its type is Before/After Subroutine.

Within the subroutine, code in DataStage BASIC and use the BASIC SQL Client Interface, or BCI, functions to mimic operation of the ODBC API.

Search the forum for examples.

Arthur · Post by **Arthur** » Tue Dec 18, 2007 4:07 pm

Hi!ray.wurlod

I have created a user defined before job subroutine. I want to know how to embed stored procedures and SQL DML statements in a single before job sub routines.

ray.wurlod · Post by **ray.wurlod** » Tue Dec 18, 2007 4:22 pm

You use SQLExecDirect() or SQLExecute() (for prepared SQL) functions.

There is a lot of structure you have to set up around this, to bind parameters and bind result columns, allocate memory for managing the environment and freeing the same, connecting and disconnection, and handling errors. The suite of functions is called the BCI.

Search

rleishman · Post by **rleishman** » Wed Dec 19, 2007 12:26 am

Another way to do it is to use the After Subroutine to execute a DataStage Server job that runs the SQLs for you.