The Database Services Utility (DBS Utility) processes commands that are unique to the DBS Utility and certain SQL statements.
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.
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.
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:
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.
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).
To invoke the DBS Utility, use the following EXEC statement as part of the JCL:
// EXEC PGM=ARIDBS,SIZE=AUTO
To invoke the DBS Utility, use either:
// EXEC PROC=ARISDBSD
or
// EXEC ARISQLDS,SIZE=AUTO, X // PARM='SYSMODE=S,LOGMODE=N,PROGNAME=ARIDBS'
The DBS Utility automatically ends after all commands in the control file are processed; that is, when /* is encountered.
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'----------------------------------><
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-+---+---'
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 ***.
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:
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:
infile-subcommand - VSE
>>-INFILE-------------------------------------------------------> >-----+-(--*--+---------------------------------------+---+-----------------------------------+---)--+> | | (1) (2) .-NO--. | | (1) (2) .-YES--. | | | '-CONTINUED------------(--+-----+---)---' '-LIST------------(--+------+---)---' | | '-YES-' '-NO---' | | | '-(--ddname--+------------------------+---)----------------------------------------------------' '-| tape/disk options |--' >-----+------------------------+--+-------------------------+-->< '-COMMITCOUNT--(ccount)--' '-RESTARTCOUNT--(rcount)--'
Notes:
tape/disk options |--+------------------------+-----------------------------------> | .-2048--. | '-BLKSZ--(-+-size--+--)--' >-----+----------------------------------+----------------------> | .-REWIND---. | | .-(TAPE)--+-NOREWIND-+--. | '-PDEV--+-(DASD)----------------+--' >-----+------------------+---+----------------+-----------------| '-RECFM--(format)--' '-RECSZ--(size)--'
Selectively unloads data from tables and views to a user-defined sequential file of data.
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:
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:
outfile-subcommand - VSE (1) |--OUTFILE--------(--ddname----+----------------+---------------> '-BLKSZ--(size)--' >----+---------------------------------+---+------------------+-> | .-NOREWIND--. | '-RECFM--(format)--' | .-TAPE--+-REWIND----+--. | '-PDEV--+-(DASD)---------------+--' >----+----------------+-----------------------------------------| '-RECSZ--(size)--'
Notes:
Rebinds an existing package.
>>-REBIND PACKAGE----(package_name)----------------------------><
Identifies a RELOAD DBSPACE request and identifies a DBSPACE to be loaded.
>>-RELOAD DBSPACE----(dbspace_name)---+-NEW---+-----------------> '-PURGE-' >----INFILE--(ddname)---+----------------------------+----------> '-COMMITCOUNT--(--ccount--)--' >-----+---------------------------------+-----------------------> '-RESTARTTABLE--(--table_name--)--' >-----+-----------------------------+-------------------------->< '-RESTARTCOUNT--(--rcount--)--'
>>-RELOAD DBSPACE----(dbspace_name)---+-NEW---+-----------------> '-PURGE-' >----INFILE--(--ddname--+---------------------------+-----------> | .-2048--. | '-BLKSZ--(--+-size--+---)---' >-----+----------------------------------+--)-------------------> | .-REWIND---. | | .-(TAPE)--+-NOREWIND-+--. | '-PDEV--+-(DASD)----------------+--' >-----+----------------------------+----------------------------> '-COMMITCOUNT--(--ccount--)--' >-----+---------------------------------+-----------------------> '-RESTARTTABLE--(--table_name--)--' >-----+-----------------------------+-------------------------->< '-RESTARTCOUNT--(--rcount--)--'
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.
>>-RELOAD PACKAGE----(package_name)---+-NEW-----------------+---> | .-KEEP---. | '-REPLACE--+--------+-' '-REVOKE-' >-----+--------------------------------+--INFILE----(ddname)--->< | .-,--------------. | | V | | '-TO--(-----server_name---+---)--'
>>-RELOAD PACKAGE----(package_name)---+-NEW-----------------+---> | .-KEEP---. | '-REPLACE--+--------+-' '-REVOKE-' >-----+--------------------------------+------------------------> | .-,--------------. | | V | | '-TO--(-----server_name---+---)--' >----INFILE--(--ddname--+---------------------------+-----------> | .-2000--. | '-BLKSZ--(--+-size--+---)---' >-----+----------------------------------+--)------------------>< | .-REWIND---. | | .-(TAPE)--+-NOREWIND-+--. | '-PDEV--+-(DASD)----------------+--'
Identifies a RELOAD TABLE request and a table to be loaded.
>>-RELOAD TABLE----(table_name)---+-PURGE---------------+-------> '-NEW--(dbspace_name)-' >-----+------------------------+--INFILE--(ddname)--------------> '-INTABLE--(table_name)--' >-----+----------------------------+----------------------------> '-COMMITCOUNT--(--ccount--)--' >-----+-----------------------------+-------------------------->< '-RESTARTCOUNT--(--rcount--)--'
>>-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--)--'
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---'
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.
>>-SCHEMA INFILE----(ddname)---+---------------------+--------->< '-IN--(dbspace_name)--'
>>-SCHEMA INFILE--(--ddname----| option-c |--)------------------> >-----+---------------------+---------------------------------->< '-IN--(dbspace_name)--'
option-c |--+---------------------------+---+----------------------------------+-> | .-2000--. | | .-REWIND---. | '-BLKSZ--(--+-size--+---)---' | .-(TAPE)--+-NOREWIND-+--. | '-PDEV--+-(DASD)----------------+--' >---------------------------------------------------------------|
Activates or suppresses the automatic execution of the SQL COMMIT WORK statements. The SET AUTOCOMMIT command cannot span input records.
.-(--. .-)--. >>-SET AUTOCOMMIT--+----+---+-ON--+--+----+-------------------->< '-OFF-'
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-'
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-'
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-'
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.
>>-SET----------------------------------------------------------> >-----+-LineCount--(ccc)---+------------------------------+-+-->< | | .-80---. | | | | +-120--+ | | | '-LineWidth--(--+-www--+---)---' | '-LineWidth--(www)---+-----------------------------+--' | .-60--. | '-LineCount--(--+-ccc-+---)---'
>>-SET----------------------------------------------------------> >-----+-LineCount--(ccc)---+------------------------------+-+-->< | | .-120--. | | | '-LineWidth--(--+-www--+---)---' | '-LineWidth--(www)---+-----------------------------+--' | .-60--. | '-LineCount--(--+-ccc-+---)---'
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-'
Unloads all tables of the specified DBSPACE to a sequential output file.
>>-UNLOAD DBSPACE----(dbspace_name)--OUTFILE----(ddname)-------><
>>-UNLOAD DBSPACE----(dbspace_name)-----------------------------> >----OUTFILE--(--ddname--+---------------------------+----------> | .-2048--. | '-BLKSZ--(--+-size--+---)---' >-----+-----------------------------------+--)----------------->< | .-NOREWIND--. | | .-(TAPE)--+-REWIND----+--. | '-PDEV--+-(DASD)-----------------+--'
Unloads a specific package to a file.
>>-UNLOAD PACKAGE----(package_name)---+----------------------+--> '-FROM--(server_name)--' >----OUTFILE----(ddname)---------------------------------------><
>>-UNLOAD PACKAGE----(package_name)---+----------------------+--> '-FROM--(server_name)--' >----OUTFILE--(--ddname--+--------------------------+-----------> | .-2000--. | '-BLKSZ--(--+-size--+---)--' >-----+-----------------------------------+--)----------------->< | .-NOREWIND--. | | .-(TAPE)--+-REWIND----+--. | '-PDEV--+-(DASD)-----------------+--'
Unloads a specific table or view to an output file.
>>-UNLOAD TABLE----(table_name)--OUTFILE----(ddname)-----------><
>>-UNLOAD TABLE----(table_name)---------------------------------> >----OUTFILE--(--ddname--+--------------------------+-----------> | .-2048--. | '-BLKSZ--(--+-size--+---)--' >-----+-----------------------------------+--)----------------->< | .-NOREWIND--. | | .-(TAPE)--+-REWIND----+--. | '-PDEV--+-(DASD)-----------------+--'