Re: Derived Index V6R1

Why that scalar function was not allowed [the sqlcode and preceding error(s)] was left unstated. That might be worthwhile to record in this message thread.?

However by further review of the documentation I believe the "Note" [no colon; I had searched "Note:"] suggesting that the system-supplied SQL scalar function TIMESTAMP_FORMAT is defined as NOT DETERMINISTIC per "Determinism: TIMESTAMP_FORMAT is a non-deterministic function." I infer that is what precludes use of that scalar function in the derived index, rather than the builtin function possibly being unsupported on the derived index due to an implementation as an effective UDF or similar [one term used is "system generated" function].

While the example coded forms of the invocation [using literal as format-string] are clearly deterministic, that the builtin is described to\by the database SQL as being non-deterministic would seem likely to preclude its use in a derived index. Presumably the use of that scalar function in a CHECK CONSTRAINT would fail with CPD439D RC4 and SQL0546, just like for the use of RAND() [another non-deterministic function], even when a literal seed were included like RAND(5) which would make the invocation deterministic regardless that the function itself is defined as non-deterministic.

Regards, Chuck

On 30-Aug-2011 10:03 , CRPence wrote:

On 30-Aug-2011 09:12 , Schutte, Michael D wrote:

FYI, TIMESTAMP_FORMAT cannot be used when creating a derived index.

CRPence on Tuesday, August 30, 2011 11:03 AM

On 30-Aug-2011 06:25 , Schutte, Michael D wrote:

Looking for a best solution for implementing and using derived
index. <<SNIP>>

The following expression(s) might be more succinct [¿and
functional?] alternatives. <<SNIP>>

I figured that might be the case :-( While left unstated, that was one
of the various reasons for my having included "¿and functional?".
Another reason would be the requirement that all of the numeric data
properly represent legitimate dates. Sorry that scalar function
TIMESTAMP_FORMAT was of no assist; I have only v5r3 available to
verify\test anything, and I did not recall if that was a limitation.

Some of the system-supplied SQL functions are effective UDFs, yet the
documentation seems lacking with regard to both any special "Note:" of
that, along with the associated restrictions given the special
implementation. I forget how those functions are described in
nomenclature, in either messages or the documentation. Those functions
with restrictions also seem not to be separated in any way from the
other builtins which do not suffer the same restrictions [e.g. also for
use in CHECK CONSTRAINT], as presented in the hierarchy of the doc link:

SQL reference > Built-in functions > Scalar functions
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscatsformat.htm