Friends,

Three questions for you:

1. Are CREATE INDEX statements against DGTTs logged?

2. While running the SP recently, it died with a "transaction log
filled" (SQL0964C)
message while it was UPDATEing a NOT LOGGED DGTT. This is baffling and
seems
contrary to the behavior of NOT LOGGED DGTTs. Note that the UPDATE was
wrapped by a SELECT from the FINAL TABLE modifying table function.

3. Is there any point (performance-wise or other) to COMMITing
INSERTs
and UPDATEs against NOT LOGGED DGTTs? Perhaps (although it shouldn't
be necessary)
regular COMMITs will solve the problem of the log filling.


--Jeff

jefftyzzer wrote:
Yes CREATE INDEX is logged for NOT LOGGED DGTT

How did you manage to have an UPDATE to a DGTT in an SQL Table UDF?
Did you go through a CALL statement?
SQL UDF cannot directly access DGTT....


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

On Oct 10, 7:52 am, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
Serge:

Thanks as always for your help. Perhaps I misspoke when I used the
term "modifying table function," I thought that was the term for OLD
TABLE, NEW TABLE, etc., when wrapped around an INSERT, UPDATE, or
DELETE. There is no UDF involved; my actual statement looks like this:

<SNIP>
SELECT
DISTINCT
GRP_NUM NEW_GRP_NUM,
OLD_GRP_NUM
FROM
FINAL TABLE
(
UPDATE
SESSION.NM_BAG NMB1
INCLUDE
(OLD_GRP_NUM BIGINT)
SET
OLD_GRP_NUM = GRP_NUM,
<SNIP>

As to the COMMIT (it's within a loop), I've only added it after
getting the SQL0964C, and it helps in that I no longer get that error.
I'm still baffled, though, by why this error even appears, as the
table I'm UPDATEing is a NOT LOGGED DGTT. For the record, here's the
declaration of the DGTT:

DECLARE GLOBAL TEMPORARY TABLE
SESSION.NEAR_MATCH
(
GRP_NUM BIGINT,
EMPR_ID_A BIGINT,
EMPR_ID_B BIGINT
)
ON COMMIT PRESERVE ROWS
NOT LOGGED
ON ROLLBACK DELETE ROWS
WITH REPLACE;

Lastly, I'd be happy to share any and all SP source code.

Regards,

--Jeff

On Oct 10, 9:42 am, jefftyzzer <jefftyz... (AT) sbcglobal (DOT) net> wrote:
Sorry--I pasted the wrong DGTT declaration before. The right one is:

DECLARE GLOBAL TEMPORARY TABLE
SESSION.NM_BAG
(
GRP_NUM BIGINT,
EMPR_ID BIGINT
)
ON COMMIT PRESERVE ROWS
NOT LOGGED
ON ROLLBACK DELETE ROWS
WITH REPLACE;

--Jeff

Beats me. Does the UPDATE modify an index?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

On Oct 10, 12:36 pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
Yes--there are two indexes declared on that DGTT.

--Jeff