We're working on a report that compares a list of database tables and columns to what our master files contain, so that it gets a little easier for us to notice that the database design changed. For that reason I'm currently playing around with SYSFILES and SYSCOLUM.

The first thing I noticed, it would appear that the SYSCOLUM table is not listed in SYSCOLUM? Both files are listed in SYSFILES, though.

SQL FMI SET SYSFILES MASTER
SQL FMI SET FILECASE UPPER

TABLE FILE SYSFILES
BY FILENAME
WHERE UPCASE(64, FILENAME, 'A64') EQ 'SYSFILES' OR 'SYSCOLUM';
END

 FILENAME 
----------
 SYSCOLUM 
 SYSFILES 

TABLE FILE SYSCOLUM
BY TBNAME
WHERE UPCASE(64, TBNAME, 'A64') EQ 'SYSFILES' OR 'SYSCOLUM';
END

0 NUMBER OF RECORDS IN TABLE=        0  LINES=      0

The odd thing is that CHECK FILE lists SYSCOLUM's contents just fine (although with less detail). Is this deliberate?



The other thing bothering me is that we have quite a number of master files that reference a specific variation of a table in a (specific) library - "library" is DB2-speak for SCHEMA, I think.

For example, we have a master named "nltable1" that references a table "yadayada_nl/table1". How do I get to that reference?
I will need the actual library and table names for my report.

To complicate matters, the master/access file on the WebFOCUS client just references "EDASERVE/nltable1", while on the server the access-file references "DB2/yadayada_nl/table1". We'll probably need a server-side fex to obtain this information, that's not a problem.

This message has been edited. Last edited by: Wep5622,

Have you looked at SYSTABLE ?

Also look in the srv##/home/catalog directory for other sys masters, for other sources of info.

SYSFILES is separate to datasources as it looks at actual files.

That is indeed the information I was looking for! Thanks.

Glad I could help.