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.

CICS and IMS

• 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

• DSNADM.ADMIN_TASK_LIST
• DSNADM.ADMIN_TASK_OUTPUT
• DSNADM.ADMIN_TASK_STATUS()
• DSNADM.ADMIN_TASK_STATUS(max-history)

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.

Roles and Trusted Context

During the SHARE conference in Orlando, I was standing in the vendor hall having a discussion with someone about DB2 security. This person told me how his company implemented roles and trusted context to give DBAs SYSADM capabilities without granting them SYSADM authority. I found this very interesting, because I've not heard of many companies implementing roles and trusted context.

I was telling this person how unusual this is, and expressing my belief that a lack of information is the cause. Implementing roles hasn't really been presented in a way that people can understand, so few see the value.

Funny then that shortly after I got back from Orlando, I received notification of a new IBM Redbooks publication, "Security Functions with DB2 10 for z/OS."

This publication, which is currently in draft form, includes information on roles and trusted context. Chapter 4 examines some challenges roles and trusted context can help solve and then provides details to implement the solution. The challenge of granting SYSADM is mentioned in chapter 4.5 ("example of local trusted context, securing DBA activities").

Specific to DB2 10, I noticed only minor updates concerning roles and trusted context. The most significant change is the new SECADM authority. Users will need this authority to manage roles and trusted context. Other related changes involve classifying row permission, column mask and role and trusted context as security-related functions.

What I love about Redbooks publications is that you get real people presenting real problems and providing real-life solutions. For anyone working with DB2 for z/OS -- whether you're a developer, DBA or security administrator -- I highly recommend taking the time to review how security works. This particular publication is a good start.