DB2 Server for VSE & VM: Database Services Utility

Set-Item Commands

SET AUTOCOMMIT

SET AUTOCOMMIT Format



Format:

                   .-(-.    .-OFF--.  .-)-.
>>-SET AUTOCOMMIT--+---+----+-ON---+--+---+--------------------><

The SET AUTOCOMMIT command allows you to activate or suppress the execution of SQL COMMIT statements by the Database Services Utility. This command cannot span input records.

OFF
identifies that you do not want the utility to commit database changes after control commands are successfully processed. You must supply SQL COMMIT statements in the Database Services Utility input stream at the points at which you commit the changes.

In this mode of operation, the only execution of an SQL COMMIT by the utility is at the end-of-program after all control commands have been successfully processed.

ON or OFF must be specified in this command. If you do not supply a SET AUTOCOMMIT command in the input records, the utility operates as if you had issued SET AUTOCOMMIT OFF.

ON
identifies that you want the utility to run an SQL COMMIT command after the successful processing of any control command (that is, Database Services Utility commands or SQL statements) except those noted below. The utility ensures that any database changes made before the receipt of the AUTOCOMMIT ON command are committed before processing continues.

In AUTOCOMMIT ON mode, the Database Services Utility does not run an SQL COMMIT command after the successful processing of these commands: 

         SQL Statements    Database Services Utility Commands
 
         COMMIT            COMMENT
         CONNECT           SET AUTOCOMMIT OFF
         LOCK              SET ERRORMODE
         ROLLBACK          SET FORMAT
                           SET ISOLATION
                           SET LINECOUNT
                           SET LINEWIDTH
                           SET UPDATE STATISTICS

SET ERRORMODE

SET ERRORMODE Format



Format:

                  .-(-.    .-OFF------.  .-)-.
>>-SET ERRORmode--+---+----+-ON-------+--+---+-----------------><
                           '-CONTINUE-'

The SET ERRORMODE command allows you to:

This command cannot span input records. If you do not supply a SET ERRORMODE command in the input records, the utility operates as if you had issued SET ERRORMODE OFF.

OFF
causes the utility to resume execution of Database Services Utility commands and SQL statements after a command processing error has occurred or to terminate error mode CONTINUE processing.

Any subsequent command processing errors cause the utility to execute an SQL ROLLBACK statement and enter Database Services Utility error mode processing.

Notes:

  1. The SET ERRORMODE OFF command also terminates the processing mode established by the Database Services Utility SET AUTOCOMMIT ON command.

  2. A SET ERRORMODE OFF command is not used:
    1. If the error mode is already off.
    2. If a serious database error has previously occurred. A serious error, by definition, causes all subsequent SQL statements to terminate. Thus, a SET ERRORMODE OFF command in this case has no effect.

ON
causes the utility to suspend execution of following Database Services Utility commands and SQL statements. A SET ERRORMODE ON command terminates:

When a SET ERRORMODE ON command is processed, the utility initiates error mode processing. While in error mode, the Database Services Utility displays commands in the report or message file listing and performs syntax checking on Database Services Utility commands. No SQL statements or Database Services Utility commands (except SET ERRORMODE OFF or SET ERRORMODE CONTINUE) are executed during error mode processing. Therefore, any errors that result from command execution (for example, SQL syntax errors and data file errors) are not detected.

Database Services Utility error mode processing is also entered when the utility detects a command processing error, and the utility is operating in normal command processing mode. If the processing mode established by a SET ERRORMODE CONTINUE command is in effect, and a command processing error is detected, Database Services Utility error mode processing is entered only when the error is a serious database error.

Notes:

  1. A SET ERRORMODE ON command also terminates the processing mode established by the SET AUTOCOMMIT ON command.

  2. A SET ERRORMODE ON command is not used if Database Services Utility error mode processing is already in effect.

CONTINUE
suppresses the normal Database Services Utility error processing after an error is detected; that is, Database Services Utility error mode processing is not performed. An SQL ROLLBACK statement is not executed, and the utility continues to process Database Services Utility commands and SQL statements after a command processing error occurs. If any errors occur, and they occur only while SET ERRORMODE CONTINUE is in effect, the Database Services Utility issues error message ARI8007I when the Database Services Utility command processing ends.

Notes:

  1. If a serious database error occurs while the processing mode established by a SET ERRORMODE CONTINUE command is in effect, an SQL ROLLBACK is executed, and the utility enters error mode processing. A serious database error, by definition, causes all subsequent SQL statements to terminate.

  2. After a SET ERRORMODE CONTINUE command is processed, because the Database Services Utility does not execute an SQL ROLLBACK command does not mean that the logical unit of work is always in progress. Certain SQL statement errors cause the logical unit of work to be terminated by the database manager. If one of these errors occurs, all database changes made during the logical unit of work or since the last SQL COMMIT statement are lost.

    To control the logical unit of work after a SET ERRORMODE CONTINUE command is processed, you can use SQL COMMIT statements or the Database Services Utility SET AUTOCOMMIT ON command.

  3. The SET ERRORMODE CONTINUE command is not used:
    1. If the processing mode established by a previous SET ERRORMODE CONTINUE command is still in effect
    2. If a serious database error has previously occurred.

  4. If the Database Services Utility is not in error mode when a SET ERRORMODE CONTINUE command is encountered, the Database Services Utility AUTOCOMMIT processing status is not changed.

    If the Database Services Utility is in error mode when a SET ERRORMODE CONTINUE command is encountered, the Database Services Utility AUTOCOMMIT processing status is set to off.

  5. Database Services Utility end-of-program COMMIT processing is based on the current status of the command processing. That is, if Database Services Utility error mode processing and the SET AUTOCOMMIT ON command processing mode are not in effect, Database Services Utility end-of-program COMMIT processing is performed.

  6. All SQL statements are treated as one LUW when running the database with LOGMODE=N and Database Services Utility with AUTOCOMMIT=OFF and ERRORMODE=CONTINUE. If an error occurs, ALL statements are rolled back.

Note:The ERRORMODE setting has an effect on the final Database Services Utility return code. For more information, see Chapter 9, Error Handling and Debugging.

SET FORMAT

SET FORMAT Format



Format:

               .-(-.    .-CB--.  .-)-.
>>-SET FORMAT--+---+----+-CL--+--+---+-------------------------><
                        '-LO--'

This command allows you to identify whether the Database Services Utility should use column or block format, column or list format, or only list format for SQL SELECT statement results.

If the format is not specified, Database Services Utility processing uses column or block format for SQL SELECT statement output. This command cannot span input records.

The SET FORMAT command overrides the formats specified by either a parameter list format control parameter or the default column or block format. This command specification remains in effect until another SET FORMAT command is encountered and successfully processed, or when Database Services Utility processing ends.

CB
identifies that either column or block format should be used. The column format is used when a report or message file record can contain all column names or column data for a selected row. The block format is used when a report or message file record cannot contain all column names or column data for a selected row. Column or block format is the default if you do not override it by supplying either a format control parameter--FORMAT(CL) or FORMAT(LO)--or a SET FORMAT command.

CL
identifies that either column or list format should be used. The column format is used when a report or message file record can contain all column names or column data for a selected row. The list format is used when a report or message file record cannot contain all column names or column data for a selected row.

LO
identifies that only list format is to be used. The list format is used even when the report or message file record can contain all column names or column data for a selected row.

SET ISOLATION

SET ISOLATION Format



Format:

                  .-(-.    .-RR--.  .-)-.
>>-SET ISOLation--+---+----+-CS--+--+---+----------------------><
                           '-UR--'

This command allows you to control the isolation level used for Database Services Utility processing. Every time the utility is run, the isolation level is initialized to repeatable read (RR). SQL processing through the utility is performed at this isolation level until a SET ISOLATION command is encountered. The utility sets the isolation level to the value specified in the command and processes at this level until another SET ISOLATION command is executed or Database Services Utility processing ends. The other isolation level settings are cursor stability (CS) and uncommitted read (UR). This command cannot span input records.

If you are accessing a non-DB2 Server for VM application server, or if you are using DRDA flow, the isolation level for the Database Services Utility is always set to CS and the SET ISOLATION command has no effect.

RR
is used to protect a logical unit of work from uncommitted updates of another logical unit of work. Also, no other logical unit of work can modify any row that has been read by this logical unit of work.

CS
is used to protect a logical unit of work from uncommitted updates of another logical unit of work. After data is read, the data is freed for others to update before the end of the logical unit of work.

Use this setting only when the data is read or when you are the only user authorized to update the data.

UR
is used when protection from other logical units of work is not required. Data can be read without waiting for other logical units of work that are updating the data. Reading data will not prevent other application processes from updating it.

Note that data integrity may be compromised because read-only access to uncommitted data is allowed.

This setting applies only to read-only operations (SELECTs, DATAUNLOAD and UNLOAD) against data in public dbspaces with ROW or PAGE level locking. For other operations (UPDATE, DELETE, INSERT, DATALOAD, and LOAD), the rules of CS apply.

For dbspaces with DBSPACE level of locking, the rules of RR apply.

Recommended settings for Database Services Utility processing:

SET LINECOUNT, SET LINEWIDTH

SET LINECOUNT (LINEWIDTH) Format



Format:

>>-SET---------------------------------------------------------->
 
>-----+-LineCount--(ccc)---+------------------------------+-+--><
      |                    |               .-80---.       | |
      |                    |               +-120--+       | |
      |                    '-LineWidth--(--+-www--+---)---' |
      '-LineWidth--(www)---+-----------------------------+--'
                           |               .-60--.       |
                           '-LineCount--(--+-ccc-+---)---'
Note:80 is valid in DB2 Server for VM only.

The SET LINECOUNT/LINEWIDTH command allows you to:

This command cannot span input records.

LINECOUNT(ccc) or LC(ccc)
If LINECOUNT(ccc) or LC(ccc) is specified, the value ccc is the number of lines per page of printed output written to the Database Services Utility report or message file. The value ccc can range from 10 to 32767; the default value is 60.

LINEWIDTH(www) or LW(www)
If LINEWIDTH(www) or LW(www) is specified, the value www is the maximum number of print data positions used in a Database Services Utility report or message file record containing SQL SELECT statement output. The default value for www is 120. In DB2 Server for VM, if the Database Services Utility message file (ddname=SYSPRINT) is assigned to the terminal, the number of print data positions used for the SQL SELECT statement defaults to 80.

The value www can range from 60 to 256, but cannot be equal to or greater than the logical record length of the Database Services Utility message file.

Notes:

  1. The Database Services Utility always supplies an American Standards Association (ASA) control character in the first position of the print record. The second through nth positions of the print record are the print data positions. If the value www+1 is less than the print record length, all unused print data positions in the print record contain a blank (hex 40).

  2. The DB2 Server for VSE Database Services Utility report record length is always 121.

  3. The minimum DB2 Server for VM Database Services Utility message file record length is 81. If the Database Services Utility control parameter PAGECTL(NO) is specified, the minimum message file record length is 80.

  4. If the value www is equal to or greater than the print record length, an error occurs.

SET UPDATE STATISTICS

SET UPDATE STATISTICS Format



Format:

        .-UPDATE-.              .-(-.    .-ON--.  .-)-.
>>-SET--+--------+--STATISTICS--+---+----+-OFF-+--+---+--------><

The SET UPDATE STATISTICS command allows you to control the automatic statistics collection performed during Database Services Utility RELOAD TABLE, RELOAD DBSPACE, and DATALOAD TABLE command processing. This command cannot span input records. If you do not supply a SET UPDATE STATISTICS command in the input records, the utility operates as if you had issued SET UPDATE STATISTICS ON.

The SET UPDATE STATISTICS command is not supported on a non-DB2 Server for VM application server or if you are using DRDA flow.

ON
causes the utility to automatically collect statistics for each table loaded during Database Services Utility RELOAD TABLE, RELOAD DBSPACE, and DATALOAD TABLE command processing. This is the default mode of processing.

The Database Services Utility writes message ARI8980I for each table or dbspace loaded by a RELOAD TABLE, RELOAD DBSPACE, or DATALOAD TABLE command. The message informs you that the statistics were collected while the data was loading.

The Database Services Utility writes message ARI8996I and issues an SQL UPDATE STATISTICS statement for each table loaded if you are using the DATALOAD command and when any one of the following is true:

The SQL UPDATE STATISTICS FOR TABLE statement issued by the Database Services Utility must read the whole table to update the internal DB2 Server for VSE & VM statistics for the table. The statistics are updated based on the current contents of the table and dbspace to which the table is assigned. Refer to the DB2 Server for VSE & VM Application Programming manual and the DB2 Server for VSE & VM Database Administration manual for a description of the SQL UPDATE STATISTICS statement processing.

OFF
suppresses the Database Services Utility statistics collection performed

during RELOAD TABLE, RELOAD DBSPACE, and DATALOAD TABLE command processing.

A SET UPDATE STATISTICS OFF remains in effect until a SET UPDATE STATISTICS ON command is processed or until the Database Services Utility is restarted.
Note:If tables are loaded by the Database Services Utility RELOAD TABLE, RELOAD DBSPACE, and DATALOAD TABLE commands while SET UPDATE STATISTICS OFF is in effect, you must issue an SQL UPDATE STATISTICS statement for the table or dbspace to update the internal statistics.

To avoid the processing overhead associated with an SQL UPDATE STATISTICS statement processing, you can suppress the normal Database Services Utility UPDATE STATISTICS when you are using the DATALOAD TABLE command to load a few records into a table that currently contains a larger number of records. See Update Statistics Considerations for details.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]