Create Non-Unique Index DEFER (z/OS) - Contention with applications?

Michael Hannan

Create Non-Unique Index DEFER (z/OS) - Contention with applications?

I was reading about how DB2 V8 gave us the ability for Dynamic SQLs to ignore an index Created with Defer, and even ignore Inserts and Updates if not Unique. 
V9 gave us REBUILD SHRLEVEL CHANGE to go with that.

So idea is to have minimal outage to create Non-unique Indexes.

I have not been a Prod DBA for some time. So in practice, does it work well?

Can the CREATE INDEX fail with contention on some objects?

Why would that be. I understand it has to lock Catalog objects and Directory briefly, DBD.

Are their other major issues to cause CREATE INDEX DEFER to fail, or application problems that might occur?

I do understand Unique indexes is different since, Uniqueness has to be enforced by the index so Inserts and Updates affecting this index cannot proceed.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

 

Walter Janißen

AW: Create Non-Unique Index DEFER (z/OS) - Contention with applications?
(in response to Michael Hannan)
Hi Michael

Recently we had a big performance problem because a create of an index with DEFER YES timeout out due to a lock of a package. The drop succeeded, but the create immediately afterwards failed. Then the automatic rebind happened and the package did a tablespace scan for a multi-million table. Then the index was recreated, but because we are also a fan of APREUSE(WARN) the bad access path remained.

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: Michael Hannan <[login to unmask email]>
Gesendet: Samstag, 6. April 2019 09:48
An: [login to unmask email]
Betreff: [DB2-L] - Create Non-Unique Index DEFER (z/OS) - Contention with applications?


I was reading about how DB2 V8 gave us the ability for Dynamic SQLs to ignore an index Created with Defer, and even ignore Inserts and Updates if not Unique.
V9 gave us REBUILD SHRLEVEL CHANGE to go with that.

So idea is to have minimal outage to create Non-unique Indexes.

I have not been a Prod DBA for some time. So in practice, does it work well?

Can the CREATE INDEX fail with contention on some objects?

Why would that be. I understand it has to lock Catalog objects and Directory briefly, DBD.

Are their other major issues to cause CREATE INDEX DEFER to fail, or application problems that might occur?

I do understand Unique indexes is different since, Uniqueness has to be enforced by the index so Inserts and Updates affecting this index cannot proceed.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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

  • image001.png (2.6k)

Michael Hannan

RE: AW: Create Non-Unique Index DEFER (z/OS) - Contention with applications?
(in response to Walter Janißen)



In Reply to Walter Janißen:

Recently we had a big performance problem because a create of an index with DEFER YES timeout out due to a lock of a package. The drop succeeded, but the create immediately afterwards failed. Then the automatic rebind happened and the package did a tablespace scan for a multi-million table. Then the index was recreated, but because we are also a fan of APREUSE(WARN) the bad access path remained.

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

Walter,

Your scenario was slightly different from what I intended, because you dropped an index used by access paths.

In my situation, I was thinking only of new indexes, so no Drop first. No access path should be using the new index yet is the idea.

Potentially you could have created the new index first and dropped the old one later. That would be my preferred approach. I don't like to reuse existing index names (to effect a change) due to the additional impact. 

Additionally if I change an index I like access path to show the change.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Apr 09, 2019 - 08:52 AM (Europe/Berlin)

Michael Hannan

RE: Create Non-Unique Index DEFER (z/OS) - Contention with applications?
(in response to Michael Hannan)

Nobody answered my question a while back on CREATE Non Unique INDEX DEFER YES having contention with applications, other than Walter but was on a tangent to my theme.

So I found out a bit more.

There is IBM documentation that indicates:

* if you make the Index Unique more problems due to Index having to Ensure uniqueness during REBUILD. So don't make an Index UNIQUE if you don't really have to. Save that for Primary and main Unique Key.

* Dynamic SQLs take DBD Share locks if you have no Dynamic Statement Cache (hopefully no one does that)

* R.I. Check operations e.g. INSERT (with RI), take a Share lock on DBD. No idea exactly why just yet, since it would not protect against R.I. being added during the INSERT. Check Pending would be normal protection. Is it to stop one removing R.I. during an R.I. check? That seems overkill to me, and potentially drastically limits concurrency.

RI is much more trouble than it is worth, mostly. He he.

To promote concurrency of DDL with applications, you want to keep to small Databases with few objects. Yevich once suggested one Tablespace per Database, many years ago. 

I suggest to all, don't change an index if you can help it, create a new one, and maybe later drop the old one, if it is not needed anymore, to reduce contention and reduce impact on Packages becoming Invalidated and potentially moving to bad access paths before the new index is available.

ALTER a Unique  index to add include columns, is probably going to mean an outage, so use it rarely. I might rather build a non unique extra index if table is not so large.

Now once a CREATE INDEX DEFER wanting an Exclusive Lock to the DBD, is held up due to other Shared Lockers, further application transactions coming along can queue behind it and maybe even timeout, if the CREATE Index did not timeout in time. We had better Commit these Create INDEX DDLs very promptly when they do succeed to release that DBD lock.

In past someone even reported a deadlock on a CREATE INDEX

Advanced experience comments welcome if sheds light on the workings so we understand better.

Michael Hannan,
DB2 Application Performance Specialist