Page 1 of 1
before job subroutine
Posted: Tue Dec 18, 2007 1:04 pm
by Arthur
Hi!
How to create a before job subroutine that will handle bunch of DML statements and stored porcdures.
Thanks in advance!
Posted: Tue Dec 18, 2007 3:00 pm
by ray.wurlod
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.
Posted: Tue Dec 18, 2007 4:07 pm
by Arthur
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.
Posted: Tue Dec 18, 2007 4:22 pm
by ray.wurlod
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
Posted: Wed Dec 19, 2007 12:26 am
by rleishman
Another way to do it is to use the After Subroutine to execute a DataStage Server job that runs the SQLs for you.