Dynamic SQL with multi row fetch

Renato Delbrueck

Dynamic SQL with multi row fetch

This is about DB2 for z/OS:

Is it possible to build a multi-row fetch into a Cobol program that performs a dynamic SQL?

The mentioned program is a subprograma that receives a string which contains a SQL query. It then performs the requested query through the following commands:

DECLARE C001 CURSOR FOR STMT

PREPARE  STMT FROM :SQLSTRING

DESCRIBE STMT INTO :SQLDA

And finally

FETCH C001 USING DESCRIPTOR :SQLDA

i.e., it uses the SQLDA structure.

Can such a program perform a multi-row fetch?

What should be taken into account for this to work out?

Avram Friedman

RE: Dynamic SQL with multi row fetch
(in response to Renato Delbrueck)

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/apsg/src/tpc/db2z_executesqlrowsetcursor.html

 

Google is my friend, can I introduce ypu?

Avram Friedman
DB2-L hall of fame contributer
DB2-L acting administrator

[login to unmask email]

Walter Janißen

AW: Dynamic SQL with multi row fetch
(in response to Renato Delbrueck)
I believe yes, because DSNTIAUL and DSNTEP4 do exactly this.

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: Renato Delbrueck [mailto:[login to unmask email]
Gesendet: Dienstag, 27. Februar 2018 20:22
An: [login to unmask email]
Betreff: [DB2-L] - Dynamic SQL with multi row fetch


This is about DB2 for z/OS:

Is it possible to build a multi-row fetch into a Cobol program that performs a dynamic SQL?

The mentioned program is a subprograma that receives a string which contains a SQL query. It then performs the requested query through the following commands:

DECLARE C001 CURSOR FOR STMT

PREPARE STMT FROM :SQLSTRING

DESCRIBE STMT INTO :SQLDA

And finally

FETCH C001 USING DESCRIPTOR :SQLDA

i.e., it uses the SQLDA structure.

Can such a program perform a multi-row fetch?

What should be taken into account for this to work out?

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

  • image001.png (2.6k)

Renato Delbrueck

RE: AW: Dynamic SQL with multi row fetch
(in response to Walter Janißen)

Vielen dank, Herr Janißen. Es handelt sich aber in diesem Fall um eine Anpassung eines bestehenden Programms. Denken sie, diese Tools – DSNTIAUL oder DSNTEP4 – könnten das Verfahren vereinfachen?

Walter Jani&#223;en

AW: AW: Dynamic SQL with multi row fetch
(in response to Renato Delbrueck)
Ich wollte damit nur sagen, dass es möglich ist.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Spezialisten
Technische Anwendungsarchitektur
Victoriaplatz 2
40477 Düsseldorf

Tel 0211 477-2928
Fax 0211 477-7146
[login to unmask email]

Vorsitzender des Aufsichtsrats: Dr. Christoph Jurecka
Geschäftsführung: Tomasz Smaczny (Vorsitzender),
Gary Robert Dashwood, Lothar Engelke, Joachim Fensch, Oliver Gasmus,
Bernd Jung, Paul Spiteri
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996
Von: Renato Delbrueck [mailto:[login to unmask email]
Gesendet: Freitag, 2. März 2018 13:01
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: Dynamic SQL with multi row fetch


Vielen dank, Herr Janißen. Es handelt sich aber in diesem Fall um eine Anpassung eines bestehenden Programms. Denken sie, diese Tools – DSNTIAUL oder DSNTEP4 – könnten das Verfahren vereinfachen?

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

Michael Hannan

RE: Dynamic SQL with multi row fetch - MRF is not always a solution
(in response to Renato Delbrueck)

In Reply to Renato Delbrueck:

Can such a program perform a multi-row fetch?

What should be taken into account for this to work out?

Others answered well. A couple of things. Sometimes savings from Multi-row Fetch is quoted at up to 50%. This is really useless and misleading information. The savings are not a percentage of the total Cursor cost at all, only a portion of the Fetch call overheads.

It is not worth the effort to code for multi-row Fetch unless you typically have a very large number of Fetches per day especially in Peak Hours, on that Cursor. Also if you have only an average of 1.1Fetches per Cursor Open (when most Fetches are a Not Found), the MRF does not help you. You might need at least a few Fetches per Open to make MRF worthwhile.

Consider this a batch Fetch might have an overhead of a 5 microsecs (depending on processor speed). That is on a slow engine. If you save 1 million Fetches through using multi-row fetch you might save 5 seconds of CPU. Not a lot if at a time when it does not matter. In CICS the overheads can be about 3 times higher, if not using Thread safe.

The message is use your effort to code multi-row Fetch on the most Fetch intensive processes in the installation.

I have seen a lot of MRF used where it just was not necessary, then again not used when Fetches were really huge. Sometimes puzzling choices.

Akira Shibamiya quoted Fetch call overhead as about 0.1 millisecs on an old 3090 model 180 uniprocessor I believe so its much much lower in the modern day.

You can find an idea of your Fetch minimum Class 7 CPU overhead guess on your machine if you have a CPU monitoring tool and find a very high frequency Fetch where all the query work was materialised by the Cursor Open, leaving the Fetch to do very little work other than return the rows (hopefully very few columns). Class 7 is only part of overhead in CICS without threadsafe.

Once we had a suggestion to setup QMF to use Multi-Row Fetch option. We were asked what the savings might be achieved too. Based on total Fetches per day occurring in QMF not being very high, we estimated savings to be well below 1 MIPS, and virtually unmeasurable. Mostly QMF is used to report highly summarised data with relatively few Fetches in total. DSNTIAUL is a much better candidate for MRF, since it is often used to unload huge row sets.

Do your top 5 high frequency Fetching Cursors in your installation in peak hours have Multi-Row Fetch? If not, don't look elsewhere first.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Mar 04, 2018 - 10:10 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 04, 2018 - 10:14 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 04, 2018 - 10:17 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 04, 2018 - 10:25 AM (Europe/Berlin)

Roy Boxwell

Dynamic SQL with multi row fetch
(in response to Michael Hannan)
Yep! As always it depends on the use case. I have seen great savings and also none...
Gather data, evaluate, code and test to prove that it really helped!

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

On 4 Mar 2018, at 09:51, Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>> wrote:


In Reply to Renato Delbrueck:

Can such a program perform a multi-row fetch?

What should be taken into account for this to work out?

Others answered well. A couple of things. Sometimes savings from Multi-row Fetch is quoted at up to 50%. This is really useless an misleading information. The savings are not a percentage of the total Cursor cost at all, only a portion of the Fetch call overheads.

It is not worth the effort to code for multi-row Fetch unless you typically have a very large number of Fetches per day especially in Peak Hours. Also if you have only an average of 1.1Fetches per Cursor Open (when most Fetches are a Not Found), the MRF does not help you. You might need at least a few Fetches per Open to make MRF worthwhile.

Consider this a batch Fetch might have an overhead of a 5 microsecs (depending on processor speed). That is on a slow engine. If you save 1 million Fetches through using multi-row fetch you might save 5 seconds of CPU. Not a lot if at a time when it does not matter. In CICS the overheads can be about 3 times higher, if not using Thread safe.

The message is use your effort to code multi-row Fetch on the most Fetch intensive processes in the installation.

I have seen a lot of MRF used where it just was not necessary, then again not used when Fetches were really huge. Sometimes puzzling choices.







Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

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

Michael Hannan

RE: Dynamic SQL with multi row fetch
(in response to Roy Boxwell)

In Reply to Roy Boxwell:

Yep! As always it depends on the use case. I have seen great savings and also none...
Gather data, evaluate, code and test to prove that it really helped!

If changing existing Cursors to MRF, you will want to know which Cursors might benefit, to be sure effort is worthwhile. Class 2 Acctng captures Fetch Counts at Thread level (by Tran or Job).  Class 10 Acctng Captures Package Level Fetch Counts. Class 7 has SQL counts all types in general only at Package level (might be able to guess Fetch levels especially if only one SQL section in the Package).

Various products like Apptune, Detector, IBM Query Monitor, Strobe, APA, etc. can show specific Cursors with very high Fetch Counts, and exclude all those Cursors that are already ROWSET type (MRF).

You then multiply the total number of Fetches for a Cursor in a time period by estimated Fetch overhead to get potential MRF savings. If can't prove a good saving, in advance, then don't bother. You don't want to put it in and measure very little benefit.

Total CPU used by Cursor in Open and Fetches is not very relevant, since MRF savings are not a percentage of the CPU used.

If writing new code, the programmer needs to estimate the total Fetch Counts predicted per day or per peak period. If Fetch count is not amongst the very high counts, MRF is not really needed. 100 million row Fetches per day on a single Cursor, you will want it. Even 10 Million should be worthwhile if used in peak times. 100 thousand row fetches, don't bother!

Its that simple, but not well understood generally. That is why I write these tips. I just want to see MRF used wisely.

MRF is not something that can make savings to Cursors in general! Just to very specific carefully determined cases.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Renato Delbrueck

RE: Dynamic SQL with multi row fetch
(in response to Michael Hannan)

Hi Michael,

 

Thank you so much for all the information!