Prblm while clearing large data

rajiivnb · Post by **rajiivnb** » Mon Apr 04, 2005 10:25 pm

Hi Group,

We have a table called STORE which has some 3 lacs rows.
In the target ODBC we set the update option as, 'Clear table then Insert rows'.

But if we run the job, after 1,88,183 records it throws warnings as new record cannot be inserted. one of my friend said, datastage is not deleting large volume tables completely. I am not accepting that. can any one suggest, whtz the problem with this.

We droped the table and created it again, then it was running fine. Yesterday it was running fine. Suddenly this error came.

Thnx in advance.

dhiraj · Post by **dhiraj** » Mon Apr 04, 2005 10:50 pm

looks to me that datastage is deleting records from the database and not truncating the table,hence your transaction logs are getting full.

Try trucating the table before you run the job.

Regards

Dhiraj

dhiraj · Post by **dhiraj** » Mon Apr 04, 2005 10:55 pm

and yes Could also be an issue with free disk space on your database.

Regards
Dhiraj

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Apr 05, 2005 6:36 am

Set a transaction and array size

kollurianu · Post by **kollurianu** » Tue Apr 05, 2005 8:16 am

Hi ,

" looks to me that datastage is deleting records from the database and not truncating the table,hence your transaction logs are getting full.

Try trucating the table before you run the job. "

deleting the records does not mean truncating the tabel , if what are transaction logs where are they located and how to check them.

What is the main difference between truncating and deleting the table?

Thank you all ,

Any answer appreciated.

ketfos · Post by **ketfos** » Tue Apr 05, 2005 10:08 am

Hi,
Did you try the delete command outside datastage.

i.e delete from <tablename>.

What was the result?

Ketfos

kollurianu · Post by **kollurianu** » Tue Apr 05, 2005 10:40 am

yes , when i delete commit needs to be issued , but when i do the truncate the table i need not issue the commit after it , still the transaction is done .

but i want to know what are transaction logs , why are those getting full when delete command is issued.

Thank you all.

Thank you,

Code: Select all

[size=9][/size]

ketfos · Post by **ketfos** » Tue Apr 05, 2005 11:12 am

Hi,
Following is how Delete and Truncate command behave -

Delete
At the simplest level, delete scans the table and removes any rows that match the given criteria in the (optional) where clause. It generates rollback information so that the deletions can be undone should it be necessary. Index entries for the deleted rows are removed from the indexes. You must commit to make the deletions permanent.

When deleting rows from a table, extents are not deallocated, so if there were 50 extents in the table before the deletion, there will still be 50 after the deletion. In addition the High Water Mark is not moved down, so it remains where it was before the deletion began. This means that any subsequent full table scans may (still) take a long time to complete - because a full table scan always scans up to the HWM. So, by example, if a select count(*) from very_large_table; took 15 minutes to complete before all the rows were deleted, you will find that it still takes about 15 mins after the deletion - because Oracle is still scanning every single block up to the HWM - even though some (or all) of the blocks may have no data in them.

Truncate
Truncate, on the other hand, simply moves the high water mark on the table right down to the beginning. It does this very quickly, and does not need to be committed. Once you truncate a table, there is no going back. Indexes are also truncated. There is no facility to be able to specify which rows to 'delete' as you can with the where clause on the delete command.

When a table is truncated, all its extents are deallocated leaving only the extents specified when the table was originally created. So if the table was originally created with minextents 3, there will be 3 extents remaining when the tables is truncated.

--------------------------------------------------------------------------------

Ketfos

kollurianu · Post by **kollurianu** » Tue Apr 05, 2005 11:35 am

Thank you very much for ur answer Ketfos ,

but can you tell me what are transactions logs , where are those located.

thank you ,

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Apr 05, 2005 1:09 pm

The transaction logs are present in the related RDBMS.

kollurianu · Post by **kollurianu** » Tue Apr 05, 2005 1:58 pm

what are transaction logs , why are they full?

any answers greatly appreciated.

Thank you Sainath for ur answer.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Apr 05, 2005 2:00 pm

They are not transaction logs. They are 'redo logs' used to store the 'as of before' records when a DML is performed. They are cleared as a commit / rollback is issued.

kollurianu · Post by **kollurianu** » Tue Apr 05, 2005 2:54 pm

Thank you very much Sainath for ur answer.

rajiivnb · Post by **rajiivnb** » Wed Apr 06, 2005 4:10 am

Hi group,

Thnx for all ur answers. but my situation is am using a ODBC stage where i have to clear the table which has more than 3 lacs records and freshly need to insert the new records. totaly i have 100 tables with a big sequence. am running only the big sequence. so i checked the 'clear all and then insert' option in the ODBC. How can i use truncate here as prevoiusly discussed ?

Thnx

Rajiiv

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Apr 06, 2005 4:53 am

Have a pre-job or script to do it for you.

DSXchange

Prblm while clearing large data

Prblm while clearing large data

prblm while clearing large data