The IBM® DB2® Universal Database^TM (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

Back to top

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.

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

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

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

				
#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 */

Back to top

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

• DB2 Universal Database for Linux, UNIX and Windows Support is the ideal place to locate resources such as the Version 8.2 Information Center and PDF product manuals.
  
  
• For the latest DB2 information online, including reference information about all of the supported SQL administrative routines, visit the DB2 Information Center.
  
  

About the author

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

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• A sampling of the SQL administrative routines
• A pair of detailed examples
• Summary
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

New Offerings

Speed innovation with an affordable, dynamic infrastructure for software delivery

Special offers