DB2 Basics: An introduction to the DB2 UDB SQL administrative routines

The IBM DB2 Universal Database SQL administrative routines represent an easy-to-use application programming interface (API) to DB2 administrative functions through SQL. Working examples show you how to use some of these routines, which can be invoked from a DB2 command line, a command script, or an application containing embedded SQL.

Share:

Roman Melnyk, B. (roman_b_melnyk@hotmail.com), DB2 Information Development, IBM Toronto Lab

Photo: Roman B. MelnykRoman B. Melnyk , Ph.D., is a senior member of the DB2 Information Development team, specializing in database administration, DB2 utilities, and SQL. During more than ten years at IBM, Roman has written numerous DB2 books, articles, and other related materials. Roman coauthored DB2 Version 8: The Official Guide (Prentice Hall Professional Technical Reference, 2003), DB2: The Complete Reference (Osborne/McGraw-Hill, 2001), DB2 Fundamentals Certification for Dummies (Hungry Minds, 2001), and DB2 for Dummies (IDG Books, 2000).



05 May 2005

The IBM® DB2® Universal DatabaseTM (DB2 UDB) SQL administrative routines encompass a collection of built-in table functions, procedures, and scalar functions whose job it is to perform a variety of DB2 administrative tasks. These tasks include executing an action (for example, reorganizing a table), retrieving a report (for example, returning a result set containing system snapshot data), or returning a single value for use within an application (for example, returning the application ID of the current connection).

The SQL administrative routines make it possible to issue administrative commands through SQL-based applications, without the need for interpretation through the CLP. Some of the routines take no arguments, and some of them have one or more input arguments or output parameters. The table functions return a result set in the form of a table that can be queried.

A sampling of the SQL administrative routines

Table 1 summarizes some of the more commonly used SQL administrative routines. Each named routine is described and an example showing its use is provided. For complete information about all of the supported routines, visit the DB2 Information Center.

Table 1. SQL administrative routines
Routine NameRoutine TypeDescriptionExample
ADMIN_CMDProcedureExecutes DB2 command line processor (CLP) administrative commands using the SQL CALL statement. The procedure currently supports the following commands: DESCRIBE, EXPORT, PRUNE HISTORY/LOGFILE, REORG INDEXES/TABLE, RUNSTATS, and UPDATE DATABASE CONFIGURATION. For detailed information about syntax differences between the form of a command that is executed through the CLP and the form that is executed through the ADMIN_CMD procedure, see ADMIN_CMD procedure. For more information about the DB2 command line processor, see Getting to know the DB2 UDB command line processor.
  • call sysproc.admin_cmd('describe select * from staff')
  • call sysproc.admin_cmd('export to /home/melnyk/output/sales.del of del messages /home/melnyk/output/export.msg select * from sales')
  • call sysproc.admin_cmd('prune history 20050502')
  • call sysproc.admin_cmd('reorg table sales allow no access')
  • call sysproc.admin_cmd('runstats on table melnyk.employee')
  • call sysproc.admin_cmd('update db cfg using logretain recovery userexit yes')
ADMIN_LIST_HISTTable functionReturns information from the history file that is associated with the currently connected database partition.select eid, operation, start_time from table(sysproc.admin_list_hist()) as listhistory
APPLICATION_IDScalar functionReturns the application ID of the current connection. The data type of the result is VARCHAR(128).select application_id() as appl_id from sysibm.sysdummy1
ENV_GET_INST_INFOTable functionReturns information about the current instance.select inst_name, is_inst_partitionable, num_dbpartitions, inst_ptr_size from table(sysproc.env_get_inst_info()) as instanceinfo
ENV_GET_PROD_INFOTable functionReturns information about installed DB2 products.select installed_prod, prod_release from table(sysproc.env_get_prod_info()) as productinfo
ENV_GET_SYS_INFOTable functionReturns information about the system.select os_name, host_name, total_memory from table(sysproc.env_get_sys_info()) as systeminfo
GET_DBM_CONFIGTable functionReturns database manager configuration information in a table with two rows and a column for each parameter. The first column is DBMCONFIG_TYPE; the row with a value of 0 in this column contains the parameter values that are stored on disk, and the row with a value of 1 in this column contains the current parameter values stored in memory.select dbmconfig_type, numdb, diaglevel from table(sysfun.get_dbm_config()) as dbmcfg
GET_DBSIZE_INFOProcedureCalculates and returns the database size and maximum capacity, in bytes.call sysproc.get_dbsize_info(?,?,?,0)
HEALTH_CONT_HI, HEALTH_CONT_HI_HIS, HEALTH_CONT_INFO, HEALTH_DB_HI, HEALTH_DB_HIC, HEALTH_DB_HIC_HIS, HEALTH_DB_HI_HIS, HEALTH_DB_INFO, HEALTH_DBM_HI, HEALTH_DBM_HI_HIS, HEALTH_DBM_INFO, HEALTH_TBS_HI, HEALTH_TBS_HI_HIS, HEALTH_TBS_INFOTable functionsThese routines return information from health snapshots. Depending on the routine, information is returned about containers, a database, the database manager, or table spaces. Some of the routines return health indicator information, and some return health indicator history information. All of the health routines have an input argument of type INTEGER that specifies a valid partition number; you can specify a value of -1 for the current partition, or -2 for all partitions. If you specify the null value, -1 is set implicitly. Most of the health routines (but not the routines that return database manager information) have an input argument of type VARCHAR(255) that specifies a valid database name in the same instance as the currently connected database; you can specify the null value to request information from the currently connected database.select snapshot_timestamp, substr(container_name,1,24) as container_name, hi_id, hi_value, hi_timestamp, hi_alert_state from table(sysproc.health_cont_hi('sample',-1)) as health_cont_hi
REBIND_ROUTINE_PACKAGEProcedureRebinds the package that is associated with an SQL procedure.call sysproc.rebind_routine_package('P ','melnyk.update_inventory','ANY')
REG_LIST_VARIABLESTable functionReturns the DB2 registry settings that are currently being used by the database partition from which the function was invoked.select substr(reg_var_name,1,24) as reg_var_name, substr(reg_var_value,1,12) as reg_var_value, level from table(sysproc.reg_list_variables()) as registryinfo
REORGCHK_IX_STATSProcedureReturns a result set containing statistics that indicate whether or not one or more indexes need to be reorganized.call sysproc.reorgchk_ix_stats('s','melnyk')
REORGCHK_TB_STATSProcedureReturns a result set containing statistics that indicate whether or not one or more tables need to be reorganized.call sysproc.reorgchk_tb_stats('t','melnyk.employee')
SNAP_GET_CONTAINER, SNAP_GET_DB, SNAP_GET_DYN_SQL, SNAP_GET_STO_PATHS, SNAP_GET_TAB, SNAP_GET_TBSP, SNAP_GET_TBSP_PARTTable functionsThese routines return snapshot information from the tablespace_container logical data group, the database and detail_log logical data groups, the dynsql logical data group, the storage_paths logical data group, the table logical data group, the tablespace logical data group, and the tablespace_nodeinfo logical data group, respectively. For more information about logical data groups, see Snapshot monitor interface mappings to logical data groups. All of the snapshot routines have an input argument of type INTEGER that specifies a valid partition number; you can specify a value of -1 for the current partition, or (in most cases) -2 for all partitions. If you specify the null value, -1 is set implicitly. All of the SNAP_* routines have an input argument of type VARCHAR(255) that specifies a valid database name in the same instance as the currently connected database; you can specify the null value to request information from the currently connected database.select snapshot_timestamp, substr(tabschema,1,16) as tabschema, substr(tabname,1,16) as tabname, tab_type, data_object_pages, rows_written, dbpartitionnum from table(sysproc.snap_get_tab('sample',-1)) as tabinfo
SNAPSHOT_AGENT, SNAPSHOT_APPL, SNAPSHOT_APPL_INFO, SNAPSHOT_BP, SNAPSHOT_CONTAINER, SNAPSHOT_DATABASE, SNAPSHOT_DBM, SNAPSHOT_DYN_SQL, SNAPSHOT_FCM, SNAPSHOT_FCMNODE, SNAPSHOT_LOCK, SNAPSHOT_LOCKWAIT, SNAPSHOT_QUIESCERS, SNAPSHOT_RANGES, SNAPSHOT_STATEMENT, SNAPSHOT_SUBSECT, SNAPSHOT_SWITCHES, SNAPSHOT_TABLE, SNAPSHOT_TBREORG, SNAPSHOT_TBS, SNAPSHOT_TBS_CFGTable functionsThese routines return snapshot information about agents, applications, buffer pools, containers, databases, the database manager, dynamic SQL, the fast communication manager (FCM), locks, quiescers, ranges, statements, subsections of access plans, the database snapshot switch states, tables, table reorganizations, and table spaces. All of the snapshot routines have an input argument of type INTEGER that specifies a valid partition number; you can specify a value of -1 for the current partition, or -2 for all partitions. If you specify the null value, -1 is set implicitly. Most of the SNAPSHOT_* routines (but not the routines that return database manager-level information) have an input argument of type VARCHAR(255) that specifies a valid database name in the same instance as the currently connected database; you can specify the null value to request information from the currently connected database.select lock_mode, lock_object_type, substr(table_name, 1, 16) as table_name from table(sysproc.snapshot_lock('sample',-1)) as lockinfo where lock_object_type in (1,2,4,5,6,11,18,19)
SNAPSHOT_FILEWProcedureWrites system snapshot data to a file that can be subsequently queried. Input arguments to this routine are request type, database name, and database partition number. A request type of 1, for example, represents a request for database manager information. (For a list of all the supported request types, see Capturing database system snapshot information to a file using the SNAPSHOT_FILEW stored procedure.) A database partition value of -1 represents the current partition. A snapshot table function that corresponds to the specified request type returns information captured by a prior call to SNAPSHOT_FILEW if null values are specified as input arguments to the table function.(1) call sysproc.snapshot_filew(1,'sample',-1) (2) select * from table(snapshot_dbm(cast(null as integer))) as snapshot_dbm

A pair of detailed examples

To demonstrate how one might use an SQL administrative routine in an embedded SQL application, we will first create a table named TABLE_SNAPSHOTS in the SAMPLE database that comes with DB2 UDB (Listing 1). Then we will populate that table with table snapshot data captured through several invocations of the SNAP_GET_TAB table function. The columns that we have defined for the TABLE_SNAPSHOTS table correspond to columns of interest in the table returned by the SNAP_GET_TAB function. The INSERT INTO statement and its associated SELECT statement, which queries the table that the SNAP_GET_TAB routine returns, are embedded in a simple C program (MYAPP1; Listing 2). The WHERE clause restricts the result set to exclude system-generated tables and the TABLE_SNAPSHOTS table itself.

Listing 1. Creating a table to store snapshot monitor data
create table table_snapshots (
  snapshot_timestamp timestamp not null,
  tabschema varchar(16),
  tabname varchar(16),
  tab_type bigint,
  data_object_pages bigint,
  rows_written bigint,
  dbpartitionnum smallint
 );
Listing 2. Source code for a complete C program (myapp1.sqc) that demonstrates how easy it is to populate a database table with snapshot monitor data using an SQL administrative routine
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include "utilemb.h"

int TbInsert(void);

EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;

int main(int argc, char *argv[])
{
  int rc = 0;
  struct sqlca sqlca;
  char dbAlias[SQL_ALIAS_SZ + 1];
  char user[USERID_SZ + 1];
  char pswd[PSWD_SZ + 1];

  /* check the command line arguments */
  rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd);
  if (rc != 0)
  {
    return rc;
  }

  printf("\nThis sample shows how to use the SNAP_GET_TAB\n");
  printf("SQL administrative routine to capture DB2 monitoring\n");
  printf("data in a database table.\n");

  /* connect to the database */
  rc = DbConn(dbAlias, user, pswd);
  if (rc != 0)
  {
    return rc;
  }

  rc = TbInsert();

  /* disconnect from the database */
  rc = DbDisconn(dbAlias);
  if (rc != 0)
  {
    return rc;
  }

  return 0;
} /* end main */

int TbInsert(void)
{
  int rc = 0;
  struct sqlca sqlca;

  EXEC SQL INSERT INTO table_snapshots
    SELECT snapshot_timestamp, SUBSTR(tabschema,1,16) as tabschema,
        SUBSTR(tabname,1,16) as tabname, tab_type, data_object_pages,
        rows_written, dbpartitionnum
      FROM TABLE(sysproc.snap_get_tab('SAMPLE',-1)) AS tabinfo
      WHERE SUBSTR(tabschema,1,3) != 'SYS' AND tabname != 'TABLE_SNAPSHOTS';

  printf("\nInserted data into TABLE_SNAPSHOTS.\n");

  return 0;
} /* end TbInsert */

We are now ready to execute the MYAPP1 application to populate the TABLE_SNAPSHOTS table with snapshot data captured during a DB2 import operation. We can do this quite easily, using the following scenario. We will use a delimited ASCII data file named staffdata.del. This file contains a substantial amount of data (22767 records), which will give us enough time to run MYAPP1 three or four times during the import operation. Such a file can quickly be assembled by pasting many copies of exported data from the STAFF table (in the SAMPLE database) into a text file, and then overwriting the ID (SMALLINT) column data with sequential numbers that have been generated in a spreadsheet. This ensures unique values in the ID column.

Suppose we were to create a new table, NEWSTAFF, using the table definition for the STAFF table. After connecting to the SAMPLE database, we create the NEWSTAFF table by issuing the following SQL statement: create table newstaff like staff. Using the DB2 IMPORT command, we then populate this new table with data contained in the large DEL file: import from staffdata.del of del modified by chardel"" coldel, decpt. insert into newstaff.

While the import operation is running (Application 1), we will capture several table snapshots by running MYAPP1 (Application 2) several times in succession (Listing 3). We will execute Application 1 and Application 2 from each of two DB2 command windows (sessions).

The captured snapshot data is written to the TABLE_SNAPSHOTS table, which can be queried to show its contents (Listing 3).

Listing 3. Capturing snapshot monitor data in a database table (Application 2) during a DB2 import operation (Application 1)
Application 1:

connect to sample

create table newstaff like staff

import from staffdata.del of del modified by chardel"" coldel, decpt.
  insert into newstaff
  
SQL3109N  The utility is beginning to load data from file "staffdata.del".
SQL3110N  The utility has completed processing.
          "22767" rows were read from the input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "22767".
SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "22767" rows were processed from the input file.
          "22767" rows were successfully inserted into the table.
              "0" rows were rejected.

Number of rows read         = 22767
Number of rows skipped      = 0
Number of rows inserted     = 22767
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 22767

connect reset



Application 2:

elk /home/melnyk/mysamples>myapp1                        ==> run four times in succession

This sample shows how to use the SNAP_GET_TAB
SQL administrative routine to capture DB2 monitoring
data in a database table.

  Connecting to 'sample' database...
  Connected to 'sample' database.

  Inserted data into TABLE_SNAPSHOTS.

  Disconnecting from 'sample' database...
  Disconnected from 'sample' database.
...

elk /home/melnyk/mysamples>db2 connect to sample

elk /home/melnyk/mysamples>db2 "select * from table_snapshots"

SNAPSHOT_TIMESTAMP TABSCHEMA TABNAME  TAB_TYPE DATA_OBJECT_PAGES ROWS_WRITTEN DBPART...
------...--------- -----...- ----...- ----...- -----------...--- ----...----- ---------
2005-0...11.609616 MELNYK    NEWSTAFF        1                23         2015         0
2005-0...14.822350 MELNYK    NEWSTAFF        1               102         9017         0
2005-0...17.901561 MELNYK    NEWSTAFF        1               192        17051         0
2005-0...20.506826 MELNYK    NEWSTAFF        1               257        22767         0

  4 record(s) selected.

elk /home/melnyk/mysamples>db2 connect reset

Listing 4 shows the source code for a simple C program (MYAPP2) that features a call to the ADMIN_CMD procedure. In this example, the ADMIN_CMD procedure includes a command string for the DB2 EXPORT command. Data from the SALES table will be exported to a delimited ASCII file named sales.del, and export messages will be written to a file named export.msg each time that the program is executed. The product documentation for ADMIN_CMD states that "any path used in the EXPORT command must be a valid fully-qualified path on the server". It also states that "output files are created under the process ID of the procedure; this ID must have write and execute access to the directory where data is being exported and the message file is being written". On UNIX-based systems, for example, this means that you might have to change the access mode for the output directory to give groups and others write and execute permission to that directory (for example, chmod 733 <directory-name>).

Listing 4. Source code for a complete C program (myapp2.sqc) that demonstrates how easy it is to invoke a DB2 command through a call to the ADMIN_CMD procedure
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include "utilemb.h"

int TbExport(void);

EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;

int main(int argc, char *argv[])
{
  int rc = 0;
  struct sqlca sqlca;
  char dbAlias[SQL_ALIAS_SZ + 1];
  char user[USERID_SZ + 1];
  char pswd[PSWD_SZ + 1];

  /* check the command line arguments */
  rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd);
  if (rc != 0)
  {
    return rc;
  }

  printf("\nThis sample shows how to use the ADMIN_CMD\n");
  printf("SQL administrative routine to run a DB2 export operation.\n");

  /* connect to database */
  rc = DbConn(dbAlias, user, pswd);
  if (rc != 0)
  {
    return rc;
  }

  rc = TbExport();

  /* disconnect from the database */
  rc = DbDisconn(dbAlias);
  if (rc != 0)
  {
    return rc;
  }

  return 0;
} /* end main */

int TbExport(void)
{
  int rc = 0;
  struct sqlca sqlca;

  printf("\n  Exporting data to /home/melnyk/output/sales.del...\n");

  EXEC SQL CALL sysproc.admin_cmd('export to /home/melnyk/output/sales.del of del
    messages /home/melnyk/output/export.msg select * from sales');

  printf("\n  Messages have been written to /home/melnyk/output/export.msg.\n");

  return 0;
} /* TbExport */

Summary

We have seen that the DB2 UDB SQL administrative routines let you conveniently access DB2 administrative functions through SQL. These routines are particularly useful in applications, where they represent an easy-to-use alternative to the C API for accessing administrative functions. A number of these routines was described, and working examples provided, including sample programs containing procedure calls or effecting table updates using result sets returned by table functions.

Resources

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=82648
ArticleTitle=DB2 Basics: An introduction to the DB2 UDB SQL administrative routines
publish-date=05052005