|
On Nov 22, 2023, at 10:44 PM, Dan Bale <dan.bale@xxxxxxxxxxxxxxxxxxxxx> wrote:
I have the following in a CL program:
DltF DANWORK/$DSPFD@MPg
MonMsg CPF2105 /* file not found */
RunSQL ('Create table DANWORK/$DSPFD@MPg as ( +
SELECT B.SYSTEM_TABLE_SCHEMA as SrcLibrary +
, B.SYSTEM_TABLE_NAME as SrcFile +
, B.SYSTEM_TABLE_MEMBER as SrcMbrName +
, B.LAST_SOURCE_UPDATE_TIMESTAMP as SrcUpdTS +
, B.CREATE_TIMESTAMP as SrcCrtTS +
, B.LAST_CHANGE_TIMESTAMP as SrcTTchgTS +
, B.SOURCE_TYPE as SrcMbrType +
, cast( B.PARTITION_TEXT as char(50) CCSID 37 ) as SrcMbrText +
FROM DANWORK/$INCLDSRCF A join qsys2.SysPartitionStat B +
on ( A.SRCPF_LIBR = B.SYSTEM_TABLE_SCHEMA +
and A.SRCPF_NAME = B.SYSTEM_TABLE_NAME ) +
WHERE B.CREATE_TIMESTAMP IS NOT NULL +
) WITH DATA') +
COMMIT(*NONE)
When I submit two or more jobs that call this program to the job queue, the first job takes 20 - 25 minutes. Subsequent iterations of the same job, if executed shortly after the previous job, take 30 - 35 SECONDS. After an undetermined amount of time (12 hours, in my testing, although it could be shorter), the run time for the next job returns to 20 - 25 minutes.
I can only presume an access plan is built and retained for a certain amount of time. Do I have that right? What determines how long the access plan is retained? Is it documented? I found the section "Packages and access plans" in the SQL Reference and it seems to indicate that one cannot simply create a package on demand: "Packages are produced during distributed program preparation." What the heck is that?
BTW, Index Advisor recommended building two indexes on qsys2.SysPartitionStat, but I don't have authority and I think I've heard that IBM recommends NOT to build indexes or view on IBM system files.
- Dan
*** CONFIDENTIALITY NOTICE: The information contained in this communication may be confidential, and is intended only for the use of the recipients named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please return it to the sender immediately and delete the original message and any copy of it from your computer system. If you have any questions concerning this message, please contact the sender. ***
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
