A fix is available
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
20 November 2013