NOT LOGGED INITIALLY problem 
Author Message
 NOT LOGGED INITIALLY problem

Hi,

I create several tables with NOT LOGGED INITIALLY.

Then, I insert tonnes of records in a real time environment and commit
after each record. Unfortunately, these records were also filling up my
log files.

So, I now have the command "ALTER TABLE <table> ACTIVATE NOT LOGGED
INITIALLY;" infront of the previous insert SQL in an attempt to execute
the two commands as a batch CLI sequence.

I have notice 2 things:

1) The tables are empty upon program completion.

2) The program is taking 3 times longer to execute.

Any insight would be appreciated.  This is a really important problem as
the transaction logs must stop from growing and a speedy response is
appreciated.

Sent via Deja.com http://www.***.com/
Before you buy.



Fri, 11 Oct 2002 03:00:00 GMT
 NOT LOGGED INITIALLY problem

Hi,
NOT LOGGED INITIALLY is not made for this kind of work.
It should be used only when you want to populate the table with a
"large" set of rows, not one at a time.

Why your table is empty after you rcommits I don't know...
Can you post some example script?

Cheers
Serge



Fri, 11 Oct 2002 03:00:00 GMT
 NOT LOGGED INITIALLY problem


Quote:
> Hi,
> NOT LOGGED INITIALLY is not made for this kind of work.
> It should be used only when you want to populate the table with a
> "large" set of rows, not one at a time.

> Why your table is empty after you rcommits I don't know...
> Can you post some example script?

> Cheers
> Serge

Thanks for responding.  If the nature of my application does not permit
NOT LOGGED INITIALLY, then I am content with not "solving the empty
table problem".

One quick question though, could someone explain how the circular
logging works?  Say if I insert 1 000 000 records in one connection and
my log space is small, will it simply wrap and write over the the
beginning records when the log is full?

Moreover, if I chose a small log space with circular logging, can I ever
get a "log full" error and would a small log drastically affect program
speed?  Conversley, would a large log (say 4 GB) slow down the
application at all?

Sent via Deja.com http://www.deja.com/
Before you buy.



Fri, 11 Oct 2002 03:00:00 GMT
 NOT LOGGED INITIALLY problem
Not my area of expertise at all... sorry

Cheers
Serge



Fri, 11 Oct 2002 03:00:00 GMT
 NOT LOGGED INITIALLY problem
All of this is explained in the books, so I would say "RTFM". Use the
search facility to find the topic you are looking for.

In the admin guide, chapter 28 under the "Logging and recovery"
section, I see the following the following blurb.

The use of primary and secondary log files as well as the action taken
when a log file becomes full are dependent on the type of logging that
is being performed:

Circular logging
A primary log file can be reused when the changes recorded in it have
been committed. If the log file size is small and applications have
processed a large number of changes to the database without committing
the changes, a primary log file can quickly become full. If all
primary log files become full, the database manager will allocate
secondary log files to hold the new log records.

Log Retention logging
When a primary log file is full, the log is archived and a new primary
log file is allocated.

And further on:

If logretain is set to "No" and userexit is set to "No", logs are not
retained. In this situation, the database manager deletes all log
files in the logpath directory (including online archive log files),
allocates new active log files, and reverts to circular logging.

Regards,
--
Haider



Fri, 11 Oct 2002 03:00:00 GMT
 NOT LOGGED INITIALLY problem
You can still get a log full error with circular logging.  When all logs are
full and a UOW is still active on the first log, then secondary log files
are created.  If those fill up and there are still active UOW's on the first
log file, then you will get this error.
Quote:



> > Hi,
> > NOT LOGGED INITIALLY is not made for this kind of work.
> > It should be used only when you want to populate the table with a
> > "large" set of rows, not one at a time.

> > Why your table is empty after you rcommits I don't know...
> > Can you post some example script?

> > Cheers
> > Serge

> Thanks for responding.  If the nature of my application does not permit
> NOT LOGGED INITIALLY, then I am content with not "solving the empty
> table problem".

> One quick question though, could someone explain how the circular
> logging works?  Say if I insert 1 000 000 records in one connection and
> my log space is small, will it simply wrap and write over the the
> beginning records when the log is full?

> Moreover, if I chose a small log space with circular logging, can I ever
> get a "log full" error and would a small log drastically affect program
> speed?  Conversley, would a large log (say 4 GB) slow down the
> application at all?

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Fri, 11 Oct 2002 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. NOT LOGGED INITIALLY & log is full

2. About db2batch on not logged initially table

3. CREATE TABLE ...NOT LOGGED INITIALLY ???

4. Questions about "not logged initially"

5. not logged initially

6. Initially not logged table and FK′s

7. FOREIGN KEY: difference between NOT DEFERRABLE and INITIALLY

8. FOREIGN KEY: difference between NOT DEFERRABLE and INITIALLY IMMEDIATE

9. NOT LOGGED INITIALLY ... but ... Transaction Log is full

10. NOT LOGGED INITIALLY .... UPDATE ....

11. problem SQL6.0 log dump not truncating from db

12. Problem with SQLMail while not logged on


 
Powered by phpBB® Forum Software