Thanks Justin! Unfortunately, this appears to be restricted to indexes. I queried searching for the INDEX_NAME = Join LF, got one of the PFs (for which the keys were defined) but not the other. So, that would make sense, given the SYSPINDEX name.
However, I did get an answer on parsing the BASED_ON_FILES field in SYSFILES. Posting here for posterity:
select SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, b.LIBRARY, b.FILE, b.MEMBER, b.LF_FORMAT from QSYS2.SYSFILES as s,
json_table( s.BASED_ON_FILES,
'lax $.BASED_ON_FILES[*]'
COLUMNS (
LIBRARY VARCHAR(10) path 'lax $.LIBRARY',
FILE VARCHAR(10) path 'lax $.FILE',
MEMBER VARCHAR(10) path 'lax $.MEMBER',
LF_FORMAT VARCHAR(10) path 'lax $.LF_FORMAT'
)
) as b
where NUMBER_BASED_ON_FILES > 0;
I just needed to tweak the selection criteria for the files I looking for, and it works like a charm.
- Dan
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Justin Taylor
Sent: Thursday, November 14, 2024 8:32 AM
To: MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: DSPFD logical file "based on file" info to outfile
How about this SQL?
select * from QSYS2.SYSPINDEX
where INDEX_TYPE = 'LOGICAL'
date: Wed, 13 Nov 2024 18:39:22 +0000
from: Dan Bale <dan.bale@xxxxxxxxxxxxxxxxxxxxx>
subject: DSPFD logical file "based on file" info to outfile
Command "DSPFD <LF file> TYPE(*ALL)" shows the based-on physical
files. I need to get this info on all LF files into an outfile.
TYPE(*ALL) isn't valid for an outfile. I haven't found any TYPE
parameter that can be used with an outfile and that provides based-on physical files info.
SYSFILES has the information in the BASED_ON_FILES column, but it's
provided as:
{"BASED_ON_FILES":[{"LIBRARY":"TESTLIB","FILE":"SAR7001P","MEMBER":"SA
R7001P","LF_FORMAT":"RSAR7001"},{"LIBRARY":"TESTLIB","FILE":"CTYID","M
EMBER":"CTYID","LF_FORMAT":"RSAR7001"}]}
I'd really rather not have to parse that out.
Any ideas?
*** 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. ***
midrange.com
