List of source members in a file with SQL

Question

Is it possible to generate a list of all source members within an iSeries source file using SQL?

Might be similar to getting table definitions from SYSTABLES and SYSCOLUMNS, but I'm unable to find anything so far.

Answer 1

If you don't get an answer here, try asking over on the MIDRANGE-L mailing list.

Answer 2

You can write a CL program that retrieves the list of members using the DSPFD command. Perhaps you can call that program from a stored procedure?

Answer 3

Sadly SQL doesn't know anything about members, so all the sourcefile-info you could get from qsys2.syscolumns is, that they consist of three columns.

you want the member info and i suggest using the qshell( STRQSH ) together with a query to qsys2.systables as source files are specially marked there.

select table_schema , table_name from qsys2.systables where File_type = 'S'

i whacked together a qshell one-liner for copy&paste purposes ..

db2 -S "select '/QSYS.LIB/' concat table_schema concat '.LIB/' concat table_name concat '.FILE' from qsys2.systables where File_type = 'S'" | grep '/' | xargs -n1 find >/home/myuser/myfile

it pipes every member it finds to the IFS directory /home/myuser/myfile you could also specify a Sourcefile member. feel free to modify to your needs.

PS: it throws errors for Sourcefiles directly sitting in /QSYS.LIB, but i think you don't want those anyway..

take care! :)