1 / 39

Unix DB2 Navigation for an Informix DBA

Informix User Forum 2005 Moving Forward With Informix. Unix DB2 Navigation for an Informix DBA. Kate Tomchik Lead DBA / The Home Depot. Atlanta, Georgia December 8-9, 2005. DB2 Navigation for an Informix DBA.

vic
Download Presentation

Unix DB2 Navigation for an Informix DBA

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Informix User Forum 2005Moving Forward With Informix Unix DB2 Navigation for an Informix DBA Kate Tomchik Lead DBA / The Home Depot Atlanta, Georgia December 8-9, 2005

  2. DB2 Navigation for an Informix DBA Informix Users Cheat Sheet for Unix DB2 Support

  3. 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.

  4. DB2 Instances running > db2ilist #-- not always set up cpainp95:/opt/hd/db/db2/instance/adfi11> db2ilist adis32 adis33 adfi11 > Ps -ef | grep db2sysc | grep -v grep cpainp95: /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

  5. 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

  6. 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> db2start 08-16-2005 13:28:17 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful.

  7. 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

  8. Stop an Instance > onmode -ky # --- in informix > db2stop # --- run as userid of instance cpaiad28: /opt/hd/db/db2/instance/remm60 > db2stop 08-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

  9. 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}

  10. 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

  11. 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

  12. 3rd Rule – Check the log files >{db2path} / db2dump / db2diag.log cpainp95:/opt/hd/db/db2/instance/adfi11/sqllib/db2dump> ls -l total 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 activity adfi11.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.

  13. Adis32.nfy (v8 file) ADM7514W Database manager has stopped. ^^ 2004-04-02-18.57.12.883227 Instance:adis32 Node:000 PID:573572(db2star2) TID:1 Appid:none base sys utilities startdbm Probe:911 ADM7513W Database manager has started. ^^ 2005-11-02-13.12.15.570194 Instance:adis32 Node:000 PID:2457760(db2agent (CQMASTR2) 0) TID:1 Appid:*LOCAL.adis32.0800F2181219 database 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 this problem, increase the MON_HEAP_SZ DBM configuration parameter and restart the instance.

  14. Db2diag.log ADM7513W Database manager has started. 2005-08-02-15.01.16.014367 Instance:adis32 Node:000 PID:2318574(db2agent (DAD033IS) 0) TID:1 Appid:*LOCAL.adis32.0003D2190114 database utilities sqlubcka Probe:0 Database:DAD033IS Starting a full database backup. 2005-08-02-15.01.53.519377 Instance:adis32 Node:000 PID:2318574(db2agent (DAD033IS) 0) TID:1 Appid:*LOCAL.adis32.0003D2190114 database utilities sqlubcka Probe:128 Database:DAD033IS Estimated size of backup in bytes: 0x0FFFFFFFFFFF5008 : 0x0000000008489000 .....H.. 2005-08-02-15.01.53.524680 Instance:adis32 Node:000 PID:2318574(db2agent (DAD033IS) 0) TID:1 Appid:*LOCAL.adis32.0003D2190114 database utilities sqlubcka Probe:128 Database:DAD033IS Actual size of backup in bytes: 0x0FFFFFFFFFFF5010 : 0x0000000008405000 .....@P. 2005-08-02-15.01.53.529787 Instance:adis32 Node:000 PID:2318574(db2agent (DAD033IS) 0) TID:1 Appid:*LOCAL.adis32.0003D2190114 database utilities sqlubcka Probe:130 Database:DAD033IS Backup Complete.

  15. When all else fails - Call Support IBM support calls create PMRs {Problem Management Record} You will want already have the following: • Hardware type and operating system version >uname –a #ex AIX 5.1 • DB2 version • Configuration file, environment variables • Ability to send the log files via ftp to IBM.

  16. Version of DB2 > su - adfi11 # enter password > db2level cpainp95:/opt/hd/db/db2/instance/adfi11> db2level DB21085I 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".

  17. DB2 Environment Variables cpainp95:> env | grep DB2 DB2INSTANCE=adfi11 cpainp95:> db2set -all [i] DB2COMM=tcpip [i] DB2AUTOSTART=YES [g] DB2_EEE_LICENSE_POLICY=1125904201875456 [g] DB2ADMINSERVER=db2as

  18. View the Configuration File Informix >view $INFORMIXDIR/etc/onconfig >view $INFORMIXDIR/etc/sqlhosts DB2 > 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}

  19. Current path similar to >echo $INFORMIXDIR but each instance has a different location > db2path cpainp95:/opt/hd/db/db2/instance/adfi11> db2path /opt/hd/db/db2/instance/adfi11/sqllib

  20. 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.

  21. 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

  22. Database Connection State > db2 "get connection state" Database Connection State Connection state = Connectable and Unconnected Connection mode = Local database alias = Database name =

  23. 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

  24. 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}

  25. Some parameters in the DBM Database Manager Configuration Node type = Enterprise Server Edition with local and remote clients Diagnostic error capture level (DIAGLEVEL) = 3 Notify Level (NOTIFYLEVEL) = 2 Diagnostic data directory path (DIAGPATH) = /opt/hd/db/db2/instance/adis32/sqllib/db2dump Default database path (DFTDBPATH) = /opt/hd/db/db2/instance/adis32 Max number of existing agents (MAXAGENTS) = 400 TCP/IP Service name (SVCENAME) = adis32 No. of int. communication buffers(4KB) (FCM_NUM_BUFFERS) = 4096

  26. Make a Backup Db2 “backup db {database} {online} {incremental} use {adsm} Multipartition: Db2_all “<<+0<db2 backup db {database} {online} {incremental} use {adsm} Example Db2 “ backup db dpr060mm online use adsm”

  27. Check quality of the backup Checksum: Db2adutl verify full taken at {datetime} db {database} Example: Db2adutl verify full taken at 20040512171343 db dpr010sa

  28. 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

  29. Restore Database command Db2 “restore database {database} use {tsm} taken at {date/time} NOTE: This is the start time from the history output.

  30. Example Restore Get last good backup date/time: >db2 list history backup all for dpr010sa Check attached users: >db2 list applications Force users off: • db2 force applications all Run restore as nohup: >nohup db2 “restore database dpr010sa use tsm taken 20040512171343” Verify completed: >db2 “connect to dpr010sa”

  31. 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

  32. 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

  33. Some Important System Tables Prefix with owner/schema syscat Tables Indexes Dbauth Tabauth Dependancies Tablespaces tbspaceauth

  34. 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

  35. 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

  36. 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.

  37. 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.

  38. 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

  39. Informix User Forum 2005Moving Forward With Informix Unix DB2 Navigation for an Informix DBA Kate Tomchik Kate_Tomchik@HomeDepot.com kate@iiug.org Atlanta, Georgia December 8-9, 2005

More Related