Prblm while clearing large data
Moderators: chulett, rschirm, roy
Prblm while clearing large data
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.
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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
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.
" 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.
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
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,
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]
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
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
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
prblm while clearing large data
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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom