Advanced Technical Skills ATS North America Storage Technical

Advanced Technical Skills (ATS) North America Storage Technical Exchange Tivoli Storage Manager DB 2 for TSM Administrators - Update Dave Canan IBM Advanced Technical Skills ddcanan@us. ibm. com © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America TSM Advanced Technical Support Team § Dave Canan – ddcanan@us. ibm. com § Rich Crespo – racrespo@us. ibm. com § Dave Daun – djdaun@us. ibm. com § Tom Hepner – hep@us. ibm. com 2 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Topics § Objectives for this presentation § DB 2 Monitoring and Diagnostic Utilities § TSM Server Instrumentation – DB 2 related information § TSM V 6 Reorgs Revisited 3 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Objectives for this Presentation DB 2 for TSM Admins - Update © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Objectives for this Presentation § This presentation was created for TSM admins who have not attended any DB 2 classes. § It was built on top of the previous TSM Session given on TSM for TSM Admins (given in December 2010) – This presentation assumes you know how to invoke the db 2 command line to issue basic DB 2 commands. § The goal here is not to make you a DB 2 expert; the goal is to teach you about some basics about some common utilities you need to know in order to monitor and diagnose TSM Server issues. § TSM support recommends that you NOT change the TSM DB without their advice. § We still have the recommendation that you keep up to date on your TSM V 6 software levels. As that time of this presentation, the recommended level was V 6. 3. 4. 200 5 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America DB 2 Monitoring and Diagnostic Utilities DB 2 for TSM Admins - Update © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America DB 2 Monitoring, Diagnostic Utilities, and Logs § runstats § db 2 diag. log § db 2 pd § db 2 dart § db 2 top § db 2 support § db 2 mtrk § For each utility, I will provide some ways you may want to use it and some recommendations. 7 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America runstats DB 2 for TSM Admins - Update © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America DB 2 runstats - Introduction § DB 2 maintains statistics for tables and indices so that it knows how to optimally execute a given SQL statement § Those statistics are obtained via a DB 2 process called runstats – It scans the table determining it’s size and cardinality of the various columns § TSM or DB 2 automatically runstats periodically on tables that have changed a lot – You shouldn’t have to manually runstats in order to maintain this information, but you can examine when it was last run for a given table/index 9 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America DB 2 Table Scans § When the proper indices don’t exist to optimally read data from a table, then a table scan is performed by DB 2. – Optimally, DB 2 should be avoiding this instead be pulling data from buffers in memory § This can happen for several reasons – Runstats has not been run on that table – The runstats data is obsolete • Data can get obsolete quick, especially on an empty table that is filling fast – Note: newly installed servers can quickly get obsolete runstats data. – Maybe there needs to be an index created - a proper index to service that query simply does not exist. (Open a PMR) 10 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America DB 2 Table Scans § Symptoms of a table scan: – DB 2 is consuming a CPU (or two) and TSM barely registers • i. e. DB 2 is consuming 25% of CPU on a 4 core box and TSM is maybe consuming 2% – You have individual queries taking many minutes/hours – DB 2 statement snapshots show no index activity § What do I do if I think table scans are happening? – A manual runstats on that table can be run (with the direction of TSM support) • You have to know the table name, may need a trace – DBSTMT trace would be used for this analysis • Call support and open a PMR. 11 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America DB 2 runstats – How to Determine if it is Running § Look for message ANR 0136 I - this actlog message means that runstats has just been run on a table: – ANR 0136 I Table updating statistics performed successfully for 1 of 1. § Issue the show runstats command. It shows when the last TSM initiated runstats was run – Stats only tracked since server start § Issue the following command to db 2: d 2 "select tabname, stats_time from syscat. tables where tabschema=‘TSMDB 1’ and type=‘T’“ – Shows when DB 2 believes runstats was last run 12 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America DB 2 runstats – How to Issue Manually § Runstats may be run manually via the TSM runstats command. Do this if support requests that it be run. Examples of command: runstats BF. Aggregated. Bitfiles runstats all • Do not use all unless directed to by TSM support!! – Table names are case sensitive • See show runstats output for proper case § Runstats may also be run via DB 2 commands. Example: db 2 runstats on table tsmdb 1. bf_aggregated_bitfiles and indexes allow write access 13 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America DB 2 runstats – Checking the History of Executions § DB 2 auto runstats doesn’t always succeed – Due to failure to obtain locks, etc. – Runs as low priority process – To see the runstats history collect the db 2 optstats. * files in ~/sqllib/db 2 dump/events 14 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America DB 2 runstats – Looking for Errors (db 2 diag. log) § An example of failure: 2010 -12 -10 -16. 59. 47. 598047 -420 E 71536 A 630 LEVEL: Event PID : 13362 TID : 2199174244624 PROC : db 2 sysc 0 INSTANCE: db 2 inst 1 NODE : 000 DB : TSMDB 1 APPHDL : 0 -1495 APPID: *LOCAL. db 2 inst 1. 101210235955 AUTHID : DB 2 INST 1 EDUID : 70 EDUNAME: db 2 agent (TSMDB 1) 0 FUNCTION: DB 2 UDB, SW- optimizer, sqlno_collect_stats, probe: 210 COLLECT : TABLE AND INDEX STATS : Object name with schema : AT "2010 -12 -10 -16. 59. 47. 598033" : BY "Synchronous" : DUE TO "Conflict" : failure OBJECT : Object name with schema, 23 bytes TSMDB 1. RESTORE_SRVOBJ IMPACT : None – But also look for subsequent success before running runstats § Given runstats has to scan (nearly) the entire table and it’s indexes, runstats can take a long time – Potentially hours per table if table is large – So, don’t do a runstats all unless directed by TSM support 15 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 diag. log DB 2 for TSM Admins - Update © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 diag. log § Lots of useful information here: – When did DB 2/TSM last start on my instance? – What level of DB 2 am I using? – How much free memory do you have? Is it enough? – Has my db 2 instance gotten any “critical” errors? – Do you need to adjust the DB 2 locklist? – Are you experiencing hardware errors? – Am I running out of space on my DB / logs / boot disk? – Am I getting errors when running runstats? (discussed previously) 17 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Db 2 diag. log – Information Displayed at Start of Instance Timestamp Use eduid to tie entries in the log together—like threadid Db 2 build level Phys Mem 18 Watch the free physical memory. If it’s lower than the minimum required, that suggests a problem. © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Db 2 diag. log – How Much Memory Do You Have? § If you want to know how much free memory is available each time the instance starts: – grep ‘Physical Memory (MB)’ db 2 diag. * Even though there’s 16 G of memory, most of it’s in use when the instance starts! See also this slide. 19 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Db 2 diag. log – Am I Getting any Critical Errors? 2012 -06 -03 -13. 36. 30. 039000 -420 E 18294014 F 999 LEVEL: Critical PID PROC : db 2 syscs. exe : 14496 TID : 10504 INSTANCE: SERVER 1 NODE : 000 DB : TSMDB 1 APPHDL : 0 -43077 APPID: *LOCAL. SERVER 1. 120603203517 AUTHID : ADMINISTRATOR EDUID : 10504 EDUNAME: db 2 agent (TSMDB 1) FUNCTION: DB 2 UDB, base sys utilities, sqe. Local. Database: : Mark. DBBad, probe: 10 MESSAGE : ADM 14001 C An unexpected and critical error has occurred: "DBMarked. Bad". The instance may have been shutdown as a result. "Automatic" FODC (First Occurrence Data Capture) has been invoked and diagnostic information has been recorded in directory "C: Program. DataIBMDB 2 TSM 1SERVER 1FODC_DBMarked. Bad_2012 -06 -03 -1 3. 36. 30. 039000_0000". Please look in this directory for detailed evidence about what happened and contact IBM support if necessary to diagnose the problem. 20 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Db 2 diag. log – Checking for Out of Space Conditions in Active Log / Archive Logs § Running out of disk space is very bad; it’s even worse if the filesystem that runs out of space is the one that db 2 diag. log resides on (can be the boot or system drive). § grep for “DIA 8312 C” in db 2 diag. log 21 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 diag. log – Out of Space Example 1 Using command: grep ‘DIA 8312 C’ db 2 diag. * 2012 -10 -26 -19. 01. 45. 842623 -240 E 3797 A 737 LEVEL: Error (OS) PID PROC : db 2 sysc 0 : 9044114 TID : 4885 INSTANCE: tsm 011 NODE : 000 EDUID EDUNAME: db 2 pclnr (TSMDB 1) 0 : 4885 FUNCTION: DB 2 UDB, oper system services, sqlo. Lio. AIOCollect, probe: 100 MESSAGE : ZRC=0 x 850 F 000 C=-2062614516=SQLO_DISK "Disk full. " DIA 8312 C Disk was full. 22 CALLED : OS, -, aio_return OSERR : ENOSPC (28) "No space left on device" Note the EDUID and find associated messages. © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 diag. log– Out of Space Example 2 (DB Out of Space) Using command: grep ‘is full’ db 2 diag. * 2012 -10 -26 -19. 01. 45. 828204 -240 E 1507 A 775 LEVEL: Error PID PROC : db 2 sysc 0 : 9044114 TID : 4885 INSTANCE: tsm 011 NODE : 000 EDUID EDUNAME: db 2 pclnr (TSMDB 1) 0 : 4885 FUNCTION: DB 2 UDB, buffer pool services, sqlb. Clnr. Async. Write. Completion, probe: 0 MESSAGE : ADM 6017 E The table space "LGTMPTSP" (ID "7") is full. Detected on container "/tsmdbs/tsm 011/db 7/tsm 011/NODE 0000/TSMDB 1/T 0000007/C 0000006. TMP" (ID "6"). The underlying file system is full or the maximum allowed space usage for the file system has been reached. It is also possible that there are user limits in place with respect to maximum file size and these limits have been reached. 23 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 diag. log – Out of Space Example 3 (Example of running out of space in archivelog filesystem) Using command: grep ‘Failed to archive log file’ db 2 diag. * 2013 -03 -19 -16. 08. 48. 148052+060 E 37551034 E 499 PID : 5576 TID LEVEL: Warning : 140145739163392 PROC : db 2 sysc 0 INSTANCE: tsminst 1 NODE : 000 EDUID EDUNAME: db 2 logmgr (TSMDB 1) 0 : 108 FUNCTION: DB 2 UDB, data protection services, sqlpg. Archive. Log. File, probe: 3150 MESSAGE : ADM 1848 W Failed to archive log file "S 0020872. LOG" to "/tsmbox/archlog/archmeth 1/tsminst 1/TSMDB 1/NODE 0000/C 0000000/" from "/tsmbox/actlog/NODE 0000/". 2013 -03 -19 -16. 08. 48. 157169+060 E 37551534 E 568 PID : 5576 TID LEVEL: Error : 140145739163392 PROC : db 2 sysc 0 INSTANCE: tsminst 1 NODE : 000 EDUID EDUNAME: db 2 logmgr (TSMDB 1) 0 : 108 FUNCTION: DB 2 UDB, data protection services, sqlpg. Archive. Log. File, probe: 3160 MESSAGE : ZRC=0 x 850 F 000 C=-2062614516=SQLO_DISK "Disk full. " DIA 8312 C Disk was full. DATA #1 : <preformatted> Failed to archive log file S 0020872. LOG to /tsmbox/archlog/archmeth 1/tsminst 1/TSMDB 1/NODE 0000/C 0000000/ from /tsmbox/actlog/NODE 0000/. 24 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 pd DB 2 for TSM Admins - Update © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 pd – Pinned Log Situations § Long-running read-write transactions can span many log files, pin the active log and cause the server to halt. § Using db 2 pd, you can figure out who the culprit is, and cancel it before it crashes your TSM server. § This section provides a series of steps, similar to what the previous “show logpinned” command used to provide for determining the transaction pinning the active log, and then will show to cancel it. (Note: the “show logpinned” command does not exists in V 6. ) 26 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 pd – Pinned Log Situations § This command has many functions. Using db 2 pd for looking for log pining is just one way it can be used. § Use along with script (available from support) correlate. Txn. Log. pl § Once you determine thread / session / application ID, you can take appropriate action to cancel it. 27 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 pd – Using for pinned log situations (cont. ) 1. Assume we have a server with many R/W transactions and we gotten the command output from db 2 pd –transactions –logs –db tsmdb 1 2. Figure out what is the first and last log file being used by every R/W transaction. – To do this, use the correlate. Txn. Log. pl perl script. – Syntax: perl /pathname/correlate. Txn. Log. pl txn. File log. File > outfile 3. Figure out who is using all the log space from the from previous step. Write down the appl ID. 4. Determine the process / thread / application ID that is pinning my active log from previous step from a snapshot. 5. Use DB 2 to “force” that application to close 28 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 pd – Pinned Log Situations 1. Issue command: db 2 pd –transactions –logs –db tsmdb 1 2. Use script to display log usage: $ perl correlatetxnlog. pl First. Log=S 0060695. LOG, last. Log=S 0060934. LOG App. Id 457, first. Log=S 0060853. LOG, last. Log=S 0060853. LOG, space. Reserved=31706652, log. Space=62803841 App. Id 504, first. Log=S 0060853. LOG, last. Log=S 0060853. LOG, space. Reserved=13292901, log. Space=25300514 App. Id 538, first. Log=S 0060853. LOG, last. Log=S 0060853. LOG, space. Reserved=13929200, log. Space=26244752 App. Id 579, first. Log=S 0060853. LOG, last. Log=S 0060853. LOG, space. Reserved=9155968, log. Space=17089294 App. Id 602, first. Log=S 0060853. LOG, last. Log=S 0060853. LOG, space. Reserved=12664603, log. Space=23639788 App. Id 696, first. Log=S 0060853. LOG, last. Log=S 0060853. LOG, space. Reserved=49847, log. Space=95985 App. Id 695, first. Log=S 0060853. LOG, last. Log=S 0060853. LOG, space. Reserved=84209, log. Space=185466 App. Id 6107, first. Log=S 0060853. LOG, last. Log=S 0060853. LOG, space. Reserved=23384430, log. Space=53871872 App. Id 6101, first. Log=S 0060853. LOG, last. Log=S 0060853. LOG, space. Reserved=5779293, log. Space=13416988 App. Id 3540, first. Log=S 0060695. LOG, last. Log=S 0060851. LOG, space. Reserved=161816138, log. Space=349631511 3. Note that App. Id 3540 spans 156 log files. (60695 – 60851) (156 * 512 MB is 78 GB of active log space!!) Who is this 3540 application id? We need to look at the application snapshot file 29 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 pd – Pinned Log Situations 4. Determine the application ID that belongs to the process: – db 2 connect to tsmdb 1 – db 2 get snapshot for all applications >application. out Examine application. out file, find the entry that says: Most recent operation = xxxxx Find the line that says "Application handle" Sample: Application handle = NNNNN (where NNNNN is the actual application handle) 30 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 pd – Pinned Log Situations Find the application handle that matches : This is index reorg – application name is dsmsvc. exe Application handle Application status Application name Connection request start timestamp Snapshot timestamp Coordinator agent process or thread ID Lock timeout (seconds) Locks held by application Lock waits since connect Buffer pool data logical reads Buffer pool data physical reads Buffer pool index logical reads Buffer pool index physical reads Buffer pool index writes Number of SQL requests since last commit Commit statements Dynamic SQL statements attempted Static SQL statements attempted Most recent operation 31 = = = = = 3540 UOW Executing dsmsvc. exe 03/07/2013 02: 47: 25. 972586 03/07/2013 08: 21: 12. 931830 11772 -1 4 0 4 3 19153833 2307802 2 0 1 2 1 Reorganize © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 pd – Pinned Log Situations 5. Issue the command in the DB 2 Command Line Processor Window : – db 2 "force application (NNNNN)" Wait. Be patient!! Wait 30 minutes, and then do steps 1 -4 again to verify recent entry doesn’t still say same Appl handle. Note: If recent operation is Reorganize, no work is lost with index reorg cancellation as redo logging is in place 32 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 dart DB 2 for TSM Admins - Update © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 dart • The db 2 dart command can be used to verify the architectural correctness of databases and the objects within them. It can also be used to display the contents of database control files in order to extract data from tables that might otherwise be inaccessible. Command syntax: db 2 dart tsmdb 1 /db >db 2 dart. out - inspects entire database • TSM server should be halted (use db 2 stop to make sure the instance is halted) • This is to make sure the DB is in consistent state • Also will improve the performance of the db 2 dart process • Use db 2 dart when you suspect that the database and/or indices have been corrupted. • If db 2 dart indicates that just indices are corrupted/incorrect, TSM and DB 2 L 2 will provide instructions on how to drop the corrupted indices and rebuild them. • db 2 dart has many different options to view and repair databases—DB 2 L 2 will give specific commands to do so based on their analysis 34 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 dart § Sample of db 2 dart output: Database inspection phase end. ___________________ DB 2 DART Processing completed with error! WARNING: The inspection phase did not complete! ERROR: Some tables indexes may be corrupt; errors will be cleared for index objects with invalid state after they have been succesfully rebuilt. DB 2 DART Processing completed with warning(s)! Warning(s) detected during processing. ___________________ 35 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 top DB 2 for TSM Admins - Update © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Db 2 top – An Introduction § There are several methods to collect information and diagnose DB 2 system performance issues. The snapshot monitor is one of the most commonly used tools to collect information in order to narrow down a problem. However, most entries in snapshots are cumulative values and show the condition of the system at a point in time. Manual work is needed to get delta value for each entry from one snapshot to the next. § The db 2 top tool comes with DB 2 (except Windows), and can be used to calculate delta values for those snapshot entries in real time. This tool provides a GUI under a command line mode, so that users can get a better understanding while reading each entry. This tool also integrates multiple types of DB 2 snapshots, categorizes them, and presents them in different screens for the GUI environment. 37 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Starting db 2 top – (2 ways) § Db 2 top can be run in two modes: – interactive mode. In interactive mode, the user enters command directly at the terminal text user interface and waits for the system to respond. The left and right arrow keys on the keyboard can be used to scroll columns to left or right. – Batch mode In batch mode a series of jobs are executed without user interaction. Batch mode creates a file which is then viewed later for analysis § Db 2 top not supported on Windows platform 38 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Starting db 2 top – interactive mode § 1. Log in as instance owner. § 2. Change directory to location of db 2 top. (On AIX this is /opt/tivoli/tsm/db 2/bin. ) § 3. . /db 2 top –d database-name (for TSM, this is typically tsmdb 1) 39 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Db 2 top – Using the Keyboard When in Interactive mode § To go to a specific display, enter letter for that display (there a total of 42 letter command). Examples: – d (Database) – good for viewing active log space used, disk performance. – T (Tables) Shows the tables in the DB 2 DB. – B (Bottlenecks) – Shows resource usage – m (memory) – shows memory usage, bufferpools. – h (Help) - Display help for all single letter commands. § If you invoke “h” to show all single letter commands, just hit <enter> to return to previous display 40 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 top – d Option, Showing Log Space Used 41 § 1. Log in as instance owner. § 2. Change directory to location of db 2 top. (On AIX this is /opt/tivoli/tsm/db 2/bin. ) § 3. . /db 2 top –d database-name (for TSM, this is tsmdb 1) § 4. d option to see database information © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 top – d option, Disk Subsystem Performance 42 § Average Physical Read time (Avg. PRd. Time), § Average Direct Read Time (Avg. DRd. Time), § Average Physical Write time (Avg. PWr. Time), § Average Direct Write time (Avg. DWr. Time). Disk performance info © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 top – m –i 10 option , Memory Usage This will show memory pool allocations, and their % of total. Because we have specified the i option of 10, the display will refresh approximately every 10 seconds. Probably would be better to do this in batch mode, and watch over time segments for memory leaking issues. Note also the package cache. This stores the package and information required for the execution of SQL statements. If it is too small, then your hit% suffers, meaning older statements have to be pushed out to make room for new ones, and aren’t reused as much. (Should be close to 100%, and it usually is. ) 43 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Starting db 2 top – Batch Mode § Log in as instance owner. § Change directory to location of db 2 top. (On AIX this is /opt/tivoli/tsm/db 2/bin. ) §. /db 2 top –d database-name -f /tmp/tsmdb 1. collect. file –C –m 120 –i 60 – -f is name of the file to create – -C run in snapshot collector mode – -m nnn Run for “nnn” minutes – -i nn run in “nn” intervals § Also should reply N to “Writing to” prompt 44 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 top – Using the Data File Previously Collected § To perform automatic performance analysis, issue: . /db 2 top -d tsmdb 1 -f /tmp/tsmdb 1. collect. file -b l -A § To jump to a certain point in time (example, what happened at 2: 00 AM? ): . /db 2 top -d tsmdb 1 -f /tmp/tsmdb 1. collect. file /02: 00 Much more info available on db 2 top. See : http: //www. ibm. com/developerworks/data/library/techarticle/dm-0812 wang/ 45 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 support DB 2 for TSM Admins - Update © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Why would you need this command? § Many times when a PMR is opened, there is a need for analysis from DB 2 point of view, and db 2 support zip file is often obtained. § Command syntax: db 2 support –d tsmdb 1 –c –s -g. § This produces a db 2 support. zip file that can be sent to DB 2 L 2 for analysis. § Contains a collection of lots of files. § Most of the files are person-readable, and often TSM developers and DB 2 developers can gain insight and understanding by examining some of the files. 47 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Structure of db 2 support. zip File Unzipping will give several directories something like this: Definitions: DB 2 CONFIG – configuration DB 2 DUMP – db 2 diag/traps/FODC/stacks • DB 2 SNAP – Snapshots Db 2 supp_system. zip – Additional diagnosis -- extract to same directory EVENTS – generally runstats output STMM – Self-tuning Memory Module 48 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America db 2 mtrk DB 2 for TSM Admins - Update © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Why would you need this command? § During the daily monitoring of your logs, you see the following (TSM actlog): 50 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Why would you need this command? § During the daily monitoring of your logs, you also see the following (db 2 diag. log): 2013 -10 -04 -17. 50. 55. 591345300 E 94676800 A 542 LEVEL: Error PID : 23724122 INSTANCE: tsminst 1 APPHDL 1004 : 0 - AUTHID : TSMINST 1 EDUID : 52314 TID : 52314 NODE : 000 PROC : db 2 sysc 0 DB : TSMDB 1 APPID: *LOCAL. tsminst 1. 131004225022 EDUNAME: db 2 agent (TSMDB 1) 0 FUNCTION: DB 2 UDB, Common Trace API, sqlb. Free. Up. Slot, probe: 122 MESSAGE : ADM 6019 E All pages in buffer pool "IBMDEFAULTBP" (ID "1") ar e in use. Refer to the documentation for SQLCODE - 1218 51 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Memory shortages – What Support Will Need to Know: § How much memory is on this box? Does it meet minimum requirements? (See this slide. ) § How many TSM instances are on this box? § How much memory is allocated on this box? (Look again at the db 2 diag. log described previously. ) § If you have more than 1 instance what is your DBMEMPERCENT option set to? § Is deduplication being used? This will increase memory requirements. § Support may want to track memory usage with the command: – db 2 mtrk -i -d -v -m 52 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Summary of Commands in previous slides 53 Function Command Slide reference Display when runstats last run show runstats Slide 12 How much memory do you have after restart? grep ‘Physical Memory (MB)’ db 2 diag. * Slide 19 Am I getting any critical errors? find. -name "db 2 dia*" exec grep -l "Level: Critical" {} ; Slide 20 Am I getting out of space conditions? grep ‘DIA 8312 C’ db 2 diag. * Slide 22 Am I getting out of space on Archives? grep ‘Failed to archive log file’ db 2 diag. * Slide 24 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Summary of Commands in previous slides (cont. ) Function 54 Command Slide reference Command to assist in figuring out log pinning transaction. db 2 pd –transactions –logs –db tsmdb 1 Slide 28 Figuring out applid that belongs to a transaction. - db 2 connect to tsmdb 1 - db 2 get snapshot for all applications >application. out Slide 30 Force a DB 2 Applid to stop db 2 "force application (NNNNN)" Slide 32 Validate integrity of DB db 2 dart tsmdb 1 /db >db 2 dart. out Slide 34 Create db 2 support file for PMR db 2 support –d tsmdb 1 –c –s -g Slide 47 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Server Instrumentation – DB 2 Related Information DB 2 for TSM Admins - Update © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Understanding the DB 2 Server Instrumentation Categories DB 2 Fetch Prep - Prepare SQL select statements that fetch one row DB 2 MFtch Prepare SQL select statements that may fetch more than one row DB 2 Inser Prepare SQL insert statements DB 2 Delet Prepare SQL delete statements DB 2 Updat Prepare SQL update statements DB 2 Fetch Execute fetch SQL statements (does not actually retrieve data) DB 2 MFtch Execute fetch SQL statements (does not actually retrieve data) DB 2 Inser Execute insert SQL statement DB 2 Delet Execute delete SQL statement DB 2 Updat Execute update SQL statement 56 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Understanding the DB 2 Server Instrumentation Categories DB 2 Fetch data for a single row SQL statement DB 2 Mfetch - Fetch data for a multi-row SQL statement DB 2 CR Prep - Prepare a ‘select count(*)’ CR (count rows) statement DB 2 CR Exec - Execute the CR statement DB 2 Commit - Commit the DB 2 transaction (if high, a potential symptom of log disk) DB 2 Reg Prep - Prepare a registered SQL statement (could be select, delete, etc. ) DB 2 Reg Exec - Execute the registered SQL statement DB 2 Reg Fetch - Fetch data from a registered SQL statement DB 2 Connect - Time to connect to DB 2 (if high watch out for LDAP problems) 57 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America DB 2 – A little background on how TSM communicates § Prepares are expensive – For a given statement we try to do just one prepare and reuse it for later statements that are identical • So if the count of prepares is close to the count of executes then we are not reusing old prepares effectively – You can’t fix this as a customer, but it should be called into TSM support. 58 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Instrumentation Summary Block § Find this at the end of the instrumentation output § Accumulates the timings for each category across all threads § Good way to get a quick feel for where the bottleneck may be § Inst. Tput column – Look in this column for the slowest activity § Don’t stop here though— always look at individual threads to complete the analysis 59 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America TSM Server DB 2 Statistics § Find this near the end of the Instrumentation output § Highlights potential problems with the TSM server database (DB 2) § Look for zero “sec/wait”, “sec/read” and “sec/write” for locks, database reads and database writes – Any value greater than zero indicates a possible problem 60 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America DB 2 SQL Statistics § Find this near the end of the Instrumentation output Execution Time: 64. 626 sec, Number of Executions: 60985, Rows. Returned: 64445 § SELECT SUPERBFID FROM "TSMDB 1". "BF_AGGREGATED_BITFILES" WHERE (SRVID=? AND BFID=? ) ORDER BY SUPERBFID FOR READ ONLY OPTIMIZE FOR 10 ROWS WITH UR --78 To gather this, collectstmt=true is run in the instr begin command. Execution Time: 18. 751 sec, Number of Executions: § 23509, Rows. Returned: 655990 Look for high values in the Execution time. SELECT BFID FROM "TSMDB 1". "BF_AGGREGATED_BITFILES" WHERE (SRVID=? AND SUPERBFID=? ) ORDER BY BFID FOR READ ONLY WITH UR --85 61 – Might point to database response time issue, CPU utilization, locking issue. © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America TSM V 6 Reorg Revisited DB 2 for TSM Admins - Update © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Reorg Technote: § Technote describing TSM / DB 2 reorg in detail: – https: //www. ibm. com/support/docview. wss? uid=swg 21452146 § This technote is updated frequently – some of the useful information provided is in the following slides. 63 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America When were tables/indices last reorganized? New table reorg will be started after 20 days db 2 "select cast( substr(name, 10, min(30, length(name)-9)) as char(30)) as "Tablename", substr(char(datetime), 1, 10) as "Last Reorg" from global_attributes where owner='RDB' and name like 'REORG_TB_%' and datetime is not NULL order by datetime desc" > table_last_reorg. txt Tablename ---------------LICENSE_DETAILS ACTIVITY_SUMMARY GROUP_LEADERS AF_CLUSTERS TEMP_EXPORTED_OBJECTS EXPORT_SESSIONS EXPORT_OBJECTS 64 Last Reorg -----2012 -11 -18 2012 -11 -12 2012 -10 -06 2012 -10 -11 2012 -10 -21 2012 -09 -20 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America DB 2 reorgchck db 2 reorgchk current statistics on table all > db 2 reorgchk. out Important note: If you do not specify "current statistics, " the default is "update statistics, " which will run RUNSTATS commands on all tables in the database. This will likely have a huge performance impact and will take many days to complete. For further detailed information and index reorgchk analysis see: http: //publib. boulder. ibm. com/infocenter/db 2 luw/v 9 r 7/index. jsp? topic=/ com. ibm. db 2. luw. admin. cmd. doc/r 0001971. html 65 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America DB reorgchk: Table Statistics Verify table reorg is indicated Table statistics: F 1: 100 * OVERFLOW / CARD < 5 F 2: 100 * (Effective Space Utilization of Data Pages) > 70 F 3: 100 * (Required Pages / Total Pages) > 80 SCHEMA. NAME CARD OV NP FP ACTBLK TSIZE F 1 F 2 F 3 REORG --------------------------------------------. . Table: TSMDB 1. BACKUP_OBJECTS 8. 3 e+07 1 e+06 - 2. 70 e+10 1 78 92 --Table: TSMDB 1. BF_AGGREGATED_BITFILES 3. 5 e+08 8 e+06 2 e+06 4 e+06 - 2. 47 e+10 2 44 60 -** Table: TSMDB 1. BF_AGGREGATE_ATTRIBUTES 2186123 24 4845 6688 - 72142056 0 69 72 -** Tivoli Storage Manager server should only initiate reorg if F 1 and/or F 2 are indicated and 20 days have passed since the last table reorganization. If you‘re seeing F 3 being set and reorgs happening: – http: //www. ibm. com/support/docview. wss? uid=swg 1 IC 83382 – Fix is available with V 6. 2. 5 & V 6. 3. 3 – Local fix – apply on direction of TSM support: db 2 "insert into tsmdb 1. global_attributes (owner, name, type, length, int 32) values('RDB', 'REORG_TBLFLAGS', 3, 0, 3)" 66 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America DB reorgchk: Index Statistics Verify index reorg is indicated Index statistics: F 4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80 F 5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 – PCTFREE)) F 6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100 F 7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20 F 8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20 SCHEMA. NAME INDCARD LEAF ELEAF LVLS NDEL KEYS |. . |F 4 F 5 F 6 F 7 F 8 REORG ----------------------------------|. . |------------. . | Table: TSMDB 1. BACKUP_OBJECTS |. . | Index: TSMDB 1. IMBKIDXEXPIRE_NDX |. . | 8. 8 e+07 2 e+05 871 4 7 e+06 8. 8 e+07 |. . |65 66 68 7 0 *---Index: TSMDB 1. IMBKIDXKEYS_NDX |. . | 8. 8 e+07 3 e+05 178 4 1 e+07 8. 8 e+07 |. . |64 62 54 9 0 *---Index: TSMDB 1. IMBKNAME_NDX |. . | 8. 8 e+07 3 e+05 2134 4 5 e+06 8. 8 e+07 |. . |98 54 38 5 0 ----Index: TSMDB 1. IMBK_NDX |. . | 8. 8 e+07 1 e+05 233 3 9 e+06 8. 8 e+07 |. . |98 42 2 9 0 -*--Index: TSMDB 1. IMBK_REPL_NDX |. . | 8. 8 e+07 16559 13 3 3 e+07 1536213 |. . |98 40 8 26 0 -*-*- If F 5 is indicated on any index for a table and 20 days have passed since the last index reorganization, the indices for that table are reorganized. If F 7 or F 8 is indicated on table BF_BITFILE_EXTENTS, APAR IC 82352 will perform an index reorganization with the CLEANUP ONLY option on that table. 67 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Verify Index Reorg Activity New index reorg will be started after 20 days db 2 "select cast( substr(name, 10, min(30, length(name)-9)) as char(30)) as "Indices for Tablename", substr(char(datetime), 1, 10) as "Last Reorg" from global_attributes where owner='RDB' and name like 'REORG_IX_%' and datetime is not NULL order by datetime desc" > index_last_reorg. txt Empty result set with ALLOWINDEXREORG NO (default) Indices for Tablename Last Reorg ---------------0 record(s) selected. 68 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Automatic Reorg Tables that have shown difficulties Tablename Page size Buffer pool BF_AGGREGATED_BITFILES 16 K IBMDEFAULTBP BF_BITFILE_EXTENTS 16 K IBMDEFAULTBP BF_DEREFERENCED_CHUNKS 16 K IBMDEFAULTBP BF_QUEUED_CHUNKS 16 K IBMDEFAULTBP BACKUP_OBJECTS 32 K IBMDEFAULTBP ARCHIVE_OBJECTS 32 K IBMDEFAULTBP No automatic reorg for BF_DEREFERENCED_CHUNKS & BF_QUEUED_CHUNKS Online reorg might take too long to complete Reorg might result in locking issues during normal server operation Offline reorg might be indicated – check via db 2 reorgchk command 69 © 2013 IBM Corporation

Advanced Technical Skills (ATS) North America Questions ? 70 © 2013 IBM Corporation