Executing a DB2 stored procedure in Unix Environment.

[umalaxmig]

Hi I am Uma. I am new to DB2 and Unix(AIX) Environment. Can any one please help me to how to execute a DB2 stored procedure in Unix Environment. Thanks a lot.

[sakumar9]

You can follow these steps:
1. If your stored procedure is in any external language like C, Java, cobol etc, then compile it and paste the object file in sqllib/functions directory.

Recommended method: Go to sqllib/samples directory. Then go to the directory corresponding to the language in which you have written stored procedure. And give following command:
bldrtn <filename> --- Filename should be without extensions.
This will compile and put the object file at desired location.

2. If your stored procedure is in CLP, then you just need to execute the command, and SP is ready for use.

3. Execute CREATE PROCEDURE command to catalog the SP in DB2.
4. Done , now you can use the SP.


Let me know if you have any problems.

Regards
-- Sanjay

[umalaxmig]

Hi Sanjay,

Thank you so much for the reply. the SP is sql and it uses cursor inside it.It also contains CREATE PROCEDURE command even. I am trying to execute it by pasting it in a file and giving the command db2 -tvsf <filename>. But it gives me lot of errors.

Eg:- Please look into the below error.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
select current timestamp from sysibm.sysdummy1

1
--------------------------
2008-06-05-08.36.06.720460

1 record(s) selected.


CREATE PROCEDURE pharm.PJSP094hkp_presitm ( OUT SQLCODE_OUT int,
OUT SQLTEXT varchar(1000), OUT rows_processed int)
RESULT SETS 1
LANGUAGE SQL


PJSP094P1: BEGIN
DECLARE SQLCODE INT DEFAULT 0
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "QLCODE
INT DEFAULT 0". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=8. SQLSTATE=42601

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~

And in the error it is giving like expecting semicolon which is already present at that line ending. I am unable to figure out why it is giving. And if we are executing normal DB2 quries we are able to run with out any errors. The entire code is correct and unable to figure out why we are getting this error.

Can you please help us in this regard.
Thanks a lot Sanjay.

Thanks & Regards,
Uma G

You can follow these steps:
1. If your stored procedure is in any external language like C, Java, cobol etc, then compile it and paste the object file in sqllib/functions directory.

Recommended method: Go to sqllib/samples directory. Then go to the directory corresponding to the language in which you have written stored procedure. And give following command:
bldrtn <filename> --- Filename should be without extensions.
This will compile and put the object file at desired location.

2. If your stored procedure is in CLP, then you just need to execute the command, and SP is ready for use.

3. Execute CREATE PROCEDURE command to catalog the SP in DB2.
4. Done , now you can use the SP.


Let me know if you have any problems.

Regards
-- Sanjay