Another SQL question

KATHY JONES

Another SQL question
Sorry in advance for the long email.

I have a developer working on an SQL that I feel is not taking the correct index. It is a 3 table join and when I do the explain, it tells me it is taking index XSASI30D instead of XSASI30C which I thought it should take.

The indexes are defined as follows:

XSASI30B Schoolnum, SEctionID Unique, Clusterratio 100 num levels 3 Firstkeycard 100 Fullkeycard 112,994

XSASI30C Schoolnum, Classlink Unique Clusterration 73 Num levels 3 no firstkeycar or fullkeycard stats

XSASI30D Schoolnum, course Duplicate index, Clusterration 100, num levels 3 Fullkeycard 20,849.
The join is the AMST table. We are using the C index columns to join the tables but DB2 according to the explain is using the D index. Why?
The query is as follows:
DECLARE FRENCH-CUR CURSOR FOR
SELECT A . SCHOOLNUM , A . ENTERCODE , A . PERMNUM , A . GRADE , C .
SECTIONID
FROM SSASIDB1.ASTU_STUDENT A INNER JOIN SSASIDB1.ACLS_CLASS_SCHED B
ON A . SCHOOLNUM = B . SCHOOLNUM
AND A . PERMNUM = B . PERMNUM INNER JOIN SSASIDB1.AMST_MSTR_SCHEDULE C
ON B . SCHOOLNUM = C . SCHOOLNUM
AND B . CLASSLINK = C . CLASSLINK
WHERE ( ( A . GRADE > ' 5'
AND A . ENTERCODE IN ('E1 ','E2 ','R1 ','R2 ','R3 ','R4 ',
'R5 ','R6 '))
AND ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '0680'
AND A . GRADE = ' 6')
OR (( SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '1150'
AND A . GRADE < ' 9')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '2170'
AND A . GRADE < ' 9')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '3660'
AND A . GRADE > ' 7' )
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '3674'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3 ) || SUBSTR ( SECTIONID , 6 , 1))
= '3684'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3 ) || SUBSTR(SECTIONID , 6 , 1))
= '3694'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))

= '3692'
AND A . GRADE > ' 8'))
ORDER BY A . SCHOOLNUM , C . SECTIONID , A . GRADE;

The explain shows:
Use 'S' for additional information Plan_Table Row 3 OF 4
_ Table information for: SSASIDB1.AMST_MSTR_SCHEDULE
_ Index information for: SSASIDB1.XSASI30D


Timestamp : 2002-07-17 08.53.45.88 Performance/Concurrency:
Method : 2
Plan number : 3 Table number : 3 Index Only : N
Statement : 1 Program Name : OSSQLCAT Access type : I
Query number : 1 Appl name : Match columns : 0
QBlock number : 1 Mult Index Seq : 0
Prefetch : S
Sort on New Table: Sort on Composite Table: Col Func Eval :
SortN_Unique : N SortC_Unique : N TS lock mode : S
SortN_Join : Y SortC_Join : Y Access degree : NULL
SortN_Orderby : N SortC_Orderby : N Access PGROUPID : NULL
SortN_Groupby : N SortC_Groupby : N Join degree : NULL
Join PGROUPID : NULL


It is also doing a tablespace scan on the first table instead of the index which we use in the ON Join clause but that is another problem. This cursor is used with different section ids in about 6 or more variations in the program. All are taking the 3rd index instead of the 2nd index. The first cursor took over 15 minutes to process. My aim is to improve the performance.

Kathy Jones
Central Information Services
Clark County School District
O/S390 DB2 DBA
NT DB2 DBA
702-799-5040 x366
[login to unmask email]



Eric Pearson

Re: Another SQL question
(in response to KATHY JONES)
Why 'no firstkeycar or fullkeycard stats' on the index you
want the optimizer to select?

Regards,
eric pearson
NS ITO Database Support


-----Original Message-----
From: KATHY JONES [mailto:[login to unmask email]
Sent: Wednesday, July 17, 2002 11:59 AM
To: [login to unmask email]
Subject: Another SQL question


Sorry in advance for the long email.

I have a developer working on an SQL that I feel is not taking the correct
index. It is a 3 table join and when I do the explain, it tells me it is
taking index XSASI30D instead of XSASI30C which I thought it should take.

The indexes are defined as follows:

XSASI30B Schoolnum, SEctionID Unique, Clusterratio 100 num levels 3
Firstkeycard 100 Fullkeycard 112,994

XSASI30C Schoolnum, Classlink Unique Clusterration 73 Num levels 3 no
firstkeycar or fullkeycard stats

XSASI30D Schoolnum, course Duplicate index, Clusterration 100, num levels 3
Fullkeycard 20,849.
The join is the AMST table. We are using the C index columns to join the
tables but DB2 according to the explain is using the D index. Why?
The query is as follows:
DECLARE FRENCH-CUR CURSOR FOR
SELECT A . SCHOOLNUM , A . ENTERCODE , A . PERMNUM , A . GRADE , C .
SECTIONID
FROM SSASIDB1.ASTU_STUDENT A INNER JOIN SSASIDB1.ACLS_CLASS_SCHED B
ON A . SCHOOLNUM = B . SCHOOLNUM
AND A . PERMNUM = B . PERMNUM INNER JOIN SSASIDB1.AMST_MSTR_SCHEDULE C
ON B . SCHOOLNUM = C . SCHOOLNUM
AND B . CLASSLINK = C . CLASSLINK
WHERE ( ( A . GRADE > ' 5'
AND A . ENTERCODE IN ('E1 ','E2 ','R1 ','R2 ','R3 ','R4 ',
'R5 ','R6 '))
AND ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '0680'
AND A . GRADE = ' 6')
OR (( SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '1150'
AND A . GRADE < ' 9')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '2170'
AND A . GRADE < ' 9')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '3660'
AND A . GRADE > ' 7' )
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '3674'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3 ) || SUBSTR ( SECTIONID , 6 , 1))
= '3684'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3 ) || SUBSTR(SECTIONID , 6 , 1))
= '3694'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))

= '3692'
AND A . GRADE > ' 8'))
ORDER BY A . SCHOOLNUM , C . SECTIONID , A . GRADE;

The explain shows:
Use 'S' for additional information Plan_Table Row 3 OF
4
_ Table information for: SSASIDB1.AMST_MSTR_SCHEDULE

_ Index information for: SSASIDB1.XSASI30D





Timestamp : 2002-07-17 08.53.45.88
Performance/Concurrency:
Method : 2

Plan number : 3 Table number : 3 Index Only : N

Statement : 1 Program Name : OSSQLCAT Access type : I

Query number : 1 Appl name : Match columns : 0

QBlock number : 1 Mult Index Seq : 0

Prefetch : S

Sort on New Table: Sort on Composite Table: Col Func Eval :

SortN_Unique : N SortC_Unique : N TS lock mode : S

SortN_Join : Y SortC_Join : Y Access degree :
NULL
SortN_Orderby : N SortC_Orderby : N Access PGROUPID :
NULL
SortN_Groupby : N SortC_Groupby : N Join degree :
NULL
Join PGROUPID :
NULL


It is also doing a tablespace scan on the first table instead of the index
which we use in the ON Join clause but that is another problem. This cursor
is used with different section ids in about 6 or more variations in the
program. All are taking the 3rd index instead of the 2nd index. The first
cursor took over 15 minutes to process. My aim is to improve the
performance.

Kathy Jones
Central Information Services
Clark County School District
O/S390 DB2 DBA
NT DB2 DBA
702-799-5040 x366
[login to unmask email]



http://listserv.ylassoc.com. The owners of the list can be reached at
[login to unmask email]



KATHY JONES

Re: Another SQL question
(in response to Eric Pearson)
I am a fairly new DBA so I don't know why - I even reorged the index and ran runstats and still no firstkeycard or fullkeycard stats. If runstats is not how I get firstkeycard or fullkeycard, how do I get them?

Kathy Jones
Central Information Services
Clark County School District
O/S390 DB2 DBA
NT DB2 DBA
702-799-5040 x366
[login to unmask email]

>>> [login to unmask email] 07/17/02 10:02AM >>>
Why 'no firstkeycar or fullkeycard stats' on the index you
want the optimizer to select?

Regards,
eric pearson
NS ITO Database Support


-----Original Message-----
From: KATHY JONES [mailto:[login to unmask email]
Sent: Wednesday, July 17, 2002 11:59 AM
To: [login to unmask email]
Subject: Another SQL question


Sorry in advance for the long email.

I have a developer working on an SQL that I feel is not taking the correct
index. It is a 3 table join and when I do the explain, it tells me it is
taking index XSASI30D instead of XSASI30C which I thought it should take.

The indexes are defined as follows:

XSASI30B Schoolnum, SEctionID Unique, Clusterratio 100 num levels 3
Firstkeycard 100 Fullkeycard 112,994

XSASI30C Schoolnum, Classlink Unique Clusterration 73 Num levels 3 no
firstkeycar or fullkeycard stats

XSASI30D Schoolnum, course Duplicate index, Clusterration 100, num levels 3
Fullkeycard 20,849.
The join is the AMST table. We are using the C index columns to join the
tables but DB2 according to the explain is using the D index. Why?
The query is as follows:
DECLARE FRENCH-CUR CURSOR FOR
SELECT A . SCHOOLNUM , A . ENTERCODE , A . PERMNUM , A . GRADE , C .
SECTIONID
FROM SSASIDB1.ASTU_STUDENT A INNER JOIN SSASIDB1.ACLS_CLASS_SCHED B
ON A . SCHOOLNUM = B . SCHOOLNUM
AND A . PERMNUM = B . PERMNUM INNER JOIN SSASIDB1.AMST_MSTR_SCHEDULE C
ON B . SCHOOLNUM = C . SCHOOLNUM
AND B . CLASSLINK = C . CLASSLINK
WHERE ( ( A . GRADE > ' 5'
AND A . ENTERCODE IN ('E1 ','E2 ','R1 ','R2 ','R3 ','R4 ',
'R5 ','R6 '))
AND ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '0680'
AND A . GRADE = ' 6')
OR (( SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '1150'
AND A . GRADE < ' 9')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '2170'
AND A . GRADE < ' 9')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '3660'
AND A . GRADE > ' 7' )
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '3674'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3 ) || SUBSTR ( SECTIONID , 6 , 1))
= '3684'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3 ) || SUBSTR(SECTIONID , 6 , 1))
= '3694'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))

= '3692'
AND A . GRADE > ' 8'))
ORDER BY A . SCHOOLNUM , C . SECTIONID , A . GRADE;

The explain shows:
Use 'S' for additional information Plan_Table Row 3 OF
4
_ Table information for: SSASIDB1.AMST_MSTR_SCHEDULE

_ Index information for: SSASIDB1.XSASI30D





Timestamp : 2002-07-17 08.53.45.88
Performance/Concurrency:
Method : 2

Plan number : 3 Table number : 3 Index Only : N

Statement : 1 Program Name : OSSQLCAT Access type : I

Query number : 1 Appl name : Match columns : 0

QBlock number : 1 Mult Index Seq : 0

Prefetch : S

Sort on New Table: Sort on Composite Table: Col Func Eval :

SortN_Unique : N SortC_Unique : N TS lock mode : S

SortN_Join : Y SortC_Join : Y Access degree :
NULL
SortN_Orderby : N SortC_Orderby : N Access PGROUPID :
NULL
SortN_Groupby : N SortC_Groupby : N Join degree :
NULL
Join PGROUPID :
NULL


It is also doing a tablespace scan on the first table instead of the index
which we use in the ON Join clause but that is another problem. This cursor
is used with different section ids in about 6 or more variations in the
program. All are taking the 3rd index instead of the 2nd index. The first
cursor took over 15 minutes to process. My aim is to improve the
performance.

Kathy Jones
Central Information Services
Clark County School District
O/S390 DB2 DBA
NT DB2 DBA
702-799-5040 x366
[login to unmask email]



http://listserv.ylassoc.com. The owners of the list can be reached at
[login to unmask email]






Eric Pearson

Re: Another SQL question
(in response to KATHY JONES)
Try looking in FIRSTKEYCARDF and FULLKEYCARDF.

Regards,
eric pearson



-----Original Message-----
From: KATHY JONES [mailto:[login to unmask email]
Sent: Wednesday, July 17, 2002 1:37 PM
To: [login to unmask email]
Subject: Re: Another SQL question


I am a fairly new DBA so I don't know why - I even reorged the index and ran
runstats and still no firstkeycard or fullkeycard stats. If runstats is
not how I get firstkeycard or fullkeycard, how do I get them?

Kathy Jones
Central Information Services
Clark County School District
O/S390 DB2 DBA
NT DB2 DBA
702-799-5040 x366
[login to unmask email]

>>> [login to unmask email] 07/17/02 10:02AM >>>
Why 'no firstkeycar or fullkeycard stats' on the index you
want the optimizer to select?

Regards,
eric pearson
NS ITO Database Support


-----Original Message-----
From: KATHY JONES [mailto:[login to unmask email]
Sent: Wednesday, July 17, 2002 11:59 AM
To: [login to unmask email]
Subject: Another SQL question


Sorry in advance for the long email.

I have a developer working on an SQL that I feel is not taking the correct
index. It is a 3 table join and when I do the explain, it tells me it is
taking index XSASI30D instead of XSASI30C which I thought it should take.

The indexes are defined as follows:

XSASI30B Schoolnum, SEctionID Unique, Clusterratio 100 num levels 3
Firstkeycard 100 Fullkeycard 112,994

XSASI30C Schoolnum, Classlink Unique Clusterration 73 Num levels 3 no
firstkeycar or fullkeycard stats

XSASI30D Schoolnum, course Duplicate index, Clusterration 100, num levels 3
Fullkeycard 20,849.
The join is the AMST table. We are using the C index columns to join the
tables but DB2 according to the explain is using the D index. Why?
The query is as follows:
DECLARE FRENCH-CUR CURSOR FOR
SELECT A . SCHOOLNUM , A . ENTERCODE , A . PERMNUM , A . GRADE , C .
SECTIONID
FROM SSASIDB1.ASTU_STUDENT A INNER JOIN SSASIDB1.ACLS_CLASS_SCHED B
ON A . SCHOOLNUM = B . SCHOOLNUM
AND A . PERMNUM = B . PERMNUM INNER JOIN SSASIDB1.AMST_MSTR_SCHEDULE C
ON B . SCHOOLNUM = C . SCHOOLNUM
AND B . CLASSLINK = C . CLASSLINK
WHERE ( ( A . GRADE > ' 5'
AND A . ENTERCODE IN ('E1 ','E2 ','R1 ','R2 ','R3 ','R4 ',
'R5 ','R6 '))
AND ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '0680'
AND A . GRADE = ' 6')
OR (( SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '1150'
AND A . GRADE < ' 9')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '2170'
AND A . GRADE < ' 9')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '3660'
AND A . GRADE > ' 7' )
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '3674'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3 ) || SUBSTR ( SECTIONID , 6 , 1))
= '3684'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3 ) || SUBSTR(SECTIONID , 6 , 1))
= '3694'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))

= '3692'
AND A . GRADE > ' 8'))
ORDER BY A . SCHOOLNUM , C . SECTIONID , A . GRADE;

The explain shows:
Use 'S' for additional information Plan_Table Row 3 OF
4
_ Table information for: SSASIDB1.AMST_MSTR_SCHEDULE

_ Index information for: SSASIDB1.XSASI30D





Timestamp : 2002-07-17 08.53.45.88
Performance/Concurrency:
Method : 2

Plan number : 3 Table number : 3 Index Only : N

Statement : 1 Program Name : OSSQLCAT Access type : I

Query number : 1 Appl name : Match columns : 0

QBlock number : 1 Mult Index Seq : 0

Prefetch : S

Sort on New Table: Sort on Composite Table: Col Func Eval :

SortN_Unique : N SortC_Unique : N TS lock mode : S

SortN_Join : Y SortC_Join : Y Access degree :
NULL
SortN_Orderby : N SortC_Orderby : N Access PGROUPID :
NULL
SortN_Groupby : N SortC_Groupby : N Join degree :
NULL
Join PGROUPID :
NULL


It is also doing a tablespace scan on the first table instead of the index
which we use in the ON Join clause but that is another problem. This cursor
is used with different section ids in about 6 or more variations in the
program. All are taking the 3rd index instead of the 2nd index. The first
cursor took over 15 minutes to process. My aim is to improve the
performance.

Kathy Jones
Central Information Services
Clark County School District
O/S390 DB2 DBA
NT DB2 DBA
702-799-5040 x366
[login to unmask email]



http://listserv.ylassoc.com. The owners of the list can be reached at
[login to unmask email]








http://listserv.ylassoc.com. The owners of the list can be reached at
[login to unmask email]



Bernd Oppolzer

Re: SQL question
(in response to Eric Pearson)
Hi Dave and Terry,

thanks for your answers.

Dave's solution is the kind of trick I was looking for. But in fact, in the
meantime, I wrote a C function that does it in the way I've outlined yesterday.
The result has to be put in an ISPF table, and I can manage it that way.
The user does not see the C looping behind it. Works ok for me, and I can stop
after getting 200 records, which is ok for our users. The queries are index
only, no sort, so I have 2 seconds or less. This is not batch, but TSO dialog,
so I need such response times.

Now for your suggestions:

I looked at the explain output, and Dave's trick helped to make better use of
the indexes on table T2 (I-access with matchcols 2 instead of 1). The
materializing results should be smaller.

But all the statements I tried (also Inner Join and Full Join, both with
different results) lead to materialization of both sides, followed by a merge
scan join.

For the full join, suggested by Terry, I had to append another where clause
outside the join (where xxx is not NULL), because otherwise lots of records come
from the right side, which don't appear on the left side, but I don't need
them. So, I guess, the full join is again transformed to a left join. The
explains are the same in both cases.

Thanks again

Bernd



Am Mit, 17 Jul 2002 schrieben Sie:
> Bernd,
> I'd be interested in finding out how Terry's solution worked for
> you. But, here's another solution that may work for you that I have used
> in processes here.
> select t1.key1, t1.key2, count1, coalesce(count2,0)
> from
> (select key1, key2, count(*) as count1
> from table1
> where key1 like :hostvar
> group by key1, key2) as t1
> left join
> (select key1, key2, count(*) as count2
> from table2 T2
> ,table1 T1
> where T1.key1 like :hostvar
> and T1.key1 = T2.key1
> and T1.key2 = T2.key2
> and another_col between :hv1 and :hv2
> group by T2.key1, T2.key2) as t2
> on t1.key1 = t2.key1
> and t1.key2 = t2.key2;
>
>
>
> Dave Nance
> First Health Services, Corp.
> (804)527-6841
>



> >>> [login to unmask email] 07/16/02 05:35PM >>>
> Hello DB2-Listers,
>
> I had a SQL problem today and couldn't find a simple solution.
>
> This statement would do it right, but it takes too much resources:
>
> select t1.key1, t1.key2, count1, coalesce(count2,0)
> from
> (select key1, key2, count(*) as count1
> from table1
> where key1 like :hostvar
> group by key1, key2) as t1
> left join
> (select key1, key2, count(*) as count2
> from table2
> where key1 like :hostvar
> and another_col between :hv1 and :hv2
> group by key1, key2) as t2
> on t1.key1 = t2.key1
> and t1.key2 = t2.key2;
>

...



KATHY JONES

Re: Another SQL question
(in response to Bernd Oppolzer)
After looking in the sysibm table directly instead of using the PRF query, the firstkeycard is 100 and 112994 for the B index. So any reasons why it is taking the C index over the B index?

Kathy Jones
Central Information Services
Clark County School District
O/S390 DB2 DBA
NT DB2 DBA
702-799-5040 x366
[login to unmask email]

>>> [login to unmask email] 07/17/02 10:02AM >>>
Why 'no firstkeycar or fullkeycard stats' on the index you
want the optimizer to select?

Regards,
eric pearson
NS ITO Database Support


-----Original Message-----
From: KATHY JONES [mailto:[login to unmask email]
Sent: Wednesday, July 17, 2002 11:59 AM
To: [login to unmask email]
Subject: Another SQL question


Sorry in advance for the long email.

I have a developer working on an SQL that I feel is not taking the correct
index. It is a 3 table join and when I do the explain, it tells me it is
taking index XSASI30D instead of XSASI30C which I thought it should take.

The indexes are defined as follows:

XSASI30B Schoolnum, SEctionID Unique, Clusterratio 100 num levels 3
Firstkeycard 100 Fullkeycard 112,994

XSASI30C Schoolnum, Classlink Unique Clusterration 73 Num levels 3 no
firstkeycar or fullkeycard stats

XSASI30D Schoolnum, course Duplicate index, Clusterration 100, num levels 3
Fullkeycard 20,849.
The join is the AMST table. We are using the C index columns to join the
tables but DB2 according to the explain is using the D index. Why?
The query is as follows:
DECLARE FRENCH-CUR CURSOR FOR
SELECT A . SCHOOLNUM , A . ENTERCODE , A . PERMNUM , A . GRADE , C .
SECTIONID
FROM SSASIDB1.ASTU_STUDENT A INNER JOIN SSASIDB1.ACLS_CLASS_SCHED B
ON A . SCHOOLNUM = B . SCHOOLNUM
AND A . PERMNUM = B . PERMNUM INNER JOIN SSASIDB1.AMST_MSTR_SCHEDULE C
ON B . SCHOOLNUM = C . SCHOOLNUM
AND B . CLASSLINK = C . CLASSLINK
WHERE ( ( A . GRADE > ' 5'
AND A . ENTERCODE IN ('E1 ','E2 ','R1 ','R2 ','R3 ','R4 ',
'R5 ','R6 '))
AND ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '0680'
AND A . GRADE = ' 6')
OR (( SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '1150'
AND A . GRADE < ' 9')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '2170'
AND A . GRADE < ' 9')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '3660'
AND A . GRADE > ' 7' )
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '3674'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3 ) || SUBSTR ( SECTIONID , 6 , 1))
= '3684'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3 ) || SUBSTR(SECTIONID , 6 , 1))
= '3694'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))

= '3692'
AND A . GRADE > ' 8'))
ORDER BY A . SCHOOLNUM , C . SECTIONID , A . GRADE;

The explain shows:
Use 'S' for additional information Plan_Table Row 3 OF
4
_ Table information for: SSASIDB1.AMST_MSTR_SCHEDULE

_ Index information for: SSASIDB1.XSASI30D





Timestamp : 2002-07-17 08.53.45.88
Performance/Concurrency:
Method : 2

Plan number : 3 Table number : 3 Index Only : N

Statement : 1 Program Name : OSSQLCAT Access type : I

Query number : 1 Appl name : Match columns : 0

QBlock number : 1 Mult Index Seq : 0

Prefetch : S

Sort on New Table: Sort on Composite Table: Col Func Eval :

SortN_Unique : N SortC_Unique : N TS lock mode : S

SortN_Join : Y SortC_Join : Y Access degree :
NULL
SortN_Orderby : N SortC_Orderby : N Access PGROUPID :
NULL
SortN_Groupby : N SortC_Groupby : N Join degree :
NULL
Join PGROUPID :
NULL


It is also doing a tablespace scan on the first table instead of the index
which we use in the ON Join clause but that is another problem. This cursor
is used with different section ids in about 6 or more variations in the
program. All are taking the 3rd index instead of the 2nd index. The first
cursor took over 15 minutes to process. My aim is to improve the
performance.

Kathy Jones
Central Information Services
Clark County School District
O/S390 DB2 DBA
NT DB2 DBA
702-799-5040 x366
[login to unmask email]



http://listserv.ylassoc.com. The owners of the list can be reached at
[login to unmask email]






James Campbell

Re: Another SQL question
(in response to KATHY JONES)
Kathy,

Fullkeycard for indexes B and D disagree. Try running a full runstats and seeing what
happens.

RUNSTATS TABLESPACE <whatever> TABLE(SSASIDB1.AMST_MSTR_SCHEDULE)
INDEX(ALL) SHRLEVEL CHANGE

For this purpose there is no need to, but you might for direct access to this table
want to add
COLUMN( Schoolnum, SEctionID, Classlink, course)



James Campbell

> -----Original Message-----
> From: KATHY JONES [mailto:[login to unmask email]
> Sent: Wednesday, July 17, 2002 11:59 AM
> To: [login to unmask email]
> Subject: Another SQL question
>
>
> Sorry in advance for the long email.
>
> I have a developer working on an SQL that I feel is not taking the correct
> index. It is a 3 table join and when I do the explain, it tells me it is
> taking index XSASI30D instead of XSASI30C which I thought it should take.
>
> The indexes are defined as follows:
>
> XSASI30B Schoolnum, SEctionID Unique, Clusterratio 100 num levels 3
> Firstkeycard 100 Fullkeycard 112,994
>
> XSASI30C Schoolnum, Classlink Unique Clusterration 73 Num levels 3 no
> firstkeycar or fullkeycard stats
>
> XSASI30D Schoolnum, course Duplicate index, Clusterration 100, num levels 3
> Fullkeycard 20,849.

<rest snipped>
James A Campbell



Terry Purcell

Re: SQL question
(in response to James Campbell)
Bernd,

Sorry about the dud answer. I was thinking that maybe table2 was a child of
table1 and thus a full join would give you the same result. The full join
was to force a merge scan join as it was implied from your first mail that
you were repeatedly scanning (nested loop).

Anyway, following on from David's reply, and in an effort to reduce
materialization. Try creating an index on key1, key2 on table1 (and if you
want indexonly on the 2nd table, add another_col to the key1,key2 index on
table2).

Then try something like:

select t1.key1, t1.key2, min(count1), sum(case when t2.key1 is null then 0
else 1 end) as count2
from
(select key1, key2, count(*) as count1
from table1
where key1 like :hostvar
group by key1, key2) as t1
left join table2 t2
on t1.key1 = t2.key1
and t1.key2 = t2.key2
and another_col between :hv1 and :hv2
group by t1.key1, t2.key2
fetch first 200 rows;

It may take a bit of playing around but I have gotten this type of query in
V7 to avoid materialization (I recall you are V7). There is another
alternative using correlated NTEs, but requires PQ61024.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Bernd Oppolzer
Sent: Wednesday, July 17, 2002 2:36 PM
To: [login to unmask email]
Subject: Re: SQL question


Hi Dave and Terry,

thanks for your answers.

Dave's solution is the kind of trick I was looking for. But in fact, in the
meantime, I wrote a C function that does it in the way I've outlined
yesterday.
The result has to be put in an ISPF table, and I can manage it that way.
The user does not see the C looping behind it. Works ok for me, and I can
stop
after getting 200 records, which is ok for our users. The queries are index
only, no sort, so I have 2 seconds or less. This is not batch, but TSO
dialog,
so I need such response times.

Now for your suggestions:

I looked at the explain output, and Dave's trick helped to make better use
of
the indexes on table T2 (I-access with matchcols 2 instead of 1). The
materializing results should be smaller.

But all the statements I tried (also Inner Join and Full Join, both with
different results) lead to materialization of both sides, followed by a
merge
scan join.

For the full join, suggested by Terry, I had to append another where clause
outside the join (where xxx is not NULL), because otherwise lots of records
come
from the right side, which don't appear on the left side, but I don't need
them. So, I guess, the full join is again transformed to a left join. The
explains are the same in both cases.

Thanks again

Bernd



Am Mit, 17 Jul 2002 schrieben Sie:
> Bernd,
> I'd be interested in finding out how Terry's solution worked for
> you. But, here's another solution that may work for you that I have used
> in processes here.
> select t1.key1, t1.key2, count1, coalesce(count2,0)
> from
> (select key1, key2, count(*) as count1
> from table1
> where key1 like :hostvar
> group by key1, key2) as t1
> left join
> (select key1, key2, count(*) as count2
> from table2 T2
> ,table1 T1
> where T1.key1 like :hostvar
> and T1.key1 = T2.key1
> and T1.key2 = T2.key2
> and another_col between :hv1 and :hv2
> group by T2.key1, T2.key2) as t2
> on t1.key1 = t2.key1
> and t1.key2 = t2.key2;
>
>
>
> Dave Nance
> First Health Services, Corp.
> (804)527-6841
>



> >>> [login to unmask email] 07/16/02 05:35PM >>>
> Hello DB2-Listers,
>
> I had a SQL problem today and couldn't find a simple solution.
>
> This statement would do it right, but it takes too much resources:
>
> select t1.key1, t1.key2, count1, coalesce(count2,0)
> from
> (select key1, key2, count(*) as count1
> from table1
> where key1 like :hostvar
> group by key1, key2) as t1
> left join
> (select key1, key2, count(*) as count2
> from table2
> where key1 like :hostvar
> and another_col between :hv1 and :hv2
> group by key1, key2) as t2
> on t1.key1 = t2.key1
> and t1.key2 = t2.key2;
>

...








Terry Purcell

Re: Another SQL question
(in response to Terry Purcell)
Kathy,

The problem maybe due to the clusterratio of C. Trying setting it to 81 or
above. You could also try for index-only by adding section_id to the C index
(but that wont guarantee uniqueness on the first 2 columns).

A few other points:
- Is table B redundant or mandatory to ensure only certain schoolnum values
are returned?
- You can only use an index on the first table accessed if you have local
predicates that can be applied to the leading index columns, such as
entercode and/or grade.
- You may wish to promote stage 2 SUBSTR predicates to stage 1 predicates
using LIKE such as
((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1)) = '0680' becomes
LIKE '_068_0%'
- You can also consolidate some of the OR conditions, (GRADE < ' 9' AND
.....) OR (GRADE < ' 9' AND .....) can become (GRADE < ' 9' AND (..OR...)

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of KATHY JONES
Sent: Wednesday, July 17, 2002 10:59 AM
To: [login to unmask email]
Subject: Another SQL question


Sorry in advance for the long email.

I have a developer working on an SQL that I feel is not taking the correct
index. It is a 3 table join and when I do the explain, it tells me it is
taking index XSASI30D instead of XSASI30C which I thought it should take.

The indexes are defined as follows:

XSASI30B Schoolnum, SEctionID Unique, Clusterratio 100 num levels 3
Firstkeycard 100 Fullkeycard 112,994

XSASI30C Schoolnum, Classlink Unique Clusterration 73 Num levels 3 no
firstkeycar or fullkeycard stats

XSASI30D Schoolnum, course Duplicate index, Clusterration 100, num levels 3
Fullkeycard 20,849.
The join is the AMST table. We are using the C index columns to join the
tables but DB2 according to the explain is using the D index. Why?
The query is as follows:
DECLARE FRENCH-CUR CURSOR FOR
SELECT A . SCHOOLNUM , A . ENTERCODE , A . PERMNUM , A . GRADE , C .
SECTIONID
FROM SSASIDB1.ASTU_STUDENT A INNER JOIN SSASIDB1.ACLS_CLASS_SCHED B
ON A . SCHOOLNUM = B . SCHOOLNUM
AND A . PERMNUM = B . PERMNUM INNER JOIN SSASIDB1.AMST_MSTR_SCHEDULE C
ON B . SCHOOLNUM = C . SCHOOLNUM
AND B . CLASSLINK = C . CLASSLINK
WHERE ( ( A . GRADE > ' 5'
AND A . ENTERCODE IN ('E1 ','E2 ','R1 ','R2 ','R3 ','R4 ',
'R5 ','R6 '))
AND ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '0680'
AND A . GRADE = ' 6')
OR (( SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '1150'
AND A . GRADE < ' 9')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '2170'
AND A . GRADE < ' 9')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '3660'
AND A . GRADE > ' 7' )
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))
= '3674'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3 ) || SUBSTR ( SECTIONID , 6 , 1))
= '3684'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3 ) || SUBSTR(SECTIONID , 6 , 1))
= '3694'
AND A . GRADE > ' 7')
OR ((SUBSTR(SECTIONID , 2 , 3) || SUBSTR(SECTIONID , 6 , 1))

= '3692'
AND A . GRADE > ' 8'))
ORDER BY A . SCHOOLNUM , C . SECTIONID , A . GRADE;

The explain shows:
Use 'S' for additional information Plan_Table Row 3 OF
4
_ Table information for: SSASIDB1.AMST_MSTR_SCHEDULE
_ Index information for: SSASIDB1.XSASI30D


Timestamp : 2002-07-17 08.53.45.88
Performance/Concurrency:
Method : 2
Plan number : 3 Table number : 3 Index Only : N
Statement : 1 Program Name : OSSQLCAT Access type : I
Query number : 1 Appl name : Match columns : 0
QBlock number : 1 Mult Index Seq : 0
Prefetch : S
Sort on New Table: Sort on Composite Table: Col Func Eval :
SortN_Unique : N SortC_Unique : N TS lock mode : S
SortN_Join : Y SortC_Join : Y Access degree :
NULL
SortN_Orderby : N SortC_Orderby : N Access PGROUPID :
NULL
SortN_Groupby : N SortC_Groupby : N Join degree :
NULL
Join PGROUPID :
NULL


It is also doing a tablespace scan on the first table instead of the index
which we use in the ON Join clause but that is another problem. This cursor
is used with different section ids in about 6 or more variations in the
program. All are taking the 3rd index instead of the 2nd index. The first
cursor took over 15 minutes to process. My aim is to improve the
performance.

Kathy Jones
Central Information Services
Clark County School District
O/S390 DB2 DBA
NT DB2 DBA
702-799-5040 x366
[login to unmask email]

================






Bernd Oppolzer

Re: SQL question
(in response to Terry Purcell)
Hello Terry,

many thanks for the answer.

First, I'd like to repeat, that "in real life" I found a solution which works OK
(with C programming implied). So this is only an item of interest and DB2
discussions.

Indeed, the solution you suggested does no materialization on table2, which
is very good. But the left side subquery does materialize, and I don't
understand, why. I'll switch to my real examples, to show you some strange
effects.

==> SELECT PRFPAKET, EINSATZ, COUNT(*) AS ANZPRUEF
==> FROM LS91DVZ.VLDV196U
==> WHERE PRFPAKET LIKE 'A%'
==> AND MAILSTATUS BETWEEN 100 AND 899
==> GROUP BY PRFPAKET, EINSATZ;

*** SELECT-Anweisung gestartet um 17:29:43
*** SELECT-Anweisung beendet um 17:29:44
*** Anzahl ausgegebene Saetze: 00006498

This is the left side subquery. There are 6498 result rows in 1 second, which
is very good, but explain shows a sort due to group by. I don't unterstand
this, because there is an index on PRFPAKET and EINSATZ, which, in my opinion,
should be used for the LIKE condition and for the GROUP BY. The column
MAILSTATUS is not part of any index. Or, is this combination of index usage not
possible ? The index has additional columns, but PRFPAKET is the first and
EINSATZ the second. Explain show index usage for the LIKE (matchcols 1), but the
result is sorted anyway for the GROUP BY. Maybe matchcols 1 is the problem.

But, so far, so good.

The statement suggested by Dave goes like this:

==> SELECT U.PRFPAKET, U.EINSATZ, ANZPRUEF, ANZOBJ
==> FROM (SELECT PRFPAKET, EINSATZ, COUNT(*) AS ANZPRUEF
==> FROM LS91DVZ.VLDV196U
==> WHERE PRFPAKET LIKE 'A%'
==> AND MAILSTATUS BETWEEN 100 AND 899
==> GROUP BY PRFPAKET, EINSATZ) AS U
==> LEFT JOIN
==> (SELECT A.GEVO, A.EINSATZ, COUNT(*) AS ANZOBJ
==> FROM LS91DVZ.VFREIGABE A,
==> LS91DVZ.VLDV196U B
==> WHERE B.EINSATZ = A.EINSATZ
==> AND B.PRFPAKET = A.GEVO
==> AND B.PRFPAKET LIKE 'A%'
==> AND MAILSTATUS BETWEEN 100 AND 899
==> AND FREISTAT BETWEEN ' 500' AND ' 879'
==> GROUP BY A.GEVO, A.EINSATZ) AS V
==> ON U.EINSATZ = V.EINSATZ
==> AND U.PRFPAKET = V.GEVO;

*** SELECT-Anweisung gestartet um 17:29:27
*** SELECT-Anweisung beendet um 17:29:30
*** Anzahl ausgegebene Saetze: 00006498

Performance is OK for me (3 seconds), but the ordering of the rows coming from
the left side is not preserved. I guess, this is due to the merge scan join
implied.

Now the statement you suggested (first without "first 200 rows only"):

==> SELECT U.PRFPAKET, U.EINSATZ, MIN(ANZPRUEF),
==> SUM (CASE WHEN V.AUFTRAGSNR IS NULL THEN 0 ELSE 1 END)
==> AS ANZOBJ
==> FROM (SELECT PRFPAKET, EINSATZ, COUNT(*) AS ANZPRUEF
==> FROM LS91DVZ.VLDV196U
==> WHERE PRFPAKET LIKE 'A%'
==> AND MAILSTATUS BETWEEN 100 AND 899
==> GROUP BY PRFPAKET, EINSATZ) AS U
==> LEFT JOIN
==> LS91DVZ.VFREIGABE V
==> ON U.EINSATZ = V.EINSATZ
==> AND U.PRFPAKET = V.GEVO
==> AND V.FREISTAT BETWEEN ' 500' AND ' 879'
==> GROUP BY U.PRFPAKET, U.EINSATZ;

*** SELECT-Anweisung gestartet um 17:29:41
*** SELECT-Anweisung beendet um 17:29:43
*** Anzahl ausgegebene Saetze: 00006498

Works great (2 seconds), and I get the ordering I want due to the final GROUP
BY. The left side is materializing, but the right side doesn't. This is
exactly the kind of solution I was looking for. I wasn't able to find it
myself, because I'm still used to V4, where ON conditions with predicates only
for one side (as in V.FREISTAT BETWEEN ...) were not allowed. So thank you
again for pointing this out.

But now something strange happens with the new "fetch first n rows" clause.
I can see no difference in the explain; maybe "fetch first n rows" is ignored
by explain ? But the elapsed time is much longer, although I wanted (and
got) only the first 200 rows. Why ? I have no idea.

==> SELECT U.PRFPAKET, U.EINSATZ, MIN(ANZPRUEF),
==> SUM (CASE WHEN V.AUFTRAGSNR IS NULL THEN 0 ELSE 1 END)
==> AS ANZOBJ
==> FROM (SELECT PRFPAKET, EINSATZ, COUNT(*) AS ANZPRUEF
==> FROM LS91DVZ.VLDV196U
==> WHERE PRFPAKET LIKE 'A%'
==> AND MAILSTATUS BETWEEN 100 AND 899
==> GROUP BY PRFPAKET, EINSATZ) AS U
==> LEFT JOIN
==> LS91DVZ.VFREIGABE V
==> ON U.EINSATZ = V.EINSATZ
==> AND U.PRFPAKET = V.GEVO
==> AND V.FREISTAT BETWEEN ' 500' AND ' 879'
==> GROUP BY U.PRFPAKET, U.EINSATZ
==> FETCH FIRST 200 ROWS ONLY;

*** SELECT-Anweisung gestartet um 17:29:32
*** SELECT-Anweisung beendet um 17:29:41
*** Anzahl ausgegebene Saetze: 00000200

Do you have an explanation for this ? Maybe some kind of prefetch does not
happen in the limited version, but explain shows no difference.

After doing this testing, I realized, that the access to the right table
(VFREIGABE) is not indexonly any more, because I used the column AUFTRAGSNR
in the SELECT list (by accident; this is the primary key but it's not part of
the index involved). So I changed this column (I can use any column, because
I simply need to count the NULL and NOT NULL results).

After this change, the "first 200 rows only" statement was faster than the
full statement; the access to the right table is indexonly.

In explain, the only difference I saw was the change of INDEXONLY from N to Y.

So there are two remaining questions:

- why does the first statement on the top of this mail do an explicit sort for
GROUP BY ?

- why is the limited query (first 200 rows only) in the 4th example above much
slower than the query for the full result ?

Kind regards

Bernd




Am Don, 18 Jul 2002 schrieben Sie:
> Bernd,
>

...

>
> Anyway, following on from David's reply, and in an effort to reduce
> materialization. Try creating an index on key1, key2 on table1 (and if you
> want indexonly on the 2nd table, add another_col to the key1,key2 index on
> table2).
>
> Then try something like:
>
> select t1.key1, t1.key2, min(count1), sum(case when t2.key1 is null then 0
> else 1 end) as count2
> from
> (select key1, key2, count(*) as count1
> from table1
> where key1 like :hostvar
> group by key1, key2) as t1
> left join table2 t2
> on t1.key1 = t2.key1
> and t1.key2 = t2.key2
> and another_col between :hv1 and :hv2
> group by t1.key1, t2.key2
> fetch first 200 rows;
>
> It may take a bit of playing around but I have gotten this type of query in
> V7 to avoid materialization (I recall you are V7). There is another
> alternative using correlated NTEs, but requires PQ61024.
>
> Regards
> Terry Purcell



Terry Purcell

Re: SQL question
(in response to Bernd Oppolzer)
Bernd,

Glad you have answered your 2nd question.

As to your 1st question: - why does the first statement on the top of this
mail do an explicit sort for GROUP BY ?

Possible answers:

- Index to avoid sort is not utilized.
- Index is utilized however list prefetch is invoked.
- Could fall into the category of Walter Janissen's similar problem on DB2-L
titled "Why do I get an additional sort?". Sanjeev, Mike Vaughan and myself
attempted answers on this one but concluded that it was something that
should be brought up with IBM. I was unable to reproduce Walter's problem,
and have not tested yours.

Also, if you are V7 (which I think you are from memory), you can actually
use:
- COUNT(V.AUFTRAGSNR) instead of the SUM (CASE.......). That will save a
small amount of CPU.
- Given that you only want 200 rows (and cannot currently restrict the
materialization), you could add a stop predicate such as AND PRFPAKET <
'AE'. This should materialize about 1000 rows based upon averages, rather
than the 6500. You will need to automatically generate this stop predicate
based upon the values input to the LIKE.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Bernd Oppolzer
Sent: Thursday, July 18, 2002 10:42 AM
To: [login to unmask email]
Subject: Re: SQL question


Hello Terry,

many thanks for the answer.

First, I'd like to repeat, that "in real life" I found a solution which
works OK
(with C programming implied). So this is only an item of interest and DB2
discussions.

Indeed, the solution you suggested does no materialization on table2, which
is very good. But the left side subquery does materialize, and I don't
understand, why. I'll switch to my real examples, to show you some strange
effects.

==> SELECT PRFPAKET, EINSATZ, COUNT(*) AS ANZPRUEF
==> FROM LS91DVZ.VLDV196U
==> WHERE PRFPAKET LIKE 'A%'
==> AND MAILSTATUS BETWEEN 100 AND 899
==> GROUP BY PRFPAKET, EINSATZ;

*** SELECT-Anweisung gestartet um 17:29:43
*** SELECT-Anweisung beendet um 17:29:44
*** Anzahl ausgegebene Saetze: 00006498

This is the left side subquery. There are 6498 result rows in 1 second,
which
is very good, but explain shows a sort due to group by. I don't unterstand
this, because there is an index on PRFPAKET and EINSATZ, which, in my
opinion,
should be used for the LIKE condition and for the GROUP BY. The column
MAILSTATUS is not part of any index. Or, is this combination of index usage
not
possible ? The index has additional columns, but PRFPAKET is the first and
EINSATZ the second. Explain show index usage for the LIKE (matchcols 1), but
the
result is sorted anyway for the GROUP BY. Maybe matchcols 1 is the problem.

But, so far, so good.

The statement suggested by Dave goes like this:

==> SELECT U.PRFPAKET, U.EINSATZ, ANZPRUEF, ANZOBJ
==> FROM (SELECT PRFPAKET, EINSATZ, COUNT(*) AS ANZPRUEF
==> FROM LS91DVZ.VLDV196U
==> WHERE PRFPAKET LIKE 'A%'
==> AND MAILSTATUS BETWEEN 100 AND 899
==> GROUP BY PRFPAKET, EINSATZ) AS U
==> LEFT JOIN
==> (SELECT A.GEVO, A.EINSATZ, COUNT(*) AS ANZOBJ
==> FROM LS91DVZ.VFREIGABE A,
==> LS91DVZ.VLDV196U B
==> WHERE B.EINSATZ = A.EINSATZ
==> AND B.PRFPAKET = A.GEVO
==> AND B.PRFPAKET LIKE 'A%'
==> AND MAILSTATUS BETWEEN 100 AND 899
==> AND FREISTAT BETWEEN ' 500' AND ' 879'
==> GROUP BY A.GEVO, A.EINSATZ) AS V
==> ON U.EINSATZ = V.EINSATZ
==> AND U.PRFPAKET = V.GEVO;

*** SELECT-Anweisung gestartet um 17:29:27
*** SELECT-Anweisung beendet um 17:29:30
*** Anzahl ausgegebene Saetze: 00006498

Performance is OK for me (3 seconds), but the ordering of the rows coming
from
the left side is not preserved. I guess, this is due to the merge scan join
implied.

Now the statement you suggested (first without "first 200 rows only"):

==> SELECT U.PRFPAKET, U.EINSATZ, MIN(ANZPRUEF),
==> SUM (CASE WHEN V.AUFTRAGSNR IS NULL THEN 0 ELSE 1 END)
==> AS ANZOBJ
==> FROM (SELECT PRFPAKET, EINSATZ, COUNT(*) AS ANZPRUEF
==> FROM LS91DVZ.VLDV196U
==> WHERE PRFPAKET LIKE 'A%'
==> AND MAILSTATUS BETWEEN 100 AND 899
==> GROUP BY PRFPAKET, EINSATZ) AS U
==> LEFT JOIN
==> LS91DVZ.VFREIGABE V
==> ON U.EINSATZ = V.EINSATZ
==> AND U.PRFPAKET = V.GEVO
==> AND V.FREISTAT BETWEEN ' 500' AND ' 879'
==> GROUP BY U.PRFPAKET, U.EINSATZ;

*** SELECT-Anweisung gestartet um 17:29:41
*** SELECT-Anweisung beendet um 17:29:43
*** Anzahl ausgegebene Saetze: 00006498

Works great (2 seconds), and I get the ordering I want due to the final
GROUP
BY. The left side is materializing, but the right side doesn't. This is
exactly the kind of solution I was looking for. I wasn't able to find it
myself, because I'm still used to V4, where ON conditions with predicates
only
for one side (as in V.FREISTAT BETWEEN ...) were not allowed. So thank you
again for pointing this out.

But now something strange happens with the new "fetch first n rows" clause.
I can see no difference in the explain; maybe "fetch first n rows" is
ignored
by explain ? But the elapsed time is much longer, although I wanted (and
got) only the first 200 rows. Why ? I have no idea.

==> SELECT U.PRFPAKET, U.EINSATZ, MIN(ANZPRUEF),
==> SUM (CASE WHEN V.AUFTRAGSNR IS NULL THEN 0 ELSE 1 END)
==> AS ANZOBJ
==> FROM (SELECT PRFPAKET, EINSATZ, COUNT(*) AS ANZPRUEF
==> FROM LS91DVZ.VLDV196U
==> WHERE PRFPAKET LIKE 'A%'
==> AND MAILSTATUS BETWEEN 100 AND 899
==> GROUP BY PRFPAKET, EINSATZ) AS U
==> LEFT JOIN
==> LS91DVZ.VFREIGABE V
==> ON U.EINSATZ = V.EINSATZ
==> AND U.PRFPAKET = V.GEVO
==> AND V.FREISTAT BETWEEN ' 500' AND ' 879'
==> GROUP BY U.PRFPAKET, U.EINSATZ
==> FETCH FIRST 200 ROWS ONLY;

*** SELECT-Anweisung gestartet um 17:29:32
*** SELECT-Anweisung beendet um 17:29:41
*** Anzahl ausgegebene Saetze: 00000200

Do you have an explanation for this ? Maybe some kind of prefetch does not
happen in the limited version, but explain shows no difference.

After doing this testing, I realized, that the access to the right table
(VFREIGABE) is not indexonly any more, because I used the column AUFTRAGSNR
in the SELECT list (by accident; this is the primary key but it's not part
of
the index involved). So I changed this column (I can use any column, because
I simply need to count the NULL and NOT NULL results).

After this change, the "first 200 rows only" statement was faster than the
full statement; the access to the right table is indexonly.

In explain, the only difference I saw was the change of INDEXONLY from N to
Y.

So there are two remaining questions:

- why does the first statement on the top of this mail do an explicit sort
for
GROUP BY ?

- why is the limited query (first 200 rows only) in the 4th example above
much
slower than the query for the full result ?

Kind regards

Bernd



Al Pat

SQL question
(in response to Terry Purcell)

What is the expected result for the following sql ?.

SELECT INDIVIDUAL, COL_1, COL_2 FROM TABLE_T A

WHERE INDIVIDUAL = 9999

AND UPDATED_DATE =

(SELECT MAX(UPDATED_DATE) FROM TABLE_T B

WHERE A.INDIVIDUAL = B.INDIVIDUAL

AND A.MATCH_CD = 'SS' )

Our programmer mistyped B.MATCH_CD as A.MATCH_CD and the program returned the expected rows in the TEST environment because of which the error was not noticed. When the program was run in production environment it gave sqlcode 100 even though there were records with MATCH_CD as SS.



---------------------------------
Do You Yahoo!?
Yahoo! Health - Feel better, live better

Terry Purcell

Re: SQL question
(in response to Al Pat)
Al,

The subquery will return the maximum updated_date for an individual, but
only if the row for the outer query has a MATCH_CD = 'SS'. Thus the query
will only return a row when the MATCH_CD 'SS' row, also has the max
UPDATED_DATE. If there is another record out there with a higher
UPDATED_DATE, and MATCH_CD <> 'SS', then no row will be returned.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Al Pat
Sent: Wednesday, July 24, 2002 6:11 AM
To: [login to unmask email]
Subject: SQL question


What is the expected result for the following sql ?.

SELECT INDIVIDUAL, COL_1, COL_2 FROM TABLE_T A
WHERE INDIVIDUAL = 9999
AND UPDATED_DATE =
(SELECT MAX(UPDATED_DATE) FROM TABLE_T B
WHERE A.INDIVIDUAL = B.INDIVIDUAL
AND A.MATCH_CD = 'SS' )

Our programmer mistyped B.MATCH_CD as A.MATCH_CD and the program returned
the expected rows in the TEST environment because of which the error was not
noticed. When the program was run in production environment it gave sqlcode
100 even though there were records with MATCH_CD as SS.




Do You Yahoo!?
Yahoo! Health - Feel better, live better



Jeff L'Italien

Re: SQL question
(in response to Terry Purcell)
Al,

A colleague of mine and I spoke about this and even ran some tests as a
conclusion. I think that this works the way that it was intended. Since there
can potentially be multiple rows for a given individual id, I suspect that the
optimizer will only qualify one row in the outer query to use within the
correlated subquery. So let's say that the row which is selected by the
outquery is NOT a row with a MATCH_CD value of 'SS'. What would happen is that
the result set from the correlated subquery would be a null value for your date
field, and thus, would not match the row selected from the outer query. I'd be
willing to bet that the reason it worked in test was that there was either only
one 'SS' row for a given individual id OR that the row containing the most
recent row was an 'SS' row. The reason it works using B.MATCH_CD = 'SS' is due
to the fact that you have limited the qualifying rows within the correlated
subquery to those having this value.

Regards,
Jeff L'Italien
American Express



From: "Al Pat" <[login to unmask email]>@LISTSERV.YLASSOC.COM> on 07/24/2002 04:10
AM

Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>

Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: SQL question




What is the expected result for the following sql ?.

SELECT INDIVIDUAL, COL_1, COL_2  FROM  TABLE_T  A

WHERE INDIVIDUAL = 9999

AND UPDATED_DATE =

(SELECT MAX(UPDATED_DATE) FROM TABLE_T B

WHERE A.INDIVIDUAL = B.INDIVIDUAL

AND A.MATCH_CD = 'SS' )

Our programmer mistyped B.MATCH_CD as A.MATCH_CD and the program returned the
expected rows in the TEST environment because of which the error was not
noticed. When the program was run in production environment it gave sqlcode 100
even though there were records with MATCH_CD as SS.



Do You Yahoo!?
Yahoo! Health - Feel better, live better




Michele Payton

SQL question
(in response to Jeff L'Italien)
Listers,

I am trying to avoid using a LIKE in my SQL statement because it is taking
too long to run. My situation is I have a char (5) column in the format of
123-4. The first three characters are integers and are unique. I would
like to be able to search using just these three numbers. Is there a
column function that will mask the last two characters allowing me to
search on the first three? It would be something like this maybe:

SELECT COL1 FROM TAB1
WHERE <column function>(COL1) = '123'

thanks in advance

Michele Payton



Jeff L'Italien

Re: SQL question
(in response to Michele Payton)
Michele,

Unfortunately, I don't think scalar functions are indexable, thus, a
non-matching index scan or tablespace scan may be deployed.

Regards,
Jeff L'Italien
American Express



From: "Michele Payton" <[login to unmask email]>@LISTSERV.YLASSOC.COM> on 08/13/2002
10:56 AM CST

Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>

Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: SQL question


Listers,

I am trying to avoid using a LIKE in my SQL statement because it is taking
too long to run. My situation is I have a char (5) column in the format of
123-4. The first three characters are integers and are unique. I would
like to be able to search using just these three numbers. Is there a
column function that will mask the last two characters allowing me to
search on the first three? It would be something like this maybe:

SELECT COL1 FROM TAB1
WHERE <column function>(COL1) = '123'

thanks in advance

Michele Payton








Venu Varma

Re: SQL question
(in response to Michael Phillips)
Michele,

Use SUBSTR function.

Select col1 from tab1
where substr(col1,1,3) = '123'

Venu

-----Original Message-----
From: Michele Payton [mailto:[login to unmask email]
Sent: Tuesday, August 13, 2002 11:57 AM
To: [login to unmask email]
Subject: SQL question


Listers,

I am trying to avoid using a LIKE in my SQL statement because it is taking
too long to run. My situation is I have a char (5) column in the format of
123-4. The first three characters are integers and are unique. I would
like to be able to search using just these three numbers. Is there a
column function that will mask the last two characters allowing me to
search on the first three? It would be something like this maybe:

SELECT COL1 FROM TAB1
WHERE <column function>(COL1) = '123'

thanks in advance

Michele Payton








John W. Herbold

Re: SQL question
(in response to Jeff L'Italien)
Yes the substring function...

SELECT COL1 FROM TAB1
WHERE SUBSTR(COL1,1,3)='123';

But I warn you that the LIKE will be faster!

Thanks,

John W. Herbold Jr.
IS Specialist/DBA


-----Original Message-----
From: Michele Payton [mailto:[login to unmask email]
Sent: Tuesday, August 13, 2002 11:57 AM
To: [login to unmask email]
Subject: SQL question

Listers,

I am trying to avoid using a LIKE in my SQL statement because it is taking
too long to run. My situation is I have a char (5) column in the format of
123-4. The first three characters are integers and are unique. I would
like to be able to search using just these three numbers. Is there a
column function that will mask the last two characters allowing me to
search on the first three? It would be something like this maybe:

SELECT COL1 FROM TAB1
WHERE <column function>(COL1) = '123'

thanks in advance

Michele Payton








Michael Phillips

Re: SQL question
(in response to John W. Herbold)
Michele,
The substr function will work, but I don't know that it will be any more
efficient.

SELECT * FROM TABLE
WHERE SUBSTR(COLUMN,1,3) = '123';

Mike

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Michele Payton
Sent: Tuesday, August 13, 2002 12:57 PM
To: [login to unmask email]
Subject: SQL question

Listers,

I am trying to avoid using a LIKE in my SQL statement because it is taking
too long to run. My situation is I have a char (5) column in the format of
123-4. The first three characters are integers and are unique. I would
like to be able to search using just these three numbers. Is there a
column function that will mask the last two characters allowing me to
search on the first three? It would be something like this maybe:

SELECT COL1 FROM TAB1
WHERE <column function>(COL1) = '123'

thanks in advance

Michele Payton








Dave Nance

Re: SQL question
(in response to Venu Varma)
Michele,
Doing a column function, such as a substr, is going to be a stage 2
predicate, and therefore even slower. Using a like predicate should not
be making the SQL very slow as you have indicated. The LIKE is stage one
and indexable as long as you are giving the first character(s). I would
tend to look at the rest of your statement and the explain. Is this
column part of an index? Are you matching columns on index access,
etc...? Do you have good runstats on the table with FREQVALs? If this
column is the first column in an index you will only get matchcols = 1
and index screening on any subsequent indexed columns. A solution that
has been put forth on the list in the past, is to add a column to the
table that contains the first part of the column you are after and put
that column in your index, should work since you say they are unique.

Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 08/13/02 12:56PM >>>
Listers,

I am trying to avoid using a LIKE in my SQL statement because it is
taking
too long to run. My situation is I have a char (5) column in the
format of
123-4. The first three characters are integers and are unique. I
would
like to be able to search using just these three numbers. Is there a
column function that will mask the last two characters allowing me to
search on the first three? It would be something like this maybe:

SELECT COL1 FROM TAB1
WHERE <column function>(COL1) = '123'

thanks in advance

Michele Payton



visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of
the list can
This message, including any attachments,is intended solely for the use of
the named recipient(s) and may contain confidential and/or privileged
information. Any unauthorized review, use, disclosure or distribution of
this communications is expressly prohibited. If you are not the intended
recipient,please contact the sender by reply e-mail and destroy any and
all copies of the original message.
Thank you.



Bayard Tysor

Re: SQL question
(in response to Dave Nance)
Michele,

SUBSTR(COL1,1,3) = '123' will work, but I don't know
if it will perform any better than COL1 LIKE '123%'.
In fact, it might be worse.

Tink
--- Michele Payton <[login to unmask email]> wrote:
> Listers,
>
> I am trying to avoid using a LIKE in my SQL
> statement because it is taking
> too long to run. My situation is I have a char (5)
> column in the format of
> 123-4. The first three characters are integers and
> are unique. I would
> like to be able to search using just these three
> numbers. Is there a
> column function that will mask the last two
> characters allowing me to
> search on the first three? It would be something
> like this maybe:
>
> SELECT COL1 FROM TAB1
> WHERE <column function>(COL1) = '123'
>
> thanks in advance
>
> Michele Payton
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://listserv.ylassoc.com. The owners of the list
> can be reached at
[login to unmask email]


=====
B.L. "Tink" Tysor
Bayard Lee Tysor, Inc
(401)965-2688
www.BLTysor.com



Bakul Naik

Re: SQL question
(in response to Bayard Tysor)
Michele,

You can try this, define two host variables in the program and plug in
low-values and high-values to the each variable. Use group field as host
variable in where condition using between clause.


Example: ( using cobol program)

01 WS-HOST1.
05 WS-HOST-VAR1 PIC X(3) VALUE SPACES
05 FILLER PIC X(2) VALUE LOW-VALUES.


01 WS-HOST2.
05 WS-HOST-VAR2 PIC X(3) VALUE SPACES
05 FILLER PIC X(2) VALUE HIGH-VALUES.

MOVE '123' TO WS-HOST-VAR1
WS-HOST-VAR2.

SELECT COL1 FROM TAB1
WHERE col1 BETWEEN :WS-HOST1 AND
:WS-HOST2


Thanks
Bakul Naik

-----Original Message-----
From: Michele Payton [mailto:[login to unmask email]
Sent: Tuesday, August 13, 2002 12:57 PM
To: [login to unmask email]
Subject: SQL question

Listers,

I am trying to avoid using a LIKE in my SQL statement because it is taking
too long to run. My situation is I have a char (5) column in the format of
123-4. The first three characters are integers and are unique. I would
like to be able to search using just these three numbers. Is there a
column function that will mask the last two characters allowing me to
search on the first three? It would be something like this maybe:

SELECT COL1 FROM TAB1
WHERE <column function>(COL1) = '123'

thanks in advance

Michele Payton








Fred Sobotka

Re: SQL question
(in response to Bakul Naik)
>The first three characters are integers and are unique.
>I would like to be able to search using just these three numbers.

If I am correct, you may get better performance by simply adding an index on the
CHAR(5) column and using
WHERE COL1 LIKE '123%'
which would still use the index, because it is effectively a "begins with"
predicate. Others here can tell you exactly how "sargable" that predicate is
with the new index.

Good luck,

Fred



Michele Payton

Re: SQL question
(in response to Fred Sobotka)
Listers,

Thank you so much for all your help. There was an index on that column, but
the person in charge of this database had not used runstats on it, so it
was not choosing the best access path. I think we're OK now.

Michele Payton



Partha Pratim (Cognizant) Biswas

Re: SQL question
(in response to Michele Payton)
Hi Fred,
Why are you suggesting this solution? How does it going to effect the
performance?

I am a new DB2 learner.. and curious to know the reason ..please excuse
me for this silly question.

Thanks
Partha

-----Original Message-----
From: Fred Sobotka [mailto:[login to unmask email]
Sent: Wednesday, August 14, 2002 4:43 AM
To: [login to unmask email]
Subject: Re: SQL question

>The first three characters are integers and are unique.
>I would like to be able to search using just these three numbers.

If I am correct, you may get better performance by simply adding an index on
the
CHAR(5) column and using
WHERE COL1 LIKE '123%'
which would still use the index, because it is effectively a "begins with"
predicate. Others here can tell you exactly how "sargable" that predicate is
with the new index.

Good luck,

Fred








Hello

SQL question
(in response to Partha Pratim (Cognizant) Biswas)
I have this table (resulted from an union of select '-sto(', ... and
select '-sta db(',....)

---------+---------+---------+---
COL1 COL2
---------+---------+---------+---
-STO DB(BBXDES02) SP(BBI0501)
-STO DB(BBXDES02) SP(BBI9991)
-STO DB(DVXDEG01) SP(DVI0161)
-STO DB(DVXDEI01) SP(DVI0161)
-STA DB(BBXDES02) SP(BBI0501)
-STA DB(BBXDES02) SP(BBI9991)
-STA DB(DVXDEG01) SP(DVI0161)
-STA DB(DVXDEI01) SP(DVI0161)


i would like to sort the result so i have the STO of a tablespace followed
by immediately a STA of this tablespace , i took hours but do not achieve ,
can you help me please .

SELECT
'-STO DB('||STRIP(A.DBNAME)||')' AS COL1,
'SP('||STRIP(A.NAME)||')' AS COL2

FROM TEMPTAB A, TEMPTABF B
WHERE A.NAME = B.TSNAME
AND A.CREATOR = B.DBNAME ;



David S Waugh

Re: SQL question
(in response to Hello)
Finally! An SQL question I can actually answer! Woo Hoo!

Christophe: Try this (it's a little crude, but it works)...

SELECT
' -STO ', 'DB('||STRIP(A.DBNAME)||')' AS COL2,
'SP('||STRIP(A.NAME)||')' AS COL3
FROM TEMPTAB A, TEMPTABF B
WHERE A.NAME = B.TSNAME
AND A.CREATOR = B.DBNAME
UNION
SELECT
'-STA ', 'DB('||STRIP(A.DBNAME)||')' AS COL2,
'SP('||STRIP(A.NAME)||')' AS COL3
FROM TEMPTAB A, TEMPTABF B
WHERE A.NAME = B.TSNAME
AND A.CREATOR = B.DBNAME
ORDER BY COL2, COL3;

-STO and -STA become a column of their own, the rest is COL2 & COL3. Note that the " -STO " is shifted to the right one position from the
"-STA ", so the output looks like this when the query executes:

-STO DB(BBXDES02) SP(BBI0501)
-STA DB(BBXDES02) SP(BBI0501)
-STO DB(BBXDES02) SP(BBI9991)
-STA DB(BBXDES02) SP(BBI9991)
-STO DB(DVXDEG01) SP(DVI0161)
-STA DB(DVXDEG01) SP(DVI0161)
-STO DB(DVXDEI01) SP(DVI0161)
-STA DB(DVXDEI01) SP(DVI0161)

If you don't shift -STO, the output comes out with -STA first, followed by -STO, which is backwards from what you want. The DB2 command processor doesn't care what column the commands start in, so this should work for you.

HTH

David Waugh, NCW
DSW Consulting & Services
"Giving consultants a bad name since 1993"
===
Creative BUMPER STICKERS (from www.dumbbumpers.com)
===
Fleece on earth, good wool to ewe.

---------- Christophe Radier <[login to unmask email]> writes:

From: Christophe Radier <[login to unmask email]>
To: [login to unmask email]
Subject: SQL question
Date: Tue, 24 Dec 2002 09:49:25 -0600

I have this table (resulted from an union of select '-sto(', ... and
select '-sta db(',....)

---------+---------+---------+---
COL1 COL2
---------+---------+---------+---
-STO DB(BBXDES02) SP(BBI0501)
-STO DB(BBXDES02) SP(BBI9991)
-STO DB(DVXDEG01) SP(DVI0161)
-STO DB(DVXDEI01) SP(DVI0161)
-STA DB(BBXDES02) SP(BBI0501)
-STA DB(BBXDES02) SP(BBI9991)
-STA DB(DVXDEG01) SP(DVI0161)
-STA DB(DVXDEI01) SP(DVI0161)


i would like to sort the result so i have the STO of a tablespace followed
by immediately a STA of this tablespace , i took hours but do not achieve ,
can you help me please .

SELECT
'-STO DB('||STRIP(A.DBNAME)||')' AS COL1,
'SP('||STRIP(A.NAME)||')' AS COL2

FROM TEMPTAB A, TEMPTABF B
WHERE A.NAME = B.TSNAME
AND A.CREATOR = B.DBNAME ;






Dave Nance

Re: SQL question
(in response to David S Waugh)
I accidentally deleted the original mail.

This is in response to how to get the -STA immediately after the -STO commands. Use the following

ORDER BY 2, 1 DESC

This orders the results by your spacenam, then the descending on the command puts the stop command first.

Dave Nance
First Health Services, Corp.
(804)527-6841

This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.

Hello

Re: SQL question
(in response to Dave Nance)
Dear David S Waugh and David Nance,

thank you for your replies , i forgot the possibility to order on more than
1 column !!
David S Waugh : why you don't have column 1 ordered ? it is a forgetting
isn't it ?



Neil Courtney

Re: SQL question
(in response to Hello)
Christophe,
try this:

SELECT COL1, COL2 FROM
(SELECT SUBSTR(COL1,1,4) AS S1,
SUBSTR(COL1,9,8) AS S2,
COL1 AS C1,
COL2 AS C2
FROM TEMP
) AS TMP1
ORDER BY S2, S1 DESC


Cheers,
Neil Courtney
IS Consultant
IT Services / Legacy Systems
TelstraClear
mailto:[login to unmask email]
Ph. 64 09 912-4927
"MG - Life's too short not to"


-----Original Message-----
From: Christophe Radier [mailto:[login to unmask email]
Sent: Wednesday, December 25, 2002 4:49 AM
To: [login to unmask email]
Subject: SQL question


I have this table (resulted from an union of select '-sto(', ... and
select '-sta db(',....)

---------+---------+---------+---
COL1 COL2
---------+---------+---------+---
-STO DB(BBXDES02) SP(BBI0501)
-STO DB(BBXDES02) SP(BBI9991)
-STO DB(DVXDEG01) SP(DVI0161)
-STO DB(DVXDEI01) SP(DVI0161)
-STA DB(BBXDES02) SP(BBI0501)
-STA DB(BBXDES02) SP(BBI9991)
-STA DB(DVXDEG01) SP(DVI0161)
-STA DB(DVXDEI01) SP(DVI0161)


i would like to sort the result so i have the STO of a tablespace followed
by immediately a STA of this tablespace , i took hours but do not achieve ,
can you help me please .

SELECT
'-STO DB('||STRIP(A.DBNAME)||')' AS COL1,
'SP('||STRIP(A.NAME)||')' AS COL2

FROM TEMPTAB A, TEMPTABF B
WHERE A.NAME = B.TSNAME
AND A.CREATOR = B.DBNAME ;