SET QUERY OPTIMIZATION in UDF

[Eugene]

I am trying to set query optimization class in a simple SQL UDF like
this:

CREATE FUNCTION udftest ( in_item_id INT )
SPECIFIC udftest
MODIFIES SQL DATA
RETURNS TABLE( location_id INT, period_id INT )
BEGIN ATOMIC

SET CURRENT QUERY OPTIMIZATION 1;

RETURN
SELECT location_id, period_id FROM activities WHERE item_id =
in_item_id;
END
@

where 'activities' is a very complex view built on multiple objects
and I found that the optimization class changed from the default 5 to
1 improves performance considerably.

But the compilation fails with the error:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "CURRENT QUERY OPTIMIZATION 1" was found
following "EGIN ATOMIC SET". Expected tokens may include:
"<set_clause_list>". LINE NUMBER=6. SQLSTATE=42601

It works fine without the SET CURRENT QUERY OPTIMIZATION 1 statement
though.
From the DB2 SQL reference it seems like SET special registers is
allowed in SQL routines as long as MODIFIES SQL DATA is specified.

Any idea why it does not compile?

Thanks,
-Eugene

[Rhino]

"Eugene" <eu****@profitlogic.com> wrote in message
news:95**************************@posting.google.c om...

I am trying to set query optimization class in a simple SQL UDF like
this:

CREATE FUNCTION udftest ( in_item_id INT )
SPECIFIC udftest
MODIFIES SQL DATA
RETURNS TABLE( location_id INT, period_id INT )
BEGIN ATOMIC

SET CURRENT QUERY OPTIMIZATION 1;

RETURN
SELECT location_id, period_id FROM activities WHERE item_id =
in_item_id;
END
@

where 'activities' is a very complex view built on multiple objects
and I found that the optimization class changed from the default 5 to
1 improves performance considerably.

But the compilation fails with the error:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "CURRENT QUERY OPTIMIZATION 1" was found
following "EGIN ATOMIC SET". Expected tokens may include:
"<set_clause_list>". LINE NUMBER=6. SQLSTATE=42601

It works fine without the SET CURRENT QUERY OPTIMIZATION 1 statement
though.
From the DB2 SQL reference it seems like SET special registers is
allowed in SQL routines as long as MODIFIES SQL DATA is specified.

Any idea why it does not compile?

You didn't state which version of DB2 you are using or what platform you are
running.

Assuming you are talking about DB2 V8.1 on Windows/Unix/Linux, the answer
can be found in the SQL Reference, Part 2:
ftp://ftp.software.ibm.com/ps/produc...r/db2s2e80.pdf.
If you look at the article about CREATE FUNCTION (SQL Scalar, Table or Row),
you'll see that your UDF may contain a "dynamic-compound-statement". If you
follow the link for that at the end of the article to page 123 of the
manual, you'll see that the only version of the SET statement supported by
UDFs is "SET variable statement". "SET CURRENT QUERY OPTIMIZATION" is
different from "SET variable". This is consistent with the error message you
are getting, which appears to be detecting the fact that your SET statement
doesn't match the syntax of the "SET variable" statement.

I'm not sure what to suggest as an alternative.

Rhino

[Knut Stolze]

Eugene wrote:

I am trying to set query optimization class in a simple SQL UDF like
this:

CREATE FUNCTION udftest ( in_item_id INT )
SPECIFIC udftest
MODIFIES SQL DATA
RETURNS TABLE( location_id INT, period_id INT )
BEGIN ATOMIC

SET CURRENT QUERY OPTIMIZATION 1;

RETURN
SELECT location_id, period_id FROM activities WHERE item_id =
in_item_id;
END
@

where 'activities' is a very complex view built on multiple objects
and I found that the optimization class changed from the default 5 to
1 improves performance considerably.

But the compilation fails with the error:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "CURRENT QUERY OPTIMIZATION 1" was found
following "EGIN ATOMIC SET". Expected tokens may include:
"<set_clause_list>". LINE NUMBER=6. SQLSTATE=42601

It works fine without the SET CURRENT QUERY OPTIMIZATION 1 statement
though.
From the DB2 SQL reference it seems like SET special registers is
allowed in SQL routines as long as MODIFIES SQL DATA is specified.

Any idea why it does not compile?

First, because as Rhino stated, the SET CURRENT QUERY OPTIMIZATION is not
supported inside the body of a UDF.

To understand that, it helps to know how UDFs are processed by DB2 LUW. If
you have a SQL statement that calls the UDF, the UDF is compiled into the
statement as is. Essentially, the UDF body is pasted into the SQL
statement and then the DB2 optimizer kicks in and optimizes the complete
SQL statement, including the body of the UDF.

Note that the same happens for triggers. They are compiled into the
statement and then the complete statement is optimized.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena