Importing Stored Procedure Definition from Manager

horserider · Post by **horserider** » Fri May 02, 2008 11:18 pm

I have a DB2 Stored Procedure, SYSPROC.STPRC009 that I want to run from Data Stage using the Stored Procedure Stage.

The Stored Procedure accepts 1 character imut like 'U' 'I' 'P' and return multiple row for the input character passed.

Input Parameter
COUNTRY_CODE CHAR(1)

Output of Stored Procedure is

REGION_CODE CHAR(1)
REGION_ID CHAR(4)
START_DATE DATE
PAID_AMOUNT SMALLINT

Here is what I do

(1) I login to Manager > Import table definition > Stored Procedure Def.
(2) Then I enter userid/password and my Stored Procedure Name.
(3) Another window comes up asking me to enter the ARGUMENT LIST
(4) When I enter 'U' in argument list, I get an error

DSR.MetaGeta(GET.PROC.BY.EXEC.COLUMNS)(SQLExecDirect( '{CALL "STPRC009" ('U')}' )): BCI Error:
SQLSTATE=42884,CODE=-440,[DataStage][SQL Client][ODBC][IBM][CLI Driver][DB2] SQL0440N No authorized routine named "STPRC009" of type "" having compatible arguments was found. SQLSTATE=42884

Question
(1) What should be entered in argument box while importing this SP?
(2) I entered the value of input assuming it is same as input parameter.

ray.wurlod · Post by **ray.wurlod** » Fri May 02, 2008 11:39 pm

Does the stored procedure name exist in the database instance to which you are connecting? Are you (the user ID used to run the job) its owner? In either case, you may need to provide a stored procedure name qualified by a schema/owner name.

horserider · Post by **horserider** » Sat May 03, 2008 5:08 pm

While importing the Stored Procedure from Manager, what should I pass in the argument list?

shamshad · Post by **shamshad** » Sun May 04, 2008 8:07 am

Did you try to pass any value from MANAGER? What error you get?