COBOL DB2 using Temporary table, Error while compiling

IBM Mainframe Forums -> DB2

Quick References

View previous topic :: View next topic

Author

Message

sushanth bobby

Senior Member

Joined: 29 Jul 2008
Posts: 1020
Location: India

Hi,

I am getting following error while compiling the program.

Code:

DSNH504I E DSNHSMUD LINE 81 COL 19 CURSOR "DEPTCUR" WAS NOT DECLARED
DSNH521I W DSNHSM3 LINE 107 COL 19 THE FETCH FOR CURSOR "DEPTCUR" IS ASSUMED TO APPLY TO A DYNAMIC ALLOCATE CURSOR STATEMENT

Error is pointing to the following line.

Code:

80 007600 EXEC SQL
81 007700 OPEN DEPTCUR
82 007800 END-EXEC.

But, i have declared the cursor. Following is my program

Code:

000100 IDENTIFICATION DIVISION.
000200 PROGRAM-ID. TEMPTB.
000300*AUTHOR. SUSHANTH BOBBY.
000400*DATE-WRITTEN. MARCH 2009.
000500*REMARKS. A SAMPLE DB2 TEMPORARY TABLE CURSOR PROGRAM ***
000600
001300 ENVIRONMENT DIVISION.
001400
001500 DATA DIVISION.
001600
001700****************************************************************
001800 WORKING-STORAGE SECTION.
001900****************************************************************
001910 01 WS-TEMP-VAR.
001920 05 END-OF-LOOP PIC X(1).
001930 05 RECORD-COUNT PIC 9(7).
001940
002000 01 WS-TEMP-DEPT.
002100 10 WS0-DEPT PIC S9(4) USAGE COMP.
002200 10 WS1-DEPT PIC 9(4).
002300 10 WS-NAME PIC X(5).
002400
002500* ***********************************************************
002600* SQL CONTROL BLOCK
002700* ***********************************************************
002800 EXEC SQL INCLUDE SQLCA END-EXEC.
002900
002910 EXEC SQL DECLARE DEPTTMP TABLE
002930 ( DEPT SMALLINT NOT NULL,
002940 NAME CHAR(5) NOT NULL
002970 )
002980 END-EXEC.
002990
003000* ************************************************************
003100* CURSOR DECLARATION
003200* ************************************************************
003300 EXEC SQL
003400 DROP TABLE SESSION.DEPTTMP
003500 END-EXEC.
003600
003700 EXEC SQL
003800 DECLARE GLOBAL TEMPORARY TABLE SESSION.DEPTTMP
003900 (DEPT SMALLINT NOT NULL
004000 ,NAME CHAR(5) NOT NULL
004100 )
004200 ;
004300 END-EXEC.
004400
004500 EXEC SQL
004600 DECLARE DEPTCUR CURSOR FOR
004700 SELECT DEPT ,NAME FROM SESSION.DEPTTMP
004800 END-EXEC.
004900
005000 EXEC SQL
005100 INSERT INTO SESSION.DEPTTMP VALUES(10, 'N1');
005200 INSERT INTO SESSION.DEPTTMP VALUES(10, 'N2');
005300 INSERT INTO SESSION.DEPTTMP VALUES(10, 'N3');
005400 INSERT INTO SESSION.DEPTTMP VALUES(10, 'N4');
005500 INSERT INTO SESSION.DEPTTMP VALUES(10, 'N5');
005600 INSERT INTO SESSION.DEPTTMP VALUES(10, 'N6');
005700
005800 INSERT INTO SESSION.DEPTTMP VALUES(20, 'N1');
005900 INSERT INTO SESSION.DEPTTMP VALUES(20, 'N2');
006000 INSERT INTO SESSION.DEPTTMP VALUES(20, 'N3');
006100
006200 INSERT INTO SESSION.DEPTTMP VALUES(30, 'N4');
006300 INSERT INTO SESSION.DEPTTMP VALUES(30, 'N5');
006400 INSERT INTO SESSION.DEPTTMP VALUES(30, 'N6');
006500 END-EXEC.
006600
006700
006800* ***********************************************************
006900
007000****************************************************************
007100 PROCEDURE DIVISION.
007200 INITIALIZE WS-TEMP-DEPT
007300* ***********************************************************
007400* OPENING ECURSOR
007500* ***********************************************************
007600 EXEC SQL
007700 OPEN DEPTCUR
007800 END-EXEC.
007900
008000 0000-START-PROGRAM.
008100 EXEC SQL
008200 WHENEVER SQLERROR
008300 GOTO 9000-DB-ERROR
008400 END-EXEC.
008500
008600 EXEC SQL
008700 WHENEVER SQLWARNING
008800 CONTINUE
008900 END-EXEC.
009000
009100 EXEC SQL
009200 WHENEVER NOT FOUND
009300 CONTINUE
009400 END-EXEC.
009500
009600
009700 PERFORM 0100-READ-PARA.
009800 GO TO 1000-END-PROGRAM.
009900
010000 0100-READ-PARA.
010100 PERFORM UNTIL END-OF-LOOP = 'Y'
010200 EXEC SQL
010300 FETCH DEPTCUR
010400 INTO :WS0-DEPT , :WS-NAME
010500 END-EXEC
010600 EVALUATE SQLCODE
010700 WHEN 0
010800 MOVE WS0-DEPT TO WS1-DEPT
010900 DISPLAY WS0-DEPT WS1-DEPT
011000 ADD 1 TO RECORD-COUNT
011100
011200 WHEN +100
011300 DISPLAY 'TOTAL NUMBER OF RECORDS = ' RECORD-COUNT
011400 MOVE 'Y' TO END-OF-LOOP
011500
011600 WHEN OTHER
011700 DISPLAY 'ERROR IN RETREVING DATA'
011800 DISPLAY 'SQLCODE = ' SQLCODE
011900
012000 END-EVALUATE
012100 END-PERFORM.
012200
012300 1000-END-PROGRAM.
012400 EXEC SQL
012500 CLOSE DEPTCUR
012600 END-EXEC.
012700 GOBACK.
012800
012900 9000-DB-ERROR.
013000 GO TO 1000-END-PROGRAM.
013100

What is the reason for this error ?

Thank You,
Sushanth Bobby

ashimer

Active Member

Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

Posted: Tue Mar 31, 2009 10:33 pm

Declare SESSION.DEPTTMP in procedure division along with the INSERT stmts ..

sushanth bobby

Senior Member

Joined: 29 Jul 2008
Posts: 1020
Location: India

Ashimer,

Still getting the same error.

Code:

DSNHSMUD LINE 81 COL 19 CURSOR "DEPTCUR" WAS NOT DECLARED
DSNHSM3 LINE 107 COL 19 THE FETCH FOR CURSOR "DEPTCUR" IS ASSUMED TO APPLY TO A DYNAMIC ALLOCATE CURSOR STATEMENT

Error Line

Code:

80 007600 EXEC SQL
81 007700 OPEN DEPTCUR
82 007800 END-EXEC.

Warning Line

Code:

106 010200 EXEC SQL
107 010300 FETCH DEPTCUR
108 010400 INTO :WS0-DEPT , :WS-NAME
109 010500 END-EXEC

Following is the changed code.

Code:

000100 IDENTIFICATION DIVISION.
000200 PROGRAM-ID. TEMPTB.
000300*AUTHOR. SUSHANTH BOBBY.
000400*DATE-WRITTEN. MARCH 2009.
000500*REMARKS. A SAMPLE DB2 TEMPORARY TABLE CURSOR PROGRAM ***
000600
001300 ENVIRONMENT DIVISION.
001400
001500 DATA DIVISION.
001600
001700****************************************************************
001800 WORKING-STORAGE SECTION.
001900****************************************************************
001910 01 WS-TEMP-VAR.
001920 05 END-OF-LOOP PIC X(1).
001930 05 RECORD-COUNT PIC 9(7).
001940
002000 01 WS-TEMP-DEPT.
002100 10 WS0-DEPT PIC S9(4) USAGE COMP.
002200 10 WS1-DEPT PIC 9(4).
002300 10 WS-NAME PIC X(5).
002400
002500* ***********************************************************
002600* SQL CONTROL BLOCK
002700* ***********************************************************
002800 EXEC SQL INCLUDE SQLCA END-EXEC.
002900
002910 EXEC SQL DECLARE DEPTTMP TABLE
002920 ( DEPT SMALLINT NOT NULL,
002930 NAME CHAR(5) NOT NULL
002940 )
002950 END-EXEC.
002990
003000* ************************************************************
003100* CURSOR DECLARATION
003200* ************************************************************
004400
004500 EXEC SQL
004600 DECLARE DEPTCUR CURSOR FOR
004700 SELECT DEPT ,NAME FROM SESSION.DEPTTMP
004800 END-EXEC.
006700
006800* ************************************************************
006900
007000****************************************************************
007100 PROCEDURE DIVISION.
007106
007110* EXEC SQL
007120* DROP TABLE SESSION.DEPTTMP
007130* END-EXEC.
007140
007150 EXEC SQL
007160 DECLARE GLOBAL TEMPORARY TABLE SESSION.DEPTTMP
007170 (DEPT SMALLINT NOT NULL
007180 ,NAME CHAR(5) NOT NULL
007190 )
007192 END-EXEC.
007193
007194 EXEC SQL
007195 INSERT INTO SESSION.DEPTTMP VALUES(10, 'N1');
007196 INSERT INTO SESSION.DEPTTMP VALUES(10, 'N2');
007197 INSERT INTO SESSION.DEPTTMP VALUES(10, 'N3');
007198 INSERT INTO SESSION.DEPTTMP VALUES(10, 'N4');
007199 INSERT INTO SESSION.DEPTTMP VALUES(10, 'N5');
007200 INSERT INTO SESSION.DEPTTMP VALUES(10, 'N6');
007201
007202 INSERT INTO SESSION.DEPTTMP VALUES(20, 'N1');
007203 INSERT INTO SESSION.DEPTTMP VALUES(20, 'N2');
007204 INSERT INTO SESSION.DEPTTMP VALUES(20, 'N3');
007205
007206 INSERT INTO SESSION.DEPTTMP VALUES(30, 'N4');
007207 INSERT INTO SESSION.DEPTTMP VALUES(30, 'N5');
007208 INSERT INTO SESSION.DEPTTMP VALUES(30, 'N6');
007209 END-EXEC.
007210
007220 INITIALIZE WS-TEMP-DEPT
007300* ***********************************************************
007400* OPENING ECURSOR
007500* ***********************************************************
007600 EXEC SQL
007700 OPEN DEPTCUR
007800 END-EXEC.
007900
008000 0000-START-PROGRAM.
008100 EXEC SQL
008200 WHENEVER SQLERROR
008300 GOTO 9000-DB-ERROR
008400 END-EXEC.
008500
008600 EXEC SQL
008700 WHENEVER SQLWARNING
008800 CONTINUE
008900 END-EXEC.
009000
009100 EXEC SQL
009200 WHENEVER NOT FOUND
009300 CONTINUE
009400 END-EXEC.
009500
009600
009700 PERFORM 0100-READ-PARA.
009800 GO TO 1000-END-PROGRAM.
009900
010000 0100-READ-PARA.
010100 PERFORM UNTIL END-OF-LOOP = 'Y'
010200 EXEC SQL
010300 FETCH DEPTCUR
010400 INTO :WS0-DEPT , :WS-NAME
010500 END-EXEC
010600 EVALUATE SQLCODE
010700 WHEN 0
010800 MOVE WS0-DEPT TO WS1-DEPT
010900 DISPLAY WS0-DEPT WS1-DEPT
011000 ADD 1 TO RECORD-COUNT
011100
011200 WHEN +100
011300 DISPLAY 'TOTAL NUMBER OF RECORDS = ' RECORD-COUNT
011400 MOVE 'Y' TO END-OF-LOOP
011500
011600 WHEN OTHER
011700 DISPLAY 'ERROR IN RETREVING DATA'
011800 DISPLAY 'SQLCODE = ' SQLCODE
011900
012000 END-EVALUATE
012100 END-PERFORM.
012200
012300 1000-END-PROGRAM.
012400 EXEC SQL
012500 CLOSE DEPTCUR
012600 END-EXEC.
012700 GOBACK.
012800
012900 9000-DB-ERROR.
013000 GO TO 1000-END-PROGRAM.
013100

Is my CURSOR declaration correct for GLOBAL TEMPORARY TABLE ?

Thank you,
Sushanth

sushanth bobby

Senior Member

Joined: 29 Jul 2008
Posts: 1020
Location: India

Hi,

Funny or Careless error i made. I moved the EXEC SQL & END-EXEC statements little bit to the right. Now the error iam getting is

Code:

E DSNHPARS LINE 61 COL 59 ILLEGAL SYMBOL ";". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <END-OF-STATEMENT>

I DSNHMAIN WARNINGS HAVE BEEN SUPPRESSED DUE TO LACK OF TABLE DECLARATIONS

Quote:

The Declaration statement is not for a temp table.

by dbzTHEdinosauer in this link
I commented the declare statement in the working-storage section.

Thank You,
Sushanth Bobby

sushanth bobby

Senior Member

Joined: 29 Jul 2008
Posts: 1020
Location: India

Hello Guyz,

I removed multiple insert statements in the program, i have no errors now.
Is it not possible to insert multiple rows into a temporary table in the same EXEC SQL statement ?

But, when i execute the program its taking too much of time

Code:

NP JOBNAME STEPNAME PROCSTEP JNUM C TYPE CPU% CPU TIME REAL EXCP CNT
HXSULLXX RUNPROGR 16643 L JOB 1.74 84.20 751 216

Still Running....

Actually it shouldn't take soo much time. Its having only one insert statement, fetch it, display it and close. That's it. But, its still running........

Thank You,
Sushanth Bobby

dbzTHEdinosauer

Global Moderator

Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

Quote:

Is it not possible to insert multiple rows into a temporary table in the same EXEC SQL statement ?

yes, but your problem is the syntax:
you can not have multiple INSERT statements in the same sql call.

sushanth bobby

Senior Member

Joined: 29 Jul 2008
Posts: 1020
Location: India

Dino,

Quote:

you can not have multiple INSERT statements in the same sql call.

0.k. So, got to look into other possibilities of inserting in a single statement like insert into session.depttmp select col1,col2 from another_table.

Thank You,
Sushanth Bobby

View previous topic :: :: View next topic

View Bookmarks
All times are GMT + 6 Hours

Forum Index -> DB2

Topic		Forum	Replies
Similar Topics
	Replace each space in cobol string wi...	COBOL Programming	3
	Error to read log with rexx	CLIST & REXX	11
	Load new table with Old unload - DB2	DB2	6
	COBOL -Linkage Section-Case Sensitive	COBOL Programming	1
	Error when install DB2	DB2	2

Search our Forums:

IBMMainframes.com is not an official and/or affiliated with IBM^® in anyway
Board Rules \| FAQ \| Downloads \| Wiki \| SiteMap \| Contact Us