Database monitor view 1000 - SQL Information
Displays the SQL logical view format for database monitor QQQ1000.
Create View QQQ1000 as
(SELECT QQRID as Row_ID,
QQTIME as Time_Created,
QQJFLD as Join_Column,
QQRDBN as Relational_Database_Name,
QQSYS as System_Name,
QQJOB as Job_Name,
QQUSER as Job_User,
QQJNUM as Job_Number,
QQI9 as Thread_ID,
QQUCNT as Unique_Count,
QQI5 as Unique_Refresh_Counter,
QQUDEF as User_Defined,
QQSTN as Statement_Number,
QQC11 as Statement_Function,
QQC21 as Statement_Operation,
QQC12 as Statement_Type,
QQC13 as Parse_Required,
QQC103 as Package_Name,
QQC104 as Package_Library,
QQC181 as Cursor_Name,
QQC182 as Statement_Name,
QQSTIM as Start_Timestamp,
QQ1000 as Statement_Text,
QQC14 as Statement_Outcome,
QQI2 as Result_Rows,
QQC22 as Dynamic_Replan_Reason_Code,
QQC16 as Data_Conversion_Reason_Code,
QQI4 as Total_Time_Milliseconds,
QQI3 as Rows_Fetched,
QQETIM as End_Timestamp,
QQI6 as Total_Time_Microseconds,
QQI7 as SQL_Statement_Length,
QQI1 as Insert_Unique_Count,
QQI8 as SQLCode,
QQC81 as SQLState,
QVC101 as Close_Cursor_Mode,
QVC11 as Allow_Copy_Data_Value,
QVC12 as PseudoOpen,
QVC13 as PseudoClose,
QVC14 as ODP_Implementation,
QVC21 as Dynamic_Replan_SubCode,
QVC41 as Commitment_Control_Level,
QWC1B as Concurrent_Access_Resolution,
QVC15 as Blocking_Type,
QVC16 as Delay_Prepare,
QVC1C as Explainable,
QVC17 as Naming_Convention,
QVC18 as Dynamic_Processing_Type,
QVC19 as LOB_Data_Optimized,
QVC1A as Program_User_Profile_Used,
QVC1B as Dynamic_User_Profile_Used,
QVC1281 as Default_Collection,
QVC1282 as Procedure_Name,
QVC1283 as Procedure_Library,
QQCLOB2 as SQL_Path,
QVC1284 as Current_Schema,
QQC18 as Binding_Type,
QQC61 as Cursor_Type,
QVC1D as Statement_Originator,
QQC15 as Hard_Close_Reason_Code,
QQC23 as Hard_Close_Subcode,
QVC42 as Date_Format,
QWC11 as Date_Separator,
QVC43 as Time_Format,
QWC12 as Time_Separator,
QWC13 as Decimal_Point,
QVC104 as Sort_Sequence_Table ,
QVC105 as Sort_Sequence_Library,
QVC44 as Language_ID,
QVC23 as Country_ID,
QQIA as First_N_Rows_Value,
QQF1 as Optimize_For_N_Rows_Value,
QVC22 as SQL_Access_Plan_Reason_Code,
QVC24 as Access_Plan_Not_Saved_Reason_Code,
QVC81 as Transaction_Context_ID,
QVP152 as Activation_Group_Mark,
QVP153 as Open_Cursor_Threshold,
QVP154 as Open_Cursor_Close_Count,
QVP155 as Commitment_Control_Lock_Limit,
QWC15 as Allow_SQL_Mixed_Constants,
QWC16 as Suppress_SQL_Warnings,
QWC17 as Translate_ASCII,
QWC18 as System_Wide_Statement_Cache,
QVP159 as LOB_Locator_Threshold,
QVP156 as Max_Decimal_Precision,
QVP157 as Max_Decimal_Scale,
QVP158 as Min_Decimal_Divide_Scale ,
QWC19 as Unicode_Normalization,
QQ1000L as Statement_Text_Long,
QVP15B as Old_Access_Plan_Length,
QVP15C as New_Access_Plan_Length,
QVP151 as Fast_Delete_Count,
QQF2 as Statement_Max_Compression,
QVC102 as Current_User_Profile,
QVC1E as Expression_Evaluator_Used,
QVP15A as Host_Server_Delta,
QQC301 as NTS_Lock_Space_Id,
QQC183 as IP_Address,
QFC11 as IP_Type,
QQSMINT2 as IP_Port_Number,
QVC3004 as NTS_Transaction_Id,
QQSMINT3 as NTS_Format_Id_Length,
QQSMINT4 as NTS_Transatction_ID_SubLength,
QVRCNT as Unique_Refresh_Counter2,
QVP15F as Times_Run,
QVP15E as FullOpens,
QVC1F as Proc_In_Cache,
QWC1A as Combined_Operation,
QVC3001 as Client_Applname,
QVC3002 as Client_Userid,
QVC3003 as Client_Wrkstnname,
QVC3005 as Client_Acctng,
QVC3006 as Client_Progamid,
QVC5001 as Interface_Information,
QVC82 as Open_Options,
QWC1D as Extended_Indicators,
QWC1C as DECFLOAT_Rounding_Mode,
QWC1E as SQL_DECFLOAT_Warnings,
QVP15D as Worst_Time_Micro,
QQINT05 as SQ_Unique_Count,
QFC13 as Concurrent_Access_Res_Used,
QQSMINT8 as SQL_UDFs_Not_Inlined,
QVC3007 as Result_Set_Cursor,
QFC12 as Implicit_XMLPARSE_Option,
QQSMINT7 as SQL_XML_Data_CCSID,
QQSMINT5 as OPTIMIZER_USE,
QFC14 as XML_Schema_In_Cache,
QQC105 as Current_User,
QFC15 as Row_Column_Access_Control,
QQTIM12A as Temporal_System_Time,
QFC16 as SYSTIME_Bind_Option,
QFC17 as Temporal_System_Time_Query,
QQDBCLOB1 as DBCLOB_HOSTVR,
QQSMINT6 as StmtCmpReuseMin,
QFC18 as Override_Generated_Values
FROM DbMonLib/DbMonTable
WHERE QQRID=1000)
View Column Name | Table Column Name | Description |
---|---|---|
Row_ID | QQRID | Row identification |
Time_Created | QQTIME | Time row was created |
Join_Column | QQJFLD | Join column (unique per job) |
Relational_Database_Name | QQRDBN | Relational database name |
System_Name | QQSYS | System name |
Job_Name | QQJOB | Job name |
Job_User | QQUSER | Job user |
Job_Number | QQJNUM | Job number |
Thread_ID | QQI9 | Thread identifier |
Unique_Count | QQUCNT | Unique count (unique per query) When monitor files are created from an SQL Plan Cache snapshot, this value is the Plan Identifier |
Unique_Refresh_Counter | QQI5 | Unique refresh counter |
User_Defined | QQUDEF | User-defined column |
Statement_Number | QQSTN | Statement number (unique per statement) |
Statement_Function | QQC11 | Statement function:
|
Statement_Operation | QQC21 | Statement operation:
|
Statement_Operation (continued) | QQC21 |
|
Statement_Operation (continued) | QQC21 |
|
Statement_Type | QQC12 | Statement type:
|
Parse_Required | QQC13 | Parse required (Y/N) |
Package_Name | QQC103 | Name of the package or name of the program that contains the current SQL statement |
Package_Library | QQC104 | Name of the library containing the package |
Cursor_Name | QQC181 | Name of the cursor corresponding to this SQL statement, if applicable |
Statement_Name | QQC182 | Name of statement for SQL statement, if applicable |
Start_Timestamp | QQSTIM | Time this statement entered Note: For a plan cache snapshot, this is the timestamp of the last run of the plan. |
Statement_Text | QQ1000 | First 1000 bytes of statement text |
Statement_Outcome | QQC14 | Statement outcome
|
Result_Rows | QQI2 | Number of result rows returned. Will
only be set for the following SQL operations and is 0 for all others:
|
Dynamic_Replan_Reason_Code | QQC22 | Dynamic replan (access plan rebuilt)
|
Dynamic_Replan_Reason_Code (continued) | QQC22 |
|
Data_Conversion_Reason_Code | QQC16 | Data conversion
|
Data_Conversion_Reason_Code (continued) |
|
|
Total_Time_Milliseconds | QQI4 | Total time for this statement, in
milliseconds. For fetches, the time includes all fetches for this
OPEN of the cursor. Note: When monitor files are created when using an SQL Plan Cache snapshot, this time represents the aggregate time for all runs of this query. This time can be divided by the total number of runs, COALESCE(QVP15F,1), to determine an average time for a given run of the query. |
Rows_Fetched | QQI3 | Total rows fetched for cursor Note: When monitor files are created when using an SQL Plan Cache snapshot, this count represents the aggregate count for all runs of this query. This count can be divided by the total number of runs, COALESCE(QVP15F,1), to determine the average rows fetched for a given query run |
End_Timestamp | QQETIM | Time SQL request completed |
Total_Time_Microseconds | QQI6 | Total time for this statement, in
microseconds. For fetches, this time includes all fetches for this
OPEN of the cursor. Note: When monitor files are created when using an SQL Plan Cache snapshot, this time represents the aggregate time for all runs of this query. This time can be divided by the total number of runs, COALESCE(QVP15F,1), to determine an average time for a given run of the query. |
SQL_Statement_Length | QQI7 | Length of SQL Statement |
Insert_Unique_Count | QQI1 | If the operation (QQC21) indicates INSERT (IN), this field contains the unique query count for the QDT associated with the INSERT. QQUCNT contains the unique query count for the QDT associated with the WHERE part of the statement. If the operation (QQC21) indicates DELETE (DL) or TRUNCATE (TT), this field contains the fast delete reason code. Possible values
if the operation is a DELETE or TRUNCATE are :
All other values if the operation is a
DELETE or TRUNCATE indicate the reason the database was unable to
implement the request using fast delete. Fast delete attempt denied
values:
|
SQLCode | QQI8 | SQL return code |
SQLState | QQC81 | SQLSTATE |
Close_Cursor_Mode | QVC101 | Close Cursor. Possible values are:
|
Allow_Copy_Data_Value | QVC11 | ALWCPYDTA setting (Y/N/O)
|
PseudoOpen | QVC12 | Pseudo Open (Y/N) for SQL operations
that can trigger opens.
|
PseudoClose | QVC13 | Pseudo Close (Y/N) for SQL operations
that can trigger a close.
|
ODP_Implementation | QVC14 | ODP implementation
|
Dynamic_Replan_SubCode | QVC21 | Dynamic replan, subtype reason code |
Commitment_Control_Level | QVC41 | Commitment control level. Possible
values are:
|
Concurrent_Access_Resolution | QWC1B | Indicates what method of concurrent access resolution
was specified.
|
Blocking_Type | QVC15 | Type of blocking. Possible values
are:
|
Delay_Prepare | QVC16 | Delay prepare of statement (Y/N). |
Explainable | QVC1C | The SQL statement is explainable (Y/N). |
Naming_Convention | QVC17 | Naming convention. Possible values:
|
Dynamic_Processing_Type | QVC18 | Type of dynamic processing.
|
LOB_Data_Optimized | QVC19 | Optimize LOB data types (Y/N) |
Program_User_Profile_Used | QVC1A | User profile used when compiled programs
are executed. Possible values are:
|
Dynamic_User_Profile_Used | QVC1B | User profile used for dynamic SQL
statements.
|
Default_Collection | QVC1281 | Name of the default collection. |
Procedure_Name | QVC1282 | Procedure name on CALL to SQL. |
Procedure_Library | QVC1283 | Procedure library on CALL to SQL. |
SQL_Path | QQCLOB2 | Path used to find procedures, functions, and user-defined types for static SQL statements. |
Current_Schema | QVC1284 | SQL current schema. |
Binding_Type | QQC18 | Binding type:
|
Cursor_Type | QQC61 | Cursor Type:
|
Statement_Originator | QVC1D | SQL statement originator:
|
Hard_Close_Reason_Code | QQC15 | SQL cursor hard close reason. Possible reasons
are:
|
Hard_Close_Subcode | QQC23 | SQL cursor hard close reason subcode. For QQC15 Reason code ‘A’ the following subcodes apply:
|
Date_Format | QVC42 | Date Format. Possible values are:
|
Date_Separator | QWC11 | Date Separator. Possible values are:
|
Time_Format | QVC43 | Time Format. Possible values are:
|
Time_Separator | QWC12 | Time Separator. Possible values are:
|
Decimal_Point | QWC13 | Decimal Point. Possible values are:
|
Sort_Sequence_Table | QVC104 | Sort Sequence Table |
Sort_Sequence_Library | QVC105 | Sort Sequence Library |
Language_ID | QVC44 | Language ID |
Country_ID | QVC23 | Country ID |
First_N_Rows_Value | QQIA | Value specified on the FIRST n ROWS clause. |
Optimize_For_N_Rows _Value | QQF1 | Value specified on the OPTIMIZE FOR n ROWS clause. |
SQL_Access_Plan_Reason_Code | QVC22 | SQL access plan rebuild reason code. Possible
reasons are:
|
SQL_Access_Plan_Reason_Code (continued) |
|
|
Access_Plan_Not_Saved_Reason_Code | QVC24 | Access plan not saved reason code. Possible
reasons are:
|
Transaction_Context_ID | QVC81 | Transaction context ID. |
Activation_Group_Mark | QVP152 | Activation Group Mark |
Open_Cursor_Threshold | QVP153 | Open cursor threshold |
Open_Cursor_Close_Count | QVP154 | Open cursor close count |
Commitment_Control_Lock_Limit | QVP155 | Commitment control lock limit |
Allow_SQL_Mixed_Constants | QWC15 | Using SQL mixed constants (Y/N) |
Suppress_SQL_Warnings | QWC16 | Suppress SQL warning messages (Y/N) |
Translate_ASCII | QWC17 | Translate ASCII to job (Y/N) |
System_Wide_Statement_Cache | QWC18 | Using system-wide SQL statement cache (Y/N) |
LOB_Locator_Threshold | QVP159 | LOB locator threshold |
Max_Decimal_Precision | QVP156 | Maximum decimal precision (63/31) |
Max_Decimal_Scale | QVP157 | Maximum decimal scale |
Min_Decimal_Divide_Scale | QVP158 | Minimum decimal divide scale |
Unicode_Normalization | QWC19 | Unicode data normalization requested (Y/N) |
Statement_Text_Long | QQ1000L | Complete statement text |
Old_Access_Plan_Length | QVP15B | Length of old access plan |
New_Access_Plan_Length | QVP15C | Length of new access plan |
Fast_Delete_Count | QVP151 | SQL fast delete count. Possible values are:
|
Statement_Max_Compression | QQF2 | SQL statement maximum compression. Possible
values are:
|
Current_User_Profile | QVC102 | Current user profile name |
Expression_Evaluator_Used | QVC1E |
|
Host_Server_Delta | QVP15A | Time not spent within Host Server |
NTS_Lock_Space_Id | QQC301 | NTS Lock Space Identifier |
IP_Address | QQC183 | IP Address |
IP_Type | QFC11 | IP address type
|
IP_Port_Number | QQSMINT2 | IP Port Number |
NTS_Transaction_Id | QVC3004 | NTS Transaction Identifier |
NTS_Format_Id_Length | QQSMINT3 | NTS Format Identified length |
NTS_Transaction_ID_SubLength | QQSMINT4 | NTS Transaction Identifier sublength. |
Unique_Refresh_Counter2 | QVRCNT | Unique refresh counter |
Times_Run | QVP15F | Number of times this Statement was run. If
Null, then the statement was run once. Note: While using an SQL Plan Cache snapshot, this value can be set by the database monitor. This value might be null if the query never completed, or was running when the snapshot was created. If there is not a plan cache snapshot, the value is null. |
Full_Opens | QVP15E | Number of runs that were processed as full opens.
If Null, then the refresh count (qvrcnt) is used to determine if
the open was a full open (0) or a pseudo open (>0). Note: While using an SQL Plan Cache snapshot, this value can be set by the database monitor. This value might be null if the query never completed, or was running when the snapshot was created. If there is not a plan cache snapshot, the value is null. |
Proc_In_Cache | QVC1F | Procedure definition was found in an internal cache. (Y/N) Only applicable for CALL statements. |
Combined_Operation | QWC1A | Statement was performed with the processing for another statement. (Y/N) Only applicable for OPEN, FETCH, and CLOSE statements. |
Client_Applname | QVC3001 | Client Special Register - application name |
Client_Userid | QVC3002 | Client Special Register - userid |
Client_Wrkstnname | QVC3003 | Client Special Register - work station name |
Client_Acctng | QVC3005 | Client Special Register - accounting string |
Client_Programid | QVC3006 | Client Special Register - program name |
Interface_Information | QVC5001 | Part of the CLIENT special register information.
Three types of info are stored in this char500 column, separated
by colons.
|
Open_Options | QVC82 | Open options appear as a combination of the
following characters, representing the actual capability for the cursor.
The character values are left-aligned and padded on the right with
blanks. Example 'RU ' indicate that the cursor is both read and
update capable.
|
Extended_Indicators | QWC1D | An Update or Insert statement was enabled to use extended indicators (Y/N). |
DECFLOAT_Rounding_Mode | QWC1C | Rounding mode to use for DECFLOAT computations
and conversions.
|
SQL_DECFLOAT_Warnings | QWC1E | DECFLOAT computations and conversions involving division by 0, overflow, underflow, an invalid operand, an inexact result, or a subnormal number results in a warning (Y/N). |
Worst_Time_Micro | QVP15D | If not null, this time is the time for the slowest
single run of this query. Note: When monitor files are created when using an SQL Plan Cache snapshot, this time represents the run time for the longest single run of the query. If the value is null, then the longest run information is not available. In that case, QQI6 might be the next best answer. See documentation for QQI6 for the proper use of that field |
SQ_Unique_Count | QQINT05 | A unique count used to uniquely identify statements which do not have an ODP but do pass in host variables. If QQUCNT is 0 and the statement passes in host variables, this value is non-zero. An example would be a CALL statement. |
Concurrent_Access_Res_Used | QFC13 | Specifies what method of concurrent access resolution
was used.
|
SQL_UDFs_Not_Inlined | QQSMINT8 | Specifies the number of SQL user-defined scalar functions (UDFs) or user-defined table functions (UDTFs) that were not inlined in a SQL query or expression. |
Result_Set_Cursor | QVC3007 | Result Set Cursor name. Set by Allocate Cursor, Fetch, and Close. |
Implicit_XMLPARSE_Option | QFC12 | CURRENT IMPLICIT XMLPARSE OPTION special register.
This option is used to specify white-space handling for an implicit
parse of serialized XML data.
|
SQL_XML_Data_CCSID | QQSMINT7 | The CCSID used for XML columns, host variables, parameter markers, and expressions if not explicitly specified. |
OPTIMIZER_USE | QQSMINT5 | Which optimizer was used for the query. Set
to null if the monitor predates this option.
|
XML_Schema_In_Cache | QFC14 | The XML schema binary used during XMLVALIDATE
or decomposition was found in the XML cache.
|
Current_User | QQC105 | The value of the CURRENT USER special register. The value only appears in the QQC105 column if the SQL statement used CURRENT USER. |
Row_Column_Access_Control | QFC15 | Type of row or column access applied.
|
Temporal_System_Time | QQTIM12A | The value of the CURRENT TEMPORAL SYSTEM_TIME special register. This TIMESTAMP(12) value is used as a default FOR SYSTEM_TIME period specification when a system-period temporal table is referenced in either static or dynamic SQL statements. This value will only be set when the SQL SYSTIME bind option is set to YES and the CURRENT TEMPORAL SYSTEM_TIME was applied to a system-period temporal table or when CURRENT TEMPORAL SYSTEM_TIME is explicitly used. |
SYSTIME_Bind_Option | QFC16 | References to system-period temporal tables
in both static or dynamic SQL statements are impacted by the value
of the CURRENT TEMPORAL SYSTEM_TIME special register.
|
Temporal_System_Time_Query | QFC17 | A system time period specification was included
on a system-period temporal table reference causing both current and
historical rows to be queried.
|
DBCLOB_HOSTVR | QQDBCLOB1 | Host variables values in a DBCLOB CCSID 1200 field (max 1MB). Only set if HOSTVAR(*CONDENSED) is used on STRDBMON, otherwise null. |
StmtCmpReuseMin | QQSMINT6 | SQL statement compression reuse minimum. This only applies to process extended dynamic *SQLPKGs. The minimum number of reuses that a statement must have in order for its access plan information to remain in the package across a compress of the package. Possible values are:
|
Override_Generated_Values | QFC18 | Override system generated values.
|