Global Temporary Table usage in Db2 z/OS
Learn, through examples, why CGTT (CREATEd Global Temporary Tables) are possibly better than DGTT (DECLAREd Global Temporary Tables) and how to use them to speed up performance.
When Global Temporary Tables were first introduced (DB2 V5.1) it was all pretty clear! You used normal DDL to CREATE a GLOBAL TEMPORARY Table that existed in the Db2 Catalog, but every process got “their” own copy. There were limitations like No UPDATE, No Single DELETE, no Default Values and ROLLBACK / COMMIT and data reuse was not simple but it all sort of worked…
Global – but not as you know it!
Then along came the brand new Global Temporary Table (DB2 V6.1) which, just to make matters clearer, they prefixed with DECLARE so that it was 100% obvious that it had *nothing* to do with the “other” temporary table… I tell ya, if I ever meet the developer who came up with these names in a dark alley one night…. But I digress… So with the new DECLARE version you suddenly *could* do Update and Delete and it came with an ON COMMIT clause to make it simpler to handle data with COMMIT – Hoorah!
Global Temporary Table in Db2 12 – All new and improved
As of Db2 12, the full list of things you can do with a CREATE GLOBAL TEMPORARY TABLE (from now on simply CGTT) as opposed to a DECLARE GLOBAL TEMPORARY TABLE (from now on simply DGTT) is:
What CGTT DGTT
MERGE No Yes
UPDATE No Yes
Single DELETE No Yes
DEFAULTs No Yes
ROLLBACK Deletes all Yes (Plus NOT LOGGED ON ROLLBACK)
COMMIT Deletes all Depends on ON COMMIT
(unless WITH HOLD)
Indexes? No Yes
WITH RETURN Yes Yes <- This is very good news
TO CLIENT
CGTT why?
So, looking at the list, you have to wonder – “Why would anyone be using CGTTs?”
Well the reason is “Performance!” –
DGTTs might do all the tricks but they also run like a dog!
Belief is ok, test is trust!
I wrote a couple of small COBOL programs that did the same thing: one using a DGTT and one using a CGTT. All the programs did, was call a section 1000 times that DECLARED the table (or obviously not!) and then called another section that inserted 10 rows and opened a cursor to then fetch these rows right back with an ORDER BY. I chose this test as most DGTT/CGTT usage is low-volume but high called rate (Think stored procedures here!) so I wanted to see what sort of overhead 1000 DGTTs caused.
No EDMPOOL here!
Monitoring is not easy as DGTT usage is not in the EDMPOOL, so I did it the “good old way” by running my tests when I was alone on the system and five times each. I was a bit shocked by the results…
What CGTT DGTT
DBM1 EXCPs 0 8,817
MSTR EXCPs 0 59,653
Db2 CPU 0.140 secs 3.546 secs
Program cpu 4.506 secs 101.124 secs
Program elapsed 4.958 secs 118.730 secs
I then changed the DGTT to also be NOT LOGGED
What DGTT
DBM1 EXCPs 66
MSTR EXCPs 47
Db2 CPU 2.446 secs
Program cpu 100.818 secs
Program elapsed 116.426 secs
Not that much better! But remember, I did not do any DELETE or UPDATE processing so your performance data may be better than mine.
Impressive!
Now the reason for all of the I/O and CPU is
all of the internals that Db2 must do for the DGTT, remember the CGTT has *already* been declared and exists in the catalog. The DGTT naturally not…
This can, as be seen, add a lot of overhead!
ROT still correct
Now the good old Rule of Thumb still holds true:
A CGTT is great for doing zero updates and sequential access!
Db2 10 improvement
When a cursor is declared with the attribute RETURN TO CLIENT then this cursor is available from the originating calling client even if the current stored procedure is down line. This is very neat as the cursor is “invisible” to all the intermediate procedures.
Death by DGTT
The problem in the past was the “hand shaking” of the final stored procedure result set. It had to be read out and put into another DGTT and then this handling was repeated all the way back up the calling chain. Now, if you have a “final cursor”, you can simply declare it as RETURN TO CLIENT – This saves tons of CPU and elapsed time!
Final tip!
Remember if you use STATIC SCROLLABLE cursors then they *must* use DGTTs in the background to actually work!
Even more work…
Then a colleague who proof-reads all my newsletters, a nasty job but someone must do it, asked me:
“I wonder what the numbers would be if you declared the GTT only once, then INSERTED/SELECTED/DELETED a bunch of rows 1000 times? Because that’s how I’m using it …“
So then I changed the programs to only DECLARE once, insert a 1000 rows, Select a 1000 rows and then do a MASS DELETE of the data.
What CGTT DGTT DGTT NOT LOGGED
DBM1 EXCPs 7,572 7,720 7,769
MSTR EXCPs 0 672 238
Db2 CPU 0.220 secs 0.310 secs 0.410 secs
Program cpu 0.346 secs 0.482 secs 0.468 secs
Program elapsed 0.704 secs 0.836 secs 0.818 secs
Much closer, in terms of performance, but still the CGTT is quicker – Time to check your usage of these Temporary Tables if you ask me!
Feel free to send me your comments and ask questions.
TTFN,
Roy Boxwell
Senior Architect