Please Help : commited delete
A user nightly unloads a table then deletes the unloaded rows, but the mass DELETE blows the logical logs with a long transaction error; he didn't want to enlarge logs and couldn't switch the table to RAW. Respondents offered several approaches: break the delete into small batches with frequent COMMITs (Art Kagel pointed to his dbdelete utility for exactly this), or, if the whole table is being cleared, drop/rename and recreate the table (saving view definitions, since dependent views are dropped - a query to list them was supplied), load into a raw table and rename it, truncate, or raise LTXHWM/LTXEHWM. The poster never reported which option he used, so no definitive resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, SOMEBODY please help me with this... I have a table which I want to unload every night at 12:00 and after unloading the table I want to delete all those rows which I have unloaded..... when i give delete to those rows i get long transaction error. I know that logical logs are full but dont want to go onto that solution of increasing logs size... SOMEBODY tell me how to unlog this activity ande how to make it commited. I have a standard table with constraints so cannot alter table to RAW. PLEASE HELP ME AND SAVE MY LIFE REQUEST EVERYONE TO GIVE STRAIGHT ANSWER AS I AM DUMB in informix. Amit.
Amit Assuming you are unloading and deleting the entire contents of the table, the quickest and simplest way is to drop the table then recreate it. This will take a minimum of time, a minimum of log space and have the added advantage of 'automatically' defragging the table each time. Disadvantage is that it is not reversible so you have to be absolutely sure you have all your data extracted correctly before starting. An alternative would be to write a small 4GL process to cursor around each record and delete it, however this would take longer and require log space, although you would never hit a long transaction problem. Keith -> -----Original Message----- -> From: AMIT DIXIT [mailto:amdixit_x@hssworld.com] -> Sent: Monday, November 15, 2004 8:41 AM -> To: ids@iiug.org -> Subject: Please Help : commited delete [3684] -> -> -> Hi, -> SOMEBODY please help me with this... -> I have a table which I want to unload every night at 12:00 -> and after unloading the table I want to delete all those rows -> which I have unloaded..... -> -> when i give delete to those rows i get long transaction error. -> -> I know that logical logs are full but dont want to go onto that -> solution of increasing logs size... -> SOMEBODY tell me how to unlog this activity ande how to make it -> commited. -> I have a standard table with constraints so cannot alter table to -> RAW. -> -> PLEASE HELP ME AND SAVE MY LIFE -> REQUEST EVERYONE TO GIVE STRAIGHT ANSWER AS I AM DUMB in informix. -> -> Amit. -> -> ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** **
Drop and re-create? Or am I missing something? Ian > Hi, > SOMEBODY please help me with this... > I have a table which I want to unload every night at 12:00 > and after unloading the table I want to delete all those rows > which I have unloaded..... > > when i give delete to those rows i get long transaction error. > > I know that logical logs are full but dont want to go onto that > solution of increasing logs size... > SOMEBODY tell me how to unlog this activity ande how to make it > commited. > I have a standard table with constraints so cannot alter table to > RAW. > > PLEASE HELP ME AND SAVE MY LIFE > REQUEST EVERYONE TO GIVE STRAIGHT ANSWER AS I AM DUMB in informix. > > Amit.
Hi Amit, you need to find a way to break up the deletes into smaller bits, and then commit after each bit. I send you some messages of this groups about this topics. Good luck Paola Mensaje citado por AMIT DIXIT <amdixit_x@hssworld.com>: > Hi, > SOMEBODY please help me with this... > I have a table which I want to unload every night at 12:00 > and after unloading the table I want to delete all those rows > which I have unloaded..... > > when i give delete to those rows i get long transaction error. > > I know that logical logs are full but dont want to go onto that > solution of increasing logs size... > SOMEBODY tell me how to unlog this activity ande how to make it > commited. > I have a standard table with constraints so cannot alter table to > RAW. > > PLEASE HELP ME AND SAVE MY LIFE > REQUEST EVERYONE TO GIVE STRAIGHT ANSWER AS I AM DUMB in informix. > > Amit. > > > >
Ok here are some suggestions and try them on your test box first and then deploy them in production. As some dbas have already suggested you, try to do the delete in smaller groups so that the long transaction would not take place. If you have the choice then turn off the logging in database level and do your deletes and then turn the logging back on to the database. Drop and recreate the table if there are no other database objects, such as views, synonyms etc referring to the said table. If you have the choice increase the LTXHWM and LTXEHWM parameters in the onconfig file, bounce the instance and do your deletes. Or perform which I call the double occupancy method (Ravis special), create a raw table, load all the data into it, do your deletes from the raw table, and then create all necessary constraints in the raw table and rename this newly created table with the actual table name you want to use. Hope these help. Ravi Thero. "Simmons, Keith" <keith.simmons@office2office.biz> wrote: Amit Assuming you are unloading and deleting the entire contents of the table, the quickest and simplest way is to drop the table then recreate it. This will take a minimum of time, a minimum of log space and have the added advantage of 'automatically' defragging the table each time. Disadvantage is that it is not reversible so you have to be absolutely sure you have all your data extracted correctly before starting. An alternative would be to write a small 4GL process to cursor around each record and delete it, however this would take longer and require log space, although you would never hit a long transaction problem. Keith -> -----Original Message----- -> From: AMIT DIXIT [mailto:amdixit_x@hssworld.com] -> Sent: Monday, November 15, 2004 8:41 AM -> To: ids@iiug.org -> Subject: Please Help : commited delete [3684] -> -> -> Hi, -> SOMEBODY please help me with this... -> I have a table which I want to unload every night at 12:00 -> and after unloading the table I want to delete all those rows -> which I have unloaded..... -> -> when i give delete to those rows i get long transaction error. -> -> I know that logical logs are full but dont want to go onto that -> solution of increasing logs size... -> SOMEBODY tell me how to unlog this activity ande how to make it -> commited. -> I have a standard table with constraints so cannot alter table to -> RAW. -> -> PLEASE HELP ME AND SAVE MY LIFE -> REQUEST EVERYONE TO GIVE STRAIGHT ANSWER AS I AM DUMB in informix. -> -> Amit. -> -> ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** ** --------------------------------- Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com
> Assuming you are unloading and deleting the entire contents of the
> table, the quickest and simplest way is to drop the table then recreate
> it. This will take a minimum of time, a minimum of log space and have
> the added advantage of 'automatically' defragging the table each time.
Make sure you save the schema of all views of the table as well , as the
views get dropped automatically when a underlying table is dropped.
To get all views :
select t.tabname from systables t
where t.tabid in ( select dtabid from sysdepend d, systables tt
where tt.tabid = d.btabid and tt.tabname = '<table>' ) ;
Replace <table> with the name of your table.
Regards
Tilman
Gave you the answer already last week. Get my dbdelete utility. This is just the kind of situation it was written to address. Art S. Kagel ----- Original Message ----- From: Amit Dixit <amdixit_x@hssworld.com> At: 11/15 4:14 > Hi, > SOMEBODY please help me with this... > I have a table which I want to unload every night at 12:00 > and after unloading the table I want to delete all those rows > which I have unloaded..... > > when i give delete to those rows i get long transaction error. > > I know that logical logs are full but dont want to go onto that > solution of increasing logs size... > SOMEBODY tell me how to unlog this activity ande how to make it > commited. > I have a standard table with constraints so cannot alter table to > RAW. > > PLEASE HELP ME AND SAVE MY LIFE > REQUEST EVERYONE TO GIVE STRAIGHT ANSWER AS I AM DUMB in informix. > > Amit.
Amit, After unloading the table, you can rename it to something else and then recreate the original table. Once you're sure that your unloading was successful, you could drop the renamed table. Vivek Chaudhary -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of AMIT DIXIT Sent: Monday, November 15, 2004 1:41 AM To: ids@iiug.org Subject: Please Help : commited delete [3684] Hi, SOMEBODY please help me with this... I have a table which I want to unload every night at 12:00 and after unloading the table I want to delete all those rows which I have unloaded..... when i give delete to those rows i get long transaction error. I know that logical logs are full but dont want to go onto that solution of increasing logs size... SOMEBODY tell me how to unlog this activity ande how to make it commited. I have a standard table with constraints so cannot alter table to RAW. PLEASE HELP ME AND SAVE MY LIFE REQUEST EVERYONE TO GIVE STRAIGHT ANSWER AS I AM DUMB in informix. Amit.
you can always truncate table - or drop / recreate. If you are not dealing with the entire contents, but still some signifcant portion of the contents, it may be faster to re-write the table. j. ----- Original Message ----- From: "AMIT DIXIT" <amdixit_x@hssworld.com> To: <ids@iiug.org> Sent: Monday, November 15, 2004 4:40 AM Subject: Please Help : commited delete [3684] > Hi, > SOMEBODY please help me with this... > I have a table which I want to unload every night at 12:00 > and after unloading the table I want to delete all those rows > which I have unloaded..... > > when i give delete to those rows i get long transaction error. > > I know that logical logs are full but dont want to go onto that > solution of increasing logs size... > SOMEBODY tell me how to unlog this activity ande how to make it > commited. > I have a standard table with constraints so cannot alter table to > RAW. > > PLEASE HELP ME AND SAVE MY LIFE > REQUEST EVERYONE TO GIVE STRAIGHT ANSWER AS I AM DUMB in informix. > > Amit. > > >