Z/OS V10 Access Path Issue

Dave Nance

Z/OS V10 Access Path Issue
Hi all,   While waiting on folks here to get me a PMR, thought I'd see if I could get any suggestions. Thanks for any help.
I have an SQL statement that runs for over 46 minutes, butif I influence the access path by add a concat ‘’, the query runs in less than3 seconds. Looking at the explains, I see why optimizer selects the much cheaper looking path, but the reality is that the more expensive path is MUCH cheaper. The onlydifference between the two queries is I changed:     andnme           like 'UNI%'   To:    and nme || ''    like 'UNI%'    This gets DB2 to not consider this column forindex access matching on the column and to decide to use the index I want it touse.Original Query:Query execution time => 46 min: 45 s: 799 ms Changed Query:Query execution time => 2 s: 815 ms I have collected more in-depth Statistics. Here are thelatest parms I used trying to give DB2 better info on these tables/indexes. Ihave tried about every trick I can think of to improve this query withoutrewriting, which is what the application team wants. Not sure if I should trysomething different or not to get the correct access path. I am looking atlying to DB2 to get the access path I want, by updating the index statisticsmanually.LISTDEF TSLIST       INCLUDETABLESPACE  DPRSPCUE.BCUEPSCD       INCLUDETABLESPACE  DPRSPCUE.BCUEPSPJ       INCLUDETABLESPACE  DPRSPPRS.BPRSP000       INCLUDETABLESPACE  DPRSPPRS.BPRSPP06       INCLUDETABLESPACE  DPRSPPRS.BPRSPPUB RUNSTATS TABLESPACE LIST TSLIST      TABLE(ALL)               INDEX ALL      KEYCARD             FREQVAL NUMCOLS  1 COUNT 40             FREQVAL NUMCOLS  2 COUNT 40             FREQVAL NUMCOLS  3 COUNT 40             FREQVAL NUMCOLS  4 COUNT 40             FREQVAL NUMCOLS  5 COUNT 40      UPDATE ALL FORCEROLLUP YES      SHRLEVEL CHANGE SORTNUM 25SORTDEVT DISK      HISTORY ALL  Original SQL:select distinct   nme || ovflw as aka_nme
     , name
     , co_name
     , acct_no
     , seq_no
     , cd
     , soc_nme
     , ind
     , code
     , lib_ind
     , type_name
     , detail_ind
     , acct_desc
     , case when seq_no = 0 then tot_cnt
            else null
       end                 as tot_cnt
     , case when seq_no = 0 then ttl_cnt
            else null
       end                 as ttl_cnt 
   from   shwcue_d cue
join shw_part
    on cue.nbr         = spj_nbr     
join pwn_nme ipname
    on part_nbr            = acct_no
join affiliatn
    on ipname.affl_cd = affl_cd
left join publisher
    on acct_no         = ipname.acct_no
left join acct_type
    on ipname.acct_type    = acct_type   
where nbr in (1624910043, 1624910021
, 1624910073, 1624910048, 1624910100, 1624910105, 1624910085, 1624910104
, 1624910121, 1624910063, 1624910074, 1624910023, 1624910106, 1624910101
, 1624910111, 1624910128, 1624910029, 1624910032, 1624910026, 1624910064
, 1624910049, 1624910097, 1624910125, 1624910086, 1624910008, 1624910110
, 1624910027, 1624910102, 1624910013, 1624910087, 1624910044, 1624910124
, 1624910075, 1624910076, 1624910112, 1624910011, 1624910051, 1624910050
, 1624910017, 1624910007, 1624910016, 1624910091, 1624910047, 1624910129
, 1624910113, 1624910077, 1624910130, 1624910033, 1624910030, 1624910065
, 1624910066, 1624910067, 1624910109, 1624910041, 1624910025, 1624910022
, 1624910114, 1624910092, 1624910052, 1624910042, 1624910034, 1624910035
, 1624910093, 1624910053, 1624910054, 1624910058, 1624910103, 1624910090
, 1624910118, 1624910119, 1624910096, 1624910095, 1624910122, 1624910115
, 1624910036, 1624910001, 1624910088, 1624910098, 1624910004, 1624910046
, 1624910037, 1624910031, 1624910059, 1624910055, 1624910015, 1624910019
, 1624910079, 1624910116, 1624910056, 1624910123, 1624910045, 1624910038
, 1624910080, 1624910006, 1624910099, 1624910117, 1624910012, 1624910084
, 1624910083, 1624910082, 1624910068, 1624910070, 1624910003, 1624910081
, 1624910108, 1624910018, 1624910071, 1624910126, 1624910020, 1624910127
, 1624910107, 1624910094, 1624910072, 1624910028, 1624910005, 1624910069
, 1624910002, 1624910057, 1624910039, 1624910060, 1624910009, 1624910089
, 1624910120, 1624910024, 1624910040, 1624910014, 1624910078, 1624910062
, 1624910131, 1624910061, 1624910010 )
Altered to get the desired access path:select distinct   nme || ovflw as aka_nme
     , name
     , co_name
     , acct_no
     , seq_no
     , cd
     , soc_nme
     , ind
     , code
     , lib_ind
     , type_name
     , detail_ind
     , acct_desc
     , case when seq_no = 0 then tot_cnt
            else null
       end                 as tot_cnt
     , case when seq_no = 0 then ttl_cnt
            else null
       end                 as ttl_cnt 
   from   shwcue_d cue
join shw_part
    on cue.nbr         = spj_nbr     
join pwn_nme ipname
    on part_nbr            = acct_no   and nme concat '' like 'UNI%'
join affiliatn
    on ipname.affl_cd = affl_cd
left join publisher
    on acct_no         = ipname.acct_no
left join acct_type
    on ipname.acct_type    = acct_type   
where nbr in (1624910043, 1624910021
, 1624910073, 1624910048, 1624910100, 1624910105, 1624910085, 1624910104
, 1624910121, 1624910063, 1624910074, 1624910023, 1624910106, 1624910101
, 1624910111, 1624910128, 1624910029, 1624910032, 1624910026, 1624910064
, 1624910049, 1624910097, 1624910125, 1624910086, 1624910008, 1624910110
, 1624910027, 1624910102, 1624910013, 1624910087, 1624910044, 1624910124
, 1624910075, 1624910076, 1624910112, 1624910011, 1624910051, 1624910050
, 1624910017, 1624910007, 1624910016, 1624910091, 1624910047, 1624910129
, 1624910113, 1624910077, 1624910130, 1624910033, 1624910030, 1624910065
, 1624910066, 1624910067, 1624910109, 1624910041, 1624910025, 1624910022
, 1624910114, 1624910092, 1624910052, 1624910042, 1624910034, 1624910035
, 1624910093, 1624910053, 1624910054, 1624910058, 1624910103, 1624910090
, 1624910118, 1624910119, 1624910096, 1624910095, 1624910122, 1624910115
, 1624910036, 1624910001, 1624910088, 1624910098, 1624910004, 1624910046
, 1624910037, 1624910031, 1624910059, 1624910055, 1624910015, 1624910019
, 1624910079, 1624910116, 1624910056, 1624910123, 1624910045, 1624910038
, 1624910080, 1624910006, 1624910099, 1624910117, 1624910012, 1624910084
, 1624910083, 1624910082, 1624910068, 1624910070, 1624910003, 1624910081
, 1624910108, 1624910018, 1624910071, 1624910126, 1624910020, 1624910127
, 1624910107, 1624910094, 1624910072, 1624910028, 1624910005, 1624910069
, 1624910002, 1624910057, 1624910039, 1624910060, 1624910009, 1624910089
, 1624910120, 1624910024, 1624910040, 1624910014, 1624910078, 1624910062
, 1624910131, 1624910061, 1624910010 ) 

Dave Nance

Walter Janißen

AW: Z/OS V10 Access Path Issue
(in response to Dave Nance)
Hi Dave

If you are on V11 you could look at the recommended runstats and collect these. Then check, if the access path is the one you prefer.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Dave Nance [mailto:[login to unmask email]
Gesendet: Donnerstag, 11. Mai 2017 15:42
An: Daniel L. Luksetich
Betreff: [DB2-L] - Z/OS V10 Access Path Issue

Hi all,
While waiting on folks here to get me a PMR, thought I'd see if I could get any suggestions. Thanks for any help.

I have an SQL statement that runs for over 46 minutes, but if I influence the access path by add a concat ‘’, the query runs in less than 3 seconds. Looking at the explains, I see why optimizer selects the much cheaper looking path, but the reality is that the more expensive path is MUCH cheaper. The only difference between the two queries is I changed:

and nme like 'UNI%'

To:
and nme || '' like 'UNI%'

This gets DB2 to not consider this column for index access matching on the column and to decide to use the index I want it to use.
Original Query:
Query execution time => 46 min: 45 s: 799 ms

Changed Query:
Query execution time => 2 s: 815 ms

I have collected more in-depth Statistics. Here are the latest parms I used trying to give DB2 better info on these tables/indexes. I have tried about every trick I can think of to improve this query without rewriting, which is what the application team wants. Not sure if I should try something different or not to get the correct access path. I am looking at lying to DB2 to get the access path I want, by updating the index statistics manually.
LISTDEF TSLIST
INCLUDE TABLESPACE DPRSPCUE.BCUEPSCD
INCLUDE TABLESPACE DPRSPCUE.BCUEPSPJ
INCLUDE TABLESPACE DPRSPPRS.BPRSP000
INCLUDE TABLESPACE DPRSPPRS.BPRSPP06
INCLUDE TABLESPACE DPRSPPRS.BPRSPPUB

RUNSTATS TABLESPACE LIST TSLIST
TABLE(ALL)
INDEX ALL
KEYCARD
FREQVAL NUMCOLS 1 COUNT 40
FREQVAL NUMCOLS 2 COUNT 40
FREQVAL NUMCOLS 3 COUNT 40
FREQVAL NUMCOLS 4 COUNT 40
FREQVAL NUMCOLS 5 COUNT 40
UPDATE ALL FORCEROLLUP YES
SHRLEVEL CHANGE SORTNUM 25 SORTDEVT DISK
HISTORY ALL

Original SQL:
select distinct nme || ovflw as aka_nme
, name
, co_name
, acct_no
, seq_no
, cd
, soc_nme
, ind
, code
, lib_ind
, type_name
, detail_ind
, acct_desc
, case when seq_no = 0 then tot_cnt
else null
end as tot_cnt
, case when seq_no = 0 then ttl_cnt
else null
end as ttl_cnt
from shwcue_d cue
join shw_part
on cue.nbr = spj_nbr
join pwn_nme ipname
on part_nbr = acct_no
join affiliatn
on ipname.affl_cd = affl_cd
left join publisher
on acct_no = ipname.acct_no
left join acct_type
on ipname.acct_type = acct_type
where nbr in (1624910043, 1624910021
, 1624910073, 1624910048, 1624910100, 1624910105, 1624910085, 1624910104
, 1624910121, 1624910063, 1624910074, 1624910023, 1624910106, 1624910101
, 1624910111, 1624910128, 1624910029, 1624910032, 1624910026, 1624910064
, 1624910049, 1624910097, 1624910125, 1624910086, 1624910008, 1624910110
, 1624910027, 1624910102, 1624910013, 1624910087, 1624910044, 1624910124
, 1624910075, 1624910076, 1624910112, 1624910011, 1624910051, 1624910050
, 1624910017, 1624910007, 1624910016, 1624910091, 1624910047, 1624910129
, 1624910113, 1624910077, 1624910130, 1624910033, 1624910030, 1624910065
, 1624910066, 1624910067, 1624910109, 1624910041, 1624910025, 1624910022
, 1624910114, 1624910092, 1624910052, 1624910042, 1624910034, 1624910035
, 1624910093, 1624910053, 1624910054, 1624910058, 1624910103, 1624910090
, 1624910118, 1624910119, 1624910096, 1624910095, 1624910122, 1624910115
, 1624910036, 1624910001, 1624910088, 1624910098, 1624910004, 1624910046
, 1624910037, 1624910031, 1624910059, 1624910055, 1624910015, 1624910019
, 1624910079, 1624910116, 1624910056, 1624910123, 1624910045, 1624910038
, 1624910080, 1624910006, 1624910099, 1624910117, 1624910012, 1624910084
, 1624910083, 1624910082, 1624910068, 1624910070, 1624910003, 1624910081
, 1624910108, 1624910018, 1624910071, 1624910126, 1624910020, 1624910127
, 1624910107, 1624910094, 1624910072, 1624910028, 1624910005, 1624910069
, 1624910002, 1624910057, 1624910039, 1624910060, 1624910009, 1624910089
, 1624910120, 1624910024, 1624910040, 1624910014, 1624910078, 1624910062
, 1624910131, 1624910061, 1624910010 )

Altered to get the desired access path:
select distinct nme || ovflw as aka_nme
, name
, co_name
, acct_no
, seq_no
, cd
, soc_nme
, ind
, code
, lib_ind
, type_name
, detail_ind
, acct_desc
, case when seq_no = 0 then tot_cnt
else null
end as tot_cnt
, case when seq_no = 0 then ttl_cnt
else null
end as ttl_cnt
from shwcue_d cue
join shw_part
on cue.nbr = spj_nbr
join pwn_nme ipname
on part_nbr = acct_no
and nme concat '' like 'UNI%'
join affiliatn
on ipname.affl_cd = affl_cd
left join publisher
on acct_no = ipname.acct_no
left join acct_type
on ipname.acct_type = acct_type
where nbr in (1624910043, 1624910021
, 1624910073, 1624910048, 1624910100, 1624910105, 1624910085, 1624910104
, 1624910121, 1624910063, 1624910074, 1624910023, 1624910106, 1624910101
, 1624910111, 1624910128, 1624910029, 1624910032, 1624910026, 1624910064
, 1624910049, 1624910097, 1624910125, 1624910086, 1624910008, 1624910110
, 1624910027, 1624910102, 1624910013, 1624910087, 1624910044, 1624910124
, 1624910075, 1624910076, 1624910112, 1624910011, 1624910051, 1624910050
, 1624910017, 1624910007, 1624910016, 1624910091, 1624910047, 1624910129
, 1624910113, 1624910077, 1624910130, 1624910033, 1624910030, 1624910065
, 1624910066, 1624910067, 1624910109, 1624910041, 1624910025, 1624910022
, 1624910114, 1624910092, 1624910052, 1624910042, 1624910034, 1624910035
, 1624910093, 1624910053, 1624910054, 1624910058, 1624910103, 1624910090
, 1624910118, 1624910119, 1624910096, 1624910095, 1624910122, 1624910115
, 1624910036, 1624910001, 1624910088, 1624910098, 1624910004, 1624910046
, 1624910037, 1624910031, 1624910059, 1624910055, 1624910015, 1624910019
, 1624910079, 1624910116, 1624910056, 1624910123, 1624910045, 1624910038
, 1624910080, 1624910006, 1624910099, 1624910117, 1624910012, 1624910084
, 1624910083, 1624910082, 1624910068, 1624910070, 1624910003, 1624910081
, 1624910108, 1624910018, 1624910071, 1624910126, 1624910020, 1624910127
, 1624910107, 1624910094, 1624910072, 1624910028, 1624910005, 1624910069
, 1624910002, 1624910057, 1624910039, 1624910060, 1624910009, 1624910089
, 1624910120, 1624910024, 1624910040, 1624910014, 1624910078, 1624910062
, 1624910131, 1624910061, 1624910010 )



Dave Nance


-----End Original Message-----
Attachments

  • image001.png (2.6k)

Walter Jani&#223;en

AW: Z/OS V10 Access Path Issue
(in response to Dave Nance)
Hi Dave

One more remark. AFAIK you runstats will collect frequency values for the first, second and so forth columns of all indexes. As you didn’t provide any index definition I cannot say, if this runstats will help you. Maybe histogram-stats for column nme will help, because then the optimizer can see that UNI% does not filter that much.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Dave Nance [mailto:[login to unmask email]
Gesendet: Donnerstag, 11. Mai 2017 15:42
An: Daniel L. Luksetich
Betreff: [DB2-L] - Z/OS V10 Access Path Issue

Hi all,
While waiting on folks here to get me a PMR, thought I'd see if I could get any suggestions. Thanks for any help.

I have an SQL statement that runs for over 46 minutes, but if I influence the access path by add a concat ‘’, the query runs in less than 3 seconds. Looking at the explains, I see why optimizer selects the much cheaper looking path, but the reality is that the more expensive path is MUCH cheaper. The only difference between the two queries is I changed:

and nme like 'UNI%'

To:
and nme || '' like 'UNI%'

This gets DB2 to not consider this column for index access matching on the column and to decide to use the index I want it to use.
Original Query:
Query execution time => 46 min: 45 s: 799 ms

Changed Query:
Query execution time => 2 s: 815 ms

I have collected more in-depth Statistics. Here are the latest parms I used trying to give DB2 better info on these tables/indexes. I have tried about every trick I can think of to improve this query without rewriting, which is what the application team wants. Not sure if I should try something different or not to get the correct access path. I am looking at lying to DB2 to get the access path I want, by updating the index statistics manually.
LISTDEF TSLIST
INCLUDE TABLESPACE DPRSPCUE.BCUEPSCD
INCLUDE TABLESPACE DPRSPCUE.BCUEPSPJ
INCLUDE TABLESPACE DPRSPPRS.BPRSP000
INCLUDE TABLESPACE DPRSPPRS.BPRSPP06
INCLUDE TABLESPACE DPRSPPRS.BPRSPPUB

RUNSTATS TABLESPACE LIST TSLIST
TABLE(ALL)
INDEX ALL
KEYCARD
FREQVAL NUMCOLS 1 COUNT 40
FREQVAL NUMCOLS 2 COUNT 40
FREQVAL NUMCOLS 3 COUNT 40
FREQVAL NUMCOLS 4 COUNT 40
FREQVAL NUMCOLS 5 COUNT 40
UPDATE ALL FORCEROLLUP YES
SHRLEVEL CHANGE SORTNUM 25 SORTDEVT DISK
HISTORY ALL

Original SQL:
select distinct nme || ovflw as aka_nme
, name
, co_name
, acct_no
, seq_no
, cd
, soc_nme
, ind
, code
, lib_ind
, type_name
, detail_ind
, acct_desc
, case when seq_no = 0 then tot_cnt
else null
end as tot_cnt
, case when seq_no = 0 then ttl_cnt
else null
end as ttl_cnt
from shwcue_d cue
join shw_part
on cue.nbr = spj_nbr
join pwn_nme ipname
on part_nbr = acct_no
join affiliatn
on ipname.affl_cd = affl_cd
left join publisher
on acct_no = ipname.acct_no
left join acct_type
on ipname.acct_type = acct_type
where nbr in (1624910043, 1624910021
, 1624910073, 1624910048, 1624910100, 1624910105, 1624910085, 1624910104
, 1624910121, 1624910063, 1624910074, 1624910023, 1624910106, 1624910101
, 1624910111, 1624910128, 1624910029, 1624910032, 1624910026, 1624910064
, 1624910049, 1624910097, 1624910125, 1624910086, 1624910008, 1624910110
, 1624910027, 1624910102, 1624910013, 1624910087, 1624910044, 1624910124
, 1624910075, 1624910076, 1624910112, 1624910011, 1624910051, 1624910050
, 1624910017, 1624910007, 1624910016, 1624910091, 1624910047, 1624910129
, 1624910113, 1624910077, 1624910130, 1624910033, 1624910030, 1624910065
, 1624910066, 1624910067, 1624910109, 1624910041, 1624910025, 1624910022
, 1624910114, 1624910092, 1624910052, 1624910042, 1624910034, 1624910035
, 1624910093, 1624910053, 1624910054, 1624910058, 1624910103, 1624910090
, 1624910118, 1624910119, 1624910096, 1624910095, 1624910122, 1624910115
, 1624910036, 1624910001, 1624910088, 1624910098, 1624910004, 1624910046
, 1624910037, 1624910031, 1624910059, 1624910055, 1624910015, 1624910019
, 1624910079, 1624910116, 1624910056, 1624910123, 1624910045, 1624910038
, 1624910080, 1624910006, 1624910099, 1624910117, 1624910012, 1624910084
, 1624910083, 1624910082, 1624910068, 1624910070, 1624910003, 1624910081
, 1624910108, 1624910018, 1624910071, 1624910126, 1624910020, 1624910127
, 1624910107, 1624910094, 1624910072, 1624910028, 1624910005, 1624910069
, 1624910002, 1624910057, 1624910039, 1624910060, 1624910009, 1624910089
, 1624910120, 1624910024, 1624910040, 1624910014, 1624910078, 1624910062
, 1624910131, 1624910061, 1624910010 )

Altered to get the desired access path:
select distinct nme || ovflw as aka_nme
, name
, co_name
, acct_no
, seq_no
, cd
, soc_nme
, ind
, code
, lib_ind
, type_name
, detail_ind
, acct_desc
, case when seq_no = 0 then tot_cnt
else null
end as tot_cnt
, case when seq_no = 0 then ttl_cnt
else null
end as ttl_cnt
from shwcue_d cue
join shw_part
on cue.nbr = spj_nbr
join pwn_nme ipname
on part_nbr = acct_no
and nme concat '' like 'UNI%'
join affiliatn
on ipname.affl_cd = affl_cd
left join publisher
on acct_no = ipname.acct_no
left join acct_type
on ipname.acct_type = acct_type
where nbr in (1624910043, 1624910021
, 1624910073, 1624910048, 1624910100, 1624910105, 1624910085, 1624910104
, 1624910121, 1624910063, 1624910074, 1624910023, 1624910106, 1624910101
, 1624910111, 1624910128, 1624910029, 1624910032, 1624910026, 1624910064
, 1624910049, 1624910097, 1624910125, 1624910086, 1624910008, 1624910110
, 1624910027, 1624910102, 1624910013, 1624910087, 1624910044, 1624910124
, 1624910075, 1624910076, 1624910112, 1624910011, 1624910051, 1624910050
, 1624910017, 1624910007, 1624910016, 1624910091, 1624910047, 1624910129
, 1624910113, 1624910077, 1624910130, 1624910033, 1624910030, 1624910065
, 1624910066, 1624910067, 1624910109, 1624910041, 1624910025, 1624910022
, 1624910114, 1624910092, 1624910052, 1624910042, 1624910034, 1624910035
, 1624910093, 1624910053, 1624910054, 1624910058, 1624910103, 1624910090
, 1624910118, 1624910119, 1624910096, 1624910095, 1624910122, 1624910115
, 1624910036, 1624910001, 1624910088, 1624910098, 1624910004, 1624910046
, 1624910037, 1624910031, 1624910059, 1624910055, 1624910015, 1624910019
, 1624910079, 1624910116, 1624910056, 1624910123, 1624910045, 1624910038
, 1624910080, 1624910006, 1624910099, 1624910117, 1624910012, 1624910084
, 1624910083, 1624910082, 1624910068, 1624910070, 1624910003, 1624910081
, 1624910108, 1624910018, 1624910071, 1624910126, 1624910020, 1624910127
, 1624910107, 1624910094, 1624910072, 1624910028, 1624910005, 1624910069
, 1624910002, 1624910057, 1624910039, 1624910060, 1624910009, 1624910089
, 1624910120, 1624910024, 1624910040, 1624910014, 1624910078, 1624910062
, 1624910131, 1624910061, 1624910010 )



Dave Nance


-----End Original Message-----
Attachments

  • image001.png (2.6k)

Dave Nance

AW: Z/OS V10 Access Path Issue
(in response to Walter Janißen)
Should have mentioned the following:
The column nme and nbr are leading columns of an index in their respective tables.
The nme column is leading column of the primary key index. First key is 2,974,450, full key is 3,249,028. The UNI% qualifies 1204 rows from this table.
The nbr column is leading column of index, not unique. First key is 3,196,562, full key is 43,020,344, there are 70,594,243 rows in table. The IN list below qualifies 5179 rows.
The thing is the tables that we join to in the middle of this query, using the nme column as access path explode the intermediate result set into the millions of rows, using the nbr column keeps us in the 10's of thousands of rows through the joins.
The final result set is only 4 rows.
    


From: "Walter Jani&#223;en" <[login to unmask email]>
To: [login to unmask email]
Sent: Thursday, May 11, 2017 9:01 AM
Subject: [DB2-L] - AW: Z/OS V10 Access Path Issue

<!--#yiv7190484696 _filtered #yiv7190484696 {font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv7190484696 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv7190484696 {font-family:Verdana;panose-1:2 11 6 4 3 5 4 4 2 4;} _filtered #yiv7190484696 {font-family:"Lucida Handwriting";panose-1:3 1 1 1 1 1 1 1 1 1;}#yiv7190484696 #yiv7190484696 p.yiv7190484696MsoNormal, #yiv7190484696 li.yiv7190484696MsoNormal, #yiv7190484696 div.yiv7190484696MsoNormal {margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New Roman", serif;}#yiv7190484696 a:link, #yiv7190484696 span.yiv7190484696MsoHyperlink {color:blue;text-decoration:underline;}#yiv7190484696 a:visited, #yiv7190484696 span.yiv7190484696MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv7190484696 p {margin-right:0cm;margin-left:0cm;font-size:12.0pt;font-family:"Times New Roman", serif;}#yiv7190484696 span.yiv7190484696E-MailFormatvorlage18 {font-family:"Arial", sans-serif;color:#1F497D;font-weight:normal;font-style:normal;}#yiv7190484696 .yiv7190484696MsoChpDefault {font-size:10.0pt;} _filtered #yiv7190484696 {margin:70.85pt 70.85pt 2.0cm 70.85pt;}#yiv7190484696 div.yiv7190484696WordSection1 {}-->Hi Dave   One more remark. AFAIK you runstats will collect frequency values for the first, second and so forth columns of all indexes. As you didn’t provide any index definition I cannot say, if this runstats will help you. Maybe histogram-stats for column nme will help, because then the optimizer can see that UNI% does not filter that much.   Kind regards
Walter Janißen 

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996   Von: Dave Nance [mailto:[login to unmask email]
Gesendet: Donnerstag, 11. Mai 2017 15:42
An: Daniel L. Luksetich
Betreff: [DB2-L] - Z/OS V10 Access Path Issue   Hi all,    While waiting on folks here to get me a PMR, thought I'd see if I could get any suggestions. Thanks for any help.   I have an SQL statement that runs for over 46 minutes, but if I influence the access path by add a concat ‘’, the query runs in less than 3 seconds. Looking at the explains, I see why optimizer selects the much cheaper looking path, but the reality is that the more expensive path is MUCH cheaper. The only difference between the two queries is I changed:        and nme            like 'UNI%'    To:      and nme|| ''     like 'UNI%'      This gets DB2 to not consider this column for index access matching on the column and to decide to use the index I want it to use. Original Query: Query execution time => 46 min: 45 s: 799 ms   Changed Query: Query execution time => 2 s: 815 ms   I have collected more in-depth Statistics. Here are the latest parms I used trying to give DB2 better info on these tables/indexes. I have tried about every trick I can think of to improve this query without rewriting, which is what the application team wants. Not sure if I should try something different or not to get the correct access path. I am looking at lying to DB2 to get the access path I want, by updating the index statistics manually. LISTDEF TSLIST        INCLUDE TABLESPACE  DPRSPCUE.BCUEPSCD        INCLUDE TABLESPACE  DPRSPCUE.BCUEPSPJ        INCLUDE TABLESPACE  DPRSPPRS.BPRSP000        INCLUDE TABLESPACE  DPRSPPRS.BPRSPP06        INCLUDE TABLESPACE  DPRSPPRS.BPRSPPUB   RUNSTATS TABLESPACE LIST TSLIST       TABLE(ALL)                 INDEX ALL       KEYCARD               FREQVAL NUMCOLS  1 COUNT 40               FREQVAL NUMCOLS  2 COUNT 40               FREQVAL NUMCOLS  3 COUNT 40               FREQVAL NUMCOLS  4 COUNT 40               FREQVAL NUMCOLS  5 COUNT 40       UPDATE ALL FORCEROLLUP YES       SHRLEVEL CHANGE SORTNUM 25 SORTDEVT DISK       HISTORY ALL    Original SQL: select distinct   nme || ovflw as aka_nme
     , name
     , co_name
     , acct_no
     , seq_no
     , cd
     , soc_nme
     , ind
     , code
     , lib_ind
     , type_name
     , detail_ind
     , acct_desc
     , case when seq_no = 0 then tot_cnt
            else null
       end                 as tot_cnt
     , case when seq_no = 0 then ttl_cnt
            else null
       end                 as ttl_cnt 
   from   shwcue_d cue
join shw_part
    on cue.nbr         = spj_nbr     
join pwn_nme ipname
    on part_nbr            = acct_no
join affiliatn
    on ipname.affl_cd = affl_cd
left join publisher
    on acct_no         = ipname.acct_no
left join acct_type
    on ipname.acct_type    = acct_type   
where nbr in (1624910043, 1624910021
, 1624910073, 1624910048, 1624910100, 1624910105, 1624910085, 1624910104
, 1624910121, 1624910063, 1624910074, 1624910023, 1624910106, 1624910101
, 1624910111, 1624910128, 1624910029, 1624910032, 1624910026, 1624910064
, 1624910049, 1624910097, 1624910125, 1624910086, 1624910008, 1624910110
, 1624910027, 1624910102, 1624910013, 1624910087, 1624910044, 1624910124
, 1624910075, 1624910076, 1624910112, 1624910011, 1624910051, 1624910050
, 1624910017, 1624910007, 1624910016, 1624910091, 1624910047, 1624910129
, 1624910113, 1624910077, 1624910130, 1624910033, 1624910030, 1624910065
, 1624910066, 1624910067, 1624910109, 1624910041, 1624910025, 1624910022
, 1624910114, 1624910092, 1624910052, 1624910042, 1624910034, 1624910035
, 1624910093, 1624910053, 1624910054, 1624910058, 1624910103, 1624910090
, 1624910118, 1624910119, 1624910096, 1624910095, 1624910122, 1624910115
, 1624910036, 1624910001, 1624910088, 1624910098, 1624910004, 1624910046
, 1624910037, 1624910031, 1624910059, 1624910055, 1624910015, 1624910019
, 1624910079, 1624910116, 1624910056, 1624910123, 1624910045, 1624910038
, 1624910080, 1624910006, 1624910099, 1624910117, 1624910012, 1624910084
, 1624910083, 1624910082, 1624910068, 1624910070, 1624910003, 1624910081
, 1624910108, 1624910018, 1624910071, 1624910126, 1624910020, 1624910127
, 1624910107, 1624910094, 1624910072, 1624910028, 1624910005, 1624910069
, 1624910002, 1624910057, 1624910039, 1624910060, 1624910009, 1624910089
, 1624910120, 1624910024, 1624910040, 1624910014, 1624910078, 1624910062
, 1624910131, 1624910061, 1624910010 )   Altered to get the desired access path: select distinct   nme || ovflw as aka_nme
     , name
     , co_name
     , acct_no
     , seq_no
     , cd
     , soc_nme
     , ind
     , code
     , lib_ind
     , type_name
     , detail_ind
     , acct_desc
     , case when seq_no = 0 then tot_cnt
            else null
       end                 as tot_cnt
     , case when seq_no = 0 then ttl_cnt
            else null
       end                 as ttl_cnt 
   from   shwcue_d cue
join shw_part
    on cue.nbr         = spj_nbr     
join pwn_nme ipname
    on part_nbr            = acct_no    and nme concat '' like 'UNI%'
join affiliatn
    on ipname.affl_cd = affl_cd
left join publisher
    on acct_no         = ipname.acct_no
left join acct_type
    on ipname.acct_type    = acct_type   
where nbr in (1624910043, 1624910021
, 1624910073, 1624910048, 1624910100, 1624910105, 1624910085, 1624910104
, 1624910121, 1624910063, 1624910074, 1624910023, 1624910106, 1624910101
, 1624910111, 1624910128, 1624910029, 1624910032, 1624910026, 1624910064
, 1624910049, 1624910097, 1624910125, 1624910086, 1624910008, 1624910110
, 1624910027, 1624910102, 1624910013, 1624910087, 1624910044, 1624910124
, 1624910075, 1624910076, 1624910112, 1624910011, 1624910051, 1624910050
, 1624910017, 1624910007, 1624910016, 1624910091, 1624910047, 1624910129
, 1624910113, 1624910077, 1624910130, 1624910033, 1624910030, 1624910065
, 1624910066, 1624910067, 1624910109, 1624910041, 1624910025, 1624910022
, 1624910114, 1624910092, 1624910052, 1624910042, 1624910034, 1624910035
, 1624910093, 1624910053, 1624910054, 1624910058, 1624910103, 1624910090
, 1624910118, 1624910119, 1624910096, 1624910095, 1624910122, 1624910115
, 1624910036, 1624910001, 1624910088, 1624910098, 1624910004, 1624910046
, 1624910037, 1624910031, 1624910059, 1624910055, 1624910015, 1624910019
, 1624910079, 1624910116, 1624910056, 1624910123, 1624910045, 1624910038
, 1624910080, 1624910006, 1624910099, 1624910117, 1624910012, 1624910084
, 1624910083, 1624910082, 1624910068, 1624910070, 1624910003, 1624910081
, 1624910108, 1624910018, 1624910071, 1624910126, 1624910020, 1624910127
, 1624910107, 1624910094, 1624910072, 1624910028, 1624910005, 1624910069
, 1624910002, 1624910057, 1624910039, 1624910060, 1624910009, 1624910089
, 1624910120, 1624910024, 1624910040, 1624910014, 1624910078, 1624910062
, 1624910131, 1624910061, 1624910010 )       Dave Nance     -----End Original Message-----
Attachment Links: image001.png (3 k)  
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email] how ESAi's fast data refresh & Test Data Management products can save up to 90% in CPU, I/O
and manual efforts compared to typical solutions. Be a hero to your users with BCV5 & XDM. See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Walter Jani&#223;en

AW: AW: Z/OS V10 Access Path Issue
(in response to Dave Nance)
Hi Dave

I don’t know, if I am blind or you missed the predicate in your original query. If this predicate (nme LIKE ‘UNI%’) is really missing, but DB2 picks up this index, it must be a non-matching index scan. You didn’t tell, which DB2 release you are on.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Dave Nance [mailto:[login to unmask email]
Gesendet: Donnerstag, 11. Mai 2017 16:27
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: Z/OS V10 Access Path Issue

Should have mentioned the following:

The column nme and nbr are leading columns of an index in their respective tables.

The nme column is leading column of the primary key index. First key is 2,974,450, full key is 3,249,028. The UNI% qualifies 1204 rows from this table.

The nbr column is leading column of index, not unique. First key is 3,196,562, full key is 43,020,344, there are 70,594,243 rows in table. The IN list below qualifies 5179 rows.

The thing is the tables that we join to in the middle of this query, using the nme column as access path explode the intermediate result set into the millions of rows, using the nbr column keeps us in the 10's of thousands of rows through the joins.

The final result set is only 4 rows.




-----End Original Message-----
Attachment Links: image001.png (3 k) http://www.idug.org/p/fo/do/?download=1&fid=8222
Site Links: View post online http://www.idug.org/p/fo/st/?post=181159&anc=p181159#p181159 View mailing list online http://www.idug.org/p/fo/si/?topic=19 Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription http://www.idug.org/p/us/to

This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>
Learn how ESAi's fast data refresh & Test Data Management products can save up to 90% in CPU, I/O
and manual efforts compared to typical solutions. Be a hero to your users with BCV5 & XDM. See
http://www.ESAIGroup.com/idug http://www.esaigroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2
________________________________


-----End Original Message-----
Attachments

  • image001.png (2.6k)

Roy Boxwell

Z/OS V10 Access Path Issue
(in response to Dave Nance)
The altered SQL

and nme concat '' like 'UNI%'


Does not exist at all in the original SQL…

And remember that FREQVAL by default only collects MOST and with 1024 values I would suspect that BOTH or LEAST would be better alternatives…as Walter has said HISTOGRAM might also help a bit

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Dave Nance [mailto:[login to unmask email]
Sent: Thursday, May 11, 2017 3:42 PM
To: Daniel L. Luksetich <[login to unmask email]>
Subject: [DB2-L] - Z/OS V10 Access Path Issue

Hi all,
While waiting on folks here to get me a PMR, thought I'd see if I could get any suggestions. Thanks for any help.

I have an SQL statement that runs for over 46 minutes, but if I influence the access path by add a concat ‘’, the query runs in less than 3 seconds. Looking at the explains, I see why optimizer selects the much cheaper looking path, but the reality is that the more expensive path is MUCH cheaper. The only difference between the two queries is I changed:

and nme like 'UNI%'

To:
and nme || '' like 'UNI%'

This gets DB2 to not consider this column for index access matching on the column and to decide to use the index I want it to use.
Original Query:
Query execution time => 46 min: 45 s: 799 ms

Changed Query:
Query execution time => 2 s: 815 ms

I have collected more in-depth Statistics. Here are the latest parms I used trying to give DB2 better info on these tables/indexes. I have tried about every trick I can think of to improve this query without rewriting, which is what the application team wants. Not sure if I should try something different or not to get the correct access path. I am looking at lying to DB2 to get the access path I want, by updating the index statistics manually.
LISTDEF TSLIST
INCLUDE TABLESPACE DPRSPCUE.BCUEPSCD
INCLUDE TABLESPACE DPRSPCUE.BCUEPSPJ
INCLUDE TABLESPACE DPRSPPRS.BPRSP000
INCLUDE TABLESPACE DPRSPPRS.BPRSPP06
INCLUDE TABLESPACE DPRSPPRS.BPRSPPUB

RUNSTATS TABLESPACE LIST TSLIST
TABLE(ALL)
INDEX ALL
KEYCARD
FREQVAL NUMCOLS 1 COUNT 40
FREQVAL NUMCOLS 2 COUNT 40
FREQVAL NUMCOLS 3 COUNT 40
FREQVAL NUMCOLS 4 COUNT 40
FREQVAL NUMCOLS 5 COUNT 40
UPDATE ALL FORCEROLLUP YES
SHRLEVEL CHANGE SORTNUM 25 SORTDEVT DISK
HISTORY ALL

Original SQL:
select distinct nme || ovflw as aka_nme
, name
, co_name
, acct_no
, seq_no
, cd
, soc_nme
, ind
, code
, lib_ind
, type_name
, detail_ind
, acct_desc
, case when seq_no = 0 then tot_cnt
else null
end as tot_cnt
, case when seq_no = 0 then ttl_cnt
else null
end as ttl_cnt
from shwcue_d cue
join shw_part
on cue.nbr = spj_nbr
join pwn_nme ipname
on part_nbr = acct_no
join affiliatn
on ipname.affl_cd = affl_cd
left join publisher
on acct_no = ipname.acct_no
left join acct_type
on ipname.acct_type = acct_type
where nbr in (1624910043, 1624910021
, 1624910073, 1624910048, 1624910100, 1624910105, 1624910085, 1624910104
, 1624910121, 1624910063, 1624910074, 1624910023, 1624910106, 1624910101
, 1624910111, 1624910128, 1624910029, 1624910032, 1624910026, 1624910064
, 1624910049, 1624910097, 1624910125, 1624910086, 1624910008, 1624910110
, 1624910027, 1624910102, 1624910013, 1624910087, 1624910044, 1624910124
, 1624910075, 1624910076, 1624910112, 1624910011, 1624910051, 1624910050
, 1624910017, 1624910007, 1624910016, 1624910091, 1624910047, 1624910129
, 1624910113, 1624910077, 1624910130, 1624910033, 1624910030, 1624910065
, 1624910066, 1624910067, 1624910109, 1624910041, 1624910025, 1624910022
, 1624910114, 1624910092, 1624910052, 1624910042, 1624910034, 1624910035
, 1624910093, 1624910053, 1624910054, 1624910058, 1624910103, 1624910090
, 1624910118, 1624910119, 1624910096, 1624910095, 1624910122, 1624910115
, 1624910036, 1624910001, 1624910088, 1624910098, 1624910004, 1624910046
, 1624910037, 1624910031, 1624910059, 1624910055, 1624910015, 1624910019
, 1624910079, 1624910116, 1624910056, 1624910123, 1624910045, 1624910038
, 1624910080, 1624910006, 1624910099, 1624910117, 1624910012, 1624910084
, 1624910083, 1624910082, 1624910068, 1624910070, 1624910003, 1624910081
, 1624910108, 1624910018, 1624910071, 1624910126, 1624910020, 1624910127
, 1624910107, 1624910094, 1624910072, 1624910028, 1624910005, 1624910069
, 1624910002, 1624910057, 1624910039, 1624910060, 1624910009, 1624910089
, 1624910120, 1624910024, 1624910040, 1624910014, 1624910078, 1624910062
, 1624910131, 1624910061, 1624910010 )

Altered to get the desired access path:
select distinct nme || ovflw as aka_nme
, name
, co_name
, acct_no
, seq_no
, cd
, soc_nme
, ind
, code
, lib_ind
, type_name
, detail_ind
, acct_desc
, case when seq_no = 0 then tot_cnt
else null
end as tot_cnt
, case when seq_no = 0 then ttl_cnt
else null
end as ttl_cnt
from shwcue_d cue
join shw_part
on cue.nbr = spj_nbr
join pwn_nme ipname
on part_nbr = acct_no
and nme concat '' like 'UNI%'
join affiliatn
on ipname.affl_cd = affl_cd
left join publisher
on acct_no = ipname.acct_no
left join acct_type
on ipname.acct_type = acct_type
where nbr in (1624910043, 1624910021
, 1624910073, 1624910048, 1624910100, 1624910105, 1624910085, 1624910104
, 1624910121, 1624910063, 1624910074, 1624910023, 1624910106, 1624910101
, 1624910111, 1624910128, 1624910029, 1624910032, 1624910026, 1624910064
, 1624910049, 1624910097, 1624910125, 1624910086, 1624910008, 1624910110
, 1624910027, 1624910102, 1624910013, 1624910087, 1624910044, 1624910124
, 1624910075, 1624910076, 1624910112, 1624910011, 1624910051, 1624910050
, 1624910017, 1624910007, 1624910016, 1624910091, 1624910047, 1624910129
, 1624910113, 1624910077, 1624910130, 1624910033, 1624910030, 1624910065
, 1624910066, 1624910067, 1624910109, 1624910041, 1624910025, 1624910022
, 1624910114, 1624910092, 1624910052, 1624910042, 1624910034, 1624910035
, 1624910093, 1624910053, 1624910054, 1624910058, 1624910103, 1624910090
, 1624910118, 1624910119, 1624910096, 1624910095, 1624910122, 1624910115
, 1624910036, 1624910001, 1624910088, 1624910098, 1624910004, 1624910046
, 1624910037, 1624910031, 1624910059, 1624910055, 1624910015, 1624910019
, 1624910079, 1624910116, 1624910056, 1624910123, 1624910045, 1624910038
, 1624910080, 1624910006, 1624910099, 1624910117, 1624910012, 1624910084
, 1624910083, 1624910082, 1624910068, 1624910070, 1624910003, 1624910081
, 1624910108, 1624910018, 1624910071, 1624910126, 1624910020, 1624910127
, 1624910107, 1624910094, 1624910072, 1624910028, 1624910005, 1624910069
, 1624910002, 1624910057, 1624910039, 1624910060, 1624910009, 1624910089
, 1624910120, 1624910024, 1624910040, 1624910014, 1624910078, 1624910062
, 1624910131, 1624910061, 1624910010 )



Dave Nance


-----End Original Message-----

Terry Purcell

RE: Z/OS V10 Access Path Issue
(in response to Dave Nance)

Hi Dave,
There isn't sufficient information to give you an exact answer - so I am going to have to make an educated guess.

Because its difficult to map the columns back to each table (since columns are not prefixed in the query with their table or correlation names), I am going to guess that the table join sequence you want is (note I am limiting to what appear to be the more important tables):
shwcue_d -> shw_part -> pwn_name
but you are getting:
pwn_name -> shw_part -> shwcue_d

And what you are saying is that with the 1st access path, the joined rows number in the tens of thousands in the intermediate tables, but the 2nd, its in the millions (again, in the intermediate tables). This implies that there is join skew - which is difficult to detect and optimize for.

Some suggestions if you prefer not to change the SQL in anyway (any of the following may change the access path individually, but together may also improve the performance with or without an access path change):
1) As suggested, collecting HISTOGRAMS on NME column may help improve the optimizer's estimate of that predicate. Those FREQVAL NUMCOLS 2, 3, 4 & 5 statistics are not helpful here.
2) You may try to increase zparm INLISTP to 200 - this should have the effect of copying the "nbr IN (....)" predicate to shw_part table also - which means it can be applied earlier in the join sequence given the current access path.
2) You may try to see if you can make the join to shw_part index-only - as this buffers the impact of join skew by avoiding data page access. This one may be the hardest to achieve.

Regards
Terry Purcell

In Reply to Dave Nance:

Hi all,   While waiting on folks here to get me a PMR, thought I'd see if I could get any suggestions. Thanks for any help.
I have an SQL statement that runs for over 46 minutes, butif I influence the access path by add a concat ‘’, the query runs in less than3 seconds. Looking at the explains, I see why optimizer selects the much cheaper looking path, but the reality is that the more expensive path is MUCH cheaper. The onlydifference between the two queries is I changed:     andnme           like 'UNI%'   To:    and nme || ''    like 'UNI%'    This gets DB2 to not consider this column forindex access matching on the column and to decide to use the index I want it touse.Original Query:Query execution time => 46 min: 45 s: 799 ms Changed Query:Query execution time => 2 s: 815 ms I have collected more in-depth Statistics. Here are thelatest parms I used trying to give DB2 better info on these tables/indexes. Ihave tried about every trick I can think of to improve this query withoutrewriting, which is what the application team wants. Not sure if I should trysomething different or not to get the correct access path. I am looking atlying to DB2 to get the access path I want, by updating the index statisticsmanually.LISTDEF TSLIST       INCLUDETABLESPACE  DPRSPCUE.BCUEPSCD       INCLUDETABLESPACE  DPRSPCUE.BCUEPSPJ       INCLUDETABLESPACE  DPRSPPRS.BPRSP000       INCLUDETABLESPACE  DPRSPPRS.BPRSPP06       INCLUDETABLESPACE  DPRSPPRS.BPRSPPUB RUNSTATS TABLESPACE LIST TSLIST      TABLE(ALL)               INDEX ALL      KEYCARD             FREQVAL NUMCOLS  1 COUNT 40             FREQVAL NUMCOLS  2 COUNT 40             FREQVAL NUMCOLS  3 COUNT 40             FREQVAL NUMCOLS  4 COUNT 40             FREQVAL NUMCOLS  5 COUNT 40      UPDATE ALL FORCEROLLUP YES      SHRLEVEL CHANGE SORTNUM 25SORTDEVT DISK      HISTORY ALL  Original SQL:select distinct   nme || ovflw as aka_nme
     , name
     , co_name
     , acct_no
     , seq_no
     , cd
     , soc_nme
     , ind
     , code
     , lib_ind
     , type_name
     , detail_ind
     , acct_desc
     , case when seq_no = 0 then tot_cnt
            else null
       end                 as tot_cnt
     , case when seq_no = 0 then ttl_cnt
            else null
       end                 as ttl_cnt 
   from   shwcue_d cue
join shw_part
    on cue.nbr         = spj_nbr     
join pwn_nme ipname
    on part_nbr            = acct_no
join affiliatn
    on ipname.affl_cd = affl_cd
left join publisher
    on acct_no         = ipname.acct_no
left join acct_type
    on ipname.acct_type    = acct_type   
where nbr in (1624910043, 1624910021
, 1624910073, 1624910048, 1624910100, 1624910105, 1624910085, 1624910104
, 1624910121, 1624910063, 1624910074, 1624910023, 1624910106, 1624910101
, 1624910111, 1624910128, 1624910029, 1624910032, 1624910026, 1624910064
, 1624910049, 1624910097, 1624910125, 1624910086, 1624910008, 1624910110
, 1624910027, 1624910102, 1624910013, 1624910087, 1624910044, 1624910124
, 1624910075, 1624910076, 1624910112, 1624910011, 1624910051, 1624910050
, 1624910017, 1624910007, 1624910016, 1624910091, 1624910047, 1624910129
, 1624910113, 1624910077, 1624910130, 1624910033, 1624910030, 1624910065
, 1624910066, 1624910067, 1624910109, 1624910041, 1624910025, 1624910022
, 1624910114, 1624910092, 1624910052, 1624910042, 1624910034, 1624910035
, 1624910093, 1624910053, 1624910054, 1624910058, 1624910103, 1624910090
, 1624910118, 1624910119, 1624910096, 1624910095, 1624910122, 1624910115
, 1624910036, 1624910001, 1624910088, 1624910098, 1624910004, 1624910046
, 1624910037, 1624910031, 1624910059, 1624910055, 1624910015, 1624910019
, 1624910079, 1624910116, 1624910056, 1624910123, 1624910045, 1624910038
, 1624910080, 1624910006, 1624910099, 1624910117, 1624910012, 1624910084
, 1624910083, 1624910082, 1624910068, 1624910070, 1624910003, 1624910081
, 1624910108, 1624910018, 1624910071, 1624910126, 1624910020, 1624910127
, 1624910107, 1624910094, 1624910072, 1624910028, 1624910005, 1624910069
, 1624910002, 1624910057, 1624910039, 1624910060, 1624910009, 1624910089
, 1624910120, 1624910024, 1624910040, 1624910014, 1624910078, 1624910062
, 1624910131, 1624910061, 1624910010 )
Altered to get the desired access path:select distinct   nme || ovflw as aka_nme
     , name
     , co_name
     , acct_no
     , seq_no
     , cd
     , soc_nme
     , ind
     , code
     , lib_ind
     , type_name
     , detail_ind
     , acct_desc
     , case when seq_no = 0 then tot_cnt
            else null
       end                 as tot_cnt
     , case when seq_no = 0 then ttl_cnt
            else null
       end                 as ttl_cnt 
   from   shwcue_d cue
join shw_part
    on cue.nbr         = spj_nbr     
join pwn_nme ipname
    on part_nbr            = acct_no   and nme concat '' like 'UNI%'
join affiliatn
    on ipname.affl_cd = affl_cd
left join publisher
    on acct_no         = ipname.acct_no
left join acct_type
    on ipname.acct_type    = acct_type   
where nbr in (1624910043, 1624910021
, 1624910073, 1624910048, 1624910100, 1624910105, 1624910085, 1624910104
, 1624910121, 1624910063, 1624910074, 1624910023, 1624910106, 1624910101
, 1624910111, 1624910128, 1624910029, 1624910032, 1624910026, 1624910064
, 1624910049, 1624910097, 1624910125, 1624910086, 1624910008, 1624910110
, 1624910027, 1624910102, 1624910013, 1624910087, 1624910044, 1624910124
, 1624910075, 1624910076, 1624910112, 1624910011, 1624910051, 1624910050
, 1624910017, 1624910007, 1624910016, 1624910091, 1624910047, 1624910129
, 1624910113, 1624910077, 1624910130, 1624910033, 1624910030, 1624910065
, 1624910066, 1624910067, 1624910109, 1624910041, 1624910025, 1624910022
, 1624910114, 1624910092, 1624910052, 1624910042, 1624910034, 1624910035
, 1624910093, 1624910053, 1624910054, 1624910058, 1624910103, 1624910090
, 1624910118, 1624910119, 1624910096, 1624910095, 1624910122, 1624910115
, 1624910036, 1624910001, 1624910088, 1624910098, 1624910004, 1624910046
, 1624910037, 1624910031, 1624910059, 1624910055, 1624910015, 1624910019
, 1624910079, 1624910116, 1624910056, 1624910123, 1624910045, 1624910038
, 1624910080, 1624910006, 1624910099, 1624910117, 1624910012, 1624910084
, 1624910083, 1624910082, 1624910068, 1624910070, 1624910003, 1624910081
, 1624910108, 1624910018, 1624910071, 1624910126, 1624910020, 1624910127
, 1624910107, 1624910094, 1624910072, 1624910028, 1624910005, 1624910069
, 1624910002, 1624910057, 1624910039, 1624910060, 1624910009, 1624910089
, 1624910120, 1624910024, 1624910040, 1624910014, 1624910078, 1624910062
, 1624910131, 1624910061, 1624910010 ) 

Dave Nance

Charles Brown

Z/OS V10 Access Path Issue
(in response to Terry Purcell)
Hello Dave,
Is this a one time (ad hoc) query -- just wondering?
The shwcue_d table, quite a large one has always been a problem. i.e -- one of the few tables not clustered by its primary. Check out if it is clustered by the primary. The PWN table is quite smaller than the shwcue_d table so hitting it first is a step in the right direction. On the other hand, the PWN with its 17++ indexes doesn't make the optimizer's task easier. This is a scenario in which you may have to resort using hint. Your thoughts.

Hope this helps
Chas/b
NZ DBA

Sent from my iPad

> On May 12, 2017, at 2:38 PM, Terry Purcell <[login to unmask email]> wrote:
>
> Hi Dave,
> There isn't sufficient information to give you an exact answer - so I am going to have to make an educated guess.
>
> Because its difficult to map the columns back to each table (since columns are not prefixed in the query with their table or correlation names), I am going to guess that the table join sequence you want is (note I am limiting to what appear to be the more important tables):
> shwcue_d -> shw_part -> pwn_name
> but you are getting:
> pwn_name -> shw_part -> shwcue_d
>
> And what you are saying is that with the 1st access path, the joined rows number in the tens of thousands in the intermediate tables, but the 2nd, its in the millions (again, in the intermediate tables). This implies that there is join skew - which is difficult to detect and optimize for.
>
> Some suggestions if you prefer not to change the SQL in anyway (any of the following may change the access path individually, but together may also improve the performance with or without an access path change):
> 1) As suggested, collecting HISTOGRAMS on NME column may help improve the optimizer's estimate of that predicate. Those FREQVAL NUMCOLS 2, 3, 4 & 5 statistics are not helpful here.
> 2) You may try to increase zparm INLISTP to 200 - this should have the effect of copying the "nbr IN (....)" predicate to shw_part table also - which means it can be applied earlier in the join sequence given the current access path.
> 2) You may try to see if you can make the join to shw_part index-only - as this buffers the impact of join skew by avoiding data page access. This one may be the hardest to achieve.
>
> Regards
> Terry Purcell
>
> In Reply to Dave Nance:
>
> Hi all, While waiting on folks here to get me a PMR, thought I'd see if I could get any suggestions. Thanks for any help.
> I have an SQL statement that runs for over 46 minutes, butif I influence the access path by add a concat ‘’, the query runs in less than3 seconds. Looking at the explains, I see why optimizer selects the much cheaper looking path, but the reality is that the more expensive path is MUCH cheaper. The onlydifference between the two queries is I changed: andnme like 'UNI%' To: and nme || '' like 'UNI%' This gets DB2 to not consider this column forindex access matching on the column and to decide to use the index I want it touse.Original Query:Query execution time => 46 min: 45 s: 799 ms Changed Query:Query execution time => 2 s: 815 ms I have collected more in-depth Statistics. Here are thelatest parms I used trying to give DB2 better info on these tables/indexes. Ihave tried about every trick I can think of to improve this query withoutrewriting, which is what the application team wants. Not sure if I should trysomething different or not to get the correct access path. I am looking atlying to DB2 to get the access path I want, by updating the index statisticsmanually.LISTDEF TSLIST INCLUDETABLESPACE DPRSPCUE.BCUEPSCD INCLUDETABLESPACE DPRSPCUE.BCUEPSPJ INCLUDETABLESPACE DPRSPPRS.BPRSP000 INCLUDETABLESPACE DPRSPPRS.BPRSPP06 INCLUDETABLESPACE DPRSPPRS.BPRSPPUB RUNSTATS TABLESPACE LIST TSLIST TABLE(ALL) INDEX ALL KEYCARD FREQVAL NUMCOLS 1 COUNT 40 FREQVAL NUMCOLS 2 COUNT 40 FREQVAL NUMCOLS 3 COUNT 40 FREQVAL NUMCOLS 4 COUNT 40 FREQVAL NUMCOLS 5 COUNT 40 UPDATE ALL FORCEROLLUP YES SHRLEVEL CHANGE SORTNUM 25SORTDEVT DISK HISTORY ALL Original SQL:select distinct nme || ovflw as aka_nme
> , name
> , co_name
> , acct_no
> , seq_no
> , cd
> , soc_nme
> , ind
> , code
> , lib_ind
> , type_name
> , detail_ind
> , acct_desc
> , case when seq_no = 0 then tot_cnt
> else null
> end as tot_cnt
> , case when seq_no = 0 then ttl_cnt
> else null
> end as ttl_cnt
> from shwcue_d cue
> join shw_part
> on cue.nbr = spj_nbr
> join pwn_nme ipname
> on part_nbr = acct_no
> join affiliatn
> on ipname.affl_cd = affl_cd
> left join publisher
> on acct_no = ipname.acct_no
> left join acct_type
> on ipname.acct_type = acct_type
> where nbr in (1624910043, 1624910021
> , 1624910073, 1624910048, 1624910100, 1624910105, 1624910085, 1624910104
> , 1624910121, 1624910063, 1624910074, 1624910023, 1624910106, 1624910101
> , 1624910111, 1624910128, 1624910029, 1624910032, 1624910026, 1624910064
> , 1624910049, 1624910097, 1624910125, 1624910086, 1624910008, 1624910110
> , 1624910027, 1624910102, 1624910013, 1624910087, 1624910044, 1624910124
> , 1624910075, 1624910076, 1624910112, 1624910011, 1624910051, 1624910050
> , 1624910017, 1624910007, 1624910016, 1624910091, 1624910047, 1624910129
> , 1624910113, 1624910077, 1624910130, 1624910033, 1624910030, 1624910065
> , 1624910066, 1624910067, 1624910109, 1624910041, 1624910025, 1624910022
> , 1624910114, 1624910092, 1624910052, 1624910042, 1624910034, 1624910035
> , 1624910093, 1624910053, 1624910054, 1624910058, 1624910103, 1624910090
> , 1624910118, 1624910119, 1624910096, 1624910095, 1624910122, 1624910115
> , 1624910036, 1624910001, 1624910088, 1624910098, 1624910004, 1624910046
> , 1624910037, 1624910031, 1624910059, 1624910055, 1624910015, 1624910019
> , 1624910079, 1624910116, 1624910056, 1624910123, 1624910045, 1624910038
> , 1624910080, 1624910006, 1624910099, 1624910117, 1624910012, 1624910084
> , 1624910083, 1624910082, 1624910068, 1624910070, 1624910003, 1624910081
> , 1624910108, 1624910018, 1624910071, 1624910126, 1624910020, 1624910127
> , 1624910107, 1624910094, 1624910072, 1624910028, 1624910005, 1624910069
> , 1624910002, 1624910057, 1624910039, 1624910060, 1624910009, 1624910089
> , 1624910120, 1624910024, 1624910040, 1624910014, 1624910078, 1624910062
> , 1624910131, 1624910061, 1624910010 )
> Altered to get the desired access path:select distinct nme || ovflw as aka_nme
> , name
> , co_name
> , acct_no
> , seq_no
> , cd
> , soc_nme
> , ind
> , code
> , lib_ind
> , type_name
> , detail_ind
> , acct_desc
> , case when seq_no = 0 then tot_cnt
> else null
> end as tot_cnt
> , case when seq_no = 0 then ttl_cnt
> else null
> end as ttl_cnt
> from shwcue_d cue
> join shw_part
> on cue.nbr = spj_nbr
> join pwn_nme ipname
> on part_nbr = acct_no and nme concat '' like 'UNI%'
> join affiliatn
> on ipname.affl_cd = affl_cd
> left join publisher
> on acct_no = ipname.acct_no
> left join acct_type
> on ipname.acct_type = acct_type
> where nbr in (1624910043, 1624910021
> , 1624910073, 1624910048, 1624910100, 1624910105, 1624910085, 1624910104
> , 1624910121, 1624910063, 1624910074, 1624910023, 1624910106, 1624910101
> , 1624910111, 1624910128, 1624910029, 1624910032, 1624910026, 1624910064
> , 1624910049, 1624910097, 1624910125, 1624910086, 1624910008, 1624910110
> , 1624910027, 1624910102, 1624910013, 1624910087, 1624910044, 1624910124
> , 1624910075, 1624910076, 1624910112, 1624910011, 1624910051, 1624910050
> , 1624910017, 1624910007, 1624910016, 1624910091, 1624910047, 1624910129
> , 1624910113, 1624910077, 1624910130, 1624910033, 1624910030, 1624910065
> , 1624910066, 1624910067, 1624910109, 1624910041, 1624910025, 1624910022
> , 1624910114, 1624910092, 1624910052, 1624910042, 1624910034, 1624910035
> , 1624910093, 1624910053, 1624910054, 1624910058, 1624910103, 1624910090
> , 1624910118, 1624910119, 1624910096, 1624910095, 1624910122, 1624910115
> , 1624910036, 1624910001, 1624910088, 1624910098, 1624910004, 1624910046
> , 1624910037, 1624910031, 1624910059, 1624910055, 1624910015, 1624910019
> , 1624910079, 1624910116, 1624910056, 1624910123, 1624910045, 1624910038
> , 1624910080, 1624910006, 1624910099, 1624910117, 1624910012, 1624910084
> , 1624910083, 1624910082, 1624910068, 1624910070, 1624910003, 1624910081
> , 1624910108, 1624910018, 1624910071, 1624910126, 1624910020, 1624910127
> , 1624910107, 1624910094, 1624910072, 1624910028, 1624910005, 1624910069
> , 1624910002, 1624910057, 1624910039, 1624910060, 1624910009, 1624910089
> , 1624910120, 1624910024, 1624910040, 1624910014, 1624910078, 1624910062
> , 1624910131, 1624910061, 1624910010 )
>
> Dave Nance
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Learn how ESAi's fast data refresh & Test Data Management products can save up to 90% in CPU, I/O
> and manual efforts compared to typical solutions. Be a hero to your users with BCV5 & XDM. See
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>