Log Full

shsandeep

Hi guys,

My developers are loading the DB2 tables from flat files through
DataStage.
DB2 V8.1.1 on AIX V5.2

They are committing every 500 rows.
Everything runs fine when they load empty tables, but when they load
tables that already contain data, they first delete the existing data and
then insert the new data into them.
This always fills up the log files and gives a log full error.

They do not commit while deleting the rows (no such option in DataStage
for them to do so).

Is there any solution to overcome this issue eg: some Procedure to delete
the data which can be called by DataStage?

Cheers,
San.

Apr 10 '06 #1

Serge Rielau

shsandeep wrote:

Is there any solution to overcome this issue eg: some Procedure to delete
the data which can be called by DataStage?

Hmm... I'll lift the secret at the IM Conference in The Hague
http://www-304.ibm.com/jct03001c/ser...age&c=a0009439

OK, ok....here is the procedure

CREATE PROCEDURE purgeInventory(IN dt DATE)
BEGIN
DECLARE SQLCODE INTEGER;
loop: LOOP
DELETE FROM
(SELECT 1 FROM Inventory
WHERE InvDate <= dt
FETCH FIRST 1000 ROWS ONLY) AS D;
IF SQLCODE = 100 THEN
LEAVE loop;
END IF;
COMMIT;
END LOOP loop;
END

CALL purgeInventory(‘2003-10-01’)

Note that you can also use
ALTER TABLE ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 10 '06 #2

shsandeep

Thanks Serge for disclosing the secret!
Just to understand it better, can you tell me what is the procedure
actually doing and what parameters should I put in place of Inventory and
Invdate?

Thanks.

Apr 10 '06 #3

Gregor Kovaè

Serge Rielau wrote:

shsandeep wrote:

Is there any solution to overcome this issue eg: some Procedure to delete
the data which can be called by DataStage? Hmm... I'll lift the secret at the IM Conference in The Hague

http://www-304.ibm.com/jct03001c/ser...age&c=a0009439

Hi!

Do you perhaps know if the presentations will be available online somewhere
to the public? If not is it possible to get yours ? :)

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

Apr 10 '06 #4

Serge Rielau

Gregor Kova� wrote:

Do you perhaps know if the presentations will be available online somewhere
to the public? If not is it possible to get yours ? :)

*snicker*
For our most valued European ISVs I just may give a private presentation.

I would guess that the presentations will be online.
Check whether you can find presentations from past years through that link.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 10 '06 #5

Serge Rielau

shsandeep wrote:

Thanks Serge for disclosing the secret!
Just to understand it better, can you tell me what is the procedure
actually doing and what parameters should I put in place of Inventory and
Invdate?

The DELETE statement deletes 1000 rows at a time. The WHERE clause is
simple just that.. A predicate determining what will be deleted.
If you want to delete the whole table you can remove it (but then alter
table may truly be a more suitable way to truncate the table).
The rest of the procedure is just a loop with commits until no more rows
qualify for the delete.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 10 '06 #6

krishan

if you want empty the table ie delete all records then you may
want to consider using import from /dev/null.
this shall have little impact on the logs and give you an empty
table with no records at all.

krishna j
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:49************@individual.net...

shsandeep wrote:

Is there any solution to overcome this issue eg: some Procedure to delete
the data which can be called by DataStage?

Hmm... I'll lift the secret at the IM Conference in The Hague
http://www-304.ibm.com/jct03001c/ser...age&c=a0009439

OK, ok....here is the procedure

CREATE PROCEDURE purgeInventory(IN dt DATE)
BEGIN
DECLARE SQLCODE INTEGER;
loop: LOOP
DELETE FROM
(SELECT 1 FROM Inventory
WHERE InvDate <= dt
FETCH FIRST 1000 ROWS ONLY) AS D;
IF SQLCODE = 100 THEN
LEAVE loop;
END IF;
COMMIT;
END LOOP loop;
END

CALL purgeInventory(‘2003-10-01’)

Note that you can also use
ALTER TABLE ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 10 '06 #7