Make a function nonvariant

A nonvariant UDR exhibits the following characteristics:

The function always returns the same result when invoked with the same arguments.
In the sysprocedures system catalog table entry for the UDR, the variant column contains the value f (for false).
The CREATE FUNCTION statement inserts a description of the strategy function in the sysprocedures system catalog table. By default, the variant column of the sysprocedures system catalog table contains a t (for true), even if that function invariably returns equivalent results. When you create a function with the NOT VARIANT routine modifier, the database server sets the sysprocedures variant indicator for that function too.

If you do write strategy or support functions, specify the NOT VARIANT routine modifier in the CREATE FUNCTION statement and ensure that the database server recognizes them as nonvariant.

Tip: Create the UDR as NOT VARIANT only if it really is not variant.

By contrast, a variant UDR exhibits the following characteristics:

In the sysprocedures system catalog table entry for the UDR, the variant column contains the value t (for true).
Because the CREATE FUNCTION statement for the function did not specify the NOT VARIANT routine modifier, the variant column contains the default value.
Each execution of a variant function with the same arguments can return a different result.

Important: Always specify the NOT VARIANT routine modifier in the CREATE function statement for an operator-class strategy function. If the variant column for a strategy function contains a t, the optimizer does not invoke the access method to scan the index keys. Instead, the database server performs a full table scan.

In the following example, the FileToCLOB() function returns variable results. Therefore, the optimizer examines every smart large object that the reports file references:

SELECT * FROM reports WHERE
   contains(abstract, ROW("IFX_CLOB",
   FileToCLOB("/data/clues/clue1.txt","server")
      ::lld_lob,NULL::LVARCHAR),