FILTERDATA statement and parameters

The FILTERDATA statement specifies filtering criteria to refine the scope of a report. You can specify the fields that you want to filter, and set item value filters to limit the data provided to reports.

The following table describes the parameters that you can specify in a FILTERDATA statement:

Parameter	Description
fieldValue	The value that represents the field on which you want to filter data You can specify values that represent fields for the accounting, statistics, and audit reports, as listed in FILTERDATA: field values and corresponding field names.
GT	Greater than
LT	Less than
GE	Greater than or equal to
LE	Less than or equal to
EQ	Equal
NE	Not equal
nnnnnnnn	Decimal or character qualifier You can specify the following values: A maximum value of 99999999 for numeric qualifiers A maximum of 8 characters Note For certain audit fields, you can specify up to 18 characters. For more information, see FILTERDATA: field values and corresponding field names.
hh:mm:ss	Time qualifier

Consider the following points when using the FILTERDATA keyword:

You can abbreviate FILTERDATA to FD.
You can specify a maximum of four conditions in a statement.

A statement can include multiple conditions, with or without parentheses; however, each condition must be separated by a comma.

Example

FILTERDATA( (ACCTCL1ELAP GT 00:00:01), (ACCTCL2ELAP LT 00:00:10), - 
            (ACCTCL1CPU  GT 00:00:01), (ACCTCL2CPU  LT 00:00:10) )

FILTERDATA( ACCTCL1ELAP GT 00:00:01, ACCTCL2ELAP LT 00:00:10, - 
            ACCTCL1CPU  GT 00:00:01, ACCTCL2CPU  LT 00:00:10 )

You cannot specify the same field value more than once in a statement.
Example
This example causes an error message:
```
FD( ( ACCTCL1ELAP GT 00:00:01 ), ( ACCTCL1ELAP LT 00:00:10 ) )
```
Quotation marks are invalid in the FILTERDATA statement. If you enclose values in quotation marks, no data is returned.
To indicate continuation in a statement with multiple lines, type a hyphen (-) and a space at the end of each line that is followed by a continuation line.
Example
```
FD( ( ACCTCL1ELAP GT 00:00:01 ), ( ACCTCL2ELAP LT 00:00:10 ), - 
  ( ACCTCL1CPU  GT 00:00:00 ), ( ACCTCL2CPU  LT 00:00:10 ) )
```
BMC does not recommend using the FILTERDATA statement when specifying multiple reports concurrently. If you specify detail and summary reports in the same FILTERDATA statement, incorrect results might be displayed.
For certain fields, qualifiers must be specified in a decimal format:
- You can see the required format for qualifiers in the Qualifier format column in FILTERDATA: field values and corresponding field names.
  For the audit reports, some fields in IFCIDs 140, 141, and 145 are accompanied by characters (such as C for collection data). You must convert the hexadecimal value for these characters to a decimal value.
  FILTERDATA: hexadecimal-to-decimal conversions for QW0140OB shows hexadecimal-to-decimal conversions for the audit fields that require decimal values.
- For some fields, only certain qualifier values are valid.
  FILTERDATA: valid qualifiers for QW0140PR and QW0145ST lists the valid values for the Audit Privileges check field (QW0140PR), and the Audit SQL DML statement type field (QW0145ST).
  Note
  
  You can view definitions for the QW0140xx, QW0141xx, and QW0145xx fields in the DSNMACS data set for the specified DB2 version, or the DB2 DSNIVPD(DSNWMSGS) data set.
If you are filtering fields on the BTHAUDIT report, you can limit the data included in the report to only one IFCID by using the IFCID parameter.
For example, for the QW0141AC field, assume that you want to retain only IFCID 141 records that equal 199. You can specify one of the following statements:
```
FILTERDATA( AUDITACCTYPE EQ 199 )
REPORT(NAME(BTHAUDIT) EXPAND IFCID(141) )
```
```
FILTERDATA( AUDITACCTYPE EQ 199 )
REPORT(NAME(BTHAUDIT) EXPAND RECTRACE IFCID(141) )
```

The following table lists valid field names for the FILTERDATA statement, the corresponding field values, and the required format for qualifiers if specified with the field value.

FILTERDATA: field values and corresponding field names

Report type	Field name	Corresponding field value	Qualifier format
Accounting reports that use IFCID 3: BACCTACM BACCTDR BACCTLT BACCTSR BACCTSRD BACCTSRI BACCTSRP BACCTSRX BACCTST Note BACCTST does not support the AcctNumThreads, AcctNumDbats, and AcctNumRollbks field values.	QWACC1EL Class 1 elapsed time	AcctCL1Elap	Time
	QWACC1CP Class 1 CPU time	AcctCL1CPU	Time
	QWACC2EL Class 2 elapsed time	AcctCL2Elap	Time
	QWACC2CP Class 2 CPU time	AcctCL2CPU	Time
	Q8ACAELA (PTFs BPD4868 and BQU2326 applied) Accelerator elapsed time Note This field value is available only for the BACCTLT report.	AcctAccelElap	Time
	Q8ACACPU (PTFs BPD4868 and BQU2326 applied) Accelerator CPU time Note This field value is available only for the BACCTLT report.	AcctAccelCPU	Time
	QWACTHED Calculated number of threads	AcctNumThreads	Decimal
	QWACDBAT Calculated number of dbats	AcctNumDbats	Decimal
	QWACCOMM Calculated number of commits	AcctNumCommits	Decimal
	QWACABRT Calculated number of rollbacks	AcctNumRollbks	Decimal
Statistics reports that use IFCID 2: BSTATDR BSTATLT BSTATST	Q3STCTHD Number of threads	StatNumThreads	Decimal
	QDSTCNAT Number of dbats	StatNumDbats	Decimal
	Q3STCOMM Number of commits	StatNumCommits	Decimal
	QXINCRB Number of incremental binds	StatIncrBinds	Decimal
	Total SQL DML	StatTotSQLDML	Decimal
	Total SQL DCL	StatTotSQLDCL	Decimal
	Total SQL DDL	StatTotSQLDDL	Decimal
Audit reports: BAUDTAUT (IFCID 140) BTHAUDIT (IFCIDs 140, 141, and 145)	QW0140PR Audit privileges check	AuditPrivCheck	Decimal ²
	QW0140OB Audit object type	AuditObjType	Decimal ¹
	QW0140SC Audit source object owner	AuditSrcOwn	Character (8)
	QW0140SN Audit source object name	AuditSrcObjn	Character (18)
	QW0140TC Audit target object owner	AuditTgtOwn	Character (8)
	QW0140TN Audit target object name	AuditTgtObjn	Character (18)
	QW0140UR Audit authorization ID checked	AuditAuthidChk	Character (8)
	QW0141AC Audit explicit access type	AuditAccType	Decimal ¹
	QW0141OB Audit explicit object type	AuditObjType2	Decimal ¹
	QW0141RE Audit reason access granted	AuditReasGrant	Decimal ¹
	QW0145ST Audit SQL DML statement type	AuditSQLType	Decimal ²
	QW0145IS Audit SQL DML statement isolation level	AuditSQLStmt	Decimal ¹

^{1 FILTERDATA: hexadecimal-to-decimal conversions for QW0140OB lists the valid values for this field.}

^{2 FILTERDATA: valid qualifiers for QW0140PR and QW0145ST lists the valid values for this field.}

The following table shows hexadecimal-to-decimal conversions for the audit fields that require decimal values.

FILTERDATA: hexadecimal-to-decimal conversions for QW0140OB

Field name	Character	Hex value	Decimal value
QW0140OB/QW0141OB	A (ACEE)	C1	193
	B (bufferpool)	C2	194
	C (collection)	C3	195
	D (database)	C4	196
	E (distinct type)	C5	197
	F (function)	C6	198
	H (session variable)	C8	200
	J (jar)	D1	209
	K (package)	D2	210
	L (role)	D3	211
	M (schema)	D4	212
	N (trusted context)	D5	213
	O (procedure)	D6	214
	P (application plan)	D7	215
	Q (sequence)	D8	216
	R (tablespace)	D9	217
	S (storage group)	E2	226
	T (table or view)	E3	227
	U (user authorization)	E4	228
	W (row)	E6	230
QW0141AC	G (grant)	C7	199
QW0141AC	R (revoke)	D9	217
QW0141RE	A (PACKADM on all collections)	C1	193
	C (DBCTRL)	C3	195
	D (DBADM)	C4	196
	E (SECADM)	C5	197
	G (ACCESSCTRL)	C7	199
	L (SYSCTRL)	D3	211
	M (DBMAINT)	D4	212
	O (SYSOPR)	D6	214
	P (PACKADM on a specific collection ID)	D7	215
	S (SYSADM)	E2	226
QW0145IS	L (RS isolation-level X-lock)	D7	215
	R (RR isolation level)	D9	217
	S (CS isolation level)	E2	226
	T (RS isolation level)	E3	227
	U (UR isolation level)	E4	228
	X (RR isolation-level X-lock)	E7	231

The following table describes the qualifier values that you can specify for the Audit Privileges check field (QW0140PR), and the Audit SQL DML statement type field (QW0145ST).

FILTERDATA: valid qualifiers for QW0140PR and QW0145ST

Field name	Qualifier value	Type of privilege
QW0140PR	09	Display profile
	10	Start profile
	11	Stop profile
	12	Start RLIMIT
	13	Stop RLIMIT
	14	Display RLIMIT
	15	Create alias
QW0140PR (continued)	16	Monitor 1
	17	Monitor 2
	19	Check utility
	20	Drop alias
	21	DDF command—start, stop, or cancel
	50	Select
	51	Insert
	52	Delete
	53	Update
	54	References
	55	Trigger
	56	Create index
	57	DBADM
	58	Terminate utility on database
	60	All on packages
	61	Alter
	62	Display thread or display database
	64	Execute
	65	Bind, rebind, or free
	66	Create DBA
	67	Create STOGROUP
	68	DBCTRL
	69	DBMAINT
	72	Recover INDOUBT
	73	Drop
	74	Copy
	75	Load
	76	Explicit qualifier use
	77	Reorg
	78	Repair
	79	Start database
	80	Start DB2, stop DB2, start DB(), or stop DB()
QW0140PR (continued)	82	RUNSTATS utility
	83	Stop database
	84	Stop or start trace
	85	SYSADM
	86	SYSOPR
	87	Use
	88	Bind ADD
	89	Recover (utility)
	92	Create DBC
	93	Recover BSDS
	94	Create table
	95	Create tablespace
	96	Display utility
	97	Comment on
	98	Lock table
	99	Display database
	102	Create synonym
	103	Alter index
	104	Drop synonym
	105	Drop index
	107	STOSPACE utility
	108	Create view
	109	Term utility
	112	Display bufferpool
	113	Alter bufferpool
	224	SYSCTRL
	225	Copy package
	226	Create IN
	227	Bind agent
	228	ALLPKAUT
QW0140PR (continued)	229	SUBPKAUT
	231	Archive
	233	Describe table
	236	Diagnose utility
	237	MERGECOPY utility
	238	Modify utility
	239	Quiesce utility
	240	Report utility
	241	Repair DBD utility
	242	PACKADM
	243	Set archive
	244	Display archive
	248	Create global temporary table (CREATETMTAB)
	251	Rename table
	252	ALTERIN
	261	CREATEIN
	262	DROPIN
	263	Usage
	265	Start
	266	Stop
	267	Display
	274	Comment on index
	280	VALIDATE SECLABEL
	281	MLS READWRITE
	282	Debug session
	283	Rename index
	284	SECADM
	285	Create secure object
	286	Explain
	287	SYSTEM DBADM
	289	ACCESSCTRL
	290	SQLADM
QW0140PR (continued)	291	Read
	292	Write
	293	Explain monitor
	294	Query tuning
	295	Check data utility
	296	SYSOPR SYSCTRL SYSADM SECADM
	501	Access control drop table exemption
	502	Access control truncate exemption
	503	Access control utility exemption
QW0145ST	3	Open
	4	Fetch
	5	Close
	14	Prepare
	15	Execute
	16	Execute immediate
	17	Describe
	18	Explain
	229	Truncate
	231	Select-query
	232	Insert
	233	Delete
	234	Update
	278	Lock
	308	Create view
	746	Set host variable
	835	Set assignment
	837	Values clause
	867	Refresh
	893	Merge

Examples

The following examples show how to use FILTERDATA statements to filter report results.

This example filters all IFCID 3 threads with Class 1 elapsed time greater than 1 second for the BACCTSR accounting report:

FILTERDATA(AcctCL1Elap GT 00:00:01)
REPORT(BACCTSR)

This example filters only commits greater than 256 for the BSTATLT statistics report:

FD(StatNumCommits GT 256 )
REPORT(BSTATLT)

This example filters only WRITE privileges checked for Object Types of Database for the BAUDTAUT report. The qualifiers values are as follows:

QW0140PR — W (write) = 292
QW0140OB — D (database), hex value=C4, decimal value=196

FILTERDATA((AudPrivCheck EQ 292),(AudObjType EQ 196))
REPORT(BAUDTAUT)

This example filters IFCID 140 (QW0140PR) and IFCID 145 (QW0145ST) if they are present in the input data that is used for the report. The Expand keyword shows hidden groups, if present.

FILTERDATA( (AuditPrivCheck EQ 226), (AuditSQLType EQ 231) )
REPORT(NAME(BTHAUDIT) EXPAND)

Related topic

DOMBRPT1 utility

FILTERDATA statement and parameters

Comments