Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


robert fuschetto

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...