DB2 v7.1 ZOSV1R2 SQL Performance question

Arlen Stovall

DB2 v7.1 ZOSV1R2 SQL Performance question
I have question about the performance of two SQL statements. The table in
questions has 200 million records currently. The buffer pools are not large
enough to hold the whole index.
Here is the first SQL statement:

SELECT SGJTAMRM ,SGJTRIDX,OWNERID, SGJEVTYP, SGJBATCH,SGJITEM,
SGJTRIDX, SGJTYTYP
FROM DISETS.WEA2TRAN
WHERE OWNERID = '18045'
AND SGJEVTYP = 'ADJ'
AND SGJBATCH = '0000000025'
AND SGJITEM = '474'
AND SGJTYTYP = 'CHRG'
AND SGJTRIDX < 180000
AND SGJTRIDX = (SELECT MAX(SGJTRIDX) FROM DISETS.WEA2TRAN
WHERE OWNERID = '18045' )
AND SGJEVTYP = 'ADJ'
AND SGJBATCH = '0000000025'
AND SGJITEM = '474'
AND SGJTYTYP = 'CHRG'
AND SGJTRIDX = 180000 )
FOR READ ONLY WITH UR ;

The explain shows that the index I2tran1 is being accessed twice once for
the first select and once for the subselect. My question is would it be
better to access the index once and do a additional sort or to access the
index twice.

---------+---------+---------+---------+---------+---------+---------+-
TNAME ACCESSNAME TABNO MATCHCOLS INDEXONLY
---------+---------+---------+---------+---------+---------+---------+-
WEA2TRAN I2TRAN1 1 4
N
WEA2TRAN I2TRAN1 2 4
N
DSNE610I NUMBER OF ROWS DISPLAYED IS 2

TB_CARD INDEX_CARD NAME CREATOR NAME
---------+---------+---------+---------+---------+---------+------
199818566 199818566 WEA2TRAN DISETS I2TRAN1
199818566 199818566 WEA2TRAN DISETS I2TRAN2
199818566 199818566 WEA2TRAN DISETS I2TRAN3
199818566 199818566 WEA2TRAN DISETS I2TRAN4
199818566 106129722 WEA2TRAN DISETS I2TRAN5
199818566 199818566 WEA2TRAN DISETS I2TRAN6
199818566 34329488 WEA2TRAN DISETS I2TRAN7
DSNE610I NUMBER OF ROWS DISPLAYED IS 7

The second SQL statement:

SELECT SGJTAMRM ,MAX(SGJTRIDX)
FROM DISETS.WEA2TRAN
WHERE OWNERID = '18045'
AND SGJEVTYP = 'ADJ'
AND SGJBATCH = '0000000025'
AND SGJITEM = '474'
AND SGJTYTYP = 'CHRG'
AND SGJTRIDX < 178160
GROUP BY SGJTAMRM
FETCH FIRST ROW ONLY
FOR READ ONLY WITH UR;

The explain shows that the index is accessed only once, but a additional
sort is being performed.
When executed in batch the clock time is almost the same.
---------+---------+---------+---------+---------+---------+---------+--
TNAME ACCESSNAME TABNO MATCHCOLS INDEXONLY
---------+---------+---------+---------+---------+---------+---------+--
WEA2TRAN I2TRAN1 1 4
N
0 0
N
DSNE610I NUMBER OF ROWS DISPLAYED IS 2

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: DB2 v7.1 ZOSV1R2 SQL Performance question
(in response to Arlen Stovall)
It might be me (nearly time foi another long weekend after all) but I am struggling to make logical sense of the WHERE clause!

Ignoring the fact that there is one too many ")" (I asume the one on the line WHERE OWNERID = '18045' ) shouldn't be there, but the one on line AND SGJTRIDX = 180000 ) should for starters...)

Well, just looking at the SGJTRIDX predicates, you seem to be looking for rows that have SGJTRIDX < 180000 AND SGJTRIDX = (SELECT MAX(SGJTRIDX) FROM DISETS.WEA2TRAN
WHERE OWNERID = '18045'
AND SGJEVTYP = 'ADJ'
AND SGJBATCH = '0000000025'
AND SGJITEM = '474'
AND SGJTYTYP = 'CHRG'
AND SGJTRIDX = 180000 )

Regardless of the perfoamnce, aren't these mutually exclusive????

Phil G
Computer Associates


-----Original Message-----
From: DB2 Data Base Discussion List on behalf of arlen stovall
Sent: Fri 31/12/2004 14:16
To: [login to unmask email]
Cc:
Subject: [DB2-L] DB2 v7.1 ZOSV1R2 SQL Performance question



I have question about the performance of two SQL statements. The table in
questions has 200 million records currently. The buffer pools are not large
enough to hold the whole index.
Here is the first SQL statement:

SELECT SGJTAMRM ,SGJTRIDX,OWNERID, SGJEVTYP, SGJBATCH,SGJITEM,
SGJTRIDX, SGJTYTYP
FROM DISETS.WEA2TRAN
WHERE OWNERID = '18045'
AND SGJEVTYP = 'ADJ'
AND SGJBATCH = '0000000025'
AND SGJITEM = '474'
AND SGJTYTYP = 'CHRG'
AND SGJTRIDX < 180000
AND SGJTRIDX = (SELECT MAX(SGJTRIDX) FROM DISETS.WEA2TRAN
WHERE OWNERID = '18045' )
AND SGJEVTYP = 'ADJ'
AND SGJBATCH = '0000000025'
AND SGJITEM = '474'
AND SGJTYTYP = 'CHRG'
AND SGJTRIDX = 180000 )
FOR READ ONLY WITH UR ;

The explain shows that the index I2tran1 is being accessed twice once for
the first select and once for the subselect. My question is would it be
better to access the index once and do a additional sort or to access the
index twice.

---------+---------+---------+---------+---------+---------+---------+-
TNAME ACCESSNAME TABNO MATCHCOLS INDEXONLY
---------+---------+---------+---------+---------+---------+---------+-
WEA2TRAN I2TRAN1 1 4
N
WEA2TRAN I2TRAN1 2 4
N
DSNE610I NUMBER OF ROWS DISPLAYED IS 2

TB_CARD INDEX_CARD NAME CREATOR NAME
---------+---------+---------+---------+---------+---------+------
199818566 199818566 WEA2TRAN DISETS I2TRAN1
199818566 199818566 WEA2TRAN DISETS I2TRAN2
199818566 199818566 WEA2TRAN DISETS I2TRAN3
199818566 199818566 WEA2TRAN DISETS I2TRAN4
199818566 106129722 WEA2TRAN DISETS I2TRAN5
199818566 199818566 WEA2TRAN DISETS I2TRAN6
199818566 34329488 WEA2TRAN DISETS I2TRAN7
DSNE610I NUMBER OF ROWS DISPLAYED IS 7

The second SQL statement:

SELECT SGJTAMRM ,MAX(SGJTRIDX)
FROM DISETS.WEA2TRAN
WHERE OWNERID = '18045'
AND SGJEVTYP = 'ADJ'
AND SGJBATCH = '0000000025'
AND SGJITEM = '474'
AND SGJTYTYP = 'CHRG'
AND SGJTRIDX < 178160
GROUP BY SGJTAMRM
FETCH FIRST ROW ONLY
FOR READ ONLY WITH UR;

The explain shows that the index is accessed only once, but a additional
sort is being performed.
When executed in batch the clock time is almost the same.
---------+---------+---------+---------+---------+---------+---------+--
TNAME ACCESSNAME TABNO MATCHCOLS INDEXONLY
---------+---------+---------+---------+---------+---------+---------+--
WEA2TRAN I2TRAN1 1 4
N
0 0
N
DSNE610I NUMBER OF ROWS DISPLAYED IS 2

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



Arlen Stovall

Re: DB2 v7.1 ZOSV1R2 SQL Performance question
(in response to Phil Grainger)
Phil, you are right. Cut and paste erorr The SQL should have been like this.

SELECT
SGJTAMRM
,SGJTRIDX
INTO
:WS-SGJTAMRM
,:WS-SGJTRIDX
FROM WEA2TRAN
WHERE OWNERID = :WEA2TRAN-SGJOWN-ID
AND SGJEVTYP = :WEA2TRAN-SGJEVTYP
AND SGJBATCH = :WEA2TRAN-SGJBATCH
AND SGJITEM = :WEA2TRAN-SGJITEM
AND SGJTYTYP = 'ALOC'
AND SGJTRIDX < :WEA2TRAN-SGJTRIDX
AND SGJTRIDX = (SELECT MAX(SGJTRIDX) FROM WEA2TRAN
WHERE OWNERID = :WEA2TRAN-SGJOWN-ID
AND SGJEVTYP = :WEA2TRAN-SGJEVTYP
AND SGJBATCH = :WEA2TRAN-SGJBATCH
AND SGJITEM = :WEA2TRAN-SGJITEM
AND SGJTYTYP = 'ALOC'
AND SGJTRIDX < :WEA2TRAN-SGJTRIDX)

>From: "Grainger, Phil" <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: [DB2-L] DB2 v7.1 ZOSV1R2 SQL Performance question
>Date: Fri, 31 Dec 2004 14:42:09 -0000
>
>It might be me (nearly time foi another long weekend after all) but I am
>struggling to make logical sense of the WHERE clause!
>
>Ignoring the fact that there is one too many ")" (I asume the one on the
>line WHERE OWNERID = '18045' ) shouldn't be there, but the one on line AND
>SGJTRIDX = 180000 ) should for starters...)
>
>Well, just looking at the SGJTRIDX predicates, you seem to be looking for
>rows that have SGJTRIDX < 180000 AND SGJTRIDX = (SELECT MAX(SGJTRIDX) FROM
>DISETS.WEA2TRAN
> WHERE OWNERID = '18045'
> AND SGJEVTYP = 'ADJ'
> AND SGJBATCH = '0000000025'
> AND SGJITEM = '474'
> AND SGJTYTYP = 'CHRG'
> AND SGJTRIDX = 180000 )
>
>Regardless of the perfoamnce, aren't these mutually exclusive????
>
>Phil G
>Computer Associates
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List on behalf of arlen stovall
> Sent: Fri 31/12/2004 14:16
> To: [login to unmask email]
> Cc:
> Subject: [DB2-L] DB2 v7.1 ZOSV1R2 SQL Performance question
>
>
>
> I have question about the performance of two SQL statements. The table in
> questions has 200 million records currently. The buffer pools are not
>large
> enough to hold the whole index.
> Here is the first SQL statement:
>
> SELECT SGJTAMRM ,SGJTRIDX,OWNERID, SGJEVTYP, SGJBATCH,SGJITEM,
> SGJTRIDX, SGJTYTYP
> FROM DISETS.WEA2TRAN
> WHERE OWNERID = '18045'
> AND SGJEVTYP = 'ADJ'
> AND SGJBATCH = '0000000025'
> AND SGJITEM = '474'
> AND SGJTYTYP = 'CHRG'
> AND SGJTRIDX < 180000
> AND SGJTRIDX = (SELECT MAX(SGJTRIDX) FROM DISETS.WEA2TRAN
> WHERE OWNERID = '18045' )
> AND SGJEVTYP = 'ADJ'
> AND SGJBATCH = '0000000025'
> AND SGJITEM = '474'
> AND SGJTYTYP = 'CHRG'
> AND SGJTRIDX = 180000 )
> FOR READ ONLY WITH UR ;
>
> The explain shows that the index I2tran1 is being accessed twice once for
> the first select and once for the subselect. My question is would it be
> better to access the index once and do a additional sort or to access the
> index twice.
>
> ---------+---------+---------+---------+---------+---------+---------+-
> TNAME ACCESSNAME TABNO MATCHCOLS INDEXONLY
> ---------+---------+---------+---------+---------+---------+---------+-
> WEA2TRAN I2TRAN1 1 4
> N
> WEA2TRAN I2TRAN1 2 4
> N
> DSNE610I NUMBER OF ROWS DISPLAYED IS 2
>
> TB_CARD INDEX_CARD NAME CREATOR NAME
> ---------+---------+---------+---------+---------+---------+------
> 199818566 199818566 WEA2TRAN DISETS I2TRAN1
> 199818566 199818566 WEA2TRAN DISETS I2TRAN2
> 199818566 199818566 WEA2TRAN DISETS I2TRAN3
> 199818566 199818566 WEA2TRAN DISETS I2TRAN4
> 199818566 106129722 WEA2TRAN DISETS I2TRAN5
> 199818566 199818566 WEA2TRAN DISETS I2TRAN6
> 199818566 34329488 WEA2TRAN DISETS I2TRAN7
> DSNE610I NUMBER OF ROWS DISPLAYED IS 7
>
> The second SQL statement:
>
> SELECT SGJTAMRM ,MAX(SGJTRIDX)
> FROM DISETS.WEA2TRAN
> WHERE OWNERID = '18045'
> AND SGJEVTYP = 'ADJ'
> AND SGJBATCH = '0000000025'
> AND SGJITEM = '474'
> AND SGJTYTYP = 'CHRG'
> AND SGJTRIDX < 178160
> GROUP BY SGJTAMRM
> FETCH FIRST ROW ONLY
> FOR READ ONLY WITH UR;
>
> The explain shows that the index is accessed only once, but a additional
> sort is being performed.
> When executed in batch the clock time is almost the same.
> ---------+---------+---------+---------+---------+---------+---------+--
> TNAME ACCESSNAME TABNO MATCHCOLS INDEXONLY
> ---------+---------+---------+---------+---------+---------+---------+--
> WEA2TRAN I2TRAN1 1 4
> N
> 0
>0
> N
> DSNE610I NUMBER OF ROWS DISPLAYED IS 2
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
>select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm
>
>
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm