Pls. read this doc. this may give you some better ideas to bulk inserts.
My advice is to have a commit frequency even when using NOT LOGGED INITIALLY as I have seen this issue coming up when other transactions are run in parellel with INSERT with NOT LOGGED(till the time you commit your insert all the log files remain active even though other transactions commit .This situation can fill up all the available log files to generate TRANSACTION LOG Full )...Also increase the number of secondary log files as these are allocated on demand.
Did you try to run an explain on your query and see if it is using right access path? Also see if you can create a clustering index on the columns in your predicate or where clause.
If you are writing a query with GROUP BY ORDER BY etc. then you need to ensure that the tempspace has sufficient space to do the sorts as sort heap may spill over(explain should tell you about the steps in creating the result set).So ,if the access path cannot be fine tuned further by creating indexes etc, I suggest you free up enough space on your temp tablespace path or allocate a new temp tablespace(remember to drop the old one) on a path where it can grow as much as it needs to( a rule of thumb could be to sum the size of the tablespaces and indexspaces of the queried tables and allocate this space on temp ts path.
-----Original Message-----
From: Birendra Kumar Kabi Satpathy
[mailto:db2udbdba-ezmlmshield-x24874026.[Email address protected]
Sent: 24 December 2004 09:37
To: LazyDBA Discussion
Subject: RE: Spam: RE: Transaction Log Full
Hi Deepak
Yesterday It was working fine for loads of insert(1
million rows)
into a particular table after changing the logging parameters & making
the table "NOT LOGGED INITIALLY".
Then again we had to clear all the tables and fire the query for
2Million records.
Again I faced the same problem..
sometimes it gives "Transaction Log Full"
And sometime:SYS TEMPORARY SPACE IS FULL"
Can anybody please suggest some permanent solution for this type of DML
activities whatever huge may be the number of records??
Thanks in Advance
Birendra
-----Original Message-----
From: deepak
[mailto:db2udbdba-ezmlmshield-x77674413.[Email address protected]
Sent: Thursday, December 23, 2004 1:35 PM
To: LazyDBA Discussion
Subject: Spam: RE: Transaction Log Full
If this is a one off case and if you are not really bothered about
recovery try the following
alter table <tabname> activate not logged initially; execute your insert
statement commit;
Hope this helps...
Rgds
Deepak
-----Original Message-----
From: Birendra Kumar Kabi Satpathy
[mailto:db2udbdba-ezmlmshield-x18723865.[Email address protected]
Sent: Thu 12/23/2004 1:18 PM
To: LazyDBA Discussion
Cc:
Subject: Transaction Log Full
Hi All
I have 10,00,000 records to be inserted in a table(but
not
through LOAD but we need a simple INSERT statement)
I'm Getting a TRANSACTION LOG FULL after running for some
time..
MY CONFIGURATION SETTINGS ARE
=================================
LOGFILSIZE 20000(4kb)
PRIMARY LOG 5
SECONDARY LOG 5
APPHEAPSIZE 1024(4kb)
BUFFPAGE 250(4kb)
SORTHEAP 2000(4kb)
BUFFERPOOL 32K
TABLESPACE using this BUFFERPOOL
Plz respond if anybody has any permanent solution to this
problem,
whatever the number of records may be..
Thanks
Birendra
------------------------------------------------------------------------
---------------------
This message, including any attachments, contains confidential
information intended for a specific individual and purpose, and is
intended for the addressee only. Any unauthorized disclosure, use,
dissemination, copying, or distribution of this message or any of its
attachments or the information contained in this e-mail, or the taking
of any action based on it, is strictly prohibited. If you are not the
intended recipient, please notify the sender immediately by return
e-mail and delete this message.
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DISCLAIMER:
This message contains privileged and confidential information and is
intended only for the individual named.If you are not the intended
recipient you should not disseminate,distribute,store,print, copy or
deliver this message.Please notify the sender immediately by e-mail if
you have received this e-mail by mistake and delete this e-mail from
your system.E-mail transmission cannot be guaranteed to be secure or
error-free as information could be
intercepted,corrupted,lost,destroyed,arrive late or incomplete or
contain viruses.The sender therefore does not accept liability for any
errors or omissions in the contents of this message which arise as a
result of e-mail transmission. If verification is required please
request a hard-copy version.
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------------------------------
This message, including any attachments, contains confidential information intended for a specific individual and purpose, and is intended for the addressee only. Any unauthorized disclosure, use, dissemination, copying, or distribution of this message or any of its attachments or the information contained in this e-mail, or the taking of any action based on it, is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail and delete this message.
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page