DB2 Server for VSE & VM: Control Center Operations Guide for VM

Appendix G. Command Mode Interface

Control Center provides a command mode interface allowing most Control Center tools and tool options to be invoked directly from the CMS command prompt or from within a REXX exec. In some cases, options available in command mode are not available in panel mode.

Depending on the command and the command options specified, a data entry or options selection panel may be displayed. For example, entering SQM VERSION at the CMS command prompt will return a message identifying, among other things, the Control Center product release and modification level, whereas entering SQM by itself displays the Control Center Main Menu (panel mode interface).

Issuing Control Center Commands

Issuing Control Center commands requires READ access to the Control Center code disk and, depending on the command specified, read access to the database production disk.

DBINIT: Database Access

The DBINIT command sets up access to a database by linking to the database production disk and, if necessary, performing an SQLINIT.
Operational Consideration: DBINIT requires access to these files:

Database Product Modules:

SQLDBID EXEC
ARISQLLD EXEC
Control File:
DBINIT CONTROL is discussed in Chapter 8, Managing the Environment.

DBINIT Command >>-DBINIT-------------------------------------------------------> >-----+--------------------------------------------------------------+> +-database--+------------------------------------------------+-+ | '-(--PROFile--filename--filetype--+-----------+--' | | '-filemode--' | '-STACK--------------------------------------------------------' >--------------------------------------------------------------><

If DBINIT is entered without any parameters, a message indicating the currently accessed database is displayed.

database: the name or nickname of the database to access.
PROFile filename filetype filemode: specifies an alternative file to be used instead of DBINIT CONTROL.
STACK: The name of the currently accessed database is placed in the program stack.

SQLMAINT: DBSPACE Maintenance

The SQLMAINT command lets you automate dbspace maintenance. It performs UPDATE STATISTICS and SQLREORG functions on selected dbspaces. DBSPACE selection parameters include the number of days that have elapsed since the same maintenance was last performed on the dbspace. Refer to Chapter 33, Automated DBSPACE Maintenance Tools for additional information.

Usage Considerations:

Authority required:
SQLMAINT must be executed from a user ID that has DBA authority in the target database.
Authorization for SFS Databases:

Upon execution of SQLREORG, the Control Center support machine must be:
- Granted READ access on the production code directory files
- Enrolled as a USER in the SFS filepool where the production code directory is located
- Granted DBA on the servers where the database administration activities will be performed.

SQLMAINT Command >>-SQLMAINT-----------------------------------------------------> >-----+---------------------------------------------------------+->< '-dbname--+-INSTALL--+-------------------------+-------+--' | '-(--| INSTALL options |--' | +-LISTREORG--+---------------------------+---+ | '-(--| LISTREORG options |--' | +-LISTSTATS--+---------------------------+---+ | '-(--| LISTSTATS options |--' | +-REORGPRIOR--+----------------------------+-+ | '-(--| REORGPRIOR options |--' | +-RUNREORG--+--------------------------+-----+ | '-(--| RUNREORG options |--' | '-UPSTATS--+-------------------------+-------' '-(--| UPSTATS options |--'

The SQLMAINT panel interface is invoked if no parameters are specified.

dbname

is the database name (the dbname, not the database machine ID) of the database where the maintenance will be performed.

INSTALL

will create a public table within the target database which is used to keep track of SQLMAINT activities.

INSTALL options: Panel Option I. See INSTALL options.

Note: SQLMAINT must first be installed using the INSTALL option before it is fully operational.

LISTREORG

will list candidate dbspaces that require reorganization based on specified criteria.

LISTREORG options: Panel option LR. See LISTREORG options.

LISTSTATS

will list dbspaces which need to have statistics updated.

LISTSTATS options: Panel option LS. See LISTSTATS options.

REORGPRIOR

will reorganize dbspaces based on candidates chosen by a previous evaluation (generated by either LISTREORG or RUNREORG). The previous candidate list must be available on the A-disk (filename=database, filetype=SQLMAINT).

The essential purpose of this option is to allow one execution of the candidate evaluation process using either LISTREORG or RUNREORG, with multiple subsequent reorganization runs based on the single prior evaluation. This can save considerable dbspace examination time, but should be used with caution since the evaluation data may quickly become invalid.

REORGPRIOR options: Panel Option RP. See REORGPRIOR options.

RUNREORG

will reorganize candidate dbspaces that meet the specified criteria.

RUNREORG options: Panel Option RR. See RUNREORG options.

UPSTATS

will update statistics on dbspaces that meet the specified criteria.

UPSTATS options: Panel Option US. See UPSTATS options.

INSTALL options |--dbspacename--------------------------------------------------|

dbspacename

is a public dbspace where the SQLMAINT table may be created. If not specified, SQLMAINT will prompt the user for the dbspace name.

Note:

The SQLMAINT table must first be created before the SQLMAINT function is fully operational.

LISTREORG options |--+---------------------+---+----------------------+-----------> | .-ALL-----. | '-DBSPname=dbspacename-' '-OWNer=-+-PUBLIC--+--' +-PRIVATE-+ '-owner---' >-----+----------------+---+----------------+---+---------+-----> '-DBMIN=minpages-' '-DBMAX=maxpages-' '-QTY=nnn-' >-----+----------+----------------------------------------------| '-DAYS=ddd-'

OWNer=owner: is the owner of dbspaces to be listed (PUBLIC, PRIVATE, ALL, or specific owner). ALL is the default. Owner name may include a leading or trailing percent sign (%) to indicate a wildcard match.
DBSPname=dbspacename: is a dbspace name to list. A blank entry will select ALL dbspaces. For specific dbspace names, a leading or trailing percent sign (%) can be used for wildcard matching.
DBMIN=minpages: is the minimum dbspace size to be included, expressed as number of pages (NPAGES).
DBMAX=maxpages: is the maximum dbspace size to be included, expressed as number of pages (NPAGES).
QTY=nnn: is the number of dbspaces to be listed (default is ALL).
DAYS=ddd: is the number of days since the previous update that must have passed for a dbspace to be listed.

LISTSTATS options |--+---------------------+---+----------------------+-----------> | .-ALL-----. | '-DBSPname=dbspacename-' '-OWNer=-+-PUBLIC--+--' +-PRIVATE-+ '-owner---' >-----+----------------+---+----------------+---+---------+-----> '-DBMIN=minpages-' '-DBMAX=maxpages-' '-QTY=nnn-' >-----+----------+----------------------------------------------| '-DAYS=ddd-'

OWNer=owner: is the owner of dbspaces to be listed (PUBLIC, PRIVATE, ALL, or specific owner). ALL is the default. Owner name may include a leading or trailing percent sign (%) to indicate a wildcard match.
DBSPname=dbspacename: is a dbspace name to list. A blank entry will select ALL dbspaces. For specific dbspace names, a leading or trailing percent sign (%) can be used for wildcard matching.
DBMIN=minpages: is the minimum dbspace size to be included, expressed as number of pages (NPAGES).
DBMAX=maxpages: is the maximum dbspace size to be included, expressed as number of pages (NPAGES).
QTY=nnn: is the number of dbspaces to be listed (default is ALL).
DAYS=ddd: is the number of days since the previous update that must have passed for a dbspace to be listed.

REORGPRIOR options |--+---------------------+---+----------------------+-----------> | .-ALL-----. | '-DBSPname=dbspacename-' '-OWNer=-+-PUBLIC--+--' +-PRIVATE-+ '-owner---' >-----+----------------+---+----------------+---+---------+-----> '-DBMIN=minpages-' '-DBMAX=maxpages-' '-QTY=nnn-' >-----+-------------+---+----------+----------------------------> '-FM=filemode-' '-TIME=mmm-' >----+-----+--+------+--+----------+----+--------------+--------| '-AGE-' '-STOP-' '-ALLStats-' '-COMCOUNT=nnn-'

OWNer=owner: is the owner of dbspaces to be reorganized (PUBLIC, PRIVATE, ALL, or specific owner). ALL is the default. This value must match the value provided for the previous LISTREORG or RUNREORG job, or it may be a valid subset of the previous candidate list.
DBSPname=dbspacename: is a dbspace name to reorganize. A blank entry will select ALL dbspaces. This value must match the value provided for the previous LISTREORG or RUNREORG job, or it may be a valid subset of the previous candidate list.
DBMIN=minpages: is the minimum dbspace size to be included, expressed as number of pages (NPAGES).
DBMAX=maxpages: is the maximum DBSPACE size to be included, expressed as number of pages (NPAGES).
QTY=nnn: is the number of dbspaces to be reorganized (default is ALL).
FM=filemode: is an optional filemode of a disk linked in WRITE mode to be used for reorganizations. The default is to use a temporary disk.
TIME=mmm: is the maximum number of minutes that will be allowed to complete the reorganizations. If all selected dbspaces cannot be completed within the specified minutes, SQLMAINT will complete as many as possible.
AGE: is an optional selection sequence for reorganizations. The default sequence is by weight.
STOP: indicates that SQLMAINT should immediately terminate if a reorganization fails on any dbspace, other than failures caused by insufficient external workspace.
ALLSTATS: is the keyword to use when it is desired to have SQLREORG issue an UPDATE ALL STATISTICS command in the DDL instead of the default UPDATE STATISTICS.
COMCOUNT=nnn: is the COMMITCOUNT value desired in RELOAD DDL.

RUNREORG options |--+---------------------+---+----------------------+-----------> | .-ALL-----. | '-DBSPname=dbspacename-' '-OWNer=-+-PUBLIC--+--' +-PRIVATE-+ '-owner---' >-----+----------------+---+----------------+---+---------+-----> '-DBMIN=minpages-' '-DBMAX=maxpages-' '-QTY=nnn-' >-----+-------------+---+----------+---+----------+-------------> '-FM=filemode-' '-DAYS=ddd-' '-TIME=mmm-' >----+-----+--+------+--+----------+----+--------------+--------| '-AGE-' '-STOP-' '-ALLStats-' '-COMCOUNT=nnn-'

OWNer=owner: is the owner of dbspaces to be listed (PUBLIC, PRIVATE, ALL, or specific owner). ALL is the default. Owner name may include a leading or trailing percent sign (%) to indicate a wildcard match.
DBSPname=dbspacename: is a dbspace name to list. A blank entry will select ALL dbspaces. For specific dbspace names, a leading or trailing percent sign (%) can be used for wildcard matching.
DBMIN=minpages: is the minimum dbspace size to be included, expressed as number of pages (NPAGES).
DBMAX=maxpages: is the maximum dbspace size to be included, expressed as number of pages (NPAGES).
QTY=nnn: is the number of dbspaces to be reorganized (default is ALL).
FM=filemode: is an optional filemode of a disk linked in WRITE mode to be used for reorganizations. The default is to use a temporary disk.
DAYS=ddd: is the number of days since the previous update that must have passed for a dbspace to be reorganized.
TIME=mmm: is the maximum number of minutes that will be allowed to complete the reorganizations. If all selected dbspaces cannot be completed within the specified minutes, SQLMAINT will complete as many as possible.
AGE: is an optional selection sequence for reorganizations. The default sequence is by weight.
STOP: indicates that SQLMAINT should immediately terminate if a reorganization fails on any dbspace, other than failures cause by insufficient external work space.
ALLSTATS: is the keyword to use when it is desired to have SQLREORG issue an UPDATE ALL STATISTICS command in the DDL instead of the default UPDATE STATISTICS.
COMCOUNT=nnn: is the COMMITCOUNT value desired in RELOAD DDL.

UPSTATS options |--+---------------------+---+----------------------+-----------> | .-ALL-----. | '-DBSPname=dbspacename-' '-OWNer=-+-PUBLIC--+--' +-PRIVATE-+ '-owner---' >-----+----------------+---+----------------+---+---------+-----> '-DBMIN=minpages-' '-DBMAX=maxpages-' '-QTY=nnn-' >-----+----------+---+----------+--+------+---------------------| '-DAYS=ddd-' '-TIME=mmm-' '-STOP-'

OWNer=owner: is the owner of dbspaces to be listed (PUBLIC, PRIVATE, ALL, or specific owner). ALL is the default. Owner name may include a leading or trailing percent sign (%) to indicate a wildcard match.
DBSPname=dbspacename: is a dbspace name to list. A blank entry will select ALL dbspaces. For specific dbspace names, a leading or trailing percent sign (%) can be used for wildcard matching.
DBMIN=minpages: is the minimum dbspace size to be included, expressed as number of pages (NPAGES).
DBMAX=maxpages: is the maximum dbspace size to be included, expressed as number of pages (NPAGES).
QTY=nnn: is the number of dbspaces to be updated (default is ALL).
DAYS=ddd: is the number of days since the previous update that must have passed for a dbspace to be updated.
TIME=mmm: is the maximum number of minutes that will be allowed to complete the UPDATE STATISTICS. If all selected dbspaces cannot be completed within the specified minutes, SQLMAINT will complete as many as possible.
STOP: indicates that SQLMAINT should immediately terminate if the UPDATE STATISTICS command fails on any DBSPACE.

SQLRBIND: Rebind Package

The SQLRBIND command lets you selectively rebind packages within the database. SQLBIND uses the REBIND command of the Database Services Utility (DBSU). Refer to Chapter 34, Rebind Package Tool for additional information.

Usage Considerations:

Authority required:
SQLRBIND must be executed from a user ID that has DBA authority in the target database.

SQLRBIND Command >>-SQLRBIND----+------------------------------------------+---->< '-dbname--| O |---+--------+---+--------+--' -| PNAME |- '-VALID--' '-PAUSE--' Options |--+--------------------------------------------+---------------| '-(--+------------------+---+-------------+--' '-CREATOR=pcreator-' '-PNAME=pname-' PNAME |- ---------------------------------------------|

The SQLRBIND panel interface is invoked if no parameters are specified.

dbname: is the database name (the dbname, not the database machine ID) of the database where the maintenance will be performed.
CREATOR=pcreator: will only REBIND packages that were created by the specified pcreator. A leading or trailing percent sign (%) may be used for wildcard matching.
PNAME=pname: will only REBIND packages with the specified pname. A leading or trailing percent sign (%) may be used for wildcard matching.
VALID: will REBIND all packages, regardless of whether they are currently invalid. The default is to REBIND only those packages that are currently invalid.
PAUSE: will cause SQLRBIND to capture all REBIND commands within a Database Services Utility (DBSU) command file and stop without executing the REBIND commands.

SQLREORG: DBSPACE Reorganization

The SQLREORG command lets you reorganize a database dbspace. The specified dbspace will be unloaded, dropped, re-acquired, and reloaded. This will assure maximum efficiency and space utilization. Refer to Multiple User Mode DBSPACE Reorganization Tool for additional information.

Usage Considerations:

Authority required:
You must have DB2 Server for VM Database Administrator authority for the database specified.
Processing:
SQLREORG will acquire a temporary disk for the unloaded dbspace, unless an optional linked disk filemode or TAPE is specified.
A copy of the unloaded data will also be sent to your reader to provide recovery backup in case the disk copy is lost.
Tape Processing:
SQLREORG will unload the dbspace to tape if you specify the TAPE option. Prior to invoking SQLREORG, you must issue a FILEDEF command for ddname DBSFILE and have the tape mounted and attached. If your unloaded data will span more than one tape, you will also need to issue a LABELDEF command to provide for multivolume tape handling. SQLREORG executes the UNLOAD utility. For UNLOAD, always specify a record format (RECFM) of variable-length blocked spanned (VBS). For instance, an example of FILEDEF and LABELDEF commands would be:
FILEDEF DBSFILE TAP1 SL (RECFM VBS BLKSIZE 28672 LABELDEF DBSFILE VOLID SCRATCH
You would then need to have a scratch tape mounted and attached as 181.
If your unloaded data fits on a single tape, SQLREORG may be invoked without the PAUSE option and a tape rewind will automatically be performed between the UNLOAD and RELOAD processing. If your unloaded data spans more than one tape, then you must use the PAUSE option and issue the proper DETACH and MOUNT commands between the UNLOAD and RELOAD processing (detaching the last tape and remounting the first tape).
Authorization for SFS Databases:

Upon execution of SQLREORG, the Control Center support machine must be:
- Granted READ access on the production code directory files
- Enrolled as a USER in the SFS filepool where the production code directory is located
- Granted DBA on the servers where the database administration activities will be performed.

SQLREORG Command >>-SQLREORG-----------------------------------------------------> >-----+------------------------------------------------+------->< '-database--owner.dbspace---+-----------------+--' '-(--| options |--'

The SQLREORG panel interface is invoked if no parameters are specified.

database: is the name of the applicable database.
owner.dbspace: is the database owner and name of the dbspace to be reorganized.
options: See options which follow.

options |--+-------+--+----------+--+------+----+-------------------+---> '-PAuse-' '-NOUNload-' '-TAPE-' '-DATABASE=database-' >-----+-------------+---+-------------------------+-------------> '-OWNER=owner-' '-AFN=alternate file name-' >-----+--------------------------------+------------------------> '-SENDTO=userid-+-------------+--' '-AT--nodeid--' >-----+---------------------+---+-----------+---+----------+----> '-DBSPace=dbspacename-' '-PAGES=nnn-' '-POOL=nnn-' >-----+-------------+---+---------------+---+--------------+----> '-PCTFree=nnn-' '-ALTerfree=nnn-' '-PCTIndex=nnn-' >-----+-----------+---+--------------------+--+----------+------> '-NHeader=n-' '-LOCK=-+-DBSPACE-+--' '-ALLStats-' +-PAGE----+ '-ROW-----' >-----+---------------+---+------------------+------------------| '-DISK=filemode-' '-TDSIZE=cylinders-'

PAuse: will perform the UNLOAD portion of the reorganization only. To continue processing, run the REXX exec that is created.
NOUNload: will PAUSE before the data UNLOAD, providing only the DDL capture.
TAPE: will perform the UNLOAD to TAPE instead of DISK.
DATABASE=database: is the database where the dbspace will be moved.
OWNER=owner: is the new owner of the dbspace.
AFN=alternate file name: is the user selected file name for SQLREORG generated files.
SENDTO=userid AT nodeid: is where a copy of the unloaded data is sent for recovery backup when a temporary disk is used. The default is your user ID.
DBSPace=dbspacename: is the new name for the dbspace.
PAGES=nnn: is the new size of the dbspace.
POOL=nnn: is the new STORPOOL.
PCTFree=nnn: is the PCTFREE to use during the RELOAD.
ALTerfree=nnn: is the new PCTFREE for after the RELOAD.
PCTIndex=nnn: is the new PCTINDEX.
NHeader=n: is the new NHEADER size.
LOCK=xxxxx: is the new LOCK size (DBSPACE, PAGE, ROW).
ALLStats: will use UPDATE ALL STATISTICS instead of UPDATE STATISTICS.
DISK=filemode: is the filemode of a linked disk for the data UNLOAD (defaults to using a TEMP disk).
TDSIZE=cylinders: is the temporary disk size (defaults to calculation based on the DBSPACE size).

SQLTABLE: Table Reorganization and Redefinition

The SQLTABLE command lets you reorganize and redefine database tables. Options can be specified to reorganize a table, copy a table, move a table, or redefine columns in a table. Column redefinition is available only when run in panel mode. Refer to Overview for additional information.

Two Database Services Utility (DBSU) files are created by the SQLTABLE command. A DBSU file with a filetype of LOADDBSU will contain the DATALOAD statement, along with any required DROP TABLE and CREATE TABLE statements (depending on selected options). A DBSU file with a filetype of DDLDBSU will contain additional data definition language (DDL) statements associated with a table, such as CREATE INDEX, CREATE VIEW, and GRANT. The PAUSE option allows you to modify these files prior to execution. This is useful when copying tables to a new name under the same owner, allowing the user to change the index names and view names to avoid duplication of existing names.

Usage Considerations:

Authority required:
The DATA option will require SELECT authority on the source table and INSERT authority on the target table.
The DDL option requires that you own the target DBSPACE, have RESOURCE authority if the target DBSPACE is PUBLIC, or have DBA authority (required for complete capture of all VIEW and GRANT DDL capture).
Processing options:
SQLTABLE will perform various functions depending on the options specified.

Table Reorganization:
If only the source database and table is specified, SQLTABLE will perform a table reorganization, using DATAUNLOAD to capture the data from the table and DATALOAD to restore the data to the table. If the DATA option is specified, SQLTABLE will delete all rows in the table prior to performing the DATALOAD. The DATA option requires that you have SELECT and INSERT authority to the table.
If the DDL option is specified, SQLTABLE will capture the table create statement and all associated database objects, including indexes, views, and grants.
When data and DDL are both captured for a reorganization, you will require DBA authority for complete capture of all dependent objects within the database (including those of other users). The table reorganization for DDL and DATA will capture the data using DATAUNLOAD, DROP and CREATE the table, DATALOAD the data, and then recreate all dependent objects (indexes, views, and grants).
Table Migration
SQLTABLE will migrate data or table DDL between databases if the New Database (NEWDB) option is used. The source table will not be dropped or modified in any way.
Table Copy
SQLTABLE will copy either data or the table definition (DDL) or both if a new owner.table is specified with the NEWTABLE option. If DATA is specified, the target table must already exist and the column definitions must match the source table exactly. If DDL is specified, you must have DBA authority to capture all dependent database objects. It is important to note that if the target owner is the same as the source table owner, the index and view create names will be identical to the existing ones on the old table, so the create statements will fail unless the user takes steps to eliminate the duplication.
Table Move
SQLTABLE will move a table to a new DBSPACE if the DATA and DDL option are specified with the new dbspace (NEWSpace) option. Note that the old table will only be deleted if the new owner and table remain the same as the old owner and table.
Redefine Table
SQLTABLE will allow you to redefine a table, changing column names, adding columns, deleting columns, changing column datatypes and lengths, through the panel mode option only. Since the DATAUNLOAD/DATALOAD functions of the Database Services Utility (DBSU) are used for this process, all restrictions described in the "Data Conversion Summary" section of the DB2 Server for VSE & VM Database Services Utility manual apply. You should be aware of which datatypes are compatible and which ones will cause data truncation or dataload failures.
Tape Processing
SQLTABLE will unload the table data to tape if you specify the TAPE option. Prior to invoking SQLTABLE, you must issue a FILEDEF command for ddname DBSFILE and have the tape mounted and attached. If your unloaded data will span more than one tape, you will also need to issue a LABELDEF command to provide for multivolume tape handling.
SQLTABLE executes the DATAUNLOAD utility. DATAUNLOAD supports all record format (RECFM) values with the exception of undefined (U). For instance, an example of a FILEDEF and LABELDEF command are given below:
FILEDEF DBSFILE TAP1 SL (RECFM VBS BLKSIZE 28672 LABELDEF DBSFILE VOLID SCRATCH
You would then need to have a scratch tape mounted and attached as 181.
If your unloaded data fits on a single tape, SQLTABLE may be invoked without the PAUSE option and a tape rewind will automatically be performed between the UNLOAD and RELOAD processing. If your unloaded data spans more than one tape, then you must use the PAUSE option and issue the proper DETACH and MOUNT commands between the UNLOAD and RELOAD processing (detaching the last tape and remounting the first tape).

SQLTABLE Command >>-SQLTABLE----+----------------------------------------------+->< '-database--owner.table---+-----------------+--' '-(--| options |--'

The SQLTABLE panel interface is invoked if no parameters are specified.

database: is the name of the database where the table is located.
owner.table: is the database owner and table name of the table that will be the target of SQLTABLE operations.
options: See options below.

options |--+------+--+-----+----+----------------+----------------------> '-DATA-' '-DDL-' '-NEWDB=database-' >-----+----------------------+---+----------------------------+-> '-NEWTable=owner.table-' '-NEWSpace=owner.dbspacename-' >----+-------+----+--------------+--+--------+--+----------+----> '-PAuse-' '-COMmit=nnnnn-' '-NOPrep-' '-ALLStats-' >-----+-------------+---+-------------+---+---------------+-----> '-PCTFree=nnn-' '-ALTFree=nnn-' '-DISK=filemode-' >----+------+--+-----------+------------------------------------| '-TAPE-' '-UPDBefore-'

DATA: will only unload the DATA within the specified table, and will not capture associated Data Definition Language (DDL) statements (CREATE TABLE, INDEX).
DDL: will only capture the Data Definition Language statements associated with the specified table, and will not unload the data.
NEWDB=database: is the target database for the data or DDL. If this option is used, SQLTABLE will perform a COPY function, and the original table will remain intact.
NEWTable=owner.table: is the target table for the data or DDL. If this option is specified, SQLTABLE will perform a COPY function, and the original table will remain intact. If DDL is captured, the INDEX and VIEW names on the new table will be identical to the names on the old table, causing them to fail unless the PAUSE option is used and either the old or new names are changed.
NEWSpace=owner.dbspacename: is the target dbspace for the data and DDL. If this option is used without specifying a new database or owner.table name, SQLTABLE will perform a MOVE function, moving the table, indexes and views to the new dbspace.
PAuse: will perform the DATAUNLOAD and DDL capture only.
COMmit=nnnnn: is the COMMITCOUNT for the DATALOAD.
NOPrep: packages will not be reprepped.
ALLStats: will use UPDATE ALL STATISTICS instead of UPDATE STATISTICS.
PCTFree=nnn: is the PCTFREE to be used during the DATALOAD.
ALTFree=nnn: is the new PCTFREE for after the DATALOAD.
DISK=filemode: is the filemode of a linked disk for the DATAUNLOAD.
TAPE: will perform the DATAUNLOAD to TAPE instead of DISK.
UPDBefore: will UPDATE STATISTICS before calculating temp disk space.

SQM: Control Center

The SQM command is used to start Control Center in panel mode and to issue Control Center administrator commands and database administrator commands in command mode. ¹⁰

SQM Command >>-SQM----+---------------------------------+-------------------> +-?-------------------------------+ +-HELP----------------------------+ +-Control Center main menu option-+ +-| Control Center Admin Cmds |---+ '-| Database Admin Cmds |---------' >-----+------------------------+------------------------------->< '-(--userid--AT--nodeid--'

The Control Center Main Menu is displayed if no parameters are specified. Refer to Chapter 9, Getting Started for additional information.

?

displays the current Control Center communication path.

HELP

displays help for the SQM command.

Control Center main menu option

allows you to go directly to any option on the Control Center Main Menu when invoking Control Center in panel mode. For example, you can go directly from CMS to the ARCHIVING selection panel by specifying SQM A at the CMS command level.

Control Center Administrator Commands

See Control Center Admin Cmds.

Database Administrator Commands

See Figure 250.

userid AT nodeid

is used to change communications to a different Control Center machine.

Note:

This must be the first SQM command issued by a new user in order to establish initial communications.

Control Center Admin Cmds |--+-ADDPROF--profline------------------------------------------+-> +-CMS--cms_command-------------------------------------------+ +-CODETRACE--dbname--+-START-+-------------------------------+ | '-STOP--' | | .-CLOSE--. | +-CONSOLE--+-ON-----+----------------------------------------+ | '-OFF----' | +-DBstatus---------------------------------------------------+ +-MESSAGE--| Update |--msgno--action--| Msg Type |--comment--+ +-NEWPROF----------------------------------------------------+ +-OP--Database Operator Command------------------------------+ +-QUERY--+-JOBS-----+----------------------------------------+ | '-SCHEDULE-' | +-RDRLIST----------------------------------------------------+ +-STARTJOB--jobname------------------------------------------+ +-STOP-------------------------------------------------------+ '-VERSION----------------------------------------------------' >---------------------------------------------------------------| Update (1) |---+-A------+--------------------------------------------------| | (2) | +-C------+ | (3) | '-D------' Msg Type (4) |---+-A------+--------------------------------------------------| | (5) | +-D------+ | (6) | +-E------+ | (7) | +-I------+ | (8) | +-O------+ | (9) | '-S------'

Notes:

Add

Change

Delete

Administrator alert.

Database alert.

Error alert.

Information only.

Operator alert.

Serious error alert.

ADDPROF

is used to add a line to the SQLMSTR PROFILE. The syntax must conform to SQLMSTR PROFILE requirements.

CMS

is used to have Control Center execute any valid CMS command.

cmscommand: is any valid CMS command.

Note:

Caution must be exercised with this command. If the command causes any console input (VM READ), Control Center will be disabled.

CODETRACE

is used to start or stop tracing of certain code within Control Center for a database. The results of the tracing will appear in the Control Center CONSOLE and LOG files.

dbname: is the database which will have code tracing started or stopped.
START: will start code tracing.
STOP: will stop code tracing.

CONSOLE

is used to control the spooling of the Control Center virtual machine console.

CLOSE: is used to have Control Center close the current console and send it to your reader for examination. This is the default.
ON: specifies that the Control Center virtual machine should begin spooling its console.
OFF: specifies that the Control Center virtual machine should stop spooling its console.

DBstatus

is used to display the Master Database Status panel.

MESSAGE

is used to add/change/delete an action routine in the DATABASE MESSAGES file (message filtering).

Update

A: to add a message.
C: to change a message.
D: to delete a message.

msgno

is the message identifier (ARI0001E).

action

is the executable command that will be performed when this message is received from a database.

Message Type

A: for Administrator alert.
D: for Dbstatus alert.
E: for Error alert.
I: for Information only.
O: for Operator alert.
S: for Serious Error alert.

comment

is any text which describes the message.

NEWPROF

causes Control Center to temporarily halt execution and re-read the SQLMSTR PROFILE, and then restart. This must be done if any changes are made to the SQLMSTR PROFILE in order for the changes to become effective.

OP

issues a database operator command to the database specified in the last executed SQLINIT or DBINIT command.

Database Operator Command: the database operator command to execute. Refer to the DB2 Server for VSE & VM Operation manual.

QUERY

JOBS: will display the current Job Schedule for ALL databases.
QUERY SCHEDULE: will display the current SQLMSTR TIMES schedule.

RDRLIST

causes Control Center to send you a list of Control Center reader files.

STARTJOB

is used to immediately start a scheduled job.

jobname: the name of the Control Center job to start.

STOP

causes Control Center execution to terminate.

VERSION

displays Control Center code level (release, modification, service level, and last PTF applied).

Figure 250. Database Admin Cmds

Database Admin Cmds |--+------------+---database machine ID--| Command Options |----| '-nodeid--.--'

nodeid: is the CPU node ID. Specify when it is necessary to distinguish between database machines with the same name on different CPUs. For example: VMSYSTM1.SQLDBA can be used to specify the SQLDBA database machine on CPU VMSYSTM1.
database machine ID: is any valid database machine ID that is controlled by Control Center. It must precede every Control Center DBA command.
Command Options: See Command Options.

 
Command Options
 
|--+-ADD DBSPACE-------------------------------+----------------|
   +-ADD DBEXTENT------------------------------+
   |         (1)                               |
   +-ADDTAPE-------+-ARCHIVE-+---series--------+
   |               +-LOG-----+                 |
   |               '-TRACE---'                 |
   +-ARCHIVE--+---------+--+----------+--------+
   |          '-DVERIFY-'  '-TRCPURGE-'        |
   |        (6)                                |
   +-BACKUP-------+----------------+-----------+
   |              +-FULL-----------+           |
   |              +-INCremental----+           |
   |              '-+------------+-'           |
   |                +-AUTOFULL---+             |
   |                '-NOAUTOFULL-'             |
   +-CANCEL------------------------------------+
   +-CMS--command------------------------------+
   +-COLDLOG-----------------------------------+
   +-COPY--+-DIRECTORY-+-----------------------+
   |       +-LOGDISK---+                       |
   |       '-DBEXTENT--'                       |
   |                      (2)                  |
   +-COUNTER--counteropts-------+------------+-+
   |                            '-(--NOWAIT--' |
   +-DBstatus----------------------------------+
   +-DELETE--DBEXTENT--------------------------+
   +-FORCE--+-userid--------------+------------+
   |        '-AGENT--agentnumber--'            |
   +-FORWard--| Forward Options |--------------+
   +-LARCHIVE----------------------------------+
   +-Query--| Query Options |------------------+
   +-REORG--userid--vaddr--dbsfname------------+
   |                  (3)                      |
   +-RESET--resetopts--------------------------+
   |                (4)                        |
   +-SHOW--showopts-------+------------+-------+
   |                      '-(--NOWAIT--'       |
   +-SQLCIREO----------------------------------+
   |                    (5)                    |
   +-SQLEND--sqlendopts------------------------+
   +-SQMDBU--parm--parmval---------------------+
   +-SQMDINIT----------------------------------+
   +-SQMODE--newmode---------------------------+
   +-STARTUP-----------------------------------+
   +-STATUS--newstatus-------------------------+
   +-TRACE--+-ON--+------+----------------+----+
   |        |     '-DUMP-'                |    |
   |        |     .-CLOSE-.               |    |
   |        '-OFF-+-------+--+---------+--'    |
   |                         '-NOCLOSE-'       |
   +-UARCHIVE--completion status---------------+
   '-UARCSTAT--+-SUCCESSFUL-+------------------'
               +-FAILED-----+
               '-RUNNING----'
 
Forward Options
 
|---+-MESSages-+---+-OFF-----------------+----------------------|
    '-MSGs-----'   '-userid--AT--nodeid--'

Notes:

Only valid when using VMTAPE.
Any valid COUNTER command option; refer to the DB2 Server for VSE & VM Operation manual.
Any valid RESET command option; refer to the DB2 Server for VSE & VM Operation manual.
Any valid SHOW command option; refer to the DB2 Server for VSE & VM Operation manual.
Any valid SQLEND command option; refer to the DB2 Server for VSE & VM Operation manual.
If Incremental Backup is enabled, then only Full and Incremental options are valid.

ADD DBSPACE

will initiate the Add DBSPACE tool using the database SQLADBSP file on Control Center.

ADD DBEXTENT

will initiate the Add DBEXTENTS tool using the database ADEXTENT and ADNLPOOL files on Control Center.

ADDTAPE

is used to request a scratch tape to be mounted, catalogued, and added to a database TAPES file.

Usage Consideration:

This function is available only with VMTAPE.

Specify the tape type: