One small problem, NOT LOGGED INITIALLY

[Tim.D]

Folks,

I did run into a small problem afterall. Running my first SP I am
seeing a number of errors in the windows 2k server event viewer.
Following is a paste:
--
2004-03-03-15.06.49.967001 Instance:DB2 Node:000
PID:2444(db2sys cs.exe) TID:1696 Appid:*LOCAL.DB 2.0115C3193403
data management sqldEndNoLogLis t Probe:1 Database:dbname

ADM5530E The COMMIT processing of table "tbname" that
used NOT LOGGED INITIALLY has been initiated. It is recommended that
you take
a backup of this table's table space(s).
--
This appears to be happening after I am performing an explicit commit
from inside the SP. I have two tables that apply the activate not
logged initially param to at the start of the proc [to avoid logging
issue] and after each explicit commit. This is performed after a
commit counter reaches a certain level from within a 'while' loop.

Windows is logging it as an error [red cross] however it really doesnt
mean a thing to me as that is what I wanted to happen. No errors in
the db2diag.log at all. Is there a way to configure windows or db2 to
supress this ? It will be more than annoying after a while as it will
fill the log and wipe out the rest.

Many thanks for any assistance you mightbe able to offer.

Tim

[Matt Emmerton]

"Tim.D" <td******@hotma il.com> wrote in message
news:11******** *************** ***@posting.goo gle.com...

Folks,

I did run into a small problem afterall. Running my first SP I am
seeing a number of errors in the windows 2k server event viewer.
Following is a paste:
--
2004-03-03-15.06.49.967001 Instance:DB2 Node:000
PID:2444(db2sys cs.exe) TID:1696 Appid:*LOCAL.DB 2.0115C3193403
data management sqldEndNoLogLis t Probe:1 Database:dbname

ADM5530E The COMMIT processing of table "tbname" that
used NOT LOGGED INITIALLY has been initiated. It is recommended that
you take a backup of this table's table space(s).
From the DB2 InfoCenter:
http://publib.boulder.ibm.com/infoce...n/r0000888.htm

<start snip>
Any changes made to the table by an INSERT, DELETE, UPDATE, CREATE INDEX,
DROP INDEX, or ALTER TABLE in the same unit of work after the table is
altered by this statement are not logged. Any changes made to the system
catalog by the ALTER statement in which the NOT LOGGED INITIALLY attribute
is activated are logged. Any subsequent changes made in the same unit of
work to the system catalog information are logged
<end snip>

While catalog changes are recorded in the database log, none of the data
changes are. This means that if you have a database failure before all the
changed pages are written to disk (which happens in the background via the
page cleaners), then you will lose all of the changes you made to your data
while in NOT LOGGED INITIALLY mode..

If you do a rollforward recovery (or restore from an older backup and do a
rollforward recovery), your database catalogs will be up-to-date, but none
of the data operations you did while in NOT LOGGED INITIALLY mode will be
recovered.

This is why it is important to do a tablespace (or database) backup after
doing operations in NOT LOGGED INITIALLY mode, as all of the changed pages
will be flushed to disk and then backed up, preventing the possibilty of
data loss.
This appears to be happening after I am performing an explicit commit
from inside the SP. I have two tables that apply the activate not
logged initially param to at the start of the proc [to avoid logging
issue] and after each explicit commit. This is performed after a
commit counter reaches a certain level from within a 'while' loop.

Windows is logging it as an error [red cross] however it really doesnt
mean a thing to me as that is what I wanted to happen. No errors in
the db2diag.log at all. Is there a way to configure windows or db2 to
supress this ? It will be more than annoying after a while as it will
fill the log and wipe out the rest.

I would suggest that you rethink why you are using NOT LOGGED INITIALLY and
whether it's worth the risk.

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab

[Serge Rielau]

Matt,

Seems Tim is OK with NLI and he wants DB2 to stop bugging him about it.
Kind of like your seat-belt warning light flashing when you're parked ;-)

Tim which diag level are you running with?

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

[Tim.D]

I had done some significant reading [both in this group and via
manuals] on the matter before I went this way. See my other post re:
cpu util for the SP logic, it completes a fairly significant number of
small inserts to both a tmp table and final result. No concurrency is
necessary, no online uses other than myself, can be repeated as many
times as necessary which is why I dont really worry about any further
changes to the tables after activating. My first runs of the SP
resulted in the log file getting hammered as the procedure was
running. The table space containing the tmp and data tables were not.
I increased the group commit but it didnt seem to help, so here we are
.... heh

I'd really like to get rid of the error though, as I do not see it as
an error so to speak. If it is paramater activated, and can only be
done so intentionally, why log it as an error and not a warning ? and
be able to suppress it if desired ?

In the procedure I maintain a v_commit count that I activate every
2000 records, wwould that be better being set higher or lower perhaps
?

Thanks Matt,

Tim

[Tim.D]

Hi Serge,

DIAGLEVEL = 3
NOTIFYLEVEL = 3

Thanks for your help on this.

Tim

[PM \(pm3iinc-nospam\)]

For event logs on windows, that would be the NOTIFYLEVEL ant not
theDIAGLEVEL.

http://publib.boulder.ibm.com/infoce...76%65%6c%22%20

PM

"Serge Rielau" <sr*****@ca.e ye-be-em.com> a écrit dans le message de
news:c2******** **@hanover.toro lab.ibm.com...

Matt,

Seems Tim is OK with NLI and he wants DB2 to stop bugging him about it.
Kind of like your seat-belt warning light flashing when you're parked ;-)

Tim which diag level are you running with?

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

[Matt Emmerton]

"Tim.D" <td******@hotma il.com> wrote in message
news:11******** *************** ***@posting.goo gle.com...

Hi Serge,

DIAGLEVEL = 3
NOTIFYLEVEL = 3

Set these to 1 (or 2?) and you won't see the errors anymore.

--
Matt Emmerton

[Tim.D]

Thanks PM will give it a try.