Problem with a stored procedure

Lkmi · New User Joined: 04 Mar 2005 Posts: 53 Location: Newyork

I need to write a simple stored procedure in db2 which has a simple select.

so all i did was to declare a cursor with return option and i open the cursor after populatinhg the where clause with the input parameters send to the stored proc.

the stored proc is defined in the sysibm.sysprocedures.

when i call the stored proc through the java program, i get a sql code of -430. iam not able to find why it aBENDS,

what are the system tables in which definitions for the stored proc should be present apart from sysibm.sysprocedures?????

anamikak · New User Joined: 10 May 2006 Posts: 64 Location: Singapore

Firstly,he stored procedure definition would be stored in SYSIBM.SYSROUTINES and not in SYSIBM.SYSROUTINES, not sure if such a table called SYSIBM.SYSROUTINES exists. The In and out parameter definitions are stored in SYSIBM.SYSPARMS table.

If you are writing an external stored procedure with embedded SQL statments you need to crteate a package and mention the COLLECTION ID in the definition. If you are creating SQL PROCEDURE, then need not create a package.

Have you handled for SQL code +100 in your procedure?

have you opened and closed the cursors?

have you done the iterations for fetching until sqlcode 100?

can you publish the definition of your Stored procedure.(The create procedure statement).

Anamika

Lkmi · New User Joined: 04 Mar 2005 Posts: 53 Location: Newyork

thanks for the response
i fouind what the problem was. the environment was not properly set up.
i fixed that. and one more mistake was i had the input fields as 10 level under a 01 level..which is wrong.