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

    Unanswered: Systables 'obid' Z/os Cobol-db2 ??

    We are running DB2 version 7, in Z/OS - and this question pertains to the COBOL-DB2 program I am writing.

    If I issue a particular SQL action against an unqualified DB2 table, does that table's 'OBID' (object identifier value located in SYSIBM.SYSTABLES) get generated and stored anywhere apart from SYSIBM.SYSTABLES ? (i.e. in the SQLCA or SQLDA, or some other register or misc storage area) ? If yes, is it available for the COBOL program to retrieve it from that other area ?

    regards and thanks in advance. (happy 2004)
    dcshnier

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't believe that it is stored anywhere a COBOL program can access it besides the DB2 catalog. It is stored in image copies (and can be retrieved with certain utilities), but I don't think that will help you.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2003
    Location
    Baltimore MD
    Posts
    21
    Originally posted by Marcus_A
    I don't believe that it is stored anywhere a COBOL program can access it besides the DB2 catalog. It is stored in image copies (and can be retrieved with certain utilities), but I don't think that will help you.
    thanks Marcus for your reply.

    What about DSNTIAR ?? When this IBM utility program is called from a COBOL program (using the SQLCA), does it either receive from ... or send back to, the calling COBOL program either the OBID, and/or the fully qualified table name ?

    regards
    dcshnier

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It has been a while since I used DSNTIAR, and I don't recall if the OBID is returned. Remember than an SQL statement can contain multiple tables. I believe that the table name is returned if the table is the object in error (as opposed to some other object such as a constraint name).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Nov 2003
    Location
    Baltimore MD
    Posts
    21
    Originally posted by Marcus_A
    It has been a while since I used DSNTIAR, and I don't recall if the OBID is returned. Remember than an SQL statement can contain multiple tables. I believe that the table name is returned if the table is the object in error (as opposed to some other object such as a constraint name).
    To be honest, when I came across this need to expessively know the table prefix (creator) from within the COBOL program, I was greatly surprised that there was no easy way to dynamically derive it from some register. Why was I surprised ? - because there are several times when this is needed for reporting purposes and/or dynamic SQL purposes and/or meta-data purposes. And I did not want to resort to having to demand that a hard-coded parm be passed to the program. So I thought of rigging the program to force an innocuous error using the table as the object - and then calling DSNTIAR in order to see if it would return a fully qualified table name in the message. Alternatively, I could open up the TSO command data-set in the SYSTSIN dd, and read through that in order to find the name of the governing plan/package, and once retrieved, I would then query SYSIBM.SYSPACKAGE. However both solutions - even if they work, are kind of 'dorky' and probably worthy of making the chapter on
    'stupid COBOL-DB2 programmer tricks'.
    I have also logged this question with IBM and other DB2 experts across the net - if they do not respond, then, I will have to swallow my pride and try the 'dorky' approach.

    regards
    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
  •