IDS Forum

Re: split update into multiple transactions

Posted By: Roderick Schertler
Date: Thursday, 8 June 2017, at 4:23 p.m.

In Response To: Re: split update into multiple transactions (Art Kagel)

Thanks, Art. Using rowids is what I had to settle for.

The question was about doing this with a cursor using "where current of",
that's what I've not been able to do.

Roderick

On Thu, Jun 8, 2017 at 3:29 PM, Art Kagel <art.kagel@gmail.com> wrote:

> Roderick:
>
> The way to do this is to fetch the rowids that you need to update in a
> separate cursor, preferably in a separate database (use CONNECT TO and SET
> CONNECTION) session or cached into a local array, then in a separate
> session you can update a batch of rows by looping on the list of rowids
> fetched previously and commit periodically. If the rowids are fetched into
> a local array or from another session, the commits would interfere at all.
> If you do it looping on a cursor in the same session, you can declare that
> rowid cursor to be WITH HOLD so it will not close when you commit the
> updates.
>
> Look at the code for my dbdelete utility for other clues, but 99% of what
> you need is right here..
>
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.com
>
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on the IIUG, nor any other organization with which I am
> associated either explicitly, implicitly, or by inference. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity with which I am affiliated nor those of the entities themselves.
>
> On Thu, Jun 8, 2017 at 3:14 PM, Roderick Schertler <roderick@argon.org>
> wrote:
>
> > Thanks, I looked at tx_split. The way it's written it uses a wait-mode
> > lock, and if it times out it throws an error. I changed it to use
> not-wait
> > and updated the code to loop if it gets a -244 error. With this in place
> > the behavior was the same as in 4GL -- the cursor keeps trying to fetch
> the
> > same (locked) row rather than moving on to the next one.
> >
> > I also tested the behavior in Perl and it was the same as in 4GL and
> > ESQL/C.
> >
> > I've come to believe that what I'm trying to do just isn't possible using
> > "where current of".
> >
> > Thanks for the replies.
> >
> > Roderick
> >
> > On Wed, May 31, 2017 at 4:35 PM, Marcus Haarmann <
> > marcus.haarmann@midoco.de>
> > wrote:
> >
> > > take a look at tx_split, it is in the iiug code repository.
> > > http://members.iiug.org/software/archive/tx_split.html
> > > ftp://ftp.iiug.org/pub/informix/pub/tx_split.tar.gz
> > >
> > > You will need esql/c for this.
> > >
> > > Marcus Haarmann
> > >
> > > Von: "Roderick Schertler" <roderick@argon.org>
> > > An: "ids" <ids@iiug.org>
> > > Gesendet: Mittwoch, 31. Mai 2017 20:02:38
> > > Betreff: split update into multiple transactions [39306]
> > >
> > > Hi folks.
> > >
> > > I'm trying to split a simple update into multiple transactions, to
> > > avoid holding arbitrarily many locks. Locking the table (even in
> > > shared mode) is not an option.
> > >
> > > I need to be able to skip rows which are currently locked, they'll be
> > > updated by another run later. Using "lock mode wait" also isn't an
> > > option.
> > >
> > > Additionally, it would be good if I could do the update "where current
> > > of" a cursor rather than relying on the table's key or rowid. This is
> > > where I run into trouble.
> > >
> > > I first tried to do this using SPL. In order to use "where current of"
> > > you have to use a foreach loop. When I use "on exception" to deal with
> > > the locked rows I can't get it to continue to the next iteration of the
> > > foreach loop, instead it exits the foreach. See sample below.
> > >
> > > I next tried to do this using 4GL. This fails because an error
> > encountered
> > > doing a fetch of the locked row keeps the cursor on that locked row --
> > the
> > > next fetch tries to fetch the same row and fails again. I can't figure
> > out
> > > a way to get it to move past the locked row. A sample of this is below
> > > too.
> > >
> > > Thanks for any help.
> > >
> > > Roderick
> > >
> >
> >
> > ************************************************************
> > *******************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

Messages In This Thread

• split update into multiple transactions
  Roderick Schertler -- Wednesday, 31 May 2017, at 2:02 p.m.
  • Re: split update into multiple transactions
    david@smooth1.co.uk -- Wednesday, 31 May 2017, at 2:22 p.m.
    • Re: split update into multiple transactions
      RODERICK SCHERTLER -- Thursday, 1 June 2017, at 8:57 a.m.
      • RE: split update into multiple transactions
        Everett Mills -- Thursday, 1 June 2017, at 12:08 p.m.
  • Re: split update into multiple transactions
    Marcus Haarmann -- Wednesday, 31 May 2017, at 4:35 p.m.
    • Re: split update into multiple transactions
      Roderick Schertler -- Thursday, 8 June 2017, at 3:14 p.m.
      • Re: split update into multiple transactions
        Art Kagel -- Thursday, 8 June 2017, at 3:29 p.m.
        • Re: split update into multiple transactions
          Roderick Schertler -- Thursday, 8 June 2017, at 4:23 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.