Two kinds of commands that you can specify in the Database Services Utility are Database Services Utility commands and SQL statements.
The difference between an SQL statement and a Database Services Utility command is that a Database Services Utility command can only be issued within the Database Services Utility. If you try to issue a Database Services Utility command outside of the utility itself, it fails. On the other hand, an SQL statement can be issued in both ISQL and the Database Services Utility.
Here is a summary of the Database Services Utility commands:
Control commands are entered by means of one or more 80-byte input records in the (input) control file. The utility usually reads only the first 72 positions of these command records; you can use positions 73 through 80 for sequence numbers. When control command input is being read directly from a DB2 Server for VM terminal, all 80 positions of the command record can contain command information. Some Database Services Utility commands allow you to place data within the (input) control file; these data records are not restricted to the first 72 positions and can have information in all 80 positions.
In DB2 Server for VSE, lowercase information supplied in an input control card file and read by the Database Services Utility is not converted to uppercase by Database Services Utility processing. ISQL, on the other hand, converts lowercase information to uppercase. Use uppercase in the input control card file to avoid a case mismatch, especially for a table or dbspace name. In DB2 Server for VM, lowercase information supplied in commands read by the utility is converted to uppercase only when the control file (SYSIN) is assigned to the terminal. If you require lowercase information and the utility reads commands from the terminal, specify LOWCASE when you issue a CMS FILEDEF command to define the control file (SYSIN). Alternatively, you can use the CMS SET INPUT xx yy command to reset the hexadecimal code xx to the hexadecimal code yy. Refer to the VM/ESA: CMS Command Reference manual for more information.
Except where noted, the control commands can span multiple 80-byte input records. Individual keywords or parameter values must never span input records, or a Database Services Utility processing error results. For example, these records are correct:
Figure 75. Example of Correct Records
*------------------------------------------------------------------------------* | 1 *---------------* col 72 80 | | |-------------------- | INPUT RECORDS | -------------------------| | | | | *---------------* | | | | V V V | | SELECT MFB001 | | EMPNO, LASTNAME FROM SQLDBA.EMPLOYEE; MFB002 | | *-----------* | | | Correct. | | | *-----------* | | | *------------------------------------------------------------------------------*
MFB001 and MFB002 are sequence numbers that the Database Services Utility ignores. Note that SELECT ends in position 72 in the above example. Conceptually, the Database Services Utility inserts a single blank character between input records; the above records are interpreted as:
SELECT EMPNO, LASTNAME FROM SQLDBA.EMPLOYEE;
The following input records are incorrect:
Figure 76. Example of Incorrect Records
*------------------------------------------------------------------------------* | 1 *---------------* col 72 80 | | |-------------------- | INPUT RECORDS | -------------------------* | | | | *---------------* | | | | V V V | | SELE MFB001 | | CT EMPNO, LASTNAME FROM SQLDBA.EMPLOYEE; MFB002 | | *----------------------* | | | Incorrect! | | | | Don't split keywords | | | *----------------------* | | | *------------------------------------------------------------------------------*
The Database Services Utility inserts a blank after column 72, and thus interprets the input records as:
SELE CT EMPNO, LASTNAME FROM SQLDBA.EMPLOYEE;
The Database Services Utility does not recognize SELE as a Database Services Utility command, and an error results.
There is an exception to the rule that individual keywords or parameter values must not span input records. This exception occurs when a parameter is enclosed in either single (') or double (") quotation marks. For these parameters, the Database Services Utility does not insert a blank after position 72. Consider the following example in which a character string constant spans multiple input records. The character constant is delimited by a single quotation mark ('):
Figure 77. Example of a Character String Constant Spanning Multiple Input Records
*------------------------------------------------------------------------------* | 1 *---------------* col 72 80 | | |-------------------- | INPUT RECORDS | -------------------------| | | | | *---------------* | | | | V V V | | SELECT 'AVERA MFB001 | | GE', AVG(BONUS) FROM SQLDBA.EMPLOYEE; MFB002 | | *----------------------* | | | Correct. | | | | You can split | | | | quoted strings. | | | *----------------------* | | | *------------------------------------------------------------------------------*
The Database Services Utility interprets the above records as:
SELECT 'AVERAGE', AVG(BONUS) FROM SQLDBA.EMPLOYEE;
Each Database Services Utility command or SQL statement must begin on a new (input) control file input record.
| DB2 Server for VM |
|---|
|
Database Services Utility commands are terminated by a semicolon, by the start of the next command, or by the end of the input control records. Terminate Database Services Utility commands with a semicolon when Database Services Utility control command input is being read directly from a terminal. In the Database Services Utility environment, SQL statements must be terminated with a semicolon. Do not use the SQL continuation character (required by ISQL) in an SQL statement that spans record boundaries in either a batch or an interactive environment. |
Database Services Utility processing ends when all the input records are processed.
|You cannot reorganize a catalog index by using the REORGANIZE INDEX |command. To reorganize the catalog index in VM, use the SQLCIREO |utility. In VSE, set the STARTUP initialization parameter to one to |reorganize the catalog index.
| Note: | You can only reorganize a primary key index or a unique index by using the ALTER TABLE ACTIVATE PRIMARY KEY and ALTER TABLE ACTIVATE UNIQUE statements. |