hi, i have a table with create ddl using not logged initially,

CREATE TABLE "NJIPD "."IVPWGMR" ("IVDY01" DECIMAL(2,0) , "IVMO01"
DECIMAL(2,0) , "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY
( START WITH +1 , INCREMENT BY +1 , NO CACHE ) ) IN "USERSPACE1" NOT
LOGGED INITIALLY ;

right now i have to delete almost one million rows in the table, so i
found that i can use alter table activate not logged initially before
i execute delete, but i found that delete still use the transaction
log and cause log error full problem , why is that?

"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0405051212.634143be@posting.google.c om...
> hi, i have a table with create ddl using not logged initially,
>
> CREATE TABLE "NJIPD "."IVPWGMR" ("IVDY01" DECIMAL(2,0) , "IVMO01"
> DECIMAL(2,0) , "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY
> ( START WITH +1 , INCREMENT BY +1 , NO CACHE ) ) IN "USERSPACE1" NOT
> LOGGED INITIALLY ;
>
> right now i have to delete almost one million rows in the table, so i
> found that i can use alter table activate not logged initially before
> i execute delete, but i found that delete still use the transaction
> log and cause log error full problem , why is that?

"not logged initially" is turned off after the first commit. So it will
only help you if you're deleting all the rows via one DELETE statement and
then committing the changes.

--
Matt Emmerton

"Matt Emmerton" <memmerto@yahoo.com> wrote in message
news:e1cmc.410093$2oI1.211961@twister01.bloor.is.n et.cable.rogers.com...
>
> "xixi" <dai_xi@yahoo.com> wrote in message
> news:c0f33a17.0405051212.634143be@posting.google.c om...
> > hi, i have a table with create ddl using not logged initially,
> >
> > CREATE TABLE "NJIPD "."IVPWGMR" ("IVDY01" DECIMAL(2,0) , "IVMO01"
> > DECIMAL(2,0) , "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY
> > ( START WITH +1 , INCREMENT BY +1 , NO CACHE ) ) IN "USERSPACE1" NOT
> > LOGGED INITIALLY ;
> >
> > right now i have to delete almost one million rows in the table, so i
> > found that i can use alter table activate not logged initially before
> > i execute delete, but i found that delete still use the transaction
> > log and cause log error full problem , why is that?
>
> "not logged initially" is turned off after the first commit. So it will
> only help you if you're deleting all the rows via one DELETE statement and
> then committing the changes.
>
> --
> Matt Emmerton
>
To add to what Matt has said, you need turn off auto-commit for this to
work, and then explicitly commit after the delete. This can be done with the
+c parm on the db2 command line. For example.

db2 +c -tvf input.sql

In input.sql you will have 3 SQL statements:

alter table ... (to set not logged initially) ;
delete ... ;
commit;