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

    Unanswered: DB2 Package QUALIFIER() QUESTION

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    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 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

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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'
    ...
    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
    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

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Nov 2003
    Location
    Baltimore MD
    Posts
    21
    Originally posted by Marcus_A
    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

  8. #8
    Join Date
    Nov 2003
    Location
    Baltimore MD
    Posts
    21
    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

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Nov 2003
    Location
    Baltimore MD
    Posts
    21
    Originally posted by Marcus_A
    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

Posting Permissions

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