Start of change

DSNUTILV stored procedure

Use the DSNUTILV stored procedure to run Db2 utilities from a Db2 application program. DSNUTILV can run utility statements as large as 2 GB.

When called, DSNUTILV 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.

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

Environment for DSNUTILV

DSNUTILV must run in a WLM environment. The WLM core environment DSNWLM_UTILS. DSNWLM_UTILS is intended for the following Db2 utilities stored procedures only:

  • Start of changeDSNUTILVEnd of change
  • DSNUTILU
  • DSNUTILS (deprecated)

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:

//SYSIN    DD    UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND),
//         DCB=(RECFM=V,LRECL=32708)

For more information, see Core WLM environments for Db2-supplied routines.

The WLM-established address space for DSNUTILV

The JCL procedure for starting the WLM-established address space in which DSNUTILV runs requires DD statements for DSSPRINT, SYSIN, and SYSPRINT. The SYSIN and SYSPRINT DD statements must allocate workfiles that DSNUTILV 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 DSNUTILV. That address space procedure is called DSNWLMU. For detailed information about DSNWLMU, see Core WLM environments for Db2-supplied routines.

Authorization required for DSNUTILV

To call the DSNUTILV 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 DSNUTILV
  • Ownership of the package
  • PACKADM authority for the package collection
  • DATAACCESS authority
  • SYSADM authority

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

Start of changeIf you use RACF to protect JES resources, you must define a RACF group that is authorized to access those resources and add that RACF group name to the profile of the user ID that calls DSNUTILV.End of change

Utility control statements that are passed to DSNUTILV

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, DSNUTILV does not dynamically allocate data sets. Use the TEMPLATE utility control statement to dynamically allocate data sets.

For more information, see:

DSNUTILV stored procedure syntax diagram

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

Read syntax diagramSkip visual syntax diagramCALLDSNUTILV(utility-id, restart, utstmt, retcode)

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

For more information, see:

utstmt
Specifies the utility control statements.

utstmt is an input parameter of type CLOB(2G) 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.

You can specify a CLOB file reference variable for this parameter. However, the referenced data set must be in variable record format. For example, suppose that you want DSNUTILV to use LOAD utility statements that were generated by the UNLOAD utility. You can pass these statements to DSNUTILV by using a file reference variable. However, you must first convert the data set that contains these statements to variable record format. (By default, UNLOAD creates these data sets in fixed record format.) Then, make any other necessary changes, such as adding templates for the LOAD data sets.

Restriction: Any keywords, names, or constants in the utility control statement cannot be larger than 32,704 bytes.

Because conversion between UNICODE and EBCDIC is not always exact, some constants (such table names, index names, column names, and constant values) can be incorrectly converted. In such cases, use hexadecimal constants. For example, use X'31' instead of the UNICODE value of character '1'.

For more information, see:

retcode
Specifies the utility highest return code.

retcode is an output parameter of type INTEGER.

Terminating or restarting a utility with DSNUTILV

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.

For more information, see WLM_REFRESH stored procedure.

Sample program for calling DSNUTILV

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

  • Start of changeJob DSNTEJ6X compiles, link-edits, binds, and runs sample C-language caller program DSN8EDA, which invokes the DSNUTILV stored procedure to execute a utility. DSN8EDA can pass either a utility statement or the name of a data set that contains the utility statement. End of change

For more information, see Job DSNTEJ6R.

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

For more information, see DIAGNOSE.

End of change