Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Master File Descriptions

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Master File Descriptions
 Login/Join
 
Member
posted
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,
 
Posts: 8 | Registered: March 30, 2007Report This Post
Virtuoso
posted Hide Post
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.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
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


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
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
 
Posts: 8 | Registered: March 30, 2007Report This Post
Virtuoso
posted Hide Post
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.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
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.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Expert
posted Hide Post
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?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
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...)




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
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!


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
Thanks,
The code worked like a charm!!!
 
Posts: 8 | Registered: March 30, 2007Report This Post
Gold member
posted Hide Post
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.


WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
 
Posts: 65 | Location: Chicago, IL | Registered: July 26, 2007Report This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Member
posted Hide Post
I had 1,400 .mas files. Using the check file fn would have been a lengthy process.
 
Posts: 8 | Registered: March 30, 2007Report This Post
Expert
posted Hide Post
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?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
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


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Master File Descriptions

Copyright © 1996-2020 Information Builders