Qualification fields for READS requests

Monitor programs can use the qualification area of READS requests to specify constraints on the data that is to be returned by the request.

Begin program-specific programming interface information.

If the qualification area does not exist (length of binary zero), information is obtained from all active allied threads and database access threads. Information is not obtained for any inactive database access threads that might exist.

Certain trace records cannot be qualified. Any qualifications are ignored for the following for the trace records that have the following IFCIDs: 1, 2, 106, 202, 217, 225, 230.

All other synchronous records can be qualified. However, certain trace records can use only certain qualifications fields. For information about the qualifications that particular trace records can use, see Table 4.

Qualification area fields for READS requests

Unless the qualification area has a length of binary zero (in which case the area does not exist), the address of the qualification area supplied by the monitor program points to an area that is formatted by the monitor program, as shown in the following table.

Tip: Start of changeIFCID numbers are often presented in Db2 and its documentation with three or four digits and leading zeros. For example, you might find: "IFCID 1," "IFCID 001," or "IFCID 0001." However, you can assume that these references each have the same meaning.End of change
Table 1. Qualification area fields . This area is mapped by the assembler mapping macro DSNDWQAL.
Name Hex offset Data type Description
WQALLEN 0 Signed two-byte integer Length of the qualification area, plus 4. The following constants set the qualification area length field:
  • WQALLN11 (1024 bytes)
  • WQALLN9 (920 bytes)
  • WQALLN6 (264 bytes)
  • WQALLN5 (192 bytes)
  • WQALLN4 (168 bytes)
  • WQALLN21 (156 bytes)
  • WQALLN22 (120 bytes)
  • WQALLN23 (78 bytes)

For information about the qualification fields that particular trace records can use, see Table 4.

For more information about the locations of qualification fields in the qualification area, see member DSNDWQAL of the prefix.SDSNMACS data set.
  2 Signed two-byte integer Reserved.
WQALEYE 4 Character, 4 bytes Eye catcher for block, WQAL.
WQALACE 8 Address Thread identification token value. This value indicates the specific thread wanted; binary zero if it is not to be used.
WQALAIT2 C Address Reserved.
WQALPLAN 10 Character, 8 bytes Plan name; binary zero if it is not to be used.
WQALAUTH 18 Character, 8 bytes The current primary authorization ID; binary zero if it is not to be used.
WQALOPID 20 Character, 8 bytes The original authorization ID; binary zero if it is not to be used.
WQALCONN 28 Character, 8 bytes Connection name; binary zero if it is not to be used.
WQALCORR 30 Character, 12 bytes Correlation ID; binary zero if it is not to be used.
WQALREST 3C Character, 32 bytes Resource token for a specific lock request when IFCID 149 is specified. The field must be set by the monitor program. The monitor program can obtain the information from a previous READS request for IFCID 150 or from a READS request for IFCID 147 or 0148.

Start of changeWhen you specify a resource token in this field, you must also specify a resource hash value in WQALHASH.End of change
WQALHASH 5C Hex, 4 bytes Resource hash value that specifies the resource token for a specific lock request when IFCID 149 is specified. The field must be set by the monitor program. The monitor program can obtain the information from a previous READS request for IFCID 150 or possibly from a READS request for IFCID 147 or 0148.

Start of changeWhen you specify a resource hash value in this field, you must also specify a resource token in WQALREST.End of change
WQALASID 60 Hex, 2 bytes ASID that specifies the address space of the process.
WQALFOPT 62 Hex, 1 byte Filtering options for IFCID 150:
X'80'
Return lock information for resources that have local or global waiters. (WQALLCON)
X'40'
Return lock information only for resources that have one or more interested agents. (WQALMAGN)
X'20'
Return lock information only for resources that have waiters. (WQALWAIT)
WQALFLGS 63 Hex, 1 byte Options for 147/148 records:
X'40'
Active allied agent 147/148 records are not written for this READS request. DDF/RRSAF rollup records are written if WQAL148NR = OFF. (WQAL148NA)
X'80'
DDF/RRSAF rollup 147/148 records are not written for this READS request. Active allied agent records are written if WQAL148NA = OFF. 1 (WQAL148NR)
WQALLUWI 64 Character, 24 bytes LUWID (logical unit of work ID) of the thread wanted; binary zero if it is not to be used
  7C Character, 16 bytes Location name. If specified, data is returned only for distributed agents that originate at the specified location.

Example: If site A is located where the IFI program is running and SITE A is specified in the WQALLOCN, database access threads and distributed allied agents that execute at SITE A are reported. Local non-distributed agents are not reported.

Example: If site B is specified in the WQALLOCN and the IFI program is still executing at site A, information on database access threads that execute in support of a distributed allied agent at site B are reported.

Example: If WQALLOCN is not specified, information on all threads that execute at SITE A (the site where the IFI program executes) is returned. This includes local non-distributed threads, local database access agents, and local distributed allied agents.
WQALLTYP 8C Character, 3 bytes Specifies the type of log data access. 'CI ' must be specified to obtain log record control intervals (CIs).
WQALLMOD 8F Character, 1 byte The mode of log data access:
'D'
Retrieves the single log record whose RBA value and member id is specified in WQALLRBA. Issuing a D request while holding a position in the log, causes the request to fail and terminates the log position held.
'F'
Used as a first call to request log records beyond the LRSN or RBA specified in WQALLRBA that meet the criteria specified in WQALLCRI.
'H'
Retrieves the highest LRSN or log RBA in the active log. The value is returned in field IFCAHLRS of the IFI communications area (IFCA). There is no data returned in the return area and the return code for this call will indicate that no data was returned.
'N'
Used following mode F or N calls to request any remaining log records that meet the criteria specified in WQALLCRI. * and any option specified in WQALLOPT. As many log records as fit in the program's return area are returned.
'T'
Terminates the log position that was held by any previous F or N request. This allows held resources to be released.
WQALLNUM 90 Hex, 2 bytes The number of log CIs to be returned. The valid range is X'0001' to X'0007'.
WQALCDCD 92 Character, 1 byte Data description request flag:
'A'
Indicates that a data description can only be returned the first time a DATA request is issued from the region or when it was changed for a given table. This is the default.
'N'
Indicates that a data description is not returned.
'Y'
Indicates that a data description is returned for each table in the list for every new request.
  93 Hex, 1 byte Reserved.
  94 Hex, 8 bytes Reserved.
WQALGBPN 9C Character, 8 bytes Group buffer pool name for IFCID 254. Buffer pool name for IFCID 199. To specify a single buffer pool or group buffer pool, specify the buffer pool name in hexadecimal, followed by hexadecimal blanks.

Example: To specify buffer pool BP1, put X'C2D7F14040404040' in this field.

To specify more than one buffer pool or group buffer pool, use the pattern-matching character X'00' in any position in the buffer pool name. X'00' indicates that any character can appear in that position, and in all positions that follow.

Example: If you put X'C2D7F10000000000' in this field, you indicate that you want data for all buffer pools whose names begin with BP1, so IFI collects data for BP1, BP10 through BP19, and BP16K0 through BP16K9.

Example: If you put X'C2D700F100000000' in this field, you indicate that you want data for all buffer pools whose names begin with BP, so IFI collects data for all buffer pools. IFI ignores X'F1' in position four because it occurs after the first X'00'.
WQALLCRI A4 Hex, 1 byte Log Record Selection Criteria:

Indicates what types of log records are returned:

X'00' (WQALLCR0)
Only log records for changed data capture and unit of recovery control.
X'01' (WQALLCR1)
Only log records for changed data capture and unit of recovery control from the proxy data sharing group in a GDPS® Continuous Availability with zero data loss environment. Records are returned until the end-of-scope log point is reached.
X'02' (WQALLCR2)
All types of log records from the proxy data sharing group in a GDPS Continuous Availability with zero data loss environment. Records are returned until the end-of-scope log point is reached.
X'03' (WQALLCR3)
Only log records for changed data capture and unit of recovery control from the proxy data sharing group in a GDPS Continuous Availability with zero data loss environment. Records are returned until the end-of-log point is reached for all members of the data sharing group.
X'04' (WQALLCR4)
All types of log records from the proxy data sharing group in a GDPS Continuous Availability with zero data loss environment. Records are returned until the end-of-log point is reached for all members of the data sharing group.
X'FF' (WQALLCRA)
All types of log records. Use of this option can retrieve large data volumes and degrade Db2 performance.
WQALLOPT A5 Hex, 1 byte Processing options relating to decompression and data conversion:
X'00'
Indicates that decompression should not occur. (WQALLOP0)
X'01'
Indicates to decompress the log records if they are compressed. (WQALLOP1)
Start of changeX'02'End of change
Start of changeTells Db2 to convert the log records to the format for the table space version that was current when the data was written. (WQALLOP2)End of change
Start of changeX'03'End of change
Start of changeTells Db2 to decompress the log records, and to convert the log records to the format for the table space version that was current when the data was written. (WQALLOP3)End of change
WQALFLTR A6 Hex, 1 byte For IFCID 316 requests, WQALFLTR identifies the filter method:
X'00'
Indicates no filtering. This value tells Db2 to return information for as many cached statements as fit in the return area. (WQALFLT0)
X'01'
Start of changeIndicates that Db2 returns information about the cached statements that have the highest values for a particular statistics field. The statistics field is specified in WQALFFLD. Db2 returns information for as many statements as fit in the return area. (WQALFLT1)

Example: If the return area is large enough for information about 10 statements, the statements with the ten highest values for the specified statistics field are reported.

Start of changeFor an IFCID 316 request, if the QWALFLTR value is X'01' and the QWALFFLD value is 'C' (WQALFFLC), the values are returned in descending order of CPU time.End of change

End of change
X'02'
Indicates that Db2 returns information about the cached statements that exceed a threshold value for a particular statistics field. The name of the statistics field is specified in WQALFFLD. The threshold value is specified in WQALFVAL. Db2 returns information for as many qualifying statements as fit in the return area.
X'04'
Indicates that Db2 returns information about a single cached statement. The application provides the four-byte cached statement identifier in field WQALSTID. An IFCID 316 request with this qualifier is intended for use with IFCID 172 or IFCID 196, to obtain information about the statements that are involved in a timeout or deadlock.
WQALFLTR (continued)    
For IFCID 401 requests:
X'00'
Indicates no filtering. This value tells Db2 to return information for as many statements as fit in the return area. (WQALFLT0)
X'02'
Indicates that Db2 returns information about the statements that exceed a threshold value for a particular statistics field. The name of the statistics field is specified in WQALFFLD. The threshold value is specified in WQALFVAL. Db2 returns information for as many qualifying statements as fit in the return area.

For IFCID 317 requests, WQALFLTR identifies the filter method:

X'04'
Indicates that Db2 returns information about a single cached statement. The application provides the four-byte cached statement identifier in field WQALSTID. An IFCID 317 request with this qualifier is intended for use with IFCID 172 or IFCID 196, to obtain information about the statements that are involved in a timeout or deadlock.

For IFCID 306 requests, WQALFLTR indicates whether Db2 merges log records in a data sharing environment:

X'00'
Indicates that Db2 merges log records from data sharing members. (WQALMERG)
X'03'
Indicates that Db2 does not merge log records from data sharing members. (WQALNOMR)
WQALFFLD A7 Character, 1 byte For an IFCID 316 request, when WQALFLTR is X'01' or X'02', this field specifies the statistics field that is used to determine the cached statements about which Db2 reports. You can enter the following values:
'A'
The accumulated elapsed time (QW0316AE). This option is valid only when QWALFLTR=X'01'. (WQALFFLA)
'B'
The number of buffer reads (QW0316NB). (WQALFFLB)
'C'
The accumulated CPU time (QW0316CT). This option is valid only when QWALFLTR=X'01'. (WQALFFLC)
'E'
The number of executions of the statement (QW0316NE). (WQALFFLE)
'G'
The number of GETPAGE requests (QW0316NG). (WQALFFG)
'I'
The number of index scans (QW0316NI). (WQALFFLI)
'L'
The number of parallel groups (QW0316NL). (WQALFFLL)
'P'
The number of rows processed (QW0316NP). (WQALFFLP)
'R'
The number of rows examined (QW0316NR). (WQALFFLR)
'S'
The number of sorts performed (QW0316NS). (WQALFFLS)
'T'
The number of table space scans (QW0316NT). (WQALFFLT)

(Continued in the following row.)
WQALFFLD
(continued)
 A7 Character, 1 byte

(Continued from the previous row.)

'W'
The number of buffer writes (QW0316NW). (WQALFFLW)
'X'
The number of times that a RID list was not used because the number of RIDs would have exceeded one or more internal Db2 limits (QW0316RT).
'Y'
The number of times that a RID list was not used because not enough storage was available (QW0316RS). (WQALFFLY)
'1'
The accumulated wait time for synchronous I/O (QW0316W1). This option is valid only when QWALFLTR=X'01'. (WQALFFL1)
'2'
The accumulated wait time for lock requests (QW0316W2). This option is valid only when QWALFLTR=X'01'. (WQALFFL2).
'3'
The accumulated wait time for a synchronous execution unit switch (QW0316W3). This option is valid only when QWALFLTR=X'01'. (WQALFFL3)
'4'
The accumulated wait time for global locks (QW0316W4). This option is valid only when QWALFLTR=X'01'.(WQALFFL4)
'5'
The accumulated wait time for read activity by another thread (QW0316W5). This option is valid only when QWALFLTR=X'01'. (WQALFFL5)
'6'
The accumulated wait time for write activity by another thread (QW0316W6). This option is valid only when QWALFLTR=X'01'. (WQALFFL6)
'7'
The accumulated wait time for lock requests (QW0316W7). This option is valid only when QWALFLTR=X'01'. (WQALFFL7).

(Continued in the following row.)
WQALFFLD
(continued)
 A7 Character, 1 byte

(Continued from the previous row.)

'8'
The update timestamp when statistics were last changed (QW0316W8). This option is valid only when QWALFLTR=X'01'. (WQALFFL8).
'9'
The insertion timestamp when statements first enter the dynamic statement cache or EDM pool (QW0316W9). QWALFLTR=X'01'. (WQALFFL9)
WQALFVAL A8 Signed 4-byte integer For an IFCID 316 request, when WQALFLTR is X'02', this field and WQALFFLD determine the cached statements about which Db2 reports.

To be eligible for reporting, a cached statement must have a value for WQALFFLD that is no smaller than the value that you specify in WQALFVAL. Db2 reports information on as many eligible statements as fit in the return area.
WQALSTNM AC Character, 16 bytes For an IFCID 317 request, when WQALFLTR is not X'04', this field specifies the name of a cached statement about which Db2 reports. This is a name that Db2 generates when it caches the statement. To obtain this name, issue a READS request for IFCID 316. The name is in field QW0316NM. This field and WQALSTID uniquely identify a cached statement.
WQALSTID BC Unsigned 4-byte integer For an IFCID 316 or IFCID 317 request, this field specifies the ID of a cached statement about which Db2 reports. Db2 generates this ID when it caches the statement.
To obtain the ID, use the following options:
  • For an IFCID 317 request, when WQALFLTR is not X'04', obtain this ID by issuing a READS request for IFCID 316. The ID is in field QW0316TK. This field and WQALSTNM uniquely identify a cached statement.
  • For an IFCID 316 or IFCID 317 request, when WQALFLTR is X'04', obtain this ID by issuing a READS request for IFCID 172 or IFCID 196. The ID is in field QW0172H9 (cached statement ID for the holder in a deadlock), QW0172W9 (cached statement ID for the waiter in a deadlock), or QW0196H9 (cached statement ID of the holder in a timeout). This field uniquely identifies a cached statement.
WQALEUID C0 Character, 16 bytes The first 16 bytes of the user's workstation user ID. This value can be different from the authorization ID that is used to connect to Db2.
WQALEUTX D0 Character, 32 bytes The first 32 bytes of the name of the transaction or application that the user is running. This value identifies the application that is running, not the product that is used to run the application.
WQALEUWN F0 Character, 18 bytes The first 18 bytes of the user's workstation name. This value can be different from the authorization ID used to connect to Db2.
WQALPLOC 102 Character, 128 bytes The package location name.
WQALPCOL 182 Character, 128 bytes The collection name of the package that the user is running.
WQALPPNM 202 Character, 128 bytes The program name of the package that the user is running.
WQALROLE 282 Character, 128 bytes The connection role of the user. This field contains binary zeros if the client does not supply this information.
WQALRTBL 302 Character, 128 Bytes The name of the referenced table.
WQALEXCD 382 Character, 4 bytes This field specifies the level of exclude filtering to be performed, according to the following values:
X'80000000'
Specifies exclude filtering by plan name (WQALPLAN)
X'40000000'
Specifies exclude filtering by current authorization ID (WQALAUTH)
X'20000000'
Specifies exclude filtering by original authorization ID (WQALOPID)
X'10000000'
Specifies exclude filtering by connection name (WQALCONN)
X'08000000'
Specifies exclude filtering by correlation name (WQALCORR)
X'04000000'
Specifies exclude filtering by cached statement ID (WQALASID)
X'02000000'
Specifies exclude filtering by location name (WQALLUWI)
X'01000000'
Specifies exclude filtering by location name (WQALLOCN)
X'00800000'
Specifies exclude filtering by workstation ID (WQALEUID)
X'00400000'
Specifies exclude filtering by transaction or application name (WQALEUTX)
X'00200000'
Specifies exclude filtering by workstation name (WQALEUWN)

Continues in the following row.
WQALEXCD (continued)    

Continued from the previous row.

X'00100000'
Specifies exclude filtering by package location name (WQALEPLOC)
X'00080000'
Specifies exclude filtering by package collection name (WQALEPCOL)
X'00040000'
Specifies exclude filtering by package program name (WQALEPPNM)
X'00002000'
Specifies exclude filtering by connection role (WQALEROLE)
  386 Character, 2 bytes Reserved.
WQALFVAL64 388 Signed integer, 8 bytes 64-bit threshold value for IFCID 316 and 0401 requests. If the value is non-zero and the target system is DB2® 10 or higher, this value is used in place of WQALFVAL.
WQALLRBA 390 Character, 12 bytes For IFCID 129, the third through twelfth bytes are the starting log RBA of the CIs to be returned. The two high-order bytes are not used.

For IFCID 306:

  • If WQALLMOD is 'D', the two high-order bytes are the member ID, and the rest of the bytes are the log RBA or LRSN to be used.
  • If WQALLMOD is 'F', the third through twelfth bytes are the log RBA or LRSN to be used. The two high-order bytes are not used.
WQALWQLS 3A0 Address, 4 bytes Reserved for future. Must be 0.
WQALWQL4 3A4 Address, 4 bytes Address of WQLS or 0.
reserved 3A8 Character, 8 bytes Reserved
Notes:
  1. The only valid filters for DDF/RRSAF 147/148 rollup records are WQALEUID, WQALEUTX, and WQALEUWN. For a 147/148 request, DDF/RRSAF records are not processed if any of the following WQAL fields are not X'00':
    • WQALACE
    • WQALAUTH
    • WQALOPID
    • WQALPLAN
    • WQALCORR
    • WQALLUWI
    • WQALLOCN
    • WQALASID
    • WQALCONN
    • WQALPLOC
    • WQALPCOL
    • WQALPPNM
    • WQALROLE

WQLS maps the area used to filter IFCID 306 records. Unless the qualification area has a length of binary zero (in which case the area does not exist), the address of the qualification area supplied by the monitor program points to an area that is formatted by the monitor program, as shown in the following table.

Table 2. Qualification area fields of WQALWQLS. This area is mapped by the assembler mapping macro DSNDWQAL. These fields map the area used to filter IFCID 306 records.
Name Hex offset Data type Description
WQLSLEN 0 Signed integer, 4 bytes Length of WQLS.
WQLSEYE 4 Character, 4 bytes Eye catcher for block, WQLS.
WQLSFLG 8 Hex, 1 byte Flags:
Start of changeX'40' (WQLSALTR)End of change
Start of change
  • 1: Log records with log record subtype code X'0083' (alter or modify log records used for Db2 replication), are not filtered by DBID and PSID. As a result, log records with subtype code X'0083' are returned for all table spaces.
  • 0: Log records with log record subtype code X'0083' (alter or modify log records used for Db2 replication), are filtered by DBID and PSID.
End of change
reserved 9 Character, 47 bytes Reserved
WQLSTYPE 38 Character, 4 bytes Type of qualification items:Start of change
  • 'DBPS': For filtering at the table space level
  • 'DBOB': For filtering at the table level
End of change
WQLSITEM 3C Signed integer, 4 bytes Number of qualification items

The fields that are mapped by WQLSDBPS are located at offset X'40' in WQLS.

Table 3. Qualification area fields of WQLSDBPS. Start of changeThis area is mapped by the assembler mapping macro DSNDWQAL. These fields map the area used to filter IFCID 306 records when WQLSTYPE='DBPS' or WQLSTYPE='DBOB'.End of change
Name Hex offset Data type Description
WQLSDBID 0 Hex, 2 bytes DBID of database.
WQLSPSID 2 Hex, 2 bytes PSID of the table space for filtering at the table space level, or OBID of the table for filtering at the table level.

If your monitor program does not initialize the qualification area, the READS request is denied.

Qualification fields that are used by IFCIDs

The following table lists the qualification fields that can be used for certain IFCIDs that contain synchronous data. For descriptions of these IFCIDs, see Trace fields for READS requests

Table 4. Qualification fields that are used by particular IFCIDs
These IFCIDs Are allowed to use these qualification fields Minimum length (WQALLEN constant) to use all qualifications 3
0001, 0002, 0104, 0106, 0202, 0217, 0225, 0230, 0373 None. All qualifications are ignored. Not applicable.
0124, 0147, 0148, 0150, 0346
  • WQALACE
  • WQALAIT2
  • WQALPLAN1
  • WQALAUTH1
  • WQALOPID1
  • WQALCONN1
  • WQALCORR1
  • WQALASID
  • WQALLUWI1
  • WQALLOCN1
  • WQALEUID
  • WQALEUTX
  • WQALEUWN
  • WQALPLOC
  • WQALPCOL
  • WQALPPNM
  • WQALROLE1
 WQALLN9
0129
  • WQALLTYP
  • WQALLMOD
  • WQALLRBA
  • WQALLNUM
 WQALLN23
0149
  • WQALREST
  • WQALHASH
 WQALLN6
0150
  • WQALFOPT
 WQALLN21
0185
  • WQALCDCD
 WQALLN4
0199, 0254
  • WQALGBPN2
 WQALLN5
0306
  • WQALFLTR
  • WQALLMOD
  • WQALLRBA
  • WQALLCRI
  • WQALLOPT
  • WQALWQLS
 WQALLN11
0316
  • WQALFLTR
  • WQALFFLD
  • WQALFVAL
  • WQALFVAL64
  • WQALSTID

Filtering by the following fields apply, only after all other filters for IFCID 316 are applied:

  • WQALEUID
  • WQALEUTX
  • WQALEUWN
  • WQALRTBL
 WQALLN9
0317
  • WQALFLTR
  • WQALSTNM
  • WQALSTID
 WQALLN5
0401
  • WQALFLTR
  • WQALFFLD
  • WQALFVAL
  • WQALFVAL64
 WQALLN9
Notes:
  1. Db2 allows you to partially qualify a field and fill the rest of the field with binary zero. For example, the 12-byte correlation value for a CICS® thread contains the 4-character CICStransaction code in positions 5-8. Assuming a CICS transaction code of AAAA, the following hexadecimal qual-area correlation qualification can be used to find the first transaction with a correlation value of AAAA in positions 5-8: X'00000000C1C1C1C100000000'.
  2. X'00' in this field indicates a pattern-matching character. X'00' in any position of the field indicates that IFI collects data for buffer pools whose names contain any character in that position and all following positions.
  3. The specified WQALLEN constant represents the minimum qualification area length that is required for the use of all qualifications for the specified IFICIDs. A longer length can also be specified for any IFCID. However, the length must match one of these constants.

End program-specific programming interface information.