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     [CASE-OPENED] General question on creating synonym from SQL table with indexes

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE-OPENED] General question on creating synonym from SQL table with indexes
 Login/Join
 
Member
posted
When I create a synonym from a SQL table which has many indexes (let's say 15) however, the indexed fields are not the first 15 fields. When you look at the .mas in WF using the synonym editor, their are key markers next to the first 15 fields in the synonym regardless if they are indexed in the SQL table. Can anyone explain?

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


WF v7.6.8
Windows
Excel/PDF/HTML
 
Posts: 8 | Registered: November 02, 2006Report This Post
Expert
posted Hide Post
Without looking at one of my files, I'm pretty sure that the WebFOCUS synonym generator puts all the indexed columns at the top of the master, first the indexed columns, then the rest. The acx file will have the corresponding count of indexed columns. The order of the columns in the master does not have to correspond to the order in the RDBMS table.


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
Francis,

Thanks for the response, but that does not seem to be the case if I'm understanding you correctly. What's happening is that indexes are put onto the first 15 fields in the table (at least a key marker appears in synonym editor) regardless if they are indexed or not in the SQL table. For example if we have a table with fields A, B, and C in that order and indexes on Fields A and C. The synononym editor will show indexes on A and B or the first 2 fields in the table.


WF v7.6.8
Windows
Excel/PDF/HTML
 
Posts: 8 | Registered: November 02, 2006Report This Post
Expert
posted Hide Post
I have a DB2 table with 30 columns. Columns 1, 6, 7, 8 and 9 are indexed. The acx file states "KEYS=5, $". The mas file has the 5 indexed columns at the top, with the other, non-indexed columns right after: column 1 in the table is column 1 in the master, column 6 in the table is column 2 in the master, column 7 in the table is column 3 in the master, ..., column 2 in the table is column 6 in the master, etc.

What DBMS are you working with? Did you use the synonym generator in the WebFOCUS Server Console to create the meta-data (acx and mas)?


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
It's a table that gets created in SQL2005 and yes, I did use the synonym generator.....that's what has me so baffled.


WF v7.6.8
Windows
Excel/PDF/HTML
 
Posts: 8 | Registered: November 02, 2006Report This Post
Expert
posted Hide Post
I'd open a case with IBI Tech Support.


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
Francis,

Thanks for all your input. We do have a work around because like I said, it's a table we create in SQL2005 so we just changed the table around to have the first 15 fields be the indexed fields then all was fine. I just found it odd and never really noticed this before. Now I'm thinking we better check on others....it may be affecting performance and we haven't really realized it.


WF v7.6.8
Windows
Excel/PDF/HTML
 
Posts: 8 | Registered: November 02, 2006Report This Post
Expert
posted Hide Post
Make sure you don't get confused between Indexed fields and Key Fields.

The Keys should go to the top of the master be default.


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: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Member
posted Hide Post
The 15 fields in SQL are indexes (via the create index command). The .acx file specifies that there are 15 keys and synonym editor shows the keyed fields as the first 15 which are not necessarily the 15 indexed fields. Why doesn't the synonym generator recognize the correct 15 indexed fields and then move them to the top 15 fields of the synonym instead of just assuming the top 15?


WF v7.6.8
Windows
Excel/PDF/HTML
 
Posts: 8 | Registered: November 02, 2006Report This Post
Expert
posted Hide Post
Todd, you really should open a case with IBI Tech Support - this is not normal.


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
Guru
posted Hide Post
Todd, how many columns are in the primary key of the table? You can check this easily with ms sqlserver studio.

The first fields are PK fields.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Virtuoso
posted Hide Post
As Waz already said, do not confuse key fields with indexed fields.
The synonym editor will not show any indexed field. If you want indexed fields to show up in your master file, you will have to add the FIELDTYPE=I yourself.
The synonym generator will retrieve from the database which fields are in the primary key and will put these fields in the master file first, followed by the other fields in the table. The number of field in the primary key will be stored in the access file. That way the product knows how many fields to show with the key-symbol.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Member
posted Hide Post
I don't believe I am getting them confused rather maybe I'm not being clear. We have a table that gets created in SQL2005 with a clustered index which consists of 15 fields. There are no "keyed" fields in the SQL table when you look at the keys through management studio. The synonym generator creates the synonym with 15 keyed fields (in the .asx) but they are the first 15 fields in the table....not the 15 indexed fields (unless we create the SQL table and put make them the first 15 fields). That is the strange/weird situation I'm looking for an answer for. I will be logging a ticket


WF v7.6.8
Windows
Excel/PDF/HTML
 
Posts: 8 | Registered: November 02, 2006Report This Post
Expert
posted Hide Post
Todd, please keep us up to date on the case you've opened.

Cheers.


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     [CASE-OPENED] General question on creating synonym from SQL table with indexes

Copyright © 1996-2020 Information Builders