DB2 Utilities and Commands (helpful for DB2 Certification)

• Utilities
– Image Copy
– CDB SuperCopy
– Quiesce
– Load
– CDB SuperLoad
– Check
– BSCTIAUL
– CDB SuperUnload
– Recover
– CDB SuperRestore
– Reorg
– CDB SuperReorg
– Runstats
– Report Recovery
– Repair

• Commands
– Display/Stop/Start Database
– Display/Term Utility


DB2 Logging

• DB2 Logs
– A ‘journal’ of all activity for a subsystem
– Undo/redo records for table updates
– Each event in the log is identified by its RBA(relative byte address)
– RBA denotes a point in time: the greater the RBA, the later in time the event occurred
– Data Sharing - LRSNs along with RBAs

– Some events:
• Full image copy
• Quiesce
• Load


Data Sharing

• Benefits of Data Sharing
– High availability
– Workload balancing
– Sysplex parallelism
– Better use of resources


Backing Up Tables

• Image Copy
– Backup for DB2 table spaces
– Can also copy index spaces
– Can perform full (all data) or incremental (changed data) copies
– Can prevent or allow updates by others during copy
– SAMPLIB: DB2COPY

• CDB SuperCopy
– Faster method of creating standard DB2 image copies
– Can back up several spaces with single command, via generic name
– Can stack multiple copies on one tape
– Supports dynamic allocation of image copy tape files
– SAMPLIB: CDBCOPY

The Quiesce Utility

• Quiesce
– Sets a ‘bookmark’ in the DB2 log,indicating the RBA at which NO activity is occurring on the specified table space(s)
– Use this utility to produce an RBA that can later be used for a point-intime (PIT) recovery of the table space(s)
– Several spaces can be quiesced in a single request
– Runs very quickly
– Use this in production wherever you want to establish a possible recovery point
– SAMPLIB: DB2QUIES

Loading Tables

• Load
– Loads data into DB2 table from an external QSAM file (tape or disk)
– Can selectively include or exclude input records
– Can replace all rows, or add to existing rows
– Can enforce RI or ignore it (if ignored, RI must be verified via Check utility)
– SAMPLIB: DB2LOAD

• CDB SuperLoad
– Faster way to load a DB2 table
– Automatic image copy, if desired
– Enhanced data conversion
– Load into multiple tables, from multiple input files
– SAMPLIB: CDBLOAD

Integrity Utilities

• Check
– Verifies all RI on a table space
– Verifies table constraints
– Resets ‘check pending’ flag, usually set by Load utility, if constraints were not enforced
– Will insert all invalid rows into a separate table for review, if requested
– SAMPLIB: DB2CHECK



Unloading Tables

• BSCTIAUL
– Modified version of IBM-supplied sample program DSNTIAUL
– Uses dynamic SQL to unload table(s) to sequential file(s)
– Also generates DB2 Load utility control statements
– Can use output to (re)load table

– Uses:
• Data transfer (e.g., production to test)
• Data archival
• Simple data editing
– SAMPLIB: BSCTIAUL


• CDB SuperUnload
– Quick way to unload table data to sequential file
– Can unload from:
• Table itself
• Full/partial image copy
• DSN1COPY file
– Can unload subset of data via extensive filtering options
– Can sort output
– Generates Load utility statements
– SAMPLIB: CDBUNLD


Data Reorganization

• Reorg
– Reorganizes table and/or index spaces
– Use to put certain table/index space changes into effect (e.g., PRIQTY,SECQTY, SEGSIZE, etc)
– Deletes and reallocates underlying datasets
– Can use as unload - Unload External
– Can delete rows
– SAMPLIB: DB2REORG

• CDB SuperReorg
– Faster Reorg
– SAMPLIB: CDBREOR


Statistics and Reporting

• Runstats
– Updates DB2 catalog with current statistics regarding table space, table, column and index information
– These statistics are used by the optimizer to determine access paths
– Plans/packages should be rebound to take advantage of any updated information
– SAMPLIB: DB2STATS

• Report Recovery
– Provides listing of all recovery-related events for table space(s)
– Use to identify datasets needed for recovery
– SAMPLIB: DB2RPTRC

• Repair
– Used to reset the following flags:
• Copy pending
• Recovery pending
• Check pending
– Also used by DBA to fix broken pages
– SAMPLIB: DB2REPR


Recovery Utilities

• Recover
– Restores table space to consistent state,using log and/or image copy files
– Recover to currency: useful if table space was modified outside of DB2
– Point-in-time (PIT) recovery:
• To specific RBA
• To specific image copy
• Separate index recovery required
– SAMPLIB: DB2RECOV

• CDB SuperRestore
– Faster recovery
– Recover test option
– SAMPLIB: CDBRSTR


DB2 Commands

– DISPLAY DATABASE
– STOP DATABASE
– START DATABASE
– DISPLAY UTILITY
– TERM UTILITY

• Syntax:
-command options
Ex. -DISPLAY DB(CLAS01DB)
• Issue via:
– DB2I, Option 7
– Platinum DB2 Command Processor (DB2C)


Display Database

• DISPLAY DATABASE
– Displays database and optionally table/index space status
– Use to identify any spaces in any kind of restricted mode (e.g., copy pending,recovery pending, etc)
– May help identify cause of -904 errors

• Syntax
-DISPLAY DATABASE(dbname)
SPACENAM(space-name)
LIMIT(*) RESTRICT


Result on Screen

• Partial output from display database command
– All index / table spaces are in RW status
-DIS DB(CLAS01DB) SPACE(*)
DSNT360I -DB2T ***********************************
DSNT361I -DB2T * DISPLAY DATABASE SUMMARY
* GLOBAL
DSNT360I -DB2T ***********************************
DSNT362I -DB2T DATABASE = CLAS01DB STATUS = RW
DBD LENGTH = 4028
DSNT397I -DB2T
NAME TYPE PART STATUS
-------- ---- ---- ------------------
MKDEPTTS TS RW
MKEMPTS TS RW
MKDEPTXB IX RW
MKDEPTX1 IX RW
MKDEPTX2 IX RW
MKEMPXB IX RW
MKEMPXB IX RW
MKEMPX1 IX RW
******* DISPLAY OF DATABASE CLAS01DB ENDED *******

Result on Screen

• Partial output from display database command
– Some spaces are in restricted mode
-DIS DB(CLAS01DB) SPACE(*)
DSNT360I -DB2T ***********************************
DSNT361I -DB2T * DISPLAY DATABASE SUMMARY
* GLOBAL
DSNT360I -DB2T ***********************************
DSNT362I -DB2T DATABASE = CLAS01DB STATUS = RW
DBD LENGTH = 4028
DSNT397I -DB2T
NAME TYPE PART STATUS
-------- ---- ---- ------------------
MKDEPTTS TS RW
MKEMPTS TS RW,RECP,CHKP
MKDEPTXB IX STOP
MKDEPTX1 IX RW
MKDEPTX2 IX RW
MKEMPXB IX RW,RBDP
MKEMPXB IX RW,RBDP
MKEMPX1 IX RW,RBDP
******* DISPLAY OF DATABASE CLAS01DB ENDED *******


Database Status Description
<
• Possible values for database status:
– RO The database is started for read activity only.
– RW The database is started for read and write activity.
– STOP The database is stopped.
– STOPP A stop is pending or is in progress for this DB.
– UT The database is started for utility processing only.

• Common values for space status:
– CHKP The object (a table space or a partition) is in check pending status.
– COPY The object (a table space or a partition) is in copy pending status.
– RBDP The object (an index space, index partition or logical index partition) is
in rebuild pending status. For logical partitions, status can also appear as
RBDP*.
– RECP The object (a table space, table space partition, index space, index
partition or logical index partition) is in recovery pending status.
– REORP The object (a table space or partition) is in recovery pending status.
– RO The table space, partition, index or index part is started for read-only.
– RW The table space, partition, index or index part is started for read write.
– STOP The table space, table space partition, index space or index space
partition is stopped.
– STOPP A stop is pending for the database, table space, table space
partition,index space or index space partition.
– UT The table space, table space partition, index space or index space
partition is started for utility processing only.
– UTRO A utility is in process on the table space, table space partition, index
space, or index space partition that allows only RO access.
– UTRW A utility is in process on the table space, table space partition, index
space or index space partition that allows RW access.
– UTUT A utility is in process on the table space, table space partition, index
space or index space partition that allows only UT access.



Stop Database

– Use to deallocate table/index spaces from the DB2 subsystem
– Required for certain types of ALTER DDL (MAXROWS)
– Required for certain “stand-alone” DB2


• Syntax
-STOP DATABASE(dbname)
SPACENAM(space-name)
• Example
-STO DB(CLAS01DB) SPACE(MKEMPTS)


Start Database

– Use to make database spaces available to DB2 subsystem
– Can start in any of the following modes
• RW - read/write (normal mode)
• RO - read-only
• UT - utility mode (no application access)
– Can apply to database or specific space

• Syntax
-START DATABASE(dbname)
SPACENAM(space-name)
ACCESS(mode)
• Example
-STA DB(CLAS01DB) SPACE(MKEMPTS)


Display Samples

-DIS DB(CLAS01DB) SPACE(*) RESTRICT LIMIT(*)
-DIS DB(CLAS01DB) SPACE(*) USE LIMIT(*)
-DIS DB(CLAS01DB) SPACE(*) ACTIVE USE LIMIT(*)
-DIS DB(CLAS01DB) SPACE(*) CLAIMERS LIMIT(*)
-DIS DB(CLAS01DB) SPACE(*) LOCKS LIMIT(*)
-DIS DB(CLAS01DB) SPACE(MK*)
-DIS DB(DSNDB06) SPACE(SYSDDF:SYSVIEWS)
-DIS DB(PWSE*) SPACE(*) RESTRICT LIMIT(*)


Display Utility

– Provides status of one or more DB2 utilities currently in progress
– Can use generic name for utility ID
e.g. ‘*’ for all utilities; ‘PRGI*’ for all utility ID’s that start with ‘PRGI’

• Syntax
-DISPLAY UTILITY(util-id)
• Example
-DIS UTIL(SADIMK*)


Term Utility

– Terminate one or more utilities
– Same generic name rules as those for DISPLAY UTILITY
– Be careful with generics: don’t kill some one else’s utility by mistake

• Syntax
-TERM UTILITY(util-id)
• Example
-TER UTIL(SADIMK1.RECVR)