Embedded SQL Access Paths and For Update Of

[Vectorspace]

I've been an RPGLE programmer for a while, but the company I work for who taught me RPG does not use embedded SQL. I am only just starting to teach myself.

According to this article: http://www.itjungle.com/fhg/fhg010709-story02.html
If you update a table through a cursor, SQL will not use access paths that use columns that might be updated. And if you omit the for update of clause, SQL will think all columns will be updated.

I have a few questions about this:

1. Say I had a table listing transactions, with a processed yes/no column indicating if each transaction has been processed or not, and I want to process those transactions that have not been processed yet and then update the flag. I would have done something like :

  //The transact file is keyed on the processed column

  exec sql declare trans cursor for 
      select * from transact
        where processed = 'N'
        for update of processed;

  do xxxx;

    exec sql fetch transact into :tdata;

    // Process transaction

    If successful;
      exec sql update transact set processed = 'Y'
        where current of trans;
    endif;
  enddo;

If I understand the article correctly, because the processed column can be updated, its access path cannot be used. So the SQL will have to read through every record instead of using the key. So if there are thousands of processed transactions and only a few unprocessed, it would be very inefficient.

Am I correct?
And if so, what would be the efficient way of doing this?



2. Say I am doing a delete instead of an update. I would have done this:

  //The transact file is keyed on the processed column

  exec sql declare trans cursor for 
      select * from transact
        where processed = 'N'
        for update of processed;

  do xxxx;

    exec sql fetch trans into :tdata;

    // Process transaction

    If successful;
      exec sql delete from transact
        where current of trans;
    endif;
  enddo;

No fields are being updated, so I can't use the for update of clause. Will the SQL processor still not use the access path on processed and read through every record? Or will it see there is no update statement for this cursor and know that processed will not be updated?


I am on 6.1.1 if that makes a difference.


P.S. I registered with the wrong username - how do I contact a moderator and request it be changed?

[TedHolt]

Re: Embedded SQL Access Paths and For Update Of

1. I'd have to play with it and probably ask IBM to be sure, but the first thing I would try would be to remove "for update of" and change the update to refer to the key field(s) rather than current of the cursor.

2. I wouldn't delete thru the cursor. I'd do it this way:

delete from transact where processed = 'N'.

[Jim_IT]

Re: Embedded SQL Access Paths and For Update Of

The article is probably correct as it would only make sense that the index would be ignored if part of the key is going to be changed. The way to update the keyed field is to use both a cursor and a prepared statement to perform the update using another index.

Deleting the current record using a cursor is a little different than using a cursor to update current record.

The cursor is first defined with hold for and then deleted using Where Current of CursorName;

Jim

[B.Hauser]

Re: Embedded SQL Access Paths and For Update Of

There is one rule, that the optimizer cannot use indexes (access paths) built over the columns to be updated. (Just for avoiding that the same row is processed over and over again, because the key fields have changed).
If FOR UPDATE OF is specified, the optimizer can use any index (access path) that does not include the specified columns to be updated.
If FOR UPDATE OF is not specified, the optimizer cannot use any index, because it is not known what columns are to be updated, i.e. it is expected that all columns will be modified.
I assume that there are additional where conditions for restricting the number of rows to be updated (not only the processed criteria).
With FOR UPDATE OF the optimizer can use access path (indexes) built over those columns specified within the WHERE condition (that are not updated).

Also if there is only the criteria processed Y or N I assume there are so much rows with either of these options, that the optimizer cannot use any index at all, simpliy because a table scan is less expensive (the optimizer only uses binary radix tree indexes only if up to 15 % of all records within a table are returned).

Birgitta

[Vectorspace]

Re: Embedded SQL Access Paths and For Update Of

Hi all

So it sounds like in both cases, if I want to process all processed = N records and then update/delete only some of them, I should use an enquiry-only fetch, and then anupdate/delete statement using the key instead of cursor?

  //The transact file is keyed on the processed column

  exec sql declare trans cursor for 
      select * from transact
        where processed = 'N'
        for fetch only;

  do xxxx;

    exec sql fetch trans into :tdata;

    // Process transaction

    If successful and updating;
      exec sql update transact set processed = 'Y'
        where keyfield = tdata.keyfield
    endif;

    If successful and deleting;
      exec sql delete from transact
        where keyfield = tdata.keyfield
    endif;

  enddo;

Makes sense, thanks all.

[Vectorspace]

Re: Embedded SQL Access Paths and For Update Of

Followup question

I assume for fetch only means the record is not locked. What if I wanted to read through the transact file for processed = Y, lock each record, and then either update, delete, or unlock the record depending on the results of the processing?

[Michael Jones]

Re: Embedded SQL Access Paths and For Update Of

2. I wouldn't delete thru the cursor. I'd do it this way:

delete from transact where processed = 'N'.

I definitely agree with Ted. SQL is intended and is best used to process a FULL set of rows with a single executable SQL statement. You will get better performance and greater simplicity by processing a full set of rows at a time. Since you are learning SQL, now is the time to learn it correctly before you develop less than optimum habits. Diligently avoid using cursors with fetch loops.

Your example does not show what you're doing with the transaction. But, let's say for example you're posting the transaction into another table called PROCESSED_TRANS. In that case, you can / should structure your code like this:

--Post rows not already posted
insert into PROCESSED_TRANS ( list of columns ) select [matching list of columns] from TRANSACT T where not exists( select 1 from PROCESSED_TRANS PT where PT.TRANS = T.TRANS )
;
--Delete rows that reached the processed table
delete from TRANSACT T where exists( select 1 from PROCESSED_TRANS PT where PT.TRANS = T.TRANS )
;

That gets the job done, without cursors, with better performance, and much greater simplicity. Only two statements get executed. Also, notice how column PROCESSED serves no purpose. It is better to drive transactions through your system based on the transaction ID, and remove columns like PROCESSED, unless you preserve the rows in a table like TRANSACT after they are processed. If you preserve rows in a table like TRANSACT after they are processed, then a column like PROCESSED may serve a purpose. Technically, a column like PROCESSED is redundant data. It is a redundant flag that represents whether or not the row has been processed (i.e. in this example it reached table PROCESSED_TRANS). Defining redundant columns should be avoided, but are occasionally appropriate to implement for performance reasons only.

Batch jobs, in particular, will greatly benefit from using SET based SQL techniques.