|
I'm trying to execute the following statement in ISQL : CREATE MATERIALIZED VIEW VWBWERTELISTE AS SELECT WB_WERTEBEREICH, WB_TYP, WB_FORMAT, WL_RANG, WL_ANZEIGE, WL_WERT FROM WERTEBEREICH WB JOIN WERTELISTE WL ON WB.WB_ID = WL.WB_ID this error message pops up Could not execute statement. Cannot create a materialized view because option 'string_rtruncation' has an inappropriate setting SQLCODE=-1051, ODBC 3 State="HY000" Line 1, column 1If I set the option to 'On', create the mat.view and set the option back to the old value ('Off), everything is ok and works as supposed to. I couldn't find anything related in the documentation. It might be a bug, but it's at least an unexpected behavior. Any possible explanations? |
|
See http://dcx.sybase.com/index.html#1201/en/dbusage/workingwdb-s-3924160.html The results of a SELECT statement (or VIEW definition) and the errors reported by it depend on various user options. To use a materialized view, the options in effect at the time the materialized view is used must match the options in effect when it was materialized otherwise it is impossible to know whether the values in the materialized view are meaningful & correct relative to the current option settings. To address that issue, certain option settings are either mandated or stored with the materialized view definition itself. 3
The requirement for some "stored" settings (date_format etc) to match in value is easy to understand and accept. However, the "mandated" settings (string_rtruncation=On etc) are not so easy to understand... do you know why these settings are mandated?
(04 Oct '12, 16:57)
Breck Carter
Replies hidden
Yes, that's exactly what I'd like to know.
(05 Oct '12, 04:11)
Reimer Pods
If the question is "Why do they have a mandatory value instead of having to match in value?", then that's my question, too. In case those non-mandatory options do not match: Am I right that this prevents the mat. view from being used in optimization (i.e. "in the background") but still does allow it to query the mat. view directly?
(05 Oct '12, 04:31)
Volker Barth
In my case I could select from the mat. view although the option value ist different from the value at creation time. So I'd say that can be answered with "Yes".
(05 Oct '12, 04:56)
Reimer Pods
Replies hidden
That makes sense to me: If you select directly from the mat. view, then it's obviously your responsibility to know the particular circumstances. - If the optimizer has to evaluate on view matching, it must decide whether the view's result would semantically fit to the given query...
(05 Oct '12, 05:23)
Volker Barth
4
The person with the definitive answer is out of the office for a week or so. From talking with other query-processing engineers, it seems that the mandated options are essentially ones that we wish really didn't exist and they only exist today to mimic old behaviour for compatibility reasons. Settings other than the recommended settings are discouraged even outside the scope of materialized views. The mandated string_rtruncation setting, for example, is ANSI standard behaviour. Other options such as ansinull=off are, well, "evil" and may cause significant complexities in verifying correctness when using materialized views. I believe you can always reference a materialized view directly; however, it will only be substituted automatically by the optimizer if the current options match the mandated/stored options.
(05 Oct '12, 10:18)
John Smirnios
|
|
I can't explain why this option is needed, but the docs do tell that it is needed, like some others:
If you do change the setting back to "invalid" values, the view might not be used in optimization, and the system procedure sa_materialized_view_info might tell you why - cf. the "AvailForOptimization" column. |
