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,

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,

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.

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)?

It's a table that gets created in SQL2005 and yes, I did use the synonym generator.....that's what has me so baffled.

I'd open a case with IBI Tech Support.

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.

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.

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?

Todd, you really should open a case with IBI Tech Support - this is not normal.

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.

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.

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

Todd, please keep us up to date on the case you've opened.

Cheers.