unix db2 navigation for an informix dba

Unix DB2 Navigation for an Informix DBA

Kate TomchikKate TomchikLead DBA / The Home DepotLead DBA / The Home Depot

Informix User Forum 2005 Moving Forward With Informix

Atlanta, Georgia December 8-9, 2005

DB2 Navigation for an Informix DBA

Informix Users Cheat Sheetfor Unix DB2 Support

First rule of DBA support

When user calls with a problem verify the database is up.

If not restart it.

If it is, get permission to bounce it.

DB2 Instances running

> db2ilist #-- not always set upcpainp95:/opt/hd/db/db2/instance/adfi11> db2ilistadis32adis33adfi11

> Ps -ef | grep db2sysc | grep -v grepcpainp95: /home/lkt01> ps -ef | grep db2sysc | grep -v grep adfi11 876768 917730 0 Jul 27 - 0:09 db2sysc 0 adis33 1056790 1392730 0 Jul 27 - 0:09 db2sysc 0 adis32 1933360 2170886 0 Aug 12 - 0:01 db2sysc 0

Typical DB2 processes cpainp95: /home/lkt01> ps -fu adfi11 | grep db2 adfi11 647364 876768 0 Jul 27 - 0:36 db2gds 0 adfi11 786632 876768 0 Jul 27 - 0:04 db2ipccm 0 adfi11 811222 647364 0 Aug 13 - 0:04 db2dlock (DAD014FI) 0 adfi11 876768 917730 0 Jul 27 - 0:09 db2sysc 0 adfi11 901346 876768 0 Jul 27 - 0:00 db2syslog 0 adfi11 934120 647364 0 Jul 27 - 0:00 db2srvlst 0 adfi11 983280 876768 0 Jul 27 - 0:00 db2tcpcm 0 adfi11 999672 876768 0 Jul 27 - 0:00 db2resync 0 adfi11 1016056 876768 0 Jul 27 - 0:00 db2spmrsy 0 adfi11 1040638 647364 0 Jul 27 - 0:00 db2spmlw 0 adfi11 1261798 647364 0 Aug 13 - 0:06 db2loggw (DAD014FI) 0 adfi11 1327176 786632 0 Jul 27 - 2:42 db2agent (instance) 0 adfi11 1491052 647364 0 Jul 27 - 0:02 db2cart 0 adfi11 1556610 647364 0 Jul 27 - 1:19 db2agent (DAD014FI) 0 adfi11 1564804 647364 0 Jul 27 - 0:03 db2agent (idle) 0 adfi11 1753190 647364 0 Aug 13 - 0:00 db2pfchr 0 adfi11 1884196 647364 0 Aug 13 - 0:00 db2event

(DB2DETAILDEADLOCK) 0 adfi11 2015258 647364 0 Aug 13 - 0:00 db2pfchr 0 adfi11 2023660 647364 0 Aug 13 - 0:00 db2loggr (DAD014FI) 0 adfi11 2433208 647364 0 Aug 13 - 0:00 db2pclnr 0

Start an Instance

>oninit # --- in Informix> db2start #--- run as id of instance

cpaiad28: /opt/hd/db/db2/> su – remm60 cpaiad28: /opt/hd/db/db2/instance/remm60> db2start08-16-2005 13:28:17 0 0 SQL1063N DB2START

processing was successful.SQL1063N DB2START processing was successful.

Start an Instance (con’t)

cpaiad28: /opt/hd/db/db2/instance/remm60> ps -fu remm60 UID PID PPID C STIME TTY TIME CMD remm60 1335306 7127088 0 13:28:16 - 0:00 db2ipccm 0 remm60 1687694 7192802 0 13:18:55 pts/0 0:00 -ksh remm60 1695826 4866232 0 13:28:17 - 0:00 db2srvlst 0 remm60 2252828 7127088 0 13:28:17 - 0:00 db2spmrm 0 remm60 3645576 4866232 0 13:28:17 - 0:00 db2resyn 0 remm60 4530428 1687694 1 13:28:24 pts/0 0:00 ps -fu remm60 remm60 4603946 7127088 0 13:28:16 - 0:00 db2tcpcm 0 remm60 4628634 7127088 0 13:28:16 - 0:00 db2tcpcm 0 remm60 4866232 7127088 0 13:28:16 - 0:00 db2gds 0 remm60 6955014 4866232 0 13:28:17 - 0:00 db2spmlw 0 remm60 7127088 4997122 0 13:28:16 - 0:00 db2sysc 0

Stop an Instance

> onmode -ky # --- in informix

> db2stop # --- run as userid of instancecpaiad28: /opt/hd/db/db2/instance/remm60> db2stop08-16-2005 13:22:17 0 0 SQL1064N DB2STOP processing was

successful.SQL1064N DB2STOP processing was successful.> ps -fu remm60 UID PID PPID C STIME TTY TIME CMD remm60 1687694 7192802 0 13:18:55 pts/0 0:00 -ksh remm60 4538434 1687694 1 13:27:02 pts/0 0:00 ps –fu remm60

Hard Kill an Instance (bad way)

Informix:> ps -ef | grep oninit # --- find #1

owned process> kill -9 {PID}DB2:> ps -ef | grep db2sysc {0} # --- 0

process for multiprocessor engine> kill -9 {PID}

Db2_kill (less corruption risk)

> db2_kill # db2 utility

cpaiad28: /opt/hd/db/db2/instance/remm60> db2_kill

cpaiad28: ipclean: Removing DB2 engine and client's IPC resources for remm60.

cpaiad28: db2nkill [] completed ok

2nd Rule of DBA Support

If one user is stopping a 100 users from getting to their tables,

Kill the Offending Process.

>db2 list applications show detail# identify bad process and get the Appl.

Handle and Coordinator PID/Thread.

db2 force appliction {AH}# similar to onmode –z {PID} in Informix

3rd Rule – Check the log files

>{db2path} / db2dump / db2diag.logcpainp95:/opt/hd/db/db2/instance/adfi11/sqllib/db2dump> ls -ltotal 10352-rw-rw-rw- 1 adfi11 sysadm 4821 Jul 27 07:46 adfi11.nfy-rw-rw-rw- 1 adfi11 sysadm 46803 Jul 27 12:04 db2diag.log-rw-r----- 1 adfi11 sysadm 5242044 Jul 27 07:36

db2eventlog.000.crash

db2diag.log – main log of database activityadfi11.nfy file contains notifications.db2eventlog.000 files are similar to /tmp/af files since they are

created during database errors but they are not readable. Send to IBM when opening a PMR.

Adis32.nfy (v8 file)ADM7514W Database manager has stopped.^^2004-04-02-18.57.12.883227 Instance:adis32 Node:000PID:573572(db2star2) TID:1 Appid:nonebase sys utilities startdbm Probe:911

ADM7513W Database manager has started.^^2005-11-02-13.12.15.570194 Instance:adis32 Node:000PID:2457760(db2agent (CQMASTR2) 0) TID:1

Appid:*LOCAL.adis32.0800F2181219database monitor sqm.evmgr::log_ev_err Probe:2 Database:DAD036IS

ADM2004E The Event Monitor "DB2DETAILDEADLOCK" was not activated because there

was not enough storage space in the database monitor heap. To remedy thisproblem, increase the MON_HEAP_SZ DBM configuration parameter and restart

theinstance.

Db2diag.logADM7513W Database manager has started.

2005-08-02-15.01.16.014367 Instance:adis32 Node:000PID:2318574(db2agent (DAD033IS) 0) TID:1 Appid:*LOCAL.adis32.0003D2190114database utilities sqlubcka Probe:0 Database:DAD033IS

Starting a full database backup.


Estimated size of backup in bytes:

0x0FFFFFFFFFFF5008 : 0x0000000008489000 .....H..


Actual size of backup in bytes:

0x0FFFFFFFFFFF5010 : 0x0000000008405000 .....@P.


Backup Complete.

When all else fails - Call Support

IBM support calls create PMRs {Problem Management Record}

You will want already have the following:1) Hardware type and operating system

version >uname –a #ex AIX 5.1 2) DB2 version3) Configuration file, environment variables4) Ability to send the log files via ftp to IBM.

Version of DB2

> su - adfi11 # enter password> db2levelcpainp95:/opt/hd/db/db2/instance/adfi11> db2levelDB21085I Instance "adfi11" uses "32" bits and DB2 code

release SQL08013" with level identifier "02040106".Informational tokens are "DB2 v8.1.1.24", "s030728",

"U488481", and FixPak "3".Product is installed at "/usr/opt/db2_08_01".

DB2 Environment Variables

cpainp95:> env | grep DB2DB2INSTANCE=adfi11

cpainp95:> db2set -all[i] DB2COMM=tcpip[i] DB2AUTOSTART=YES[g]

DB2_EEE_LICENSE_POLICY=1125904201875456

[g] DB2ADMINSERVER=db2as

View the Configuration File

Informix>view $INFORMIXDIR/etc/onconfig>view $INFORMIXDIR/etc/sqlhostsDB2> db2 get dbm cfg | more#--- you scroll through the file. Updates cannot be

made directly to the file, you use a utility

> db2 update dbm cfg using {parameter} {value}

Current path

similar to >echo $INFORMIXDIR but each instance has a different

location> db2pathcpainp95:/opt/hd/db/db2/instance/adfi11> db2path/opt/hd/db/db2/instance/adfi11/sqllib

Advanced Beginning Functions

Recommendation: Take a class if you really need to work in UDB DB2.

Warning: The beginning class shows everything through a GUI interface.

The following shows the basics using command line functions.

Databases for an Instance

Must run as the instance owner:

> db2 "list db directory"

System Database Directory

Number of entries in the directory = 1

Database 1 entry:

Database alias = DPR060TA Database name = DPR060TA Local database directory = /opt/hd/db/db2/dbdir/dpr060ta Database release level = 9.00 Comment = Directory entry type = Indirect Catalog node number = 0

Database Connection State

> db2 "get connection state"

Database Connection State

Connection state = Connectable and Unconnected Connection mode = Local database alias = Database name =

Connect to a Database

Informix > dbaccess {database} {command.sql}

Gives error if the instance has not been started

> db2 connect to dpr060ta Database Connection Information Database server = DB2/6000 7.2.6 SQL authorization ID = REMM60 Local database alias = DPR060TA

Instance creation

You must have root access to create You must have user ids created for each

instance, where as in Informix you could always just use Informix

You may also create a fenced user id for the instance. This ID does not have system administration (sysadm) privileges

> db2icrt -u {instance fenced id} {instance}

Some parameters in the DBM

Database Manager Configuration

Node type = Enterprise Server Edition with local and remote clients

Diagnostic error capture level (DIAGLEVEL) = 3Notify Level (NOTIFYLEVEL) = 2Diagnostic data directory path (DIAGPATH) =

/opt/hd/db/db2/instance/adis32/sqllib/db2dumpDefault database path (DFTDBPATH) =

/opt/hd/db/db2/instance/adis32Max number of existing agents (MAXAGENTS) = 400 TCP/IP Service name (SVCENAME) = adis32 No. of int. communication buffers(4KB) (FCM_NUM_BUFFERS) = 4096

Make a Backup

Db2 “backup db {database} {online} {incremental} use {adsm}

Multipartition:Db2_all “<<+0<db2 backup db {database}

{online} {incremental} use {adsm}

ExampleDb2 “ backup db dpr060mm online use

adsm”

Check quality of the backup

Checksum:Db2adutl verify full taken at

{datetime} db {database}

Example:Db2adutl verify full taken at

20040512171343 db dpr010sa

Get list of past backups

> db2 list history backup all for {database}

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ------------ -------------- B D 20050927020010001 N A S0000509.LOG S0000510.LOG ---------------------------------------------------------------------------- Contains 7 tablespace(s):

00001 SYSCATSPACE 00002 WCSDATA4K 00003 WCSINDEX4K 00004 WCSDATA8K 00005 WCSINDEX8K 00006 WCSDATA16K 00007 WCSINDEX16K ---------------------------------------------------------------------------- Comment: DB2 BACKUP DPR010SA ONLINE Start Time: 20050927020010 End Time: 20050927020043 ---------------------------------------------------------------------------- 00013 Location: adsm/libtsm.a

Restore Database command

Db2 “restore database {database} use {tsm} taken at {date/time}

NOTE: This is the start time from the history output.

Example Restore

Get last good backup date/time:>db2 list history backup all for dpr010saCheck attached users:>db2 list applicationsForce users off: db2 force applications allRun restore as nohup:>nohup db2 “restore database dpr010sa use tsm

taken 20040512171343”Verify completed:>db2 “connect to dpr010sa”

Run SQL Against a table

db2 connect to {database} Db2 “select * from {table-name}

Example:>db2 “select substr(tabname,1,18) as

table_name from syscat.tables where type=‘T’ order by 1”

# gets system tables

External SQL file for run

Create a file that contains each SQL command separated by a “;”

cpaita28: /usr/ra/bin> cat count.sql> select count(*) from syscat.tables where type='T';> select count(*) from syscat.indexes;

Run an sql file:>db2 –tvf {sql file} –z {output file}

Example:>db2 –tvf count.sql –z count.out

Some Important System Tables

Prefix with owner/schema syscat

TablesIndexesDbauthTabauthDependanciesTablespacestbspaceauth

Data space issues

db2 “list tablespaces”

Tablespaces for Current Database

Tablespace ID = 0 Name = SYSCATSPACE Type = System managed space Contents = Any data State = 0x0000 Detailed explanation: Normal

Tablespace ID = 1 Name = TEMPSPACE1 Type = System managed space Contents = Temporary data State = 0x0000 Detailed explanation: Normal

Tablespace ID = 2 Name = USERSPACE1 Type = System managed space Contents = Any data State = 0x0000 Detailed explanation: Normal

Data space issues

User created tablespace:

Tablespace ID = 3 Name = PR001TAAQB Type = Database managed space Contents = Any data State = 0x0000 Detailed explanation: Normal

System managed space: automatic expansions up to the max size of the file system.

Database managed space: dba allocates additional space

Data space – Detailed information

> db2 “list tablespaces show detail” | more Tablespace ID = 3 Name = PR001TAAQB Type = Database managed space Contents = Any data State = 0x0000 Detailed explanation: Normal Total pages = 100000 Useable pages = 99968 Used pages = 99968 Free pages = 0 High water mark (pages) = 99968 Page size (bytes) = 4096 Extent size (pages) = 32 Prefetch size (pages) = 32 Number of containers = 1

##Includes used pages information and page size for additional allocations.

Add disk space

Expand the containers for a tablespace.

>alter tablespace {tbspacename} extend (all containers {expand amount});

#Every container is expanded the same amount and data is blanced across all containers. Expand amount is in pages. Can take many minutes.

Get Stored Procedure SQL

Db2 “export to {filename} of del select text from syscat.procedures where procname=‘{ProcName}’”

Example>db2 “export to filename.sql of del select

text from syscat.procedures where procname=‘ADJUSTINV’”

# creates procedure code in filename.sql

Unix DB2 Navigation for an Informix DBA

Kate TomchikKate [email protected]

[email protected]@iiug.org

Informix User Forum 2005 Moving Forward With Informix

Atlanta, Georgia December 8-9, 2005

mailto:[email protected]

unix db2 navigation for an informix dba

Documents

grep adfi11

ps fu remm60 remm60

ps fu remm60

ksh remm60

opthddbdb2 su remm60

informix db2start

informix db2stop

db2agent instance