The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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 1
If 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?

asked 04 Oct '12, 09:26

Reimer%20Pods's gravatar image

Reimer Pods
4.1k324278
accept rate: 12%

edited 04 Oct '12, 09:27


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.

permanent link

answered 04 Oct '12, 10:15

John%20Smirnios's gravatar image

John Smirnios
8.7k377106
accept rate: 40%

edited 04 Oct '12, 10:23

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
showing 2 of 6 show all flat view

I can't explain why this option is needed, but the docs do tell that it is needed, like some others:

The following database options must have the specified settings when a materialized view is created; otherwise, an error is returned. These database option values are also required for the view to be used by the optimizer:

ansinull=On
conversion_error=On
divide_by_zero_error=On
sort_collation=Internal
string_rtruncation=On

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.

permanent link

answered 04 Oct '12, 10:06

Volker%20Barth's gravatar image

Volker Barth
29.3k287438645
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×30
×15
×8

question asked: 04 Oct '12, 09:26

question was seen: 1,680 times

last updated: 05 Oct '12, 10:18