Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

COBOL DB2 using Temporary table, Error while compiling

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Mar 31, 2009 4:02 pm    Post subject: COBOL DB2 using Temporary table, Error while compiling
Reply with quote

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
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Mar 31, 2009 10:33 pm    Post subject:
Reply with quote

Declare SESSION.DEPTTMP in procedure division along with the INSERT stmts ..
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Apr 01, 2009 11:27 am    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Apr 01, 2009 3:24 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Apr 01, 2009 5:14 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Apr 01, 2009 5:19 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Apr 01, 2009 5:30 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 16 Thu Jul 13, 2017 2:11 pm
No new posts XML Parse for COBOL 5.2 Bhanu Praveen COBOL Programming 1 Sat Jul 08, 2017 8:58 pm
No new posts Dynamic output file creation in cobol... smileheal COBOL Programming 7 Thu Jun 15, 2017 10:53 pm
No new posts Search utility(3.14) using JCl and Cobol RKS3 COBOL Programming 12 Sat Jun 10, 2017 10:24 pm
No new posts Compiling program in endeavor for tra... RALAKKAL Compuware & Other Tools 4 Mon Jun 05, 2017 8:27 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us