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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] Extracting SCD type from the SQL WF Repository database

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Extracting SCD type from the SQL WF Repository database
 Login/Join
 
Member
posted
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,


WebFOCUS 8.1M
Windows, All Outputs
 
Posts: 3 | Registered: November 19, 2014Report This Post
Expert
posted Hide Post
The synonyms should be on the reporting server, under the APPS directory.

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


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Guru
posted Hide Post
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.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Member
posted Hide Post
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.


WebFOCUS 8.1M
Windows, All Outputs
 
Posts: 3 | Registered: November 19, 2014Report This Post
Guru
posted Hide Post
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.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] Extracting SCD type from the SQL WF Repository database

Copyright © 1996-2020 Information Builders