Re: Astonished by embedded sql

Charles,

I was talking about cursors (declare cursor...) in your programm, nothing else and normaly you would work on a set of records and need a cursor. diffrent from RLA, there would be no need to do things like select ... from custumor where key = somValue, just join the needed tables together.

The usual way to make an update, you read a record and then you would make your update and for this read with a cursor and positioned update is much faster, than select into with following update where. If you have to read many records, it would even outperform blocked reading and update where key = someValue, thow blocked reads are very fast (1000 records in a dim DS as fast as one single read).

Without commitment controll (COMMIT *NONE) select into does not put any lock on the record and another job could read the same record between your read and the update and so you could get a lost update. Using a cursor for this, DB2/400 doesn't follow the sql standard (and the DB2 standard too) and acts rather similar to RLA and puts a share read lock to the current record until a positioned update or the next fetch operation.

BTW: using commitment controll lock handling is far more easy, before you give the controll to the user, just say commit and all record locks are freed!

I would recommend to have a look to the SQL reference...

D*B


--------------------------------------------------
From: "Charles Wilt" <charles.wilt@xxxxxxxxx>
Sent: Tuesday, October 12, 2010 7:23 PM
To: "RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
Subject: Re: Astonished by embedded sql

On Tue, Oct 12, 2010 at 11:21 AM, <dieter.bender@xxxxxxxxxxxx> wrote:

- SQL is a set oriented language and that means a cursor

Nonsense, there's a huge difference between a internal to the DB
cursor and cursor that is surfaced to your program.

Sure, when I say
update myfile set myfield = myfield + 1

Internally the DB is (probably) using a cursor. But that's not the
cursor I'm talking about.

- positioned updates need a cursor and are mostly much faster, than
searched updates

Sure, if you're talking about searched updates that update one row at
a time. But the idea is not to do anything one row at a time,
remember RBAR (Row By Agonizing Row).

This:
update myfile set myfield = myfield + 1
where mykey = somevalue

Is always going to be better/faster than the equivalent positioned
update via cursor.

- working without commitment controll, you should use a cursor for all
updates, otherwise its not safe for anything

You'd have to provide an example of this. I don't see any differences
in "safety" between a cursor and set based processing. With or
without commitment control.

The only difference I can think of off the top of my head, would be if
there's a possibility that records may already be locked. With a
cursor, it's easy to skip the locked records. But as of 6.1, the SKIP
LOCKED DATA clause allows an easy way for the set based update to do
the same thing.

But in all honestly, the problem of locked data usually indicates
poorly designed applications; namely keeping data locked while waiting
on user input. It's never been a good idea and now-a-days it usually
a horrible idea. Your application should be designed to minimize the
time data is locked.

in other words: there is nothing wrong with a cursor!

From a performance standpoint, there's lots wrong with a cursor or

otherwise using SQL to handle one row at a time. It may not be
noticeable when your development (and/or production) environment has
1000 rows. But move it to a production environment with 100,000 or
1,000,000 rows and you'll get bit. It doesn't matter if you're on DB2
for i, DB2 for LUW, Oracle, or MS SQL Server.

If you're on the i, instead of using SQL to do thing row by row, you'd
be better off using RPG's native op-codes. That's what _they_ are
designed for.

Charles
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.