First, I set my screen to show the Table name and columns. H...

[robert fuschetto]

First, I set my screen to show the Table name and columns. How do I get it to show me the type for each column

 

image.png495623 27.5 KB

 

Second, if I added a primary key to this table on the sql server, must I do anything with the master file

[Debra Waybright]

Click on the middle icon at the top, the one to the left of the magnifying glass. This lets you choose which columns to display. Select Data type to show the data type.

Any indexes or keys created on the SQL server table I think show in the access (acx) file for the synonym.

[Martin Yergeau]

robertf:

 

Second, if I added a primary key to this table on the sql server, must I do anything with the master file

 

 

You will have to refresh the master file to have the key added to the acx file

From the Console, right click the master files name, select Metadata Managment then Refresh

[robert fuschetto]

If I refresh, do I lose DEFINES I created or types I altered on columns

[Martin Yergeau]

AFAIK, you will

One thing that you can do instead is to generates a new master files name adding either a prefix or sufix to the name.

Something such as MyMasterFileName_New

Copy the new acx content over the old one.

Then just update the master fields concerned by the new key. They must not have the MISSING=ON attribute.

[Toby Mills]

Hi Robert

At the risk of mean comments Maybe you dont need to update your Master at all.

The Masters/ACX files job is to generate SQL for you when youre running Focexecs. The addition of a key field is helpful for a Master if you want WebFOCUS to start warning you about multplicative effects, or inefficient joins But if your code is already written, and your just adding a keyfield (maybe for performance reasons or whatever), then really - you can just leave your Master alone.

In fact, you could even add a few new columns to your SQL Server table and never update your master and itll still work to run old reports. The only problem there is that the new fields will not be visible to WebFOCUS.

Unlike a Master file that reads a flat file, or VSAM or whatever, SQL masters dont really have to exactly match, byte for byte Theyre just things to help generate the correct SQL on the backend.

With that in mind, if you dont update your Master to indicate you now have a primary key, youll miss out on some warnings WebFOCUS might otherwise be able to give you that have to do with knowing keyfields are. Like if you use this Master file with client side tools, you WebFOCUS can take a better guess about JOIN columns and so forth.

But - if you are just running existing code, dont kill yourself about matching up the key field.

It certainly doesnt hurt to have your Master as accurate to the data as possible. And thatd probably a best practice. But - do you need the headache in this case Maybe not.

The most important part is that your SQL gets generated correctly and in an efficient way. As long as the execution plan looks okay, youll be fine without WF knowing what the key field(s) are.

Ive seen where adding the Key fields makes WebFOCUS start to throw JOIN not Optimized because Multiplicative Effect will Ensue (on a 3 table join for example that looks to WF like its a parent with 2 children) If you know your data, and you know theres no multiplicative effect with your JOIN, then you dont need WF to inefficiently generate 3 SELECT statements trying to protect you. Youd rather it just generated a single SELECT for all 3 tables which was likely your intention.

Just a thought. Feel free to disagree Just suggesting you might be able to save yourself some work.

[robert fuschetto]

toby.mills:

 

The Masters/ACX files job is to generate SQL for you when youre running Focexecs. The addition of a key field is helpful for a Master if you want WebFOCUS to start warning you about multplicative effects, or inefficient joins But if your code is already written, and your just adding a keyfield (maybe for performance reasons or whatever), then really - you can just leave your Master alone.

 

 

RE:

and your just adding a keyfield (maybe for performance reasons or whatever), then really - you can just leave your Master alone.

We added the key with hopes of boosting performance both inside and outside of WF. So I think I have the answer I was looking for,. Thanks.

[Toby Mills]

So whats really important is that your newly indexed field ends up in a WHERE statement. I got the impression the fieldname already existed for some reason.

If the field is brand new, then for sure you need to add it to your Master so you can make it the subject of a WHERE condition.

If the field was already in your Master, as long as you have a WHERE on the field, then itll run better because SQL Server knows about the index. It wont help WF run any faster for knowing its indexed.

Glad you you have it worked out!

[robert fuschetto]

Exactly what I was hoping to hear! THANKS!