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

Obtain the fix for this APAR.

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 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.