Have to call a DB2 stored procedure

Amarpreet · Post by **Amarpreet** » Thu Jun 08, 2006 11:07 pm

I have a scenario, where in I am using a loop to execute a job 20 times with some different variable each time. I want to execute/call a DB2 stored procedure after end of the loop. How can I achieve this in parallel jobs?

kumar_s · Post by **kumar_s** » Thu Jun 08, 2006 11:12 pm

Just execute it after the loop.
You can do this from the job sequence, by using ExecuteCommandActivity to call the SP from OS level. Or use a JobControl which has the db2 api stage (in the before/after sql tab) to call SP.

Amarpreet · Post by **Amarpreet** » Fri Jun 09, 2006 12:48 am

Thanks for the answer. But I have no idea that how to execute db2 stored procedure from command line. what has to be written in executecommandactivity to execute db2 stored procedure from command line.

kumar_s · Post by **kumar_s** » Fri Jun 09, 2006 4:59 am

Using commnad line interface.

Code: Select all

db2 "call StoredProcedure(100, ?)"

Where "?" can be used for IN type.

ArndW · Post by **ArndW** » Fri Jun 09, 2006 5:06 am

Don't forget that you also need to execute a "db2 connect ..." statement as well!

Amarpreet · Post by **Amarpreet** » Mon Jun 12, 2006 1:37 am

I am calling procedure using syntax---db2 "call test_proc()". But its giving following error:
TEST_PROC..JobControl (@Execute_Command_0): Executed: db2 "call svsods.test_proc()"
Reply=255
Output from command ====>
exec(): 0509-036 Cannot load program db2 because of the following errors:
0509-022 Cannot load module /db2home/db2inst3/sqllib/lib/libdb2.a(shr_64.o).
0509-150 Dependent module /usr/opt/db2_08_01/lib/libdb2osse.a(shr_64.o) could not be loaded.
0509-152 Member shr_64.o is not found in archive
0509-022 Cannot load module db2.
0509-150 Dependent module /db2home/db2inst3/sqllib/lib/libdb2.a(shr_64.o) could not be loaded.
0509-022 Cannot load module .

ArndW · Post by **ArndW** » Mon Jun 12, 2006 2:08 am

The error message is telling you that the object

db2home/db2inst3/sqllib/lib/libdb2.a(shr_64.o).

is either not there, has too little permissions for your user or for some other reason isn't accessible. Have you checked your UNIX permissions?

ray.wurlod · Post by **ray.wurlod** » Mon Jun 12, 2006 6:09 am

You need to be using the 32-bit libraries. DataStage is a 32-bit application. Make sure that, at least for your DataStage processes, the 32-bit libraries precede the 64-bit libraries in your shared library search path.

Amarpreet · Post by **Amarpreet** » Tue Jun 13, 2006 3:48 am

Could you please explain this in detail, as how to change the shared library from 64 bit to 32 bit?

kumar_s · Post by **kumar_s** » Tue Jun 13, 2006 9:42 am

You can check with this example given by Craig on Oracle Shared library.