DB2
The DB2 page displays information about the activity of DB2.
Contents:
DB2
The DB2 page displays statistics about DB2 activity and which SQL statements were consuming the CPU during the monitoring period. This helps in identifying improperly designed applications and databases, inadequate indexing, and programming errors.
Menu commands available in this view include:
-
Code Details -- Displays the Code Details subpage, which reports the activity of each Module and CSECT that is associated with the selected DB2 plan or package name, and statement number (as active and wait percentages of all samples in the monitor data set). From the Code Detail subpage, you can display a Histogram showing the locations within a CSECT where delays occurred, or you can list the types of delays found within a CSECT.
-
Statement Detail -- Displays the detailed properties of a particular statement. Each property name and its value are shown, with the exception of properties whose values are zero, blank, or 'N/A'.
-
Select SQL -- Displays the SQL Statement subpage that shows the full text of the selected SQL statement.
-
Explain SQL -- Displays the Explain subpage for the SQL statement.
This feature requires that the Explain function has been enabled in the CA Mainframe Application Tuner Server customization. -
Show Declare -- Displays the DECLARE Statement panel, which shows the DECLARE statement coded by the application programmer for this DB2 verb.
-
This action is valid for OPEN, FETCH, and CLOSE statements and UPDATE/DELETE statements that contain the clause WHERE CURRENT OF <cursor_name>.
-
This action is not valid when DB2NOCAT=YES is specified.
This feature requires that the Explain function has been enabled in the CA Mainframe Application Tuner Server customization.
-
-
Explain Information -- Displays the messages that are generated based on the results of the DB2 EXPLAIN command. If the statement has an associated DECLARE CURSOR indicated by a number in the Declare Stmt column, the Explain data is from the DECLARE CURSOR and not from the statement itself. The displayed set of messages provides suggestions to improve the performance of the SQL statement.
DB2 Columns
-
DBRM or Package
Displays the name of a package (DBRM) which describes the SQL statement. -
D S
Indicates the type of SQL being executed. Either D for Dynamic or S for Static SQL. -
Data From
This column represents the method used to collect the SQL data being presented. CA MAT collects dat about SQL using two different methods. One is through the Sampling process and the other is by the Harvester intercepting the SQL as it processes.-
H
This SQL Data was collected by the CA MAT DB2 Harvester. This would also indicate that this SQL statement was not sampled as part of the Sampling process. Fields normally containing sample based data will contain zeros.
Examples of those fields would be %Active, %Wait. -
S
This SQL Data was collected by the CA MAT DB2 Sampling SRB. This would also indicate that this SQL statement was not intercepted by the CA MAT DB2 Harvester. Fields normally containing Harvester collected data will contain zeros.
Examples of those fields would be Call Count or any of the CPU fields such as Total CPU. -
B
This SQL Data was collected by both the Harvester and Sampler. SQL statements containing data from both the Harvester and the sampler provide the most complete view of that SQL statements performance.
Note: There are a number of reasons why data may not be collected by either DB2 Harvester or the sampling process based on the DB2 options selected during customization or by the adminstrator of the CA MAT environment after customization.
If you are running with both the Harvester and the Sampler collecting data, filtering this column by putting a B in the column header will eliminate any data collected by only the Sampler or the Harvester.
-
-
Stmt Num
Displays the statement number for a specific statement contained in a package or plan. -
Type
Displays the type of call issued with this statement as an argument. Some values for call type are:-
OPEN
The OPEN statement opens a cursor.
Invocation:
This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared. -
FETCH
The FETCH statement positions a cursor on the next row of its result table and assigns the values of that row to host variables.
Invocation:
This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared. -
CLOSE
The CLOSE statement closes a cursor. If a temporary copy of a result table was created when the cursor was opened, that table is destroyed.
Invocation:
This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared. -
PREPARE
-
SELECT
The SELECT INTO statement produces a result table containing at most one row, and assigns the values in that row to host variables.
If the table is empty, the statement assigns +100 to SQLCODE, '02000' to SQLSTATE, and does not assign values to the host variables. The tables or views identified in the statement can exist at the current server or at any DB2 subsystem with which the current server can establish a connection.
Invocation:
This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.Tip: Use the Explain feature to find out where the delays are occurring.
-
INSERT
The INSERT statement inserts rows into a table or view. The table or view can be at the current server or any DB2 subsystem with which the current server can establish a connection. Inserting a row into a view also inserts the row into the table on which the view is based.
There are two forms of this statement:-
The INSERT via VALUES is used to insert a single row into the table or view using the values provided or referenced.
-
The INSERT via SELECT is used to insert one or more rows into the table or view using values from other tables and/or views.
-
-
DELETE
The DELETE statement deletes rows from a table or view. The table or view can be at the current server or any DB2 subsystem with which the current server can establish a connection. Deleting a row from a view deletes the row from the table on which the view is based.
There are two forms of this statement:-
The searched DELETE form is used to delete one or more rows, optionally determined by a search condition.
-
The positioned DELETE form is used to delete exactly one row, as determined by the current position of a cursor.
-
-
UPDATE
The UPDATE statement updates the values of specified columns in rows of a table or view. Updating a row of a view updates a row of the table on which the view is based. The table or view can exist at the current server or at any DB2 subsystem with which the current server can establish a connection.
There are two forms of this statement:-
The searched UPDATE form is used to update one or more rows optionally determined by a search condition.
-
The positioned UPDATE form is used to update exactly one row, as determined by the current position of a cursor.
-
-
-
Call Count
This is the number of times during the monitoring session that CA MAT detected that this SQL statement was called from the target address space. -
Total CPU
The amount of CPU time, in seconds, that was consumed by the task processing the SQL calls. -
CPU-P-CALL
The average amount of CPU time, in seconds, that was consumed by the task to process each SQL call. -
Total Resp Time
The total amount of time, in seconds, for all of the SQL statements to complete. -
Average Resp Time
The total amount of time, in seconds, for all of the SQL statements to complete. -
Declare Stmt
Displays the statement number of the DECLARE associated with the statement
Declare statements are only associated with static OPEN, FETCH, CLOSE, and UPDATE/DELETE statements that contain the clause WHERE CURRENT OF <cursor_name>.
DECLARE statements are obtained from the DB2 Catalog only when DB2CTSQL=YES.
If there is no DELCARE associated with the statement or DB2CTSQL=NO was specified, this column will contain NA -
Total Samps
During this monitor session, this is the count of all samples when CA MAT detected any occurance of this SQL statement. A zero in this field indicates that this statement was not sampled and the data being presented was obtained by the DB2 Harvester. -
Active %
Displays the percentage of the monitored period that the DB2 statement was actually using the CPU. -
Wait %
Displays the percentage of the monitored period that the program waited for DB2 to return data. -
Total %
The sum of both the Actv% and the Wait% columns.