CODEWITHSUNDEEP
sqlibm-midrange
nearly_lunchtime
nearly_lunchtime

Reputation: 12933

List of source members in a file with SQL

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.

Upvotes: 9

Views: 19000

Answers (7)

smeep
smeep

Reputation: 362

Similar to @john-y's answer, you can also get a list of source physical file members like this:

SELECT SYSTEM_TABLE_MEMBER, SOURCE_TYPE FROM QSYS2/SYSPARTITIONSTAT WHERE
SYSTEM_TABLE_SCHEMA = 'MYLIB' AND SYSTEM_TABLE_NAME = 'QRPGLESRC'

Upvotes: 1

Alfredo
Alfredo

Reputation: 1

I need it to find where an specific source member was located, the RPGLE program has /COPY #IFSIO_H, but Its not qualified from which Source File. Therefore, I had to write a quick QSH to find the specific source member in all libraries, starting from QSYS.LIB: This only works if within the Member has the string.

find '/QSYS.LIB/' -name '*.MBR' -exec  grep -rins  '#IFSIO_H'  {} \;

It takes time, the grep and find are submitted to batch

Upvotes: 0

John Y
John Y

Reputation: 14559

More tables and views have been added to the system catalog since the other answers were presented. Now, you can get the list of members (a.k.a. "partitions" in SQL parlance) for a given file (a.k.a. table) like this:

SELECT TABLE_PARTITION FROM SYSPARTITIONSTAT
WHERE TABLE_NAME = myfile AND TABLE_SCHEMA = mylib

You can also get other information from SYSPARTITIONSTAT such as the number of rows in each member, and timestamps for the last change, save, restore, or use.

Upvotes: 11

stephj
stephj

Reputation: 21

Just used this, it works a treat.

DSPFD FILE(Libname/Filename)
      TYPE(*MBRLIST)         
      OUTPUT(*OUTFILE)       
      OUTFILE(QTEMP/MBRLIST)

then in SQL 

SELECT MLNAME FROM MBRLIST

Upvotes: 2

Michael Delk
Michael Delk

Reputation: 43

Basically, for just library and source file:

SELECT sys_dname, sys_tname    
FROM qsys2/systables         
ORDER BY sys_dname, sys_tname

However, for more detail, a procedure is outlined in this discussion.

Upvotes: 0

squarefox
squarefox

Reputation: 232

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! :)

Upvotes: 5

Kwebble
Kwebble

Reputation: 2075

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?

Upvotes: 2

Related Questions