Bina Nusantara University 1 Matakuliah Tahun

Bina Nusantara University 1

Matakuliah Tahun : 2009 : T 0413 Trouble Shooting and Maintenance Pertemuan 13 Bina Nusantara University 2

Problem Diagnosis db 2 ? <code> Review the DB 2 online manuals (Information Center) Problem! Review Admin notification log or the db 2 diag. log Check the DB 2 Express-C forum Review system with Operating System commands Search for APARs, or known problems Bina Nusantara University 3

db 2 help: ? db 2 db 2 Bina Nusantara University ? ? ? SQL 0104 N SQL-0104 SQL-104 N 4

Finding More Information About Error Codes • To find out more information about an error code received, in the Command Editor input area, enter the code prefixed by a question mark and click the Execute button Prefix error code by a question mark in the Input area More information about the error code appears in the Output area Bina Nusantara University 5

The DB 2 Information Center • The DB 2 Information Center contains the DB 2 online manuals. It also includes the manuals of products related to DB 2. • It has a search field • It can be installed locally, or accessed through the internet • The internet version is the most up-to-date one. The URLs are: – V 9: http: //publib. boulder. ibm. com/infocenter/db 2 luw/v 9/index. jsp – V 9. 5: http: //publib. boulder. ibm. com/infocenter/db 2 luw/v 9 r 5/index. jsp • Most of the answers to your questions can be found from the DB 2 Information Center! Bina Nusantara University 6

DB 2 Administration Notification Log §Log with diagnosis information at the point failure §On Linux/UNIX platforms, the administration notification log is a text file called instance. nfy §On Windows, all administration notification messages are written to the Event Log, or you can see in in the DB 2 Journal §The DBM configuration parameter notifylevel specifies the level of information to be recorded: ƒ 0 -- No administration notification messages captured (not recommended) ƒ 1 -- Fatal or unrecoverable errors ƒ 2 -- Immediate action required ƒ 3 -- Important information, no immediate action required (default) ƒ 4 -- Informational messages Bina Nusantara University 7

db 2 diag. log §Information included in the db 2 diag. log § § The timestamp when the error occurred. Use it to find the error quickly The application name and DB 2 function involved in the problem. A diagnostic message explaining the reason for the error. Any available supporting data, such as SQLCA data structures and pointers to the location of any extra dump or trap files. §If you can recreate the problem: § Remove/rename the db 2 diag. log § Recreate the problem. A new db 2 diag. log file will be generated Bina Nusantara University 8

Example of a db 2 diag. log entry Timestamp when the problem occurred Level: Be concerned when you see “Severe” or “Error” here. Application name Error message information. Use any of these for searching in “Google” Bina Nusantara University 9

Locating the db 2 diag. log § WIN XP/2003 (default) 4 C: Documents and SettingsAll UsersApplication DataIBMDB 2 COPY 1<instance name> ƒWIN VISTA ƒ Program. DataIBMDB 2 § Linux/UNIX (default) 4 INSTHOME/sqllib/db 2 dump (INSTHOME is the home directory of the instance owner) § You can change the location of the db 2 diag. log using diagpath in the dbm cfg ƒ db 2 update dbm cfg using diagpath <path> § The verbosity of diagnostic text is determined by diaglevel in the dbm cfg ƒ range is 0 to 4 (default is 3) ƒ most verbose is 4 Bina Nusantara University 10

The DB 2 Express-C Forum §Free community assistance www. ibm. com/developerworks/forums/dw_forum. jsp? forum=805&cat=19 §Mainly in English §The IBM DB 2 Express-C team monitors the forum, though it is the community who assists the most §You can also access it from the DB 2 Express-C web site: www. ibm. com/db 2/express (and click the button to go to the forum) Bina Nusantara University 11

Maintenance Utilities REORG, RUNSTATS, REBIND REORG RUNSTATS REBIND Bina Nusantara University 12

Database Operational View in Control Center Bina Nusantara University 13

Table Reorganization (REORG) • • Over time, tables and indexes get fragmented (much like a file system) A REORG reclaims wasted space and re-organizes data to make retrieval more efficient Tables that are modified the most will benefit the most from REORGs REORG has an Online and Offline option – Offline REORG is faster and more efficient, but does not permit access to the table – Online REORG allows access to the table, but can consume a lot of system resources; it works best for small tables REORG TABLE <tablename> Bina Nusantara University §Example: REORG TABLE employee 14

Statistics (RUNSTATS) • Keeps database statistics up-to-date – e. g. number of rows in a table, index size, data value ranges, etc. • Statistics are used by DB 2 whenever queries are executed – DB 2 Query Optimizer is a cost-based optimizer – Analyzes these statistics to produce data access plan • The frequency of statistics gathering should be determined by how often the data in the table changes RUNSTATS ON TABLE <schema. tablename> §Example: RUNSTATS ON TABLE myschema. employee Bina Nusantara University 15

RUNSTATS and REORG from the Control Center Bina Nusantara University 16 Control Center > (expand) All Databases folder > (expand) Database > (select) Tables Folder > (right-click) Table > Reorganize/Run Statistics

Bind/Rebind Packages § Applies to embedded SQL applications, SQL stored procedures § Static SQL application depicted embedded C program (. sqc) Access plans for static SQL are determined at BIND time Bina Nusantara University C file (. c) library. exe bind file (. bnd) package 17

REBIND db 2 rbind database_alias -l <logfile> §Example: db 2 rbind sample -l mylog. txt Bina Nusantara University 18

Maintenance Choices • Automated Maintenance – Have DB 2 automatically look after maintenance for you (REORG, RUNSTATS, BACKUP) • Manual Maintenance – You perform maintenance activities when the need arises • Create Scripts to Perform Maintenance – Schedule regular execution of scripts Bina Nusantara University 19

Automated Maintenance • Based on a user-defined maintenance window – DB 2 will perform maintenance on this window ONLY if needed. – Two maintenance windows: • ONLINE: For online maintenance activities such as RUNSTATS and online BACKUP. These can be defined at any time because the database remains fully accessible • OFFLINE: For offline maintenance activities such as offline BACKUP and REORG. Database may become inaccessible and any connected applications may be affected. – Database keeps track of operations performed and schedules any needed maintenance in the next appropriate maintenance window Bina Nusantara University 20

Scheduled Maintenance Using Task Center • More time-consuming than automatic maintenance, but more customization possibilities – E. g. different tables might have different backup needs • Task Center is the central location for all scheduled activities for the DB 2 administration tools – It is the main interface where all tasks can be created, edited, and scheduled Bina Nusantara University 21

Performance • Configure parameters – Use the Configuration Advisor – The bufferpool is the most important parameter to configure • This is like cache for databases • Do not over allocate physical memory to the bufferpool • Design good indexes – Use the Design Advisor – Indexes can reduce sorts Bina Nusantara University 22

Performance • Choose the right locking strategy – Depending on your application logic choose the appropriate isolation level. UR provides the most concurrency. • Reduce deadlocks by minimizing: – Repeatable Read isolation – Lock Escalation – Lock Conversion – Bad Access plans (table scans) – Catalog Modification – Referential Constraint Enforcement Bina Nusantara University 23

Performance • Minimize network traffic – Use stored procedures – Limit the fetch size using clauses like: • Optimize for X rows: Use this clause to determine the best communications buffer size. • fetch first x rows only: Limit on the number of rows retrieved Eg: SELECT EMPNAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC FETCH FIRST 100 ROWS ONLY OPTIMIZE FOR 20 ROWS Bina Nusantara University 24

Performance • Dynamic vs. Static SQL – Static SQL performs better than dynamic SQL – If must use dynamic SQL, ensure to use parameter markers • Code your SQL appropriately – Don’t ask for more than you need • For example, asking for all the columns is not good, like in: Select * from … – Analyze bad performing SQL with Visual Explain Bina Nusantara University 25

Visual Explain. Access Plan Execute & Access Plan Statement to analyze Bina Nusantara University 26

Visual Explain Bina Nusantara University 27