Yeah, Frank, I think PFs based on DDS or DDL can't be searched using
path naming, which qsh used - due, perhaps, to the composite nature of
PFs. And I verified this when I used the "head" function on a PF, like
this - head returns the first 10 lines, at least it does that in QShell.
head /qsys.lib/corpdatavh.lib/act.file/act.mbr
head: 001-0023 Error found opening file
/qsys.lib/corpdatavh.lib/act.file/act.mbr. Operation not supported.
BTW, syntax needs to dive down all the way to the member - a PF is
treated like a directory in IFS path syntax.
Now a PF created only with RCDLEN CAN be processed with qsh - I just
verified this - first I created a flatfile, then copied a DDS-based PF
to it with FMTOPT(*NOCHK).
head /qsys.lib/vhamberg.lib/flatfile.file/flatfile.mbr
MANAGE MANAGE/ADVISE
ECOST ESTIMATE COST
DEFINE
DEFINE SPECS
LEADPR LEAD PROGRAM/DESIGN
SPECS
WRITE SPECS
LOGIC DESCRIBE LOGIC
ãCODE CODE PROGRAMS
&TEST TEST PROGRAMS
!ADMQS ADM QUERY SYSTEM
ÀTEACH TEACH CLASSES
You can see some odd characters, plus some lines that look split - there
are actually only 10 returned - but there are control bytes in here, too.
So even though I can imagine a technique or CPYFing all your PFs to a
long flatfile, that also seems like a bad idea. :)
You asked about SQL - I Googled for "use sql to see if a table contains
a string" and got an interesting hit -
https://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/
Now this is for SQL Server, but, within limits, every SQL platform has a
lot of similarities. It's worth a look for concepts, one of which is, in
SQL Server and probably every other SQL,
there is not a system stored procedure that does a "for each column"
But there IS a view that lists every column in every table on the system
- except for flat files, which probably isn't an issue. That view is
SYSCOLUMNS. Kind of like DSPFFD *ALLUSR/*ALL to an outfile and reading
that, except the works already been done by the system. You can write a
program that reads through SYSCOLUMNS with a FETCH loop, maybe with a
WHERE clause that limits to data type (you want only character fields,
right?) with a length >= the length of the search string. You could
build a SELECT for each table that comprises WHERE clauses for all
eligible character columns in that table. Maybe that can give you a start.
That SQL Server article has some ideas, it has a different name for the
columns info, but at least it has the word "column" in the name. Just
don't get TOO much in the deeps of that article, eh?
Cheers
Vern
On 2/28/2021 3:46 AM, frank boke wrote:
thanks vern. i was hoping to use qsh find but syntax is confusing and i’m
not sure if using it over qsys.lib will work.
On Wed, Feb 24, 2021 at 4:38 PM Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:
hi Frank
Basically, no, you can't with SQL - not easily for what you want, I think.
Of the options I looked at and mentioned, I see only a program that gets
the list of all PFs - could be done with SQL service object_statistics
or something like that - the for each file you get, do something like this
-
FNDSTRPDM STRING('search-string')
FILE(LIBNAME/FILENAME)
MBR(*ALL)
OPTION(*NONE)
PRTRCDS(*ALL)
You'll get lots of SPLF - although you MIGHT be able to do an OVRDBF to
point whatever PRTF it puts out at some kind of PF, maybe one created
not from source. I don't remember if there's an *ADD option on the
override.
Then lots of lines NOT to look at, right?
I'm just taking an off-the-cuff shot at how to do all this - test,
verify, see what works, eh?
And I hope someone else has a better idea!
Regards
Vern
On 2/24/2021 3:21 PM, frank boke wrote:
yes i am looking to see what files contain a certain string...actually a name...Is it possible to use SQL for that?
midrange.com
