Utility control statements

Control statement coding rules

DB2® typically reads utility control statements from the SYSIN data set. DB2 can read LISTDEF control statements from the SYSLISTD data set and TEMPLATE control statements from the SYSTEMPL data set. The statements in these data sets must obey the following rules:

• Fixed-length records: If the records are 80-character fixed-length records, DB2 ignores columns 73 through 80.
• Statements that span records: The records are concatenated before they are parsed; therefore, a statement or any of its syntactical constructs can span more than one record. No continuation character is necessary.

  However, if the input data set contains variable-length records, DB2 might interpret the part of a statement that is in column 1 as the continuation of the statement from the previous record. To avoid syntax errors, ensure that all syntactical constructs in utility control statements are properly delimited. Doing so is especially important for the first character in each record of a data set with variable-length records.

• Character set encoding: All control statements in a data set must be written entirely in a single character set. The following two character sets are supported: EBCDIC (code page 500) and Unicode UTF-8 (code page 1208). DB2 automatically detects and processes Unicode UTF-8 control statements if the first character of the data set is one of the following characters:
  • A Unicode UTF-8 blank (x'20')
  • A Unicode UTF-8 dash (x'2D')
  • A Unicode UTF-8 uppercase A through Z (x'41' through x'5A')
  In all other cases, the control statement data set is processed as EBCDIC. An informational message is issued to identify the character set that is being processed. Message DSNU1044I indicates that the statement is being processed as EBCDIC. Message DSNU1045I indicates that the statement is being processed as Unicode.

  The following EBCDIC characters have the same hexadecimal code point value as the Unicode UTF-8 characters J to P:

  

  ¢ . < ( + | &

  

  If any of these characters are the first character in the input data set, the control statement can be misinterpreted as Unicode. This error is a utility syntax error. However, these characters might cause Unicode to EBCDIC translation errors and abends before the syntax error is detected. After the syntax error is detected, message DSNU005I might contain indecipherable statements and message DSNU082I might identify an indecipherable keyword.

  Related information:

  • Code pages and CCSIDs
  • EBCDIC
  • Unicode
  • DSNU1044I
  • DSNU1045I
  • DSNU005I
  • DSNU082I
• Syntax: The utility statement must start with the syntax for a utility. Other syntactical constructs in the utility control statement describe options; you can separate these constructs with an arbitrary number of blanks.
• Options: The options that you can specify after the online utility name depend on which online utility you use. To specify a utility option, specify the option keyword, followed by its associated parameter or parameters, if any. The parameter value can be a keyword. You need to enclose the values of some parameters in parentheses. The syntax diagrams for utility control statements show parentheses where they are required.

  Where the syntax of each utility control statement is described, parameters are indented under the option keyword that they must follow. The following option is a typical example:

  WORKDDN ddname

  Specifies a temporary work file.

  ddname is the data set name of the temporary file.

  The default value is SYSUT1.

  In the example, WORKDDN is an option keyword, and ddname is a variable parameter. You can enclose parameter values in parentheses, but parentheses are not always required. In this case, you can specify the temporary work file as either WORKDDN SYSUT1 or WORKDDN (SYSUT1).

  For the specific syntax and options for each utility, see Control statement syntax.

  For sample control statements for each utility, see Control statement samples.

• Delimiter character and decimal character: In a utility control statement, when you specify multiple numeric values that are meant to be delimited, you must delimit these values with a comma (","). You must use this delimiter regardless of the definition of DECIMAL in the application defaults load module (either DSNHDECP or a user-specified application defaults load module). Likewise, when you specify a decimal number in a utility control statement, you must use a period ("."), regardless of the definition of DECIMAL in the application defaults load module.
  Related information:

  • DECIMAL POINT IS field (DECIMAL DECP value)
  
• Multiple utility control statements: You can specify more than one utility control statement in the SYSIN stream. However, if any of the control statements return a return code of 8 or greater, the subsequent statements in the job step are not executed.
• Comments: You can enter comments within the SYSIN stream. Comments must begin with two hyphens (--) and are subject to the following rules:
  • You must use two hyphens on the same line with no space between them.
  • You can start comments wherever a space is valid, except within a delimiter token.
  • The end of a line terminates a comment.

  Two comments are shown in the following statement:

  

  // SYSIN DD *
  RUNSTATS TABLESPACE DSNDB06.SYSDDF  -- COMMENT HERE
  -- COMMENT HERE
  /*

  
• The concatenation operator: Utility control statements support the || concatenation operator. The operator is allowed between two delimited character strings or between two non-delimited character strings. (Delimited character strings are enclosed in double quotation marks.) The result is a character string that consists of the string that is after the operator concatenated to the string that precedes the operator. The operation is shown in the following statement:

  string1  ||  string2

  Both string1 and string2 must be syntactically correct within each SYSIN input record. Quotation marks must be balanced within each string. If DBCS characters are used, shift-out and shift-in characters must be balanced within each string. Any one multi-byte character must be contained entirely within a single SYSIN record.

  The || operator must be entered as a stand-alone token, with one or more blanks before and after it. It can be entered on the same input record as "string1", alone on an input record, or on the same input record with "string2". This operator functions at the token level before any context is detected or semantic meaning is applied. An example utility statement is shown in the following statement:

  COPY  INDEX  
         "A" ||
         "B"
  results in:
         COPY INDEX   "AB"

  The utility || operator is ignored in an EXEC SQL control statement by utility processing, because the operator has an existing SQL meaning. The operators remain part of the SQL statement for subsequent processing by SQL.

  Another example of the || concatenation operator is shown in the following statement:

  LOAD INTO TABLE
  CREA ||
  TOR.
  "TABL" ||
  "ENAME"                                                     

  In this example, the strings CREA and TOR are non-delimited, and the strings TABL and ENAME are delimited by double quotation marks. The processed output of this example is equivalent to the following statement:

  LOAD INTO TABLE CREATOR."TABLENAME"

Tips for using multi-byte character sets in control statements

Multi-byte character sets can be difficult to work with in fixed 80-byte SYSIN data sets. Long object names and long character literals might not fit on a single line.

Where possible, avoid having to break object names or character literals by using the following techniques:

• Use a SYSIN data set with variable length records or sufficiently large record length.
• Use shorter object names. The longer the name, the more likely continuation issues arise.
• If possible, process the object by space name (table space or index space) and avoid specifying long multi-byte table and index names in utility syntax.

If necessary, use one of the following continuation techniques:

• Shift the starting point of the string to the left or right within the input record so that a complete multi-byte character ends in column 72. Continue with the next character in column 1 of the next input record.
• Separate qualified object names into two parts following the dot ".", which separates the qualifiers. Separating long names into multiple parts makes it easier to follow the continuation rules. This technique cannot be used in the EXEC SQL utility, which must follow both utility and SQL syntax rules.
• Use the || concatenation operator to divide long identifiers into two or more parts that fit properly into each SYSIN record.

Control statement syntax

Use the following table to find the control statement syntax for the particular utility that you want to use.

Control statement samples

Use the following table to find sample control statements for the particular utility that you want to use.