Error Executing Stored Procedure

raj_konig · Post by **raj_konig** » Tue Apr 11, 2006 3:33 am

Hi folks,

My Database is IBM DB2. I created a Dynamic stored procedure. I tried running this from the database only and is working fine. When i am trying to run the same procedure importing into the Datastage its throwing the following error. TESTING is the name of the procedure.

"TES..Stored_Procedure_14: [IBM][CLI Driver][DB2/NT] SQL0444N Routine "*TING" (specific name "SQL060411115550670") is implemented with code in library or path "...TING", function "TESTING" which cannot be accessed. Reason code: "4". SQLSTATE=42724 "

I dropped the procedure and recreated with a different name and tried but it is not working.

Please help me out.

Thanks in advance
raj

richdhan · Post by **richdhan** » Tue Apr 11, 2006 4:08 am

Hi Raj,

Did a web search and this is what I found.

http://www-128.ibm.com/developerworks/f ... 298&cat=19

HTH
--Rich

DSguru2B · Post by **DSguru2B** » Tue Apr 11, 2006 8:15 am

does your stored procedure have any IN parameters. What about OUT parameters?
How exactly are you calling your St. Proc. and from where?
Or this error is only happening during Import?
If thats the case, just import it and try calling it in the after sql tab of the UDB stage.

raj_konig · Post by **raj_konig** » Tue Apr 11, 2006 9:39 am

Hello DS Guru,

I do have IN Parameters but no OUT params. Its throwing the error while importing the procedure itself.

I feel I need to change my Databse Configurations. The following is what i found on the web. But need to give that a try. Or you guys hav any other suggestions please let me know.

"This error generally indicates a problem with the stored procedures. Check the createsp.log located in the <wc_install_dir>\instances\<instance_name>\logs folder. If you see errors, then the stored procedures where not created successfully during instance creation. To manually create the store procedures, ensure that the Commerce and Payment application servers are stopped, and DB2 is started. Open a DB2 command window and change to the <wc_install_dir>\bin directory. Then type the following:
createsp.db2.bat <db_name> <user_name> <user_password> <schema_owner>"

Thanks,
rajesh

DSguru2B · Post by **DSguru2B** » Tue Apr 11, 2006 10:09 am

i had a problem similar to yours, but that was during the call of the proc, not during the import. Regardless of the msg you got during the import, you should be able to import it. Then try calling it in an odbc stage as a target with the input param or if you have 7.5.1A, then call it using the STP stage.

raj_konig · Post by **raj_konig** » Wed Apr 12, 2006 1:40 am

Hi DS Guru,

I also tried using ODBC Stage. In this case i am able to import the procedure but during the runtime i am getting a warning. This warning contains the same previous error message.

I dont find any STP stage. U mean this by stored procedure stage or what?

Previosuly I used Stored Procedure stage to run the procedures. It was fine and running with out any discrepancies. I am getting this error recently. Even i drop the procedure and recreate this with a different name the error was invincible.

May I know wht have u done to overcome this error in your case.

Thanks,
raj