The DB2 System Commands

Thursday Apr 10th 2003 by Marin Komadina
Share:

DB2 is enhanced with full set of system commands that is extended with each new version (87 of them as of version 7.2 DB2 UDB EEE). Marin Komadina gives an overview of these system commands, with a detailed explanation of db2ptree, db2empfa, db2gov, and db2osconf .

Beside the standard Command Line Processor Commands (CLP), DB2 is enhanced with full set of system commands. DB2 system commands are used to access and maintain the DB2 database and can be entered from the OS command line, or can be used in a shell script. Set of commands depends on the version and packaging models (Standard, EE or EEE) installed on the machine. Every new DB2 version extends the existing set of the commands; in version 7.2 DB2 UDB EEE there is already 87 such commands. A DBA has a chance to be familiar with some of these commands only if he has a problem and needs to use some of them. More over, there is a slight difference in command sets between platforms.

In this article I will explain some of the special DB2 system commands.

This article covers:

  • DB2 System Commands Overview
  • db2ptree - Show DB2 Process Tree for Sun Solaris
  • db2empfa - Enable Multi-page File Allocation
  • db2gov - DB2 Governor
  • db2osconf - DB2 Operating System Configuration
  • Conclusion

DB2 System Commands Overview

All DB2 system commands are installed in the sqllib/bin directory by the installation procedure. Most commands have interactive help so simply typing the command will generate a listing of all possible command switches.

Database version 7.1 with Fixpack 7 has 87 different db2 system commands including "db2_" system commands for control of multipartitioned database.

>> db2level
DB21085I  Instance "db2udb1" uses DB2 code release "SQL07025" with level identifier "03060105" and 
informational tokens "DB2 v7.1.0.68", "s020616" and "U484483"

>> ls sqllib/bin/db2* | wc -l
      87

Here is standard set of the system commands for DB2 version 7:

Number

System Command

Explanation

1

Db2admin

DB2 Administration Server

2

db2adutl

Work with TSM Archived Images

3

db2advis

DB2 Index Advisor

4

db2audit

Audit Facility Administrator Tool

5

db2atld

Autoload

6

db2batch

Benchmark Tool

7

db2bfd

Bind File Description Tool

8

db2cap

CLI/ODBC Static Package Binding Tool

9

db2cc

Start Control Center

10

db2cdbcr

Create Control Database for Data Warehouse

11

db2cfexp

Connectivity Configuration Export Tool

12

db2cfimp

Connectivity Configuration Import Tool

13

db2cidmg

Remote Database Migration Tool

14

db2ckbkp

Check Backup

15

db2ckmig

Database Pre-migration Tool

16

db2ckrst

Check Incremental Restore Image Sequence

17

db2cli

DB2 Interactive CLI

18

db2cmd

Open DB2 Command Window

19

db2dclgn

Declaration Generator

20

db2drdat

DRDA Trace

21

db2empfa

Enable Multi-page File Allocation

22

db2eva

Event Analyzer

23

db2evmon

Event Monitor Productivity Tool

24

db2exfmt

Explain Table Format Tool

25

db2expln

DB2 SQL Explain Tool

26

db2flsn

Find Log Sequence Number

27

db2fs

First Steps

28

db2gncol

Update Generated Column Values

29

db2gov

DB2 Governor

30

db2govlg

DB2 Governor Log Query

31

db2icrt

Create Instance

32

db2idrop

Remove Instance

33

db2ilist

List Instances

34

db2imigr

Migrate Instance

35

db2inidb

Initialize a Mirrored Database

36

db2ipxad

Get IPX/SPX Internetwork Address

37

db2iupdt

Update Instances

38

db2ldcfg

Configure LDAP Environment

39

db2licm

License Management Tool

40

db2look

DB2 Statistics and DDL Extraction Tool

41

db2move

Database Movement Tool

42

db2mscs

Set up Windows NT Failover Utility

43

db2nchg

Change Database Partition Server Configuration

44

db2ncrt

Add Database Partition Server to an Instance

45

db2ndrop

Drop Database Partition Server from an Instance

46

db2perfc

Reset Database Performance Values

47

db2perfi

Performance Counters Registration Utility

48

db2perfr

Performance Monitor Registration Tool

49

db2profc

DB2 SQLJ Profile Customizer

50

db2profp

DB2 SQLJ Profile Printer

51

db2rbind

Rebind all Packages

52

Db2relocatedb

Relocate Database

53

db2sampl

Create Sample Database

54

db2set

DB2 Profile Registry Command

55

db2sql92

SQL92 Compliant SQL Statement Processor

56

db2start

Start DB2

57

db2stop

Stop DB2

58

db2support

Problem Analysis and Environment Collection Tool

59

db2sync

Start DB2 Synchronizer

60

db2tbst

Get Tablespace State

61

db2trc

Trace

62

db2uiddl

Prepare Unique Index Conversion to V5 Semantics

63

db2untag

Release Container Tag

Version 8.1 has more system commands and the DBA should regularly check /sqllib/bin directory after installation for the new stuff.

db2ptree - Show DB2 Process Tree for Sun Solaris

There is the special system command db2ptree which is applicable only on the Sun Solaris platform and it is installed as a part of DB2 version 6, 7 or 8 installation on Sun Solaris.

db2ptree is used to show the DB2 processes tree because the ps command on Sun's operating system does not provide the real process name of the DB2 processes (instead ps shows only "db2sysc"). With db2ptree we can see all the DB2 instance processes in the tree structure with the process real names (db2wdog, db2pfchr, db2resyn, db2gds...).

Command switches:

-i <instance>
-p <pid> Show tree from pid down
-s Show db2watch status for each process
-c Show process uid credentials
-v Show process arguments as well

db2ptree called without any parameters shows the DB2 processes of your current instance.

DB2 regular process list on Solaris:

>> ps -edf | grep db2 | more
...
 db2udb1 15318  5126  0 19:37:14 ?        0:00 db2sysc
 db2udb1  5443  5128  0 21:40:21 ?        0:01 db2sysc
 db2udb1  5117  5108  0 21:40:08 ?        0:00 db2sysc
 db2udb1  5116  5105  0 21:40:08 ?        0:00 db2sysc
 db2udb1  5115  5107  0 21:40:08 ?        0:00 db2sysc
    root  5110     1  0 21:40:07 ?        0:00 db2sysc
    root  5105     1  0 21:40:07 ?        0:00 db2sysc
    root  5106     1  0 21:40:07 ?        0:00 db2sysc
    root  5107     1  0 21:40:07 ?        0:00 db2sysc
    root  5108     1  0 21:40:07 ?        0:00 db2sysc
 db2udb1  5118  5110  0 21:40:08 ?        0:00 db2sysc
 db2udb1  5127  5119  0 21:40:08 ?        0:00 db2sysc
    root  5126  5119  0 21:40:08 ?        0:13 db2sysc
    root  5128  5117  0 21:40:09 ?        0:01 db2sysc
    root  5129  5116  0 21:40:09 ?        0:02 db2sysc
    root  5130  5115  0 21:40:09 ?        0:01 db2sysc
    root  5131  5118  0 21:40:09 ?        0:02 db2sysc
 db2udb1  5134  5118  0 21:40:09 ?        0:00 db2sysc
 db2udb1  5135  5115  0 21:40:09 ?        0:00 db2sysc
 db2udb1  5136  5117  0 21:40:09 ?        0:00 db2sysc
...

From the above output, you cant recognize the different db2 processes. Using the db2ptree system command (below), we can see specific processes:

>> db2ptree | grep 5110 
5110  db2wdog 4                       ' watchdog process 
>>  db2ptree | grep 5118
  5118  db2sysc 4                      ' system controller process
>> db2ptree | grep 5131          
    5131  db2gds 4                     ' generic demon spawner 

or the full db2 database process tree:

>> db2ptree  
14438  db2wdog 1
  14504  db2sysc 1
    14512  db2gds 1
      4182  db2agnta (ARTIST) 1
      14523  db2resyn 1
      14524  db2srvlst 1
      14585  db2fmtlg 1
      14587  db2cart 1
      17915  db2loggr (ARTIST) 1
      18082  db2dlock (ARTIST) 1
      18173  db2pfchr 1
      18306  db2pclnr 1
      24851  db2event 1
    14514  db2fcmdm 1
    14521  db2pdbc 1
    14522  db2ipccm 1
      24755  db2agent (instance) 1
    14527  db2panic (idle) 1
24676  db2govd

It is very important for every DBA to fully understand each database process listed in the output from the db2ptree command.

Here is sorted description for some of the most important database processes:

DB2 Instance Global Process

  • db2wdog -'watchdog' monitors the other processes and cleans up after abnormal terminations.
  • db2sysc -'system controller' handles startup, shutdown and other housekeeping tasks.
  • db2gds -'Generic Daemon Spawner' is responsible for spawning most DB2 daemons.
  • db2ipccm -The 'IPC Communication Manager' handles communication requests from local clients, spawning an agent for each.
  • db2tcpcm -The 'TCP/IP Communications Manager' handles TCP/IP connect requests from remote clients.
  • db2tcpdm -The 'TCP/IP Administration Manager' handles TCP/IP requests for database administration jobs.
  • db2resyn - The 'resync' handles recovery of indoubt transactions for Distributed Unit of Work (DUOW).

DB2 Instance Connect Process

  • db2agent - An 'agent' that handles all SQL processing that the application requests.>
  • db2dlock - 'Database Deadlock Detector' looks for, and resolves, deadlocks on a particular database.
  • db2loggr - 'Database Logger' handles all log I/O for a particular database.
  • db2pfchr - 'Prefetcher' allows for more efficient processing via read-ahead, big-block and parallel I/O.
  • db2pclnr - 'Page Cleaner' increases efficiency by asynchronously writing dirty pages when the CPU would otherwise be idle.
  • db2bp - DB2 CLP process, spawned for a Command Line Processor session.

db2empfa - Enable Multi-page File Allocation

The DB2 system command db2empfa is used by databases with SMS (System Managed Tablespace) tablespace when a bulk load is expected. The SMS tablespace is normally expanded on demand, one page at a time. To minimize internal overhead for the tablespace extension we can enable database multi-page file allocation. Tablespace will be extended in groups of pages or extents. For a multinode database, the db2empfa system command only affects the node upon which it is executed.

Getting the actual database configuration:

db2 => get db cfg for ARTIST 

       Database Configuration for Database ARTIST
 
 Database configuration release level                    = 0x0900
 Database release level                                  = 0x0900
 
 Database territory                                      = US
 Database code page                                      = 819
 Database code set                                       = ISO8859-1
 Database country code                                   = 1
 
...
 Multi-page file allocation enabled                      = NO

Multi-page file allocation can be enabled by running the DB2 system command db2empfa on the target database:

$ db2empfa ARTIST 

After db2empfa execution against target database, we can check the database configuration parameter Multi-page file allocation enabled for status:

db2 => get db cfg for ARTIST 

       Database Configuration for Database ARTIST
 
 Database configuration release level                    = 0x0900
 Database release level                                  = 0x0900
 
 Database territory                                      = US
 Database code page                                      = 819
 Database code set                                       = ISO8859-1
 Database country code                                   = 1
 
...
 Multi-page file allocation enabled                      = YES

Database has enabled Multi-page file allocation.

db2gov - DB2 Governor

System command db2gov is started on logical node monitors and changes the behaviour of applications that run against a database. After starting, governor first reads the configuration file after which it checks all applications against rules from configuration file. When the governor finishes checking, it sleeps for the interval specified in the configuration file. This looping is executed over and over.

Rules in the configuration file are checked against database statistics for each application. Upon discovery of an application that is outside of the predefined conditions, governor has following options:

- force the application
- change the application's priority
- change the schedule for the application

The governor writes a record of the any action to a log file, together with all errors and messages. The governor daemon is not a database application, and, therefore, does not maintain a connection to the database.

If the governor uses too much CPU, you can increase its wake-up interval to reduce its CPU usage.

Governor start command:

(db2gov START database NODENUM node-num config-filelog-file)

db2gov start artist /home/artist/gov/artist.gov.cfg artist.gov

Governor stop command:

( db2gov STOP database NODENUM node-num)

db2gov stop artist

The parameters are as follows:

  • start database -starts the governor daemon to monitor the specified database
  • nodenum node-num - specifies the database partition on which to start the governor daemon.

  • config-file - specifies the configuration file to use when monitoring the database.
  • log-file - specifies the base name of the file to which the governor writes log records. The log file is stored in the log subdirectory of the sqllib directory.
  • stop database - stops the governor daemon that is monitoring the specified database.
  • nodenum node-num -specifies the database partition on which to stop the governor daemon.

Example of governor configuration file:

interval 60;
dbname artist;
account 60;

desc "Limit all processes to 60 cpu minutes (5400 seconds) "
setlimit cpu 5400 action force;

desc "Load jobs have no limit  "
applname db2bp_s
setlimit cpu -1;

desc "Op id's - unlimited cpu "
authid oper01, oper02, dbadm 
setlimit cpu -1;

desc "Limit all processes to 120 min (7200 seconds)
idle time" 
setlimit idle 7200 action force;

desc "Op id's - unlimited idle "
authid oper01, oper02, dbadm
setlimit idle -1;

db2osconf - DB2 Operating System Configuration

The db2osconf is not a well-known system command. It is used to compare machine kernel parameters with parameters recommended from IBM. It is installed as part of DB2 fixpack 6 and is located with other system commands:

# ls -lrt | grep db2osconf
-r-xr-xr-x   1 bin      bin        23284 Sep  5  2002 db2osconf

The db2osconf command is sensitive to Solaris OS level. A 64-bit version of db2osconf will be necessary for a 32-bit or 64-bit DB2 database version for Solaris.

db2osconf system switches:

 -c                 # Client only
 -f                 # Compare to current
 -l                 # List current
 -m <mem in GB>     # Specify memory in GB
 -n <num CPUs>      # Specify number of CPUs
 -p <perf level>    # Msg Q performance level (0-3)
 -s <scale factor>  # Scale factor (1-3)

Examples of db2conf usage:

a.) Listing kernel configuration parameters relevant to DB2:

# db2osconf -l 
 
msgmap = 258
msgmax = 65535
msgmnb = 65535
msgmni = 256
msgssz = 16
msgtql = 1024
msgseg = 32768
semmap = 1026
semmni = 300
semmns = 2048
semmnu = 2048
semmsl = 1000
semopm = 100
semume = 10
semvmx = 32767
semaem = 16384
semusz = 96
shmmax = 1645831782
shmmin = 1
shmmni = 100
shmseg = 16
max_nprocs   = 7978
maxuprc      = 7973
availrmem    = 51738
freemem      = 20197
maxmem       = 62432
physmem      = 63804
max_ncpus    = 1
ncpus        = 1
ncpus_online = 1
maxusers     = 498
 
Total kernel space for IPC:
0.01MB (shm) + 0.94MB (sem) + 0.54MB (msg) == 1.49MB (total)

b.) Listing recomended kernel parameters for local machine with 10 GB RAM size:

# db2osconf -m10
memorySize = 10
 
set msgsys:msginfo_msgmax = 65535
set msgsys:msginfo_msgmnb = 65535
set msgsys:msginfo_msgssz = 32
set msgsys:msginfo_msgseg = 32767
set msgsys:msginfo_msgmap = 1794
set msgsys:msginfo_msgmni = 1792
set msgsys:msginfo_msgtql = 1792
set semsys:seminfo_semmap = 2050
set semsys:seminfo_semmni = 2048
set semsys:seminfo_semmns = 4300
set semsys:seminfo_semmnu = 2048
set semsys:seminfo_semume = 200
set shmsys:shminfo_shmmax = 4294967295
set shmsys:shminfo_shmmni = 2048
set shmsys:shminfo_shmseg = 200
 
Total kernel space for IPC:
0.23MB (shm) + 1.13MB (sem) + 1.24MB (msg) == 2.61MB (total)
 

c.) Comparing recomended kernel parameters for local machine with current kernel parameters:

#  db2osconf -f 

msgssz = 32     (16)
msgseg = 32767  (32768)
msgtql = 1280   (1024)
msgmni = 1280   (256)
msgmap = 1282   (258)
semmap = 1538   (1026)
semmni = 1536   (300)
semmns = 3226   (2048)
semmnu = 1536   (2048)
semume = 200    (10)
shmmax = 470414131      (1645831782)
shmmni = 1536   (100)
shmseg = 200    (16)

Conclusion

New DB2 system commands are enhanced with each upgrade. DBAs should regularly check the installation directory for updates. A full set of system commands are installed only with high-end enterprise-class databases equivalents DB2 UDB EEE.

An experienced DBA might need some extra commands in the diagnostics and tuning areas, which IBM did not provide so far.

For example DB2 has no easy way of analysing trace command output and no tool to tune SQL by rewriting it. Also DB2 does not have a report that tells users which objects have statistics and when these statistics were generated.

Other than that, DB2 has very sophisticated tools such as the Performance SMART Guide, the Governor and the Queuing Patroller, all of which are top database tools on the market.

» See All Articles by Columnist Marin Komadina

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved