Hi,
It seems indeed to be a commitment control issue, because in the update
statement WITH NC is not specified an rows get updated but not released.
But it is not a good idea to use commit=*NONE, when commitment control is
used by default.
If a rollback will be executed only those statements that are inserted,
updated or deletet under commitment control are reset. That means if an
rollback will be executed the rows changed by this update statement are not
reset, which may cause a lot of trouble.
I assume the final commit was forgotten. The rows stay locked until either a
commit or rollback is performed or the job ends. Closing the ODP will not
release commited rows.
Second using CLOSQLCSR with the option *ENDMOD is not an good idea with
regard to SQL performance.
The ODP used by the SQL-Statement will completely be deleted at the end of
the module, i.e. for all subsequent calls of the same module, full
optimizations must be performed, which is the most time consuming process in
executing an SQL statement.
Full optimization means:
1. An access plan must be created or at least validated
1.1. All indexes must be checked and estimated
1.2. Temporary objects needed to execute the SQL statement must be
determined.
2. The ODP must be opened
2.1. The temporary objects, such as hash tables, relative record lists,
tables, indexes must be created
2.2. The temporary objects must be filled with data
If the option CLOSQLCSR is set to *ENDACTGRP. The ODP will only be deleted
after the first execution. In the second execution the access path will be
validated again, the temporary objects must be recreated and filled with
data.
At the end of the second execution the ODP (if reuseable) stays open, that
means, the temporary objects will not be deleted. For all subsequent calls
of the module only the data in the temporary objects must be actualized.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Lim Hock-Chai
Gesendet: Wednesday, August 01, 2007 17:57
An: RPG programming on the AS400 / iSeries
Betreff: RE: Embedded SQL File Locking issue
I wonder if this is a commitment control issue? If you do not intend to use
commitment control, try add the following lines of codes:
C/exec sql
C+ set option commit=*none
C/end-exec
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Doug Palme
Sent: Wednesday, August 01, 2007 9:08 AM
To: 'RPG programming on the AS400 / iSeries'
Subject: Embedded SQL File Locking issue
Charles was kind enough to help me yesterday with a few issues and he
suggested that I include CLOSSQLCSR(*ENDMOD) on either the compiler options
or include it within the program in order to release any locks.
I have it on the compiler options (Charles, turns out with our change
control program, I can add them with a option 15) but when I run the program
interactively I still end up with a record lock...is there something else I
need to be doing here?
Here is the actual code that the program is processing at this time:
C/EXEC SQL
C+ INSERT INTO USETAXPF SELECT CCUSNR, CCUSNM, :ZSALES
C+ FROM CORCUSTM
C+ WHERE CSTATE = 'MN' AND
C+ CSTMTL = :ZTYPE
C/END-EXEC
************************************************************************
****
This transmission may contain information that is privileged, Confidential
and/or exempt from disclosure under applicable law. If you are not the
intended recipient, you are hereby notified that any disclosure, copying,
distribution, or use of the information contained herein (including any
reliance thereon) is STRICTLY PROHIBITED. If you received this transmission
in error, please immediately contact the sender and destroy the material in
its entirety, whether in electronic or hard copy format. Thank you.
************************************************************************
****
--
This is the RPG programming on the AS400 / iSeries (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.
midrange.com
