DB2 Server for VSE & VM: Quick Reference


Database Services Utility Commands

The Database Services Utility (DBS Utility) processes commands that are unique to the DBS Utility and certain SQL statements.


Starting and Stopping the DBS Utility

Before using the DB2 Server for VM or DB2 Server for VSE database manager for any application, initialize your database. The method of initialization depends on whether the DBS Utility is run in multiple user mode or single user mode.

Starting the DBS Utility - VM Users

SQLINIT EXEC

This portion of the SQLINIT command should be issued before the SQLDBSU EXEC to initialize DB2 Server for VM database access and load the multiple user support system routines.

>>-SQLINIT--Dbname----(dbname)---+--------------------+--------><
                                 '-dcssID--(dcss_id)--'
 

For more information about using the SQLINIT EXEC, see the DB2 Server for VSE & VM Database Administration manual.

SQLDBSU EXEC

Used to invoke the DBS Utility in single or multiple user mode.

>>-SQLDBSU----+----------------------------------+-------------->
              '-sysIN--(--+-Reader--------+---)--'
                          +-Terminal------+
                          '-| fileparms |-'
 
>-----+-------------------------------------+--Dbname----------->
      '-sysPRint--(--+-Printer-------+---)--'
                     +-Terminal------+
                     '-| fileparms |-'
 
                    (1)                             (1)
>-----(server_name)--------+--------------------+--------------->
                           '-dcssID--(dcss_id)--'
 
                                     (1)
>------+-------------------------+------------------------------>
       '-LOGmode--(--+-A-+---)---'
                     +-L-+
                     +-N-+
                     '-Y-'
 
                                     (1)
>------+--------------------------+----------------------------><
       '-PARMID--(--file_name--)--'
 
fileparms
 
|---file_name----+----------------------------+-----------------|
                 '-file_type--+------------+--'
                              '-file_mode--'
 

Notes:

  1. Only applicable to single user mode.

Exiting from the DBS Utility - VM Users

Batch Mode

If a control file is supplied with the SYSIN option, an exit is made from the utility automatically after all the commands in the control file are processed.

Interactive Mode

If a control file is not supplied, the DBS Utility is used interactively. To exit, type the following command, and press ENTER.

  EXIT;

Any uncommitted work is committed, and an exit is made from the utility to the conversational monitor system (CMS).

Starting the DBS Utility - VSE Users

Multiple User Mode

To invoke the DBS Utility, use the following EXEC statement as part of the JCL:

  // EXEC PGM=ARIDBS,SIZE=AUTO

Single User Mode

To invoke the DBS Utility, use either:

  // EXEC PROC=ARISDBSD

or

  // EXEC ARISQLDS,SIZE=AUTO,                    X
  //   PARM='SYSMODE=S,LOGMODE=N,PROGNAME=ARIDBS'

Exiting from the DBS Utility - VSE Users

The DBS Utility automatically ends after all commands in the control file are processed; that is, when /* is encountered.


COMMENT

Documents input by supplying the Database Services Utility COMMENT commands at appropriate points within the Database Services Utility control command input stream. The utility displays the comments in the Database Services Utility message file listing.

>>-COMMENT--'string_constant'----------------------------------><
 

CREATE SCHEMA

The sequential SCHEMA input file contains one CREATE SCHEMA statement, which is the first statement in the file.

                                                  .-;-.
>>-CREATE SCHEMA AUTHORIZATION--authorization_id--+---+--------->
 
      .--------------------------------.
      V                                |
>--------+--------------------------+--+-----------------------><
         |                  .-;-.   |
         '-schema_statement-+---+---'
 

DATALOAD

Loads or adds rows into existing tables from data in a sequential input file. DATALOAD and its subcommands are on more than one input record with each record in general containing data for a table row. Refer to the syntax diagram on page ***.

Table-Column-ID-Subcommand (TCI)

Identifies the location of the input records of the data for a table column. Each table-column-id-subcommand occupies a separate input record. Data must be in the same record positions in all records that relate to the table. Refer to the syntax diagram on page ***.

>>-DATALOAD TABLE--(--table_name--)----------------------------->
 
                                                                          (5)
>----+-----------------------------------------------------------------+------->
     '-IF--POS--(--startpos--+--------------+--)--+- = --+---constant--'
                             |         (2)  |     +- <> -+
                             '--endpos------'     +- ^= -+
                                                  +- < --+
                                                  +- > --+
                                                  +- <= -+
                                                  '- >= -'
 
      .-----------------------------------.
      V                                   |
>--------| table-column-id-subcommand |---+--------------------->
 
>----| infile-subcommand |--+-----------------------------------------------+>
                            |  .--------------------------.                 |
                            |  V                   (3)    |           (4)   |
                            '----user_data_records--------+---ENDDATA-------'
 
>--------------------------------------------------------------><
 
table-column-id-subcommand
 
     .---------------------------------------------------------------------------------.
     V                                  .-CHARacter--.                                 |
|------col_name--startpos--+---------+--+------------+---+-------------------------+---+->
                           '--endpos-'  '-data_type--'   |               (1)  (6)  |
                                                         '-| nc-clause |-----------'
 
>---------------------------------------------------------------|
 
nc-clause
 
     (1)                          (6)
|----------+-CURRENT DATE------+-------------------------------->
           +-CURRENT TIME------+
           +-CURRENT TIMESTAMP-+
           '-NULL--------------'
 
                                                                         (6)
>------+--------------------------------------------------------------+----->
       | .-IF-.                                                       |
       '-+----+--POS--(--startpos--+---------+--)--+-=--+---constant--'
                                   '--endpos-'     +-<>-+
                                                   +-^=-+
                                                   +-<--+
                                                   +->--+
                                                   +-<=-+
                                                   '->=-'
 
>---------------------------------------------------------------|
 

Notes:

  1. nc-clause is the short form for null-current-clause.

  2. No blanks are allowed between startpos, hyphen, and endpos.

  3. The user-data-records contain the data referenced by the preceding DATALOAD subcommands.

  4. ENDDATA identifies the end of user-supplied data embedded in the control file. The command is valid if the previous DBS Utility command was an INFILE(*) subcommand.

  5. These options must appear on the same physical line as DATALOAD TABLE.

  6. This clause must appear on the same physical line.

Infile-subcommand

Identifies the file containing the data referenced by the preceding DATALOAD and TCI subcommands. When INFILE is followed by an asterisk, the data is in the control file and immediately follows the subcommand.

infile-subcommand - VM

>>-INFILE------------------------------------------------------->
 
                                                                                                  (3)
>-----+-(--*--+----------------------------------+---| options |-----------------------------)-+----->
      |       |               .-NO--.     (3)    |                                             |
      |       '-CONTINUED--(--+-----+---)--------'                                             |
      |                       '-YES-'                                                          |
      '-(ddname)-------------------------------------------------------------------------------'
 
>------+------------------------+---+-------------------------+-><
       '-COMMITCOUNT--(ccount)--'   '-RESTARTCOUNT--(rcount)--'
 
options
 
|--+-----------------------------------+------------------------|
   |          .-YES--.     (1)  (2)    |
   '-LIST--(--+-NO---+---)-------------'
 

Notes:

  1. No blanks are allowed between the keywords CONTINUED and LIST and the value specified for CONTINUED and LIST.

  2. LIST can be specified before CONTINUED.

  3. These options must appear on the same physical line as INFILE.

infile-subcommand - VSE

>>-INFILE------------------------------------------------------->
 
>-----+-(--*--+---------------------------------------+---+-----------------------------------+---)--+>
      |       |           (1)  (2)      .-NO--.       |   |      (1)  (2)      .-YES--.       |      |
      |       '-CONTINUED------------(--+-----+---)---'   '-LIST------------(--+------+---)---'      |
      |                                 '-YES-'                                '-NO---'              |
      |                                                                                              |
      '-(--ddname--+------------------------+---)----------------------------------------------------'
                   '-| tape/disk options |--'
 
>-----+------------------------+--+-------------------------+--><
      '-COMMITCOUNT--(ccount)--'  '-RESTARTCOUNT--(rcount)--'
 

Notes:

  1. No blanks are allowed between the keywords CONTINUED and LIST and the value specified for CONTINUED and LIST. LIST can be specified before CONTINUED.

  2. LIST can be specified before CONTINUED.

 
tape/disk options
 
|--+------------------------+----------------------------------->
   |          .-2048--.     |
   '-BLKSZ--(-+-size--+--)--'
 
>-----+----------------------------------+---------------------->
      |                 .-REWIND---.     |
      |       .-(TAPE)--+-NOREWIND-+--.  |
      '-PDEV--+-(DASD)----------------+--'
 
>-----+------------------+---+----------------+-----------------|
      '-RECFM--(format)--'   '-RECSZ--(size)--'
 

DATAUNLOAD

Selectively unloads data from tables and views to a user-defined sequential file of data.

Data-Field-Identification Subcommand

Identifies the location in the output record where the data for a column in the select-list parameter should be placed, and identifies the output record data-field data-type.

               (1)
>>-DATAUNLOAD--------------------------------------------------->
 
                                   (1)  (2)
>----interactive_select_statement;------------+------------------------------------------------+>
                                              |  .------------------------------------------.  |
                                              |  V                              (1)  (2)    |  |
                                              '----| data-field-id-subcommand |-------------+--'
 
>-----| outfile-subcommand |-----------------------------------><
 

Notes:

  1. Each of these must be a separate record.

  2. Both must appear on the same physical line.

 
data-field-id-subcommand (DFI)
 
|--+-column_reference-+--startpos--+---------+------------------>
   '-integer----------'            '--endpos-'
 
     .-CHARacter--.
>----+------------+---+---------------------+-------------------|
     '-data_type--'   '-| set-null-clause |-'
 

 
set-null-clause
 
   .-IF-.        .-SET-.
|--+----+--NULL--+-----+---------------------------------------->
 
>----POS--(--startpos--+---------+--)-- = --value---------------|
                       '--endpos-'
 

 
outfile-subcommand - VM
 
            (1)
|--OUTFILE---------(ddname)-------------------------------------|
 

Notes:

  1. The outfile-subcommand identifies the sequential output file that contains the data referenced by the preceding DATAUNLOAD subcommands.

 
outfile-subcommand - VSE
 
            (1)
|--OUTFILE--------(--ddname----+----------------+--------------->
                               '-BLKSZ--(size)--'
 
>----+---------------------------------+---+------------------+->
     |               .-NOREWIND--.     |   '-RECFM--(format)--'
     |       .-TAPE--+-REWIND----+--.  |
     '-PDEV--+-(DASD)---------------+--'
 
>----+----------------+-----------------------------------------|
     '-RECSZ--(size)--'
 

Notes:

  1. The outfile-subcommand identifies the sequential output file that contains the data referenced by the preceding DATAUNLOAD subcommands.

REBIND PACKAGE

Rebinds an existing package.

>>-REBIND PACKAGE----(package_name)----------------------------><
 

RELOAD DBSPACE

Identifies a RELOAD DBSPACE request and identifies a DBSPACE to be loaded.

VM Users

>>-RELOAD DBSPACE----(dbspace_name)---+-NEW---+----------------->
                                      '-PURGE-'
 
>----INFILE--(ddname)---+----------------------------+---------->
                        '-COMMITCOUNT--(--ccount--)--'
 
>-----+---------------------------------+----------------------->
      '-RESTARTTABLE--(--table_name--)--'
 
>-----+-----------------------------+--------------------------><
      '-RESTARTCOUNT--(--rcount--)--'
 

VSE Users

>>-RELOAD DBSPACE----(dbspace_name)---+-NEW---+----------------->
                                      '-PURGE-'
 
>----INFILE--(--ddname--+---------------------------+----------->
                        |           .-2048--.       |
                        '-BLKSZ--(--+-size--+---)---'
 
>-----+----------------------------------+--)------------------->
      |                 .-REWIND---.     |
      |       .-(TAPE)--+-NOREWIND-+--.  |
      '-PDEV--+-(DASD)----------------+--'
 
>-----+----------------------------+---------------------------->
      '-COMMITCOUNT--(--ccount--)--'
 
>-----+---------------------------------+----------------------->
      '-RESTARTTABLE--(--table_name--)--'
 
>-----+-----------------------------+--------------------------><
      '-RESTARTCOUNT--(--rcount--)--'
 

RELOAD PACKAGE

Identifies a RELOAD PACKAGE request and a package to be loaded. The UNLOAD PACKAGE output file becomes the input file for the RELOAD PACKAGE command.

VM Users

>>-RELOAD PACKAGE----(package_name)---+-NEW-----------------+--->
                                      |          .-KEEP---. |
                                      '-REPLACE--+--------+-'
                                                 '-REVOKE-'
 
>-----+--------------------------------+--INFILE----(ddname)---><
      |        .-,--------------.      |
      |        V                |      |
      '-TO--(-----server_name---+---)--'
 

VSE Users

>>-RELOAD PACKAGE----(package_name)---+-NEW-----------------+--->
                                      |          .-KEEP---. |
                                      '-REPLACE--+--------+-'
                                                 '-REVOKE-'
 
>-----+--------------------------------+------------------------>
      |        .-,--------------.      |
      |        V                |      |
      '-TO--(-----server_name---+---)--'
 
>----INFILE--(--ddname--+---------------------------+----------->
                        |           .-2000--.       |
                        '-BLKSZ--(--+-size--+---)---'
 
>-----+----------------------------------+--)------------------><
      |                 .-REWIND---.     |
      |       .-(TAPE)--+-NOREWIND-+--.  |
      '-PDEV--+-(DASD)----------------+--'
 

RELOAD TABLE

Identifies a RELOAD TABLE request and a table to be loaded.

VM Users

>>-RELOAD TABLE----(table_name)---+-PURGE---------------+------->
                                  '-NEW--(dbspace_name)-'
 
>-----+------------------------+--INFILE--(ddname)-------------->
      '-INTABLE--(table_name)--'
 
>-----+----------------------------+---------------------------->
      '-COMMITCOUNT--(--ccount--)--'
 
>-----+-----------------------------+--------------------------><
      '-RESTARTCOUNT--(--rcount--)--'
 

VSE Users

>>-RELOAD TABLE----(table_name)---+-PURGE---------------+------->
                                  '-NEW--(dbspace_name)-'
 
>-----+------------------------+-------------------------------->
      '-INTABLE--(table_name)--'
 
>----INFILE--(--ddname--+-------------------------------+------->
                        |           .-2048------.       |
                        '-BLKSZ--(--+-+------+--+---)---'
                                      '-size-'
 
>-----+----------------------------------+--)------------------->
      |                 .-REWIND---.     |
      |       .-(TAPE)--+-NOREWIND-+--.  |
      '-PDEV--+-(DASD)----------------+--'
 
>-----+----------------------------+---------------------------->
      '-COMMITCOUNT--(--ccount--)--'
 
>-----+-----------------------------+--------------------------><
      '-RESTARTCOUNT--(--rcount--)--'
 

REORGANIZE INDEX

Corrects index fragmentation and skewing of index key values without first having to drop the index and then create it by using the DROP INDEX and CREATE INDEX SQL statements.

>>-REORGANIZE INDEX----(index_name)---+----------------------+-><
                                      '-PCTFREE =--integer---'
 

SCHEMA

Specifies an authorization ID and a list of table, view, and privilege definitions using the syntax of the SQL CREATE TABLE, CREATE VIEW, and GRANT statements. The SCHEMA command reads the text of a schema file and processes the statements in it.

VM Users

>>-SCHEMA INFILE----(ddname)---+---------------------+---------><
                               '-IN--(dbspace_name)--'
 

VSE Users

>>-SCHEMA INFILE--(--ddname----| option-c |--)------------------>
 
>-----+---------------------+----------------------------------><
      '-IN--(dbspace_name)--'
 

 
option-c
 
|--+---------------------------+---+----------------------------------+->
   |           .-2000--.       |   |                 .-REWIND---.     |
   '-BLKSZ--(--+-size--+---)---'   |       .-(TAPE)--+-NOREWIND-+--.  |
                                   '-PDEV--+-(DASD)----------------+--'
 
>---------------------------------------------------------------|
 

SET AUTOCOMMIT

Activates or suppresses the automatic execution of the SQL COMMIT WORK statements. The SET AUTOCOMMIT command cannot span input records.

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

SET ERRORMODE

The SET ERRORMODE command cannot span input records. If you do not supply a SET ERRORMODE command in the input records, the DBS Utility operates as if you issued SET ERRORMODE OFF.

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

SET FORMAT

Identifies 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 not specified, Database Services Utility processing uses column-or-block format for SQL select-statement output.

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

SET ISOLATION

Controls the isolation level used for Database Services Utility processing. Each time the Database Services Utility runs, the isolation level is initialized to repeatable read (RR). SQL processing through the Database Services Utility is performed at the RR isolation level until a SET ISOLATION command is encountered.

The other isolation level settings are cursor stability (CS) and uncommitted read (UR).

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

SET LINECOUNT (LINEWIDTH)

Defines the number of lines per page for Database Services Utility message file output and the number of print data positions used in each Database Services Utility message file record containing SQL SELECT statement output.

The SET LINECOUNT command cannot span input records. You must specify either the LINEWIDTH(www) parameter or the LINECOUNT(ccc) parameter, or both, to prevent a Database Services Utility processing error from occurring.

VM Users

>>-SET---------------------------------------------------------->
 
>-----+-LineCount--(ccc)---+------------------------------+-+--><
      |                    |               .-80---.       | |
      |                    |               +-120--+       | |
      |                    '-LineWidth--(--+-www--+---)---' |
      '-LineWidth--(www)---+-----------------------------+--'
                           |               .-60--.       |
                           '-LineCount--(--+-ccc-+---)---'
 

VSE Users

>>-SET---------------------------------------------------------->
 
>-----+-LineCount--(ccc)---+------------------------------+-+--><
      |                    |               .-120--.       | |
      |                    '-LineWidth--(--+-www--+---)---' |
      '-LineWidth--(www)---+-----------------------------+--'
                           |               .-60--.       |
                           '-LineCount--(--+-ccc-+---)---'
 

SET UPDATE STATISTICS

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

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

UNLOAD DBSPACE

Unloads all tables of the specified DBSPACE to a sequential output file.

VM Users

>>-UNLOAD DBSPACE----(dbspace_name)--OUTFILE----(ddname)-------><
 

VSE Users

>>-UNLOAD DBSPACE----(dbspace_name)----------------------------->
 
>----OUTFILE--(--ddname--+---------------------------+---------->
                         |           .-2048--.       |
                         '-BLKSZ--(--+-size--+---)---'
 
>-----+-----------------------------------+--)-----------------><
      |                 .-NOREWIND--.     |
      |       .-(TAPE)--+-REWIND----+--.  |
      '-PDEV--+-(DASD)-----------------+--'
 

UNLOAD PACKAGE

Unloads a specific package to a file.

VM Users

>>-UNLOAD PACKAGE----(package_name)---+----------------------+-->
                                      '-FROM--(server_name)--'
 
>----OUTFILE----(ddname)---------------------------------------><
 

VSE Users

>>-UNLOAD PACKAGE----(package_name)---+----------------------+-->
                                      '-FROM--(server_name)--'
 
>----OUTFILE--(--ddname--+--------------------------+----------->
                         |           .-2000--.      |
                         '-BLKSZ--(--+-size--+---)--'
 
>-----+-----------------------------------+--)-----------------><
      |                 .-NOREWIND--.     |
      |       .-(TAPE)--+-REWIND----+--.  |
      '-PDEV--+-(DASD)-----------------+--'
 

UNLOAD TABLE

Unloads a specific table or view to an output file.

VM Users

>>-UNLOAD TABLE----(table_name)--OUTFILE----(ddname)-----------><
 

VSE Users

>>-UNLOAD TABLE----(table_name)--------------------------------->
 
>----OUTFILE--(--ddname--+--------------------------+----------->
                         |           .-2048--.      |
                         '-BLKSZ--(--+-size--+---)--'
 
>-----+-----------------------------------+--)-----------------><
      |                 .-NOREWIND--.     |
      |       .-(TAPE)--+-REWIND----+--.  |
      '-PDEV--+-(DASD)-----------------+--'
 


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