Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    Baltimore MD
    Posts
    21

    Unanswered: Describe Table? (z/os - Os390)

    I am running COBOL-DB2 batch (version 7.0 DB2) in Z/OS -OS390.

    I see very little documentation - in particular, illustrative examples, of the DESCRIBE TABLE syntax and what exactly gets populated to the SQLDA when the command is issued.

    In the following example (simplified from my current program which is failing), I have the following code:
    (assume table: ABCD.TABLEAA has 50 columns)

    EXEC SQL
    INCLUDE SQLDA
    END-EXEC.


    MOVE 'ABCD.TABLEAA' TO WS-HOST-TABLE

    EXEC SQL
    DESCRIBE TABLE :WS-HOST-TABLE INTO QLDA
    END-EXEC.

    DISPLAY 'NUM OF COLUMNS: ' SQLD

    PERFORM VARYING WS-SUB1 FROM 1 BY 1
    UNTIL WS-SUB1 > SQLD
    DISPLAY 'COL NAME: ' SQLNAME (WS-SUB1)
    END-PERFORM.
    ************************************************** ***
    In the above example, SQLD is accurately displaying 50 as the true number of columns. However, every occurence of SQLNAME is coming up with low-values. I have checked for bugs in the code but I cannot spot any. Does anyone see the cause of my failure ?

    regards and thanks in advance
    dcshnier

  2. #2
    Join Date
    Nov 2003
    Posts
    5
    A fairly good document on this subject from IBM is:
    "Squeezing the most out of Dynamic SQL"
    SG24-6418

  3. #3
    Join Date
    Nov 2003
    Location
    Baltimore MD
    Posts
    21
    Originally posted by parrisgeiser
    A fairly good document on this subject from IBM is:
    "Squeezing the most out of Dynamic SQL"
    SG24-6418
    Thanks for your feedback.
    However, I already have a copy of this (SG24-6418) manual and while it is an incomparable guide for Z/OS DB2 dynamic SQL development, it appears to not provide details on the 'DESCRIBE TABLE' command.
    Indeed in context with dynamic SQL development, it does talk about the 'DESCRIBE STATEMENT', 'DESCRIBE INPUT' and 'DESCRIBE PROCEDURE' command. But I see no reference to the 'DESCRIBE TABLE' command.
    I know this command exists in Z/OS DB2, because I was able to compile, and my program did produce some results.
    The command is documented on page 718 of the
    "DB2 Universal Database for OS/390 and z/OS
    SQL Reference Version 7 Publication No. SC26-9944-02".

    regards
    dcshnier

  4. #4
    Join Date
    Nov 2003
    Location
    Baltimore MD
    Posts
    21
    Originally posted by dcshnier
    Thanks for your feedback.
    However, I already have a copy of this (SG24-6418) manual and while it is an incomparable guide for Z/OS DB2 dynamic SQL development, it appears to not provide details on the 'DESCRIBE TABLE' command.
    Indeed in context with dynamic SQL development, it does talk about the 'DESCRIBE STATEMENT', 'DESCRIBE INPUT' and 'DESCRIBE PROCEDURE' command. But I see no reference to the 'DESCRIBE TABLE' command.
    I know this command exists in Z/OS DB2, because I was able to compile, and my program did produce some results.
    The command is documented on page 718 of the
    "DB2 Universal Database for OS/390 and z/OS
    SQL Reference Version 7 Publication No. SC26-9944-02".

    regards
    dcshnier
    Eureka !!!
    I figured out what I was doing wrong in the DESCRIBE TABLE statement.
    Before issuing the DESCRIBE statement, you first have to set the SQLN field (which is in the fixed portion of the SQLDA), to some non zero value which is <= 750 but at least as big as the maximum number of columns you expect to have in the table being described and not . In this case, I chose to move 750 into SQLN. In fact, when you look at the layout to the varying portion in the SQLDA, you see:
    SQLVAR OCCURS 0 TO 750 TIMES DEPENDING ON SQLN.
    Anyway after doing this, the DESCRIBE TABLE did return the meta-data into the SQLDA fields. This is good to know, because now I can get the meta-data, without having to open up a cursor against SYSIBM.SYSCOLUMNS.
    dcshnier

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •