I have hundreds of files that are being read from Oracle. I am using the following code
to produce a report that contains the names of all the tables:

SQL SQLORA
SELECT OWNER, TABLE_NAME FROM ALL_TABLES;
TABLE FILE SQLOUT
BY TABLE_NAME
WHERE TABLE_NAME NOT CONTAINS '$' OR '/' OR 'SMP';
WHERE OWNER CONTAINS 'OMS_OWNER';
END

PRODUCES THE FOLLOWING REPORT:

TABLE_NAME
ABC_TRUST_REPORT_TMP
ACCESSIBLE_FORM_TABLES
ACCOUNT_CODES
ACCOUNT_CODES_JN
ACCOUNT_PERIODS

How do I create a report against all of the masters in my application to capture
the FIELD_NAME, ALIAS, USAGE, ETC. IN a report?

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

If you create your reports based on the direct SQL, you do not have a master file.
What you seem to want is a information from the master files.

You can create a master file description for every view or database table on your oracle database.
How?
It highly depends on what software components you have from IBI.
I use the gui to create a synonym (which is both the MAS and the ACX file.

The ACX holds the basic connection information (Where is the system, what is the name of the table etc, number of keyfields)
In the master file you describe the record layout, the fieldnames used by Oracle, the field size from Oracle and the name you want to give to these fields, description, indexes etc.

(there is a separate book only on this issue)

I hope this gives you an idea...

And please update your signature as asked in the top issue on this forum.

There exists an ancient IBI-created Master called comaster.mas that I found in one of the sample app folders a long time ago (I don't know if a newer one exists). It describes the different attributes of a master file. You can use that to do what you need.

This should get you on your way:

comaster.mas

SUFFIX=COM,SEGNAME=FILEID
FIELDNAME=FILENAME   ,FILE       ,A28 ,     ,$
FIELDNAME=FILE SUFFIX ,SUFFIX    ,A8 ,     ,$
FIELDNAME=FDEFCENT   ,FDFC       ,A4 ,     ,$
FIELDNAME=FYRTHRESH  ,FYRT       ,A2 ,     ,$
SEGNAME=RECID
FIELDNAME=SEGNAME    ,SEGMENT    ,A28 ,     ,$
FIELDNAME=SEGTYPE    ,SEGTYPE    ,A4 ,     ,$
FIELDNAME=SEGSIZE    ,SEGSIZE    ,I4 ,A4   ,$
FIELDNAME=PARENT     ,PARENT     ,A8 ,     ,$
FIELDNAME=CRKEY      ,VKEY       ,A66,     ,$
SEGNAME=FIELDID
FIELDNAME=FIELDNAME  ,FIELD      ,A66,     ,$
FIELDNAME=ALIAS      ,SYNONYM    ,A66,     ,$
FIELDNAME=FORMAT     ,USAGE      ,A8 ,     ,$
FIELDNAME=ACTUAL     ,ACTUAL     ,A8 ,     ,$
FIELDNAME=AUTHORITY  ,AUTHCODE   ,A8 ,     ,$
FIELDNAME=FIELDTYPE  ,INDEX      ,A8 ,     ,$
FIELDNAME=TITLE      ,TITLE      ,A64,     ,$
FIELDNAME=HELPMESSAGE,MESSAGE    ,A256,    ,$
FIELDNAME=MISSING    ,MISSING    ,A4,      ,$
FIELDNAME=ACCEPTS    ,ACCEPTABLE ,A255,    ,$
FIELDNAME=RESERVED   ,RESERVED   ,A44,     ,$
FIELDNAME=DEFCENT    ,DFC        ,A4,      ,$
FIELDNAME=YRTHRESH   ,YRT        ,A4,      ,$
SEGNAME=DEFREC
FIELDNAME=DEFINITION ,DESCRIPTION,A44,     ,$
SEGNAME=PASSREC,PARENT=FILEID
FIELDNAME=READ/WRITE ,RW         ,A32 ,     ,$
SEGNAME=CRSEG,PARENT=RECID
FIELDNAME=CRFILENAME,CRFILE,A8,,$
FIELDNAME=CRSEGNAME,CRSEGMENT,A8,,$
FIELDNAME=ENCRYPT,ENCRYPT,A4,,$
SEGNAME=ACCSEG,PARENT=DEFREC
FIELDNAME=DBA,DBA,A8,,$
FIELDNAME=DBAFILE,,A8,,$
FIELDNAME=USER,PASS,A8,,$
FIELDNAME=ACCESS,ACCESS,A8,,$
FIELDNAME=RESTRICT,RESTRICT,A8,,$
FIELDNAME=NAME,NAME,A66,,$
FIELDNAME=VALUE,VALUE,A80,,$

fex example

-SET &ECHO=ALL;

TABLE FILE SYSCAT_TABLES
PRINT
TABNAME
HEADING
"WEBFOCUS REPORT"
WHERE READLIMIT EQ 5
ON TABLE HOLD FORMAT ALPHA
END
-RUN

-*?FF HOLD
-*-RUN

-READ HOLD NOCLOSE &TABNAME.A128

-REPEAT END_LOOP1 WHILE &IORETURN EQ 0;

-SET &TABNAMEF = LOCASE(&TABNAME.LENGTH, &TABNAME, 'A&TABNAME.LENGTH') || '.mas';

FILEDEF COMASTER DISK basel/basel_&TABNAMEF
-RUN

TABLE FILE COMASTER
PRINT
FILENAME
FIELDNAME
ALIAS
FORMAT
ACTUAL
END
-RUN

-READ HOLD NOCLOSE &TABNAME.A128
-END_LOOP1

Unfortunately, neither suggestion did the trick. Perhaps I am not explaing it correctly. I have 1,000 .mas files. Without looking at each .mas, how do I find out the information in each .mas? I expect to see the following on the report:

FIELDNAME ALIAS FORMAT ACTUAL INDEX
BODYTYPE TYPE A12 A012
SEATS SEAT I3 A003
DEALER_COST DCOST D7 A007

There are 2 mfds' worth looking at, sysfiles.mas and syscolum.mas. These have a suffix of FMI, a special one for running internal processes. sysfiles searches the current path for .mas files.

Try:

TABLE FILE SYSFILES
PRINT FILENAME
ON TABLE HOLD
END
TABLE FILE SYSCOLUM
PRINT ALIAS COLTYPE LENGTH
BY TBNAME
BY NAME
WHERE TBNAME IN FILE HOLD;
END

You can of course play with the code to get exactly what you want.

Alan's suggestion is the best and correct one, it reads the DBMS system tables to provide information about the tables.

I was misdirected by you wanting to read the WebFOCUS Masters, which my example DOES and which gives you all you need EXCEPT whether or not the field is an index.

Alan, Francis

This is interesting info.
The Sysfiles.mas and syscolum.mas are they always available?
Where are these files located, or do I have to create them?

Frank

They should always be there, in ibi\srvnn\home\catalog, but are always accessible from the server, you don't need to do anything special to access them, though they are worth looking at.

Alan, aren't these DBMS tables and therefore dependent on each of the defined adapters? Do you think WebFOCUS automatically generates the Master and Access files for these?

On the set up I have at the moment, I don't have any adaptors defined, I am just set up for FOCUS dbs'.

I have used them for a while now, and just thought they were part of the base product, as they just access MFDs'.

I think I came across them in Update Assist or some such thing. I thought that FMI stood for something like Focus Master Internals, or similar.

I will take a look tomorrow, does anybody know if there is something like this to describe fexes?

So you can build a metadata report on what tables and fields are used etc... (rather complex, but you never know...)

Alan,

Thanks for pointing us to these Masters. Interesting stuff.

What I've figured out so far:

sysfiles gives you a list of all the files of a particular type in the app path. By default it displays .mas, but you can easily run a command to make it display focexecs, which would resolve the issue in Is it possible. As documented in the master comments, just add SQL FMI SET SYSFILES FOCEXEC before the TABLE FILE and you get focexecs!

syscolum gives you a list of columns. If the master is for a DBMS, it uses the acx file to connect to the database to read the dbms system table!

syskeys gives you a list of table keys. If the master is for a DBMS, it uses the acx file to determine the keys.

systable gives you a list of tables in the app path.

Gosh, this needs to be documented somewhere!

Thanks,
The code worked like a charm!!!

I'm joining in a little late but have you ever heard of CHECK FILE filename HOLD ?

Then you can use COMASTER mentioned above to read the resulting HOLD file.

Alan

CHECK FILE fn HOLD is fine for a one off, and has been mentioned on the FORUM before, but if you have hundreds of masters it will be unwieldy; this technique works very well, and simply.

I had 1,400 .mas files. Using the check file fn would have been a lengthy process.

I would like to use syscolum.mas to give me a list of columns by table, but I get no data. Do I need to FILEDEF something?

Sorry!

PRINT * gives me no data, but mentioning column names does return data.

TABLE FILE SYSCOLUM
PRINT
SEG.COLNO
BY TBNAME
WHERE READLIMIT EQ 100
WHERE TBNAME LIKE 'gg%'
END