473,242 Members | 1,739 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,242 software developers and data experts.

SET QUERY OPTIMIZATION in UDF

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
Nov 12 '05 #1
2 5092

"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
Nov 12 '05 #2
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
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: ensnare | last post by:
This query is running REAL slow ... like 1.2 secs ... any ideas on how I could optimize it? Perhaps my indexes are incorrect? $this->query = "SELECT m.username as username, e.title as title,...
5
by: AC Slater | last post by:
Whats the simplest way to change a single stored procedures query optimization level? In UDB8 that is. /F
1
by: Sean C. | last post by:
Helpful folks, I have recently migrated our test server, which runs Win NT 4, from V7.2 FP11 to V8.1.3. Just about everything works wondefully, except I am having major problems getting the...
12
by: WantedToBeDBA | last post by:
Hi all, db2 => create table emp(empno int not null primary key, \ db2 (cont.) => sex char(1) not null constraint s_check check \ db2 (cont.) => (sex in ('m','f')) \ db2 (cont.) => not enforced...
11
by: 73blazer | last post by:
We are migrating a customer from Version 7.1 FP3, to Version 8.2 (8.1 FP8). For the most part, things are faster, but there is one query that is much much slower, and it is a query that is used all...
6
by: UnixSlaxer | last post by:
Hello, Running a query for the first time on DB2 takes a fixed amount of time. But when query is executed for the second time, the amount of time is usually less since the query is (most...
4
by: Bernard Dhooghe | last post by:
To retrieve data from a query where multiple rows can be returned, a cursor can be used. Different programming interface exist for cursors: embedded SQL, CLI, SQL PL, SQLJ, JDBC. I we look at...
0
ADezii
by: ADezii | last post by:
One frequently asked question at TheScripts is "Should I use a Stored Query or an SQL Statement in those situations that require a Query (RecordSets, RecordSources, Append, Delete, Update Operations,...
1
by: Don Li | last post by:
Hi, Env: MS SQL Server 2000 DB Info (sorry no DDL nor sample data): tblA has 147249 rows -- clustered index on pk (one key of datatype(int)) and has two clumns, both are being used in joins;...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.