Log Full

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shsandeep - 10 Apr 2006 03:15 GMT

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.

Reply to this Message

Serge Rielau - 10 Apr 2006 04:37 GMT

> 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/services/learning/ites.wss?pageType=page&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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

shsandeep - 10 Apr 2006 06:22 GMT

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.

Reply to this Message

Serge Rielau - 10 Apr 2006 10:17 GMT

> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Gregor Kovač - 10 Apr 2006 09:50 GMT

>> 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/services/learning/ites.wss?pageType=page&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

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

Reply to this Message

Serge Rielau - 10 Apr 2006 10:13 GMT

> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

krishan - 10 Apr 2006 14:31 GMT

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

>> Is there any solution to overcome this issue eg: some Procedure to delete
>> the data which can be called by DataStage?
[quoted text clipped - 26 lines]
> Cheers
> Serge

Reply to this Message