looking for logical log data reconstruction utilit

Hi, Does anyone have a utility that they are willing to donate that can scan an IDS (10.0.FC6) logical log and reconstruct the original data for rows that were updated? A user used an UPDATE stmt to update 4 char and 2 SmallInt columns and forgot to include a WHERE clause, updating those 6 columns in 2700 rows to the wrong value. All of the transactions are in the same logical log which is on-line in the logs dbspaces. I need something that can scan this logical log and reconstruct the "before" data for these 2700 rows and write out the rowid, column name, and before values - so that I can create sql to update the data back to its original state. Thanks, Jim

Jim Cramer wrote: > Hi, > > Jim, If the original data is on the latest archive you can use archecker to extract the data from the archive and restore it to any table, either the original or a table created just for the recovery. Much easier than trying to parse the logical log records. You cannot recover completely this way if anyone else modified the same records after the archive however. Art S. Kagel > Does anyone have a utility that they are willing to donate > that can scan an IDS (10.0.FC6) logical log and reconstruct > the original data for rows that were updated? > > A user used an UPDATE stmt to update 4 char and 2 SmallInt > columns and forgot to include a WHERE clause, updating those 6 columns > in 2700 rows to the wrong value. > > All of the transactions are in the same logical log which > is on-line in the logs dbspaces. > > I need something that can scan this logical log and reconstruct > the "before" data for these 2700 rows and write out the rowid, > column name, and before values - so that I can create sql to > update the data back to its original state. > > Thanks, > > Jim > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Version 10 has Point-In-Time-Table-Level-Restore. Look in the past chat with the labs for John Miller III presentation that has step by step instructions. Quick and easy, if you have a backup. "Jim Cramer" <jim-cramer@uiowa .edu> To Sent by: ids@iiug.org ids-bounces@iiug. cc org Subject looking for logical log data 12/05/2007 10:34 reconstruction ut.... [10598] AM Please respond to ids@iiug.org Hi, Does anyone have a utility that they are willing to donate that can scan an IDS (10.0.FC6) logical log and reconstruct the original data for rows that were updated? A user used an UPDATE stmt to update 4 char and 2 SmallInt columns and forgot to include a WHERE clause, updating those 6 columns in 2700 rows to the wrong value. All of the transactions are in the same logical log which is on-line in the logs dbspaces. I need something that can scan this logical log and reconstruct the "before" data for these 2700 rows and write out the rowid, column name, and before values - so that I can create sql to update the data back to its original state. Thanks, Jim ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Why not do a table level restore prior to the update ... that can either go to a file ... or a table ... then you can just fix it from there ... "Jim Cramer" <jim-cramer@uiowa.edu> Sent by: ids-bounces@iiug.org 12/05/2007 11:36 AM Please respond to ids@iiug.org To ids@iiug.org cc Subject looking for logical log data reconstruction ut.... [10598] Hi, Does anyone have a utility that they are willing to donate that can scan an IDS (10.0.FC6) logical log and reconstruct the original data for rows that were updated? A user used an UPDATE stmt to update 4 char and 2 SmallInt columns and forgot to include a WHERE clause, updating those 6 columns in 2700 rows to the wrong value. All of the transactions are in the same logical log which is on-line in the logs dbspaces. I need something that can scan this logical log and reconstruct the "before" data for these 2700 rows and write out the rowid, column name, and before values - so that I can create sql to update the data back to its original state. Thanks, Jim ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

I was going to do just that using archecker, normally a simple, fast matter, ... and then discovered that a problem with my onbar logical log backups is preventing me from doing it. So, I am looking to mine out the changes and create a script of UPDATE statements to restore the affected data. Jim > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Peter_Logan@spartanstores.com > Sent: Wednesday, December 05, 2007 10:44 AM > To: ids@iiug.org > Subject: Re: looking for logical log data reconstructio.... [10602] > > Why not do a table level restore prior to the update ... that can > either > go to a file ... or a table ... then you can just fix it from there ... > > "Jim Cramer" <jim-cramer@uiowa.edu> > Sent by: ids-bounces@iiug.org > 12/05/2007 11:36 AM > Please respond to > ids@iiug.org > > To > ids@iiug.org > cc > > Subject > looking for logical log data reconstruction ut.... [10598] > > Hi, > > Does anyone have a utility that they are willing to donate > that can scan an IDS (10.0.FC6) logical log and reconstruct > the original data for rows that were updated? > > A user used an UPDATE stmt to update 4 char and 2 SmallInt > columns and forgot to include a WHERE clause, updating those 6 columns > in 2700 rows to the wrong value. > > All of the transactions are in the same logical log which > is on-line in the logs dbspaces. > > I need something that can scan this logical log and reconstruct > the "before" data for these 2700 rows and write out the rowid, > column name, and before values - so that I can create sql to > update the data back to its original state. > > Thanks, > > Jim > > > *********************************************************************** > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

Art, As per my previous reply, just now, I would have done that but my logical logs cannot be processed and restored from by archecker or onbar restore. Therefore I was wondering if anyone had a tool for reconstruction data prior to UPDATEs from the logical log. Regards, Jim > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art S. Kagel (Oninit LLC) > Sent: Wednesday, December 05, 2007 10:41 AM > To: ids@iiug.org > Subject: Re: looking for logical log data reconstructio.... [10600] > > Jim Cramer wrote: > > Hi, > > > > > Jim, > > If the original data is on the latest archive you can use archecker to > extract the data from the archive and restore it to any table, either > the original or a table created just for the recovery. Much easier than > trying to parse the logical log records. > > You cannot recover completely this way if anyone else modified the same > records after the archive however. > > Art S. Kagel > > > Does anyone have a utility that they are willing to donate > > that can scan an IDS (10.0.FC6) logical log and reconstruct > > the original data for rows that were updated? > > > > A user used an UPDATE stmt to update 4 char and 2 SmallInt > > columns and forgot to include a WHERE clause, updating those 6 > columns > > in 2700 rows to the wrong value. > > > > All of the transactions are in the same logical log which > > is on-line in the logs dbspaces. > > > > I need something that can scan this logical log and reconstruct > > the "before" data for these 2700 rows and write out the rowid, > > column name, and before values - so that I can create sql to > > update the data back to its original state. > > > > Thanks, > > > > Jim > > > > > > > *********************************************************************** > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

Jim Cramer wrote: You're in virgin territory I'm afraid Jim. This is definitely doable, but I don't know anyone outside IBM/Informix who's attempted it. It's on my list of things I'd like to tackle. So, if you want to contract someone to do it for you, let me know. It was right behind "Recover a server's data after a catastrophic crash", but I did that one recently, so I'm up for it. ;-) Art S. Kagel > I was going to do just that using archecker, normally > a simple, fast matter, ... and then discovered > that a problem with my onbar logical log backups > is preventing me from doing it. > > So, I am looking to mine out the changes and create > a script of UPDATE statements to restore the affected > data. > > Jim > > >> -----Original Message----- >> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of >> Peter_Logan@spartanstores.com >> Sent: Wednesday, December 05, 2007 10:44 AM >> To: ids@iiug.org >> Subject: Re: looking for logical log data reconstructio.... [10602] >> >> Why not do a table level restore prior to the update ... that can >> either >> go to a file ... or a table ... then you can just fix it from there ... >> >> "Jim Cramer" <jim-cramer@uiowa.edu> >> Sent by: ids-bounces@iiug.org >> 12/05/2007 11:36 AM >> Please respond to >> ids@iiug.org >> >> To >> ids@iiug.org >> cc >> >> Subject >> looking for logical log data reconstruction ut.... [10598] >> >> Hi, >> >> Does anyone have a utility that they are willing to donate >> that can scan an IDS (10.0.FC6) logical log and reconstruct >> the original data for rows that were updated? >> >> A user used an UPDATE stmt to update 4 char and 2 SmallInt >> columns and forgot to include a WHERE clause, updating those 6 columns >> in 2700 rows to the wrong value. >> >> All of the transactions are in the same logical log which >> is on-line in the logs dbspaces. >> >> I need something that can scan this logical log and reconstruct >> the "before" data for these 2700 rows and write out the rowid, >> column name, and before values - so that I can create sql to >> update the data back to its original state. >> >> Thanks, >> >> Jim >> >> >> *********************************************************************** >> ******** >> >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >> *********************************************************************** >> ******** >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Jim Cramer wrote: > Art, > See my other reply. But I'd also like to note that you MAY want to take a new clean archive, even with the stomped on rows, just in case Murphy was monitoring the Forum. He can be a bit of a scamp at times like this! Art S. Kagel > As per my previous reply, just now, I would > have done that but my logical logs cannot > be processed and restored from by archecker > or onbar restore. > > Therefore I was wondering if anyone had a tool > for reconstruction data prior to UPDATEs from > the logical log. > > Regards, > > Jim > > >> -----Original Message----- >> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of >> Art S. Kagel (Oninit LLC) >> Sent: Wednesday, December 05, 2007 10:41 AM >> To: ids@iiug.org >> Subject: Re: looking for logical log data reconstructio.... [10600] >> >> Jim Cramer wrote: >> >>> Hi, >>> >>> >>> >> Jim, >> >> If the original data is on the latest archive you can use archecker to >> extract the data from the archive and restore it to any table, either >> the original or a table created just for the recovery. Much easier than >> trying to parse the logical log records. >> >> You cannot recover completely this way if anyone else modified the same >> records after the archive however. >> >> Art S. Kagel >> >> >>> Does anyone have a utility that they are willing to donate >>> that can scan an IDS (10.0.FC6) logical log and reconstruct >>> the original data for rows that were updated? >>> >>> A user used an UPDATE stmt to update 4 char and 2 SmallInt >>> columns and forgot to include a WHERE clause, updating those 6 >>> >> columns >> >>> in 2700 rows to the wrong value. >>> >>> All of the transactions are in the same logical log which >>> is on-line in the logs dbspaces. >>> >>> I need something that can scan this logical log and reconstruct >>> the "before" data for these 2700 rows and write out the rowid, >>> column name, and before values - so that I can create sql to >>> update the data back to its original state. >>> >>> Thanks, >>> >>> Jim >>> >>> >>> >>> >> *********************************************************************** >> ******** >> >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >>> >>> >> *********************************************************************** >> ******** >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Jim: Why can you not process the logical logs with archecker?? Archecker Point-In-Time Table Level Restore will restore the data from both the physical restore followed by restoring the logical logs. You should be able to restore the data up to the exact transaction that started the update John = "Jim Cramer" = <jim-cramer@uiowa = .edu> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect RE: looking for logical log data= 12/05/2007 09:01 reconstructio.... [10604] = AM = = = Please respond to = ids@iiug.org = = = Art, As per my previous reply, just now, I would have done that but my logical logs cannot be processed and restored from by archecker or onbar restore. Therefore I was wondering if anyone had a tool for reconstruction data prior to UPDATEs from the logical log. Regards, Jim > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of= > Art S. Kagel (Oninit LLC) > Sent: Wednesday, December 05, 2007 10:41 AM > To: ids@iiug.org > Subject: Re: looking for logical log data reconstructio.... [10600] > > Jim Cramer wrote: > > Hi, > > > > > Jim, > > If the original data is on the latest archive you can use archecker t= o > extract the data from the archive and restore it to any table, either= > the original or a table created just for the recovery. Much easier th= an > trying to parse the logical log records. > > You cannot recover completely this way if anyone else modified the sa= me > records after the archive however. > > Art S. Kagel > > > Does anyone have a utility that they are willing to donate > > that can scan an IDS (10.0.FC6) logical log and reconstruct > > the original data for rows that were updated? > > > > A user used an UPDATE stmt to update 4 char and 2 SmallInt > > columns and forgot to include a WHERE clause, updating those 6 > columns > > in 2700 rows to the wrong value. > > > > All of the transactions are in the same logical log which > > is on-line in the logs dbspaces. > > > > I need something that can scan this logical log and reconstruct > > the "before" data for these 2700 rows and write out the rowid, > > column name, and before values - so that I can create sql to > > update the data back to its original state. > > > > Thanks, > > > > Jim > > > > > > > *********************************************************************= ** > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > > > > *********************************************************************= ** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Hi John, Thanks for your reply. See my previous responses to the same suggestion. To be more specific and to confess my guilt, here is exactly why I cannot use archecker's point in time resore, and why archecker will do only the physical restore and nothing from the logical restore phase. After installing IDS 10 on an instance, I did a dbexport from the 7.31 instance that was being replaced by 10.x. I then set LTAPEDEV to /dev/null in onconfig for the new server so that the dbimport would not generate a bunch of transactions. Looking at the start of the onbar bar_act.log file I see: "(-43183) WARNING: Logical Logs will not be backed up / salvaged because LTAPEDEV value is /dev/null ." logged when I did my level-0 after the dbimport into IDS 10. Apparently I forgot to change this config back to something other than /dev/null before the level-0 and so the logical log backup that went with the "onbar -b -w -L 0" went into the bit bucket. At some point before the next backup, I fixed this and all of the level-1 and level-2 backups since have been backing up the logical logs. But, without that initial log backup, I am told by Informix tech support that archecker cannot proceed with the logical restore. It seems that I am hosed... If you feel that this is not true and that there is actually a way to do it, please let me know as soon as you can. Thanks again, Jim Regards, Jim > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > John Miller iii > Sent: Wednesday, December 05, 2007 11:38 AM > To: ids@iiug.org > Subject: RE: looking for logical log data reconstructio.... [10607] > > Jim: > > Why can you not process the logical logs with archecker?? > > Archecker Point-In-Time Table Level Restore > will restore the data from both the physical restore > followed by restoring the logical logs. > > You should be able to restore the data up to the exact > transaction that started the update > > John > > = > > "Jim Cramer" = > > <jim-cramer@uiowa = > > ..edu> = > To > > Sent by: ids@iiug.org = > > ids-bounces@iiug. = > cc > > org = > > Subj= > ect > > RE: looking for logical log data= > > 12/05/2007 09:01 reconstructio.... [10604] = > > AM = > > = > > = > > Please respond to = > > ids@iiug.org = > > = > > = > > Art, > > As per my previous reply, just now, I would > have done that but my logical logs cannot > be processed and restored from by archecker > or onbar restore. > > Therefore I was wondering if anyone had a tool > for reconstruction data prior to UPDATEs from > the logical log. > > Regards, > > Jim > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > Of= > > > Art S. Kagel (Oninit LLC) > > Sent: Wednesday, December 05, 2007 10:41 AM > > To: ids@iiug.org > > Subject: Re: looking for logical log data reconstructio.... [10600] > > > > Jim Cramer wrote: > > > Hi, > > > > > > > > Jim, > > > > If the original data is on the latest archive you can use archecker > t= > o > > extract the data from the archive and restore it to any table, > either= > > > the original or a table created just for the recovery. Much easier > th= > an > > trying to parse the logical log records. > > > > You cannot recover completely this way if anyone else modified the > sa= > me > > records after the archive however. > > > > Art S. Kagel > > > > > Does anyone have a utility that they are willing to donate > > > that can scan an IDS (10.0.FC6) logical log and reconstruct > > > the original data for rows that were updated? > > > > > > A user used an UPDATE stmt to update 4 char and 2 SmallInt > > > columns and forgot to include a WHERE clause, updating those 6 > > columns > > > in 2700 rows to the wrong value. > > > > > > All of the transactions are in the same logical log which > > > is on-line in the logs dbspaces. > > > > > > I need something that can scan this logical log and reconstruct > > > the "before" data for these 2700 rows and write out the rowid, > > > column name, and before values - so that I can create sql to > > > update the data back to its original state. > > > > > > Thanks, > > > > > > Jim > > > > > > > > > > > > *********************************************************************= > ** > > ******** > > > Forum Note: Use "Reply" to post a response in the discussion > forum.= > > > > > > > > > > > > > > *********************************************************************= > ** > > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > *********************************************************************** > = > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > = > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

Yes, Murphy be be around and "me bad"... I will definitely do a new archive, this time correctly. See my reply to John Miller... Regards, Jim > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art S. Kagel (Oninit LLC) > Sent: Wednesday, December 05, 2007 11:10 AM > To: ids@iiug.org > Subject: Re: looking for logical log data reconstructio.... [10606] > > Jim Cramer wrote: > > Art, > > > > See my other reply. But I'd also like to note that you MAY want to take > a new clean archive, even with the stomped on rows, just in case Murphy > was monitoring the Forum. He can be a bit of a scamp at times like > this! > > Art S. Kagel > > > As per my previous reply, just now, I would > > have done that but my logical logs cannot > > be processed and restored from by archecker > > or onbar restore. > > > > Therefore I was wondering if anyone had a tool > > for reconstruction data prior to UPDATEs from > > the logical log. > > > > Regards, > > > > Jim > > > > > >> -----Original Message----- > >> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > Of > >> Art S. Kagel (Oninit LLC) > >> Sent: Wednesday, December 05, 2007 10:41 AM > >> To: ids@iiug.org > >> Subject: Re: looking for logical log data reconstructio.... [10600] > >> > >> Jim Cramer wrote: > >> > >>> Hi, > >>> > >>> > >>> > >> Jim, > >> > >> If the original data is on the latest archive you can use archecker > to > >> extract the data from the archive and restore it to any table, > either > >> the original or a table created just for the recovery. Much easier > than > >> trying to parse the logical log records. > >> > >> You cannot recover completely this way if anyone else modified the > same > >> records after the archive however. > >> > >> Art S. Kagel > >> > >> > >>> Does anyone have a utility that they are willing to donate > >>> that can scan an IDS (10.0.FC6) logical log and reconstruct > >>> the original data for rows that were updated? > >>> > >>> A user used an UPDATE stmt to update 4 char and 2 SmallInt > >>> columns and forgot to include a WHERE clause, updating those 6 > >>> > >> columns > >> > >>> in 2700 rows to the wrong value. > >>> > >>> All of the transactions are in the same logical log which > >>> is on-line in the logs dbspaces. > >>> > >>> I need something that can scan this logical log and reconstruct > >>> the "before" data for these 2700 rows and write out the rowid, > >>> column name, and before values - so that I can create sql to > >>> update the data back to its original state. > >>> > >>> Thanks, > >>> > >>> Jim > >>> > >>> > >>> > >>> > >> > *********************************************************************** > >> ******** > >> > >>> Forum Note: Use "Reply" to post a response in the discussion forum. > >>> > >>> > >>> > >> > *********************************************************************** > >> ******** > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > > > > > > > *********************************************************************** > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

Art, (The timing on our posts to one another may be a little off) Informix tech support pretty much confirmed what you say. I think that I now have enough info from them and from the manual to write a program to get my original data values from the log, but it may be faster to get it another way by reconstruction from another source. I will say though that if you were to write such a utility, it would be a "hot item". I know for a fact that such utilities are available for MS SQLSERVER (groan!), Oracle, and DB2. So, I assume by your reponse that I can be pretty sure that the IIUG does not have any such utility in it's repository. Regards, Jim > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art S. Kagel (Oninit LLC) > Sent: Wednesday, December 05, 2007 11:07 AM > To: ids@iiug.org > Subject: Re: looking for logical log data reconstructio.... [10605] > > Jim Cramer wrote: > > You're in virgin territory I'm afraid Jim. This is definitely doable, > but I don't know anyone outside IBM/Informix who's attempted it. > > It's on my list of things I'd like to tackle. So, if you want to > contract someone to do it for you, let me know. It was right behind > "Recover a server's data after a catastrophic crash", but I did that > one > recently, so I'm up for it. ;-) > > Art S. Kagel > > > I was going to do just that using archecker, normally > > a simple, fast matter, ... and then discovered > > that a problem with my onbar logical log backups > > is preventing me from doing it. > > > > So, I am looking to mine out the changes and create > > a script of UPDATE statements to restore the affected > > data. > > > > Jim > > > > > >> -----Original Message----- > >> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > Of > >> Peter_Logan@spartanstores.com > >> Sent: Wednesday, December 05, 2007 10:44 AM > >> To: ids@iiug.org > >> Subject: Re: looking for logical log data reconstructio.... [10602] > >> > >> Why not do a table level restore prior to the update ... that can > >> either > >> go to a file ... or a table ... then you can just fix it from there > ... > >> > >> "Jim Cramer" <jim-cramer@uiowa.edu> > >> Sent by: ids-bounces@iiug.org > >> 12/05/2007 11:36 AM > >> Please respond to > >> ids@iiug.org > >> > >> To > >> ids@iiug.org > >> cc > >> > >> Subject > >> looking for logical log data reconstruction ut.... [10598] > >> > >> Hi, > >> > >> Does anyone have a utility that they are willing to donate > >> that can scan an IDS (10.0.FC6) logical log and reconstruct > >> the original data for rows that were updated? > >> > >> A user used an UPDATE stmt to update 4 char and 2 SmallInt > >> columns and forgot to include a WHERE clause, updating those 6 > columns > >> in 2700 rows to the wrong value. > >> > >> All of the transactions are in the same logical log which > >> is on-line in the logs dbspaces. > >> > >> I need something that can scan this logical log and reconstruct > >> the "before" data for these 2700 rows and write out the rowid, > >> column name, and before values - so that I can create sql to > >> update the data back to its original state. > >> > >> Thanks, > >> > >> Jim > >> > >> > >> > *********************************************************************** > >> ******** > >> > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > >> > *********************************************************************** > >> ******** > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > > > > > > > *********************************************************************** > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

Yep. Bad news there. Usage: onlog [-l] [-q] [-b] [-d <tape device>] [-n <starting log unique identifier>-<ending log unique identifier>] [-u <user name>] [-t <TBLspace number>] [-x <transaction number>] -l Display maximum information about each log record -q Do not display program header -b Display information about logged BLOB pages (-d option only) -d Read from tape device -n Display the specified log(s) -u Display the specified user(s) -t Display the specified TBLspace(s) -x Display the specified transaction(s) At least with the various options listed above (at least -l and -u and possible the -x), you can grab a quick list of records to a flat file that you can then parse to perform your updates. I hope you have enough logs that none of the necessary log files have already been reused. Good luck. Take care. Clifton M. Bean Informix DBA / AIX System Admin Currency Technics & Metrics Phone: (972) 812-1411 x244 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jim Cramer Sent: Wednesday, December 05, 2007 11:58 AM To: ids@iiug.org Subject: RE: looking for logical log data reconstructio.... [10608] Hi John, Thanks for your reply. See my previous responses to the same suggestion. To be more specific and to confess my guilt, here is exactly why I cannot use archecker's point in time resore, and why archecker will do only the physical restore and nothing from the logical restore phase. After installing IDS 10 on an instance, I did a dbexport from the 7.31 instance that was being replaced by 10.x. I then set LTAPEDEV to /dev/null in onconfig for the new server so that the dbimport would not generate a bunch of transactions. Looking at the start of the onbar bar_act.log file I see: "(-43183) WARNING: Logical Logs will not be backed up / salvaged because LTAPEDEV value is /dev/null ." logged when I did my level-0 after the dbimport into IDS 10. Apparently I forgot to change this config back to something other than /dev/null before the level-0 and so the logical log backup that went with the "onbar -b -w -L 0" went into the bit bucket. At some point before the next backup, I fixed this and all of the level-1 and level-2 backups since have been backing up the logical logs. But, without that initial log backup, I am told by Informix tech support that archecker cannot proceed with the logical restore. It seems that I am hosed... If you feel that this is not true and that there is actually a way to do it, please let me know as soon as you can. Thanks again, Jim Regards, Jim > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > John Miller iii > Sent: Wednesday, December 05, 2007 11:38 AM > To: ids@iiug.org > Subject: RE: looking for logical log data reconstructio.... [10607] > > Jim: > > Why can you not process the logical logs with archecker?? > > Archecker Point-In-Time Table Level Restore > will restore the data from both the physical restore > followed by restoring the logical logs. > > You should be able to restore the data up to the exact > transaction that started the update > > John > > = > > "Jim Cramer" = > > <jim-cramer@uiowa = > > ..edu> = > To > > Sent by: ids@iiug.org = > > ids-bounces@iiug. = > cc > > org = > > Subj= > ect > > RE: looking for logical log data= > > 12/05/2007 09:01 reconstructio.... [10604] = > > AM = > > = > > = > > Please respond to = > > ids@iiug.org = > > = > > = > > Art, > > As per my previous reply, just now, I would > have done that but my logical logs cannot > be processed and restored from by archecker > or onbar restore. > > Therefore I was wondering if anyone had a tool > for reconstruction data prior to UPDATEs from > the logical log. > > Regards, > > Jim > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > Of= > > > Art S. Kagel (Oninit LLC) > > Sent: Wednesday, December 05, 2007 10:41 AM > > To: ids@iiug.org > > Subject: Re: looking for logical log data reconstructio.... [10600] > > > > Jim Cramer wrote: > > > Hi, > > > > > > > > Jim, > > > > If the original data is on the latest archive you can use archecker > t= > o > > extract the data from the archive and restore it to any table, > either= > > > the original or a table created just for the recovery. Much easier > th= > an > > trying to parse the logical log records. > > > > You cannot recover completely this way if anyone else modified the > sa= > me > > records after the archive however. > > > > Art S. Kagel > > > > > Does anyone have a utility that they are willing to donate > > > that can scan an IDS (10.0.FC6) logical log and reconstruct > > > the original data for rows that were updated? > > > > > > A user used an UPDATE stmt to update 4 char and 2 SmallInt > > > columns and forgot to include a WHERE clause, updating those 6 > > columns > > > in 2700 rows to the wrong value. > > > > > > All of the transactions are in the same logical log which > > > is on-line in the logs dbspaces. > > > > > > I need something that can scan this logical log and reconstruct > > > the "before" data for these 2700 rows and write out the rowid, > > > column name, and before values - so that I can create sql to > > > update the data back to its original state. > > > > > > Thanks, > > > > > > Jim > > > > > > > > > > > > *********************************************************************= > ** > > ******** > > > Forum Note: Use "Reply" to post a response in the discussion > forum.= > > > > > > > > > > > > > > *********************************************************************= > ** > > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > *********************************************************************** > = > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > = > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Jim: In the fully supported and what is tested, yes support is correct. Tab= le Level Restore requires all logical logs from a level 0 archive until the desired point in time= . Now when you get down to the low level workings of archecker. I just converts each logical log record to an SQL delete or insert (updates are done as both a delet= e and insert) statement and place it in a table to be replayed. This is called the stager, wh= ich can be run independent of any other part of archecker. The last phase, run as a separate archecker process, is called the "applier". It reads the previous table extract= ing each SQL statement applying it. Hope this helps you understand what is going on. John = "Jim Cramer" = <jim-cramer@uiowa = .edu> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect RE: looking for logical log data= 12/05/2007 09:57 reconstructio.... [10608] = AM = = = Please respond to = ids@iiug.org = = = Hi John, Thanks for your reply. See my previous responses to the same suggestion. To be more specific and to confess my guilt, here is exactly why I cannot use archecker's point in time resore, and why archecker will do only the physical restore and nothing from the logical restore phase. After installing IDS 10 on an instance, I did a dbexport from the 7.31 instance that was being replaced by 10.x. I then set LTAPEDEV to /dev/null in onconfig for the new server so that the dbimport would not generate a bunch of transactions. Looking at the start of the onbar bar_act.log file I see: "(-43183) WARNING: Logical Logs will not be backed up / salvaged becaus= e LTAPEDEV value is /dev/null ." logged when I did my level-0 after the dbimport into IDS 10. Apparently I forgot to change this config back to something other than /dev/null before the level-0 and so the logical log backup that went with the "onbar -b -w -L 0" went into the bit bucket. At some point before the next backup, I fixed this and all of the level-1 and level-2= backups since have been backing up the logical logs. But, without that initial log backup, I am told by Informix tech suppor= t that archecker cannot proceed with the logical restore. It seems that I am hosed... If you feel that this is not true and that there is actually a way to d= o it, please let me know as soon as you can. Thanks again, Jim Regards, Jim > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of= > John Miller iii > Sent: Wednesday, December 05, 2007 11:38 AM > To: ids@iiug.org > Subject: RE: looking for logical log data reconstructio.... [10607] > > Jim: > > Why can you not process the logical logs with archecker?? > > Archecker Point-In-Time Table Level Restore > will restore the data from both the physical restore > followed by restoring the logical logs. > > You should be able to restore the data up to the exact > transaction that started the update > > John > > =3D > > "Jim Cramer" =3D > > <jim-cramer@uiowa =3D > > ..edu> =3D > To > > Sent by: ids@iiug.org =3D > > ids-bounces@iiug. =3D > cc > > org =3D > > Subj=3D > ect > > RE: looking for logical log data=3D > > 12/05/2007 09:01 reconstructio.... [10604] =3D > > AM =3D > > =3D > > =3D > > Please respond to =3D > > ids@iiug.org =3D > > =3D > > =3D > > Art, > > As per my previous reply, just now, I would > have done that but my logical logs cannot > be processed and restored from by archecker > or onbar restore. > > Therefore I was wondering if anyone had a tool > for reconstruction data prior to UPDATEs from > the logical log. > > Regards, > > Jim > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > Of=3D > > > Art S. Kagel (Oninit LLC) > > Sent: Wednesday, December 05, 2007 10:41 AM > > To: ids@iiug.org > > Subject: Re: looking for logical log data reconstructio.... [10600]= > > > > Jim Cramer wrote: > > > Hi, > > > > > > > > Jim, > > > > If the original data is on the latest archive you can use archecker= > t=3D > o > > extract the data from the archive and restore it to any table, > either=3D > > > the original or a table created just for the recovery. Much easier > th=3D > an > > trying to parse the logical log records. > > > > You cannot recover completely this way if anyone else modified the > sa=3D > me > > records after the archive however. > > > > Art S. Kagel > > > > > Does anyone have a utility that they are willing to donate > > > that can scan an IDS (10.0.FC6) logical log and reconstruct > > > the original data for rows that were updated? > > > > > > A user used an UPDATE stmt to update 4 char and 2 SmallInt > > > columns and forgot to include a WHERE clause, updating those 6 > > columns > > > in 2700 rows to the wrong value. > > > > > > All of the transactions are in the same logical log which > > > is on-line in the logs dbspaces. > > > > > > I need something that can scan this logical log and reconstruct > > > the "before" data for these 2700 rows and write out the rowid, > > > column name, and before values - so that I can create sql to > > > update the data back to its original state. > > > > > > Thanks, > > > > > > Jim > > > > > > > > > > > > *********************************************************************= =3D > ** > > ******** > > > Forum Note: Use "Reply" to post a response in the discussion > forum.=3D > > > > > > > > > > > > > > *********************************************************************= =3D > ** > > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > *********************************************************************= ** > =3D > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > =3D > > > *********************************************************************= ** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Yeah.....but at least I have not reused the logs containing the incorrect transactions. Regards, Jim > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Clifton Bean > Sent: Wednesday, December 05, 2007 12:10 PM > To: ids@iiug.org > Subject: RE: looking for logical log data reconstructio.... [10611] > > Yep. Bad news there. > > Usage: onlog [-l] [-q] [-b] [-d <tape device>] [-n <starting log unique > > identifier>-<ending log unique identifier>] [-u <user name>] > > [-t <TBLspace number>] [-x <transaction number>] > > -l Display maximum information about each log record > > -q Do not display program header > > -b Display information about logged BLOB pages (-d option only) > > -d Read from tape device > > -n Display the specified log(s) > > -u Display the specified user(s) > > -t Display the specified TBLspace(s) > > -x Display the specified transaction(s) > > At least with the various options listed above (at least -l and -u and > possible the -x), you can grab a quick list of records to a flat file > that > you can then parse to perform your updates. > > I hope you have enough logs that none of the necessary log files have > already been reused. > > Good luck. > > Take care. > > Clifton M. Bean > Informix DBA / AIX System Admin > Currency Technics & Metrics > > Phone: (972) 812-1411 x244 > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Jim > Cramer > Sent: Wednesday, December 05, 2007 11:58 AM > To: ids@iiug.org > Subject: RE: looking for logical log data reconstructio.... [10608] > > Hi John, > > Thanks for your reply. See my previous responses to the > same suggestion. > > To be more specific and to confess my guilt, here is exactly > why I cannot use archecker's point in time resore, and why > archecker will do only the physical restore and nothing from > the logical restore phase. > > After installing IDS 10 on an instance, I did a dbexport from the > 7.31 instance that was being replaced by 10.x. I then set LTAPEDEV > to /dev/null in onconfig for the new server so that the dbimport > would not generate a bunch of transactions. Looking at the start > of the onbar bar_act.log file I see: > "(-43183) WARNING: Logical Logs will not be backed up / salvaged > because > LTAPEDEV value is /dev/null ." > logged when I did my level-0 after the dbimport into IDS 10. > > Apparently I forgot to change this config back to something other than > /dev/null before the level-0 and so the logical log backup that went > with the "onbar -b -w -L 0" went into the bit bucket. At some point > before the next backup, I fixed this and all of the level-1 and level-2 > backups since have been backing up the logical logs. > > But, without that initial log backup, I am told by Informix tech > support > that archecker cannot proceed with the logical restore. It seems that > I am hosed... > > If you feel that this is not true and that there is actually a way to > do it, > > please let me know as soon as you can. > > Thanks again, > Jim > > Regards, > > Jim > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > John Miller iii > > Sent: Wednesday, December 05, 2007 11:38 AM > > To: ids@iiug.org > > Subject: RE: looking for logical log data reconstructio.... [10607] > > > > Jim: > > > > Why can you not process the logical logs with archecker?? > > > > Archecker Point-In-Time Table Level Restore > > will restore the data from both the physical restore > > followed by restoring the logical logs. > > > > You should be able to restore the data up to the exact > > transaction that started the update > > > > John > > > > = > > > > "Jim Cramer" = > > > > <jim-cramer@uiowa = > > > > ..edu> = > > To > > > > Sent by: ids@iiug.org = > > > > ids-bounces@iiug. = > > cc > > > > org = > > > > Subj= > > ect > > > > RE: looking for logical log data= > > > > 12/05/2007 09:01 reconstructio.... [10604] = > > > > AM = > > > > = > > > > = > > > > Please respond to = > > > > ids@iiug.org = > > > > = > > > > = > > > > Art, > > > > As per my previous reply, just now, I would > > have done that but my logical logs cannot > > be processed and restored from by archecker > > or onbar restore. > > > > Therefore I was wondering if anyone had a tool > > for reconstruction data prior to UPDATEs from > > the logical log. > > > > Regards, > > > > Jim > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > > Of= > > > > > Art S. Kagel (Oninit LLC) > > > Sent: Wednesday, December 05, 2007 10:41 AM > > > To: ids@iiug.org > > > Subject: Re: looking for logical log data reconstructio.... [10600] > > > > > > Jim Cramer wrote: > > > > Hi, > > > > > > > > > > > Jim, > > > > > > If the original data is on the latest archive you can use archecker > > t= > > o > > > extract the data from the archive and restore it to any table, > > either= > > > > > the original or a table created just for the recovery. Much easier > > th= > > an > > > trying to parse the logical log records. > > > > > > You cannot recover completely this way if anyone else modified the > > sa= > > me > > > records after the archive however. > > > > > > Art S. Kagel > > > > > > > Does anyone have a utility that they are willing to donate > > > > that can scan an IDS (10.0.FC6) logical log and reconstruct > > > > the original data for rows that were updated? > > > > > > > > A user used an UPDATE stmt to update 4 char and 2 SmallInt > > > > columns and forgot to include a WHERE clause, updating those 6 > > > columns > > > > in 2700 rows to the wrong value. > > > > > > > > All of the transactions are in the same logical log which > > > > is on-line in the logs dbspaces. > > > > > > > > I need something that can scan this logical log and reconstruct > > > > the "before" data for these 2700 rows and write out the rowid, > > > > column name, and before values - so that I can create sql to > > > > update the data back to its original state. > > > > > > > > Thanks, > > > > > > > > Jim > > > > > > > > > > > > > > > > > > *********************************************************************= > > ** > > > ******** > > > > Forum Note: Use "Reply" to post a response in the discussion > > forum.= > > > > > > > > > > > > > > > > > > > > > > *********************************************************************= > > ** > > > ******** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > *****************************

Yes, it helps. Thanks John. Regards, Jim > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > John Miller iii > Sent: Wednesday, December 05, 2007 12:27 PM > To: ids@iiug.org > Subject: RE: looking for logical log data reconstructio.... [10612] > > Jim: > > In the fully supported and what is tested, yes support is correct. Tab= > le > Level Restore requires > all logical logs from a level 0 archive until the desired point in > time= > .. > > Now when you get down to the low level workings of archecker. I just > converts each logical > log record to an SQL delete or insert (updates are done as both a > delet= > e > and insert) statement > and place it in a table to be replayed. This is called the stager, wh= > ich > can be run > independent of any other part of archecker. The last phase, run as a > separate archecker > process, is called the "applier". It reads the previous table extract= > ing > each SQL statement > applying it. > > Hope this helps you understand what is going on. > John > > = > > "Jim Cramer" = > > <jim-cramer@uiowa = > > ..edu> = > To > > Sent by: ids@iiug.org = > > ids-bounces@iiug. = > cc > > org = > > Subj= > ect > > RE: looking for logical log data= > > 12/05/2007 09:57 reconstructio.... [10608] = > > AM = > > = > > = > > Please respond to = > > ids@iiug.org = > > = > > = > > Hi John, > > Thanks for your reply. See my previous responses to the > same suggestion. > > To be more specific and to confess my guilt, here is exactly > why I cannot use archecker's point in time resore, and why > archecker will do only the physical restore and nothing from > the logical restore phase. > > After installing IDS 10 on an instance, I did a dbexport from the > 7.31 instance that was being replaced by 10.x. I then set LTAPEDEV > to /dev/null in onconfig for the new server so that the dbimport > would not generate a bunch of transactions. Looking at the start > of the onbar bar_act.log file I see: > "(-43183) WARNING: Logical Logs will not be backed up / salvaged > becaus= > e > LTAPEDEV value is /dev/null ." > logged when I did my level-0 after the dbimport into IDS 10. > > Apparently I forgot to change this config back to something other than > /dev/null before the level-0 and so the logical log backup that went > with the "onbar -b -w -L 0" went into the bit bucket. At some point > before the next backup, I fixed this and all of the level-1 and level- > 2= > > backups since have been backing up the logical logs. > > But, without that initial log backup, I am told by Informix tech > suppor= > t > that archecker cannot proceed with the logical restore. It seems that > I am hosed... > > If you feel that this is not true and that there is actually a way to > d= > o > it, > please let me know as soon as you can. > > Thanks again, > Jim > > Regards, > > Jim > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > Of= > > > John Miller iii > > Sent: Wednesday, December 05, 2007 11:38 AM > > To: ids@iiug.org > > Subject: RE: looking for logical log data reconstructio.... [10607] > > > > Jim: > > > > Why can you not process the logical logs with archecker?? > > > > Archecker Point-In-Time Table Level Restore > > will restore the data from both the physical restore > > followed by restoring the logical logs. > > > > You should be able to restore the data up to the exact > > transaction that started the update > > > > John > > > > =3D > > > > "Jim Cramer" =3D > > > > <jim-cramer@uiowa =3D > > > > ..edu> =3D > > To > > > > Sent by: ids@iiug.org =3D > > > > ids-bounces@iiug. =3D > > cc > > > > org =3D > > > > Subj=3D > > ect > > > > RE: looking for logical log data=3D > > > > 12/05/2007 09:01 reconstructio.... [10604] =3D > > > > AM =3D > > > > =3D > > > > =3D > > > > Please respond to =3D > > > > ids@iiug.org =3D > > > > =3D > > > > =3D > > > > Art, > > > > As per my previous reply, just now, I would > > have done that but my logical logs cannot > > be processed and restored from by archecker > > or onbar restore. > > > > Therefore I was wondering if anyone had a tool > > for reconstruction data prior to UPDATEs from > > the logical log. > > > > Regards, > > > > Jim > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > > Of=3D > > > > > Art S. Kagel (Oninit LLC) > > > Sent: Wednesday, December 05, 2007 10:41 AM > > > To: ids@iiug.org > > > Subject: Re: looking for logical log data reconstructio.... > [10600]= > > > > > > > Jim Cramer wrote: > > > > Hi, > > > > > > > > > > > Jim, > > > > > > If the original data is on the latest archive you can use > archecker= > > > t=3D > > o > > > extract the data from the archive and restore it to any table, > > either=3D > > > > > the original or a table created just for the recovery. Much easier > > th=3D > > an > > > trying to parse the logical log records. > > > > > > You cannot recover completely this way if anyone else modified the > > sa=3D > > me > > > records after the archive however. > > > > > > Art S. Kagel > > > > > > > Does anyone have a utility that they are willing to donate > > > > that can scan an IDS (10.0.FC6) logical log and reconstruct > > > > the original data for rows that were updated? > > > > > > > > A user used an UPDATE stmt to update 4 char and 2 SmallInt > > > > columns and forgot to include a WHERE clause, updating those 6 > > > columns > > > > in 2700 rows to the wrong value. > > > > > > > > All of the transactions are in the same logical log which > > > > is on-line in the logs dbspaces. > > > > > > > > I need something that can scan this logical log and reconstruct > > > > the "before" data for these 2700 rows and write out the rowid, > > > > column name, and before values - so that I can create sql to > > > > update the data back to its original state. > > > > > > > > Thanks, > > > > > > > > Jim > > > > > > > > > > > > > > > > > > *********************************************************************= > =3D > > ** > > > ******** > > > > Forum Note: Use "Reply" to post a response in the discussion > > forum.=3D > > > > > > > > > > > > > > > > > > > > > > *********************************************************************= > =3D > > ** > > > ******** > > > Forum Note: Use "Reply" to post a response in the discussion > forum.= > > > > > > *****************

Hi Jim We have software that does what you want. It reads and interprets the logical logs and re-contructs the rows of data from one or more tables that were deleted, inserted or updated. If you would like to discuss then please call me on +44 1244 316297 or email. Regards David Linthwaite Lintel Software Consultancy Ltd

David, Thanks for contacting me after reading my post. We are a small shop and I would have to know the price of your utility and see if I can justify it's purchase against the need for such a utility. Can you send me a price list, including any Educational Discounts that may apply? Can you point me to a web site with info on this product? Jim Cramer Data Base Administrator and Developer University of Iowa College of Engineering 1256 SC Iowa City, Iowa 52242 (319)335-5757 (desk) (319)384-0549 (fax) jcramer@engineering.uiowa.edu or jim-cramer@uiowa.edu > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > DAVID LINTHWAITE > Sent: Thursday, December 06, 2007 11:32 AM > To: ids@iiug.org > Subject: Re: RE: looking for logical log data reconstructio [10638] > > Hi Jim > > We have software that does what you want. It reads and interprets the > logical > logs and re-contructs the rows of data from one or more tables that > were > deleted, inserted or updated. If you would like to discuss then please > call me > on +44 1244 316297 or email. > > Regards > > David Linthwaite > Lintel Software Consultancy Ltd > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.