DB2 COBOL Stored Procedure and GTT Global Temp Table

Peggy Haber

DB2 COBOL Stored Procedure and GTT Global Temp Table

Hello,  Is a GTT required when sending a result set from a COBOL DB2 stored procedure from a mainframe?  I see I can send my result set without the GTT but I am not sure if this is a good idea.  When I do this I see that I save a lot of MIPS.  Thank you for any suggestions on this topic.

Michael Hannan

RE: DB2 COBOL Stored Procedure and GTT Global Temp Table
(in response to Peggy Haber)

Peggy,

Ideally you should be having a clear idea of why you need a "Global" Temp Table. The data is only going to exist for your transaction (destroyed by Commit or Rollback unless Cursor with Hold.). What is advantage over Stored Proc returning a Cursor result? Typical use is when some intermediate result will be utilised in more than one following SQL query in same Stored Proc or same transaction, not just for scrolling through a result set, I think.

Disadvantages are: Data is not Global (only definition), no indexes (to assist access path in further reference), Tuning queries that reference them can be more difficult. Advantages: No logging necessary, no Reorg, etc.

I am not a big fan of these "Global" Temp Tables at all. Use very sparingly is my thought, where can save MIPS rather than costing MIPS, or where MIPS overhead is low and can find a benefit. Not a big fan of  Scrollable Cursors also, which can be a lot more costly than standard Cursor Fetching. 

I much prefer being able to reuse some materialised intermediate results in a single Query using Common Table Expressions, when I want intermediate results for multiple reuse reference in the query. CTEs don't have to have a materialise access path though, when only referenced once. 

Another point however is that Native Stored Procs can have a lot less overheads than a Cobol Stored Proc, so where performance matters, reevaluate why you have to have a COBOL Stored Proc. 

When performance is not an issue (low usage), then we do whatever is best for ease of maintenance of the code, I guess. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

bernd oppolzer

DB2 COBOL Stored Procedure and GTT Global Temp Table
(in response to Michael Hannan)
There are two types of GTTS:

- CREATED GTTS, which came first, IIRC;
they indeed don't have indexed etc. like described below

- DECLARED GTTs (slightly different syntax)
they can have indexes
they can survive COMMITs etc., until process ends
they need not be defined, that is, they are declared dynamically at runtime

I used DECLARED GTTs in some special applications,
for example to store keys of data for later reference
and it worked without problems.

With CREATED GTTs, we had a performance problem (as I was told)
some years before; not with DECLARED GTTs. I like very much the fact
that I can store temporary data with the help of DB2 and join this data to
"normal" DB2 tables ... and I don't need the DB2 admins
to design these temporary tables. Of course, this means some
resposibility for the application developer.

Kind regards

Bernd



Am 08.01.2019 um 05:54 schrieb Michael Hannan:
>
> Peggy,
>
> Ideally you should be having a clear idea of why you need a "Global"
> Temp Table. The data is only going to exist for your transaction
> (destroyed by Commit or Rollback unless Cursor with Hold.). What is
> advantage over Stored Proc returning a Cursor result? Typical use is
> when some intermediate result will be utilised in more than one
> following SQL query in same Stored Proc or same transaction, not just
> for scrolling through a result set, I think.
>
> Disadvantages are: Data is not Global (only definition), no indexes
> (to assist access path in further reference), Tuning queries that
> reference them can be more difficult. Advantages: No logging
> necessary, no Reorg, etc.
>
> I am not a big fan of these "Global" Temp Tables at all. Use very
> sparingly is my thought, where can save MIPS rather than costing MIPS,
> or where MIPS overhead is low and can find a benefit. Not a big fan
> of  Scrollable Cursors also, which can be a lot more costly than
> standard Cursor Fetching.
>
> I much prefer being able to reuse some materialised intermediate
> results in a single Query using Common Table Expressions, when I want
> intermediate results for multiple reuse reference in the query. CTEs
> don't have to have a materialise access path though, when only
> referenced once.
>
> Another point however is that Native Stored Procs can have a lot less
> overheads than a Cobol Stored Proc, so where performance matters,
> reevaluate why you have to have a COBOL Stored Proc.
>
> When performance is not an issue (low usage), then we do whatever is
> best for ease of maintenance of the code, I guess.
>
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>
>
> -----End Original Message-----

Tony Andrews

DB2 COBOL Stored Procedure and GTT Global Temp Table
(in response to Peggy Haber)
GTTs are not required, and typically just returning the result set proves to be more efficient. I only
load up a GTT in a SP if the data to be sent back is not of Db2, or the calling program needs the data in a table to
run queries against or join it to another table.

Tony Andrews


From: Peggy Haber [mailto:[login to unmask email]
Sent: Monday, January 07, 2019 5:18 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 COBOL Stored Procedure and GTT Global Temp Table


Hello, Is a GTT required when sending a result set from a COBOL DB2 stored procedure from a mainframe? I see I can send my result set without the GTT but I am not sure if this is a good idea. When I do this I see that I save a lot of MIPS. Thank you for any suggestions on this topic.

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

Joe Geller

RE: DB2 COBOL Stored Procedure and GTT Global Temp Table
(in response to Tony Andrews)

In the past, I've seen applications that used GTTs and the cursor to return the data Selected from the GTT.  They did this if the caller was from a remote server and the query against the real tables did not have to build a result set (no sorts were done) which means each row is retrieved one at a time with each fetch (from the caller).  With network delays (which were longer 15 years ago), locks would be held longer.  By using the GTT for the returned data, the locking on the real tables was minimized.

Joe

In Reply to Tony Andrews:

GTTs are not required, and typically just returning the result set proves to be more efficient. I only
load up a GTT in a SP if the data to be sent back is not of Db2, or the calling program needs the data in a table to
run queries against or join it to another table.

Tony Andrews


From: Peggy Haber [mailto:[login to unmask email]
Sent: Monday, January 07, 2019 5:18 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 COBOL Stored Procedure and GTT Global Temp Table


Hello, Is a GTT required when sending a result set from a COBOL DB2 stored procedure from a mainframe? I see I can send my result set without the GTT but I am not sure if this is a good idea. When I do this I see that I save a lot of MIPS. Thank you for any suggestions on this topic.

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

Michael Hannan

RE: DB2 COBOL Stored Procedure and GTT Global Temp Table
(in response to Joe Geller)



In Reply to Joe Geller:

In the past, I've seen applications that used GTTs and the cursor to return the data Selected from the GTT.  They did this if the caller was from a remote server and the query against the real tables did not have to build a result set (no sorts were done) which means each row is retrieved one at a time with each fetch (from the caller).  With network delays (which were longer 15 years ago), locks would be held longer.  By using the GTT for the returned data, the locking on the real tables was minimized.

Joe

People should use Block Fetch (in distributed situation) with Cursors defined for FETCH ONLY or for READ ONLY, to minimize interactions between Client and Server. Consider coding WITH UR to avoid locking as well. Even without UR, lock avoidance is possible, but not guaranteed. Obviously these things don't work on Cursor for Update, but then we would not be putting rows in a GTT anyway. OPTIMIZE FOR n ROWS  should always be coded (or Fetch First n Rows Only), to help with optimizing the access path and Fetch. 

I doubt that the GTTs was the best solution to avoid holding locks, even if was a possible approach. It did not guarantee the underlying data was not subsequently changed. In my mind, WITH UR that sees uncommitted changes potentially to be rolled back is no worse. Either way, the data may change after the Cursor has read it, with or without locking. Others may have a more conservative view, but major objective was to avoid locking the data.

Some may not realize that in Data Sharing, a Cursor WITH CS is not even guaranteed to see a change just made on another member a brief moment ago. Not all members copies of the page have yet been invalidated. The rational is, the change could happen slightly before the CS Cursor reads or slightly after, by pure chance, so it should make not make a difference. So UR is not so vastly different to CS (in my thinking).

DB2 uses locking to guarantee we don't have divergent updates to the same row by serializing. Nothing guarantees rows won't change after we read them unless we use For Update, Repeatable Read, or at least Read Stability for the Transaction duration. As soon as transaction completes the data could be viewed as potentially out of date anyway, if there are other processes able to update it.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd