Modifying Access Path Analysis Text

FANLS001

METHOD = 1 and 

An increase in the size of the table creator.tname may have the potential to cause an exponential increase in query execution time. The nested loop join method used here decreases in efficiency, either:

• as the number of rows that satisfy the join criteria increases.
• if the access path to one of the tables remains inefficient.

JOINTYPE <> S

FANLS002

METHOD in ’2, 4’ and

Processing cost and I/O cost are increased when Db2 sort is used to scan and access data during a join process.

SORTCJO = Y or
SORTNJO = Y

FANLS003

ACCESSTY = R and

A scan of a tablespace of this size significantly adds to:

• the processing cost of the query because the number of rows scanned is a contributing factor.
• the I/O cost of the query because the number of data pages read is also a factor.

PBIGTS = Y

JOINTYPE <> (not equal) S

FANLS004

ACCESSTY = R and

In a tablespace that is not segmented, Db2 reads every row in tablespace xdbname.xtsname. The total time required to execute the query depends on the number of pages to be read in the tablespace. This includes storage for other tables, space left free for expansion, and unreclaimed storage after delete operations.

PBIGTS = Y and

XSEGSIZE = 0 and

XNTABLES > 1

FANLS005

ACCESSTY = I and 

In a non-matching index scan, Db2 must read all the leaf pages of the index.

XMTCHCOL = 0

FANLS006

PINDXS = Y and

Since data pages are also read during the scan, the I/O cost of the query may be higher than for a tablespace scan.

ACCESSTY = I and

INDEXONL = N

FANLS007

PINDXS = Y and

Because of the number of rows (xcard) in table creator.tbname the full scan of index accesscr.accessna may not be appropriate since this will significantly increase the I/O cost of the query.

PBIGIX = Y

FANLS008

XNLEVELS > 3 and

A matching index scan requires an I/O operation for each level of the index.

XCOLCNT =
XMTCHCOL

FANLS009

XNLEVELS > 3

Extra I/O and CPU time is required to keep a large index balanced through the splitting of index pages, these index pages may be scattered across DASD volumes adding to I/O cost.

FANLS010

PARTIDX = Y and

The index scan involves reading index pages as well as data pages. If the data is not in index order, I/O may be even higher than a tablespace scan.

INDEXONL = N

FANLS011

PDUPIX = Y

Multiple read operations are required to obtain all information for a given index key using a duplicate index.

FANLS012

PDUPIX = Y and XSTYPE = D and

Db2 delete operations on indexes with duplicate key values allowed require more I/O time than unique indexes. Every entry of a set of duplicates is deleted one at a time.

XTYPE = N or (XTYPE = LITSPACE (blank) and

DB2VERS < 4.0)

FANLS013

ACCESSTY in 'I, I1,N, MX' and 

For small tables (less than 10 pages in size), the increase in overhead incurred for index maintenance may significantly offset any performance gains obtained by use of an index.

XNPAGES < 10 and 

XNOSTATS = N

FANLS014

TSLOCKMO = S

Contention may occur with updates of this table.

FANLS015

TSLOCKMO = S and

Contention is experienced for all tables in the tablespace.

XNTABLES > 1 and

XSEGSIZE = 0

FANLS016

TSLOCKMO = X

No other user is able to access table creator.tname for the duration of this query.

FANLS017

TSLOCKMO = X and

Contention is experienced for all tables in the tablespace.

XNTABLES > 1 and

XSEGSIZE = 0

FANLS018

PSORT = Y

Use of Db2 sort puts a load on the following resources:
- processor
- virtual and real storage
- I/O devices

FANLS019

CNSFOUND = N

No CONSEQUENCES were found for this Query Block.

FANLS020

PAGERNGE = LITSPACE and

All partitions may be opened to satisfy the query.

XSEGSIZE > 0

FANLS028

PRLMMODE = C

CP parallelism involves the use of multiple parallel CP tasks. Locks are obtained independently by each task. This may increase the number of locks acquired by an application.

FANLS029

XSTYPE IN ’H, I, A’

Cursor’s declared using the WITH HOLD option may continue to hold locks after a COMMIT.

FANLS032

PRLMMODE = X

At execution time, Db2 may use fewer operations in parallel than was originally planned or it may not use parallelism.

FANLS033

DB2VERS >= 6.0 and

Though Db2 may plan to use Direct Row Access, certain situations might cause Db2 to not use direct row access at run time. For instance, the row location may change between the first and second time it is accessed. A REORG is a potential way the row location could change. Db2 cannot use direct row access to find the row on the second attempt. In place of using direct row access, Db2 uses the access path indicated by the ACCESSTY column of PLAN_TABLE.

PRIMACCS = D

FANLS034

DB2VERS >= 6.0 and

When you give an optimization hint (OPTHINT='hint id'), Db2 verifies the information in the PLAN_TABLE to ensure that you pick a legitimate access path. If the access path you specify has major problems, Db2 rescinds all hints for that query block. In that event, Db2 determines the access path as it normally does.

OPTHINT NE LITSPACE

FANLS035

INDEXONL = Y and 

Although the Plan Table indicates Index Only access, access to the data pages is required for UPDATE and DELETES.

QBLKTYPE = DELETE or 

QBLKTYPE = UPDATE

FANLS036

JOINTYPE = S

In the Star Schema, the Fact Table must minimize unnecessary probes and provide the maximum level of matching index columns based on pre-joined dimensions.

FANLS037

JOINTYPE = S and 

In the Star Schema, use of the nested loop join method to join the Fact Table back to the (inner) Dimension Tables, require that these tables have indexes on the join columns, thus avoiding repeated scans on these tables.

METHOD = 1

FANLS038

JOINTYPE = S

In the Star Join method: If Snowflakes exist they are materialized before the joins are optimized.

FANLS039

JOINTYPE = S

In the Star Join method: Dimension Workfiles joined before the fact table may be scanned many times, possibly causing a high getpage count on the workfile buffer pool and possibly many physical page reads.

FANLS040

XMTCHCOL = XCOLCNT  and 

If the matching predicates on index accesscr.accessna are equal-predicates, the index qualifies as an equal-unique index, which is the most efficient index access outside of one-fetch index access.

XMTCHCOL > 0 and 

PDUPIX = N

FANLS041

PREFETCH = L

List Prefetch utilizes skip sequential processing, allowing unreferenced data pages to be skipped.

FANLS042

PREFETCH = L

List Prefetch minimizes seek time to locate pages by reading pages sequentially as they appear on DASD.

FANLS043

PREFETCH = L

List Prefetch allows fewer I/Os to the data pages since the RIDs are sorted and reduced before the data pages are accessed.

FANLS044

PAGERNGE = Y

In a Limited Partition Scan only needed partitions are scanned.

FANLS045

PAGERNGE = Y

In a Limited Partition Scan only the first key column of the Partitioning Index is important in limiting the range of the partition scan.

FANLS046

PAGERNGE = Y and 

This Limited Partition Scan will perform a non-partitioning index (NPI)  Logical Partition Scan.

ACCESSTY = I

FANLS047

JOINTYPE = S

For the Star Join method, queries with low filtering or of I/O bound nature will not benefit.

FANLS048

JOINTYPE = S

The Star Join method utilizes an Index Repositioning technique, versus the traditional Cartesian Join.

FANLS049

JOINTYPE = S or

Optimization Hints cannot be used to force the Star Join method.

OPTHINT NE LITSPACE (blank) or

HINTUSED NE LITSPACE (blanks)

FANLS050

JOINTYPE = S or OPTHINT NE LITSPACE (blanks)

If a Hint causes unnecessary processing, such as, additional sort(s), multiple index usage, materialization, etc., the Optimizer ignores the Hint.

FANLS051

PRLMMODE IN 'I,C,X' and 

The Degree of Parallelism may be adjusted at execution time, depending upon the contents of the host variables, ambiguous cursors, and availability of required prerequisites.

ACSSDGRE = 0

FANLS052

PRLMMODE = C

CPU Parallelism includes parallel I/O.

FANLS053

PRLMMODE IN 'I,C,X' or PRIMACCS = D

Direct Row Access and Parallelism are mutually exclusive. If the query qualifies for both, then Direct Row Access is selected.

FANLS054

DB2VERS >= 6.0 and 

When used Direct Row Access is very efficient, since Db2 does not need to use an index or tablespace scan to locate the row. Instead Db2 locates the row with the value of the ROWID.

PRIMACCS = D

FANLS055

PRLMMODE IN 'I, C, X’

If there is insufficient buffer pool space before the start of parallelism, either the
degree is reduced or no parallelism usage.

FANLS056

DB2VERS >= 7.0 and 

The result of the UNION is materialized into a Logical Work File and subsequently read from that Logical Work File by the Parent Query Block.

QBLKTYPE = UNION and

TBTYPEXP = W

FANLS057

DB2VERS >= 7.0 and 

The result of the UNION ALL is materialized into a Logical Work File and subsequently read from that Logical Work File by the Parent Query Block.

QBLKTYPE = UNIONA and 

TBTYPEXP = W

FANLS058

DB2VERS >= 7.0 and 

Materialization can be resource intensive if there are many intermediate rows which must be written to the temporary work area.

TBTYPEXP = W

FANLS059

DB2VERS >= 8.0 and 

Db2 uses a technique referred to as materialized fetch or block fetch. In a block fetch all rows are returned during the first invocation of the table function, prefetched and stored in a materialized Db2 workfile.

ACCESSTY = RW

FANLS060

DB2VERS >= 8.0 and 

A Sparse Index is dynamically built and points to a range of values.

ACCESSTY = T

FANLS061

METHOD = 2

In a Merge Scan Join, Db2 scans both tables in the order of the join column(s) and matching rows are returned in the resulting table. As the rows in the tables must be in sequence, Db2 either sorts the rows or retrieves them using a high cluster ratio index.

FANLS062

JOINTYPE = F and 

A Full Outer Join requires that the Merge Scan Join method is used.

METHOD =2

FANLS063

METHOD = 2 and 

In a Merge Scan Join that requires a sort, all predicates are applied before the sort in an attempt to reduce the number of rows to be sorted.

SORTNJO (Sort Inner Join Table) = Y  or  SORTCJO (Sort Outer Join Table) = Y

FANLS064

METHOD = 1

In the Nested Loop Join Method, Db2 retrieves every row of the composite (outer) table that satisfies your WHERE clause condition(s). As each outer table row is retrieved, Db2 uses the WHERE clause joining criteria to: (1) Access rows in the inner table; (2) Apply any further WHERE clause conditions; and (3) Retain all qualifying rows in a composite table.

FANLS065

METHOD = 1

The Nested Loop Join method does not require that the rows to be joined are in sequence or sorted. However, the Db2 Optimizer may choose to sort the composite (outer) table.

FANLS066

METHOD = 1

The Nested Loop Join technique avoids materialization of the intermediate result set.

FANLS067

METHOD = 4

In the Hybrid Join Method, Db2 sequences the outertable in the order of the inner table join index. The inner table index is then scanned to retrieve the RIDs that match the join predicate. The resulting inner tablerows are retrieved via skip sequential processing.

FANLS068

METHOD = 4

The Hybrid Join can be considered a mix between a Merge and Nested Loop Join.

FANLS069

SORTCOR = Y or SORTCUN = Y

A single sort puts the rows in order for the ORDER BY clause and removes any duplicates.

FANLS070

JOINTYPE = L

A Left Outer Join keeps unmatched rows from the composite (outer) table. The
unmatched rows are filled out with null values.

FANLS071

JOINTYPE = L

A Right Outer Join keeps unmatched rows from the new (inner) table.
However, the Db2 Optimizer converts a Right Outer Join to a Left Outer Join
before selecting the access path.

FANLS072

JOINTYPE = F

A Full Outer Join keeps unmatched rows from both tables. The composite rows resulting from the unmatched rows are filled out with null values.

FANLS073

DB2VERS > 4.0 and

An Inner Join discards all unmatched rows.

JOINTYPE = LITSPACE and

METHOD IN ’1, 2, 4’

FANLS074

DB2VERS > 6.0 and

A Star Join joins a column from the fact table with the key column of the corresponding dimension table.

JOINTYPE = S

FANLS075

DB2VERS >= 8.0 and

The NOT PADDED keyword allows varying length columns in the index key to
be used for index-only access.

XPADDED = N

ACCCRTR1 NE blanks and

ACCNAME1 NE blanks

FANLS076

DB2VERS >= 8.0 and

The NOT PADDED keyword impedes index comparisons. This is because
instead of comparing the entire key when the columns are padded to their
maximum length, Db2 must compare each pair of corresponding varyinglength
columns individually.

ACCCRTR1 NE blanks and

ACCNAME1 NE blanks
and

XPADDED = N

FANLS077

DB2VERS >= 8.0 and

If varying-length data is returned, or a VARCHAR column has a LIKE predicate Index-only access is not allowed for padded indexes. The exception to this is when the VARCHAR FROM INDEX field DB2 Install field is set to YES and Db2 plans or packages are rebound to pick up the change.

XPADDED = Y

FANLS078

DB2VERS >= 8.0 and

DPSI’s allow for query parallelism.

XPART > 0 and

XDPSIIND = D and

ACCCRTR1 NE LITSPACE
(blank) and

ACCNAME1 NE LITSPACE
(blank)

FANLS079

DB2VERS >= 8.0 and 

DPSI’s require a separate probe for each qualified partition. It is important to reduce the number of qualified partitions as much as possible. If your query predicate only references columns in a secondary index, it is likely to run slower.

XPART > 0 and

XDPSIIND = D and 

ACCCRTR1 NE LITSPACE (blank) and 

ACCNAME1 NE LITSPACE (blank)

FANLS080

DB2VERS >= 8.0 and 

Common table expressions are materialized if they are referenced more than once. If a common table expression is only referenced once the common table expression is treated like a regular table expression and materialization is avoided whenever possible.

TBTYPEXP = C or 

TBTYPEXP = R

FANLS081

DB2VERS >= 8.0 and 

Keep in mind that recursive common table expressions can create an infinite recursion cycle (loop). Make sure that recursion cycles will terminate. This is specially important if the data involved is cyclic.

TBTYPEXP = R

FANLR001

METHOD = 1

To reduce the number of rows scanned for the join, consider adding additional selection criteria within your WHERE clause to further filter out unwanted information. Or if there are correlated concatenated key columns of an index, consider executing the RUNSTATS utility with the KEYCARD and FREQVAL options to collect multi-column cardinality.

FANLR002

METHOD = 2 and 

Provide an index to allow more efficient access to the data involved in the join.

(SORTCJO = Y or SORTNJO = Y)

FANLR003

METHOD = 4 and

Provide a well clustered index on the join columns of the outer and inner tables involved in the join.

SORTCJO = Y or SORTNJO = Y

FANLR005

ACCESSTY = R and

PBIGTS = Y and

XHASINDX = Y and

XMODE = 0

FANLR006

ACCESSTY = R and

PBIGTS = Y and

XHASINDX = Y and

XMODE not = 0

FANLR007

ACCESSTY = R and

Table creator.tname has no available indexes. Consider creating an index for frequently used columns in the table and incorporating the index into the query.

PBIGTS = Y and

XHASINDX = N

FANLR008

ACCESSTY = R and

Consider segmenting tablespace xdbname.xtsname, so that Db2 will scan only segments containing rows of table creator.tname.

PBIGTS = Y and

XSEGSIZE = 0 and

XNTABLES > 1

FANLR009

PINDXS = Y and

To retrieve the data as efficiently as possible, a matching index scan should be used. Consider:
1) adding an index with the desired column(s) as the key, or
2) restructuring the query to reference the first column in the index key, or
3) if there are correlated concatenated key columns of an index, consider executing the RUNSTATS utility with the KEYCARD and FREQVAL options to collect multi-column cardinality.

XNPAGES >= 6

FANLR010

PINDXS = Y and

The low cluster ratio (clustr) of this index, indicates that reorganization of the indexspace would improve Db2’s access path during the index scan. Since this is a segmented tablespace, reorganizing it arranges the data in the order of the clustering index. A high cluster ratio allows Db2 to use sequential PREFETCH to read consecutive pages.

XDEFCLS = Y and

XCLSTRD = N and

XDEGSIZE > 0

FANLR011

PINDXS = Y and

To reduce the number of levels for index accesscr.accessna:

• reorganizing the index helps to store information more efficiently by reducing page splits.
• shortening the index key, for example splitting a long composite index column into a separate index, allows more entries on a page.
• for a clustering index, partitioning the tablespace so that each partition uses its segment of the clustered index.

XNLEVELS > 3

FANLR012

PPARTIDX = Y

To retrieve the data as efficiently as possible, a matching index scan using the full index key should be used. Consider adding an index with the desired column(s) as the key.

FANLR013

PDUPIX = Y

Modifying the index key to make it unique allows Db2 to locate needed data with the least number of I/O operations.

FANLR014

ACCESSTY IN 'I, I1,N,MX' and 

Verify that Table creator.tname remains under 10 pages in size and consider not using an index for access. The NPGTHRSH system parameter can be set to influence the Db2 Optimizer in it’s selection of tablespace scan versus index scan.

XNPAGES < 10 and 

XNOSTATS = N

FANLR015

TSLOCKMO = S or
TSLOCKMO = X

Consider segmenting the tablespace, so that a tablespace scan only locks pages containing rows of the desired table. Also, Db2’s locking concurrency control is improved since a choice of locksizes is available for each table in the tablespace.

XNTABLES > 1 and

XSEGSIZE = 0

FANLR016

PSORT = Y

Consider using indexes to give direct access to data, and improve overall response time and performance by eliminating the need for a sort.

FANLR017

XSTYPE = D and

Verify that the delete rule for table creator.tname is correct for this relationship.

XDELRULE = C

FANLR018

RECFOUND = N

No RECOMMENDATIONS were found for this Query Block.

FANLR019

XNOSTATS = Y

Use the Db2 RUNSTATS utility to gather accurate statistical information for the Db2 structures accessed by this query.

FANLR020

XNOSTATS = Y and

XMODE = O

FANLR021

XNOSTATS = Y and

XMODE not = O

FANLR022

PAGERNGE = LITSPACE and

Provide a predicate that matches columns of the partitioning index. This may allow Db2 to limit a scan of data in a partitioned table space to one or more partitions, page range screening. Page range screening can be used for each table accessed in a join, as long as the access is sequential.

XPART > 0 and

PRLMMODE <> (not equal) I and

PRLMMODE <> (not equal) C

FANLR023

JOINTYPE = LITSPACE and

Provide a predicate that matches columns of the partitioning index. This may allow Db2 to limit a scan of data in a partitioned table space to one or more partitions, providing this query is not using a join. Parallelism is being used and a page scan cannot be used with a join.

PRLMMODE IN ’I, C’

FANLR024

TRYPRLMD = Y

Specify DEGREE(ANY) for BIND’s and REBIND’s to enable parallelism for static SQL. SET CURRENT DEGREE=’ANY’ to enable parallelism for dynamic SQL. Please note: CP parallelism is not considered unless Db2 is executing on a Central Processor complex of multi tightly-coupled processors and more than one is online when the BIND occurs.

FANLR025

XSTYPE = A

Cursor appears ambiguous. Use the FOR FETCH ONLY clause or bind with CURRENTDATA(NO) to remove the ambiguity. This may enable Parallelism for the query.

FANLR027

PRLMMODE = C

Frequent commits are recommended for repeatable read applications. If CP-parallelism is a suspected source of contention problems it can be disabled. Rebind after changing DEGREE(ANY) to DEGREE(1) to disable it for static SQL. Execute SET CURRENT DEGREE = ’1’ to disable it for dynamic SQL. The default for CURRENT DEGREE is 1 unless it has been changed by your installation. If it has not changed then removing DEGREE(ANY) before rebinding or SET CURRENT DEGREE = ’1’ may also disable CP-parallelism for static or dynamic SQL respectively.

FANLR028

XSTYPE IN ’H, I, A’

Close cursors declared WITH HOLD as soon as possible to release any locks the cursor may have.

FANLR030

PRLMMODE = X

If the statement was bound at the same time as this Db2 is a member of a data sharing group, the PARALLELISM_MODE column might contain an X even if only this one Db2 is active. This permits Db2 to take advantage of extra processing power which may be available at execution time. If other members are unavailable at execution time, Db2 runs the query within the single Db2 member.

FANLR031

DB2VERS >= 6.0 and

Write applications to handle the chance that direct row access may not be used. Some alternatives are:

1) Make sure the application does not attempt to recall ROWID columns across reorganizations of the table space.

2) When an application commits, it frees its claim on the table space. It is possible that a REORG may run and move the row, this stops direct row access. Plan the commit processing accordingly. Use the returned row ID value before committing or reselect the row ID value after a commit has been issued.

3) If the application is saving the ROWID column values from another table, update those values after the table with the ROWID column is reorganized.

4) Allow Db2 to use an index if direct row access is disabled by creating an index on the ROWID column.

5) Enhance the ROWID column predicate with another predicate which enables Db2 use an existing index on the table.

PRIMACCS = D

FANLR032

DB2VERS > 6.0 and

The selection method may change in a later release of Db2. This change could cause your changes to degrade performance. To stop this, save the old catalog statistics or SQL before thinking of making any changes to control the choice of access path. Take performance measurements before and following any changes. After migrating to a new release, examine the performance again. Be ready to back out any changes that have degraded performance.

HINTUSED NE LITSPACE or

OPTHINT NE LITSPACE

FANLR033

QBLKTYPE IN 'UNION, UNIONA'

Since Db2 does not provide explicit information identifying query rewrites, closely examine the Query Block No and Parent Query No dependencies to determine if query rewrite has taken place.

FANLR034

DB2VERS >= 8.0 and 

Use of a Star Join Pool to allow in-memory workfiles can further enhance Star Join processing. In-memory workfiles can save large numbers of getpage and I/O activities on the workfile buffer pool, thereby reducing contention.

JOINTYPE IN 'S,T'

FANLR035

JOINTYPE = S

Evaluate whether Star Join support will benefit your applications before turning it ON for a specific Db2 Subsystem, as there is a possibility that it can cause performance degradation. Enable only if an overall benefit will be achieved.

FANLR036

JOINTYPE = S

Physically separating the Fact Table from the Dimension Tables may improve I/O throughput.

FANLR037

JOINTYPE = S

To increase selection of the Star Join method, make sure all Fact Tables on the current Db2 Subsystem have the appropriate indexes defined.

FANLR038

JOINTYPE = S

Cluster the data by the index that is set up to support the Star Join. Ensure the Fact Table Index has a high cluster ratio covering all the Dimension Tables.

FANLR039

PRLMMODE IN 'I, C, X'

If parallelism is not desired, use the Resource Limit Facility (RLF) to disable parallelism for identified Plans and Authids.

FANLR040

DB2VERS >= 7.0 and 

The following statistics in the SYSIBM.SYSROUTINEScatalog table should be monitored and updated appropriately to ensure proper access path selection for Table Functions:

CARDINALITY
IOS_PER_INVOC
INSTS_PER_INVOC
INITIAL_IOS
INITIAL_INSTS

TBTYPEXP = F

FANLR041

SORTNUN = Y or 

Work with the System Administrator to properly size the Sort Pool and Work File Tablespaces for your application workload.

SORTNJO = Y or 

SORTNOR = Y or 

SORTNGR = Y or 

SORTCUN = Y or 

SORTCJO = Y or 

SORTCOR = Y or 

SORTCGR = Y

FANLR042

METHOD > 0

If the desired Join Method was not chosen, verify that the Catalog Statistics on the joined tables are accurate.This can affect the Join Method selected.

FANLR043

JOINTYPE = L

Recommend following the guideline of coding Right Outer Joins as Left Outer Joins to avoid confusion in plan table interpretation.

FANLR044

DB2VERS >= 8.0 and 

To permit variable character index only retrieval, prevent incompatible data procurement of the full column width and use less space within the index, use NOT PADDED indexes.

ACCCRTR1 NE blanks and 

ACCNAME1 NE blanks and ’

XPADDED = Y

FANLR045

DB2VERS >= 8.0 and 

Consider using ALTER INDEX to change the following index indexcreator.indexname to NOT PADDED if it contains a key of a varying-length character or graphic column.

XPADDED = ? and

ACCCRTR1 NE blanks and
ACCNAME1 NE blanks

FANLR046

DB2VERS >= 8.0 and

You may reduce the need for Db2 to perform a sort for SELECT statements
with an ORDER BY. The backward index scan feature may reduce the need
for descending indexes since Db2 can scan ascending indexes backward.
In order to use an index for a backward scan the following conditions
must be true:

• The index is defined on the same columns as the columns referenced in the ORDER BY.
• The ordering specified in the index is the opposite of the ordering specified in the ORDER BY clause. This is true for each column that is in the ORDER BY clause.

Consult with your database administrator to determine if you can take
advantage of this feature.

METHOD = 3 and

SORTNOR = Y or SORTCOR = Y

FANLR047

DB2VERS >= 8.0 and

When possible provide a predicate that references the columns in the
table that control partitioning index. A query that:

• Has predicates against the secondary index
• References column(s) in the table which control partitioning
• Uses explicit partitioning key predicates

Would benefit from the use of a DPSI, because the number of partitions
scanned may be limited.

XPART > 0 and

XDPSIIND = D and

TPARTIND = Y

FANLR048

DB2VERS >= 8.0 and 

A query with predicates on partitioning columns plus secondary index columns is likely to use a DPSI index. Consider rewriting your query to meet these conditions.

XDPSIIND = N and

XPART > 0 and 

CREATR1 and TNAME NE LITSPACE (blank)

FANLR049

DB2VERS >= 8.0 and 

Consider replacing an existing Non-Partitioned Secondary Index (NPSI) with a Data Partitioned Secondary Index (DPSI), if there are perceivable benefits. Some examples would be easier data or index maintenance, improved data or index availability, or improved performance.

XDPSIIND = N and

XPART > 0 and 

ACCCRTR1 NE LITSPACE (blank) and 

ACCNAME1 NE LITSPACE (blank)

FANLR050

DB2VERS >= 8.0 and 

To prevent the possibility of an infinite loop with recursive SQL, you should desk check all recursive SQL. You should also test your recursive SQL against small tables, before implementing it in a production environment.

TBTEYPEXP = R