RE: How long does the ?access plan? stick around?

SRCPF_LIBR and SRCPF_NAME come from the $INCLDSRCF per the query below, which runs prior to the query in my OP. ArchivLibs is simply a table of PTC Implementer archive libraries, of which we have thousands, and which serve no purpose for my needs.

create table DANWORK/$INCLDSRCF as (
SELECT A.DBXLIB AS SRCPF_LIBR
, A.DBXFIL AS SRCPF_NAME
FROM QSYS.QADBXATR A
LEFT EXCEPTION JOIN
DANWORK/ArchivLibs
on A.DBXLIB = ArchiveLib
WHERE A.DBXATR = 'PF'
AND A.DBXREL = 'Y'
AND A.DBXTYP = 'S'
and ( A.DBXLIB not like 'Q%'
or A.DBXLIB in ( 'QGPL', 'QUSRSYS', 'QUSRTOOL' ) )
order by SRCPF_LIBR, SRCPF_NAME
) with data

- Dan

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jim Oberholtzer
Sent: Thursday, November 23, 2023 9:37 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: How long does the ?access plan? stick around?

I’m trying to recreate this for testing. I’m wondering how you create the two columns SRCPF_LIBR and SRCPF_NAME that is used in the join to the system tables.?

Jim Oberholtzer
Agile Technology Architects

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. ***