DB2 Administration, Monitoring, Tuning in SAP Environment
Agenda Table space management Table space concepts and administration Automatic Storage Database administration Log file management Backup / Restore / Recovery Table maintenance (runstats, reorg) DB2 Parameters Problem determination Monitoring and performance tuning Memory Management and STMM Monitoring Tools DB2 Parallelism DB2 / SAP integration SAP DB2 file system and table space layout SAP DB2 Cockpit ...
15 February, 2012
2
DB2 Table Space Management
15 February, 2012
3
Table Spaces – Overview DB2 table spaces store data and indexes A table space consist of one or more containers. A container can be a Directory File Device There are two types of table spaces: System managed space (SMS): The corresponding containers are directories. The operating system's file manager controls the storage space. Database managed space (DMS): The corresponding containers are files or devices. The database manager controls the storage space. 15 February, 2012
4
Table Spaces And Containers Database objects
Physical storage objects
System DB2 Instance Database SMS Table Space • Tables • Indexes • Long data DMS Table Space • Tables • Indexes • Long data
Containers
Directory 1
... Directory N
Containers
File 1
... File N
15 February, 2012
5
Table Spaces – Usage types Depending on the usage type, the following table space categories can be distinguished: Regular table space Stores all permanent data Uses 4 byte RIDs Large table space Always of type DMS. V8: For example used to LONG VARCHAR and LOB data. V9: Stores all permanent data. Uses 6 byte RIDs System temporary table space Stores temporary tables, i.e. work areas used by the database manager to perform operations such as sorts or joins.
15 February, 2012
6
Characteristics of DMS Table Spaces Table space containers are either pre-allocated files (DMS file containers) or raw devices (DMS device containers) Containers can be added, removed, or resized The space allocation unit is an extent Extents are striped across the containers of a table space in a round-robin fashion. An extent consists of two or more pages
15 February, 2012
7
Characteristics of SMS Table Spaces
SMS table spaces use the file system manager of the operating system to manage the space where data is stored Containers cannot be added after the table space has been created. Table space capacity can be increased by enlarging underlying operating system file systems The data in the table space is striped by extent across the containers Database objects are stored in different files in the corresponding directories. For example SQL*.DAT files store information about tables (the asterisk * is replaced by a unique set of digits that identifies the table) Additional files may exist for a table (depending on data types, e.g. LONG fields and corresponding indexes that belong to the table).
15 February, 2012
8
Storage of Table and Index Data Table data and corresponding indexes of a single table can be stored in different tablespaces: Data can be stored in a DMS or SMS table space Indexes can be stored separately from the data in a DMS or SMS table space. Large data types (e.g. LONG VARCHAR) can be stored in a separate DMS LARGE table space.
15 February, 2012
9
Table Space Page Sizes Table spaces can be created with the following page sizes: 4 KB 8 KB 16 KB 32 KB A dedicated buffer pool is required for each page size which is used by the table spaces. The default page size is 4 KB. SAP uses uniform page size of 16K with Basis >= 6.40 Only one buffer pool required. Increased table space size limits due to larger page size
15 February, 2012
10
Page Sizes And Table Space Limits (V8) 4KB
64GB 128GB
8KB
256GB
16KB
512GB
32KB Table space size
Page size 16M
255
4x109 Rows
Row ID (RID) 4 Bytes For tables in all table spaces (regular, temporary, DMS, SMS) 15 February, 2012
11
Page Sizes And Table Space Limits (V9) 4KB
2TB 4TB
8KB
8TB
16KB
16TB
32KB Table space size
Page size 512M
~2K
1.1x1012 Rows
Row ID (RID) 6 Bytes For tables in LARGE table spaces (DMS only) Also all SYSTEM and USER temporary table spaces 15 February, 2012
12
Table Space Statements
15 February, 2012
13
Table Space Statements The CREATE TABLESPACE statement defines a new table space within the database, assigns containers to the table space, and records the table space definition and attributes in the catalog The ALTER TABLESPACE statement is used to modify an existing table space Required privileges for the user who executed these statements are SYSCTRL or SYSADM authority
15 February, 2012
14
CREATE TABLESPACE – DMS Example CREATE LARGE TABLESPACE AKS#BTAB PAGESIZE 16K MANAGED BY DATABASE USING (file '/db2/AKS/sapdata1/NODE0000/AKS#BTAB.container001' 1000 M) EXTENTSIZE 2 FILE SYSTEM CACHING AUTORESIZE YES INCREASESIZE 200 M DROPPED TABLE RECOVERY OFF
15 February, 2012
15
CREATE TABLESPACE – SMS Example CREATE TEMPORARY TABLESPACE SYSTOOLSTMPSPACE IN DATABASE PARTITION GROUP IBMCATGROUP PAGESIZE 16K MANAGED BY SYSTEM USING ('/db2/V91/saptemp1/NODE0000/temp16/SYSTOOLSTMPSPAC E.container000') EXTENTSIZE 2 PREFETCHSIZE AUTOMATIC BUFFERPOOL BP_STD_16K DROPPED TABLE RECOVERY OFF
15 February, 2012
16
Getting Information About Table Spaces DB2 CLP commands: db2 list tablespaces [show detail] db2 list tablespace containers for
[show detail] DB2 snapshot: db2 get snapshot for tablespaces on SQL table functions: Example: db2 "select PAGE_SIZE, EXTENT_SIZE, PREFETCH_SIZE from table(SNAPSHOT_TBS_CONT('sample',0) as o" Command db2pd: db2pd –db –tablespaces 15 February, 2012
17
Example – LIST TABLESPACES SHOW DETAIL Tablespaces for Current Database Tablespace ID Name Type Contents State Detailed explanation: Normal Total pages Useable pages Used pages Free pages High water mark (pages) Page size (bytes) Extent size (pages) Prefetch size (pages) Number of containers […] 15 February, 2012
= = = = =
0 SYSCATSPACE Database managed space Any data 0x0000
= = = = = = = = =
166400 166398 51408 114990 51408 4096 2 2 1
18
Example – LIST TABLESPACES SHOW DETAIL […] Tablespace ID Name Type Contents State Detailed explanation: Normal Total pages Useable pages Used pages Free pages High water mark (pages) Page size (bytes) Extent size (pages) Prefetch size (pages) Number of containers
15 February, 2012
= = = = =
1 SYSTOOLSPACE System managed space Any data 0x0000
= = = = = = = = =
12761 12761 12761 Not applicable Not applicable 4096 32 32 1
19
Example – LIST TABLESPACE CONTAINERS Output on Windows using DMS file containers: db2 list tablespace containers for 5 Tablespaces Containers for Tablespace 5 Container ID = 0 Name = c:\DB2\data.cont1 Type = Disk
Output on UNIX using device containers: db2 list tablespace containers for 5 Tablespaces Containers for Tablespace 5 Container ID = 0 Name = /dev/rdata1 Type = Disk
15 February, 2012
20
Changing the Size of a Table Space
15 February, 2012
21
Changing the Size of a Table Space
The ALTER TABLESPACE statement lets you add a container to an existing DMS table space or extend a container to increase its storage capacity
You can not ALTER a SMS table space – instead you need to increase the size of the underlying file system
Data will be striped evenly across all of the table space containers until the individual containers fill up
Adding a container to a DMS table space which is smaller than existing containers may result in reduced performance If some of the smaller containers become full, this may result in reduced I/O parallelism (e.g. during data prefetching).
15 February, 2012
22
Rebalancing
When new containers are added to a table space or existing containers are extended, a rebalance of the table space data may occur
The process of rebalancing is done in an attempt to keep data evenly distributed across all containers of the table space.
Access to the table space is not restricted during rebalancing but there can be a significant impact on performance
To avoid rebalancing you may extend all existing containers by the same size or add new containers in a new stripe set
15 February, 2012
23
Rebalancing (Cont’d) A rebalancing is done asynchronously after the following ALTER TABLESPACE options: ADD will result in forward rebalancing DROP will result in reverse rebalancing To avoid rebalancing choose the following ALTER TABLESPACE options: RESIZE / EXTEND / REDUCE ALL CONTAINERS BEGIN NEW STRIPE SET to create a new stripe set
15 February, 2012
24
ALTER TABLESPACE – Examples To add a new DMS container file to table space PSAPBTABD: ALTER TABLESPACE PSAPBTABD ADD (FILE '/db2/P01/sapdata5/PSAPBTABD.container005' 25000) To extend all table space containers of table space ICP#STABD by 10000 pages: ALTER TABLESPACE ICP#STABD EXTEND (ALL 10000) To resize (e.g. shrink) all table space containers for table space Y9C#POOLI to a new size of 1000 Megabytes: ALTER TABLESPACE Y9C#POOLI RESIZE (ALL 1000) To drop a DMS container from table space SYSCATSPACE: ALTER TABLESPACE SYSCATSPACE DROP (FILE '/db2/P01/sapdata5/SYSCATSPACE.container005') 15 February, 2012
25
ALTER TABLESPACE – Examples (Cont’d) To change the buffer pool for table space PSAPBTABD: ALTER TABLESPACE PSAPBTABD BUFFERPOOL SAPBP_32K To change the prefetch size of table space Y9C#POOLI: ALTER TABLESPACE Y9C#POOLI PREFETCHSIZE AUTOMATIC To turn off file system caching for table space ICP#STABD: ALTER TABLESPACE ICP#STABD NO FILE SYSTEM CACHING To turn off dropped table recovery for table space APPL1: ALTER TABLESPACE APPL1 DROPPED TABLE RECOVERY OFF
15 February, 2012
26
Automatic Resizing Of DMS Table Spaces DMS table spaces have a feature called "auto-resize“ (available with DB2 V8.2.2). DB2 can automatically enlarge DMS table space for which the autoresize attribute is activated. SMS table spaces have similar capabilities for growing automatically but the term "auto-resize" is used exclusively for DMS Two additional attributes, MAXSIZE and INCREASESIZE, are associated with auto-resize table spaces: The MAXSIZE (Maximum Size) clause defines the maximum size for the table space. The INCREASESIZE clause defines the amount of space which is automatically added when the table space becomes full.
15 February, 2012
27
How to enable Auto-resize Creating a DMS tablespace and enabling the auto-resize feature: CREATE TABLESPACE DMS1 MANAGED BY DATABASE USING (FILE '/db2data/DMS1' 10 M) AUTORESIZE YES You can also enable or disable the auto-resize feature after a DMS table space has been created by using the ALTER TABLESPACE statement: ALTER TABLESPACE NUC#BTABD AUTORESIZE YES ALTER TABLESPACE NUC#BTABD AUTORESIZE NO
15 February, 2012
28
Auto-resize – Monitoring (1/2) Automatic resizing for DMS table spaces is displayed as part of the table space monitor snapshot output: Tablespace name
= NUC#USER1D
[…] Using automatic storage
= No
Auto-resize enabled
= Yes
File system caching
= Yes
[…] Current tablespace size (bytes)
= 10485760
Maximum tablespace size (bytes)
= NONE
Increase size (bytes)
= AUTOMATIC
Time of last successful resize
= 05/18/2005 16:48:51.474454
Last resize attempt failed
= No
15 February, 2012
29
Auto-resize – Monitoring (2/2) The db2pd command does also provide information about the automatic resizing of DMS tablespaces db2pd –db -tab[lespaces] Tablespace Autoresize Statistics: Address Id AS AR Auto LastResize LRF 0x00000004C3957960 0 No Yes No 03/22/2006 16:19:51.434751 No 0x00000004C395F960 2 No Yes No None No 0x00000004C3962920 3 No Yes No None No 0x00000004C3962FA0 4 No Yes No None No 0x00000004C396A0A0 5 No Yes No 04/04/2006 07:42:36.268448 No 0x00000004C396B1C0 6 No Yes No None No 0x00000004C396B840 7 No Yes No 04/05/2006 15:41:02.263465 No
15 February, 2012
InitSize
IncSize
IIP MaxSize
0
-4096
No
None
0
-16384
No
None
0
-4096
No
None
0
-16384
No
None
0
-16384
No
None
0
-16384
No
None
0
-16384
No
None
30
Use of Concurrent/Direct IO Without file system caching DB2 always attempts to use concurrent I/O (CIO) wherever possible: In cases where CIO is not supported (for example, if JFS is used), direct I/O (DIO) will be used instead. NFS file systems usually do not support DIO/CIO (will result in warning messages in db2diag.log) The settings for the table spaces can be retrieved with the GET SNAPSHOT FOR TABLESPACES command Alternatively, use db2pd –tab … (search for column FSC)
15 February, 2012
31
Automatic Storage
15 February, 2012
32
Automatic Storage – Overview If automatic storage is enabled for a database the container management is completely handled by DB2. Automatic storage databases have a set of one or more storage paths associated with them. Example: CREATE DATABASE myDB ON /db2/ABC1, /db2/ABC2 If a table space is defined as "managed by automatic storage" DB2 performs the following tasks automatically: Initial creation of table space containers in the given storage paths. Extension of table space containers if required. Adding table space containers if required.
15 February, 2012
33
Automatic Storage – Overview (Cont’d) A database can only be enabled for automatic storage when it is first created You cannot enable automatic storage for a database that was not originally defined to use it Similarly, you cannot disable automatic storage for a database that was originally designed to use it. New storage paths can be added to the database later on. Example: ALTER DATABASE myDB ADD STORAGE ON /db2/ABC3
15 February, 2012
34
Automatic Storage – Database Path and Storage Path The database path is the path where DB2 stores control files for the database. It is specified by the DBPATH ON clause in the CREATE DATABASE command. The storage path is the path where DB2 creates automatic storage table space containers. It is specified by the ON clause. Multiple storage paths can be specified. If the DBPATH ON clause is not specified, the first path listed in the ON clause indicates the database path (in addition to it being a storage path).
15 February, 2012
35
Automatic Storage Examples CREATE DATABASE ABC ON /db2/ABC Database is not automatic storage enabled (pre V8.2.2 default) Attention: With V9 the database is automatic storage enabled per default CREATE DATABASE ABC ON /db2/ABC1, /db2/ABC2 Database is automatic storage enabled Database Path: /db2/ABC1 Storage Paths: /db2/ABC1, /db2/ABC2 CREATE DATABASE ABC ON /db2/ABC/sapdata1, /db2/ABC/sapdata2 DBPATH ON /db2/ABC Database is automatic storage enabled Database Path: /db2/ABC Storage Path: /db2/ABC/sapdata1, /db2/ABC/sapdata2 15 February, 2012
36
Automatic Storage & SAPinst Automatic storage is the default setup for all SAP NetWeaver 2004s (and higher) systems
15 February, 2012
37
Automatic Storage – Table Spaces (1/3) Automatic storage table spaces are an extension of the existing SMS and DMS table space types: If the table space being created is a REGULAR or LARGE table space, it uses DMS file containers If the table space being created is a TEMPORARY table space, it uses SMS directory containers To create an automatic storage table space: CREATE LARGE TABLESPACE myTS MANGED BY AUTOMATIC STORAGE
15 February, 2012
38
Automatic Storage – Table Spaces (2/3) The names associated with the automatic storage containers have the following format: / / NODE0000/ / Table space ID T#######/ C#######. Container ID EXT(ension) can be: CAT (system catalog), TMP (system temporary), UTM (user temporary), USR (user regular), LRG (user large) 15 February, 2012
39
Automatic Storage – Table Spaces (3/3) Example paths used for automatic storage in a NW04S system: Tablespace Containers for Tablespace 0 Container ID = 0 Name = /db2/SAT/sapdata1/db2sat/NODE0000/SAT/T0000000/C0000000.CAT Type = File Tablespace Containers for Tablespace 3
Temporary table spaces are NOT using automatic storage in SAP environments
Container ID = 0 Name = /db2/SAT/saptemp1/NODE0000/temp16/PSAPTEMP16.container000 Type = Path Tablespace Containers for Tablespace 6 Container ID = 0 Name = /db2/SAT/sapdata1/db2sat/NODE0000/SAT/T0000006/C0000000.USR Type = File 15 February, 2012
40
Automatic Storage – Monitoring Storage Paths A database snapshot includes the list of storage paths associated with the database: db2 get snapshot for db on Number of automatic storage paths Automatic storage path Automatic storage path
= 2 = /db2/SXI/sapdata1 = /db2/SXI/sapdata2
In addition you can use db2pd command: db2pd –db –sto[ragepaths] Database Storage Paths: Number of Storage Paths
2
Address PathName 0x237E0590 /db2/SXI/sapdata1 0x24260D90 /db2/SXI/sapdata2 15 February, 2012
41
DB2 Database Administration
15 February, 2012
42
DB2 Database Administration Log file management Backup / Restore / Recovery Table maintenance (runstats, reorg) DB2 Parameters Problem determination
15 February, 2012
43
DB2 Logging – Overview Log Buffer
Online Active Log
15 February, 2012
db2agent
db2pclnr
db2loggw
synchronous write on COMMIT/ ROLLBACK every second when log buffer is full
Buffer Pool
asynchronous write chngpgs_threshold is reached SOFTMAX has been reached No BP pages are available during insert/update
Database Files 44
DB2 Logging – Circular Logging 1
Primary Logfiles
n
3
2
1
Secondary log files – allocated if necessary / defined
n
Default configuration after database creation Not supported for production environments (no online backups possible – no point-in-time recovery) 15 February, 2012
45
DB2 Logging – Archive Logging db2uext2 – located in $HOME/sqllib/adm (not provided by IBM)
DB2 archives log file (calls db2uext2 in V8.1)
12
13
Active: Contains information for noncommitted or nonexternalized transactions
14
Offline archive: Archived from active log subdirectory to disk/ tape/ TSM … 15 February, 2012
Online archive: Contains information for committed and externalized transactions Stored in the active log directory
15
16
46
DB2 Log File Management DB2 Engine
TSM
Vendor
15 February, 2012
Overflow Log
History File
Disk
db2tapemgr
db2tapemgr
Failarch Path
Mirror Log
db2logmgr
Log Dir
Beginning with DB2 Version 8.2 log file management is part of the DB2 engine Supports disk, TSM, vendor library, user exit (db2uext2) Log file location information is stored in history file Configuration via DB configuration parameters
Tape
47
Logging Configuration Parameters (1/4) Log archive method 1 (LOGARCHMETH1), log archive method 2 (LOGARCHMETH2) These parameters cause the database manager to archive log files to a location that is not the active log path If both of these parameters are specified, each log file is archived twice (e.g. to different media types) The parameter value includes a media type and, in some cases, a target field. Use a colon (:) to separate the values
15 February, 2012
48
Logging Configuration Parameters (2/4) Valid values for LOGARCHMETH1and LOGARCHMETH2 are: DISK: Log files are archived to disk (e.g. log_archive directory) TSM: Tivoli Storage Manager is used for archiving. VENDOR: Vendor library is invoked. DB2 provides a vendor API for the log file management USEREXIT Program db2uext is invoked (not part of DB2 product) LOGRETAIN Log files are not archived automatically, i.e. inactive log files need to be archieved manually. OFF 15 February, 2012
49
Logging Configuration Parameters (3/4) Log archive options 1 (LOGARCHOPT1), log archive options 2 (LOGARCHOPT2) Specifies a string which is passed on to the TSM server or vendor APIs Archive retry delay (ARCHRETRYDELAY) Specifies the amount of time (in seconds) to wait between attempts to archive log files after the previous attempt fails The default value is 20 seconds Failover archive path (FAILARCHPATH) Specifies an alternate directory for the archive log files if the log archive method specified fails
15 February, 2012
50
Logging Configuration Parameters (4/4) Log file size (LOGFILSIZ) Size of log files (in 4 KB) Number of primary log files (LOGPRIMARY) Specifies the number of primary log files to be pre-allocated when starting the database Number of secondary log files (LOGSECOND) Specifies the number of secondary log files that are created and used for recovery log files (only as needed)
15 February, 2012
51
Logging Configuration – Examples To turn on direct archiving of log files to Tivoli Data Protector on AIX use the following command: db2 update database cfg for QHA using LOGARCHMETH1 VENDOR:/usr/tivoli/tsm/tdp_r3/db264/libtdpdb264.a db2 update database cfg for QHA using LOGARCHOPT1 /db2/db2qha/tdpr3/vendor.env To turn on archiving of log files to disk use the following command: db2 update database cfg for TST using LOGARCHMETH1 DISK:/db2/TST/log_archive
15 February, 2012
52
LIST HISTORY COMMAND The LIST HISTORY command lists entries in the recovery history file To see entries regarding archive log operations the archived logs issue: db2 list history ARCHIVE LOG all for database
15 February, 2012
53
Closing Active Log File On Demand A DB2 command allows users to close and archive the active log at any time Allows users to acquire a complete set of log files up to the point in time at which the command is executed Only useable on recoverable databases (i.e. database is defined to use archival logging) Command: db2 archive log for database
15 February, 2012
54
Log Disk Full Vs. Log File Full Two common problems that may occur regarding log space are: SQL0964C: Transaction log full Long running transactions are taking up all active log space of the database Solution: force the oldest transaction (reported in db2diag.log) or increase the number of secondary log files SQL0968C: Log disk full DB2 cannot create new log files in the log directory because the file system is full Usually caused by problems with the archiving of log files check the db2uext2, connection to TSM etc.
15 February, 2012
55
DB2 Backup
15 February, 2012
56
DB2 Backup – Overview The DB2 backup and restore utilities are built as part of the DB2 engine Image(s) are logical format Not file based Provides more flexibility for restore, e.g. change disk layout on restore Amount of resources used under complete control of the caller The more resources allocated the faster the utilities will run
15 February, 2012
57
DB2 Backup – Overview (Cont’d) Backup Media available for DB2 Local devices, e.g. tape, disk, named pipe Tivoli Storage Server (TSM) XBSA compliant clients, e.g. Legato NetWorker Vendor supplied DB2 library, e.g. Veritas NetBackup DB2 Backup image contains table data as well as database configuration and history files
15 February, 2012
58
DB2 Backup – Command Syntax (excerpt) BACKUP DATABASE database-alias [USER username [USING password]] [TABLESPACE (tblspace-name [ {,tblspace-name} ... ])] [ONLINE] [INCREMENTAL [DELTA]] [USE {TSM | XBSA} [OPEN num-sess SESSIONS] [OPTIONS {optionsstring | options-filename}] | TO dir/dev [ {,dir/dev} ... ] | LOAD lib-name [OPEN num-sess SESSIONS] [OPTIONS {optionsstring | options-filename}]] [WITH num-buff BUFFERS] [BUFFER buffer-size] [PARALLELISM n] [COMPRESS [COMPRLIB lib-name [EXCLUDE]] [COMPROPTS optionsstring]] [UTIL_IMPACT_PRIORITY [priority]] [{INCLUDE | EXCLUDE} LOGS]
15 February, 2012
59
DB2 Backup Command – Examples Example command for an offline backup of database SI1 using Legato NetWorker Module on Solaris with 2 sessions: BACKUP DB SI1 LOAD /usr/lib/libnsrdb2.so OPEN 2 SESSIONS Example command for an online backup of database C11 to disk (directory c:\db2\backup): DB2 BACKUP DB C11 ONLINE TO c:\db2\backup Example command for an online backup of two table spaces of database QH2 to Tivoli Storage Manager (TSM): BACKUP DB QH2 TABLESPACE (PSAPPOOLD, PSAPPOOLI) ONLINE USE TSM COMPRESS
15 February, 2012
60
DB2 History File The DB2 history file contains a record of recovery and administrative events Recovery events include backup, restore rollforward and archive log operations Additional logged events include create, alter, drop, or rename table space, reorganize table, drop table, and load Use the LIST HISTORY command to query the history file, for example to see all backups for database QPT, issue: db2 list history backup all for QPT
15 February, 2012
61
LIST HISTORY BACKUP – Example Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ------------ -------------N O S0019095.LOG S0019100.LOG B D 20060110182712001 ---------------------------------------------------------------------------Contains 7 tablespace(s):
Log file sequence required for 00001 SYSCATSPACE Operation: Backup 00002 APPL1 rollforward of ONLINE backup 00003 APPL 00004 APPL2 00005 DFACT 00006 APPL0 00007 SYSTOOLSPACE ---------------------------------------------------------------------------Comment: DB2 BACKUP QPT ONLINE Start Time: 20060110182712 End Time: 20060111013737 Status: A ---------------------------------------------------------------------------EID: 32069 Location: /usr/lib/libnsrdb2.so Location: here Legato Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ------------ -------------B D 20060110182712002 N O S0019095.LOG S0019100.LOG ---------------------------------------------------------------------------Contains 7 tablespace(s): […] 15 February, 2012
62
DB2 Restore
15 February, 2012
63
DB2 Restore – Command Syntax (excerpt) RESTORE DATABASE source-database-alias { restore-options | CONTINUE | ABORT } restore-options: [USER username [USING password]] [ TABLESPACE (tblspace-name [ {,tblspace-name} ... ]) [ONLINE] | HISTORY FILE [ONLINE] | LOGS [ONLINE] | COMPRESSION LIBRARY [ONLINE]] [USE {TSM | XBSA} [OPEN num-sess SESSIONS] [OPTIONS {options-string | options-filename}] | FROM dir/dev [{,dir/dev} ... ] | LOAD shared-lib [OPEN num-sess SESSIONS] [OPTIONS {options-string | options-filename}]] [TAKEN AT date-time] [TO target-directory] [INTO target-database-alias] [LOGTARGET directory] [NEWLOGPATH directory] [WITH num-buff BUFFERS] [BUFFER buffer-size] [REPLACE HISTORY FILE] [REPLACE EXISTING] [REDIRECT] [PARALLELISM n] [COMPRLIB lib-name] [COMPROPTS options-string] [WITHOUT ROLLING FORWARD] [WITHOUT PROMPTING]
15 February, 2012
64
DB2 Restore Command – Example Example command for a database restore of database SI1 using Legato NetWorker Module on AIX: This command will replace the current SI1 database with the backup image taken at 05-05 2005 at 12:55:00 db2 "restore db si1 load /usr/lib/libnsrdb2.o open 2 sessions taken at 20050505125500 with 4 buffers buffer 2048 parallelism 2 replace existing without prompting"
Only if running older DB2 Version without self-tuning backup/ restore 15 February, 2012
65
Cross-platform Restoration Windows 32-bit
AIX 32-bit
Windows 64-bit
Big-endian Unix backup images are compatible
AIX 64-bit
Solaris 32-bit
Solaris 64-bit
HP-UX 32-bit
HP-UX 64-bit
Linux 32-bit
Linux 64-bit
Supported by SAP for Heterogeneous System Copies 15 February, 2012
66
Database Copy Using Backup/Restore & Pipes Source
Target Backup
Database
1. 2. 3. 4. 5. 6.
15 February, 2012
Restore
Database
Update .rhosts mkfifo mkfifo rsh "dd if= bs=8k" | dd of= db2 restore db from into db2 backup db to
67
DB2 Recovery
15 February, 2012
68
DB2 Roll Forward Versus Restart Recovery Two types of DB2 recovery: Restart recovery (aka crash recovery) Performed automatically during database restart (if DB parameter AUTORESTART is set to ON) DB2 reruns all transactions that were performed previously but have not been written to disk The DB2 log control file header contains the log sequence number, which must be used as the starting point of the recovery Restore & roll forward recovery Manually (using RESTORE and ROLLFORWARD commands – or RECOVER) 15 February, 2012
69
DB2 Rollforward – Command Syntax (excerpt) ROLLFORWARD DATABASE database-alias [USER username [USING password]] {[TO {isotime [USING LOCAL TIME] | END OF LOGS}] [AND COMPLETE]} | {COMPLETE | CANCEL | QUERY STATUS [USING LOCAL TIME]} [TABLESPACE (tblspace-name [ {,tblspace-name} ... ]) [ONLINE]] [OVERFLOW LOG PATH (log-directory)] [RECOVER DROPPED TABLE dropped-table-id TO export-directory]
15 February, 2012
70
DB2 Rollforward Command – Examples To query the roll forward status of database A3M enter: db2 rollforward db A3M query status To roll forward database B20 to end of logs (without using the db2uext2 to recover log files) and open the database afterwards, enter: db2 rollforward db B20 to end of logs and complete To roll forward database BUD to 01-01-2006, 14:05 local time, enter: db2 rollforward db BUD to 2006-01-0114.05.00.000000 using local time The database will remain in rollforward pending state – to open database issue db2 rollforward db BUD complete
15 February, 2012
71
Simplified Database Recovery – RECOVER The RECOVER DATABASE command combines the functionality of the RESTORE DATABASE and ROLLFORWARD DATABASE commands When you use this command, you specify the point-in-time to which you want the database recovered You do not need to indicate which database backup image must be restored or which log files are required to reach the specified point-in-time The RECOVER DATABASE command also supports recovery operations to the end of the log files
15 February, 2012
72
RECOVER DATABASE – Command Syntax (excerpt) RECOVER DATABASE database-alias [TO {isotime [USING LOCAL TIME | USING GMT TIME] | END OF LOGS} ] [USER username [USING password]] [USING HISTORY FILE (history-file)] [OVERFLOW LOG PATH (log-directory)] [COMPRLIB lib-name] [COMPROPTS options-string]
15 February, 2012
73
RECOVER DATABASE Command – Examples To use the latest backup image and rollforward to the end of logs using all default values: RECOVER DB XDM To recover the database to a point-in-time, issue the following command: RECOVER DB PLN TO 2005-08-31-04:00:00 The most recent image that can be used will be restored, and logs applied until the PIT is reached To recover a subset of partitions, issue: RECOVER DB J20 TO END OF LOGS ON DBPARTITIONNUMS(2 TO 4, 7, 9) A point-in-time RECOVER cannot be done in this case, the recover must be done to end-of-logs 15 February, 2012
74
Monitoring Backup/Restore Operations
15 February, 2012
75
LIST UTILITIES Command DB2 V8.2 introduces the LIST UTILITIES command It can be used to monitor DB2 utilities like: Backup Restore Crash recovery Load Rebalance The SHOW DETAIL option of the LIST UTILITY command provides information about the progress of the operation Alternatively you can use the db2pd command, for example: db2pd –uti[lities] [–repeat 10]
15 February, 2012
76
LIST UTILITIES Command – Example
15 February, 2012
77
DB2 9 – Backup And Restore Enhancements New table function to list all files in a database Used to automate support of split mirror backup/recovery Restartable Recovery Re-issuing RECOVER command will pick up where it left off Ability to change Point In Time in either direction Rebuild partial database Eliminate the need for FULL database restore Ability to rebuild the database from set of table space backup images Redirected Restore Script builder Build a redirected restore script from a backup image: ...REDIRECT GENERATE SCRIPT file-name...
15 February, 2012
78
Table/Index Maintenance
15 February, 2012
79
Data Maintenance Process – Overview RUNSTATS
REORGCHK
YES
REORG
Reorganization needed? NO
REBIND
RUNSTATS
FLUSH PACKAGE CACHE
Application Execution 15 February, 2012
80
Overview of the REORGCHK Utility REORGCHK is a data maintenance utility which uses the current database statistics to check if database objects need to be reorganized. Optionally it can also update database statistics. It generates a report with multiple REORG indicators (e.g. F1,F2,F3) for tables and indexes. Tables or indexes that require a REORG are marked with an asterisk. C:\>db2 reorgchk current statistics on table user Table statistics: F1: 100 * OVERFLOW / CARD < 5 F2: 100 * (Effective Space Utilization of Data Pages) > 70 F3: 100 * (Required Pages / Total Pages) > 80 SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG ---------------------------------------------------------------------------------------OLAF CL_SCHED - --OLAF CLASS_LIST - --OLAF DEPARTMENT 9 0 1 1 549 0 - 100 --OLAF EMP_ACT 75 0 1 1 2850 0 - 100 --OLAF EMP_PHOTO 12 0 1 1 2028 0 - 100 --OLAF EMP_RESUME 8 0 1 1 984 0 - 100 --OLAF EMPLOYEE 32 0 2 2 2784 0 68 100 -*-
15 February, 2012
81
REORGCHK Utility – Examples The following command checks table mytab based on the current table statistics: REORGCHK CURRENT STATISTICS ON TABLE mytab The following command updates the statistics and checks all tables created under the schema sapr3: REORGCHK UPDATE STATISTICS ON SCHEMA sapr3 The following command updates the statistics and checks all tables: REORGCHK UPDATE STATISTICS ON TABLE ALL
15 February, 2012
82
Overview of the RUNSTATS Utility The RUNSTATS utility is used to collect statistics about the characteristics of a table and the associated indexes Characteristics include number of records (cardinality), number of pages, average record length, and much more. This information is used by the DB2 cost based optimizer to choose the optimal access plan The decisions the optimizer makes are heavily influenced by statistical information about the size of the database tables and indexes Therefore it is crucial to keep the database statistics up to date so that an efficient data access plan can be chosen!
15 February, 2012
83
RUNSTATS Syntax (excerpt) RUNSTATS ON TABLE table-name [USE PROFILE | statistics-options] statistics-options: [table-object-options] [ALLOW {WRITE | READ} ACCESS] [table-sampling-options] [profile-options] [UTIL_IMPACT_PRIORITY [priority]] table-object-options: [{FOR index-clause | [column-stats-clause] [AND index-clause]}] table-sampling-options: [TABLESAMPLE {BERNOULLI | SYSTEM} (numeric-literal) [REPEATABLE (integer-literal)]] profile-options: [{SET PROFILE [NONE | ONLY] | UPDATE PROFILE [ONLY]}] [UTIL_IMPACT_PRIORITY [priority]]
15 February, 2012
84
RUNSTATS Utility – Examples The following command collects statistics on the table db2user.employee; Read and write access is allowed while the statistics are being calculated: RUNSTATS ON TABLE db2user.employee ALLOW WRITE ACCESS The following command collects statistics on table db2user.employee and detailed statistics on all its indexes: RUNSTATS ON TABLE db2user.employee AND DETAILED INDEXES ALL
15 February, 2012
85
Overview of the REORG Utility The REORG utility reorganizes tables and indexes and eliminates fragmented or empty pages. DB2 provides the option of performing the reorg online or offline You may also reorganize a table using an index: The order of RIDs in the index corresponds to the order of rows in the table after the reorg is completed. This helps to minimize I/O operations when accessing data using that index. Information about the table reorganization is available in the database history file in table snapshot output
15 February, 2012
86
Online Table Reorganization Enhances database availability by allowing reorganization of tables while permitting read and write (online) table access Reorg can be started, paused, and resumed. Table is reorganized in a piece-wise fashion: Vacate: Frees pages by moving data to the back of the table Fill: Fills the freed pages V A C A T E P A G E R A N G E : M O V E & C L E A N to m a k e s p a c e
T IM E
fre e space
F IL L P A G E R A N G E : M O V E & C L E A N to fill s p a c e
15 February, 2012
87
Index Reorganization REORG INDEXES supports two different functions: Rebuild: This is the default. Rebuilds the index data into unfragmented, physically contiguous pages. Cleanup only: Indexes will not be rebuild. Removes committed pseudo deleted keys and pseudo empty pages. Online index rebuild mechanism: Shadow index is created during normal transaction processing When index creation is complete, the old index is swapped out and the new index is made available Sufficient temporary space needs to be available to build the new index
15 February, 2012
88
REORG Utility – Examples Reorg of table ATAB in-place while allowing write access db2 connect to db2 reorg table sapr3.atab inplace allow write access To start, pause, and resume an in-place reorg of the ATAB table use the following commands: db2 reorg table sapr3.atab inplace start db2 reorg table sapr3.atab inplace pause db2 reorg table sapr3.atab inplace allow read access notruncate table resume Query database history file: db2 list history reorg all for database
15 February, 2012
89
Automating Database Maintenance DB2 allows the automation of certain database activities BACKUP database RUNSTATS on tables and indexes REORG of tables and indexes A Configure Automatic Maintenance wizard is available to specify Maintenance objectives Timeframe Automatic Runstats is used for all new (7.00 and higher) SAP systems Check SAP note # 860724 on how to enable older SAP releases for automatic runstats
15 February, 2012
90
Automating Database Maintenance Parameters Enablement is controlled using the hierarchical set of automatic maintenance database configuration parameters AUTO_MAINT (parent of all other parameters) AUTO_DB_BACKUP AUTO_TBL_MAINT (parent of all table maintenance parameters) AUTO_RUNSTATS AUTO_STATSPROF Turn on all three values in blue to enable automatic AUTO_PROFUPD runstats AUTO_REORG
15 February, 2012
91
Automated Statistics Collection Instead of the DBA triggering the statistic collection (deciding when to run RUNSTATS and which statistics to collect), there will be a background process which determines statistics that are out of date and collects them Checking if RUNSTATS is needed takes place every two hours There is no way for the DBA to change this at this time Monitoring: Messages in the db2diag.log file indicate start / termination (success/failure) of automatic runstats execution
15 February, 2012
92
Automatic Statistics Collection – db2diag.log For databases with automatic runstats enabled you will see messages like the following in the db2diag.log: 2006-08-01-17.06.20.137341+120 I26461795A322 LEVEL: Event PID : 23400 TID : 2369 PROC : db2hmon 0 INSTANCE: db2air NODE : 000 FUNCTION: DB2 UDB, Automatic Table Maintenance, db2HmonEvalStats, probe:100 START : Automatic Runstats: evaluation has started on database AIR 2006-08-01-17.18.31.757646+120 I26592803A377 LEVEL: Event PID : 23400 TID : 2682 PROC : db2hmon 0 INSTANCE: db2air NODE : 000 APPID : *LOCAL.db2air.060801151840 FUNCTION: DB2 UDB, Automatic Table Maintenance, AtmTable::runstats, probe:500 START : Automatic Runstats: runstats has started on table "SAPR3 "."DFKK_TRIGGER_PAY" 2006-08-01-17.18.33.704169+120 I26593181A388 LEVEL: Event PID : 23400 TID : 2682 PROC : db2hmon 0 INSTANCE: db2air NODE : 000 APPID : *LOCAL.db2air.060801151840 FUNCTION: DB2 UDB, Automatic Table Maintenance, AtmTable::runstats, probe:900 STOP : Automatic Runstats: runstats completed successfully on table "SAPR3 "."DFKK_TRIGGER_PAY" 15 February, 2012
93
Checking Database Statistics To check if database statistics are up to date you can query the STATS_TIME from the SYSCAT.TABLES system catalog view Example: db2 "select substr(tabname,1,20), stats_time from syscat.tables where tabname = ‘MYTAB‘" To check entries in db2diag.log the db2diag tool can be used. Example: db2diag –gi "function:=runstats,stop:=mytab"
15 February, 2012
94
DB2 Parameters
15 February, 2012
95
DB2 Environment Configure the DB2 environment via: DB2 Profile Registry Operating System Environment Variables DB2 Database Manager Configuration Parameters DB2 Database Configuration Parameters Server machine OS environment variables
Instance
Database manager configuration file
DBM CFG
Database DB2 registry New features, platform dependent parameters, special optimizations
15 February, 2012
Database configuration file
DB CFG
96
DB2 Profile Registry Variables Use the db2set command to update registry variables Parameters can be set globally or for a specific instance: To set a parameter for current instance: db2set parameter=value To set a parameter for a specific instance: db2set parameter=value -i instance_name To set a parameter at the global level: db2set parameter=value -g Changes take effect after the instance has been restarted.
15 February, 2012
97
Registry Variables for SAP Installations For SAP installations a special aggregate registry variable has been designed: db2set DB2_WORKLOAD=SAP To see the values that are included in DB2_WORKLOAD=SAP run db2set –gd DB2_WORKLOAD=SAP
15 February, 2012
98
Aggregate Registry Variables – Example V8.2 [i] [i] [i] [i] [i] [i] [i] [i] [i] [i] [i] [i] [i] [i] [i] [i] [i] [i] [i]
DB2_WORKLOAD=SAP DB2_MDC_ROLLOUT=YES [DB2_WORKLOAD] DB2_SKIPINSERTED=YES [DB2_WORKLOAD] DB2_VIEW_REOPT_VALUES=YES [DB2_WORKLOAD] DB2_OBJECT_TABLE_ENTRIES=65532 [DB2_WORKLOAD] DB2_OPTPROFILE=YES [DB2_WORKLOAD] These values have DB2_IMPLICIT_UNICODE=YES [DB2_WORKLOAD] been overwritten DB2_INLIST_TO_NLJN=YES [DB2_WORKLOAD] (signified by the [O]) DB2_MINIMIZE_LISTPREFETCH=YES [DB2_WORKLOAD] DB2_UPDATE_PART_KEY=YES [DB2_WORKLOAD] DB2_REDUCED_OPTIMIZATION=4,UNIQUEINDEX,JOIN [0] DB2NOTIFYVERBOSE=YES [DB2_WORKLOAD] DB2_INTERESTING_KEYS=YES [DB2_WORKLOAD] DB2_EVALUNCOMMITTED=YES_DEFERISCANFETCH [DB2_WORKLOAD] DB2_ANTIJOIN=EXTEND [DB2_WORKLOAD] DB2_SKIPDELETED=NO [0] DB2MEMMAXFREE=2000000 [DB2_WORKLOAD] DB2_RR_TO_RS=YES [DB2_WORKLOAD] DB2_FORCE_FCM_BP=YES [DB2_WORKLOAD]
15 February, 2012
99
DB2 Configuration Parameters Two groups of parameters: Database Manager Configuration parameters (DBM CFG) Database Configuration parameters (DB CFG) Types of parameters: Configurable (changes take effect after restart) Configurable Online (no restart required) Configurable Automatic (self tuning) Informational
15 February, 2012
100
How To Change The Parameters? Parameters can be viewed/modified using the following CLP commands: Database Manager Configuration commands: GET DATABASE MANAGER CONFIGURATION (or: GET DBM CFG) UPDATE DATABASE MANAGER CONFIGURATION (or: UPDATE DBM CFG) Database Configuration commands: GET DATABASE CONFIGURATION FOR (or: GET DB CFG FOR ) UPDATE DATABASE CONFIGURATION FOR (or: UPDATE DB CFG FOR ) 15 February, 2012
101
Database Manager Configuration – Sample Output Database Manager Configuration Node type = Enterprise Server Edition with local and remote clients ... Diagnostic error capture level (DIAGLEVEL) = 3 Notify Level (NOTIFYLEVEL) = 3 Diagnostic data directory path (DIAGPATH) = ... Default database monitor switches Buffer pool (DFT_MON_BUFPOOL) = ON Lock (DFT_MON_LOCK) = ON Sort (DFT_MON_SORT) = ON ... Database monitor heap size (4KB) (MON_HEAP_SZ) = 66 Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 512 Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0 Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC ... Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY Enable intra-partition parallelism (INTRA_PARALLEL) = YES ... No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC ...
15 February, 2012
102
Some Important DBM CFG Parameters
DIAGLEVEL DIAGPATH Monitor Switches NUM_POOLAGENTS SVCENAME INTRA_PARALLEL
15 February, 2012
(Diagnostic Error Capture Level) (Diagnostic Data Directory Path) (DFT_MON_SORT, …) (Size of the DB2 agent pool) (TCP/IP Service Name) (Enables Intra-Partition Parallelism)
103
Database Configuration – Sample Output Database Configuration for Database sample Database configuration release level Database release level ... Self tuning memory (SELF_TUNING_MEM) Size of database shared memory (4KB) (DATABASE_MEMORY) Max storage for lock list (4KB) (LOCKLIST) Percent. of lock lists per application (MAXLOCKS) Package cache size (4KB) (PCKCACHESZ) Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) Sort list heap (4KB) (SORTHEAP) ... Changed pages threshold (CHNGPGS_THRESH) Number of asynchronous page cleaners (NUM_IOCLEANERS) Number of I/O servers (NUM_IOSERVERS) Index sort flag (INDEXSORT) Sequential detect flag (SEQDETECT) Default prefetch size (pages) (DFT_PREFETCH_SZ) ...
15 February, 2012
= 0x0b00 = 0x0b00 = = = = = = =
ON AUTOMATIC 50 22 (MAXAPPLS*8) 5000 256
= = = = = =
60 AUTOMATIC AUTOMATIC YES YES AUTOMATIC
104
Some Important DB CFG Parameters
BUFFPAGE LOCKLIST SORTHEAP UTIL_HEAP_SZ NUM_IOCLEANERS NUM_IOSERVER LOGFILSIZ LOGPRIMARY LOGSECOND
15 February, 2012
(Buffer Pool Size) (Size limit for Lock List) (Size limit for one active sort operation) (Utilities Heap Size) (Number of Page Cleaners) (Number of I/O Servers - Prefetchers) (Log File Size) (Number of Primary Log Files) (Number of Secondary Log Files)
105
Configuration Commands Enhancements GET DBM CFG SHOW DETAIL Displays detailed information showing the current value of database manager configuration parameters as well as the value of the parameters the next time you start the instance GET DB CFG FOR SHOW DETAIL Displays detailed information showing the current value of database configuration parameters as well as the value of the parameters the next time you activate the database
15 February, 2012
106
GET DB CFG SHOW DETAIL – Example Output Database Configuration for Database Description Parameter Current Value Delayed Value ----------------------------------------------------------------------------------------------------------Database configuration release level Database release level Database territory Database code page Database code set Database country/region code Database collating sequence Alternate collating sequence Database page size Dynamic SQL Query management Discovery support for this database
= 0x0a00 = 0x0a00 = = = = = (ALT_COLLATE) = =
en_US 1208 UTF-8 1 IDENTITY_16BIT
IDENTITY_16BIT
4096
4096
(DYN_QUERY_MGMT) = DISABLE (DISCOVER_DB) = ENABLE
Default query optimization class (DFT_QUERYOPT) Degree of parallelism (DFT_DEGREE) Continue upon arithmetic exceptions (DFT_SQLMATHWARN) Default refresh age (DFT_REFRESH_AGE) Default maintained table types for opt (DFT_MTTB_TYPES) Number of frequent values retained (NUM_FREQVALUES) Number of quantiles retained (NUM_QUANTILES)
= = = = = = =
5 1 NO 0 SYSTEM 10 20
Backup pending
= NO
DISABLE ENABLE 5 1 NO 0 SYSTEM 10 20
[…]
15 February, 2012
107
DB2 Configuration Parameters & SAP As a starting point to tune your SAP DB2 database follow the SAP recommendations. SAP notes with DB2 standard parameter settings: 584952 – DB2 UDB ESE V8.1 and V8.2 899322 – DB2 9.1 Standard Parameter Settings 1086130 – DB2 9.5 Standard Parameter Settings 1329179 – DB2 9.7 Standard Parameter Settings
15 February, 2012
108
Problem Determination
15 February, 2012
109
Problem Source Identification Essential diagnostic information to identify the source of a problem: db2diag.log db2level DB2 configuration (instance, database, registry) Other possible information needed SAP/IBM support: db2nodes.cfg, trap/ dump files, stack files, core files, db2dart, DB2 Trace … Environment (set or env) Use the db2support tool to collect the data
15 February, 2012
110
First-failure Data Capture (FFDC) First-failure data capture (FFDC) information is the set of diagnostic information that DB2 captures automatically when errors occur. Configuration parameters: The DIAGPATH dbm parameter specifies the path to the FFDC storage directory The DIAGLEVEL and NOTIFYLEVEL parameters control the detail of information you receive in the log files. The information captured by FFDC includes the following: db2diag.log and administration notification log Dump files and trap files Core files (on UNIX)
15 February, 2012
111
DB2DIAG.LOG The first and most important source for problem analysis! Contains records about errors, warnings and informational records. Typical locations of DB2DIAG.LOG (values of DIAGPATH) Standard on UNIX: $HOME/sqllib/db2dump Standard SAP Installation on UNIX: /db2//db2dump Standard on Windows: \\
15 February, 2012
112
DB2DIAG.LOG – DIAGLEVEL DIAGLEVEL 0 No logging 1 Severe errors 2 Severe and non-severe errors 3 Severe and non-severe errors plus warnings 4 Severe and non-severe errors, warnings and Info Default is 3 and acceptable for most of the time Level 4 generates a lot of information - only useful in special situations (e.g. installation, migration)
15 February, 2012
113
DB2DIAG.LOG – Examples (1/3) Timestamp & time zone 2004-07-14-15.45.13.569918+120 I1A895 LEVEL: Event PID : 20940 TID : 1 PROC : db2agent (instance) 0 INSTANCE: db2xdm NODE : 000 DB : XDM APPHDL : 0-474 APPID: *LOCAL.db2xdm.040714163353 FUNCTION: DB2 UDB, RAS/PD component, _pdlogInt, probe:120 Severity level START : New db2diag.log file DATA #1 : Build Level, 144 bytes Instance "db2xdm" uses "64" bits and DB2 code release "SQL08020" with level identifier "03010106". Informational tokens are "DB2 v8.1.0.64", "s040704", "U498351", FixPak "7". DATA #2 : System Info, 1328 bytes System: SunOS us0014 5.8 Generic_108528-11 sun4u DB2 level information CPU: total:4 online:4 for started instance Physical Memory: total:2048 free:644 Virtual Memory: total:10229 free:7466 Swap Memory: total:8181 free:6822 Information in this record is only valid at the time when this file was created (see this record's time stamp).
Detailed Information: OS level, DB2 level, available memory, number of CPUs 15 February, 2012
114
DB2DIAG.LOG – Examples (2/3) 2004-07-14-16.16.46.431884+120 I53029A379 LEVEL: Event PID : 23827 TID : 1 PROC : db2agent (instance) 0 INSTANCE: db2xdm NODE : 000 APPHDL : 0-8 APPID: *LOCAL.db2xdm.040714141645 FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:20 CHANGE : CFG DB XDM: "Util_heap_sz" From: "5000" To: "131072"
Changes to configuration files documented in db2diag.log 2004-07-14-19.02.54.893205+120 E123020A589 LEVEL: Error PID : 7207 TID : 1 PROC : db2agent (XDM) 0 INSTANCE: db2xdm NODE : 000 DB : XDM APPHDL : 0-9 APPID: *LOCAL.db2xdm.040714165506 FUNCTION: DB2 UDB, buffer pool services, sqlbAllocateExtent, probe:830 MESSAGE : ADM6044E The DMS table space "PSAPLOADI" (ID "20") is full. Additional space can be added to the table space by either adding new containers or extending existing ones using the ALTER TABLESPACE SQL statement.
Severity level Error 15 February, 2012
115
DB2DIAG.LOG – Examples (3/3) OS error Called OS function
Unique record ID
2006-04-21-15.26.11.839221+120 E6835E555 LEVEL: Error (OS) PID : 6259 TID : 182968115104PROC : db2med.6158.0 0 INSTANCE: db2a3m NODE : 000 FUNCTION: DB2 UDB, oper system services, sqloLoadModule, probe:190 CALLED : OS, -, dlopen OSERR: EINTR (4) DATA #1 : Library name or path, 21 bytes /usr/lib/libnsrdb2.so DATA #2 : shared library load flags, PD_TYPE_LOAD_FLAGS, 4 bytes Formatted 0 error code DATA #3 : String, 80 bytes /usr/lib/libnsrdb2.so: cannot open shared object file: No such file or directory
15 February, 2012
OS
116
Administration Notification Log In addition to the db2diag.log an administration notification log (aka notify log) is available: Focus on administrative events (db2diag.log is intended for problem determination). Intended for use by database and system administrators. Notification messages also provide additional information to supplement the SQLCODE. The notification log is available on UNIX & Linux. On Windows it is part of the Windows Event Log The type of event and the level of detail of the information gathered are determined by the NOTIFYLEVEL (DBM parameter) Location defined by DIAGPATH configuration parameter File name is .nfy (for example db2sxi.nfy) Log messages are NLS enabled (i.e. printed in the locale of the database) while the db2diag.log is always in English
15 February, 2012
117
DB2 Notify Log – Examples With Errors 2005-03-24-11.34.29.242569 Instance:db2sxi Node:000 PID:89198(db2agent (SXI )) Appid:*LOCAL.db2inst1.020205091435 recovery manager sqlpresr Probe:370 Database:SXI ADM1531E Crash recovery has completed successfully. 2005-06-08-11.46.52.310415 Instance:db2sxi Node:000 PID:23941(db2wdog 0 0) TID:332701696 Appid:none oper system services sqloRunInstance Probe:490 ADM0503C An unexpected internal processing error has occurred. ALL DB2 PROCESSES ASSOCIATED WITH THIS INSTANCE HAVE BEEN SHUTDOWN. Diagnostic information has been recorded. Contact IBM Support for further assistance.
15 February, 2012
118
Diagnostic Information for IBM customer support In addition to the db2diag.log (and the administration notification) you may find the following files in the DIAGPATH, for example: Trap files: Function call stack, signals, exceptions. Core files: Memory image of terminated processes. Dump files: Additional information (control blocks etc) DB2 event log: Circular log of infrastructure-level events occurring in the database manager. …
15 February, 2012
119
A Typical DB2 DIAGPATH -rw-r--r--rw-rw-rw-rw-r-----rw-r----drwxr-xr-x -rw-r--r--rw-r--r--rw-r--r--rw-r--r--rw-r--r--rw-r--r--rw-r--r--rw-r--r-drwxr-x---rw-r--r-drw-rw-rw-rw-r--r--rw-rw-rw-
15 February, 2012
1 1 1 1 2 1 1 1 1 1 1 1 1 2 1 1 1 1
db2qpt db2qpt db2qpt db2qpt db2qpt db2qpt db2qpt db2qpt db2qpt db2qpt db2qpt db2qpt db2qpt root root db2qpt db2qpt db2qpt
sysadm sysadm sysadm sysadm sysadm sysadm sysadm sysadm sysadm sysadm sysadm sysadm sysadm sysadm sysadm sysadm sysadm sysadm
747988 May 6 4124675 Jun 16 6291376 Jul 18 6291376 Feb 12 4096 Feb 12 2731 Feb 12 2731 Feb 12 2731 Feb 12 2731 Feb 12 2729 Feb 12 2730 Feb 12 2730 Feb 12 2730 Feb 12 4096 Mar 1 14699 Mar 1 19514552 May 2 9262825 May 2 305440156 May
Event log
2005 xbsa.log 2005 db2diag.log_2005-06-16-10.59.37 2005 db2eventlog.000.crash db2dart files 15:57 db2eventlog.000 16:58 DART0000 17:04 t22378.000 17:19 t24338.000 17:34 t26239.000 Trap files 17:49 t28312.000 18:04 t228.000 18:19 t2447.000 18:34 t4466.000 Core file 18:49 t6428.000 19:37 c16591.000 19:37 t16591.000 13:27 db2qpt.nfy 15:29 UserExit.log 2 15:29 db2diag.log
Notify log
120
Collect System Diagnostic Information – db2support The db2support utility is designed to automatically collect all DB2 and system diagnostic information available The utility will collect for example: The db2diag.log, all trap files, lock list files, dump files, buffer pool and table space control files, database and database manager configuration, system information … Collecting most of the information required to debug a problem: db2support 
