I am trying to execute the Stored Procedure from my Client program but each time i execute my Client Program, the SQLCode is "000" during the "CALL" statement.
Here are the code snippets:
The Create Stored Procedure DDL
CREATE PROCEDURE Schema.SP1
( IN VALUE1 VARCHAR(34) CCSID EBCDIC
,OUT SQLCD INTEGER
DYNAMIC RESULT SETS 5
EXTERNAL NAME 'SP1'
PARAMETER STYLE GENERAL WITH NULLS
READS SQL DATA
WLM ENVIRONMENT D1WLM1ENV
ASUTIME LIMIT 2
STAY RESIDENT NO
PROGRAM TYPE MAIN
RUN OPTIONS 'MSGFILE(OUTFILE,,,,ENQ)'
COMMIT ON RETURN NO
INHERIT SPECIAL REGISTERS
Here is the declaration for the Linkage Section of the Stored Proc:
DECLARE L4NMINFO CURSOR WITH HOLD WITH RETURN FOR
FROM BX70RFP9.UTT_PERS PERS,
PERS.LST_NAME = :WS-LAST-NAME
AND NPN.PERS_FIRM_IND = 'P'
AND PERS.SSN_NUM LIKE :WS-L4-SSN-NUM
AND NPN.SSN_TIN_NUM = PERS.SSN_NUM
FOR FETCH ONLY
CALL UTP51001(:PL-VAR-INP, :P-INP-SQL-CD)
DESCRIBE PROCEDURE UTP51001 INTO :SQLDA
ASSOCIATE LOCATOR (:LOC01)
WITH PROCEDURE UTP51001
ALLOCATE L4NMINFO CURSOR FOR RESULT SET :LOC01
FETCH L4NMINFO INTO :R1-SSN-NUM
I always hit the SQLCode "000" during the CALL statement. I am not able to find the appropriate reason for it.
The tables have data and on checking manually i am able to retrieve data.
Can anyone let me know what can be the probable reason?
Hi Ashimer, After your suggestion i had already changed the ASUTIME to NOLIMIT but i still faced the same issues.
I identified the error occurs during the Opening of the cursor which ultimately is due to the Cursor declaration.
Here i have a question that do we need to have the schema-name or the collection id or the user-id attached with the tables while declaring the cursor.
I solved that error and now i am stuck up with -981. This is due to the application attempting to execute an SQL operation, but the RRSAF connection iss not in a state that allows the processing of SQL
I compiled my Stored Procedure with ATTACH(RRSAF) and NODYNAM option but i am still facing the same issue. Is there something else i am missing??
I went through the document about the RRS and the Language Environment Setup but the problem persists.
The SQLError Code is given below:
- DSNT408I SQLCODE = -981
DSNT418I SQLSTATE = 57015 SQLSTATE RETURN CODE
Joined: 20 Oct 2006 Posts: 6968 Location: porcelain throne
you need to connect to RRS, which you have not.
since you have RRSAF in your shop, someone, someplace, somehow, is connecting to RRS.
ask your peers, project manager, or support group how it is done at your site.
Hi Dino..this is the first time we are implementing Stored Procs so nobody have any idea about this error or the working of Stored Procs!! I am contacting the Support system for "How To Connect RRS" and it will take some time.
I think Rahuindo has coded a stored procedure and would retrieve result from it after firing from front web based application and is testing SP through a cobol DB2 component.
Fot this you require below :
2). SP - compile with WLM attach = 'Y; option
3). if you are retrieving multiple result set then declare GTT table in the SP populate it
4). declare the cursor - consider A
5). At last open the cursor A and go back and end the SP.
Now coming to calling component that will call the SP , i have attached program that would help you..
2). calling DB2 component