On 17 Dec 2012 16:12, Stone, Joel wrote:
I have a uniquely keyed physical MYLIB/FILEA.
Ops navigator shows FILEA as expected.
It also shows a separate line as "Temporary index" when I right-click
the file and click SHOW INDEXES.
What are these temp indexes? And why don't I see them with DSPFD
MYLIB/FILEA or DSPDBR MYLIB/FILEA?
They are temporary Access Paths. Typically [classically, CQE] they
were scoped to queries, to the query Open Data Path [ODP]. These are
tracked to a query, not to a file. However there are also SQE
Maintained Temporary Indexes (MTI)... commented on later.
Are these taking up resources?
The CPU to build the access path and the temporary storage that is
required. However, there are no storage charges to the user, because
they are not "owned" by any user profile.
Can I see these with green-screen commands?
None I know of. IIRC not even DMPOBJ of the PF. I believe that only
shows permanent indexes; they are LIC objects over LIC objects and both
temporary and unrelated to the creation of the permanent database file
member, so that they would not be visible in the dump, would be no surprise.
Are they LFs or what?
Access paths are internal objects over the LIC dataspace object; i.e.
defined over the data. An access path created as part of a permanent
file object is similarly associated with the data, only saved with the
data, is owned by a user, and is also "owned" by a file although may be
shared between multiple files and so the owning-file may be transitory
when the owning file is deleted.
They are listed as temporary, but they don't seem to go away.
When will they no longer appear?
For the "typical" temporary access path, when the query ODP is
closed. For the SQE Maintained Temporary Indexes (MTI), I do not recall
when they are destroyed... but those words and acronym as keywords used
for a search on DeveloperWorks or other IBM sites or other websites
related to IBM i [DB2 SQL] will likely assist to get an answer.
Destroyed: after PwrDwn before the next IPL completes for sure [as for
all temporary objects], and I think also when clearing the Plan Cache,
but I do not recall if there was a way to selectively remove them.
midrange.com
