When you run queries to display information about Db2 objects or authorizations, you can
filter the information that is displayed by using search arguments in certain input fields.
After the query is run, you can also filter the resulting information that is displayed on
the table display panel.
About this task
Depending on when you want to filter the information, use one of the following procedures:
Filtering data before running a query to display object or authorization information
Procedure
On the System Catalog (ADB21)
panel, specify the
filtering criteria at the bottom of the panel.
Use the following guidance:
Wildcard characters: In your search argument, you can specify a
percent sign (%) or an asterisk (*) as a wildcard character. If you use an
asterisk as a wildcard character, Db2 Admin Tool translates it to a
percent sign. The asterisk is displayed as a percent sign when the panel is
re-displayed.
Case:
Lowercase characters in the search argument for Name,
Owner, In DB/Coll,
Grantor, and Grantee are
translated to uppercase characters unless you change the Db2 Admin Tool default setting.
To change the default setting, navigate to the Admin Defaults (ADB2P2)
panel and set the
Capitalize object names field to NO. With this
setting, lowercase characters are not translated to uppercase characters
where Db2 rules
allow the name to contain lowercase letters. Lowercase characters cannot be
translated to uppercase characters in database names, table space names,
plan names, and package names that are not for trigger packages
Example
For example, the following panel shows how you can use a search argument with wildcard characters
in the Name field on the System Catalog (ADB21)
panel to display all the
databases in the Db2 system
catalog with names that contain the characters DSN.
Figure 1. System Catalog (ADB21)
panel - using search
criteria
DB21 min ---------- DD1A System Catalog - Objects ---------------------- 17:34
Option ===>
DB2 System: DD1A
AO - Display Authorization options DB2 SQL ID: ADM001
Object options:
G - Storage groups P - Plans
D - Databases L - Collections
S - Table spaces K - Packages
T - Tables, views, and aliases M - DBRMs
V - Views H - Schemas
A - Aliases E - User defined data types
Y - Synonyms F - Functions
X - Indexes O - Stored procedures
C - Columns J - Triggers
N - Constraints Q - Sequences
DS - Database structures DSP - DS with plans and packages
PDC - DB2 Pending definition changes GV - Global variables
XCU - Index cleanup RS - REST services
Enter standard selection criteria: Settings: LIKE operator; Criteria saved.
Name . . . . %DSN% > Grantor . . . >
Schema . . . > Grantee . . . >
Owner . . . . >
In DB/Coll . > Switch Catalog Copy . . N (N/S/C)
And/or other selection criteria (option xC shows you columns for option x)
Column . . . > Oper . . Value . .
When you press Enter, Db2 Admin Tool generates
an SQL statement that searches the Db2 catalog using an SQL LIKE
operator to qualify the search with the search criteria. The following figure shows
the ISPF table display that Db2 Admin Tool returns. All databases that
meet the search criteria (have a name that contains the characters 'DSN') are
displayed.
Figure 2. Databases (ADB21D) panel - list of
qualifying databases
DB2 Admin ------------------- DD1A Databases --------------------- Row 1 of 25
Command ===> Scroll ===> PAGE
Commands: GRANT MIG DIS STA STO UTIL MOVETB
Line commands:
T - Tables S - Table spaces X - Indexes G - Storage group ICS - IC status
DIS - Display database STA - Start database STO - Stop database A - Auth
? - Show all line commands
Storage Buffer Created Index
Select Name Owner Group Pool DBID By T E BPool I
* * * * * * * * * *
------ -------- -------- -------- -------- ------ -------- - - -------- -
ADBDSN ADB ADBGCH BP1 271 ISTFL2 E BP2 Y
DSNDB04 SYSIBM SYSDEFLT BP1 4 SYSIBM BP2 Y
DSNDB06 SYSIBM 6 SYSIBM E BP0 Y
DSNDB07 DSCGDB2 SYSDEFLT BP1 7 ISTJE W BP2 N
DSNRGFDB DSCGDB2 SYSDEFLT BP1 257 ISTJE E BP2 N
DSNRLST DSCGDB2 SYSDEFLT BP1 256 ISTJE E BP2 N
DSN8D81A DSCGDB2 DSN8G810 BP0 258 ISTJE E BP2 N
DSN8D81E DSCGDB2 DSN8G810 BP1 260 ISTJE U BP2 N
DSN8D81P DSCGDB2 DSN8G810 BP0 259 ISTJE E BP2 N
DSN8D81U DSCGDB2 DSN8G81U BP1 261 ISTJE E BP2 N
GRGDSN01 DPGROTH SYSDEFLT BP1 272 DPGROTH E BP2 N
GRGDSN02 DPGROTH SYSDEFLT BP1 273 DPGROTH E BP2 N
******************************* END OF DB2 DATA *******************************
Filtering data on a panel after the query result is returned
After Db2 Admin Tool returns the results
of a query, you can filter the rows that are displayed on a table display
panel.
Procedure
Complete one of the following actions:
Use the search argument area on the panel to specify the filtering criteria.
For example, if you want to filter the following panel to see only those table authorizations
that are granted to PUBLIC, you can type PUBLIC in the search argument area in the
Grantee column:
Figure 3. Example of using the search argument area on a table display
panel
ADB2AT in DD1A Table Authorizations Row 1 from 1000
Commands: REVOKE GRANT RMIMPL
Line commands:
R - Revoke GR - Grant T - Table I - Interpretation U D I S U R U
CA - Column authorizations RE - Grantee role P A E I N E P R E N
? - Show all line commands D L L N S L D E F T L
C T E D E E A F C R O
G H Date O E T E R C T E O I A
S Grantor Grantee T Schema Name G Grant L R E X T T E R L G D
* PUBLIC * * * * * * * * * * * * * * * *
-- -------- -------- - -------- --------------> - ------ - - - - - - - - - - -
SYSADM PUBLIC SYSIBM SYSDUMMYE S 100809 Y
SYSADM PUBLIC SYSIBM SYSDUMMYA S 100809 Y
SYSADM PUBLIC SYSIBM SYSDUMMYU S 100809 Y
SYSADM PUBLIC SYSIBM SYSDUMMY1 S 030921 Y
SYSADM PRCAL504 P QUAG5042 TBAL5042 S 200824 Y
RSTEST RSTEST RSTEST TBH_35 201005 G G G G G G G G G
SYSIBM SYSIBM SYSIBM DSNRLST01 S 160314 G G G G G G G G G
DSNRGCOL DSNRGCOL DSNRGCOL DSN_REGISTER_AP S 160314 G G G G G G G G G
DSNRGCOL DSNRGCOL DSNRGCOL DSN_REGISTER_OB S 160314 G G G G G G G G G
When you press Enter, only the authorizations granted to PUBLIC are displayed:
Figure 4. Filtered table display
panel
ADB2AT in DD1A Table Authorizations Row 1 from 1000
Commands: REVOKE GRANT RMIMPL
Line commands:
R - Revoke GR - Grant T - Table I - Interpretation U D I S U R U
CA - Column authorizations RE - Grantee role P A E I N E P R E N
? - Show all line commands D L L N S L D E F T L
C T E D E E A F C R O
G H Date O E T E R C T E O I A
S Grantor Grantee T Schema Name G Grant L R E X T T E R L G D
* PUBLIC* * * * * * * * * * * * * * * * *
-- -------- -------- - -------- --------------- - ------ - - - - - - - - - - -
SYSADM PUBLIC SYSIBM SYSDUMMYE S 100809 Y
SYSADM PUBLIC SYSIBM SYSDUMMYA S 100809 Y
SYSADM PUBLIC SYSIBM SYSDUMMYU S 100809 Y
SYSADM PUBLIC SYSIBM SYSDUMMY1 S 030921 Y
TS5513 PUBLIC TS5513 ADBCHG 200820 Y
TS5513 PUBLIC TS5513 ADBCHGS 200820 Y
TS5513 PUBLIC TS5513 ADBCHGSR 200820 Y
TS5513 PUBLIC TS5513 ADBCPREREQ 200820 Y
TS5513 PUBLIC TS5513 ADBCMASK 200820 Y
To clear the filter, specify a blank in the search argument area, and press Enter.
For detailed information about the search argument area and valid criteria that you can specify,
see D Search arguments.
Tip:Use the CAPS primary
command to toggle between using mixed-case and upper-case searching. This
command temporarily overrides the Capitalize object
names setting on the Admin Defaults (ADB2P2)
panel. For more
information about CAPS, see CAPS.
Use the SEARCH (or SARG) command to perform advanced filtering.
For example, if you want to filter the GT (Grantee type) column on this
same panel to see only blank values, you can specify the SARG command, and press Enter:
ADB2AT in DD1A Table Authorizations Row 1 from 1000
Commands: REVOKE GRANT RMIMPL
Line commands:
R - Revoke GR - Grant T - Table I - Interpretation U D I S U R U
CA - Column authorizations RE - Grantee role P A E I N E P R E N
? - Show all line commands D L L N S L D E F T L
C T E D E E A F C R O
G H Date O E T E R C T E O I A
S Grantor Grantee T Schema Name G Grant L R E X T T E R L G D
* * * * * * * * * * * * * * * * * *
-- -------- -------- - -------- --------------> - ------ - - - - - - - - - - -
SYSADM PUBLIC SYSIBM SYSDUMMYE S 100809 Y
SYSADM PUBLIC SYSIBM SYSDUMMYA S 100809 Y
SYSADM PUBLIC SYSIBM SYSDUMMYU S 100809 Y
SYSADM PUBLIC SYSIBM SYSDUMMY1 S 030921 Y
SYSADM PRCAL504 P QUAG5042 TBAL5042 S 200824 Y
RSTEST RSTEST RSTEST TBH_35 201005 G G G G G G G G G
SYSIBM SYSIBM SYSIBM DSNRLST01 S 160314 G G G G G G G G G
DSNRGCOL DSNRGCOL DSNRGCOL DSN_REGISTER_AP S 160314 G G G G G G G G G
DSNRGCOL DSNRGCOL DSNRGCOL DSN_REGISTER_OB S 160314 G G G G G G G G G
Command ===> SARG Scroll ===> PAGE
On the subsequent Search fields (ADB2SARG) panel, specify the following
values for GRANTEETYPE:
The following search operators are valid on this panel:
EQ or =
Equal to
GT or >
Greater than
GE or >=
Greater than or equal to
LT or <
Less than
LE or <=
Less than or equal to
NE or ¬=
Not equal to
Press Enter. Then press END (F3) to exit the Search fields (ADB2SARG) panel.
The previous panel displays only those rows where the GT
column is blank:
ADB2AT in DD1A Table Authorizations Row 1 from 1000
Commands: REVOKE GRANT RMIMPL
Line commands:
R - Revoke GR - Grant T - Table I - Interpretation U D I S U R U
CA - Column authorizations RE - Grantee role P A E I N E P R E N
? - Show all line commands D L L N S L D E F T L
C T E D E E A F C R O
G H Date O E T E R C T E O I A
S Grantor Grantee T Schema Name G Grant L R E X T T E R L G D
* * . * * * * * * * * * * * * * * *
-- -------- -------- - -------- --------------> - ------ - - - - - - - - - - -
SYSADM PUBLIC SYSIBM SYSDUMMYE S 100809 Y
SYSADM PUBLIC SYSIBM SYSDUMMYA S 100809 Y
SYSADM PUBLIC SYSIBM SYSDUMMYU S 100809 Y
SYSADM PUBLIC SYSIBM SYSDUMMY1 S 030921 Y
RSTEST RSTEST RSTEST TBH_35 201005 G G G G G G G G G
SYSIBM SYSIBM SYSIBM DSNRLST01 S 160314 G G G G G G G G G
DSNRGCOL DSNRGCOL DSNRGCOL DSN_REGISTER_AP S 160314 G G G G G G G G G
DSNRGCOL DSNRGCOL DSNRGCOL DSN_REGISTER_OB S 160314 G G G G G G G G G
SYSIBM SYSIBM SYSIBM DSN_PROFILE_TAB S 160314 G G G G G G G G G
Command ===> Scroll ===> PAGE
Case:
Lowercase characters in the search argument for Name,
Owner, In DB/Coll,
Grantor, and Grantee are
translated to uppercase characters unless you change the Db2 Admin Tool default setting.
To change the default setting, navigate to the Admin Defaults (ADB2P2)
panel and set the
Capitalize object names field to NO. With this
setting, lowercase characters are not translated to uppercase characters
where Db2 rules
allow the name to contain lowercase letters. Lowercase characters cannot be
translated to uppercase characters in database names, table space names,
plan names, and package names that are not for trigger packages