 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point [CASE-OPENED] General question on creating synonym from SQL table with indexes
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CASE-OPENED] General question on creating synonym from SQL table with indexes Read-Only TopicGo | Search | Notify | Tools |
| [CASE-OPENED] General question on creating synonym from SQL table with indexes | Login/Join |
| Member |
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 | ||
|
| Expert |
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 | |||
|
| Member |
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 | |||
|
| Expert |
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 | |||
|
| Member |
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 | |||
|
| Expert |
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 | |||
|
| Member |
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 | |||
|
| Expert |
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...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Member |
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 | |||
|
| Expert |
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 | |||
|
| Guru |
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. | |||
|
| Virtuoso |
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
| |||||||||
| ||||||||||
| Member |
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 | |||
|
| Expert |
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 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CASE-OPENED] General question on creating synonym from SQL table with indexes
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CASE-OPENED] General question on creating synonym from SQL table with indexesCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |