Introducing SYSPROC.ADMIN_INFO_SQL

A new way to capture service information on DB2 for z/OS

When you experience a problem with one of your IBM® DB2® for z/OS® queries that you need to report to IBM Software Support, the most critical step in resolving the problem is to capture the query environment, which includes tables, indexes, views, statistics, EXPLAIN output, module levels, and subsystem parameters. This captured information is especially useful to IBM Software Support in diagnosing performance problems, such as SQL query access path changes. In this article, gain an understanding of the new DB2-supplied stored procedure SYSPROC.ADMIN_INFO_SQL, which enables application vendors, tool vendors, and customers to quickly and simply capture a query environment.

Michael Schenker (mschenk@us.ibm.com), Advisory Software Engineer, IBM

Michael Schenker photoMichael Schenker has more than seven years of work experience with IBM, joining the company as a full-time employee at the end of 2003. During his career, Michael has worked on several database-related products, starting as an intern to work on DB2 Information Integration with focus on non-relational data sources. In 2006, Michael joined the database tools organization and worked on the DB2 web service provider functionality in IBM Data Studio Developer. He gained a lot of expertise in SOA and web technologies during that time. Since 2008, Michael has been part of the DB2 for z/OS optimizer team, working on access plan selection and optimizer testing tools.



Thomas Conrad (conrad@us.ibm.com), Senior Software Engineer, IBM

Thomas Conrad photoTom Conrad has worked for IBM for more than 30 years as an application developer, primarily creating database-related applications, supporting manufacturing process control and data transport across multiple platforms such as z/OS, z/VM, Windows, UNIX, and Linux. He joined the DB2 for z/OS organization two years ago and has been working on product support, as well as development of DB2-supported stored procedures.



09 December 2010

A short introduction to ADMIN_INFO_SQL

The SYSPROC.ADMIN_INFO_SQL stored procedure simplifies the process of collecting service data about SQL statements and database objects. ADMIN_INFO_SQL can help IBM Software Support to more quickly resolve your DB2 for z/OS problems. ADMIN_INFO_SQL is available with DB2 10 for z/OS and DB2 9.1 for z/OS (APAR PM31303 - V10 PTF UK67425 and V9 PTF UK67426 ).

ADMIN_INFO_SQL can be invoked from application programs on z/OS or from client applications that support DRDA. A z/OS program that calls ADMIN_INFO_SQL (DSNADMSB) and sample JCL for invoking that program (DSNTEJ6I) are also available, so that you can invoke ADMIN_INFO_SQL without writing a calling program.


Article overview

This article introduces the new SYSPROC.ADMIN_INFO_SQL stored procedure. In this article, you will learn about:

  • Installing ADMIN_INFO_SQL and DSNADMSB
    • Location of data sets and members
    • WLM environment setup
    • Program preparation for ADMIN_INFO_SQL and DSNADMSB
    • Authorization
  • ADMIN_INFO_SQL input and output
    • Input and output parameters
    • Format of the LIST_TABLE table
    • Generated JCL jobs
  • Scenarios for using ADMIN_INFO_SQL
    • Capture service data for SQL statements
    • Cloning database objects and statistics
  • Ways of invoking ADMIN_INFO_SQL
    • Call from a Java application
    • Call from a DB2 CLI application
    • Call from the DB2 for Linux, Unix, and Windows command line processor (CLP)
  • Problem determination using tracing

Install ADMIN_INFO_SQL

CREATE PROCEDURE and BIND commands

DSNTIJRT will create the procedure and bind the DBRM. You can find the CREATE PROCEDURE statement and the BIND command in DB2HLQ.SDSNSAMP(DSNTESR).

Stored procedure ADMIN_INFO_SQL, calling program DSNADMSB, and sample JCL job DSNTEJ6I are shipped with DB2 10 for z/OS, and as a PTF for DB2 9.1 for z/OS.

In DB2 10 for z/OS, you can use job DSNTIJRT to install ADMIN_INFO_SQL and job DSNTIJRV to verify its installation. Use job DSNTIJSG to bind the package and plan for DSNADMSB. You may also run DSNTIJRW to create the default WLM environments for the DB2-provided stored procedures.

In DB2 9.1 for z/OS, after you apply the PTF, follow the post-apply instructions that are provided with the PTF.

Table 1. DB2 10 for z/OS installation jobs and location
Data setDescription
DB2HLQ.SDSNSAMP(DSNTIJRW)Defines the core DB2 WLM environments.
DB2HLQ.SDSNSAMP(DSNTIJRT)Installs and configures DB2-supplied routines, including the ADMIN_INFO_SQL stored procedure.
DB2HLQ.SDSNSAMP(DSNTIJRV)Verifies the DB2-supplied routines, including the ADMIN_INFO_SQL stored procedure.
DB2HLQ.SDSNSAMP(DSNTIJSG)Creates user-defined tables, binds DB2-supplied programs, including DSNADMSB, and more.
Table 2. Data set and member names for ADMIN_INFO_SQL artifacts
Data setDescription
DB2HLQ.SDSNLOAD(DSNADMSS)The load module for the ADMIN_INFO_SQL procedure.
DB2HLQ.SDSNDBRM(DSNADMSS)The DBRM for the ADMIN_INFO_SQL procedure.
DB2HLQ.SDSNLOAD(DSNADMSB)A program to invoke the ADMIN_INFO_SQL procedure.
DB2HLQ.SDSNDBRM(DSNADMSB)The DBRM for DSNADMSB.
DB2HLQ.SDSNSAMP(DSNTEJ6I)A sample JCL to invoke DSNADMSB.

WLM requirements

ADMIN_INFO_SQL has no special WLM requirements, but functions best in a WLM environment that uses a NUMTCB setting of 40 - 60.

In DB2 10 for z/OS, by default, ADMIN_INFO_SQL runs in the DSNWLM_GENERAL WLM environment.

Authorization

ADMIN_INFO_SQL requires the following authorizations:

  • Execute privilege on the package.
  • (Optional) Ability to create data sets if information is collected into a data set.
  • (Optional) Access to already-created data sets if information is collected into a data set.

ADMIN_INFO_SQL input and output

ADMIN_INFO_SQL requires several input and output parameters. In addition, ADMIN_INFO_SQL returns jobs for processing collected data. When you call ADMIN_INFO_SQL, you can specify whether those jobs are returned in a stored procedure result set or are written in z/OS data sets.

Table 3 describes ADMIN_INFO_SQL parameters. None of the parameters can be NULL.

Table 3. Input and output parameters
ModeNameTypeDescription
INTABLE_CREATORVARCHAR(128)<creator or schema>

Explicit qualifier for:
  • The table - in case service data is collected for a single table.
  • The object list table - in case service data is collected for multiple objects.
  • The PLAN_TABLE - in case service data is collected for SQL statements.
INTABLE_NAMEVARCHAR(128)<table name or LIST_TABLE-table or PLAN_TABLE>

The name of:
  • A single base table - in case service data is collected for a single table.
  • The list table prefixed with LIST_TABLE-. The list table contains two columns, creator and table. These columns will drive the program to collect information.
    Example: If the list table is called OBJ_TABLE, the value to provide is LIST_TABLE-OBJ_TABLE. The "Clone database objects and statistics" section demonstrates the usage of LIST_TABLE on a complete example.
  • The PLAN_TABLE - in case service data is collected for SQL statements. This option uses the PLAN_INFO parameter (below).
INCATALOG_CREATORVARCHAR(128)<catalog name or DEFAULT>

The qualifier for the DB2 catalog tables from which data is collected, or DEFAULT. If you specify DEFAULT, the qualifier is SYSIBM.
INPLAN_INFOVARCHAR(150)<program-bquery-equery or NONE>

If TABLE_NAME is PLAN_TABLE, this value has the following form:
  • <program> - a pattern that identifies the values in the PROGNAME column of the PLAN_TABLE for which data is to be collected. <program> must be a valid pattern expression for an SQL LIKE predicate.
  • <bquery> - the lowest value of the QUERYNO column of the PLAN_TABLE for which data is to be collected.
  • <equery> - the highest value of the QUERYNO column of the PLAN_TABLE for which data is to be collected.
Example: Use all queries with a query number between 0 and 1000 associated with a program that fits the pattern 'APPL%': APPL%-0-1000. If only a single, non-PLAN_TABLE table is requested or the list function is used, then this should be set to NONE. The "Collect service data for SQL statements using EXPLAIN" section demonstrates how to use the PLAN_INFO pattern on a complete example.
INCOLLECT_DDLCHAR(1) <Y or N>

Specifies whether ADMIN_INFO_SQL returns the data definition language statements that can be used to create all objects that are related to the tables that are specified by TABLE_NAME.
INCOLLECT_STATSCHAR(1) <Y or N>

Specifies whether ADMIN_INFO_SQL returns statistical information from DB2 catalog tables about the tables that are specified by table-name and related objects.
INCOLLECT_COLSTATSCHAR(1) <Y or N>

Specifies whether ADMIN_INFO_SQL returns statistical information from DB2 catalog tables about the columns in tables that are specified by table-name and related objects. If COLLECT_COLSTATS is Y, COLLECT_STATS must also be Y.

Note: Due to the large size, it is recommended to only collect column statistics if IBM Software support is requesting the data. N should be used as the default value.
INEDIT_DDLCHAR(1) <Y or N>

Specifies whether ADMIN_INFO_SQL modifies the data definition language statements that it generates so that the data definition language statements can be more easily executed by IBM Software Support. Examples of changes that ADMIN_INFO_SQL makes include:
  • Setting the STOGROUP to SYSDEFLT
  • Setting PRIQTY and SECQTY to their minimum values
  • Setting DEFINE to NO
  • Commenting out foreign key definitions
Use Y if you are sending the files to IBM Service, unless directed otherwise by IBM.
INEDIT_VERSION_MODECHAR(4) <9-C,9-N,10-C,10-N>or<NONE>

Specifies whether ADMIN_INFO_SQL modifies the output that it produces so that it runs on a different version of DB2 for z/OS from the version of the subsystem from which the data was collected. The meanings of the values are:
  • 9-C - Modify the output for DB2, Version 9.1, for z/OS conversion mode.
  • 9-N - Modify the output for DB2, Version 9.1, for z/OS new-function mode.
  • 10-C - Modify the output for DB2 10 for z/OS conversion mode.
  • 10-N - Modify the output for DB2 10 for z/OS new-function mode.
  • NONE - Do not modify the output.
INPART_ROTATIONCHAR(1)<Y or N>

Specifies whether ADMIN_INFO_SQL checks the amount of rotation that a partitioned table has undergone, and determines the number of partition rotations that are needed to synchronize logical partitions with physical partitions.
INOUTPUT_METHODCHAR(1)<Q, N, D, R>

See Table 5. Output data set options for different options.
INOUTPUT_INFOVARCHAR(1024)Depends on the value provided for OUTPUT_METHOD. See Table 5. Output data set options for different options.
  • OUTPUT_METHOD=Q
    <qualifier(DEFAULT)-primary(value or DEFLT)-secondary(value or DEFLT)>
  • OUTPUT_METHOD=N
    <DDL_DDname-SQL_DDname-Stats_DDname-Colst_DDname-Expl_DDname-Parm_DDname> for established data sets in the WLM environment.
  • OUTPUT_METHOD=D
    < -'DDL';DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary-'SQL';DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary
    -'STATS';DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary
    -'COLST';DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary
    -'EXPL';DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary
    -'PARM';DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary>
  • OUTPUT_METHOD=R
    <NONE(for result set)>
INPMR_INFOVARCHAR(13)<xxxxx.xxx.xxx>

Specifies the PMR number for the problem for which data is being collected in the form pmr-number.branch-code.country-code.
OUTRETURN_CODEINTEGERThe results of ADMIN_INFO_SQL execution. One of the following values is returned:
  • 0: Execution was successful. The MESSAGE parameter value is NULL.
  • 4: Execution completed with warnings. See the MESSAGE parameter value for more information.
  • 12: Execution completed with errors. See the MESSAGE parameter value for more information.
OUTMESSAGEVARCHAR(1331)If RETURN_CODE is not 0, this field contains an explanation of the warnings or errors that occurred during ADMIN_INFO_SQL execution.

Output information and options

The output results can be collected:

  • As a single result set with results in the job stream (OUTPUT_METHOD=R).
  • In an already-created data set in the WLM environment (OUTPUT_METHOD=N).
  • As a data set qualifier can be provided to dynamically create the data sets with size parameters to the temporary storage (OUTPUT_METHOD=Q).
  • To dynamically create the data sets on a volume of your choice (OUTPUT_METHOD=D).

All data sets will be created regardless of the data being collected. For example, the stats table will be created even if the collect stats method is off.

Table 4 shows the information that the output data sets and result sets will contain.

Table 4. Contents of output data sets and result sets
TypeResult set type (TID column)Description
DDL1The creation statements for databases, table spaces, tables, indexes, views, and so on.
SQL2Insert statements for PLAN_TABLE, DSN_PROFILE_TABLE, DSN_PROFILE_ATTRIBUTES, SYSACCELERATORS, and SYSACCELIPLIST, if they exist.
STATS3Statistical information related to the tables as insert, update, and delete statements ready to be loaded into a DB2 for z/OS catalog.
COLST4Statistical information related to the columns, insert, update, and delete statements ready to be loaded into a DB2 for z/OS catalog.
EXPL5Visual output of the PLAN_TABLE, DSN_PREDICAT_TABLE, DSN_DETCOST_TABLE, DSN_PROFILE_TABLE, DSN_PROFILE_ATTRIBUTES, SYSACCELERATORS, and SYSACCELIPLIST, if they exist.
PARM6Service and module information, including ZPARMs.
Table 5. Output data set options
OUTPUT_METHOD parameter valueData set options (OUTPUT_INFO parameter)
Q<qualifier(DEFAULT)-primary(value or DEFLT)-secondary(value or DEFLT)>

The user supplies a 29-character qualifier, including periods. The stored procedure will append one of the following types, depending on the file:
  • .DDL
  • .SQL
  • .STATS
  • .COLST
  • .EXPL
  • .PARM

The DEFAULT value creates a data set name with the following format:
PMxxxxx.Dxxxxxx.Txxxxxx.Vx.Type

So for pmr 12345, the following files are generated:
  • PM12345.D091007.T170318.V9.COLST
  • PM12345.D091007.T170318.V9.DDL
  • PM12345.D091007.T170318.V9.EXPL
  • PM12345.D091007.T170318.V9.PARM
  • PM12345.D091007.T170318.V9.SQL
  • PM12345.D091007.T170318.V9.STATS
The user can supply a primary and secondary track value.

Note: Units are in tracks. The default value is 200 primary and 200 secondary. This data set is created as a temporary file on temporary storage. The file might get deleted in a short period of time, depending on how the Z/OS system is configured. These data sets are created as new, and old data sets with the same name are deleted.
NFor established data sets in the WLM environment, the supplied DD name should be entered in the following format:
<DDL_DDname-SQL_DDname-Stats_DDname-Colst_DDname-Expl_DDname-Parm_DDname>

WLM administrator will have to set up the data sets with DD names in the WLM start procedure and supply those names to the caller of the procedure. These data sets can be generational. Data sets should be created as new and not appended to. The stored procedure will open the data set at initialization and close the file when completed.
DHas the following format:
<'DDL';DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary -
'SQL';DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary -
'STATS';DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary -
'COLST';DSnameDEFAULT);volser;alcunit(TRK or CYL);primary;secondary -
'EXPL';DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary -
'PARM';DSname(DEFAULT);volser;alcunit(TRK or CYL);primary;secondary>

This output allows the user to control exactly where the data is kept (volser), the name of the data set (DSname) or a default name, and the units/size of the data set.

The default name value creates a data set name with the following format:
PMxxxxx.Dxxxxxx.Txxxxxx.Vx.Type

So for pmr 12345, the following files are generated:
  • PM12345.D091007.T170318.V9.COLST
  • PM12345.D091007.T170318.V9.DDL
  • PM12345.D091007.T170318.V9.EXPL
  • PM12345.D091007.T170318.V9.PARM
  • PM12345.D091007.T170318.V9.SQL
  • PM12345.D091007.T170318.V9.STATS
The data set name should always contain the identifier('DDL', 'SQL', 'STATS,' and so one) for each data set, as shown above. As a result, the data sets can be listed in any order. The data sets are created with a disp=(NEW,CATLG,KEEP), so if there is an existing data set with the same name, DSNADMSB will produce an error.

The data sets have the following format:
  • DDL - recfm = FB, lrecl = 80
  • SQL - recfm = FB, lrecl = 80
  • STATS - recfm = FB, lrecl = 80
  • COLST - recfm = FB, lrecl = 80
  • EXPL - recfm = FB, lrecl = 4096
  • PARM - recfm = FB, lrecl = 1024
ROption R will return the output as a result set to the caller, instead of writing it into data sets. The result set contains three columns:
  • TID (INTEGER) - the output type (see Table 4)
  • SEQNO (INTEGER) - the position of the data in the result
  • TEXT (VARCHAR(4096)) - one line of the output data

Collect service data for SQL statements using EXPLAIN

Figure 1. Capturing service data from SQL statements
Diagram shows output from query leading to PLAN_TABLE using EXPLAIN, which leads to the ADMIN_INFO_SQL procedure. Output from the procedure is DDL, SQL, STATS, COLST, EXPL, and PARM.

Suppose that the performance of an application called FINANCE suddenly degrades significantly. Your research indicates that a sequence of queries in the application are likely to be causing the problem. IBM Software Support opened PMR 11111.000.000 and has requested additional information about those queries. They ask you to run the ADMIN_INFO_SQL stored procedure to collect data. The FINANCE application is a dynamic SQL application (with no static SQL packages), but you have run traces for IFCIDs 316, 317, or 124 that give you the statement IDs for the problem queries, or you retrieved the statement text from the application programmer. The statement IDs range from 4907080 to 4907095.

Step 1: Explain your statements

Diagnosis of the FINANCE performance problem requires PLAN_TABLE output. You need to populate the PLAN_TABLE, which is one of the explain tables. Suppose that the explain tables exist on your system, with the qualifier DB2ADM. Execute EXPLAIN statements, like these in Listings 1 and 2, to populate the explain tables:

Listing 1. Execute EXPLAIN using the statement text
SET CURRENT SQLID = 'DB2ADM';
EXPLAIN PLAN SET QUERYNO = 4907080 FOR
  SELECT … FROM TABLE1, TABLE2 WHERE …
... 
EXPLAIN PLAN SET QUERYNO = 4907095 FOR
  SELECT … FROM TABLE12 WHERE … ORDER BY …
Listing 2. Execute EXPLAIN for statements in the statement cache
SET CURRENT SQLID = 'DB2ADM';
EXPLAIN STMTCACHE STMTID 4907080;
... 
EXPLAIN STMTCACHE STMTID 4907095;

You might need to be authorized to execute EXPLAIN before you can execute these statements.

Step 2: Invoke ADMIN_INFO_SQL

Invoke ADMIN_INFO_SQL with the parameter settings listed in Table 6.

Table 6. Parameter settings
Parameter nameParameter value
TABLE_CREATORDSNADM
TABLE_NAMEPLAN_TABLE
CATALOG_CREATORDEFAULT
PLAN_INFOFINANCE%-4907080-4907095
COLLECT_DDLY
COLLECT_STATSY
COLLECT_COLSTATSY
EDIT_DDLY
EDIT_VERSION_MODENONE
PART_ROTATIONN
OUTPUT_METHODR
OUTPUT_INFONONE
PMR_INFO11111.000.000

Listing 3 illustrates the CALL statement.

Listing 3. CALL statement
CALL SYSPROC.ADMIN_INFO_SQL(
   'DB2ADM', 'PLAN_TABLE',
   'DEFAULT',
   'FINANCE%-4907080-4907095',
   'Y', 'Y', 'Y',
   'Y',
   'NONE',
   'N',
   'R',
   'NONE',
   '11111.000.000',
   ?,
   ?
);

Clone database objects and statistics

Figure 2. Cloning database objects using a list table
Diagram shows insert statement leading to LIST_TABLE, which leads to ADMIN_INFO_SQL procedure. Output from the procedure is DDL, SQL, STATS, COLST, EXPL, and PARM.

You have objects on a development system that you want to recreate on a test system, but the data definition language statements that created those objects are not readily available. All of the tables that you want to recreate have a schema of the form 'WDA%'. You can use ADMIN_INFO_SQL to recreate the data definition statements for you. You can also use ADMIN_INFO_SQL to retrieve statistical information about those tables from the DB2 catalog on the development system, which you can propagate to the test system.

Step 1: Create a list table

You can recreate all the tables with one invocation of ADMIN_INFO_SQL, if you use a list table as input. For example, as shown in Listing 4, you can create a declared temporary table with an SQL statement.

Listing 4. Create a declared temporary table with an SQL statement
DECLARE GLOBAL TEMPORARY TABLE SESSION.OBJ_TABLE (
        CREATOR VARCHAR(128),
        TABLE VARCHAR(128)
) ON COMMIT PRESERVE ROWS;

Step 2: Populate the list table

Populate the list table by retrieving the relevant information from the SYSIBM.SYSTABLES table and inserting the retrieved rows into the list table, as shown in Listing 5:

Listing 5. Populate the list table
SELECT COUNT(*) FROM FINAL TABLE(
        INSERT INTO SESSION.OBJ_TABLE(CREATOR, TABLE)
            SELECT CREATOR, NAME FROM SYSIBM.SYSTABLES
            WHERE CREATOR LIKE 'WDA%'
);

Step 3: Invoke ADMIN_INFO_SQL

Invoke ADMIN_INFO_SQL with the parameter settings listed in Table 7.

Table 7. Parameter settings
Parameter nameParameter value
TABLE_CREATORSESSION
TABLE_NAMELIST_TABLE-OBJ_TABLE
CATALOG_CREATORDEFAULT
PLAN_INFOFINANCE%-4907080-4907095
COLLECT_DDLY
COLLECT_STATSY
COLLECT_COLSTATSY
EDIT_DDLN
EDIT_VERSION_MODENONE
PART_ROTATIONN
OUTPUT_METHODR
OUTPUT_INFONONE
PMR_INFO11111.000.000

Listing 6 illustrates the CALL statement

Listing 6. Call example with LIST_TABLE input
CALL SYSPROC.ADMIN_INFO_SQL(
   'SESSION', 'LIST_TABLE-OBJ_TABLE',
   'DEFAULT',
   'NONE',
   'Y', 'Y', 'Y',
   'N',
   'NONE',
   'N',
   'R',
   'NONE',
   '11111.000.000',
   ?,
   ?
);

Invoke SYSPROC.ADMIN_INFO_SQL from different environments

Invoking ADMIN_INFO_SQL on the host

This article focuses on the distributed way to invoke ADMIN_INFO_SQL. For host-based invocations, look at the sample job DSNTEJ6I in SDSNSAMP. It demonstrates how to invoke the DB2-supplied application driver (DSNADMSB), which calls the ADMIN_INFO_SQL procedure. Just follow the instructions in the prolog of DSNTEJ6I.

This section provides examples of invoking ADMIN_INFO_SQL from a C program and a Java program. You can find links to the complete source code in the Downloads section of this article. All samples implement cloning database objects and statistics (previously described). The programs perform these steps:

  1. Create a list table.
  2. Populate the list table with table qualifiers and names from the SYSIBM.SYSTABLES table.
  3. Invoke ADMIN_INFO_SQL and retrieve the result set.
  4. Write the result set contents to separate files: one each for DDL, statistics, column statistics, SQL, explain output, and subsystem parameter information.
Listing 7. Using Java and JDBC - excerpt
static String CALL_ADMIN_INFO_SQL =
   "CALL SYSPROC.ADMIN_INFO_SQL(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

static void collectWorkload(Connection con) 
   throws SQLException, java.io.IOException {
	System.out.println("   preparing procedure call ...");
	CallableStatement cstmt = con.prepareCall(CALL_ADMIN_INFO_SQL);
	
	cstmt.setString(1, "SESSION");
	cstmt.setString(2, "LIST_TABLE-OBJ_TABLE");
	cstmt.setString(3, "DEFAULT");		
	cstmt.setString(4, "NONE");
	cstmt.setString(5, "Y");
	cstmt.setString(6, "Y");
	cstmt.setString(7, "Y");
	cstmt.setString(8, "N");
	cstmt.setString(9, "NONE");
	cstmt.setString(10,"N");
	cstmt.setString(11,"R");
	cstmt.setString(12,"NONE");
	cstmt.setString(13,"11111.000.000");
	
	cstmt.registerOutParameter(14, java.sql.Types.INTEGER);
	cstmt.registerOutParameter(15, java.sql.Types.VARCHAR);
	
	System.out.println("   executing procedure call ...");
	cstmt.execute();
	
	int returnCode = cstmt.getInt(14);
	
	// check for error/warning message
	if (returnCode != 0) {
		String message = cstmt.getString(15);
		if (message != null) {
			System.err.println("procedure return code: " 
				+ Integer.toString(returnCode) 
				+ "\nerror message:\n" + message);
		}
	}
	
	ResultSet rs = cstmt.getResultSet();

	if (rs != null) {
		System.out.println("   reading result set ...");
		while(rs.next()) {
			int type = rs.getInt(1);
			int rowNum = rs.getInt(2);
			String line = rs.getString(3);
			//TODO: do something with the result
			}
		}
		
		// close the result set
		rs.close();
	}
	cstmt.close();
	System.out.println("   DONE with ADMIN_INFO_SQL");
}
Listing 8. Using C and DB2 CLI - excerpt
#define CALL_ADMIN_INFO_SQL "CALL SYSPROC.ADMIN_INFO_SQL(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"

int callAdminInfoSql(SQLHANDLE hdbc)
{
  SQLRETURN cliRC = SQL_SUCCESS;
  int rc = 0;
  SQLHANDLE hstmtCall; /* statement handle */
  SQLCHAR *stmtCall = (SQLCHAR *)CALL_ADMIN_INFO_SQL;
  SQLINTEGER returnCode = 0;
  SQLCHAR outMessage[1332]= {0};
  SQLSMALLINT numCols = 0;
  SQLINTEGER  colType   = 0;
  SQLINTEGER  colRowNum = 0;
  SQLCHAR     colLine[4097] = {0};

  /* allocate a statement handle */
  cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtCall);
  DBC_HANDLE_CHECK(hdbc, cliRC);

  /* prepare the statement */
  cliRC = SQLPrepare(hstmtCall, stmtCall, SQL_NTS);
  STMT_HANDLE_CHECK(hstmtCall, hdbc, cliRC);

  /* bind parameter 1 (SCHEMA) to the statement */
  cliRC = SQLBindParameter(hstmtCall, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 128, 0,
                           "SESSION", 0, NULL);
  STMT_HANDLE_CHECK(hstmtCall, hdbc, cliRC);

  /* bind parameter 2 (TABLE) to the statement */
  cliRC = SQLBindParameter(hstmtCall, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 128, 0,
                           "LIST_TABLE-OBJ_TABLE", 0, NULL);
  STMT_HANDLE_CHECK(hstmtCall, hdbc, cliRC);

  /**********************************************************************/
  /***** Please look at the attached source in the Downloads section *****/
  /***** to see all parameter bind commands                         *****/
  /**********************************************************************/
  
  cliRC = SQLBindParameter(hstmtCall, 13, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 13, 0,
                           "11111.000.000", 0, NULL);
  STMT_HANDLE_CHECK(hstmtCall, hdbc, cliRC);

  /* bind out parameter 14 (RETURN CODE) to the statement */
  cliRC = SQLBindParameter(hstmtCall, 14, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0,
                           &returnCode, 0, NULL);
  STMT_HANDLE_CHECK(hstmtCall, hdbc, cliRC);
  
  /* bind out parameter 15 (OUTPUT MESSAGE) to the statement */
  cliRC = SQLBindParameter(hstmtCall, 15, SQL_PARAM_OUTPUT, SQL_C_CHAR,SQL_VARCHAR,1331,0,
                           outMessage, 1331, NULL);
  STMT_HANDLE_CHECK(hstmtCall, hdbc, cliRC);

  /* execute the statement */
  cliRC = SQLExecute(hstmtCall);
  STMT_HANDLE_CHECK(hstmtCall, hdbc, cliRC);

  if (returnCode != 0)
  {
    printf("procedure return code: %i\nerror message:\n%s\n", returnCode, outMessage);
  }

  /* get number of result columns */
  cliRC = SQLNumResultCols(hstmtCall, &numCols);
  STMT_HANDLE_CHECK(hstmtCall, hdbc, cliRC);

  printf("   Result set returned %d columns\n", numCols);

  /* bind column 1 (TYPE) to variable */
  cliRC = SQLBindCol(hstmtCall, 1, SQL_C_LONG, &colType, 0, NULL);
  STMT_HANDLE_CHECK(hstmtCall, hdbc, cliRC);

  /* bind column 2 (ROW NUM) to variable */
  cliRC = SQLBindCol(hstmtCall, 2, SQL_C_LONG, &colRowNum, 0, NULL);
  STMT_HANDLE_CHECK(hstmtCall, hdbc, cliRC);

  /* bind column 3 (LINE) to variable */
  cliRC = SQLBindCol(hstmtCall, 3, SQL_C_CHAR, colLine, 4096, NULL);
  STMT_HANDLE_CHECK(hstmtCall, hdbc, cliRC);

  /* fetch result set returned from stored procedure */
  cliRC = SQLFetch(hstmtCall); 
  STMT_HANDLE_CHECK(hstmtCall, hdbc, cliRC);

  printf("\n   Retrieving result set\n");
   while (cliRC != SQL_NO_DATA_FOUND) 
  {
    //TODO: do someting with the result
    /* fetch next row */
    cliRC = SQLFetch(hstmtCall);
    STMT_HANDLE_CHECK(hstmtCall, hdbc, cliRC);
  }
  
  /* free the statement handle */
  cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmtCall);
  STMT_HANDLE_CHECK(hstmtCall, hdbc, cliRC);

  return rc;
}

Note:The code and scripts for the DB2 CLI example have been developed and tested with an application that runs on Linux on an x86 platform and connects to DB2 for z/OS through DB2 Connect. You need to change the compile and link commands in the Makefile to run on another platform. You can find the appropriate commands in the DB2 for Linux, UNIX, and Windows CLI sample build and link scripts. The utilcli.c and utilcli.h files have been copied from the DB2 for Linux, UNIX, and Windows CLI sample directory.

Listing 9. Using the DB2 for Linux, UNIX, and Windows command line processor
CALL SYSPROC.ADMIN_INFO_SQL(
   'SESSION', 'LIST_TABLE-OBJ_TABLE',
   'DEFAULT',
   'NONE',
   'Y', 'Y', 'Y',
   'N',
   'NONE',
   'N',
   'R',
   'NONE',
   '11111.000.000',
   ?,
   ?
);

Trace problems in SYSPROC.ADMIN_INFO_SQL

Tracing of SYSPROC.ADMIN_INFO_SQL can be enabled as specified in Listing 10 below. The trace is written to stderr. The output is written to the WLM output stream. Because the trace accumulates a large amount of data, you should enable it only when you need to do debugging.

To turn trace on, the customer must alter the stored procedure, as shown in Listing 10:

Listing 10. Turn trace on
  ALTER PROCEDURE SYSPROC.ADMIN_INFO_SQL
     RUN OPTIONS 'TRAP(OFF),STACK(,,ANY,),ENVAR("TRACE=ON")';

To turn the trace off, the customer must alter the stored procedure, as shown in Listing 11:

Listing 11. Turn trace off
  ALTER PROCEDURE SYSPROC.ADMIN_INFO_SQL
     RUN OPTIONS 'TRAP(OFF),STACK(,,ANY,)';

Conclusion

With the versatility and simplicity of the new ADMIN_INFO_SQL stored procedure, it is now much easier and faster to collect query-related service information from DB2. The procedure ensures a quick turn-around when working with the DB2 support team. As shown in this article, ADMIN_INFO_SQL can be invoked from the host as well as distributed applications, which allows it to be embedded into different environments. Last, but not least, the procedure can also be used to collect re-create information of database objects, like tables and views.


Downloads

DescriptionNameSize
DB2 CLI sample1AdminInfoSqlCLI.tar.gz8KB
Java sample2AdminInfoSqlJava.zip3KB
DB2 CLP sample3AdminInfoSqlCLP.sql1KB

Notes

  1. Sample code to call SYSPROC.ADMIN_INFO_SQL using C and DB2 CLI (Linux on x86).
  2. Sample code to call SYSPROC.ADMIN_INFO_SQL using Java and JDBC.
  3. Sample code to call SYSPROC.ADMIN_INFO_SQL using the DB2 LUW command line processor.

Resources

Learn

Get products and technologies

  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox, learning how to efficiently implement service-oriented architecture.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=599462
ArticleTitle=Introducing SYSPROC.ADMIN_INFO_SQL
publish-date=12092010