One you have established the DB2 environment and installed the application systems that access the environment, it very important that the environment be monitored regularly to ensure optimal performance. The DB2 system administrator or database administrator usually performs this job.
Many factors contribute to the level of performance achieved by DB2 application. So unless a systematic approach is implemented for performance monitoring, the performance can fluctuate widely from time to time. You must have a firm definition of DB2 performance, before you start monitoring it. The factors that influence the DB2's performance are: workload, throughput, resources, optimization and contention. The workload requested to DB2 defines the demand for data. It is a combination of the on-line transactions, batch jobs, system commands, etc., directed through the system at any given time. The workload can fluctuate during the day. There can be peak hours and there can be lean hours. The overall workload has a major impact on DB2 performance. Throughput defines the overall capability of the system to process data.
It is a composite of the I/O speed, CPU speed, and the efficiency of the operating system. The hardware and software tools at the disposal of the system are known as resources. Some examples are memory, DASD, cache controllers, microcode, etc. Even though all systems can be optimized, the optimization in DB2 is special because, it is accomplished internally. When the demand for a particular resource is high contention can result. Contention of a condition is where two or more components attempt to use the same resource at the same time in a conflicting way. As contention increases, throughput decreases.
There are many types of DB2 performance monitoring. It is wise to implement procedures for all of them. If you are not monitoring performance constantly then the performance can slowly degrade and when you realize something has gone wrong, it will be very difficult to correct them. The following sections deal with the different performance monitoring methods.
This type of monitoring is based on reading trace information. A DB2 trace can be thought of as a window into the performance characteristics of aspects of DB2 workload. DB2 traces diagnostic information describing particular events. As DB2 operates, it writes trace information that can be read and analyzed performance information.
DB2 provides six types of traces, which gives information about the performance. They are Accounting Trace, Audit Trace, Global Trace, Monitor Trace, Performance Trace and Statistics Trace. Accounting, Audit, Global, Monitor, Performance and Statistics Traces
The accounting trace is singly the most important trace for judging the performance of DB2 application programs. Using this trace DB2 writes data pertaining to CPU and elapsed time of the program, EDM pool usage, locks and latches requested for the program, number of get page requests, by bufferpool issued by programs, number of synchronous writes, type of SQL issued by the program, number of COMMITS and ABORTs issued by the program, program's use of sequential pre-fetch and other DB2 performance features.
The Audit trace is useful for installations that must meticulously track specific types of DB2 events. Not every installation needs an audit trace. However, those wanting to audit by auathid, specific table accesses, and other DB2 events can use this trace.
Global trace information is used to service DB2. It records all information regarding the entries and exits form internal DB2 modules as well as other information about DB2 internals. Avoid this trace because this trace can add 100% CPU overhead to your DB2 system.
A host of useful information is recorded by the monitor trace. The information provided include, DB2 statistics trace information, DB2 accounting trace information, information about current SQL statements, etc. The primary reason for the existence of this trace is to enable the application programmer to write programs that provide on-line monitoring of DB2 performance.
The DB2 performance trace records information about all types of DB2 events. It should be used only when all other avenues of monitoring have been exhausted, because it consumes a great deal of system resources. Information pertaining to the entire UB2 subsystem is recorded in the statistics trace. This is particularly useful for measuring the activity and response of DB2 as a whole. Information on the utilization and status of bufferpools, DB2 locking, DB2 logging, and DB2 storage are recorded here.
When a trace is started, DB2 formats records containing the requested information. After the information is prepared, it must be externalized. DB2 traces can be written to46 destinations. They are given in the following table:
The Instrumentation Facility Interface is a DB2 trace interface that enables DB2 programs to read, write and create DB2 trace records and issue DB2 commands. The following table gives a summary of the destinations for each type of DB2 trace. Y indicates that a specified destination is valid and N indicates it is not.
DB2 Trace Destinations
DB2 - Performance Monitor
IBM's DB2-PM is the most widely used batch performance monitor for DB2. Although DB2-PM provides and on-line version also, it is not widely used. DB2-PM permits performance analysts to review formatted trace records to assist in evaluating the performance of not only the DB2 subsystem, but also the DB2 applications. As the DB2 system executes, trace records are written to either GTF or SMF. Which trace records are written depends on which DB2 traces are active. The trace information is then taken to the DB2- PM, which creates various reports and graphs. A schematic diagram of the working of DB2-PM is given in the above figure.
Officially EXPLAIN is regarded as an SQL statement, the function performed by it is in the line of utility. EXPLAIN allows the user to obtain information regarding the optimizer's choice of access strategy for a specified SQL statement.
How EXPLAIN Works
The information provided by EXPLAIN is invaluable in determining the following:
Such information can be useful in tuning the existing applications and also for determining how projected applications will perform. The syntax of EXPLAIN is as follows: EXPLAIN type FOR statement
Here 'type' is PLAN or ALL and the 'statement' is the SQL statement to be EXPLAINed. The output form the EXPLAIN statement is placed into a table called PLANTABLE which is nothing but a standard DB2 table that must be defined with predetermined columns, data types and lengths.
When this EXPLAIN is executed, DB2 will place the information regarding its implementation in the LAN_TABLE of the user issuing the EXPLAIN.
IBM Mainframe Related Interview Questions
|IBM Lotus Notes Interview Questions||IBM-CICS Interview Questions|
|COBOL Interview Questions||Linux Interview Questions|
|IBM-JCL Interview Questions||IBM Mainframe Interview Questions|
|IBM AIX Interview Questions||IBM WAS Administration Interview Questions|
|IBM Lotus Domino Interview Questions||IBM Integration Bus Interview Questions|
|Mainframe DB2 Interview Questions||Unix Production Support Interview Questions|
Ibm Mainframe Tutorial
Introduction To Software Development
Introduction To Ibm Mainframes
Tso And Ispf
Jes2, ]es3 And Sms
Introduction To Job Control Language (jcl)
The Job Statement
The Exec Statement
The Job And Exec Statements
The Dd Statement
Procedures And Symbolic Parameters
Generation Data Groups (gdg), Compile/link-edit And Run Jcls
Access Method Services (ams)
Additional Vsam Commands
Introduction To Rexx
Overview Of Rexx
Introduction To Cics
Exception Handling In Cics
Developing A Cics Application
Cics Programming Techniques
Basic Mapping Support (bms)
Transient Data Control
Temporary Storage Control
Interval And Task Control
Cics Application Design
Recovery And Restart
System Security And Intersystem Communication
Cics Debugging Facilities And Techniques
Bms Map Definition Macros And Copylib Members
Cics Response And Abend Codes
Data, Information And Information Processing
Introduction To Database Management Systems
Introduction To Relational Database Management Systems
Database Architecture And Data Modeling
Overview Of Db2
Structured Query Language (sql)
Data Security And Access
Db2 Application Development
Qmf And Db2i
Db2 Performance Monitoring, Utilities And Recovery/restart
Overview Of Information Management System (ims)
Introduction To Vs Cobol Ii
Overview Of Application Development In Vs Cobol Ii
Overview Of The Cobol Program
Sorting And Merging Files
Coding Cobol Programs That Run Under Cics. Ims, Db2 And Ispf
Compiling The Program
Link-editing The Program
Executing The Program
Improving Program Performance
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.