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.

Back to top

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

Back to top

Install ADMIN_INFO_SQL

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.

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.

Back to top

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.

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.

Back to top

Collect service data for SQL statements using EXPLAIN

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:

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 …

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.

Listing 3 illustrates the 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',
   ?,
   ?
);

Back to top

Clone database objects and statistics

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.

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:

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.

Listing 6 illustrates the CALL statement

	

CALL SYSPROC.ADMIN_INFO_SQL(
   'SESSION', 'LIST_TABLE-OBJ_TABLE',
   'DEFAULT',
   'NONE',
   'Y', 'Y', 'Y',
   'N',
   'NONE',
   'N',
   'R',
   'NONE',
   '11111.000.000',
   ?,
   ?
);

Back to top

Invoke SYSPROC.ADMIN_INFO_SQL from different environments

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.

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");
}

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

	
CALL SYSPROC.ADMIN_INFO_SQL(
   'SESSION', 'LIST_TABLE-OBJ_TABLE',
   'DEFAULT',
   'NONE',
   'Y', 'Y', 'Y',
   'N',
   'NONE',
   'N',
   'R',
   'NONE',
   '11111.000.000',
   ?,
   ?
);

Back to top

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:

  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:

  ALTER PROCEDURE SYSPROC.ADMIN_INFO_SQL
     RUN OPTIONS 'TRAP(OFF),STACK(,,ANY,)';

Back to top

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.

Back to top

Downloads

Information about download methods

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

• DB2 for z/OS: Learn more about DB2 for z/OS.
  
  
• DB2 for z/OS page on developerWorks: Learn more about DB2 for z/OS, and find the technical resources to grow your skills.
  
  
• DB2 for z/OS Information Center: Learn more about the latest version of DB2 for z/OS.
  
  
• DB2 for z/OS on ChannelDB2: Learn more about DB2 for z/OS.
  
  
• developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Follow developerWorks on Twitter.
  
  
• Watch developerWorks on-demand demos, ranging from product installation and setup demos for beginners, to advanced functionality for experienced developers.
  
  

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

• Participate in the discussion forum.
  
  
• Get involved in the developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.
  
  

About the authors

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

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

Rate this article

Comments

Back to top

Table of contents

• A short introduction to ADMIN_INFO_SQL
• Article overview
• Install ADMIN_INFO_SQL
• ADMIN_INFO_SQL input and output
• Collect service data for SQL statements using EXPLAIN
• Clone database objects and statistics
• Invoke SYSPROC.ADMIN_INFO_SQL from different environments
• Trace problems in SYSPROC.ADMIN_INFO_SQL
• Conclusion
• Downloads
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers