You can refine the list of indexes to be included by providing an advanced SQL statement with a customized WHERE clause. Any valid WHERE clause that selects indexes to be included can be specified.
About this task
Advanced SQL works with the selection criteria that you specify in the Database Like, Index Like, and Creator Like fields on the Enter Indexes Like to Display window. The results of selection criteria are intersected with the results of the advanced SQL statement to select the indexes.
Selecting indexes from a list requires setting the Wildcard field to N on the Enter Indexes Like to Display window. When Wildcard is set to N, the advanced SQL is used to build the list of indexes from which to select. After you choose the indexes, the advanced SQL is discarded and not saved in the object profile.
Procedure
On the Enter Indexes Like to Display window, enter a database, index, or creator name or mask in the appropriate fields and enter N in the Wildcard field.
Note: The Creator Like and Index Like fields allow up to 128 bytes. To scroll these fields, place the cursor in the field and use the PF11 key to scroll right and the PF10 key to scroll left.
Enter Y in the Advanced SQL field and Y in the Update SQL field, as shown in the following figure.
Figure 1. Specifying advanced SQL
--------------------- Enter Indexes Like to Display --------------------
Database Like *
Creator Like * >
Index Like * >
Wildcard N (Yes/No) Exclude I (E - Exclude, I - Include)
Process Cloned Indexes N (Yes/No)
Advanced SQL . . . . Y (Yes/No) Update SQL Y (Yes/No)
Press Enter.
The Object Selection Advanced SQL panel is displayed, as shown in following figure:Figure 2. Object Selection Advanced SQL panel
AUTOXPRT V1R1 ---- Object Selection Advanced SQL --- 2023/10/19 16:54:00
Option ===> Scroll ===> CSR
Db2 Subsystem: SS01
-------------------------------------------------------------------------------
Commands: Execute - Test Sql Import - Import from dataset
Line Commands: C - Copy D - Delete I - Insert M - Move R - Repeat
T - Table/Column Lookup
-------------------------------------------------------------------------------
Cmd SQL
***************************** Bottom of Data **********************************
Enter your SQL statement in the SQL input area.
You can enter any SQL statement that meets these requirements:
It must be a SELECT statement that specifies the following columns:
The first column is required and must represent a database name.
The second column is required and must represent an index name.
The third column is optional, but if present must represent a partition number.
The FROM clause must be specified after the SELECT statement.
Use any WHERE clause criteria that identifies the objects to be included in the object profile.
You can enter comments by inserting two consecutive hyphens, followed by the comment. Any characters that follow two consecutive hyphens and are before the end of a line are ignored.
You can optionally enter the T line command to look up table names and their columns on the subsystem. This command invokes the Table Selection panel, which you can use to generate a list of tables on this subsystem and find column names associated with the tables.
The following panel shows a sample valid advanced SQL statement: Figure 3. Sample advanced SQL statement
AUTOXPRT V1R1 ---- Object Selection Advanced SQL --- 2023/10/19 16:54:50
Option ===> Scroll ===> CSR
Db2 Subsystem: SS01
-------------------------------------------------------------------------------
Commands: Execute - Test Sql Import - Import from dataset
Line Commands: C - Copy D - Delete I - Insert M - Move R - Repeat
T - Table/Column Lookup
-------------------------------------------------------------------------------
Cmd SQL
SELECT D.NAME,S.NAME
FROM SYSIBM.SYSINDEXES S, SYSIBM.SYSINDEXPART P,
SYSIBM.SYSINDEXSTATS ST,SYSIBM.SYSDATABASE D
WHERE S.NAME = P.IXNAME
AND S.CREATOR = P.IXCREATOR
AND P.PARTITION = ST.PARTITION
AND S.DBNAME = D.NAME
AND ST.NLEVELS = 3
AND S.CREATOR = 'PUSER1'
***************************** Bottom of Data **********************************
To verify that the results of the SQL select statement are as expected, enter EXECUTE in the Option field and press Enter.
The SQL statement is run and the results of the SELECT are listed on the Advanced SQL Test Facility panel, as shown in the following figure:Figure 4. Advanced SQL Test Facility panel with SQL statement results
This panel shows the results of the SQL SELECT statement execution. The data on the panel is read only.
Note: The EXECUTE command runs the SQL statement without consideration for the Database Like, Index Like, and Creator Like criteria that you specified on the Enter Indexes Like to Display window.
When you are satisfied with the results of the SELECT statement, on the Advanced SQL Test Facility panel, press PF3 twice.
Note: Because the Wildcard field was set to N on the Enter Indexes Like to Display window, the SQL statement is not saved when you exit the Object Selection Advanced SQL panel.
The Include Index Selection panel is displayed. This panel shows the results of the intersection of the SQL SELECT statement and the Database Like, Index Like, and Creator Like criteria that you specified on the Enter Indexes Like to Display window. The following figure shows the results: Figure 5. Include Index Selection panel
AUTOXPRT V1R1 ------- Include Index Selection ------ 2023/10/19 20:37:20
Option ===> Scroll ===> CSR
-------------------------------------------------------------------------------
Line Commands: S - Select Index Row 1 of 27 +>
Database Like * Db2 Subsystem: SS01
Index Like * >
Creator Like * >
-------------------------------------------------------------------------------
Cmd Name Creator Part Tbname Tbcreator
IABRE4 PUSER1 ALL TABRE4 PUSER1
IABRE4 PUSER1 1 TABRE4 PUSER1
IABRE4 PUSER1 2 TABRE4 PUSER1
IABRE4 PUSER1 3 TABRE4 PUSER1
IABRE4 PUSER1 4 TABRE4 PUSER1
IABRE4 PUSER1 5 TABRE4 PUSER1
IABRE4 PUSER1 6 TABRE4 PUSER1
IABRE4 PUSER1 7 TABRE4 PUSER1
IABRE4 PUSER1 8 TABRE4 PUSER1
IABRE4 PUSER1 9 TABRE4 PUSER1
IABRE4 PUSER1 10 TABRE4 PUSER1
IABRE4 PUSER1 11 TABRE4 PUSER1
On the Include Index Selection panel, select the indexes that you want to include in the object profile.
What to do next
When you are finished adding indexes, press PF3 until the Update Object Profile Display is displayed. For more information, see Updating an object profile.