DSNUTILU stored procedure

Use the DSNUTILU stored procedure to run DB2® utilities from a DB2 application program.

When called, DSNUTILU performs the following actions:

  • Translates the values that are specified for the utility-id and restart parameters to EBCDIC
  • Creates the utility input stream (SYSIN) for utility control statements that are encoded in Unicode
  • Invokes DB2 utilities by using DSNUTILB
  • Deletes all the rows that are currently in the created temporary table (SYSIBM.SYSPRINT)
  • Captures the utility output stream (SYSPRINT) into a created temporary table (SYSIBM.SYSPRINT)
  • Declares a cursor to select from SYSPRINT: 
    DECLARE SYSPRINT CURSOR WITH RETURN FOR
  SELECT SEQNO, TEXT FROM SYSPRINT
  ORDER BY SEQNO;
  • Opens the SYSPRINT cursor

The calling program then fetches rows from the returned result set to obtain the captured utility output.

Start of changeThe character set of the resulting utility SYPRINT output that is placed in the SYSIBM.SYSPRINT table is determined by the BIND PACKAGE statement for the DSNUTILU stored procedure. If the bind option ENCODING(EBCDIC) is specified, the SYSPRINT contents are in EBCDIC. If the bind option ENCODING(UNICODE) is specified, the SYSPRINT contents are in Unicode. The default installation job, DSNTIJRT, contains ENCODING(EBCDIC).End of change

Environment for DSNUTILU

Start of changeDSNUTILU must run in a WLM environment. End of change

Start of changeThe WLM core environment DSNWLM_UTILS. DSNWLM_UTILS is intended for the following DB2 utilities stored procedures only:End of change

  • DSNUTILU
  • DSNUTILS (deprecated)

Start of change If you plan to run other applications in this environment, add the procedure and add the DCB information for SYSIN, as shown in the following example:End of change

Start of change
//SYSIN    DD    UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND),
//         DCB=(RECFM=V,LRECL=32708)
End of change
Related information:

The WLM-established address space for DSNUTILU

The JCL procedure for starting the WLM-established address space in which DSNUTILU runs requires DD statements for DSSPRINT, SYSIN, and SYSPRINT. The SYSIN and SYSPRINT DD statements must allocate workfiles that DSNUTILU can use to temporarily store utility input statements and utility output messages. If you plan to run RUNSTATS and collect distribution statistics, you also need to allocate a DD statement for RNPRIN01.

DB2 provides an address space procedure for DSNWLM_UTILS, which is the core WLM environment for DSNUTILU. That address space procedure is called DSNWLMU. For detailed information about DSNWLMU, see Core WLM environments for DB2-supplied routines.

Authorization required for DSNUTILU

To call the DSNUTILU stored procedure, the owner of the package or plan that contains the CALL statement must have one or more of the following privileges on each package that the stored procedure uses:

  • The EXECUTE privilege on the package for DSNUTILU
  • Ownership of the package
  • PACKADM authority for the package collection
  • SYSADM authority

Then, to execute the utility, you must use a privilege set that includes the authorization to run the specified utility.

Utility control statements that are passed to DSNUTILU

Input data sets for the utility control statements can begin with the following Unicode characters:

  • A Unicode UTF-8 blank (X'20')
  • A Unicode UTF-8 dash (X'2D')
  • Uppercase Unicode UTF-8 "A" through "Z" (X'41' through X'5A')

Any utility that requires a sort must include the SORTDEVT keyword in the utility control statement. Use of the SORTNUM keyword is optional.

Also, DSNUTILU does not dynamically allocate data sets. Use the TEMPLATE utility control statement to dynamically allocate data sets.

Related information:

DSNUTILU stored procedure syntax diagram

The following syntax diagram shows the SQL CALL statement for invoking utilities as a stored procedure.

Read syntax diagram
>>-CALL--DSNUTILU--(--utility-id,restart,utstmt,retcode--)-----><

DSNUTILU option descriptions

utility-id
Specifies a unique identifier for this utility within DB2.

utility-id is an input parameter of type VARCHAR(16) in Unicode UTF-8, which must be translatable to the following allowable EBCDIC characters:

  • A - Z (uppercase and lowercase)
  • 0 - 9
  • #, $, @, ¢, !, ‸, or period (.)
restart
Specifies whether DB2 is to restart a current utility, and, if so, at what point the utility is to be restarted.

restart is an input parameter of type VARCHAR(8) in Unicode UTF-8, which must be translatable to allowable EBCDIC characters. Specify one of the following values for this parameter:

NO or null
Indicates that the utility job is new and is not a restarted job. No other utility with the same utility identifier (UID) can exist.

The default is null.

CURRENT
Restarts the utility at the last commit point.
PHASE
Restarts the utility at the beginning of the currently stopped phase. Use the DISPLAY UTILITY command to determine the currently stopped phase.
PREVIEW
Executes the utility control statements that follow in preview mode.

In preview mode, DB2 parses all utility control statements for syntax errors, but normal utility execution does not occur. If the syntax is valid, DB2 expands all LISTDEF lists and TEMPLATE data set name expressions that are included in SYSIN and prints the results to the SYSPRINT data set. DB2 evaluates and expands all LISTDEF statements into a list of table spaces or index spaces. DB2 also evaluates TEMPLATE data set name expressions into data set names through variable substitution. DB2 also expands lists and data set name expressions from any LISTDEF libraries and TEMPLATE libraries that are referenced by a utility invocation.

If the PREVIEW keyword is omitted, preview processing is turned off with one exception. The absence of this keyword does not override the PREVIEW JCL parameter, which, if specified, remains in effect for the entire job step.

This option is identical to the PREVIEW JCL parameter.

Related information:
utstmt
Specifies the utility control statements.

utstmt is an input parameter of type VARCHAR(32704) in Unicode UTF-8. If the utility statement is not in Unicode UTF-8, DB2 converts it to UTF-8. Therefore, if you pass a utility control statement in another format, consider the Possible consequences of character conversion.

retcode
Specifies the utility highest return code.

retcode is an output parameter of type INTEGER.

Terminating or restarting a utility with DSNUTILU

If you do not want to restart a utility after a failure, take the following actions:

  1. Use the TERM UTIL command to terminate the failing utility.

    When you terminate the utility, DB2 deletes the data sets that are needed by the utility.

  2. Refresh the WLM environment in one of the following ways:
    • Submit the VARY command:
      VARY WLM,APPLENV=xxx,REFRESH
    • Call the WLM_REFRESH stored procedure.

If you want to restart a utility after a failure, take the following actions:

  1. When you run the utility, use a TEMPLATE utility control statement, and specify DISP (NEW,CATLG,CATLG) in your template for data sets that are needed by the utility.
  2. When the utility fails, refresh the WLM environment, but do not terminate the utility.
  3. After the utility completes, delete the allocated data sets manually.
Related information:

Sample program for calling DSNUTILU

The following sample program calls DSNUTILU and is included in SDSNSAMP:

  • Job DSNTEJ6R compiles, link-edits, binds, and runs sample C-language caller program DSN8ED8, which invokes the DSNUTILU stored procedure to execute a utility.
Related information:

DSNUTILU output

DB2 creates the result set according to the DECLARE statement shown in Example of declaring a cursor to select from SYSPRINT

Output from a successful execution of the DSNTEJ6R sample job or an equivalent job lists the specified parameters, followed by the messages that are generated by the DB2 DIAGNOSE DISPLAY MEPL utility.

Related information: