Like many shops, our DB2 packages are bound with a QUALIFIER parameter which specifies the creator prefix and allows for one to
refer to the tables within the COBOL-DB2 program itself, without a prefix qualifier. This allows one program to be used across test versus production, as well as other cross-database situations.

For example, sometimes the program will run from a package bound with QUALIFIER (OWNERAAA) and sometimes it will run from a package bound with QUALIFIER (OWNERBBB). Thus, any reference to 'TABLEX' within the program, might mean
OWNERAAA.TABLEX or OWNERBBB.TABLEX or whatever, depending on the governing package.

My question is:
From within a COBOL-DB2 program, can one issue a command which will allow the program to dynamically determine the governing qualifier under which one is currently executing ? Consequently, one would be able to display the fully qualified table name:
OWNERAAA.TABLEX or OWNERBBB.TABLEX

Granted ... I know that prepping the JCL with some passed paramater will provide a solution ...but I am looking for a more dynamic solution which does not involve prepping the JCL with anything.

thanks for any assistance on this.
regards
dcshnier

I am not sure what you are looking for, but you might look at SET CURRENT SQLID in the SQL Reference.

This will affect dynamically prepared SQL statements if DYNAMICRULES run behavior is in effect.

Thanks Marcus for your dedication to this forum.

Before I address whether or not 'SET CURRENT SQLID' will solve the dilemna ...I should give you more details ...

The program I am writing will be a called module which uses dynamic SQL to perform inserts on any table for any column. It will be robust enough to handle any table and column including ones which do not even exist at the moment. The calling module will pass an unqualified table-name and column values to the called module. The called module I am writing will initially perform a query against the SYSIBM.SYSCOLUMNS table to get the corresponding meta-data for the passed table. It will use the meta-data, to verify the accuracy of the passed value information, and reject the call, if the passed paramaters are inconsistent with the meta-data, and plug default values, for any column values not supplied in the call. Now, to effectively SELECT against SYSIBM.SYSCOLUMNS, I need to know the CREATOR value. This CREATOR value would be the same as the QUALIFIER(*) value contained in the package bind statements. This was the intent of my question. So are my thought processes within reason ? Or am I missing something ?
Regarding, 'SET CURRENT SQLID' - doesn't it have to be SET to something ? If yes ...then that would imply some value being hard-coded somewhere - and I am trying to avoid hard-coding anything like that, or demanding that the calling program even passes the CREATOR value as a parm.
I hope that adds fuel to the discussion.
regards
dcshnier

The CURRENT SQLID defaults to the authid used to execute the process. If the program contains static SQL that was bound, then the authid of the bind would be used for unqualified table names.

For dynamic SQL, the userid of the person who executes the program at run time would be used if the authid qualifier were not specified for a table. That would be the userid of a TSO job or the SIGNON id for CICS.

For a WHERE clause, when you need to explicitly specify the authid, I assume (but not 100% sure) that you can use CURRENT SQLID just like CURRENT DATE. For example:

SELECT * FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = CURRENT SQLID
AND TBNAME = 'XXXXXXXXX'
...

!! thanks Marcus !!
Your solution is certainly worth trying.
The actual meta-data query from SYSCOLUMNS will be static SQL - in that the passed table-name will be the plug value for a host-variable
(i.e. .... WHERE TBNAME=:WS-PASSED-TBNAME)
Now, based on your response, it is possible that the default value of
CURRENT SQLID is the OWNER(*) value for the package and not the QUALIFIER(*) value. (in which case, I have no solution).
I have not yet tested it because I am relatively new to this shop and they take a long time to grant DB2 access -even to test.
This also explains why I have the time to cruise useful DB2 forums such as this one. I am looking forward to trying it.
If this does not work - do you know if the name of the governing package is available to dynamically discover within the program ? If so, then I could get the QUALIFIER value from SYSIBM.SYSPACKAGE and then use that for my TBCREATOR value.
regards
dcshnier

You can bind the plan or package with either the QUALIFIER option, the OWNER option, or both.

– If the plan or package is bound with the QUALIFIER option only, the QUALIFIER is the owner of the object. The QUALIFIER option allows the binder to name a qualifier to use for all unqualified names of tables, views, indexes, aliases, or synonyms that appear in the plan or package.

– If the plan or package is bound with the OWNER option only, the OWNER is the owner of the object.

– If the plan or package is bound with both the QUALIFIER option and the OWNER option, the QUALIFIER is the owner of the object.

– If neither option is specified, the binder of the plan or package is implicitly the object owner.

thanks Marcus for your re-assuring advice.

Indeed, we bind with both the OWNER and QUALIFIER options - and from what you write, TBCREATOR=CURRENT SQLID will do the trick.

I am looking forward to seeing this thru.
regards and I will keep you posted.
dcshnier

Hi Marcus
Just to give you an update. I got together with a colleague at work here who already has DB2 access. We ran a little program which was tied to a plan and package that had been bound with QUALIFIER(*) and
OWNER(*) statements. For illustrative sake, let's assume that the QUALIFIER was set to 'PREFIXA' and the owner was set to 'PREFIXB'. Let's also assume that my colleague's TSO ident is 'TSOAAAA'.
We selected CURRENT SQLID from SYSIBM.SYSDUMMY1 and displayed it. Unfortunately the value displayed was 'TSOAAAA' and not 'PREFIXA' nor 'PREFIXB'. We did verify in SYSPACKAGE and SYSPLAN that the corresponding plan and package had the QUALIFIER and OWNER respectively set to 'PREFIXA' and 'PREFIXB'.
Nevertheless, CURRENT SQLID is not rendering either of these values.
Unless I miss-understand the experiment (or miss-applied it) ... well its back to the drawing board.
keep you posted
dcshnier

That is correct. The CURRENT SQLID is the authid of the executor of the package unless changed by a program.

You might look at the DESCRIBE TABLE statement to see if that helps.

Actually before you responded, I had decided to explore the DESCRIBE TABLE command and the resulting SQLDA.
Unfortunately, I lack the authority to run my experimental program to reveal what's in the SQLDA. The powers at be here, who grant authority are gone for the holidays, and won't return till sometime next week ( or maybe not till 2004 ).

Do you know if the DESCRIBE table command can be issued interactively?

regards
dcshnier