UDB V8.2 (FP1) - DGTT WITH REPLACE vs. delete * from DGTT

[UDBDBA]

All:

Anyone had bad experience with doing DECLARE DGTT "WITH REPLACE"
option?
The suggested workaround was to do "DELETE FROM DGTT" sql statement.

We are seeing DGTT performance issue when replacing existing DGTT while
declaring it.

Which way is better?

Thanks!

Vijay

[Serge Rielau]

UDBDBA wrote:

All:

Anyone had bad experience with doing DECLARE DGTT "WITH REPLACE"
option?
The suggested workaround was to do "DELETE FROM DGTT" sql statement.

We are seeing DGTT performance issue when replacing existing DGTT while
declaring it.

Look up: "Using temporary tables" The example is about CREATE/DROP, but
the same holds for REPLACE:
http://www-128.ibm.com/developerwork...dm-0501rielau/

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[UDBDBA]

Hi Serge:

Thanks. I am using SQL PL profiler and have already read your technote.
It doens't say which way is faster (maybe i am not seeing the obvious
here). Do you think DGTT WITH REPLACE would be faster than DELETE rows
from DGTT?
The DECLARE "WITH REPLACE" option was taking up too much time (upto
1.5 seconds just to declare it with replace). The second highest is
opening a cursor which has reference to this temporary table. I have
SQLPL output. can i email you the screenshot please? (once again this
is for the "railroad application - TYES" :)

So the app. dev. team did the following to address DGTT slowdown:
1. Declare DGTT in the calling cobol program just once.
2. The cobol program is calling a stored procedure.
3. The stored procedure opens cursors which selects from regular tables
and the dgtt declared in the calling cobol program
4. Do insert/update operations on dgtt
5. Return control back to cobol code to delete rows from DGTT
Repeat step 1 thru 5

After doing the steps mentioned above, it is still slow and spends time
creating DGTT and declaring cursor on DGTT with other tables.

Any help is appreciated.

Vijay

[Serge Rielau]

Hi Vijay,

The (funky) DELETE will be faster.
The WITH REPLACE option will case the following codepath to execute:
* truncate the "old" temp table (free the space in the user temp)
* drop the DGTT
* invalidate all SQL statements which refer to this DGTT in this session
* declare the new temp
* recompile every SQL statement which refers to this DGTT in thsi
session as it executes teh first time.

Compare with (funky) delete:
* truncate the temp table
* done!

At the very minimum you will save CPU for the recompilation. The more
iterations the proc goes through the bigger the gain.
Note: Do not use a regular DELETE FROM SESSION.TEMP. First it will be
more codepath and second temps are append only, so you'll start leaking
user temp space.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[UDBDBA]

Hi Serge:

IMPORT or LOAD does not work on DGTT.
$ db2 " import from dummy.del of del replace into
SESSION.\"#ts155a_move_data\" "
SQL3015N An SQL error "-526" occurred during processing.
SQL0526N The requested function does not apply to declared temporary
tables.
SQLSTATE=42995
Can you elaborate on your note:

Compare with (funky) delete:
* truncate the temp table
* done!

Thanks.

Vijay

[Serge Rielau]

UDBDBA wrote:

Hi Serge:

IMPORT or LOAD does not work on DGTT.
$ db2 " import from dummy.del of del replace into
SESSION.\"#ts155a_move_data\" "
SQL3015N An SQL error "-526" occurred during processing.
SQL0526N The requested function does not apply to declared temporary
tables.
SQLSTATE=42995 Correct. Feel free to submit a requirement.
IMPORT is certainly not hard....
Can you elaborate on your note:

Compare with (funky) delete:
* truncate the temp table
* done!

Not much to elaborate.
When you declare tge global temp outside that loop of your then there
will be no statement ivalidation inside the loop, so no recompiles either.
Of course there will be be no DROP/DECLARE or DECLARE WITH REPLACE
because you moved it outside the loop.
So the only thing that's needed now is to do the DELETE.
Causing a run time error during a delete/update/insert to a global temp
which is NOT LOGGED will cause truncation (using the same algorithms
used on DROP/REPLACE.
So:
DELETE FROM SESSION.TEMP WHERE pk = CAST(raise_error('78000', 'bang') AS
INT);
will truncate the temp as efficient as possible.

If the cost simply moved from the DECLARE to the DELETE as seen in the
profiler, then that means that the cost was indeed mostly in the
truncation of temp.. Not much that can be done about that then....

Cheers
Serge

PS: Feel free to send me that screenshot
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[UDBDBA]

Hi Serge:

Awesome!

Does it mean that if we did the DELETE with raise_error, will it ONLY
truncate the temp table?
Does it invalidate the temp table which then invalidate other dependent
objects in that session?

I will send you the screenshot for the procedure (The proc does not
have the DECLARE and DELETE part in it). The cobol package has the
DECLARE WITH REPLACE and DELETE.

How can i measure the impact of DELCARE WITH REPLACE and DELETE
statements inside cobol programs? SQL PL Profiler is very nice for
stored procs!

Thank you,

Vijay

[Serge Rielau]

UDBDBA wrote:

Hi Serge:

Awesome!

Does it mean that if we did the DELETE with raise_error, will it ONLY
truncate the temp table? Yes Does it invalidate the temp table which then invalidate other dependent
objects in that session? No. It's TRUNCATE TABLE for humble developers. No unpleasant side-effects.
The semantics are: "on error - truncate" very different from a
persistent table with not logged which is "on error - drop pending"
I will send you the screenshot for the procedure (The proc does not
have the DECLARE and DELETE part in it). The cobol package has the
DECLARE WITH REPLACE and DELETE. ok
How can i measure the impact of DELCARE WITH REPLACE and DELETE
statements inside cobol programs? SQL PL Profiler is very nice for
stored procs!

Well.. the SQL PL Profiler actually does nothing magic.
It simply joins SYSCAT.STATEMENTS (containing package name, section
numbers AND source code line numbers) to the event monitor output
(containing package name, section number). Combine that with a shredded
source code (one line per row): voila!
If you look at the Java source it's all there for the taking.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[UDBDBA]

Hi Serge:

Thanks for clarifying. It is a "funky" delete -- Maybe, when we put in
the DCR we will suggest an option called FUNKY_DELETE with a flag Y or
N :)

Vijay