Re: Poor Performance from join logical files

A review of the data [by SELECT * FROM QTEMP/COUNTS] produced by the following script may assist to better grasp possible data reduction in the join. The script only provides information about the first file. A similar script can be performed on the other files, creating a specific /counts/ file and choosing each of the fields in the join and selection [and optionally those in the format]:

create table qtemp/counts
(rqs char(15)
,Total int
,cde int
,co int
,br int
,dv int
,fly int
,fle int
,fsx int
,TAG int
,SQ# int
,DLQ int
,asn int
,ckd int
)
;

insert into qtemp/counts
select cast('No Selection ' as char(15)) as rqs
,count(*) as Total
,count(distinct tk@cde) as cde
,count(distinct tk@co ) as co
,count(distinct tk@br ) as br
,count(distinct tk@dv ) as dv
,count(distinct tk@fly) as fly
,count(distinct tk@fle) as fle
,count(distinct tk@fsx) as fsx
,count(distinct TK@TAG) as TAG
,count(distinct TK@SQ#) as SQ#
,count(distinct TK@DLQ) as DLQ
,count(distinct TK@ASN) as asn
,count(distinct TK@CKD) as ckd
from tk@mst
;

insert into qtemp/counts
select cast('tag=/b ' as char(15)) as rqs
,count(*) as Total
,count(distinct tk@cde) as cde
,count(distinct tk@co ) as co
,count(distinct tk@br ) as br
,count(distinct tk@dv ) as dv
,count(distinct tk@fly) as fly
,count(distinct tk@fle) as fle
,count(distinct tk@fsx) as fsx
,count(distinct TK@TAG) as TAG
,count(distinct TK@SQ#) as SQ#
,count(distinct TK@DLQ) as DLQ
,count(distinct TK@ASN) as asn
,count(distinct TK@CKD) as ckd
from tk@mst
where tk@tag = ''
;

insert into qtemp/counts
select cast('tag+sq#¬0 ' as char(15)) as rqs
,count(*) as Total
,count(distinct tk@cde) as cde
,count(distinct tk@co ) as co
,count(distinct tk@br ) as br
,count(distinct tk@dv ) as dv
,count(distinct tk@fly) as fly
,count(distinct tk@fle) as fle
,count(distinct tk@fsx) as fsx
,count(distinct TK@TAG) as TAG
,count(distinct TK@SQ#) as SQ#
,count(distinct TK@DLQ) as DLQ
,count(distinct TK@ASN) as asn
,count(distinct TK@CKD) as ckd
from tk@mst
where tk@tag = ''
and tk@sq# <> 0
;

insert into qtemp/counts
select cast('tag+sq#+dlq¬0 ' as char(15)) as rqs
,count(*) as Total
,count(distinct tk@cde) as cde
,count(distinct tk@co ) as co
,count(distinct tk@br ) as br
,count(distinct tk@dv ) as dv
,count(distinct tk@fly) as fly
,count(distinct tk@fle) as fle
,count(distinct tk@fsx) as fsx
,count(distinct TK@TAG) as TAG
,count(distinct TK@SQ#) as SQ#
,count(distinct TK@DLQ) as DLQ
,count(distinct TK@ASN) as asn
,count(distinct TK@CKD) as ckd
from tk@mst
where tk@tag = ''
and tk@sq# <> 0
and tk@dlq <> 0
;

Using grouped counts across the keys, is more informative to see the actual values being joined; i.e. correlating a count to a specific [key] value.

Regards, Chuck