Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Location
    Bangalore
    Posts
    12

    Unanswered: EXPLAIN output - Parallel I/O ?

    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 !!!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2004
    Location
    Bangalore
    Posts
    12
    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)

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2004
    Location
    Bangalore
    Posts
    12
    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.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •