By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,287 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,287 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Oct 9 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
Oct 10 '07 #2

P: n/a
On Oct 10, 7:52 am, Serge Rielau <srie...@ca.ibm.comwrote:
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

Oct 10 '07 #3

P: n/a
On Oct 10, 9:42 am, jefftyzzer <jefftyz...@sbcglobal.netwrote:
On Oct 10, 7:52 am, Serge Rielau <srie...@ca.ibm.comwrote:


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

Oct 10 '07 #4

P: n/a
Beats me. Does the UPDATE modify an index?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Oct 10 '07 #5

P: n/a
On Oct 10, 12:36 pm, Serge Rielau <srie...@ca.ibm.comwrote:
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

Oct 11 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.