We have hundreds of synonyms we need to confirm have the correct FIELDNAMEs marked with SCD Type of Logical Key Field. We have a SQL database for our WebFocus repository. I've looked around a little, but I'm not sure what to look for.

What table(s) and column(s) of the WebFocus repository database can I query to find this information? I would like to get a result of TABLENAME, FILENAME, FIELDNAME, ALIAS, SCD TYPE. A bonus item would be FIELDTYPE (index, read-only).

Thanks.

This message has been edited. Last edited by: FP Mod Chuck,

The synonyms should be on the reporting server, under the APPS directory.

You should be able to use SYSTABLE/SYSCOLUM to get the info.

I think you may be looking in the wrong place if you mean "WebFOCUS Repository" (and not an external "SQL Repository" managed from the DMC or WebConsole).

That kind of information is stored in the Synonyms (Master and Access files) on the WebFOCUS Reporting server, or I'm guessing in your case the DataMigrator server. In Release 7.7.07 and 8.2 we've added some procedures to simply extracting this information, but you can still obtain it in 8.1.

Most of the attributes you mention are in two (virtual) system tables (syscolum and systable) and you can extract that information in a DataMigrator flow (or a FOCUS TABLE request). These tables are distributed in the home/catalog directory which you can expose from the Application Directories context menu choice filter.

Alternatively to create a data flow with one of those tables as a source you can right click on the left side of a new workflow and instead of selecting a table enter one of the following.
_edahome/catalog/syscolum
_edahome/catalog/systable

Now you can use these virtual tables as you would a real table although you can't join them.

From systable to get the actual database table name
NAME, REALNAME

From syscolum you could select
TBNAME, NAME, ALIAS, READONLY

Note that FIELDTYPE=I is almost only used for indexes on FOCUS files not RDBMS tables. You can get the key information however.

Finally the attribute "SCD Type" is only used by DataMigrator and only in flows with a Load type of Slowly Changing Dimensions. As it happens that while that attribute is stored in the Access File it's not exposed in the syscolum table. If that's something you do need please open a case with InfoResponse requesting it as a new feature.

Thanks for the feedback. To resolve my problem, I ended up using an entirely different approach. Our tables are modeled in ERStudio. I wrote a macro to read the model and re-create the synonyms. Our conventions allowed me to specify all of the pieces needed to create both the .mas and .acx files.

I'm glad you were able to work something else.

In the meantime we've added SCD_TYPE to SYSCOLUM and it will be available in the next release of the server.