1 / 27

Trouble Shooting and Maintenance Pertemuan 13

Trouble Shooting and Maintenance Pertemuan 13. Matakuliah : T0413 Tahun : 2009. db2 ? <code>. Review the DB2 online manuals (Information Center). Review Admin notification log or the db2diag.log. Problem!. Check the DB2 Express-C forum. Review system with. Operating System.

baris
Download Presentation

Trouble Shooting and Maintenance Pertemuan 13

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. Trouble Shooting and MaintenancePertemuan 13 Matakuliah : T0413 Tahun : 2009

  2. db2 ? <code> Review the DB2 online manuals (Information Center) Review Admin notification log or the db2diag.log Problem! Check the DB2 Express-C forum Review system with Operating System commands Search for APARs, or known problems Problem Diagnosis

  3. db2 help: ? db2 ? SQL0104N db2 ? SQL104N db2 ? SQL-0104 db2 ? SQL-104 db2 ? SQL-104N

  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

  5. The DB2 Information Center • The DB2 Information Center contains the DB2 online manuals. It also includes the manuals of products related to DB2. • 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: • V9: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp • V9.5: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp • Most of the answers to your questions can be found from the DB2 Information Center!

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

  7. db2diag.log • Information included in the db2diag.log • The timestamp when the error occurred. Use it to find the error quickly • The application name and DB2 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 db2diag.log • Recreate the problem. A new db2diag.log file will be generated

  8. Example of a db2diag.log entry Level: Be concerned when you see “Severe” or “Error” here. Timestamp when the problem occurred Application name Error message information. Use any of these for searching in “Google”

  9. Locating the db2diag.log • WIN XP/2003 (default) • C:\Documents and Settings\All Users\Application Data\IBM\DB2\DB2COPY1\<instance name> • WIN VISTA • ProgramData\IBM\DB2\ • Linux/UNIX (default) • INSTHOME/sqllib/db2dump (INSTHOME is the home directory of the instance owner) • You can change the location of the db2diag.log using diagpath in the dbm cfg • db2 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

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

  11. Maintenance Utilities REORG, RUNSTATS, REBIND

  12. Database Operational View in Control Center

  13. REORG TABLE <tablename> 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 • 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 DB2 whenever queries are executed • DB2 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

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

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

  17. REBIND db2rbind database_alias -l <logfile> • Example: • db2rbind sample -l mylog.txt

  18. Maintenance Choices • Automated Maintenance • Have DB2 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

  19. Automated Maintenance • Based on a user-defined maintenance window • DB2 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

  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 DB2 administration tools • It is the main interface where all tasks can be created, edited, and scheduled

  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

  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

  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

  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

  25. Visual Explain Access Plan Execute & Access Plan Statement to analyze

  26. Visual Explain

More Related