Skip to main content

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)

Comments

Unknown said…
Nice post Arunam / Maruthy.
I reckon you've experience in mainframe DB2. One of our clients use that. I am not from mainframe environment but assisting in converting Oracle scripts to DB2 SQL scripts. We face some difficulties with followings:
1. BIGINT (will become decimal(20)).
2. BLOB & CLOB
3. Table spaces (Perhaps needs one for each table).
I would highly appreciate if you could shed some light share some experience, share some resources on the Internet at your earliest.
Thanks.
Anup

Popular posts from this blog

Mainframe Written test @ Accenture

1. What r the 2 function we can get by using INSPECT verb? (c) A. file handling, error handling B. opening a file, closing a file. C. char counting, replacing. D. none of the above 2. Indicate which of the following is not true about the formal parameters in a subroutine? (c) A. every formal parameter must appear in the linkage section of the subroutine B. every formal parameter must appear in the using phrase of the procedure division header of the subroutine C. A formal parameter name can appear more than once in the using phrases of the procedure division header of the subroutine. D. A formal parameter can not be declared with value clause in a subroutine 3. What is the value of A? (B) 01 A pic 9(3). Move 100 to A. Perform 1000-para thru 1000-exit. Display the value of a=A Stop run. 1000-para. If A=100 Perform 1000-exit Else Move 150 to A End-if. Move 200 to A. 1000-exit. Exit. a.100 b.200 c.150 d. none of the above is correct. 4. What is the value of B? (A) Move 1 to A Evaluate tr

How to Solve SOC7 Abend - with screen shots

Below process helps to find out the statement, caused the SOC7 error. Check the Sysout of RUNJCL . This shows the error statement and lists offset value Take the Offset Value 000003C0 Got to respective Compilation Job listing, check the sysprint Search for the offset value 0003C0 (delete +00 -- initial 3 letters of Offset value and search for it) check below 2 screen shots This Offset value is listed under line no 0045 – which refers to Move statement. Take this no. 045 and find for it in same sysprint. This points to the exact statement, caused SOC7 T his 045 pints to the Move statement 1526, this is the exact line in the program Check for the above line no. In source program . This points to the statement highlighted below . Check the statement, variable check-4, which is added to check-6. These are having different Picture clause. check-4 is alfhanumaric, holding some junk data, when this data is moved to Chcek-6 variable(of comp-3) creates SOC7 error. This is just an example to e

NULL VALUES and NULL INDICATORS in DB2

In DB2, the columns defined as NULL needs to be handled carefully else it will throw null exception error, in order to over come this error data type can be handled by using null indicator . NULL is stored using a special one-byte null indicator that is "attached" to every nullable column. If the column is set to NULL, then the indicator field is used to record this. Using NULL will never save space in a DB2 database design - in fact, it will always add an extra byte for every column that can be NULL. The byte is used whether or not the column is actually set to NULL. The indicator variable is transparent to an end user Consider below Table : Create Table SAMP_TAB SN CHAR (10) SNAME CHAR (10) STATUS CHAR (2) NOT NULL BY DEFAULT CITY CHAR (10) NOT NULL Note :: Unless you specify NOT NULL, the default is to allow for NULL In above table SN and SNAME columns holds null values by default, in order to handle these null variables we need to have NULL-INDICATORS declares in the Pro