AW: Imbedded SQL record lock issue

Hi,

1. A select into is always read only and never locks a record.
2. NEVER EVER specify DDS described logical files within an SQL statement,
always either use the physical file(s) or SQL views.
When specifying DDS described logical files the SQL statement always gets
executed by the old/classic query engine (CQE).
The query optimizer needs to scan the DDS description an rewrite the SQL
statement based on the physical files (before starting optimization).
3. Only use CLOSQLCSR = *ENDMOD for SQL-Programs that are only called once!
Otherwise use the standard *ENDACTGRP.
SQL optimization may be time consuming for the first and second
execution.
The query optimizer needs to analyze and perhaps rewrite the SQL
statement,
Estimate all existing access paths (either in DDS described logical files
or SQL indexes)
Determine which temporary objects (such as hashtables or rrn lists) are
necessary to get access to the data (i.e. builds the access plan)
Create the temporary objects and fill them with data (i.e. opens the data
path ODP), which is the most time consuming part of the query execution.
After the first execution the temporary objects will be deleted (i.e. the
ODP gets closed)
The next time the same query will be executed the (already existing)
access plan will be validated and the ODP will be opened again.
After the second execution the ODP stays open, that means for the next
execution only the data in the temporary objects will be refreshed.
When specifying CLOSQLCSR = *ENDMOD instead, the ODP get always closed at
the end of the module and a full optimization/open must be performed.

As of your problem:
Please run the SQL statement (Select sum(select sum(epfnwp) from achonll1
...) based on the physical through iSeries Navigator's Visual Explain and
Check by which query engine it will be executed. If it is the CQE, you'll
get a comment why CQE is used. Try to move the query to the SQE.
If not yet exists, create either a DDS described logical file or better an
SQL index over epfbex, epfbln, epfbrs.

I assume in your program a temporary index must be created, which is rather
time consuming, because several table scans must be performed. When creating
a (temporary) access path a lot of CPU will be used.

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 Koester, Michael
Gesendet: Tuesday, 16. June 2009 22:39
An: rpg400-l@xxxxxxxxxxxx
Betreff: Imbedded SQL record lock issue

A co-worker is trying to troubleshoot a record-lock issue that he thinks
may be coming from an imbedded free-form SQL "Select Into" statement.
This is plain-vanilla stuff, with no SQL cursor involved. There appears
to be a lock held on the LF he has in the From clause.

We're running V4 R5.



There mainline begins with...

exec SQL

SET OPTION closqlcsr = *endmod,

commit = *none,

datfmt = *iso ;



The following SQL select occurs in an inline subprocedure.

exec sql

select sum(epfnwp)

into :tamt

from achonll1

where epfbex = :pi_mex

and epfbln = :pi_mln

and epfbrs = :pi_mrs

with nc ;



Is there something about SQL over logical views (DDS-defined) that is
causing this to lock, or is there something else we need to know?



Michael Koester