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 :
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:
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?
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 :
Code:
//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;
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:
Code:
//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;
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?
Comment