Hi All,
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
Code:
CREATE PROCEDURE Schema.SP1
( IN VALUE1 VARCHAR(34) CCSID EBCDIC
,OUT SQLCD INTEGER
)
DYNAMIC RESULT SETS 5
EXTERNAL NAME 'SP1'
LANGUAGE COBOL
PARAMETER STYLE GENERAL WITH NULLS
NOT DETERMINISTIC
NULL CALL
FENCED
READS SQL DATA
NO DBINFO
COLLID collid
WLM ENVIRONMENT D1WLM1ENV
ASUTIME LIMIT 2
STAY RESIDENT NO
PROGRAM TYPE MAIN
SECURITY DB2
RUN OPTIONS 'MSGFILE(OUTFILE,,,,ENQ)'
COMMIT ON RETURN NO
INHERIT SPECIAL REGISTERS
;
Here is the declaration for the Linkage Section of the Stored Proc:
EXEC SQL
DECLARE L4NMINFO CURSOR WITH HOLD WITH RETURN FOR
SELECT PERS.SSN_NUM
,NPN.NPN_NUM
,PERS.FRST_NAME
,PERS.MID_NAME
,PERS.LST_NAME
,PERS.SUBJ_ID
FROM BX70RFP9.UTT_PERS PERS,
BX70RFP9.UTT_NPN_MASTER NPN
WHERE
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
END-EXEC
EXEC SQL
CALL UTP51001(:PL-VAR-INP, :P-INP-SQL-CD)
END-EXEC.
EXEC SQL
DESCRIBE PROCEDURE UTP51001 INTO :SQLDA
END-EXEC
EXEC SQL
ASSOCIATE LOCATOR (:LOC01)
WITH PROCEDURE UTP51001
END-EXEC.
EXEC SQL
ALLOCATE L4NMINFO CURSOR FOR RESULT SET :LOC01
END-EXEC.
EXEC SQL
FETCH L4NMINFO INTO :R1-SSN-NUM
,:R1-NPN-NUM
,:R1-FRST-NAME
,:R1-MID-NAME
,:R1-LST-NAME
,:R1-SUBJ-ID
END-EXEC.
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?
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
What is the value of SQLCD from the SP ?
Check on the ASUTIME of 2 ... this could not be the reason but it could be too less for the SP to open the cursor and return back the result set ....
Hi Ashimer,
During the CALL statement execution, the SQLCODE is "000" and then during the ASSOCIATE LOCATOR it is "-482".
I modified the ASUTIME parameter to 5 but it still shows the same error.
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
-482 specifies that no locator was returned by the stored procedure ...
increase the ASUTIME to 100000 or NOLIMIT and try again .... dont worry about the figure ....
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.
Hi,
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
statements.
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
REASON 00C12219
DSNT418I SQLSTATE = 57015 SQLSTATE RETURN CODE
Joined: 20 Oct 2006 Posts: 6966 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.
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
Just a passing thought-- can't you use "DB2 Store Builder" from IBM to test Store PROCs instead of CALLing store procs from a COBOL program, that makes things pretty easy.
Joined: 20 Oct 2006 Posts: 6966 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.
Can you please post the two JCLs that you have used to compile the server(Stored procedure(STP)) program and the client program( the program calling the STP)?
Hi Raghu,
We compile the Stored Procedure and the CLient Program using Changeman. hence i am including only some of the important steps as each JCL runs into 400 lines of code:
Client Program:
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 :
1). Schema
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..
1). schema
2). calling DB2 component
3). SP
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
Raghu,
until rahuindo changes his attach statement for the pre-compile from (ATTACH(RRSAF)) to (ATTACH(TSO)),
I don't think the proposed changes by you will have any effect.
Also, rahuindo has not responded as to why he has the attach to RRSAF in the first place.
I noticed the ATTACH statement in the beginning of the pre-compile. I came to a conclusion that the LINKEDIT step the last one, so that may not be a problem. If it is not working, the ATTACH(RRSAF) for the client program's JCL has to be modified. Let him respond....
Hi Dino,
I was not attaching RRSAF until i encountered this Error. As the explanation for the -981 suggested that the RRSAF connection may not be there. Hence i compiled the program and stored proc with ATTACH(RRSAF).
Raghu,
I link-edited the program as suggested by you.
I think you have modified both the JCLs. Do not modify both. As I posted earlier, you just modify the JCL for Client program only. If you modify the SERVER program(STP) with DSNELI, you will get -927 sqlcode.
So make sure that you didn't modified both the JCLs.
For Server program -- use DSNRLI
For Client program -- use DSNELI.
For client program, you can avoid the use of ATTACH(RRSAF).
Hi Raghu,
I am now trying to invoke the SP thru DB2I and on executing the SP, i constantly encounter the SQLCODE -981. I figured out to some extent the DSNRLI is not getting linked to the SP even though i have coded "INCLUDE SYSLIB(DSNRLI)" in the link-edit step. I can see that execution of each SQL Statements calls are made to DSNHLI instead of DSNRLI. I suspect this may be the reason for the SQLCODE -981.
Can anyone suggest that how can i modify my SP so that the calls are made to DSNRLI and not DSNHLI?
