2nd try (renamed): NOT LOGGED DGTTs and the transacgtion log

[jefftyzzer]

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

[Serge Rielau]

jefftyzzer wrote:

1. Are CREATE INDEX statements against DGTTs logged?

Needed to do some research (aka ask people)...
Yes CREATE INDEX is logged for NOT LOGGED DGTT

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.

I do not believe that the table function is related here.
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....

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.

Nothing comes to mind

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[jefftyzzer]

On Oct 10, 7:52 am, Serge Rielau <[e-mail]:

jefftyzzer wrote:

1. Are CREATE INDEX statements against DGTTs logged?

Needed to do some research (aka ask people)...
Yes CREATE INDEX is logged for NOT LOGGED DGTT

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.

I do not believe that the table function is related here.
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....

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.

Nothing comes to mind

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

[jefftyzzer]

On Oct 10, 9:42 am, jefftyzzer <[e-mail]:

On Oct 10, 7:52 am, Serge Rielau <[e-mail]:

jefftyzzer wrote:

1. Are CREATE INDEX statements against DGTTs logged?

Needed to do some research (aka ask people)...
Yes CREATE INDEX is logged for NOT LOGGED DGTT

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.

I do not believe that the table function is related here.
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....

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.

Nothing comes to mind

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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- Hide quoted text -

- Show quoted text -

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

[Serge Rielau]

Beats me. Does the UPDATE modify an index?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[jefftyzzer]

On Oct 10, 12:36 pm, Serge Rielau <[e-mail]:

Beats me. Does the UPDATE modify an index?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Yes--there are two indexes declared on that DGTT.

--Jeff