When Should You Review IQ Options and Their Values?

I’ve been asked this question more and more by customers: “Mark, do I need to review the IQ options?”

The answer is “Absolutely!”.

There are a core set of options that are used internally by engineering to set SAP IQ behavior. When opening cases with SAP Support you may be asked to use some of these options as a workaround for an issue. It is imperative that these options be reviewed with every version change to SAP IQ.

These options are controlled by SAP IQ engineering and are used to implement various code execution paths or behavior changes in IQ.  The behavior of these options can change from release to release.  Now, most of them don’t but do you want to risk performance issues because you didn’t check?

And I don’t mean check them with every major or minor version.  Check them with every version change you make to your SAP IQ system.

Not checking these options can have a drastic effect on performance.  For instance, one query that was running in 1hr 15min, had four options set and not changed since IQ 15.4 was released.  These options changed behavior over the past few years and consequently, query performance was impacted.  When the options were set back to default, the query now ran over 35% faster, in just over 45 minutes.

The most critical options to watch are those that begin with these phrases:

• ddl_options
• mpx_options
• dqp_options
• core_options
• dml_options

While we’ve covered the internal options, this is also a good time to review the options that you’ve set as part of your normal setup.  More often than not, the current options that you’ve set will be fine. Take this time, though, to go back through and review them.

How do we reset an option to default without having to look through the manuals?  Quite easily, actually:

set option PUBLIC.OPTION_NAME=;

The “=;” tells IQ to reset the value to default.  Simply replace OPTION_NAME with the actual name of the option that you wish to reset for the PUBLIC group.  If you have options that are set to other groups of users, the syntax is the same.  Simply replace PUBLIC with the user or group name.