The Delightful Dozen of Db2 12

Jorg Lueke

The Delightful Dozen of Db2 12

I am curious as to what the list considers the most interesting and delightful changes found in Db2 12. I am going to take the obvious one and mention continuous availability. No more upgrades just a zparm change, a few jobs and away we go! Ok maybe there's still a CATMAINT here or there but it will be easier than even to upgrade, and presumably keep the system up the whole time so long as no one is holding annoying catalog locks (I'm looking at you replication and monitoring threads).

Anyway what do list members see as the best new features?

Roy Boxwell

The Delightful Dozen of Db2 12
(in response to Jorg Lueke)
I could write a book... but my fave is actually LISTAGG in FL501 as it greatly simplifies some of my own SQL!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/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

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

On 7 Jun 2018, at 10:24, Jorg Lueke <[login to unmask email]<mailto:[login to unmask email]>> wrote:


I am curious as to what the list considers the most interesting and delightful changes found in Db2 12. I am going to take the obvious one and mention continuous availability. No more upgrades just a zparm change, a few jobs and away we go! Ok maybe there's still a CATMAINT here or there but it will be easier than even to upgrade, and presumably keep the system up the whole time so long as no one is holding annoying catalog locks (I'm looking at you replication and monitoring threads).

Anyway what do list members see as the best new features?

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

Tony Andrews

The Delightful Dozen of Db2 12
(in response to Roy Boxwell)
Coming from the developer side, I like the ‘Piece Wise Delete’ which simplifies the deleting of
many rows of data from a table. This is a great piece of logic to have in an SQL PL stored
procedure taking advantage of the V11 AUTONOMOUS support, allowing for many rows of
a table to be deleted in ‘Pieces’ avoiding concurrency locking issues.

I wrote a stored procedure that gets passed as input parameters the ‘Table Name’, ‘Where Logic’,
and number of fetched rows to be deleted in pieces. Then the stored procedure goes thru looping
logic for each delete set and commits until there are no more rows that meet the ‘Where logic’.
Pretty simple, and the flexibility to use it for different tables, different fetch counts, etc.

From the SQL side, I agree with Roy about the LISTAGG. Also the additional logic of the MERGE
statement with the new ability to DELETE and adding of additional logic for the WHEN MATCHED.

Tony Andrews


From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Thursday, June 07, 2018 11:02 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: The Delightful Dozen of Db2 12

I could write a book... but my fave is actually LISTAGG in FL501 as it greatly simplifies some of my own SQL!
Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/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

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

On 7 Jun 2018, at 10:24, Jorg Lueke <[login to unmask email]<mailto:[login to unmask email]>> wrote:

I am curious as to what the list considers the most interesting and delightful changes found in Db2 12. I am going to take the obvious one and mention continuous availability. No more upgrades just a zparm change, a few jobs and away we go! Ok maybe there's still a CATMAINT here or there but it will be easier than even to upgrade, and presumably keep the system up the whole time so long as no one is holding annoying catalog locks (I'm looking at you replication and monitoring threads).

Anyway what do list members see as the best new features?

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

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

Horacio Villa

The Delightful Dozen of Db2 12
(in response to Tony Andrews)
Hi,

Why wasn't there a "COMMIT EVERY [n|:hv] ROWS" clause for DELETE?
No loop needed.
Regards,
Horacio Villa


Phil Grainger

The Delightful Dozen of Db2 12
(in response to Horacio Villa)
No-one likes PBR2? (or UTS PBR RPN to give its full wonderful 9 letter acronym)

________________________________

Phil Grainger

Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[cid:[login to unmask email]






From: Horacio Villa [mailto:[login to unmask email]
Sent: 07 June 2018 18:26
To: [login to unmask email]
Subject: [DB2-L] - RE: The Delightful Dozen of Db2 12

Hi,

Why wasn't there a "COMMIT EVERY [n|:hv] ROWS" clause for DELETE?
No loop needed.
Regards,
Horacio Villa



-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image002.png (5.9k)

Roy Boxwell

The Delightful Dozen of Db2 12
(in response to Phil Grainger)
Who likes 7 byte rids? And complete TS level reorgs on objects you cannot reorg at the TS level...mind you raising DSSIZE at part level is handy!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/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

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

On 7 Jun 2018, at 13:50, Grainger, Phil <[login to unmask email]<mailto:[login to unmask email]>> wrote:

No-one likes PBR2? (or UTS PBR RPN to give its full wonderful 9 letter acronym)

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----

Joe Geller

RE: The Delightful Dozen of Db2 12
(in response to Jorg Lueke)

My favorite (by quite a bit) is the new SQL pagination syntax.  (lastname, firstname) >= (:lname,:fname) is so much easier than any of the alternatives and is translated into the syntax that produces the efficient range list (NR) access path.

Joe

In Reply to Jorg Lueke:

I am curious as to what the list considers the most interesting and delightful changes found in Db2 12. I am going to take the obvious one and mention continuous availability. No more upgrades just a zparm change, a few jobs and away we go! Ok maybe there's still a CATMAINT here or there but it will be easier than even to upgrade, and presumably keep the system up the whole time so long as no one is holding annoying catalog locks (I'm looking at you replication and monitoring threads).

Anyway what do list members see as the best new features?

James Campbell

The Delightful Dozen of Db2 12
(in response to Roy Boxwell)
Roy - don't you like writing XML functions? But yes, LISTAGG is somewhat simpler.

Jorg - you still need to bounce Db2 to update your code base.

But all in all, I'm with Joe on the (lastname, firstname) >= (:lname,:fname) syntax.

James Campbell

On 7 Jun 2018 at 15:02, Boxwell, Roy wrote:

>
> I could write a book... but my fave is actually LISTAGG in FL501 as it greatly simplifies some of
> my own SQL!
>
> Roy Boxwell
> SOFTWARE ENGINEERING GmbH and SEGUS Inc.
> -Product Development-
> Heinrichstrasse 83-85
> 40239 Düsseldorf/Germany
> Tel. +49 (0)211 96149-675
> Fax +49 (0)211 96149-32
> Email: [login to unmask email]
> http://www.seg.de
>
> Software Engineering GmbH
> Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert, Ulf Heinrich 
>
> On 7 Jun 2018, at 10:24, Jorg Lueke <[login to unmask email]> wrote:
>
> I am curious as to what the list considers the most interesting and delightful changes found
> in Db2 12. I am going to take the obvious one and mention continuous availability. No more
> upgrades just a zparm change, a few jobs and away we go! Ok maybe there's still a
> CATMAINT here or there but it will be easier than even to upgrade, and presumably keep
> the system up the whole time so long as no one is holding annoying catalog locks (I'm
> looking at you replication and monitoring threads).
> Anyway what do list members see as the best new features?
>
> -----End Original Message-----
>

---
This email has been checked for viruses by AVG.
https://www.avg.com

J&#248;rn Thyssen

RE: The Delightful Dozen of Db2 12
(in response to Horacio Villa)

Hi Horacio,

Your comment was also the feedback from several of the Db2 12 ESP customers. However, it was rejected for several reasons. I no longer have my notes, but I believe one of the reasons was that any COMMIT logic is the responsibility of the application not the engine.

 

I like the use of a autonomous native stored procedures to create a general cleanup module.

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal. 

Walter Jani&#223;en

AW: The Delightful Dozen of Db2 12
(in response to Jørn Thyssen)
Hi

That was exactly the reason, but when the user write COMMIT EVERY n ROWS, then the user has taken the responsibility. Another RFE was, which was declined for the same reason: COMMIT EVERY n SECONDS, because it might be that no n rows qualify.

One of my favourite features is INSERT PARTITION and of course the optimizer enhancements especially Adaptive Index, Bubble up and the extension of NPGTHRSH to default statistics.

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: Jørn Thyssen <[login to unmask email]>
Gesendet: Freitag, 8. Juni 2018 09:49
An: [login to unmask email]
Betreff: [DB2-L] - RE: The Delightful Dozen of Db2 12


Hi Horacio,

Your comment was also the feedback from several of the Db2 12 ESP customers. However, it was rejected for several reasons. I no longer have my notes, but I believe one of the reasons was that any COMMIT logic is the responsibility of the application not the engine.



I like the use of a autonomous native stored procedures to create a general cleanup module.



Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email]<mailto:[login to unmask email]> • W: www.rocketsoftware.com http://www.rocketsoftware.com

2018 IBM Champion.

Views are personal.

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

  • image001.png (2.6k)

Raymond Bell

The Delightful Dozen of Db2 12
(in response to Tony Andrews)
Piece-wise delete. Nice. I didn’t know about that one. Cheers Tony. Mind you, took a while for me to find it. I should have gone straight to the ‘what’s new’ manual to start with. :o)

Every day’s a school day.


Raymond Bell
Db2
Hosting Services, Technology
Royal Bank of Scotland Group
3rd Floor Regents House
40-42 Islington High Street
London N1 8XL
Mob: +44 (0) 7894 608214
Email: [login to unmask email]<mailto:[login to unmask email]>

The content of this email is confidential unless stated otherwise.
[cid:[login to unmask email]

From: Tony Andrews [mailto:[login to unmask email]
Sent: 07 June 2018 17:15
To: [login to unmask email]
Subject: [DB2-L] - RE: The Delightful Dozen of Db2 12


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************

Coming from the developer side, I like the ‘Piece Wise Delete’ which simplifies the deleting of
many rows of data from a table. This is a great piece of logic to have in an SQL PL stored
procedure taking advantage of the V11 AUTONOMOUS support, allowing for many rows of
a table to be deleted in ‘Pieces’ avoiding concurrency locking issues.

I wrote a stored procedure that gets passed as input parameters the ‘Table Name’, ‘Where Logic’,
and number of fetched rows to be deleted in pieces. Then the stored procedure goes thru looping
logic for each delete set and commits until there are no more rows that meet the ‘Where logic’.
Pretty simple, and the flexibility to use it for different tables, different fetch counts, etc.

From the SQL side, I agree with Roy about the LISTAGG. Also the additional logic of the MERGE
statement with the new ability to DELETE and adding of additional logic for the WHEN MATCHED.

Tony Andrews


From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Thursday, June 07, 2018 11:02 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: The Delightful Dozen of Db2 12

I could write a book... but my fave is actually LISTAGG in FL501 as it greatly simplifies some of my own SQL!
Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/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

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

On 7 Jun 2018, at 10:24, Jorg Lueke <[login to unmask email]<mailto:[login to unmask email]>> wrote:

I am curious as to what the list considers the most interesting and delightful changes found in Db2 12. I am going to take the obvious one and mention continuous availability. No more upgrades just a zparm change, a few jobs and away we go! Ok maybe there's still a CATMAINT here or there but it will be easier than even to upgrade, and presumably keep the system up the whole time so long as no one is holding annoying catalog locks (I'm looking at you replication and monitoring threads).

Anyway what do list members see as the best new features?

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

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

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com
Attachments

  • image001.png (6.7k)

Roy Boxwell

The Delightful Dozen of Db2 12
(in response to James Campbell)
I hate XML functions... i have to drink beer before I begin and then whiskies at the end!!!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

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

> On 8 Jun 2018, at 06:19, James Campbell <[login to unmask email]> wrote:
>
> Roy - don't you like writing XML functions? But yes, LISTAGG is somewhat simpler.
>
> Jorg - you still need to bounce Db2 to update your code base.
>
> But all in all, I'm with Joe on the (lastname, firstname) >= (:lname,:fname) syntax.
>
> James Campbell
>
>> On 7 Jun 2018 at 15:02, Boxwell, Roy wrote:
>>
>>
>> I could write a book... but my fave is actually LISTAGG in FL501 as it greatly simplifies some of
>> my own SQL!
>>
>> Roy Boxwell
>> SOFTWARE ENGINEERING GmbH and SEGUS Inc.
>> -Product Development-
>> Heinrichstrasse 83-85
>> 40239 Düsseldorf/Germany
>> Tel. +49 (0)211 96149-675
>> Fax +49 (0)211 96149-32
>> Email: [login to unmask email]
>> http://www.seg.de
>>
>> Software Engineering GmbH
>> Amtsgericht Düsseldorf, HRB 37894
>> Geschäftsführung: Gerhard Schubert, Ulf Heinrich
>>
>> On 7 Jun 2018, at 10:24, Jorg Lueke <[login to unmask email]> wrote:
>>
>> I am curious as to what the list considers the most interesting and delightful changes found
>> in Db2 12. I am going to take the obvious one and mention continuous availability. No more
>> upgrades just a zparm change, a few jobs and away we go! Ok maybe there's still a
>> CATMAINT here or there but it will be easier than even to upgrade, and presumably keep
>> the system up the whole time so long as no one is holding annoying catalog locks (I'm
>> looking at you replication and monitoring threads).
>> Anyway what do list members see as the best new features?
>>
>> -----End Original Message-----
>>
>
> ---
> This email has been checked for viruses by AVG.
> https://www.avg.com
>
> -----End Original Message-----
>

Roy Boxwell

AW: The Delightful Dozen of Db2 12
(in response to Walter Janißen)
Hi Walter!
You mean the bug fix for NPGTHRSH? :)

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/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

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

On 8 Jun 2018, at 10:36, Walter Jani&#223;en <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Hi

That was exactly the reason, but when the user write COMMIT EVERY n ROWS, then the user has taken the responsibility. Another RFE was, which was declined for the same reason: COMMIT EVERY n SECONDS, because it might be that no n rows qualify.

One of my favourite features is INSERT PARTITION and of course the optimizer enhancements especially Adaptive Index, Bubble up and the extension of NPGTHRSH to default statistics.

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: Jørn Thyssen <[login to unmask email]<mailto:[login to unmask email]>>
Gesendet: Freitag, 8. Juni 2018 09:49
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: The Delightful Dozen of Db2 12


Hi Horacio,

Your comment was also the feedback from several of the Db2 12 ESP customers. However, it was rejected for several reasons. I no longer have my notes, but I believe one of the reasons was that any COMMIT logic is the responsibility of the application not the engine.



I like the use of a autonomous native stored procedures to create a general cleanup module.



Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email]<mailto:[login to unmask email]> • W: www.rocketsoftware.com http://www.rocketsoftware.com

2018 IBM Champion.

Views are personal.

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

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

Walter Jani&#223;en

AW: AW: The Delightful Dozen of Db2 12
(in response to Roy Boxwell)
Hi Roy

No, that was on official line item (or EPIC), but that enhancement could be improved, e.g. don’t use list prefetch and a sort, if a sort can be omitted, if list prefetch is not chosen. Take the right index to avoid a sort and maybe others.

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: Boxwell, Roy <[login to unmask email]>
Gesendet: Freitag, 8. Juni 2018 11:53
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: The Delightful Dozen of Db2 12

Hi Walter!
You mean the bug fix for NPGTHRSH? :)
Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/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

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

On 8 Jun 2018, at 10:36, Walter Jani&#223;en <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Hi

That was exactly the reason, but when the user write COMMIT EVERY n ROWS, then the user has taken the responsibility. Another RFE was, which was declined for the same reason: COMMIT EVERY n SECONDS, because it might be that no n rows qualify.

One of my favourite features is INSERT PARTITION and of course the optimizer enhancements especially Adaptive Index, Bubble up and the extension of NPGTHRSH to default statistics.

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: Jørn Thyssen <[login to unmask email]<mailto:[login to unmask email]>>
Gesendet: Freitag, 8. Juni 2018 09:49
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: The Delightful Dozen of Db2 12


Hi Horacio,

Your comment was also the feedback from several of the Db2 12 ESP customers. However, it was rejected for several reasons. I no longer have my notes, but I believe one of the reasons was that any COMMIT logic is the responsibility of the application not the engine.



I like the use of a autonomous native stored procedures to create a general cleanup module.



Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email]<mailto:[login to unmask email]> • W: www.rocketsoftware.com http://www.rocketsoftware.com

2018 IBM Champion.

Views are personal.

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

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

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

  • image001.png (2.6k)

Tony Andrews

The Delightful Dozen of Db2 12
(in response to Raymond Bell)
Thanks Raymond, here is a bit of my code for Piece Wise Delete logic within an SQL PL stored procedure (along with
some comments). If anyone knows how to gather delete counts, please let me know. I like the flexibility of
this to take advantage of any table with deletes that may be needed.

I have a Get Diagnostics after the Execute Immediate, but the row_count is always 0 because the statement
for diagnostics is on the Execute Immediate and not the actual delete. I could add the p_fetch_cnt to a counter
on each loop, but I won’t know how many rows were actually deleted on the last iteration when it gets a +100.

Any thoughts anyone?

--
-- Build the SQL Delete using input parameters
--

set v_sql_delete =
'DELETE FROM '|| P_table ||
P_logic ||
' FETCH FIRST ' || P_fetch_cnt ||' ROWS ONLY'
;

set p_sql_text = v_sql_delete;


-- Loop: Keep executing the Delete until no more rows (+100)
-- Need a Get Diagnostics to determine the number of
-- of rows that get deleted on each execution. But BEWARE!
-- This program is a generic program to delete from any table
-- with any WHERE logic and because of that the delete is
-- a dynamic sql statement built. The Get Diagnostics for
-- Row_Count will not work here because the Get Diagostics
-- is actually being done on the 'Execute Immediate' SQL
-- statement. Not sure how to get the number of rows
-- deleted out of the dynamic delete statement.

fetch_loop: loop

execute immediate v_sql_delete;

begin
get diagnostics
v_delete_cnt = row_count; -- row_count = 0 due to get diagnostics on the Execute Immediate
set p_tot_count = v_delete_cnt;
end;
if v_sqlcode = +100 then
leave fetch_loop;
else
set p_tot_count = p_tot_count + v_delete_cnt; -- v_delete_cnt always 0
commit;
end if;
end loop;


From: Bell, Raymond (Hosting Services, Technology) [mailto:[login to unmask email]
Sent: Friday, June 08, 2018 4:45 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: The Delightful Dozen of Db2 12

Piece-wise delete. Nice. I didn’t know about that one. Cheers Tony. Mind you, took a while for me to find it. I should have gone straight to the ‘what’s new’ manual to start with. :o)

Every day’s a school day.


Raymond Bell
Db2
Hosting Services, Technology
Royal Bank of Scotland Group
3rd Floor Regents House
40-42 Islington High Street
London N1 8XL
Mob: +44 (0) 7894 608214
Email: [login to unmask email]<mailto:[login to unmask email]>

The content of this email is confidential unless stated otherwise.
[cid:[login to unmask email]

From: Tony Andrews [mailto:[login to unmask email]
Sent: 07 June 2018 17:15
To: [login to unmask email]
Subject: [DB2-L] - RE: The Delightful Dozen of Db2 12


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************
Coming from the developer side, I like the ‘Piece Wise Delete’ which simplifies the deleting of
many rows of data from a table. This is a great piece of logic to have in an SQL PL stored
procedure taking advantage of the V11 AUTONOMOUS support, allowing for many rows of
a table to be deleted in ‘Pieces’ avoiding concurrency locking issues.

I wrote a stored procedure that gets passed as input parameters the ‘Table Name’, ‘Where Logic’,
and number of fetched rows to be deleted in pieces. Then the stored procedure goes thru looping
logic for each delete set and commits until there are no more rows that meet the ‘Where logic’.
Pretty simple, and the flexibility to use it for different tables, different fetch counts, etc.

From the SQL side, I agree with Roy about the LISTAGG. Also the additional logic of the MERGE
statement with the new ability to DELETE and adding of additional logic for the WHEN MATCHED.

Tony Andrews


From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Thursday, June 07, 2018 11:02 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: The Delightful Dozen of Db2 12

I could write a book... but my fave is actually LISTAGG in FL501 as it greatly simplifies some of my own SQL!
Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/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

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

On 7 Jun 2018, at 10:24, Jorg Lueke <[login to unmask email]<mailto:[login to unmask email]>> wrote:

I am curious as to what the list considers the most interesting and delightful changes found in Db2 12. I am going to take the obvious one and mention continuous availability. No more upgrades just a zparm change, a few jobs and away we go! Ok maybe there's still a CATMAINT here or there but it will be easier than even to upgrade, and presumably keep the system up the whole time so long as no one is holding annoying catalog locks (I'm looking at you replication and monitoring threads).

Anyway what do list members see as the best new features?

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

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

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.


Visit our website at www.rbs.com http://www.rbs.com
-----End Original Message-----

Joe Geller

RE: AW: AW: The Delightful Dozen of Db2 12
(in response to Walter Janißen)

Walter,

Terry Purcell did his presentation on Runstats challenges at the New York DB2 User group (TRIDEX) this week.  He said they would like to change the default for NPGTHRSH to 1 and want customer feedback.  That way it will favor index access both for default statistics and for those tables that had Runstats run when they were empty (such as when first created).  I suggested he open an RFE and we will vote for it :).

When I have time I will open an RFE, but it you would like to get to it first, that's ok and I will vote for it.

Joe



In Reply to Walter Janißen:

Hi Roy

No, that was on official line item (or EPIC), but that enhancement could be improved, e.g. don’t use list prefetch and a sort, if a sort can be omitted, if list prefetch is not chosen. Take the right index to avoid a sort and maybe others.

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: Boxwell, Roy <[login to unmask email]>
Gesendet: Freitag, 8. Juni 2018 11:53
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: The Delightful Dozen of Db2 12

Hi Walter!
You mean the bug fix for NPGTHRSH? :)
Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/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

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

On 8 Jun 2018, at 10:36, Walter Janißen <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Hi

That was exactly the reason, but when the user write COMMIT EVERY n ROWS, then the user has taken the responsibility. Another RFE was, which was declined for the same reason: COMMIT EVERY n SECONDS, because it might be that no n rows qualify.

One of my favourite features is INSERT PARTITION and of course the optimizer enhancements especially Adaptive Index, Bubble up and the extension of NPGTHRSH to default statistics.

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: Jørn Thyssen <[login to unmask email]<mailto:[login to unmask email]>>
Gesendet: Freitag, 8. Juni 2018 09:49
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: The Delightful Dozen of Db2 12


Hi Horacio,

Your comment was also the feedback from several of the Db2 12 ESP customers. However, it was rejected for several reasons. I no longer have my notes, but I believe one of the reasons was that any COMMIT logic is the responsibility of the application not the engine.



I like the use of a autonomous native stored procedures to create a general cleanup module.



Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email]<mailto:[login to unmask email]> • W: www.rocketsoftware.com http://www.rocketsoftware.com

2018 IBM Champion.

Views are personal.

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

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

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

Walter Jani&#223;en

AW: AW: AW: The Delightful Dozen of Db2 12
(in response to Joe Geller)
Hi Joe

Wow, Terry spoke at a user group! I think we are never lucky enough that he will present at our user group.

I already raised an RFE, which will hopefully cover my concerns, the number is 109688 and it’s public.

I am preparing a presentation for the IDUG in Malta, which I will present together with Terry and I will also cover some runstats-issues we are facing, especially the many, many STALE-reasons.

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: Joe Geller <[login to unmask email]>
Gesendet: Freitag, 8. Juni 2018 16:14
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: AW: The Delightful Dozen of Db2 12


Walter,

Terry Purcell did his presentation on Runstats challenges at the New York DB2 User group (TRIDEX) this week. He said they would like to change the default for NPGTHRSH to 1 and want customer feedback. That way it will favor index access both for default statistics and for those tables that had Runstats run when they were empty (such as when first created). I suggested he open an RFE and we will vote for it :).

When I have time I will open an RFE, but it you would like to get to it first, that's ok and I will vote for it.

Joe


In Reply to Walter Janißen:
Hi Roy

No, that was on official line item (or EPIC), but that enhancement could be improved, e.g. don’t use list prefetch and a sort, if a sort can be omitted, if list prefetch is not chosen. Take the right index to avoid a sort and maybe others.

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]><mailto:[login to unmask email]%3e>

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: Boxwell, Roy
Gesendet: Freitag, 8. Juni 2018 11:53
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: AW: The Delightful Dozen of Db2 12

Hi Walter!
You mean the bug fix for NPGTHRSH? :)
Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.de

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

On 8 Jun 2018, at 10:36, Walter Janißen <[login to unmask email]<mailto:[login to unmask email]>><mailto:[login to unmask email]%3e%3e> wrote:
Hi

That was exactly the reason, but when the user write COMMIT EVERY n ROWS, then the user has taken the responsibility. Another RFE was, which was declined for the same reason: COMMIT EVERY n SECONDS, because it might be that no n rows qualify.

One of my favourite features is INSERT PARTITION and of course the optimizer enhancements especially Adaptive Index, Bubble up and the extension of NPGTHRSH to default statistics.

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]><mailto:[login to unmask email]%3e>

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: Jørn Thyssen <[login to unmask email]<mailto:[login to unmask email]>><mailto:[login to unmask email]%3e%3e>
Gesendet: Freitag, 8. Juni 2018 09:49
An: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Betreff: [DB2-L] - RE: The Delightful Dozen of Db2 12


Hi Horacio,

Your comment was also the feedback from several of the Db2 12 ESP customers. However, it was rejected for several reasons. I no longer have my notes, but I believe one of the reasons was that any COMMIT logic is the responsibility of the application not the engine.



I like the use of a autonomous native stored procedures to create a general cleanup module.



Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e> • W: www.rocketsoftware.com http://www.rocketsoftware.com http://www.rocketsoftware.com

2018 IBM Champion.

Views are personal.

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

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

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

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

  • image001.png (2.6k)

Jorg Lueke

RE: The Delightful Dozen of Db2 12
(in response to Roy Boxwell)

That is nice for all those reports one wants to complete without leaving SQL :)
 
In Reply to Roy Boxwell:

I could write a book... but my fave is actually LISTAGG in FL501 as it greatly simplifies some of my own SQL!

Roy Boxwell

Jorg Lueke

RE: The Delightful Dozen of Db2 12
(in response to James Campbell)

True to update the loadlibs. That can be done member by member like maintenance is done now and hopefully without too manu Hold actions especially if one doesn't Activate a higher function level
 
In Reply to James Campbell:



Jorg - you still need to bounce Db2 to update your code base.

But all in all, I'm with Joe on the (lastname, firstname) >= (:lname,:fname) syntax.

James Campbell

Patrick Bossman

RE: AW: AW: The Delightful Dozen of Db2 12
(in response to Joe Geller)

I have used this technique to consolidate closely related RFEs that we're not exact.

Michael Hannan

RE: AW: AW: The Delightful Dozen of Db2 12
(in response to Walter Janißen)

In Reply to Walter Janißen:

Hi Roy

No, that was on official line item (or EPIC), but that enhancement could be improved, e.g. don’t use list prefetch and a sort, if a sort can be omitted, if list prefetch is not chosen. Take the right index to avoid a sort and maybe others.

Walter,

In my opinion, DB2 Sort is not so much to be feared as used to be.Start up overheads have dropped off. Small Sorts in memory are O.K.

The case when DB2 Sort must be avoided is when materialising a large result set, but only a portion of that result set will be Fetched, perhaps a small portion. Naturally every Cursor has OPTMIZE FOR n ROWS coded, right, when the full result set will not fetched (n is number of rows to be fetched). It is "criminal" to leave it out for Online programs. LOL

Even better for optimizing the Sort, should it still happen, is to code FETCH FIRST n ROWS only, if we know that the program will not Fetch beyond this number of rows. This can allow DB2 to provide ORDER BY with only partial result set even when ORDER BY does not match the index columns precisely. DB2 is getting very smarter every release.

So I am keen for DB2 to select an index with match cols over a TS Scan, almost always, but I don't need so much to be always a DB2 Sort avoiding one. The non-sort avoiding one could do a much smaller scan due to a really good match. The main point of the was to protect against tables that had sudden growth to  larger than the Dangerous Stats suggested. I hate to see zero Stats or small Stats  in the Catalog for this reason, unless no growth is guaranteed, or avoid TS Scan is guaranteed.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Walter Jani&#223;en

AW: AW: AW: The Delightful Dozen of Db2 12
(in response to Michael Hannan)
Michael

But the problem is, you don’t know if it is a small sort. In our company these cursors are often used in online applications, where the entire result set is not fetched. And if there is a clustered index to support the sort, why does Db2 choose list prefetch with an additional sort, when it doesn’t know anything. I would guess, the index was created on purpose to avoid the sort.

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: Donnerstag, 14. Juni 2018 12:17
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: AW: The Delightful Dozen of Db2 12


In Reply to Walter Janißen:
Hi Roy

No, that was on official line item (or EPIC), but that enhancement could be improved, e.g. don’t use list prefetch and a sort, if a sort can be omitted, if list prefetch is not chosen. Take the right index to avoid a sort and maybe others.

Walter,

In my opinion, DB2 Sort is not so much to be feared as used to be.Start up overheads have dropped off. Small Sorts in memory are O.K.

The case when DB2 Sort must be avoided is when materialising a large result set, but only a portion of that result set will be Fetched, perhaps a small portion. Naturally every Cursor has OPTMIZE FOR n ROWS coded, right, when the full result set will not fetched (n is number of rows to be fetched). It is "criminal" to leave it out for Online programs. LOL

Even better for optimizing the Sort, should it still happen, is to code FETCH FIRST n ROWS only, if we know that the program will not Fetch beyond this number of rows. This can allow DB2 to provide ORDER BY with only partial result set even when ORDER BY does not match the index columns precisely. DB2 is getting very smarter every release.

So I am keen for DB2 to select an index with match cols over a TS Scan, almost always, but I don't need so much to be always a DB2 Sort avoiding one. The non-sort avoiding one could do a much smaller scan due to a really good match. The main point of the was to protect against tables that had sudden growth to larger than the Dangerous Stats suggested. I hate to see zero Stats or small Stats in the Catalog for this reason, unless no growth is guaranteed, or avoid TS Scan is guaranteed.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

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

  • image001.png (2.6k)

Michael Hannan

RE: AW: AW: AW: The Delightful Dozen of Db2 12
(in response to Walter Janißen)

In Reply to Walter Janißen:

Michael

But the problem is, you don’t know if it is a small sort. In our company these cursors are often used in online applications, where the entire result set is not fetched. And if there is a clustered index to support the sort, why does Db2 choose list prefetch with an additional sort, when it doesn’t know anything. I would guess, the index was created on purpose to avoid the sort.

Walter,

You probably know the answer to your own question. DB2 Optimizer calculates cost of Fetching the entire result set, if you don't tell it otherwise. List Prefetch and the a Sort can perform vastly better than death by random data Getpage, for the ENTIRE result set.

When we don't want the entire result set, and the Optimizer does not know this, we are required to tell it, using OPTIMIZE FOR 50 ROWS or however many will be our maximum Fetch. This is not the default, and IBM are not going to change the default. The Optimizer would need this to see that a sort avoidance becomes the best access path.

Sites should have a programming standard for online Cursors to ALWAYS code OPTIMIZE FOR or FETCH FIRST n ROWS ONLY. 

The original issue was for when the Stats say the table is very small but it might have grown large, so we wanted an index chosen with a good match. It is quite likely that the table having suddenly grown large, will not perform great with a badly clustered index used without Prefetch.

Should the default be to assume a low number of Fetches. Very Dangerous in batch process. No one way works for all cases.

The site should have something to detect Realtime Stats have grown well beyond the Optimizer's Catalog Stats and collect new Stats. Should it suddenly switch to a Sort Avoidance access path due to high growth? I don't think that makes sense really. If static SQL you get to keep your old access path till REBIND. A path that was good for no data may be bad for large data, whether has a DB2 Sort or not.

Imagine we have

SELECT cols FROM TBLA

WHERE COL1 LIKE :hostvar

AND COL2 = :hv2

AND COL3 = :hv3

ORDER BY  COL1

 

Do we want the sort avoidance access path with MC=1 on COL1 only (the LIKE may do zero filtering), or do we want another index with several Matched cols and a Sort.  I would go for the later, given there is nothing to indicate we will only Fetch a few rows. Even if we do Fetch just a few rows, if other matched predicates could do strong filtering, the Sort Avoidance path might have to scan a long way to find qualifying rows.

There is no easy way to say Sort Avoidance path is always the good one. More the exception rather than the rule.

Important search queries need to be coded carefully to have good performance. They may need dynamic SQL with a good set of Stats. Leaving out OPTIMIZE clauses is not advisable for limited Fetch cases. 

Can the Optimizer correct all developer SQL shortcomings? Not quite. Sometimes the SQL has to be fixed, and better Stats collected. Also don't allow Static access paths that will become bad when the table grows. Get them right beforehand.

I am very critical of the Optimizer when it does not choose the best set of matching columns, including all join predicates possible to be matched to avoid semi-Cartesian joins, by being too Optimistic. I am not going to change and say I prefer a weaker index match that avoids the DB2 Sort when there is no OPTIMIZE FOR clause.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd