|
View previous topic :: View next topic
|
| Author |
Message |
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
Hi All,
I am inserting records into the Global Temp table and all the records get inserted succesfully. But during the fetch execution of the Temp table i am getting SQLCODE +100. Here is the code:
| Code: |
CBL OUTDD(PNP1),NODYNAM,MAP,RES,RENT,TRUNC(BIN),LIST 00000100
IDENTIFICATION DIVISION. 00000200
PROGRAM-ID. PNP1 00000300
AUTHOR. TCS. 00000400
DATE-COMPILED. 00000500
*----------------------------------------------------------------*00000600
* G E N E R A L I N F O R M A T I O N * 00000700
*----------------------------------------------------------------*00000800
*PROGRAM NAME : PNP1 *00000900
*PROGRAM TYPE : COBOL-DB2 STORED PROCEDURE *00001000
*FUNCTIONALITY : TO FETCH THE PERSON/FIRM INFORMATI*00001100
* -ON WITH FULL PNP *00001200
*----------------------------------------------------------------*00001300
* C H A N G E H I S T O R Y L O G *00001400
*--------------------------------------------------------------- *00001500
* SR.NO. | DATE. | DESCRIPTION OF CHANGES MADE. |00001600
*----------------------------------------------------------------*00001700
* | | |00001800
* | | |00001900
*--------------------------------------------------------------- *00002000
ENVIRONMENT DIVISION. 00002100
CONFIGURATION SECTION. 00002200
SOURCE-COMPUTER. IBM-370. 00002300
OBJECT-COMPUTER. IBM-370. 00002400
INPUT-OUTPUT SECTION. 00002500
FILE-CONTROL. 00002600
00002700
DATA DIVISION. 00002800
FILE SECTION. 00002900
00003000
WORKING-STORAGE SECTION. 00003100
*----------------------------------------------------------------*00003200
* SQL COMMUNICATION AREA 00003300
*----------------------------------------------------------------*00003400
EXEC SQL 00003500
INCLUDE SQLCA 00003600
END-EXEC. 00003700
00003800
*----------------------------------------------------------------*00003900
*----------------------------------------------------------------*00004000
* ERROR CODES & MESSAGES 00004100
*----------------------------------------------------------------*00004200
01 ERROR-MESSAGE. 00004300
02 ERROR-LEN PIC S9(4) COMP VALUE +960. 00004400
02 ERROR-TEXT PIC X(120) OCCURS 8 TIMES 00004500
INDEXED BY ERROR-INDEX. 00004600
77 ERROR-TEXT-LEN PIC S9(8) COMP VALUE +120. 00004700
01 WS-OUT-POS PIC 9(9). 00004800
00004900
*----------------------------------------------------------------*00005000
* MISCELLANEOUS VARIABLES 00005100
*----------------------------------------------------------------*00005200
01 WS-INP-PNP-NUM PIC X(10) VALUE SPACES. 00005300
01 WS-BYPASS-IND PIC X(01). 00005400
01 WS-PERS-FIRM-IND PIC X(01). 00005500
01 WS-WORK-VARS. 00005600
05 WS-LST-NAME PIC X(30) VALUE SPACES. 00005900
05 WS-FRST-NAME PIC X(20) VALUE SPACES. 00006000
01 WS-COUNT PIC S9(05)V USAGE COMP-3. 00006600
01 WS-PERS-FETCH-SW PIC X(01) VALUE 'N'. 00008000
88 PERS-FETCH-END VALUE 'Y'. 00008100
01 WS-FIRM-FETCH-SW PIC X(01) VALUE 'N'. 00008600
88 FIRM-FETCH-END VALUE 'Y'. 00008700
*----------------------------------------------------------------*00008800
00008900
*----------------------------------------------------------------*00009000
00009100
* CURSOR TO FETCH THE PERSON DETAILS 00009200
EXEC SQL 00009300
DECLARE PNP_PERS_DTL CURSOR FOR 00009400
SELECT PNP.FRST_NAME 00009500
,PNP.LST_NAME 00009600
FROM 00010700
UTT_PNP PERS 00010800
WHERE 00011300
PNP.PNP_NUM = :WS-INP-PNP-NUM 00011400
END-EXEC 00014700
* 00030800
*CURSOR TO FETCH THE FIRM DETAILS 00030900
EXEC SQL 00031000
DECLARE PNP_FIRM_INFO CURSOR FOR 00031100
SELECT ' ' 00031200
,PNP.FIRM_NAME 00031300
FROM UTT_PNP_MASTER PNP 00032500
WHERE 00033000
PNP.PNP_NUM = :WS-INP-PNP-NUM 00033100
FOR FETCH ONLY 00039500
END-EXEC 00039600
*----------------------------------------------------------------*00039700
LINKAGE SECTION. 00039800
*----------------------------------------------------------------*00039900
01 SP-INP-PNP-NUM PIC X(10). 00040000
01 SP-OUT-ERR-CD PIC S9(9) COMP. 00040030
01 SP-OUT-ERR-MSG. 00040040
49 SP-ERR-MSG-LTH PIC S9(4) COMP. 00040050
49 SP-ERR-MSG-TXT PIC X(998). 00040060
*----------------------------------------------------------------*00040100
00040200
*----------------------------------------------------------------*00040300
PROCEDURE DIVISION USING SP-INP-PNP-NUM 00040400
SP-OUT-ERR-CD 00040500
SP-OUT-ERR-MSG. 00040600
*----------------------------------------------------------------*00040700
00040800
*----------------------------------------------------------------*00040900
0000-MAIN-PROCESS. 00041000
*----------------------------------------------------------------*00041100
MOVE 1 TO WS-OUT-POS 00041200
MOVE 0 TO SP-OUT-ERR-CD 00041300
MOVE 1 TO SP-ERR-MSG-LTH 00041400
MOVE SPACE TO SP-ERR-MSG-TXT 00041500
00041600
PERFORM 1000-VALIDATE-INPUT 00041700
THRU 1000-VALIDATE-INPUT-X 00041800
00041900
PERFORM 2000-BUILD-TEMPTABL-PARA 00042000
THRU 2000-BUILD-TEMPTABL-X 00042100
00042200
PERFORM 3000-ENTITY-TYPE-PARA 00042300
THRU 3000-ENTITY-TYPE-X 00042400
00042500
PERFORM 4000-FETCH-TEMP-TABLE 00042600
THRU 4000-FETCH-TEMP-X 00042700
00042800
PERFORM 9999-SP-GOBACK-PARA 00042900
THRU 9999-SP-GOBACK-PARA-X 00043000
. 00043100
00043200
0000-MAIN-PROCESS-X. 00043300
EXIT. 00043400
*----------------------------------------------------------------*00043500
00043600
*----------------------------------------------------------------*00043700
1000-VALIDATE-INPUT. 00043800
*----------------------------------------------------------------*00043900
MOVE 'N' TO WS-BYPASS-IND 00044000
MOVE SP-INP-PNP-NUM TO WS-INP-PNP-NUM 00044100
DISPLAY "PNP NUMBER" WS-INP-PNP-NUM 00044110
. 00045900
00046000
1000-VALIDATE-INPUT-X. 00046100
EXIT. 00046200
*----------------------------------------------------------------*00046300
00046400
*----------------------------------------------------------------*00046500
2000-BUILD-TEMPTABL-PARA. 00046600
*----------------------------------------------------------------*00046700
* TO DECLARE THE TEMP TABLE FROM WHICH ALL THE REQUIRED INFO HAS 00046800
* TO BE FETCHED 00046900
*----------------------------------------------------------------*00047000
EXEC SQL 00047001
DECLARE GLOBAL TEMPORARY TABLE SESSION.PNP_TMP 00047002
(TEMP_FRST_NAME CHAR(20) 00047003
,TEMP_LST_NAME CHAR(30)) 00047004
ON COMMIT PRESERVE ROWS 00047015
END-EXEC 00047016
EVALUATE SQLCODE 00047040
WHEN 0 00047041
CONTINUE 00047042
WHEN -601 00047050
EXEC SQL 00047080
DROP TABLE SESSION.PNP_TMP 00047090
END-EXEC 00047091
00047092
EXEC SQL
DECLARE GLOBAL TEMPORARY TABLE SESSION.PNP_TMP
(TEMP_FRST_NAME CHAR(20)
,TEMP_LST_NAME CHAR(30))
ON COMMIT PRESERVE ROWS
END-EXEC
WHEN OTHER 00049510
MOVE SQLCODE TO SP-OUT-ERR-CD 00049520
PERFORM 8888-SQL-ERROR-PARA 00049530
THRU 8888-SQL-ERROR-PARA-X 00049540
PERFORM 9999-SP-GOBACK-PARA 00049550
THRU 9999-SP-GOBACK-PARA-X 00049560
END-EVALUATE 00049570
. 00049600
00049700
2000-BUILD-TEMPTABL-X. 00049800
EXIT. 00049900
*----------------------------------------------------------------*00050000
00050100
*----------------------------------------------------------------*00050200
3000-ENTITY-TYPE-PARA. 00050300
*----------------------------------------------------------------*00050400
* TO VERIFY THE ENTITY TYPE OF THE PNP 00050500
*----------------------------------------------------------------*00050600
EXEC SQL 00050700
SELECT PERS_FIRM_IND 00050800
INTO :WS-PERS-FIRM-IND 00050900
FROM UTT_PNP
WHERE PNP_NUM = :WS-INP-PNP-NUM 00051100
END-EXEC 00051200
EVALUATE SQLCODE 00051300
WHEN ZEROS 00051400
IF WS-PERS-FIRM-IND = 'P' 00051500
PERFORM UNTIL PERS-FETCH-END 00051600
PERFORM 3500-PROCESS-PERS-PARA 00051700
THRU 3500-EXIT 00051800
END-PERFORM 00051900
END-IF 00053200
IF WS-PERS-FIRM-IND = 'F' 00053300
PERFORM UNTIL FIRM-FETCH-END 00053400
PERFORM 3900-CUR-FIRM-PARA 00053500
THRU 3900-EXIT 00053600
END-PERFORM 00053700
END-IF 00053800
WHEN OTHER 00054200
MOVE SQLCODE TO SP-OUT-ERR-CD 00054300
PERFORM 8888-SQL-ERROR-PARA 00054400
THRU 8888-SQL-ERROR-PARA-X 00054500
PERFORM 9999-SP-GOBACK-PARA 00054600
THRU 9999-SP-GOBACK-PARA-X 00054700
END-EVALUATE 00054800
. 00054900
00055000
3000-ENTITY-TYPE-X. 00055100
EXIT. 00055200
*----------------------------------------------------------------*00055300
00055400
* 00058200
*----------------------------------------------------------------*00058300
3500-PROCESS-PERS-PARA. 00058400
*----------------------------------------------------------------*00058500
* TO PROCESS THE OA/DA CONTRACT AND FETCH THE REQUIRED DETAILS 00058600
*----------------------------------------------------------------*00058700
EXEC SQL 00058800
OPEN PNP_PERS_DTL 00058900
END-EXEC 00059000
EVALUATE SQLCODE 00059100
WHEN 0 00059200
CONTINUE 00059300
WHEN OTHER 00059400
MOVE SQLCODE TO SP-OUT-ERR-CD 00059500
PERFORM 8888-SQL-ERROR-PARA 00059600
THRU 8888-SQL-ERROR-PARA-X 00059700
PERFORM 9999-SP-GOBACK-PARA 00059800
THRU 9999-SP-GOBACK-PARA-X 00059900
END-EVALUATE 00060000
PERFORM UNTIL SQLCODE = 100 00060100
EXEC SQL 00060200
FETCH PNP_PERS_DTL 00060300
INTO :WS-FRST-NAME 00060400
,:WS-LST-NAME 00060500
END-EXEC 00061400
EVALUATE SQLCODE 00061500
WHEN 0 00061600
IF NOT PERS-FETCH-END 00061610
MOVE WS-CNTR-NUM TO WS-SAVED-CNTR-NUM 00061630
PERFORM 0160-LOAD-TEMP-TABLE 00061640
THRU 0160-LOAD-TEMP-X 00061650
END-IF 00061670
WHEN 100 00061900
SET PERS-FETCH-END TO TRUE 00062000
WHEN OTHER 00062100
MOVE SQLCODE TO SP-OUT-ERR-CD 00062200
PERFORM 8888-SQL-ERROR-PARA 00062300
THRU 8888-SQL-ERROR-PARA-X 00062400
PERFORM 9999-SP-GOBACK-PARA 00062500
THRU 9999-SP-GOBACK-PARA-X 00062600
END-EVALUATE 00062700
END-PERFORM 00062800
. 00062900
00063000
3500-EXIT. 00063100
EXIT. 00063200
*----------------------------------------------------------------*00063300
00063400
*----------------------------------------------------------------*00105100
3900-CUR-FIRM-PARA. 00105200
*----------------------------------------------------------------*00105300
* TO PROCESS THE OA/DA CONTRACT AND FETCH THE REQUIRED DETAILS 00105400
*----------------------------------------------------------------*00105500
EXEC SQL 00105600
OPEN PNP_FIRM_INFO 00105700
END-EXEC 00105800
EVALUATE SQLCODE 00105900
WHEN 0 00106000
CONTINUE 00106100
WHEN OTHER 00106200
MOVE SQLCODE TO SP-OUT-ERR-CD 00106300
PERFORM 8888-SQL-ERROR-PARA 00106400
THRU 8888-SQL-ERROR-PARA-X 00106500
PERFORM 9999-SP-GOBACK-PARA 00106600
THRU 9999-SP-GOBACK-PARA-X 00106700
END-EVALUATE 00106800
PERFORM UNTIL SQLCODE = 100 00106900
EXEC SQL 00107000
FETCH PNP_FIRM_INFO 00107100
INTO :WS-FRST-NAME 00107200
,:WS-LST-NAME 00107300
END-EXEC 00108200
EVALUATE SQLCODE 00108300
WHEN 0 00108400
IF NOT FIRM-FETCH-END 00108410
PERFORM 0160-LOAD-TEMP-TABLE 00108500
THRU 0160-LOAD-TEMP-X 00108600
END-IF 00108630
WHEN 100 00108700
SET FIRM-FETCH-END TO TRUE 00108800
WHEN OTHER 00108900
MOVE SQLCODE TO SP-OUT-ERR-CD 00109000
PERFORM 8888-SQL-ERROR-PARA 00109100
THRU 8888-SQL-ERROR-PARA-X 00109200
PERFORM 9999-SP-GOBACK-PARA 00109300
THRU 9999-SP-GOBACK-PARA-X 00109400
END-EVALUATE 00109500
END-PERFORM 00109600
. 00109700
00109800
3900-EXIT. 00109900
EXIT. 00110000
*----------------------------------------------------------------*00110100
00110200
*----------------------------------------------------------------*00110300
0160-LOAD-TEMP-TABLE. 00110400
*----------------------------------------------------------------*00110500
* TO INSERT THE FETCHED INFORMATION IN THE TEMP TABLE 00110600
*----------------------------------------------------------------*00110700
EXEC SQL 00110800
INSERT INTO SESSION.PNP_TMP 00110900
VALUES (:WS-FRST-NAME 00111000
,:WS-LST-NAME) 00111100
END-EXEC 00112000
EVALUATE SQLCODE 00112100
WHEN 0 00112200
CONTINUE 00112300
WHEN OTHER 00112400
DISPLAY "SQLCODE", SQLCODE 00112410
DISPLAY WS-CNTR-NUM 00112420
MOVE SQLCODE TO SP-OUT-ERR-CD 00112500
PERFORM 8888-SQL-ERROR-PARA 00112600
THRU 8888-SQL-ERROR-PARA-X 00112700
PERFORM 9999-SP-GOBACK-PARA 00112800
THRU 9999-SP-GOBACK-PARA-X 00112900
END-EVALUATE. 00113000
0160-LOAD-TEMP-X. 00113100
EXIT. 00113200
*----------------------------------------------------------------*00113300
00113400
*----------------------------------------------------------------*00113500
4000-FETCH-TEMP-TABLE. 00113600
*----------------------------------------------------------------*00113700
* TO INSERT THE FETCHED INFORMATION IN THE TEMP TABLE 00113800
*----------------------------------------------------------------*00113900
DISPLAY "PERFORMING 4000-FETCH-TEMP-TABLE" 00113910
EXEC SQL 00114000
DECLARE PNP_INF CURSOR WITH HOLD WITH RETURN FOR 00114100
SELECT TEMP_FRST_NAME 00114200
,TEMP_LST_NAME 00114300
FROM SESSION.PNP_TMP 00115400
END-EXEC 00115500
EVALUATE SQLCODE 00115600
WHEN 0 00115700
CONTINUE 00115800
WHEN OTHER 00115900
DISPLAY "SQLCODE", SQLCODE 00115910
MOVE SQLCODE TO SP-OUT-ERR-CD 00116000
PERFORM 8888-SQL-ERROR-PARA 00116100
THRU 8888-SQL-ERROR-PARA-X 00116200
PERFORM 9999-SP-GOBACK-PARA 00116300
THRU 9999-SP-GOBACK-PARA-X 00116400
END-EVALUATE. 00116500
EXEC SQL 00116600
OPEN PNP_INF 00116700
END-EXEC 00116800
EVALUATE SQLCODE 00116900
WHEN 0 00117000
CONTINUE 00117100
WHEN OTHER 00117200
MOVE SQLCODE TO SP-OUT-ERR-CD 00117300
PERFORM 8888-SQL-ERROR-PARA 00117400
THRU 8888-SQL-ERROR-PARA-X 00117500
PERFORM 9999-SP-GOBACK-PARA 00117600
THRU 9999-SP-GOBACK-PARA-X 00117700
END-EVALUATE. 00117800
4000-FETCH-TEMP-X. 00117900
EXIT. 00118000
00118100
*----------------------------------------------------------------*00118200
8888-SQL-ERROR-PARA. 00118300
*----------------------------------------------------------------*00118400
* CALLING DSNTIAR TO DETERMINE THE SQL MESSAGE 00118500
*----------------------------------------------------------------*00118600
CALL 'DSNTIAR' USING SQLCA ERROR-MESSAGE ERROR-TEXT-LEN 00118700
MOVE 1 TO WS-OUT-POS 00118800
IF RETURN-CODE < 8 00118900
MOVE 1 TO WS-OUT-POS 00119000
PERFORM VARYING ERROR-INDEX 00119100
FROM 1 BY 1 UNTIL ERROR-INDEX GREATER THAN 8 00119200
MOVE ERROR-TEXT(ERROR-INDEX) 00119300
TO SP-ERR-MSG-TXT(WS-OUT-POS:120) 00119400
ADD 120 TO WS-OUT-POS 00119500
END-PERFORM 00119600
COMPUTE SP-ERR-MSG-LTH = WS-OUT-POS 00119700
ELSE 00119800
DISPLAY 'DSNTIAR FAILED. RETURN-CODE : ' RETURN-CODE 00119900
END-IF 00120000
. 00120100
8888-SQL-ERROR-PARA-X. 00120200
EXIT. 00120300
*----------------------------------------------------------------*00120400
00120500
*----------------------------------------------------------------*00120600
9999-SP-GOBACK-PARA. 00120700
*----------------------------------------------------------------*00120800
GOBACK. 00120900
00121000
9999-SP-GOBACK-PARA-X. 00121100
EXIT. 00121200
*----------------------------------------------------------------*00121300
|
Does anybody have any idea about it?? Please let me know if any other details are needed. |
|
| Back to top |
|
 |
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
Check if the key used in fetch has valid combination to fetch some rows from the table. For experiment, run your fetch query in QMF/spufi and check if you get some data there.
And yes, please read about +100 in manuals. |
|
| Back to top |
|
|
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
Hi Anuj,
I have checked the query in Spufi and i am able to fetch data from it. The INSERT works succesfully in the SP (i tried that using a display statement before the Fetch from the Temp table). But during the Fetch, somehow all the data seems to get deleted. I can guess i ve done something silly but am unable to find what is that. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| i only see a declare and an open for PNP_INF. Do see any fetchs....... |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
maybe you are fetching from the module that invokes the SP.
There is a great REDBOOK about SP's: Through the CALL and beyond
this is DB2 9 for z/os
and this is just DB2 for z/OS |
|
| Back to top |
|
|
suresh1624
New User
Joined: 21 Nov 2007
Posts: 28
Location: chennai
|
|
|
|
| can you please post the stored procedure definition.?also can u exactly post the message you are getting from the stored proc? |
|
| Back to top |
|
|
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
Hi Dick,
I have gone through the book and haven't found any clue from it.
Further i have also looked into the in-house Stored Procedures and the calling programs but found that i have similar coding logic.
Here is the SP definition:
| Code: |
CREATE PROCEDURE BXCRFG89.PNP1
( IN PNPNUM CHAR(10) CCSID EBCDIC
,OUT SQLCD INTEGER
,INOUT SQLMSG VARCHAR(1000) CCSID EBCDIC
)
RESULT SETS 20
EXTERNAL NAME 'PNP1'
LANGUAGE COBOL
PARAMETER STYLE GENERAL WITH NULLS
NOT DETERMINISTIC
NULL CALL
FENCED
MODIFIES SQL DATA
NO DBINFO
COLLID BCCRFG89
WLM ENVIRONMENT DCC1WLM1ENV
ASUTIME NO LIMIT
STAY RESIDENT NO
PROGRAM TYPE MAIN
SECURITY DB2
RUN OPTIONS 'HEAP(,,ANY,FREE),STAC(,,ANY,),STO(,,,4K),
BE(4K,,),LIBSTACK(32,0,FREE),ALL31(ON)'
COMMIT ON RETURN NO
INHERIT SPECIAL REGISTERS
;
|
Here is the SQL Error Code i am receiving:
| Code: |
CONNECTION TO STORED PROC IS SUCCESSFUL
RETURN CODE FROM STORED PROCEDURE:0000000100
ERROR DESCRIPTION --> DSNT404I SQLCODE = 100, NOT FOUND: ROW NOT FOUND FOR FETCH, UPDATE, OR DELETE, OR THE RESULT OF A
QUERY IS AN EMPTY TABLE
DSNT418I SQLSTATE = 02000 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRFN SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -110 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF92' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000'
SQL DIAGNOSTIC INFORMATION
|
[/code] |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
You do understand that a +100 is the normal end of data when using a cursor to return multiple rows? |
|
| Back to top |
|
|
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
Hi Dick,
Yes i understand that +100 is the normal End of Data for a cursor which returns multiple rows. But the problem i am facing is, during my testing of the SP, i found that there are around 7 records which are inserted into the Temp table (using a Display statement and checking the Spool of the WLM address space) but when i do a fetch on the Temp table , i encounter this SQLCODE +100. I am not able to retrieve even one row of data.
So what i am unable to understand is that how does the rows get deleted from the Temp table? |
|
| Back to top |
|
|
suresh1624
New User
Joined: 21 Nov 2007
Posts: 28
Location: chennai
|
|
|
|
| why is the number of result sets defined as 20 instead of 1? |
|
| Back to top |
|
|
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
| We were trying out different options and hence we tried to increase the result-sets to see if there is any difference. But we still face the same issue. |
|
| Back to top |
|
|
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
Hi,
I was able to solved the problem. I introduced the following query just before fetching from the global temp table:
| Code: |
EXEC SQL
SELECT COUNT(*)
INTO :WS-COUNT
FROM PNP_TMP
END-EXEC
|
Strange but the SP worked retrieved result-sets. I am not sure what can be the reason behind it. Please let me if am missing some DB2 concepts out here. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
your result set was 20, there are only 10 rows retrievable.
The fetch in the CALLing module should have been ROWSET retrieval.
Because 20 was the results sets number, the +100 was valid for first fetch.
Either reduce RESULT SETS 20
to RESULT SETS 1
or change your CALLing program to properly deal with ROWSET retrieval,
i.e. using the SQLCA values to determine number of rows retrieved.
you also could have INSERTed to the GTT with one SQL
instead of the looping and double Flag interrogation.
You could really reduce the amount of code and SQL CALLs in the SP by:
INSERT INTO GTT
SELECT ... ...
FROM ....
WHERE.. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| rahuindo wrote: |
Hi,
I was able to solved the problem. I introduced the following query just before fetching from the global temp table:
| Code: |
EXEC SQL
SELECT COUNT(*)
INTO :WS-COUNT
FROM PNP_TMP
END-EXEC
|
Strange but the SP worked retrieved result-sets. I am not sure what can be the reason behind it. Please let me if am missing some DB2 concepts out here. |
did you by chance also change RESULT SETS 20 from 20 to 1?
There is no way that DB2 magically created the rows due to the SELECT statement.
the DB2 concept is about RESULT SETS. you need to review (links I provided) the concept of the RESULT SETS parameter. |
|
| Back to top |
|
|
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
Hi Dick,
I had changed the DB2 Result-set parameters to 1 and executed the SP. Still then i was getting SQLCODE +100. Then i added this query and executed the SP, it worked. Then I tried removing this query from the SP and executing it again, i was not able to retrieve any result-sets. I am trying to find out that what is the difference. I will let you know what is the actual problem. |
|
| Back to top |
|
|
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
Hi All,
I was able to find the reason behind it. I am evaluating the SQLCODE after declaring the cursor which should not be the case. Hence if in a previous fetch the SQLCODE was +100, the same SQLCODE i.e. +100 was then evaluated after the declaration of the cursor which led to the problem. This solved the problem. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
| Great! Just out of curiosity, where do you evaluate the SQLCODE now? |
|
| Back to top |
|
|
rahuindo
New User
Joined: 09 Apr 2008
Posts: 83
Location: Chennai
|
|
|
|
Hi,
I am not putting an Evaluate SQLCODE after the declaration of the cursor. Am just evaluating the SQLCODE after the OPEN statement alone. |
|
| Back to top |
|
|
mainframefan
New User
Joined: 12 May 2008
Posts: 1
Location: Pune
|
|
|
|
I am also facing the same problem while trying to run a select query thru pogram, I get +100. When the same query is fired using Spufi, result row is retrieved. I tried using count(*) query approach...but it didn't help much. If I continuously fire my program say 5 times I get the error 2 or 3 times for the same row in datatbase. While after using count(*) I get the error lesser times say 1 in 5 times. Can anyone help...... is it anything related to buffer space problems as when my program is fired there is a huge amount of data being copied from one table to other (of the order of 60k) by a parallel transaction.
Thanks a lot for looking into the problem. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello and welcome to the forum,
When you have a question/problem, it is best to start a new topic for your issue - rather than reply to an inactive topic.
You need to post the problem code and clearly describe what is happening (or not happening that should). What you have described is a problem with the code, but as you have not posted the code, not much can be done. You do not need to post the entire probram, just the bits that are related to this situation. When posting code, use copy/paste and the "Code" tag. Use Preview to see your post as it will appear to the forum and when it is as you wish, Submit. |
|
| Back to top |
|
|
|
|
