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).
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.