Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to Backup Data Rows Updated by a SQL for Reverting?

7 posts in Backup and Recovery Last posting was on 2011-04-11 02:03:13.0Z
hyjshanghai Posted on 2011-04-09 05:46:48.0Z
Sender: 1eb2.4d9ff0c7.1804289383@sybase.com
From: hyjshanghai@gmail.com
Newsgroups: sybase.public.ase.backup+recovery
Subject: How to Backup Data Rows Updated by a SQL for Reverting?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d9ff2c8.1edb.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Apr 2011 22:46:48 -0700
X-Trace: forums-1-dub 1302328008 10.22.241.41 (8 Apr 2011 22:46:48 -0700)
X-Original-Trace: 8 Apr 2011 22:46:48 -0700, 10.22.241.41
Lines: 18
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.backup+recovery:4145
Article PK: 48231

We want to provide revert functionality when executing a
complex SQL or stored procedure, so that if the logic of the
executed SQL is found wrong, we can revert its update over
the data.

The SQL or stored procedure operate on one table only.

One intuitive way is to backup all the records in the table
before we run the SQL, which is a waste of storage.

Another way is to define "select into" statements with the
same "where" clause as the updating SQL, to perform backup
before performing the business SQL. This way backups rows to
update only, but is not generic for all kinds of SQL and
store procedures.

Is there any better way to perform efficient, generic backup
to realize the reverting functionality?


"Mark A. Parsons" <iron_horse Posted on 2011-04-09 11:43:47.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.backup+recovery
Subject: Re: How to Backup Data Rows Updated by a SQL for Reverting?
References: <4d9ff2c8.1edb.1681692777@sybase.com>
In-Reply-To: <4d9ff2c8.1edb.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4da04673$1@forums-1-dub>
Date: 9 Apr 2011 04:43:47 -0700
X-Trace: forums-1-dub 1302349427 10.22.241.152 (9 Apr 2011 04:43:47 -0700)
X-Original-Trace: 9 Apr 2011 04:43:47 -0700, vip152.sybase.com
Lines: 46
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.backup+recovery:4146
Article PK: 48229

begin tran

update some rows

validate the update

if validation fails
then rollback tran
else commit tran

---------------------------

If for some reason you're talking about the validation process taking a 'long time' (ie, too long to hold open a
transaction), then consider using a trigger to write the 'old' rows to a backup/archive table (perhaps add a transaction
id to said rows so that you can distinguish between multiple update sessions).

Of course, the bigger issue is what to do if you have multiple updates, some affecting the same rows, and you want/need
to rollback one of the earlier updates, eg ...

update #1 : modify rows 1, 2, 3
update #2 : modify rows 1, 4, 5
rollback #1: undo changes to rows 2, 3; what to do about row 1 ? do you have to rollback #2 as well? do you then need
to replay #2? ?????

... basically the same issue you have with any version control system where you need to do consider rollbacks and merges.

On 04/09/2011 01:46, hyjshanghai@gmail.com wrote:
> We want to provide revert functionality when executing a
> complex SQL or stored procedure, so that if the logic of the
> executed SQL is found wrong, we can revert its update over
> the data.
>
> The SQL or stored procedure operate on one table only.
>
> One intuitive way is to backup all the records in the table
> before we run the SQL, which is a waste of storage.
>
> Another way is to define "select into" statements with the
> same "where" clause as the updating SQL, to perform backup
> before performing the business SQL. This way backups rows to
> update only, but is not generic for all kinds of SQL and
> store procedures.
>
> Is there any better way to perform efficient, generic backup
> to realize the reverting functionality?


hyjshanghai Posted on 2011-04-09 12:13:44.0Z
Sender: 26ab.4da04a4e.1804289383@sybase.com
From: hyjshanghai@gmail.com
Newsgroups: sybase.public.ase.backup+recovery
Subject: Re: How to Backup Data Rows Updated by a SQL for Reverting?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4da04d78.26e4.1681692777@sybase.com>
References: <4da04673$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 9 Apr 2011 05:13:44 -0700
X-Trace: forums-1-dub 1302351224 10.22.241.41 (9 Apr 2011 05:13:44 -0700)
X-Original-Trace: 9 Apr 2011 05:13:44 -0700, 10.22.241.41
Lines: 75
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.backup+recovery:4147
Article PK: 48230

Many thanks for your reply.

Yes, the validation involves human approval, too long for
transactions to hold.

Let me simplify my requirement a bit:
The data always goes through the same sequence of SQL
statements: update 1, update 2 and so on.
There won't be any change in the update order.

In this case, if update 1 is found wrong, yes, update 2, 3
.. should all be reverted. This is acceptable. You can
treat all these update as a whole and they are reverted as a
whole.

So, what is the generic way to detect and backup all the
records modified during all these update as a whole?

PS:
For such questions, could you recommend any other forum I
can turn to for more heated discussion?

> begin tran
>
> update some rows
>
> validate the update
>
> if validation fails
> then rollback tran
> else commit tran
>
> ---------------------------
>
> If for some reason you're talking about the validation
> process taking a 'long time' (ie, too long to hold open a
> transaction), then consider using a trigger to write the
> 'old' rows to a backup/archive table (perhaps add a
> transaction id to said rows so that you can distinguish
> between multiple update sessions).
>
> Of course, the bigger issue is what to do if you have
> multiple updates, some affecting the same rows, and you
> want/need to rollback one of the earlier updates, eg ...
>
> update #1 : modify rows 1, 2, 3
> update #2 : modify rows 1, 4, 5
> rollback #1: undo changes to rows 2, 3; what to do about
> row 1 ? do you have to rollback #2 as well? do you then
> need to replay #2? ?????
>
> ... basically the same issue you have with any version
> control system where you need to do consider rollbacks and
> merges.
>
>
> On 04/09/2011 01:46, hyjshanghai@gmail.com wrote:
> > We want to provide revert functionality when executing a
> > complex SQL or stored procedure, so that if the logic of
> > the executed SQL is found wrong, we can revert its
> > update over the data.
> >
> > The SQL or stored procedure operate on one table only.
> >
> > One intuitive way is to backup all the records in the
> > table before we run the SQL, which is a waste of
> storage. >
> > Another way is to define "select into" statements with
> > the same "where" clause as the updating SQL, to perform
> > backup before performing the business SQL. This way
> > backups rows to update only, but is not generic for all
> > kinds of SQL and store procedures.
> >
> > Is there any better way to perform efficient, generic
> > backup to realize the reverting functionality?


"Mark A. Parsons" <iron_horse Posted on 2011-04-09 14:37:59.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.backup+recovery
Subject: Re: How to Backup Data Rows Updated by a SQL for Reverting?
References: <4da04673$1@forums-1-dub> <4da04d78.26e4.1681692777@sybase.com>
In-Reply-To: <4da04d78.26e4.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4da06f47$1@forums-1-dub>
Date: 9 Apr 2011 07:37:59 -0700
X-Trace: forums-1-dub 1302359879 10.22.241.152 (9 Apr 2011 07:37:59 -0700)
X-Original-Trace: 9 Apr 2011 07:37:59 -0700, vip152.sybase.com
Lines: 79
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.backup+recovery:4148
Article PK: 48232

What's wrong with the trigger method? This provides a 'before' image of each UPDATE, so all you'd need to do is play
the trigger snapshots (ie, update base table with values in trigger snapshot) in reverse.

On 04/09/2011 08:13, hyjshanghai@gmail.com wrote:
> Many thanks for your reply.
>
> Yes, the validation involves human approval, too long for
> transactions to hold.
>
> Let me simplify my requirement a bit:
> The data always goes through the same sequence of SQL
> statements: update 1, update 2 and so on.
> There won't be any change in the update order.
>
> In this case, if update 1 is found wrong, yes, update 2, 3
> .. should all be reverted. This is acceptable. You can
> treat all these update as a whole and they are reverted as a
> whole.
>
> So, what is the generic way to detect and backup all the
> records modified during all these update as a whole?
>
> PS:
> For such questions, could you recommend any other forum I
> can turn to for more heated discussion?
>
>> begin tran
>>
>> update some rows
>>
>> validate the update
>>
>> if validation fails
>> then rollback tran
>> else commit tran
>>
>> ---------------------------
>>
>> If for some reason you're talking about the validation
>> process taking a 'long time' (ie, too long to hold open a
>> transaction), then consider using a trigger to write the
>> 'old' rows to a backup/archive table (perhaps add a
>> transaction id to said rows so that you can distinguish
>> between multiple update sessions).
>>
>> Of course, the bigger issue is what to do if you have
>> multiple updates, some affecting the same rows, and you
>> want/need to rollback one of the earlier updates, eg ...
>>
>> update #1 : modify rows 1, 2, 3
>> update #2 : modify rows 1, 4, 5
>> rollback #1: undo changes to rows 2, 3; what to do about
>> row 1 ? do you have to rollback #2 as well? do you then
>> need to replay #2? ?????
>>
>> ... basically the same issue you have with any version
>> control system where you need to do consider rollbacks and
>> merges.
>>
>>
>> On 04/09/2011 01:46, hyjshanghai@gmail.com wrote:
>>> We want to provide revert functionality when executing a
>>> complex SQL or stored procedure, so that if the logic of
>>> the executed SQL is found wrong, we can revert its
>>> update over the data.
>>>
>>> The SQL or stored procedure operate on one table only.
>>>
>>> One intuitive way is to backup all the records in the
>>> table before we run the SQL, which is a waste of
>> storage.>
>>> Another way is to define "select into" statements with
>>> the same "where" clause as the updating SQL, to perform
>>> backup before performing the business SQL. This way
>>> backups rows to update only, but is not generic for all
>>> kinds of SQL and store procedures.
>>>
>>> Is there any better way to perform efficient, generic
>>> backup to realize the reverting functionality?


hyjshanghai Posted on 2011-04-10 06:37:44.0Z
Sender: 36bb.4da12ab4.1804289383@sybase.com
From: hyjshanghai@gmail.com
Newsgroups: sybase.public.ase.backup+recovery
Subject: Re: How to Backup Data Rows Updated by a SQL for Reverting?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4da15038.3981.1681692777@sybase.com>
References: <4da06f47$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 9 Apr 2011 23:37:44 -0700
X-Trace: forums-1-dub 1302417464 10.22.241.41 (9 Apr 2011 23:37:44 -0700)
X-Original-Trace: 9 Apr 2011 23:37:44 -0700, 10.22.241.41
Lines: 100
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.backup+recovery:4149
Article PK: 48233

Yes, the trigger method is a good idea.
I just didn't understand what it is when replying to you in
my previous post. Sorry for bothering you.

Here is my understanding of the trigger method:
(Suppose all the update SQLs modify the same table only.)
(1) Create a snapshot table.
(2) We "create a trigger for update" as:
if deleted.id does not exist in the snapshot table, insert
deleted into snapshot.
Otherwise, ignore deleted, because we only inserted the most
initial data of any row into the snapshot.

To revert, override rows in the original table that are
backed up in the snapshot table. Then we can replay the
business SQL again on the original table again.

Am I understanding right?

> What's wrong with the trigger method? This provides a
> 'before' image of each UPDATE, so all you'd need to do is
> play the trigger snapshots (ie, update base table with
> values in trigger snapshot) in reverse.
>
> On 04/09/2011 08:13, hyjshanghai@gmail.com wrote:
> > Many thanks for your reply.
> >
> > Yes, the validation involves human approval, too long
> > for transactions to hold.
> >
> > Let me simplify my requirement a bit:
> > The data always goes through the same sequence of SQL
> > statements: update 1, update 2 and so on.
> > There won't be any change in the update order.
> >
> > In this case, if update 1 is found wrong, yes, update 2,
> > 3 .. should all be reverted. This is acceptable. You can
> > treat all these update as a whole and they are reverted
> > as a whole.
> >
> > So, what is the generic way to detect and backup all the
> > records modified during all these update as a whole?
> >
> > PS:
> > For such questions, could you recommend any other forum
> > I can turn to for more heated discussion?
> >
> >> begin tran
> >>
> >> update some rows
> >>
> >> validate the update
> >>
> >> if validation fails
> >> then rollback tran
> >> else commit tran
> >>
> >> ---------------------------
> >>
> >> If for some reason you're talking about the validation
> >> process taking a 'long time' (ie, too long to hold open
> a >> transaction), then consider using a trigger to write
> the >> 'old' rows to a backup/archive table (perhaps add a
> >> transaction id to said rows so that you can
> distinguish >> between multiple update sessions).
> >>
> >> Of course, the bigger issue is what to do if you have
> >> multiple updates, some affecting the same rows, and you
> >> want/need to rollback one of the earlier updates, eg
> .. >>
> >> update #1 : modify rows 1, 2, 3
> >> update #2 : modify rows 1, 4, 5
> >> rollback #1: undo changes to rows 2, 3; what to do
> about >> row 1 ? do you have to rollback #2 as well? do
> you then >> need to replay #2? ?????
> >>
> >> ... basically the same issue you have with any version
> >> control system where you need to do consider rollbacks
> and >> merges.
> >>
> >>
> >> On 04/09/2011 01:46, hyjshanghai@gmail.com wrote:
> >>> We want to provide revert functionality when executing
> a >>> complex SQL or stored procedure, so that if the
> logic of >>> the executed SQL is found wrong, we can
> revert its >>> update over the data.
> >>>
> >>> The SQL or stored procedure operate on one table only.
> >>>
> >>> One intuitive way is to backup all the records in the
> >>> table before we run the SQL, which is a waste of
> >> storage.>
> >>> Another way is to define "select into" statements with
> >>> the same "where" clause as the updating SQL, to
> perform >>> backup before performing the business SQL.
> This way >>> backups rows to update only, but is not
> generic for all >>> kinds of SQL and store procedures.
> >>>
> >>> Is there any better way to perform efficient, generic
> >>> backup to realize the reverting functionality?


"Mark A. Parsons" <iron_horse Posted on 2011-04-10 11:52:45.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.backup+recovery
Subject: Re: How to Backup Data Rows Updated by a SQL for Reverting?
References: <4da06f47$1@forums-1-dub> <4da15038.3981.1681692777@sybase.com>
In-Reply-To: <4da15038.3981.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4da19a0d$1@forums-1-dub>
Date: 10 Apr 2011 04:52:45 -0700
X-Trace: forums-1-dub 1302436365 10.22.241.152 (10 Apr 2011 04:52:45 -0700)
X-Original-Trace: 10 Apr 2011 04:52:45 -0700, vip152.sybase.com
Lines: 128
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.backup+recovery:4150
Article PK: 48235

Most of that is correct.

The only issue I'd see is the conditional saving of data into the snapshot table, though that will depend on your
requirements, eg:

1 - Suppose you perform 3 updates of the same data (update #1, update #2, update #3) and decide that updates #2 and #3
need to be rolled back (perhaps you want to re-issue the #2 and #3 updates) ... you wouldn't be able to rollback updates
#2 and #3 because you only have the data from update #1. (If you plan on managing the 3 updates as a single unit of
work, ie, all or nothing, then keeping just the data from #1 would make sense.)

2 - Suppose you have 2 unrelated updates of the same data (update #a, update #b), ie, #b has nothing to do with #a.
With the conditional trigger you mention you wouldn't save the 'before' data for update #b because a record with the
same id already exists from update #a. Rolling back #b at this point wouldn't be possible because you don't have the
'before' snapshot of update #b ... and rolling back based on the 'before' snapshot of update #a would be going to far.
(If you plan on clearing the snapshot table after each series of related updates, eg, update #a's 'before' data would be
removed before performing update #b, then that sounds ok ... as long as you're sure you've got a solid process in place
to make sure you clear the snapshot at the right time.)

It's not clear (yet) from your descriptions if the above could happen in your environment, or if you could have multiple
unrelated update sessions running in parallel against the same data ... so I'd be hesitant to ignore some 'before'
snapshots; I'd probably want to capture all 'before' snapshots JUST IN CASE I hadn't considered a particular scenario;
if you capture all 'before' snapshots you can always pick-n-choose which one(s) you need for a rollback, but if you
accidentally discard the wrong 'before' snapshots then your rollback process just got a lot tougher. I'd feel safer
with 'too much' data as opposed to 'too little' data.

On 04/10/2011 02:37, hyjshanghai@gmail.com wrote:
> Yes, the trigger method is a good idea.
> I just didn't understand what it is when replying to you in
> my previous post. Sorry for bothering you.
>
> Here is my understanding of the trigger method:
> (Suppose all the update SQLs modify the same table only.)
> (1) Create a snapshot table.
> (2) We "create a trigger for update" as:
> if deleted.id does not exist in the snapshot table, insert
> deleted into snapshot.
> Otherwise, ignore deleted, because we only inserted the most
> initial data of any row into the snapshot.
>
> To revert, override rows in the original table that are
> backed up in the snapshot table. Then we can replay the
> business SQL again on the original table again.
>
> Am I understanding right?
>
>> What's wrong with the trigger method? This provides a
>> 'before' image of each UPDATE, so all you'd need to do is
>> play the trigger snapshots (ie, update base table with
>> values in trigger snapshot) in reverse.
>>
>> On 04/09/2011 08:13, hyjshanghai@gmail.com wrote:
>>> Many thanks for your reply.
>>>
>>> Yes, the validation involves human approval, too long
>>> for transactions to hold.
>>>
>>> Let me simplify my requirement a bit:
>>> The data always goes through the same sequence of SQL
>>> statements: update 1, update 2 and so on.
>>> There won't be any change in the update order.
>>>
>>> In this case, if update 1 is found wrong, yes, update 2,
>>> 3 .. should all be reverted. This is acceptable. You can
>>> treat all these update as a whole and they are reverted
>>> as a whole.
>>>
>>> So, what is the generic way to detect and backup all the
>>> records modified during all these update as a whole?
>>>
>>> PS:
>>> For such questions, could you recommend any other forum
>>> I can turn to for more heated discussion?
>>>
>>>> begin tran
>>>>
>>>> update some rows
>>>>
>>>> validate the update
>>>>
>>>> if validation fails
>>>> then rollback tran
>>>> else commit tran
>>>>
>>>> ---------------------------
>>>>
>>>> If for some reason you're talking about the validation
>>>> process taking a 'long time' (ie, too long to hold open
>> a>> transaction), then consider using a trigger to write
>> the>> 'old' rows to a backup/archive table (perhaps add a
>>>> transaction id to said rows so that you can
>> distinguish>> between multiple update sessions).
>>>>
>>>> Of course, the bigger issue is what to do if you have
>>>> multiple updates, some affecting the same rows, and you
>>>> want/need to rollback one of the earlier updates, eg
>> ..>>
>>>> update #1 : modify rows 1, 2, 3
>>>> update #2 : modify rows 1, 4, 5
>>>> rollback #1: undo changes to rows 2, 3; what to do
>> about>> row 1 ? do you have to rollback #2 as well? do
>> you then>> need to replay #2? ?????
>>>>
>>>> ... basically the same issue you have with any version
>>>> control system where you need to do consider rollbacks
>> and>> merges.
>>>>
>>>>
>>>> On 04/09/2011 01:46, hyjshanghai@gmail.com wrote:
>>>>> We want to provide revert functionality when executing
>> a>>> complex SQL or stored procedure, so that if the
>> logic of>>> the executed SQL is found wrong, we can
>> revert its>>> update over the data.
>>>>>
>>>>> The SQL or stored procedure operate on one table only.
>>>>>
>>>>> One intuitive way is to backup all the records in the
>>>>> table before we run the SQL, which is a waste of
>>>> storage.>
>>>>> Another way is to define "select into" statements with
>>>>> the same "where" clause as the updating SQL, to
>> perform>>> backup before performing the business SQL.
>> This way>>> backups rows to update only, but is not
>> generic for all>>> kinds of SQL and store procedures.
>>>>>
>>>>> Is there any better way to perform efficient, generic
>>>>> backup to realize the reverting functionality?