DB2 Server for VSE & VM: Database Services Utility

SQL Statement Processing

The SQL statements that you can enter from the (input) control file are:

• SELECT statements (without an INTO clause or host variables)
• Data manipulation statements
• Data control statements
• Data definition statements
• Authorization statements.

You cannot enter some SQL statements in the Database Services Utility (input) control file. You receive an error message if you attempt to use these statements:

• SELECT statements with an INTO clause or host variables
• Cursor management statements (DECLARE, OPEN, FETCH, CLOSE)
• Commands that support dynamic SQL statement execution (PREPARE, DESCRIBE, EXECUTE, EXECUTE IMMEDIATE)
• Exception handling statements (WHENEVER statements)
• INCLUDE statements (INCLUDE SQLCA or INCLUDE SQLDA).

SQL statements entered from a Database Services Utility (input) control file must not be prefixed by EXEC SQL (as they are when embedded within application programs). In DB2 Server for VM, no information should be placed in the input record after the semicolon. This restriction does not apply to positions 73 through 80 of a control-file record when the Database Services Utility control file is assigned as a CMS file. In DB2 Server for VSE, use a semicolon to indicate the end of each SQL statement. Do not use the continuation character required by ISQL for SQL statements that span record boundaries. Except for positions 73 through 80, no information should be placed in the input record after the semicolon.

SELECT and Arithmetic Exceptions

Database Services Utility flags arithmetic exceptions in outer select-lists by filling the corresponding fields with number (or pound) signs (#). When one or more of these exceptions occur, (for example, when a number is divided by zero) a Database Services Utility and an SQLCODE message are issued after the results are retrieved. If more than one arithmetic exception occurs, only one message--referring to the first exception--is issued. This is to alert you to any warnings occurring during Database Services Utility (input) control file processing.

Processing Summary

Figure 78 summarizes the Database Services Utility processing that you can perform on tables and views.

Figure 78. Database Services Utility Processing

Notes:

1. See Database Services Utility UNLOAD record format description in UNLOAD DBSPACE and UNLOAD TABLE.

2. View definitions must not violate any of the rules related to SQL INSERT processing. In general, the view must be:
   • Defined only on one table
   • Defined to include all the NOT NULL columns of the underlying table
   • Defined without using virtual column definitions.

3. If SAM files created by Database Services Utility UNLOAD processing are used as input to Database Services Utility DATALOAD processing, the rules below must be followed. (This description assumes that you are thoroughly familiar with the Database Services Utility UNLOAD record formats and the contents of each Database Services Utility UNLOAD record type.)
   • All input data records selected for DATALOAD processing must contain data fields in the same position in each data record up to the last position of a data record type referenced by DATALOAD commands.

     This rule implies that all input data record varying-length fields must have the same length. Any fields that permit nulls must all be null or must all contain data.

   • The DATALOAD input-record-id-clause must be employed to select at least the UNLOAD record type 60 for DATALOAD processing.
   • The position of the column data in the input data record must be computed from the following information:
     • Order of column definition in source table or view
     • Actual length of column data at time of UNLOAD
     • Fixed "overhead" in data records created by Database Services Utility UNLOAD processing.