November 06, 2012

DB2 Administrative Stored Procedures

DB2 provides stored procedures that can be called in application programs to perform administrative functions. Here's a list of these procedures. For detailed descriptions and a complete list of the parameters used by these procedures, see the Administration Guide.


  • DSNACICS provides a way for workstation applications to invoke CICS server programs while using TCP/IP as the communication protocol. The workstation applications use TCP/IP and DB2 Connect to connect to a DB2 for z/OS subsystem, and then call DSNACICS to invoke the CICS server programs.
  • DSNAIMS allows DB2 applications to invoke IMS transactions and commands easily, without maintaining their own connections to IMS.
  • DSNAIMS2 allows DB2 applications to invoke IMS transactions and commands easily, without maintaining their own connections to IMS. DSNAIMS2 includes multi-segment input support for IMS transactions.

Security: DSNLEUSR is used to store encrypted user IDs and passwords in the SYSIBM.SYSUSERNAMES table.

DB2 Commands

  • SYSPROC.ADMIN_COMMAND_DB2 executes one or more DB2 commands on a connected DB2 subsystem or DB2 data sharing group member. This stored procedure also returns the command output messages.
  • SYSPROC.ADMIN_COMMAND_DSN executes a BIND, REBIND or FREE DSN subcommand and returns the output from the DSN subcommand execution.
  • SYSPROC.ADMIN_COMMAND_UNIX executes a z/OS UNIX System Services command and returns the output.

Dataset Management

  • SYSPROC.ADMIN_DS_BROWSE returns either text or binary records from certain data sets or their members. You can browse a physical sequential (PS) data set, a generation data set, a partitioned data set (PDS) member or a partitioned data set extended (PDSE) member. This stored procedure supports only data sets with LRECL=80 and RECFM=FB.
  • SYSPROC.ADMIN_DS_LIST returns a list of data set names, a generation data group (GDG), a PDS member, a PDSE member, or generation data sets of a GDG.
  • SYSPROC.ADMIN_DS_RENAME renames a PS data set, a PDS or a member of a PDS or PDSE.
  • SYSPROC.ADMIN_DS_SEARCH determines if certain data sets are cataloged, or if a library member of a cataloged data set exists. You can search for a PS data set, a PDS, a PDSE member, a GDG, a generation data set (GDS) or the library member of a cataloged PDS or PDSE.
  • SYSPROC.ADMIN_DS_WRITE writes text or binary records that are passed in a global temporary table to data sets or their members. You can write to a PS data set, a PDS or PDSE member or a GDS. This stored procedure can append or replace an existing PS data set, PDS or PDSE member or GDS. It can also create a new PS data set, PDS or PDSE data set or member, or a new GDS for an existing GDG as needed. This stored procedure supports only data sets with LRECL=80 and RECFM=FB.

DB2 System Information

  • SYSPROC.ADMIN_INFO_HOST returns the host name of a connected DB2 subsystem or every member of a data sharing group.
  • ADMIN_INFO_SMS returns space information about copy pools and their storage groups and volumes.
  • SYSPROC.ADMIN_INFO_SSID returns the name of the connected DB2 subsystem.
  • ADMIN_INFO_SQL 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 (e.g., a poor performing SQL query). To provide the results to IBM Software Support, you must terse the files and upload them to an FTP site, and then update the PMR when the files are available. For more information, see APAR II11945.
  • SYSPROC.ADMIN_INFO_SYSPARM returns the system parameters, application defaults modules and IRLM parameters of a connected DB2 subsystem or member of its data sharing group.

JES Job Management

  • SYSPROC.ADMIN_JOB_CANCEL purges or cancels a job.
  • SYSPROC.ADMIN_JOB_FETCH retrieves SYSOUT from JES spool and returns the SYSOUT.
  • SYSPROC.ADMIN_JOB_QUERY displays the status and completion information about a job.
  • SYSPROC.ADMIN_JOB_SUBMIT submits a job to a JES2 or JES3 system.

As I was reading about the stored procedures to support the administrative task scheduler, I noticed some other new stored procedures. Here's that list:

Administrative Task Scheduler Management

  • SYSPROC.ADMIN_TASK_ADD adds a task to the scheduler''s task list.
  • ADMIN_TASK_CANCEL attempts to stop the execution of a currently running task.
  • SYSPROC.ADMIN_TASK_REMOVE removes a task from scheduler's the task list.
  • ADMIN_TASK_UPDATE updates the schedule of a task in the scheduler's task list. If the updated task is currently running, the changes go into effect after the current execution finishes.

Administrative Task Scheduler – Table Functions


Utility Execution Management

  • SYSPROC.ADMIN_UTL_SCHEDULE executes utilities in parallel.
  • SYSPROC.ADMIN_UTL_SORT sorts objects for parallel utility execution using JCL or the ADMIN_UTL_SCHEDULE stored procedure.

Application Portability

  • GET_CONFIG retrieves data server configuration information.
  • GET_MESSAGE returns the short message text for an SQLCODE.
  • GET_SYSTEM_INFO returns system information about the data server.

Next week I’ll provide information about stored procedures that help manage performance.

Is your organization taking advantage of any of these administrative stored procedures? Please share your experience in Comments.