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

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

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