Interesting Aha Idea to vote for (Db2 for z/OS)

Roy Boxwell

Interesting Aha Idea to vote for (Db2 for z/OS)
Hi All!



I think we all know the pain of changing a DBD during dynamic SQL workload.
In the past this was not soooo important as dynamic SQL was “special” but
nowadays it is the de facto standard and so simply DROPing a dead table that
happens to be in the same database as your central, most used, table is a
real pain point. I have seen a very nice idea:



https://ibm-data-and-ai.ideas.aha.io/ideas/DB24ZOS-I-1076



That raises the desire to somehow change the way the DBD is locked and
updated in cases like a DROP of a table. I can see this as an extension of
Phase In Rebind. Why not slide the DBD change out gradually?



Whaddya all think???



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur
Datenschutzerklärung


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



Attachments

  • smime.p7s (5.1k)

Roland Schock

AW: Interesting Aha Idea to vote for (Db2 for z/OS)
(in response to Roy Boxwell)
Hi Roy,

the most important is to register at aha.io and vote for such an idea. If Db2 development sees, that many people are interested in such an idea, then they will put it into consideration for development.
So from time to time I browse through the ideas an vote for all, which make sense for me and would be a nice extension for Db2.

This time I voted for your RFE even if it is for z/OS and I'm only on LUW.

Mit freundlichen Grüßen/Kind regards
Roland Schock
[ibm-champion-rgb-130px]

Von: Boxwell, Roy <[login to unmask email]>
Gesendet: Donnerstag, 13. Februar 2020 08:20
An: [login to unmask email]
Betreff: [Marketing Mail] [DB2-L] - Interesting Aha Idea to vote for (Db2 for z/OS)

Hi All!

I think we all know the pain of changing a DBD during dynamic SQL workload. In the past this was not soooo important as dynamic SQL was "special" but nowadays it is the de facto standard and so simply DROPing a dead table that happens to be in the same database as your central, most used, table is a real pain point. I have seen a very nice idea:

https://ibm-data-and-ai.ideas.aha.io/ideas/DB24ZOS-I-1076

That raises the desire to somehow change the way the DBD is locked and updated in cases like a DROP of a table. I can see this as an extension of Phase In Rebind. Why not slide the DBD change out gradually?

Whaddya all think???

Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
Web http://www.seg.de http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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

[login to unmask email]
Telefon+Mobil +49 89 32468-130

________________________________
ARS Computer und Consulting GmbH, www.ars.de http://www.ars.de
ein Unternehmen der TIMETOACT Group
Garmischer Straße 7, 80339 München, Deutschland

ARS - The Art of Software Engineering
Software Engineering, Technologieberatung, IBM Software, IBM Lizenzmanagement Services

Handelsregister München, HRB 101829, USt-ID: DE 155 068 909
Geschäftsführer: Joachim Gucker, Stefan Schäffer, Michael Arbesmeier
Datenschutzerklärung: https://web.ars.de/datenschutz/
Attachments

  • image001.jpg (3.8k)

Michael Hannan

RE: Interesting Aha Idea to vote for (Db2 for z/OS)
(in response to Roy Boxwell)

In Reply to Roy Boxwell:

I think we all know the pain of changing a DBD during dynamic SQL workload.
In the past this was not soooo important as dynamic SQL was “special” but
nowadays it is the de facto standard and so simply DROPing a dead table that
happens to be in the same database as your central, most used, table is a
real pain point. I have seen a very nice idea:

https://ibm-data-and-ai.ideas.aha.io/ideas/DB24ZOS-I-1076

That raises the desire to somehow change the way the DBD is locked and
updated in cases like a DROP of a table. I can see this as an extension of
Phase In Rebind. Why not slide the DBD change out gradually?

Whaddya all think???

Roy,

No, not sure I fully understand the problem. In some earlier version of DB2, maybe as early as V5 I recall Jim Teng presenting on improved concurrency (maybe not relevant for this one - unsure), anyway at some point share locking on DBD  by Dynamic if using Cache was alleviated.

Quoting the DB2 V9 Redbook on Resource Serialisation:

"DBD locks
Without statement caching, dynamic DML SQL statements acquire an S-lock on the database descriptor (DBD), when the statement is prepared. This prevents DDL from executing in the database that has been referenced by the dynamic DML statement. The lock is held until the thread reaches a commit point.
When using global statement caching (CACHEDYN=YES), no DBD lock is acquired for short prepares (retrieving a statement from the cache) or for full prepares (inserts into the cache) for SELECT, INSERT, UPDATE, MERGE, or DELETE statements, provided statement caching has not been prohibited for another reason. Those other reasons include the use of the REOPT(ALWAYS) bind option, or if DDL was performed by the application process in the same unit of work. TRUNCATE and EXCHANGE both take a DBD lock."

Maybe someone could explain the exact scenario in a little more detail, where Dynamic SQL is a problem share locker of the DBD. Not using the Cache would be silly, right? Maybe REOPT ALWAYS is the problem, surely not DDL in the application?

Normally parameter markers are used in Dynamic because we don't want to REOPT, however asking for REOPT is still possible. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Mar 06, 2020 - 01:52 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2020 - 02:05 PM (Europe/Berlin)

Roy Boxwell

Interesting Aha Idea to vote for (Db2 for z/OS)
(in response to Michael Hannan)
Dynamic SQL is *always* locking the DBD and it is a real pain point at every customer site I go to...



I don’t think IBM will actually rewrite their DBD design, but hope springs eternal! Naturally you can go the one TS with one TB in one DB route and that would alleviate the problem, but at the cost of drastically changing the design of every database in existence...ho hum...





Glad it is Friday!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


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



From: Michael Hannan [mailto:[login to unmask email]
Sent: Friday, March 6, 2020 1:49 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Interesting Aha Idea to vote for (Db2 for z/OS)



In Reply to Roy Boxwell:

Hi All!



I think we all know the pain of changing a DBD during dynamic SQL workload.
In the past this was not soooo important as dynamic SQL was “special” but
nowadays it is the de facto standard and so simply DROPing a dead table that
happens to be in the same database as your central, most used, table is a
real pain point. I have seen a very nice idea:



https://ibm-data-and-ai.ideas.aha.io/ideas/DB24ZOS-I-1076



That raises the desire to somehow change the way the DBD is locked and
updated in cases like a DROP of a table. I can see this as an extension of
Phase In Rebind. Why not slide the DBD change out gradually?

Whaddya all think???

Roy,

Not sure i fully understand the problem. In some earlier version of DB2, maybe as early as V5 I recall Jim Teng presenting on improved concurrency (maybe not relevant for this one - unsure), anyway at some point share locking on DBD by Dynamic if using Cache was alleviated.

Quoting the DB2 V9 Redbook on Resource Serialisation:

"DBD locks
Without statement caching, dynamic DML SQL statements acquire an S-lock on the database descriptor (DBD), when the statement is prepared. This prevents DDL from executing in the database that has been referenced by the dynamic DML statement. The lock is held until the thread reaches a commit point.
When using global statement caching (CACHEDYN=YES), no DBD lock is acquired for short prepares (retrieving a statement from the cache) or for full prepares (inserts into the cache) for SELECT, INSERT, UPDATE, MERGE, or DELETE statements, provided statement caching has not been prohibited for another reason. Those other reasons include the use of the REOPT(ALWAYS) bind option, or if DDL was performed by the application process in the same unit of work. TRUNCATE and EXCHANGE both take a DBD lock."

Maybe someone could explain the exact scenario in a little more detail, where Dynamic SQL is a problem share locker of the DBD.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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

Attachments

  • smime.p7s (5.1k)

Michael Hannan

RE: Interesting Aha Idea to vote for (Db2 for z/OS)
(in response to Roy Boxwell)

So where did I go wrong Roy?

The redbook on Resource Serialization seems to suggest that Dynamic SQL is not *always* locking the DBD any more. Of course the redbook could have got it wrong I guess, or I am misinterpreting. It could be important to fathom the reason why it needs to take a DBD lock, if it needs it. 

It does need to take gross locks on objects used (not DBD locks), to prevent them disappearing from a DROP, but why the DBD lock? DB2 should be trying to use lock avoidance where possible. I guess the occurence of DDL making changes, could be the trigger to require the Dynamic SQL to get a share lock, in order to ensure changes by DDL processes are committed, whether relevant or not.

Naturally I would not have noticed it if it did still take a share lock, due to not making any DBD changes, concurrently.

Perhaps I will be in a position to check the locks taken by some test dynamic SQLs sometime soon.

I recall R Yevitch suggesting just one TS per DBD, Ha ha, as at DB2 V2.3 for improved concurrency. Maybe DBD locking should be made more granular like that, if possible.

Here is another perhaps contradictory part of the guide:

"Each time a database or a dependent object definition is modified, the DBD object in the
directory must be updated. This update process requires an exclusive lock on the DBD, which is incompatible with dynamic SQL and utility execution. If the DBD is in use for a plan or package, dynamic SQL and utility execution are suspended."

I think I am going to need to test it, to be sure what really happens.

My further thought is that DDL has to X Lock till Commit. If a long running dynamic SQL should take an S lock on DBD for some reason (to ensure latest copy in EDM pool changes are committed etc.), then it ought to release it promptly (not waiting till Commit) so that DDL could get a look in. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Mar 06, 2020 - 02:36 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2020 - 02:41 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2020 - 02:47 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2020 - 02:53 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2020 - 03:07 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2020 - 03:08 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2020 - 03:33 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2020 - 03:35 PM (Europe/Berlin)

Jorg Lueke

RE: Interesting Aha Idea to vote for (Db2 for z/OS)
(in response to Roy Boxwell)

You must go to a lot of SAP sites. Lol.

In Reply to Roy Boxwell:

Dynamic SQL is *always* locking the DBD and it is a real pain point at every customer site I go to...



I don’t think IBM will actually rewrite their DBD design, but hope springs eternal! Naturally you can go the one TS with one TB in one DB route and that would alleviate the problem, but at the cost of drastically changing the design of every database in existence...ho hum...