Hi,
What does not reuseable mean ?.
Yeah, that's a good question. From my testing, I know that new records
will appear in a cursor as they are added, so that's not an issue.
If an ODP is reuseable or not has nothing to do with opening or closing a
cursor or actualizing data in an cursor.
An ODP may not be reuseable for complex SQL statements (for example joining
and grouping several tables together). To check if the ODP is reuseable,
just use STRDBG and analyze the joblog or run your SQL statement through
iSeries Navigator Visual Explain and check the information on the right
hand. If an ODP is not reuseable, a full optimization/open must be performed
each time the SQL statement will be executed.
A full open means the complete optimization process must be executed.
The full optimization process consists of:
1. The SQL statement must be analyzed and perhaps rewritten (based on the
phyiscal files/tables and not on the views or logical files used in the SQL
statements). Also the optimizer adds as much as possible information into
the select statement. For example file1 and file2 are joined over Key1. And
File1.Key1 is specified in the where conditions, because only Key1 = 5 is
read. In this way File2.Key1 must also be always 5. The optimizer will add
this information to the where conditions.
2. An access plan must be searched (found perhaps in the embedded SQL
program object or in an SQL package or in the SQE plan cache). If no access
plan can be found a new one must be built. The access plan contains all
information about which access path are used, which temporary objects, such
as hashtables, bitmaps or temporary indexes must be built to execute the SQL
statement. If an access plan already exists it must be validated, i.e. all
access paths must be checked and estimated.
3. As soon as the access plan is built or validated and the open-statement
is executed, the ODP gets built. That means all temporary objects neccessary
to execute the SQL statement are built and filled with data (and can be
fetched now).
4. After the first execution or if the ODP is not reuseable, the ODP (all
temporary objects with data) will be deleted at the close statement. (Hard
Close)
5. The next time the same SQL statement will be executed (the open statement
will be performed) the full optimization (Step 1 to 3) must be executed. The
second execution of the same SQL statement is normally faster than the first
time, because an access plan that can be validated is already available in
the job's cache.
6. After the second execution and if the SQL statement is reuseable, the ODP
stays available (pseudo close). The next time the same statement is executed
only the data in the temporary objects get actualized. (Pseudo Open).
Starting with the 3rd execution the opening must be very fast. If not the
SQL statement must be analyzed.
Using different host variables in the where condition for a static SQL
statement will not cause a full open, instead the already existing ODP can
be (re-)used, i.e. will be actualized with the selected data.
If the SQL program is compiled with the option ENDSQLCSR = *ENDMOD all
cursors get hard closed as soon as the module where the SQL statement is
located ends. That means each time the module is called a full optimization
must be performed. To gain performance SQL programs the default value for
ENDSQLCSR (=*ENDACTGRP) should not be changed. Also activation group *NEW
should not be used.
My users can accept, that they have to wait half a minute for the first
call, if all the subsequent calls are very fast, but if every call takes
half a minute they will complain.
BTW ODPs will also hard closed, if the files/tables are used unqualified and
the library list gets changed.
From my testing, I know that new records will appear in a cursor as they
are added, so that's not an issue.
That's not always true. SQL tends to create temporary objects, that are only
actualized if an OPEN statement gets executed.
But it is possible to force the optimizer to use life data, by either
compiling a program with embedded SQL with the Option ALWCPY = *NO or
specifying a cursor as sensitive. SQL performance will be better if the
optimizer can use temporary objects.
BTW: Dynamic sorts are also possible with static SQL (the ODP may not be
reuseable)
Because the order by condition is the last thing the optimizer considers,
the selected data will be filled into a temporary table and the result in
this temporary table will be sorted. In this way an already existing ODP
could be reused.
Example: Order By Case When Sort = 1 Then Key1 Else NULL End,
Case When Sort = 2 Then Key2 Else NULL End, Case When Sort
= 2 Then Key3 else NULL End,
Case When Sort = 3 Then Key5 Else NULL End
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 Joe Pluta
Gesendet: Friday, October 12, 2007 16:16
An: 'RPG programming on the AS400 / iSeries'
Betreff: RE: SETLL in SQL ?
From: David Foxwell
From Birgitta :
Executing the SQL statement CLOSE will only perform a hard close if
the SQL statement is not reuseable.
What does not reuseable mean ?.
Yeah, that's a good question. From my testing, I know that new records will
appear in a cursor as they are added, so that's not an issue.
My problem was this:
All clients, companies and people in one client file. 2 LFs : one on
the person's name, one on the company name.
The subfile is cleared and recharged at every pageup or down
operation, as a lot of users are adding and deleting clients all the
time.
So if there's a pageup or down requested, the program does SETLL,
READE from the last row on displayed or SETGT READPE from the first
row.
Now I've been asked to take into account a search on the client's
maiden name. Normally I would have created a 3rd LF. Unfortunately the
procedures will have to use another parameter and juggle now with 3
LF's.
This 3rd type of search, on the maiden name, would only be asked
rarely. There will be proportionally very few clients in the LF: only
married women! I was just wondering about SQL that's all.
And well you should. This is a great example of a real world business
requirement that really reflects the strengths and weaknesses of both
approaches.
First off, you will need indexes, whether logical files or SQL indexes, on
all three fields. That's simply a given for performance reasons, and I
don't think the SQL folks will argue with me there, although more than
likely they'll suggest SQL because they perform better.
This situation has things that lean it more towards SQL and more towards
ISAM depending on your viewpoint. From a programming maintenance
standpoint, SQL is easier because you'd be able to simply modify your SELECT
statement and use pretty much the exact same code for all three fields. And
that really is the beauty of SQL; a lot of the work is done for you by the
SQL engine.
But from the standpoint of your logic, where you want to do a SETLL/READE,
there really is no equivalent. So if you wanted to stick with that logic,
you would have to recreate the cursor each time. But it seems to me that
you might not need the set logic at all; why don't you just read forward and
backward? In my experience, logical files automatically include new records
as they are added to the database.
But then if you can get rid of the SETLL, SQL cursors also include new
records, so we're back to the initial question and frankly it looks to me
like an SQL cursor would work fine in your particular case, since you really
aren't positioning to a specific key, you're only using the SETLL for paging
purposes, and I don't think you need to do that.
Joe
midrange.com
