Re: Deleting records from a table

But if I understood correctly the "bad" DELETE had no WHERE condition... I don't know the size of the table, but considering that if it succeeded you would have only records on the table which were inserted AFTER the DELETE.... Can't you be sure just by looking at the table contents? Obviously this would be clear on a few million rows table, not so clear in a table with a few rows... Regards. On Tue, Oct 18, 2016 at 2:38 PM, LARRY SORENSEN <LSORENSEN25@msn.com> wrote: > Thank you. > > I see a lot of ROLLBACKs in the log, but I can't tell which, if any, apply > to > that I am looking for. I ran onlog specifying the table that I am worried > about and there were still too many around the right time to know which may > apply. > > Larry > > ________________________________ > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Andreas > Legner > <andreas.legner@de.ibm.com> > Sent: Tuesday, October 18, 2016 3:30 AM > To: ids@iiug.org > Subject: Re: Deleting records from a table [37997] > > There are people trying this, but it's hard, and the best you can get is=20 > an SQL reflecting a particular single row INSERT/UPDATE/DELETE operation=20 > visible in the logs which not necessarily is the actual SQL that had > been=20 > ran. > > You'd have to differentiate at least three cases > - log records contain full row images - for INSERTs, DELETES and=20 > full-row-logged UPDATEs > > Here you could try applying appropriate table schema (take care of=20 > in-place alters!) > - log records contain only changed bytes of an update - for=20 > non-full-row-logged UPDATEs > > You first had to determine a row image reflecting either before or=20 > after image of such update, then you could apply the changes and proceed=20 > as above. > - anything out-of-row, i.e. blobs or sblobs being part of a row. > > That's going to be real complicated, but of course all the bits needed=20 > are there - how else would the server recover such log records :-D > > Has this delete rolled back finally? If so, I'd say there's nothing to=20 > worry about. > > Andreas > > From: "LARRY SORENSEN" <LSORENSEN25@msn.com> > To: ids@iiug.org > Date: 17.10.2016 20:56 > Subject: Re: Deleting records from a table [37995] > Sent by: ids-bounces@iiug.org > > Is there a way to get the SQL statement that was run from the onlog=20 > output?=20 > > =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F= > 5F=5F=5F=5F=5F=5F= > =5F=5F=5F=5F=5F=5F=5F=20 > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES=20 > RENAUT=20 > <jrenaut@us.ibm.com>=20 > Sent: Monday, October 17, 2016 10:01 AM=20 > To: ids@iiug.org=20 > Subject: Re: Deleting records from a table [37993]=20 > > Original post:=20 > > IDS 11.50.FC8=20 > > Solaris 10=20 > > If I have a user that logged into Informix and connected to a database via > = > > dbaccess.......=20 > > A user was deleting records from a table using SQL. She forgot to put a=20 > "WHERE" clause in, ran the command, but she hit delete before the > command=20 > completed because she saw her mistake.=20 > > Would some of the records have been deleted, or will it treat the > command=20 > as a=20 > transaction and do a "rollback"?=20 > > Larry=20 > > Response:=20 > > Well, regardless of what dbaccess should/would do...assuming you have=20 > logging=20 > on the database, you can determine exactly what happened by looking at=20 > onlog=20 > output. You should be able to find the transaction based on the time and=20 > the=20 > table's partnum and see if the transaction was compled in the onlog by=20 > either=20 > the presence of a committed record or rolledback record.=20 > > Jacques Renaut=20 > IBM Informix Advanced Support=20 > > ************************************************************ > ***************= > ****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > ************************************************************ > ***************= > ****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a114239e6b3305d053f25b91a

There are old (previous) records in the table, so I am assuming that the transaction was rolled back rather than just partially deleted. Larry ________________________________ From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Fernando Nunes <domusonline@gmail.com> Sent: Tuesday, October 18, 2016 9:55 AM To: ids@iiug.org Subject: Re: Deleting records from a table [38000] But if I understood correctly the "bad" DELETE had no WHERE condition... I don't know the size of the table, but considering that if it succeeded you would have only records on the table which were inserted AFTER the DELETE.... Can't you be sure just by looking at the table contents? Obviously this would be clear on a few million rows table, not so clear in a table with a few rows... Regards. On Tue, Oct 18, 2016 at 2:38 PM, LARRY SORENSEN <LSORENSEN25@msn.com> wrote: > Thank you. > > I see a lot of ROLLBACKs in the log, but I can't tell which, if any, apply > to > that I am looking for. I ran onlog specifying the table that I am worried > about and there were still too many around the right time to know which may > apply. > > Larry > > ________________________________ > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Andreas > Legner > <andreas.legner@de.ibm.com> > Sent: Tuesday, October 18, 2016 3:30 AM > To: ids@iiug.org > Subject: Re: Deleting records from a table [37997] > > There are people trying this, but it's hard, and the best you can get is=20 > an SQL reflecting a particular single row INSERT/UPDATE/DELETE operation=20 > visible in the logs which not necessarily is the actual SQL that had > been=20 > ran. > > You'd have to differentiate at least three cases > - log records contain full row images - for INSERTs, DELETES and=20 > full-row-logged UPDATEs > > Here you could try applying appropriate table schema (take care of=20 > in-place alters!) > - log records contain only changed bytes of an update - for=20 > non-full-row-logged UPDATEs > > You first had to determine a row image reflecting either before or=20 > after image of such update, then you could apply the changes and proceed=20 > as above. > - anything out-of-row, i.e. blobs or sblobs being part of a row. > > That's going to be real complicated, but of course all the bits needed=20 > are there - how else would the server recover such log records :-D > > Has this delete rolled back finally? If so, I'd say there's nothing to=20 > worry about. > > Andreas > > From: "LARRY SORENSEN" <LSORENSEN25@msn.com> > To: ids@iiug.org > Date: 17.10.2016 20:56 > Subject: Re: Deleting records from a table [37995] > Sent by: ids-bounces@iiug.org > > Is there a way to get the SQL statement that was run from the onlog=20 > output?=20 > > =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F= > 5F=5F=5F=5F=5F=5F= > =5F=5F=5F=5F=5F=5F=5F=20 > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of JACQUES=20 > RENAUT=20 > <jrenaut@us.ibm.com>=20 > Sent: Monday, October 17, 2016 10:01 AM=20 > To: ids@iiug.org=20 > Subject: Re: Deleting records from a table [37993]=20 > > Original post:=20 > > IDS 11.50.FC8=20 > > Solaris 10=20 > > If I have a user that logged into Informix and connected to a database via > = > > dbaccess.......=20 > > A user was deleting records from a table using SQL. She forgot to put a=20 > "WHERE" clause in, ran the command, but she hit delete before the > command=20 > completed because she saw her mistake.=20 > > Would some of the records have been deleted, or will it treat the > command=20 > as a=20 > transaction and do a "rollback"?=20 > > Larry=20 > > Response:=20 > > Well, regardless of what dbaccess should/would do...assuming you have=20 > logging=20 > on the database, you can determine exactly what happened by looking at=20 > onlog=20 > output. You should be able to find the transaction based on the time and=20 > the=20 > table's partnum and see if the transaction was compled in the onlog by=20 > either=20 > the presence of a committed record or rolledback record.=20 > > Jacques Renaut=20 > IBM Informix Advanced Support=20 > > ************************************************************ > ***************= > ****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > ************************************************************ > ***************= > ****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com Informix technology<http://informix-technology.blogspot.com/>; informix-technology.blogspot.com This is a small repository of information and a few articles about IBM Informix technology My email works... but I don't check it frequently... --001a114239e6b3305d053f25b91a ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

If the database is logged... there is no half way.... On Tue, Oct 18, 2016 at 5:35 PM, LARRY SORENSEN <LSORENSEN25@msn.com> wrote: > There are old (previous) records in the table, so I am assuming that the > transaction was rolled back rather than just partially deleted. > > Larry > > ________________________________ > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Fernando > Nunes > <domusonline@gmail.com> > Sent: Tuesday, October 18, 2016 9:55 AM > To: ids@iiug.org > Subject: Re: Deleting records from a table [38000] > > But if I understood correctly the "bad" DELETE had no WHERE condition... > I don't know the size of the table, but considering that if it succeeded > you would have only records on the table which were inserted AFTER the > DELETE.... > Can't you be sure just by looking at the table contents? > > Obviously this would be clear on a few million rows table, not so clear in > a table with a few rows... > Regards. > > On Tue, Oct 18, 2016 at 2:38 PM, LARRY SORENSEN <LSORENSEN25@msn.com> > wrote: > > > Thank you. > > > > I see a lot of ROLLBACKs in the log, but I can't tell which, if any, > apply > > to > > that I am looking for. I ran onlog specifying the table that I am worried > > about and there were still too many around the right time to know which > may > > apply. > > > > Larry > > > > ________________________________ > > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Andreas > > Legner > > <andreas.legner@de.ibm.com> > > Sent: Tuesday, October 18, 2016 3:30 AM > > To: ids@iiug.org > > Subject: Re: Deleting records from a table [37997] > > > > There are people trying this, but it's hard, and the best you can get > is=20 > > an SQL reflecting a particular single row INSERT/UPDATE/DELETE > operation=20 > > visible in the logs which not necessarily is the actual SQL that had > > been=20 > > ran. > > > > You'd have to differentiate at least three cases > > - log records contain full row images - for INSERTs, DELETES and=20 > > full-row-logged UPDATEs > > > > Here you could try applying appropriate table schema (take care of=20 > > in-place alters!) > > - log records contain only changed bytes of an update - for=20 > > non-full-row-logged UPDATEs > > > > You first had to determine a row image reflecting either before or=20 > > after image of such update, then you could apply the changes and > proceed=20 > > as above. > > - anything out-of-row, i.e. blobs or sblobs being part of a row. > > > > That's going to be real complicated, but of course all the bits needed=20 > > are there - how else would the server recover such log records :-D > > > > Has this delete rolled back finally? If so, I'd say there's nothing to=20 > > worry about. > > > > Andreas > > > > From: "LARRY SORENSEN" <LSORENSEN25@msn.com> > > To: ids@iiug.org > > Date: 17.10.2016 20:56 > > Subject: Re: Deleting records from a table [37995] > > Sent by: ids-bounces@iiug.org > > > > Is there a way to get the SQL statement that was run from the onlog=20 > > output?=20 > > > > =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F= > > 5F=5F=5F=5F=5F=5F= > > =5F=5F=5F=5F=5F=5F=5F=20 > > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of > JACQUES=20 > > RENAUT=20 > > <jrenaut@us.ibm.com>=20 > > Sent: Monday, October 17, 2016 10:01 AM=20 > > To: ids@iiug.org=20 > > Subject: Re: Deleting records from a table [37993]=20 > > > > Original post:=20 > > > > IDS 11.50.FC8=20 > > > > Solaris 10=20 > > > > If I have a user that logged into Informix and connected to a database > via > > = > > > > dbaccess.......=20 > > > > A user was deleting records from a table using SQL. She forgot to put > a=20 > > "WHERE" clause in, ran the command, but she hit delete before the > > command=20 > > completed because she saw her mistake.=20 > > > > Would some of the records have been deleted, or will it treat the > > command=20 > > as a=20 > > transaction and do a "rollback"?=20 > > > > Larry=20 > > > > Response:=20 > > > > Well, regardless of what dbaccess should/would do...assuming you have=20 > > logging=20 > > on the database, you can determine exactly what happened by looking at=20 > > onlog=20 > > output. You should be able to find the transaction based on the time > and=20 > > the=20 > > table's partnum and see if the transaction was compled in the onlog by=20 > > either=20 > > the presence of a committed record or rolledback record.=20 > > > > Jacques Renaut=20 > > IBM Informix Advanced Support=20 > > > > ************************************************************ > > ***************= > > ****=20 > > > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > > ************************************************************ > > ***************= > > ****=20 > > > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > > Informix technology<http://informix-technology.blogspot.com/>; > informix-technology.blogspot.com > This is a small repository of information and a few articles about IBM > Informix technology > > My email works... but I don't check it frequently... > > --001a114239e6b3305d053f25b91a > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --94eb2c07e80609413e053f26aecc

Thank you all for your responses on this. Larry ________________________________ From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Fernando Nunes <domusonline@gmail.com> Sent: Tuesday, October 18, 2016 11:04 AM To: ids@iiug.org Subject: Re: Deleting records from a table [38002] If the database is logged... there is no half way.... On Tue, Oct 18, 2016 at 5:35 PM, LARRY SORENSEN <LSORENSEN25@msn.com> wrote: > There are old (previous) records in the table, so I am assuming that the > transaction was rolled back rather than just partially deleted. > > Larry > > ________________________________ > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Fernando > Nunes > <domusonline@gmail.com> > Sent: Tuesday, October 18, 2016 9:55 AM > To: ids@iiug.org > Subject: Re: Deleting records from a table [38000] > > But if I understood correctly the "bad" DELETE had no WHERE condition... > I don't know the size of the table, but considering that if it succeeded > you would have only records on the table which were inserted AFTER the > DELETE.... > Can't you be sure just by looking at the table contents? > > Obviously this would be clear on a few million rows table, not so clear in > a table with a few rows... > Regards. > > On Tue, Oct 18, 2016 at 2:38 PM, LARRY SORENSEN <LSORENSEN25@msn.com> > wrote: > > > Thank you. > > > > I see a lot of ROLLBACKs in the log, but I can't tell which, if any, > apply > > to > > that I am looking for. I ran onlog specifying the table that I am worried > > about and there were still too many around the right time to know which > may > > apply. > > > > Larry > > > > ________________________________ > > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Andreas > > Legner > > <andreas.legner@de.ibm.com> > > Sent: Tuesday, October 18, 2016 3:30 AM > > To: ids@iiug.org > > Subject: Re: Deleting records from a table [37997] > > > > There are people trying this, but it's hard, and the best you can get > is=20 > > an SQL reflecting a particular single row INSERT/UPDATE/DELETE > operation=20 > > visible in the logs which not necessarily is the actual SQL that had > > been=20 > > ran. > > > > You'd have to differentiate at least three cases > > - log records contain full row images - for INSERTs, DELETES and=20 > > full-row-logged UPDATEs > > > > Here you could try applying appropriate table schema (take care of=20 > > in-place alters!) > > - log records contain only changed bytes of an update - for=20 > > non-full-row-logged UPDATEs > > > > You first had to determine a row image reflecting either before or=20 > > after image of such update, then you could apply the changes and > proceed=20 > > as above. > > - anything out-of-row, i.e. blobs or sblobs being part of a row. > > > > That's going to be real complicated, but of course all the bits needed=20 > > are there - how else would the server recover such log records :-D > > > > Has this delete rolled back finally? If so, I'd say there's nothing to=20 > > worry about. > > > > Andreas > > > > From: "LARRY SORENSEN" <LSORENSEN25@msn.com> > > To: ids@iiug.org > > Date: 17.10.2016 20:56 > > Subject: Re: Deleting records from a table [37995] > > Sent by: ids-bounces@iiug.org > > > > Is there a way to get the SQL statement that was run from the onlog=20 > > output?=20 > > > > =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F= > > 5F=5F=5F=5F=5F=5F= > > =5F=5F=5F=5F=5F=5F=5F=20 > > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of > JACQUES=20 > > RENAUT=20 > > <jrenaut@us.ibm.com>=20 > > Sent: Monday, October 17, 2016 10:01 AM=20 > > To: ids@iiug.org=20 > > Subject: Re: Deleting records from a table [37993]=20 > > > > Original post:=20 > > > > IDS 11.50.FC8=20 > > > > Solaris 10=20 > > > > If I have a user that logged into Informix and connected to a database > via > > = > > > > dbaccess.......=20 > > > > A user was deleting records from a table using SQL. She forgot to put > a=20 > > "WHERE" clause in, ran the command, but she hit delete before the > > command=20 > > completed because she saw her mistake.=20 > > > > Would some of the records have been deleted, or will it treat the > > command=20 > > as a=20 > > transaction and do a "rollback"?=20 > > > > Larry=20 > > > > Response:=20 > > > > Well, regardless of what dbaccess should/would do...assuming you have=20 > > logging=20 > > on the database, you can determine exactly what happened by looking at=20 > > onlog=20 > > output. You should be able to find the transaction based on the time > and=20 > > the=20 > > table's partnum and see if the transaction was compled in the onlog by=20 > > either=20 > > the presence of a committed record or rolledback record.=20 > > > > Jacques Renaut=20 > > IBM Informix Advanced Support=20 > > > > ************************************************************ > > ***************= > > ****=20 > > > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > > ************************************************************ > > ***************= > > ****=20 > > > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com [http://4.bp.blogspot.com/_owXf8TIBUXI/S2bpGijdAWI/AAAAAAAAABc/AlV-RTx0M38/S220- s75/fnunes.jpg]<http://informix-technology.blogspot.com/>; Informix technology<http://informix-technology.blogspot.com/>; informix-technology.blogspot.com This is a small repository of information and a few articles about IBM Informix technology > > Informix technology<http://informix-technology.blogspot.com/>; [http://4.bp.blogspot.com/_owXf8TIBUXI/S2bpGijdAWI/AAAAAAAAABc/AlV-RTx0M38/S220- s75/fnunes.jpg]<http://informix-technology.blogspot.com/>; Informix technology<http://informix-technology.blogspot.com/>; informix-technology.blogspot.com This is a small repository of information and a few articles about IBM Informix technology > informix-technology.blogspot.com > This is a small repository of information and a few articles about IBM > Informix technology > > My email works... but I don't check it frequently... > > --001a114239e6b3305d053f25b91a > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ********************************************************