PM11941: PROVIDE SERVICE SQL FUNCTIONALITY IN A DB2-SUPPLIED OPTIMIZATION SQL API STORED PROCEDURE

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • The administration SQL API stored procedure,
    SYSPROC.ADMIN_INFO_SQL, is used to collect customer environment
    information such as table, index, views, stats, plan table
    details, module details and zparms in order to provide the
    service team the documents to recreate a customer problem such
    as a badly performing SQL query.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: Users of DB2 9 for z/OS who need to send     *
    *                 environment re-creation information to the   *
    *                 DB2 service team when requested for a        *
    *                 problem related to DB2 query processing.     *
    ****************************************************************
    * PROBLEM DESCRIPTION: PROBLEM SCENARIO:                       *
    *                                                              *
    *                      Need a permanent DB2 tool to capture    *
    *                      the environment related to a            *
    *                      failed query or other DB2 error which   *
    *                      will be sent to the DB2 service team    *
    *                      for re-creation and diagnosis.          *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    PROBLEM SUMMARY:
    
    Need a permanent replacement for DB2PLI8 and the OSC Service SQL
    tool that collects user environment information as required
    to diagnosis problem queries.  This tool will now be part of
    the DB2 product and will be available any time when the
    DB2 service team requires re-creation documents.
    

Problem conclusion

Temporary fix

Comments

  • PROBLEM CONCLUSION:
    
    Introducing the SYSPROC.ADMIN_INFO_SQL stored procedure
    This PTF adds a new IBM supplied stored procedure called
    SYSPROC.ADMIN_INFO_SQL. The ADMIN_INFO_SQL stored procedure
    captures statistics about a DB2 subsystem, its objects, and
    applications and returns the results in a data set or as a
    result set. This information can help IBM Software Support
    recreate and troubleshoot problems, like a poor performing
    SQL query.
    
    Introducing DSNADMSB program
    This PTF also adds an IBM supplied program called DSNADMSB
    which can be called using JCL. This program calls the
    ADMIN_INFO_SQL stored procedure and can return the output
    in the job stream as a result set or to a data set.
    
    ===============================================================
    Deploying the SYSPROC.ADMIN_INFO_SQL stored procedure and
    DSNADMSB on DB2 for z/OS
    ================================================================
    
    Installation Job
    ----------------
    See ++HOLD text for installation information.
    
    WLM
    ---
    ADMIN_INFO_SQL must run in a WLM-established stored procedures
    address space, where NUMTCB is a value between 40 and 60.
    
    Authorization
    -------------
    To execute ADMIN_INFO_SQL and DSNADMSB, you must have the
    following DB2 privileges or authorities:
    
        * EXECUTE authority on plan DSNADMSB
        * One of the following privileges or authorities:
            o The EXECUTE privilege on the ADMIN_INFO_SQL stored
              procedure
            o Ownership of the ADMIN_INFO_SQL stored procedure
            o SYSADM authority
    In addition, if you direct ADMIN_INFO_SQL or DSNADMSB to write
    its output to data sets, you need to be authorized to
    perform either of the following tasks:
        * Create data sets
        * Write to already existing data sets
    
    Documentation
    -------------
    More detailed information can be found at the DB2 web site:
    http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp
    
    ================================================================
      Using the SYSPROC.ADMIN_INFO_SQL stored procedure and
      DSNADMSB on DB2 for Z/OS
    ================================================================
    
    Input parameters for ADMIN_INFO_SQL. NULLS are not allowed.
    -----------------------------------------------------------
    TABLE_CREATOR         VARCHAR(128)
    TABLE_NAME            VARCHAR(128)
    CATALOG_CREATOR       VARCHAR(128)
    PLAN_INFO             VARCHAR(150)
    COLLECT_DDL           CHAR(1)
    COLLECT_STATS         CHAR(1)
    COLLECT_COLUMN_STATS  CHAR(1)
    EDIT_DDL              CHAR(1)
    EDIT_VERSION_MODE     CHAR(4)
    PART_ROTATION         CHAR(1)
    OUTPUT_METHOD         CHAR(1)
    OUTPUT_INFO           VARCHAR(1024)
    PMR_INFO              VARCHAR(13)
    
    Return code and output messages
    -------------------------------
    RETURN CODE:
    Provides the return code from the stored procedure.  This is an
    output parameter of type INTEGER. Possible values are:
    
      0 - The call completed successfully.
      4 - Warning. The message output parameter contains messages
          describing the warning.
      12 - The call did not complete successfully. The message
           output parameter contains messages describing the error.
    
    
    OUTPUT MESSAGE:
    Provides the message(s) for the above return code. This is an
    output parameter of type VARCHAR(1331).
    
    Contains messages describing the error or warning encountered
    by the stored procedure. If no error occurred, the message
    states "DSNADMSS completed successfully."
    The first messages in this area are generated by the stored
    procedure. Messages that are generated by DB2 might follow
    the first messages.
    
    Output data
    -----------
    Output data is determined by the input parameters OUTPUT_METHOD
    and OUTPUT_INFO. It can be collected by setting OUTPUT_METHOD in
    one of the following ways:
    
      Q - Returns dynamically created data sets with size parameters
      N - Returns the result data sets in an already existing data
          set in the WLM environment.
      D - Returns dynamically created data sets on a volume that you
          specify.
      R - Returns a result set in a predetermined format
    
    Output data is split into several data sets or a single result
    set as follows:
      DDL - The creation statements for databases, table spaces,
            tables, and indexes.
      SQL - INSERT statements for PLAN_TABLE, DSN_PROFILE_TABLE,
            DSN_PROFILE_ATTRIBUTES, SYSACCELERATORS and
            SYSACCELIPLIST if the tables exist.
      STATS - Statistical information related to the tables.
      COLST - Statistical information related to the columns.
      EXPL - Visual output of the PLAN_TABLE, DSN_PREDICAT_TABLE,
             DSN_DETCOST_TABLE, DSN_PROFILE_TABLE,
             DSN_PROFILE_ATTRIBUTES, SYSACCELERATORS and
             SYSACCELIPLIST if the tables exist.
      PARM - Subsystem parameter, service, and module information
    
    Input parameter OUTPUT_INFO will need to also be set. Please see
    the ADMIN_INFO_SQL documentation for the correct format.
    
    More detailed information can be found at the DB2 website:
    http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp
    
    Note about disk space
    ---------------------
    Ensure that enough space is available for the output.
    ADMIN_INFO_SQL might generate large amounts of data. Two to
    three megabytes of space is the average, but larger workloads
    might generate up to twenty megabytes of data.
    
    To execute ADMIN_INFO_SQL using DSNADMSB
    ----------------------------------------
    Prepare a job for running DSNADMSB. The easiest way to do that
    is to customize a copy of sample job DSNTEJ6I, which is in data
    set prefix.SDSNSAMP. The job prolog has detailed instructions
    on how to customize the job.
    
    To execute ADMIN_INFO_SQL from Java, here is a code snippet
    -----------------------------------------------------------
    try
        {
          cstmt = conn.prepareCall("CALL SYSPROC.ADMIN_INFO_SQL(?,?,
                ?,?,?,?,?,?,?,?,?,?,?,?,?)");
                              // Create a CallableStatement object
          cstmt.setString (1, "SYSADM");
          cstmt.setString (2, "PLAN_TABLE");
          cstmt.setString (3, "DEFAULT");
          cstmt.setString (4, "APROGRAM-1-12345");
          cstmt.setString (5, "Y");
          cstmt.setString (6, "Y");
          cstmt.setString (7, "N");
          cstmt.setString (8, "Y");
          cstmt.setString (9, "NONE");
          cstmt.setString (10, "N");
          cstmt.setString (11, "R");
          cstmt.setString (12, "NONE");
          cstmt.setString (13, "12345.000.000");
                               // Set input parameters (DB2 command)
          cstmt.registerOutParameter (14, Types.INTEGER);
          cstmt.registerOutParameter (15, Types.VARCHAR);
                               // Register output parameters
    
          boolean resultsAvailable = cstmt.execute();
          rc = cstmt.getInt(14);  // Get the output parameter values
          errbuff = cstmt.getString(15);
    
          while (resultsAvailable)
          {
             ResultSet rs = cstmt.getResultSet();
             while (rs.next())
             {
                String s = rs.getString(3);
               System.out.println(s);
             }
             rs.close();
             resultsAvailable = cstmt.getMoreResults();
          }
        }
    
    To execute ADMIN_INFO_SQL from C, here is a code snippet
    --------------------------------------------------------
    EXEC SQL CALL SYSPROC.ADMIN_INFO_SQL ('SYSADM','PLAN_TABLE'
      ,'DEFAULT', 'APROGRAM-1-12345','Y','Y','N','Y','NONE','N','R',
      'NONE','12345.000.000',:out1,:out2);
      printf( "%d CALL SQLCODE\n", SQLCODE);
      printf( "%d CALL RC\n", out1);
      printf( "%s CALL DETAILS\n", out2);
    
      if(SQLCODE==+466)
      {
        EXEC SQL ASSOCIATE LOCATORS (:loc1) WITH
           PROCEDURE SYSPROC.ADMIN_INFO_SQL;
        printf( "%d ASSOC SQLCODE\n", SQLCODE);
    
        EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :loc1;
        printf( "%d ALLOC SQLCODE\n", SQLCODE);
    
        while(SQLCODE==0)
        {
           DATA.LNG = 0;
           SEQNO = 0;
           TID = 0;
    
           ind1 = -1;
           ind2 = -1;
           ind3 = -1;
    
           EXEC SQL FETCH C1 INTO :TID :ind1, :SEQNO :ind2,
             :DATA :ind3;
           memcpy(output, DATA.THEDATA, DATA.LNG);
           output??(DATA.LNG??) = '\0';
           printf( "%s\n", output);
        }
      }
      printf( "%d FETCH SQLCODE\n", SQLCODE);
    
    To execute ADMIN_INFO_SQL from a DB2 command window
    ---------------------------------------------------
    CALL SYSPROC.ADMIN_INFO_SQL('SYSADM','PLAN_TABLE','DEFAULT',
    'APROGRAM-1-12345','Y','Y','N','Y','NONE','N','R','NONE',
    '12345.000.000');
    
    Documentation
    -------------
    More detailed information can be found at the DB2 website:
    http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp
    
    
    ADDITIONAL KEYWORDS:
    SQLSP SQLSTOREDPROC ADMININFOSQL
     **** PE11/11/10 FIX IN ERROR. SEE APAR PM31303  FOR DESCRIPTION
    

APAR Information

  • APAR number

    PM11941

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-04-09

  • Closed date

    2011-02-03

  • Last modified date

    2013-11-20

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UK64557

Modules/Macros

  •    DSN@DMSB DSN@DMSS DSNADMSA DSNADMSB DSNADMSJ
    DSNADMSN DSNADMSO DSNADMSP DSNADMSS DSNTEJ6I DSNTIJSG DSNTINS1
    HDB9910J
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • R910 PSY UK64557

       UP11/02/18 P F102

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

910

Reference #:

PM11941

Modified date:

2013-11-20

Translate my page

Machine Translation

Content navigation