Utility control statements

Control statement coding rules

Utility control statements 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.

• Character set encoding: All utility control statements in a data set must be written entirely in a single character set. They can be written in EBCDIC (code page 500) or 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. Db2 issues an informational message (DSNU1044I or DSNU1045I) to identify the character set that it is using to process the statement.

  Avoid the following EBCDIC characters as the first character in the input data set:

  ¢ . < ( + | &

  These EBCDIC characters have the same hexadecimal code point value as the Unicode UTF-8 characters J to P. Therefore, the control statement can be misinterpreted as Unicode. This misinterpretation causes a utility syntax error. However, these characters might cause Unicode to EBCDIC translation errors and abends before the syntax error is detected. In this case, 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 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).

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

  Although Db2 does not limit the number of utility statements that you can include in a single input stream (SYSIN) , running too many statements might result in failures or long-running jobs, depending on available resources. For example, running thousands of EXEC SQL statements can take unacceptable amount of time or fail.

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

  For example, the following statement shows two comments:

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

  For example, the following statement shows the concatenation operator:

  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 || operator in an EXEC SQL control statement is ignored 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"

• RBA and LRSN: When specifying a log point in a utility statement, specify an RBA value for a non-data sharing environment and specify an LRSN for a data sharing environment.

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 into each SYSIN record.