[EXT] List Digest, Jun 24, 2019

Richard Janni

[EXT] List Digest, Jun 24, 2019
Hi Walter -

If you have plan management turned on, you can get the old paths back with a REBIND SWITCH PREVIOUS.  I've used it successfully several times.  You just have to make sure the previous package copy in SYSIBM.SYSPACKCOPY is valid.  The only thing I don't like about it is that the time stamp for the switch event is not recorded anywhere in the catalogs, so make a note of when you run the switch because a 'rebind' does not actually occur.  But you do get the old paths back.


Regards,
Rich Janni|Sr. Data Base Analyst|CISD - Data Administration
|*230 Raritan Center Pkwy. Edison N.J. 08837| ((732) 225-8101




From:        "DB2 - L " <[login to unmask email]>
To:        [login to unmask email]
Date:        06/25/2019 03:03 AM
Subject:        [EXT] [DB2-L] - List Digest, Jun 24, 2019



The following posts were made on Jun 24, 2019
1.        AW: DB2 Optimization Hint works on 1 subsystem/DS group but not another - (Walter Janißen)
2.        checking WLMs and policies - (James Campbell)
3.        checking WLMs and policies - (Chris Hoelscher)
4.        Db2 11 for z/OS - UTILTERM Phase of REORG - (Sam Baugh)
1. AW: DB2 Optimization Hint works on 1 subsystem/DS group but not another (Walter Janißen) From: Walter Janißen
Subject: AW: DB2 Optimization Hint works on 1 subsystem/DS group but not another Hi

I once experienced different explains, when a package was bound on member A and member B, but if I remember right it was the only time in all the years with Db2.
Does this phenomenon occur always for that statement?

Did you try to store your hint as a statement level hint in SYSQUERY in the dev- and production-system and then rebind that package?

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: Donna Domovic <[login to unmask email]>
Gesendet: Dienstag, 18. Juni 2019 18:42
An: [login to unmask email]
Betreff: [DB2-L] - DB2 Optimization Hint works on 1 subsystem/DS group but not another


We've run into a strange situation that has us scratching our heads. We have a DB2 package that had not been rebound for several versions of DB2 (I know this is bad) but was automatically rebound after we upgraded to DB2 v11 CM. After the upgrade, the access path changed with the order of the 2 indexes being swapped. This resulted in the run time and CPU time both increasing by 5-6 times.

The package did not use a hint but we tried to add one pointing to the old access path. When we do this, even though the bind says it used the hint, the rows in the PLAN_TABLE still list the indexes in the post v11 order resulting in the longer execution and CPU times.

We were able to get the hint to work on one of our development systems the way that we want it to but we can't get it to work on another dev system or on the prod system.

We have taken a look at the DB2 zparms and they appear to be the same across subsystems except for values that you'd expect to be different. We've also tried various things including updating stats and changing one of the indexes but we still can't get the hint to work or the access path back to where it was before the v11 bind.

Does anyone have any ideas of what we should try or look at to get the access path back to where we want/need it to be or to determine why the hint is working as expected on the 1 dev subsystem/DS group but not on the others?

Thanks in advance.

Donna Domovic



-----End Original Message-----
Attachments:
image001.png(2.6 KB)View Post Online | Respond Online | Respond Via Email | Respond Directly to User | Return to Top
2. checking WLMs and policies (James Campbell) From: James Campbell
Subject: checking WLMs and policies "Correctly" to whom?

/D WLM,APPLENV= ...
will merely show that it is defined and available to WLM. WLM doesn't really have any idea
about what would be correct in terms of running a Db2 environment. Because you could set
up a situation where the WLM definition doesn't need to provide any parameters - it's all in
the STC JCL (well, non data sharing, at least). Who is to say that it is incorrect?

DSNTIJRV might be as good as it gets.

James Campbell


On 23 Jun 2019 at 18:16, william giannelli wrote:

> We were doing a DR test and only after a long period did we find that a WLM Application Environment policy did not have the required startup parameters. I have a batch job that can display the Application Environment
> and shows the Status. I am hoping that is the status shows as available we can assume
> the policy is setup correctly.
> thanks
> Bill


---
This email has been checked for viruses by AVG.
https://www.avg.comView Post Online | Respond Online | Respond Via Email | Respond Directly to User | Return to Top
3. checking WLMs and policies (Chris Hoelscher) From: Chris Hoelscher
Subject: checking WLMs and policies We verify that db2 wlms are "correct" by executing WLM_REFRESH against a list of environment names - that seems to work for us

Chris Hoelscher| ITI . DB Services . Mainframe Database | Humana Inc.| T 502.476.2538 or 502.407.7266


-----Original Message-----
From: James Campbell <[login to unmask email]>
Sent: Monday, June 24, 2019 9:05 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: checking WLMs and policies

"Correctly" to whom?

/D WLM,APPLENV= ...
will merely show that it is defined and available to WLM. WLM doesn't really have any idea about what would be correct in terms of running a Db2 environment. Because you could set up a situation where the WLM definition doesn't need to provide any parameters - it's all in the STC JCL (well, non data sharing, at least). Who is to say that it is incorrect?

DSNTIJRV might be as good as it gets.

James Campbell


On 23 Jun 2019 at 18:16, william giannelli wrote:

> We were doing a DR test and only after a long period did we find that
> a WLM Application Environment policy did not have the required startup
> parameters. I have a batch job that can display the Application Environment and shows the Status. I am hoping that is the status shows as available we can assume the policy is setup correctly.
> thanks
> Bill


---
This email has been checked for viruses by AVG.
https://nam03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.avg.com&amp;data=02%7C01%7Cchoelscher%40humana.com%7C0d5b3acc280d45d4ac1c08d6f90943d1%7C56c62bbe85984b859e511ca753fa50f2%7C1%7C0%7C636970215522679030&amp;sdata=9qsW0P7yMJBZNts69TDCu0B9aQuPM%2BcV8B6TGHdJvlI%3D&amp;reserved=0

-----End Original Message----- View Post Online | Respond Online | Respond Via Email | Respond Directly to User | Return to Top
4. Db2 11 for z/OS - UTILTERM Phase of REORG (Sam Baugh) From: Sam Baugh
Subject: Db2 11 for z/OS - UTILTERM Phase of REORG
I apparently had a misconception that during a REORG SHRLEVEL CHANGE,  the table is only unavailable during the last LOG iteration and SWITCH phase.
It appears that it is unavailable during UTILTERM also.  The master log was showing some -904 resource unavailable, however, the display database showed the tablespace in UTRW (I don't know if UTRW status was for the entire UTILTERM phase or if the -904's occur only during a portion of the phase).
I have several more tablespaces to convert and my concern is that some of them will have a very long UTILTERM phase because of several indexes with small piece sizes (for pre-Db2 9 REORG Performance) and it takes what I think is unreasonably long to delete the old datasets (several seconds each), and it deletes serially.
Is there a way to speed up the delete?  I've asked the Systems Programmers and they don't think several seconds for a single delete is abnormal.  I am considering doing an ALTER on the indexes to increase the PIECESIZE to reduce the number of datasets involved.
Thanks!View Post Online | Respond Online | Respond Via Email | Respond Directly to User | Return to Top


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]
Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
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



Attachments

  • blankfilename.txt (2.3k)

Michael Hannan

RE: [EXT] List Digest, Jun 24, 2019
(in response to Richard Janni)

In Reply to Richard Janni:

If you have plan management turned on, you can get the old paths back with a REBIND SWITCH PREVIOUS.  I've used it successfully several times.  You just have to make sure the previous package copy in SYSIBM.SYSPACKCOPY is valid.  The only thing I don't like about it is that the time stamp for the switch event is not recorded anywhere in the catalogs, so make a note of when you run the switch because a 'rebind' does not actually occur.  But you do get the old paths back. 

Hi Rich, 

IBM believes all Packages should be using Extended Package Management. No reason not to use it.

I have noticed this weakness of not recording the REBIND time for a switch back too. I compare the BINDTIME of current Package (copy 0) to BINDTIME for Copies 1 and 2 to detect a Switch. If Copy 0 (current) has a lower timestamp, then it must have come from a SWITCH back.  LASTUSED on each Package could give a clue when the switch occurred but seems to be not precise (a date). At least shows all usage after the LASTUSED must be on another copy. Important to keep all Id of Packages including COLLID, and VERSION or CONTOKEN in historical data.

As a SWITCH does not determine access path, it does not record new explain data. We could manually do that following a SWITCH using  EXPLAIN Package.

I think APREUSE(WARN) is very useful on REBINDs to prevent unwanted access path changes, and as Walter says, the newer Access Path Stability features (to replace old OPTHINTs) that came in V10 and V11 (I think) are worth considering, because not dependent on a QUERYNO (instead match to SQL Statement text). I have use Selectivity Overrides (as another option with the Path Stability) to fix wrong filter factors, but still allow DB2 to pick the path.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jun 28, 2019 - 07:50 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jun 28, 2019 - 07:51 AM (Europe/Berlin)