SQLcode -206 and -501

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
Anja
Member
Posts: 6
Joined: Sun Mar 23, 2014 5:16 am
Location: Turkey

SQLcode -206 and -501

Post by Anja » Sun Mar 23, 2014 5:50 am

I need help with this code. I am new to COBOL programming and SQL. I am want to use cursor to get information from two tables from the database. But I keep gettingt the sqlcode -206 and -501. I cannot see what the problem is in my code.
Can someone please explain to me why I cannot open and fetch?


Code: Select all

      WORKING-STORAGE SECTION.

       01 CHOICE          PIC 9 VALUE ZERO.
       01 NUM           pic x(4).


           EXEC SQL
               INCLUDE SQLCA
           END-EXEC.

           EXEC SQL
               INCLUDE EMPLOYEE
            END-EXEC.

           EXEC SQL
               INCLUDE DEPT
           END-EXEC.

           EXEC SQL
               DECLARE CUR-EMP CURSOR FOR
               SELECT DEPT.DEPT,
               DEPT.DNAME,
               DEPT.MGR,
               EMPLOYEE.E_NO,
               EMPLOYEE.LNAMN,
               EMPLOYEE.FNAMN,
               EMPLOYEE.DEPT
               FROM DEPT,EMPLOYEE
               WHERE DEPT.DEPT=EMPLOYEE.DEPT
               END-EXEC.

      **************************
       PROCEDURE DIVISION.
       A000-INIT.

           PERFORM A100-START
           PERFORM B100-PROCESS UNTIL CHOICE = 9
           PERFORM C100-STOP.
       .

      *------------------------------------------
       A100-START.

      *OPEN CURSOR EMPLOYEE
           EXEC SQL
               OPEN CUR-EMP
           END-EXEC.
           PERFORM X100-CHECK-SQL.
        .
      *------------------------------------------
      *FIRST FETCH

       B100-PROCESS.
           EXEC SQL
           FETCH CUR-EMP INTO
               :DEPT-DEPT,
               :DEPT-DNAME,
               :DEPT-MGR,
               :EMPLOYEE-E-NO,
               :EMPLOYEE-LNAME,
               :EMPLOYEE-FNAME,
               :EMPLOYEE-DEPT
           END-EXEC
           PERFORM X100-CHECK-SQL.

      *-------------------------------------------------
           DISPLAY 'PRESS 1, EMPLOYEE LIST'
           DISPLAY 'TRYCK 9, TO STOP'
           ACCEPT  CHOICE

       EVALUATE CHOICE
           WHEN = 1 PERFORM A200-GET-INFO
           WHEN = 9 PERFORM C100-STOP
      *    WHEN OTHER
      *    DISPLAY 'WRONG'
       END-EVALUATE
       .
      ***************************
       A200-GET-INFO.

           DISPLAY 'ENTER YOUR ID'.
           DISPLAY 'PRESS A TO STOP'.
           ACCEPT NUM.

      *SECOND FETCH
           EXEC SQL
           FETCH CUR-EMP INTO
               :DEPT-DEPT,
               :DEPT-DNAME,
               :DEPT-MGR,
               :EMPLOYEE-E-NO,
               :EMPLOYEE-LNAME,
               :EMPLOYEE-FNAME,
               :EMPLOYEE-DEPT
           END-EXEC
           PERFORM X100-CHECK-SQL.

           DISPLAY' '
           DISPLAY 'EMLPOYEE NO: 'employee-e-no
           DISPLAY' LAST NAME:'employee-lname
           DISPLAY' first NAMN:'employee-fname
           display 'DEPARTMENT: ' dept-dept
           display 'THE BOSS NAME:' dept-mgr
           DISPLAY' '
        .
      *-------------------------------------------

       X100-CHECK-SQL.
           EVALUATE SQLCODE

               WHEN 100 DISPLAY 'DATA FINISHED'
               WHEN <0  DISPLAY 'WRONG SQLCODE =' SQLCODE
               WHEN 0   DISPLAY 'OK'SQLCODE
               WHEN >0  DISPLAY 'SQL WARNING' SQLCODE
           END-EVALUATE.
      *----------------------------------------------------
       C100-STOP.
           EXEC SQL
               CLOSE cur-emp
           END-EXEC.
           PERFORM X100-CHECK-SQL.

       STOP RUN.
Code'd[/size]

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Sun Mar 23, 2014 7:39 am

You need to download and have the DB2 documentation handy.
-206 column-name IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR
ANY TABLE IDENTIFIED IN A FROM CLAUSE, OR IS NOT A COLUMN OF THE
TRIGGERING TABLE OF A TRIGGER
-501 THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN
Which SQL code was returned on the OPEN? You need to fix the OPEN before you continue.

Something that usually helps is to compare your problem code against a similar piece of code that already works on the system.
Have a good one

Anja
Member
Posts: 6
Joined: Sun Mar 23, 2014 5:16 am
Location: Turkey

Post by Anja » Sun Mar 23, 2014 4:26 pm

Thanks for your quick reply.

Which SQL code was returned on the OPEN? You need to fix the OPEN before you continue.
-206 returned on OPEN. I do not know how to fix it. I have compared the code with another code that works and they look the same.

NicC
Active Member
Posts: 650
Joined: Sun Jul 24, 2011 5:27 pm
Location: Down on the pig farm

Post by NicC » Sun Mar 23, 2014 4:43 pm

Possibly your cursor is declared wrongly? One of these is possibly at fault:

Code: Select all

DEPT.DEPT,
DEPT.DNAME,
DEPT.MGR,
EMPLOYEE.E_NO,
EMPLOYEE.LNAMN,
EMPLOYEE.FNAMN,
EMPLOYEE.DEPT
And, please use the code tags when posting code, data, JCL, scrren cut'n'pastes and the quote tags when quoting someone. To find out more about these use the 'Preview' button instead of the 'Submit' button and you will get to the advanced editor where there are buttons for these functions.
Regards
Nic

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Sun Mar 23, 2014 11:16 pm

Which column is shown to be a problem in the message?
Have a good one

Anja
Member
Posts: 6
Joined: Sun Mar 23, 2014 5:16 am
Location: Turkey

Post by Anja » Mon Mar 24, 2014 2:16 am

Possibly your cursor is declared wrongly? One of these is possibly at fault:
Code:
DEPT.DEPT,
DEPT.DNAME,
DEPT.MGR,
EMPLOYEE.E_NO,
EMPLOYEE.LNAMN,
EMPLOYEE.FNAMN,
EMPLOYEE.DEPT
Sorry, I am very new to this.
My code is working now. I Changed the declaration in copybook. Thanks!

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Mon Mar 24, 2014 4:29 am

Good to hear it is working - thank you for letting us know :)

We were all new at one time - not to worry :wink:

It appears that when you post you have disabled bbCodes in your posts. If you uncheck this, your tags will work.
Have a good one

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others