Retreving Insert Statements for Logical Logs

Retrieving Insert statements from the logical logs using onlog and a little known of process with the Help of Informix Support. The other week we had one of the worse things happen. One process was inserting data and another process was deleting that data concurrently. As an Informix DBA this is a nightmare we all hope would only happen to people we don't like. With a little help from a co-worker with no concept of the word "NO" he found out that there is a little known of process in which Tech Support may be able to help at least retrieve the insert statements. *** The tool cannot be given to the customer at this time and there doesn't seem to be a plan to provide it *** Call Support and ask tell them you need to have someone run the "log reading tool" and be ready to provide the following; o The table schema. o A sample of logical log output (onlog -l) that includes some INSERT and DELETE activity on the table. o The partition number of the table as referenced in the log. o The platform the customer is using. o The IDS version number. I hope that in a newer release we can get a tool to recreate SQL without having to call support. Most other major DB's have a feature like this and in the rare case it is needed it would be best we have the tool or at least know one is out there. Eric B. Rowell sending to informix-list

Eric Rowell wrote: > Retrieving Insert statements from the logical logs using onlog and a > little known of process with the Help of Informix Support. > > The other week we had one of the worse things happen. One process was > inserting data and another process was deleting that data concurrently. > As an Informix DBA this is a nightmare we all hope would only happen to > people we don't like. > > With a little help from a co-worker with no concept of the word "NO" he > found out that there is a little known of process in which Tech Support > may be able to help at least retrieve the insert statements. *** The > tool cannot be given to the customer at this time and there doesn't seem > to be a plan to provide it *** It's not little known of, it actually doesn't exist at all. :o) > Call Support and ask tell them you need to have someone run the "log > reading tool" and be ready to provide the following; > o The table schema. > o A sample of logical log output (onlog -l) that includes some INSERT > and DELETE activity on the table. > o The partition number of the table as referenced in the log. > o The platform the customer is using. > o The IDS version number. And you'll have to create a spare copy of the table to write the INSERTed rows into? :o) I think I might have fairly intimate knowledge of this utility, and trust me, it is a very last resort, not through any fault of the chap who wrote it -- who is a fine fellow and clever, nay, God-like in his genius and with a keen tastebud for a good pint -- but more through the vicissitudes of log analysis. > I hope that in a newer release we can get a tool to recreate SQL without > having to call support. Most other major DB's have a feature like this > and in the rare case it is needed it would be best we have the tool or > at least know one is out there. You will almost certainly never, ever get such a tool as a supported generic facility, primarily because the logs do not record SQL, even in a parsed form, but rather a series of page and slot addressed operations. So unless you have the complete suite of logical logs from the very first, and are prepared to trawl them all and you have never added dbspaces and a whole lot of other things, it is practically impossible to develop a generic log-reading tool. I'm sure if I have any of the detail wrong, Madison will be able to leap in and correct me. However, I think I've managed to remember the exposition of the difficulties reasonably accurately. And this has reminded me that I still owe the author a pint. :o) -- "C'est pas parce qu'on n'a rien ''' dire qu'il faut fermer sa gueule" - Coluche

onlog. will display the actual data, not the command. However, it will display the fact that an insert occured. Question for all - Is this a needed functionality? "Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message news:c1dpia$1hm1nb$1@ID-64669.news.uni-berlin.de... > Eric Rowell wrote: > > > Retrieving Insert statements from the logical logs using onlog and a > > little known of process with the Help of Informix Support. > > > > The other week we had one of the worse things happen. One process was > > inserting data and another process was deleting that data concurrently. > > As an Informix DBA this is a nightmare we all hope would only happen to > > people we don't like. > > > > With a little help from a co-worker with no concept of the word "NO" he > > found out that there is a little known of process in which Tech Support > > may be able to help at least retrieve the insert statements. *** The > > tool cannot be given to the customer at this time and there doesn't seem > > to be a plan to provide it *** > > It's not little known of, it actually doesn't exist at all. :o) > > > Call Support and ask tell them you need to have someone run the "log > > reading tool" and be ready to provide the following; > > o The table schema. > > o A sample of logical log output (onlog -l) that includes some INSERT > > and DELETE activity on the table. > > o The partition number of the table as referenced in the log. > > o The platform the customer is using. > > o The IDS version number. > > And you'll have to create a spare copy of the table to write the INSERTed > rows into? :o) > > I think I might have fairly intimate knowledge of this utility, and trust > me, it is a very last resort, not through any fault of the chap who wrote > it -- who is a fine fellow and clever, nay, God-like in his genius and with > a keen tastebud for a good pint -- but more through the vicissitudes of log > analysis. > > > I hope that in a newer release we can get a tool to recreate SQL without > > having to call support. Most other major DB's have a feature like this > > and in the rare case it is needed it would be best we have the tool or > > at least know one is out there. > > You will almost certainly never, ever get such a tool as a supported generic > facility, primarily because the logs do not record SQL, even in a parsed > form, but rather a series of page and slot addressed operations. So unless > you have the complete suite of logical logs from the very first, and are > prepared to trawl them all and you have never added dbspaces and a whole > lot of other things, it is practically impossible to develop a generic > log-reading tool. > > I'm sure if I have any of the detail wrong, Madison will be able to leap in > and correct me. However, I think I've managed to remember the exposition of > the difficulties reasonably accurately. > > And this has reminded me that I still owe the author a pint. :o) > > -- > "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" > - Coluche

It might just prove to be too much rope for some people. Madison Pruet wrote: > > onlog. will display the actual data, not the command. However, it will > display the fact that an insert occured. > > Question for all - Is this a needed functionality? > > "Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message > news:c1dpia$1hm1nb$1@ID-64669.news.uni-berlin.de... > > Eric Rowell wrote: > > > > > Retrieving Insert statements from the logical logs using onlog and a > > > little known of process with the Help of Informix Support. > > > > > > The other week we had one of the worse things happen. One process was > > > inserting data and another process was deleting that data concurrently. > > > As an Informix DBA this is a nightmare we all hope would only happen to > > > people we don't like. > > > > > > With a little help from a co-worker with no concept of the word "NO" he > > > found out that there is a little known of process in which Tech Support > > > may be able to help at least retrieve the insert statements. *** The > > > tool cannot be given to the customer at this time and there doesn't seem > > > to be a plan to provide it *** > > > > It's not little known of, it actually doesn't exist at all. :o) > > > > > Call Support and ask tell them you need to have someone run the "log > > > reading tool" and be ready to provide the following; > > > o The table schema. > > > o A sample of logical log output (onlog -l) that includes some INSERT > > > and DELETE activity on the table. > > > o The partition number of the table as referenced in the log. > > > o The platform the customer is using. > > > o The IDS version number. > > > > And you'll have to create a spare copy of the table to write the INSERTed > > rows into? :o) > > > > I think I might have fairly intimate knowledge of this utility, and trust > > me, it is a very last resort, not through any fault of the chap who wrote > > it -- who is a fine fellow and clever, nay, God-like in his genius and > with > > a keen tastebud for a good pint -- but more through the vicissitudes of > log > > analysis. > > > > > I hope that in a newer release we can get a tool to recreate SQL without > > > having to call support. Most other major DB's have a feature like this > > > and in the rare case it is needed it would be best we have the tool or > > > at least know one is out there. > > > > You will almost certainly never, ever get such a tool as a supported > generic > > facility, primarily because the logs do not record SQL, even in a parsed > > form, but rather a series of page and slot addressed operations. So unless > > you have the complete suite of logical logs from the very first, and are > > prepared to trawl them all and you have never added dbspaces and a whole > > lot of other things, it is practically impossible to develop a generic > > log-reading tool. > > > > I'm sure if I have any of the detail wrong, Madison will be able to leap > in > > and correct me. However, I think I've managed to remember the exposition > of > > the difficulties reasonably accurately. > > > > And this has reminded me that I still owe the author a pint. :o) > > > > -- > > "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" > > - Coluche -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

Madison Pruet wrote: > onlog. will display the actual data, not the command. However, it will > display the fact that an insert occured. > > Question for all - Is this a needed functionality? Well, in my fantasies, I have this ability to replay log activity to allow me to recreate a consistent replay for performance assessment and also for data recovery. I could probably fantasise up about a half dozen more situations where the facility would be useful. As you can see, I need to get out a lot more. :o) > "Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message > news:c1dpia$1hm1nb$1@ID-64669.news.uni-berlin.de... >> Eric Rowell wrote: >> >> > Retrieving Insert statements from the logical logs using onlog and a >> > little known of process with the Help of Informix Support. >> > >> > The other week we had one of the worse things happen. One process was >> > inserting data and another process was deleting that data concurrently. >> > As an Informix DBA this is a nightmare we all hope would only happen to >> > people we don't like. >> > >> > With a little help from a co-worker with no concept of the word "NO" he >> > found out that there is a little known of process in which Tech Support >> > may be able to help at least retrieve the insert statements. *** The >> > tool cannot be given to the customer at this time and there doesn't >> > seem to be a plan to provide it *** >> >> It's not little known of, it actually doesn't exist at all. :o) >> >> > Call Support and ask tell them you need to have someone run the "log >> > reading tool" and be ready to provide the following; >> > o The table schema. >> > o A sample of logical log output (onlog -l) that includes some INSERT >> > and DELETE activity on the table. >> > o The partition number of the table as referenced in the log. >> > o The platform the customer is using. >> > o The IDS version number. >> >> And you'll have to create a spare copy of the table to write the INSERTed >> rows into? :o) >> >> I think I might have fairly intimate knowledge of this utility, and trust >> me, it is a very last resort, not through any fault of the chap who wrote >> it -- who is a fine fellow and clever, nay, God-like in his genius and > with >> a keen tastebud for a good pint -- but more through the vicissitudes of > log >> analysis. >> >> > I hope that in a newer release we can get a tool to recreate SQL >> > without >> > having to call support. Most other major DB's have a feature like this >> > and in the rare case it is needed it would be best we have the tool or >> > at least know one is out there. >> >> You will almost certainly never, ever get such a tool as a supported > generic >> facility, primarily because the logs do not record SQL, even in a parsed >> form, but rather a series of page and slot addressed operations. So >> unless you have the complete suite of logical logs from the very first, >> and are prepared to trawl them all and you have never added dbspaces and >> a whole lot of other things, it is practically impossible to develop a >> generic log-reading tool. >> >> I'm sure if I have any of the detail wrong, Madison will be able to leap > in >> and correct me. However, I think I've managed to remember the exposition > of >> the difficulties reasonably accurately. >> >> And this has reminded me that I still owe the author a pint. :o) -- "C'est pas parce qu'on n'a rien ''' dire qu'il faut fermer sa gueule" - Coluche

Paul Watson wrote: > It might just prove to be too much rope for some people. Ooh. Another of my fantasies! > Madison Pruet wrote: >> >> onlog. will display the actual data, not the command. However, it will >> display the fact that an insert occured. >> >> Question for all - Is this a needed functionality? >> >> "Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message >> news:c1dpia$1hm1nb$1@ID-64669.news.uni-berlin.de... >> > Eric Rowell wrote: >> > >> > > Retrieving Insert statements from the logical logs using onlog and a >> > > little known of process with the Help of Informix Support. >> > > >> > > The other week we had one of the worse things happen. One process >> > > was inserting data and another process was deleting that data >> > > concurrently. As an Informix DBA this is a nightmare we all hope >> > > would only happen to people we don't like. >> > > >> > > With a little help from a co-worker with no concept of the word "NO" >> > > he found out that there is a little known of process in which Tech >> > > Support >> > > may be able to help at least retrieve the insert statements. *** The >> > > tool cannot be given to the customer at this time and there doesn't >> > > seem to be a plan to provide it *** >> > >> > It's not little known of, it actually doesn't exist at all. :o) >> > >> > > Call Support and ask tell them you need to have someone run the "log >> > > reading tool" and be ready to provide the following; >> > > o The table schema. >> > > o A sample of logical log output (onlog -l) that includes some INSERT >> > > and DELETE activity on the table. >> > > o The partition number of the table as referenced in the log. >> > > o The platform the customer is using. >> > > o The IDS version number. >> > >> > And you'll have to create a spare copy of the table to write the >> > INSERTed rows into? :o) >> > >> > I think I might have fairly intimate knowledge of this utility, and >> > trust me, it is a very last resort, not through any fault of the chap >> > who wrote it -- who is a fine fellow and clever, nay, God-like in his >> > genius and >> with >> > a keen tastebud for a good pint -- but more through the vicissitudes of >> log >> > analysis. >> > >> > > I hope that in a newer release we can get a tool to recreate SQL >> > > without >> > > having to call support. Most other major DB's have a feature like >> > > this and in the rare case it is needed it would be best we have the >> > > tool or at least know one is out there. >> > >> > You will almost certainly never, ever get such a tool as a supported >> generic >> > facility, primarily because the logs do not record SQL, even in a >> > parsed form, but rather a series of page and slot addressed operations. >> > So unless you have the complete suite of logical logs from the very >> > first, and are prepared to trawl them all and you have never added >> > dbspaces and a whole lot of other things, it is practically impossible >> > to develop a generic log-reading tool. >> > >> > I'm sure if I have any of the detail wrong, Madison will be able to >> > leap >> in >> > and correct me. However, I think I've managed to remember the >> > exposition >> of >> > the difficulties reasonably accurately. >> > >> > And this has reminded me that I still owe the author a pint. :o) -- "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche

"Eric Rowell" <erowell@knology.net> wrote in message news:c1dmv3$62a$1@terabinaries.xmission.com... > > Retrieving Insert statements from the logical logs using onlog and a > little known of process with the Help of Informix Support. > > The other week we had one of the worse things happen. One process was > inserting data and another process was deleting that data concurrently. > As an Informix DBA this is a nightmare we all hope would only happen to > people we don't like. > > With a little help from a co-worker with no concept of the word "NO" he > found out that there is a little known of process in which Tech Support > may be able to help at least retrieve the insert statements. *** The > tool cannot be given to the customer at this time and there doesn't seem > to be a plan to provide it *** Nor to trusted "partners".

"Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message news:c1e6cp$1gd7mo$2@ID-64669.news.uni-berlin.de... > Paul Watson wrote: > > > It might just prove to be too much rope for some people. > > Ooh. Another of my fantasies! Along with amyl nitrate and an orange, no doubt.

"Neil Truby" <neil.truby@ardenta.com> wrote in message news:c1e7na$1hsj5n$1@ID-162943.news.uni-berlin.de... > "Eric Rowell" <erowell@knology.net> wrote in message > news:c1dmv3$62a$1@terabinaries.xmission.com... > > > > Retrieving Insert statements from the logical logs using onlog and a > > little known of process with the Help of Informix Support. > > > > The other week we had one of the worse things happen. One process was > > inserting data and another process was deleting that data concurrently. > > As an Informix DBA this is a nightmare we all hope would only happen to > > people we don't like. > > > > With a little help from a co-worker with no concept of the word "NO" he > > found out that there is a little known of process in which Tech Support > > may be able to help at least retrieve the insert statements. *** The > > tool cannot be given to the customer at this time and there doesn't seem > > to be a plan to provide it *** > > Nor to trusted "partners". > Nor to IDS architects, either.

Neil Truby wrote: > "Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message > news:c1e6cp$1gd7mo$2@ID-64669.news.uni-berlin.de... >> Paul Watson wrote: >> >> > It might just prove to be too much rope for some people. >> >> Ooh. Another of my fantasies! > > Along with amyl nitrate and an orange, no doubt. Melon, surely? -- "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche

On Mon, 23 Feb 2004, Madison Pruet wrote: > onlog. will display the actual data, not the command. However, it will > display the fact that an insert occured. > > Question for all - Is this a needed functionality? Most definitely YES! As a last resort, when backups aren't available or haven't yet been taken (as seems to be in the OP's case, where inserted rows were deleted right away), this might be a godsent. Sort of like the old "tbzero", which must have saved quite a few DBA's asses after long transactions that couldn't be rolled back due to lack of logspace. Obnoxio's comments about the difficulties of such a tool notwith- standing, if it is at all possible to make it available (even only through tech support dialing in), please do it. Regards, Richard