Version : DB2V6.1 ,OS290 V2.8

I ran Explain on a SQL using the platinum analyser tool and it gave the result.

I did not understand the following part in the explain output.Its saying :

"SQL Design Rule: 0082 ------------------------ Severity Code = 0
This SQL statement accesses a partitioned tablespace with an ambiguous cursor and is not utilizing parallel I/O processing. This SQL statement references the table "OFSPROD.QO_MFG_UNIT" which is in the partitioned tablespace "DPCH0001.SQOMFUNT". This tablespace contains 213209 active pages.Your installation has set a threshold of 10 for the maximum number of active pages for a partitioned tablespace accessed by an SQL statement that is not utilizing parallel I/O processing. Ambiguous cursors can reduce the degree of parallel I/O or cause parallel I/O to fall back to sequential I/O. Parallel I/O can significantly reduce response time for SQL statements that are I/O intensive.

Could any of you folks let me know if this really impacts performance.
Also I am not sure where to look out for the installation threshold for active pages.
Thanks !!!

Can you post the SQL statement? This might help to determine if it will impact performance. Also specify the partioning key.

Most installation parms are in the zparms, but I don't know for sure about this one.

The SQL:
---------
DECLARE CURSOR_MFG_UNIT CURSOR FOR
SELECT CDE_UNIT_LOC , PCT_PRVD , SEQ_UNIT , SEQ_UNIT_PROC
FROM QO_MFG_UNIT
WHERE ID_ORD = :H AND ID_ORD_CHGE = :H AND PCT_PRVD IS NOT NULL


Keys :
--------
PRIMARY KEY (ID_ORD
,ID_ORD_CHGE
,SEQ_UNIT
,SEQ_UNIT_PROC)

You can try adding the FOR FETCH ONLY clause to see if that changes the cursor to unambiguous. This assumes you will not be doing an "update or delete where current of cursor."

You didn't specify the partitioning key (which may be different than the primary key) so I can't tell you whether parallel operations would help for this query. However, given that you supplied the first 2 columns of the primary key in the predicate, and if the number of rows returned is fairly low, then parallel operations probably are not important for this particular SQL statement.

UNIQUE INDEX PROD.IQOMFUN1
ON PROD.QO_MFG_UNIT
(ID_ORD ASC
,ID_ORD_CHGE ASC
,SEQ_UNIT ASC
,SEQ_UNIT_PROC ASC )
CLUSTER.....
......

Marcus, I believe this is the one you are looking for.

The following is a partial example of syntax used to define a partitioning index within the create index statement:

(PART 1 VALUES(’H99’),
PART 2 VALUES(’P99’),
PART 3 VALUES(’Z99’),
PART 4 VALUES(’999’))

The above syntax defines the highest key value that is contained in each partition.

If you have a query that is accessing a small amount of data via an index, or all the qualifying rows are in the same partition, then you will not benefit from parallelism with DB2 for OS/390 partitioned tablespaces.

Conversely, if you are retrieving a lot of rows across all the partitions, then DB2 can use parallelism (one process per partition) to speed up the query.