hi,
I have query which runs great when optimization level is changed to 3
but does not run fine with default optimization level of 5. since this
is a query in java code, i do not know how can i change optimization
level of this one sql. also, developers are not comfortable with this.
i am thinking of writing stored procedure in which i can execute sql
with lower optimization level and return resultset to java code.
is it possible to change optimization in stored procedure ? how can
this be accomplished ?
please share your thoughts.
regards,
db2admin
2  4255 
On Nov 11, 11:00*am, db2admin <jag...@gmail.c omwrote:
hi,
I have query which runs great when optimization level is changed to 3
but does not run fine with default optimization level of 5. since this
is a query in java code, i do not know how can i change optimization
level of this one sql. also, developers are not comfortable with this.
i am thinking of writing stored procedure in which i can execute sql
with lower optimization level and return resultset to java code.
is it possible to change optimization in stored procedure ? how can
this be accomplished ?
please share your thoughts.
regards,
db2admin
Assuming we're talking dynamic SQL here--and you're on LUW--the SET
CURRENT QUERY OPTIMIZATION statement is dynamically preparable, so you
could execute this statement (via EXECUTE IMMEDIATE) before you
execute the SQL.
--Jeff
db2admin wrote:
hi,
I have query which runs great when optimization level is changed to 3
but does not run fine with default optimization level of 5. since this
is a query in java code, i do not know how can i change optimization
level of this one sql. also, developers are not comfortable with this.
i am thinking of writing stored procedure in which i can execute sql
with lower optimization level and return resultset to java code.
is it possible to change optimization in stored procedure ? how can
this be accomplished ?
please share your thoughts.
Is this JDBC or SQLJ?
You can just SET the current query optimization level before executing a
dynamic (JDBC) statement and then resetting it afterwards.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Anjula |
last post by:
I have a stored procedure that takes 18 hours to complete. Sometimes
I get the below error message when I run the stored procedure.
'Server : Msg 9002, Level 17, State 6, Line 1 The log file for
database 'Customer' is full. Back up the transaction log for the
database to free up some log space.'.
I checked my log file and it is not full. And I don't know why I am
getting this message. Any help is appreciated.
|
by: Florian |
last post by:
I need to set multiple values for some SQL statements, for example
SET NUMERIC_ROUNDABORT OFF
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS ON
GO
in a .sql file, but would like to reset them to their previous settings
|
by: Vagif Abilov |
last post by:
Hello,
I have a question regarding stored procedure desing that provides the
optimal performance. Let's say we have a table Products that consists of
three columns: Name, Status, RegistrationTime. All columns are indexed and
users should be able to lookup data by any of the columns. We have two main
options to design stored procedures for data retrieval:
1. Design separate stored procedures for each search criteria:...
|
by: Bryan Parkoff |
last post by:
.....I try to reduce un-necessary temporal variables so it can be
optimized for best performance. I try to assign only one register
storage so two variables can access to only one register storage
before they are stored into memory location (variable storage).
.....Please take a look at two functions below. Byte variable and
Carry variable are global variables outside of functions.
.....Do you notice that there are two duplicated lines...
|
by: AC Slater |
last post by:
Whats the simplest way to change a single stored procedures query
optimization level?
In UDB8 that is.
/F
| | |
by: Br |
last post by:
I'm going to go into a fair bit of detail as I'm hoping my methods may
be of assistance to anyone else wanting to implement something similar
(or totally confusing:)
One of systems I've developed has three levels of security.
Admins - can see all records
Manager - can only see records based on an organisation structure held
in a table (simple tree structure)
Employee - can only see own records
|
by: kanda |
last post by:
Hello.
I am developing the application (VBA&ODBC, to be exact) which
periodically calls the stored procedures in the IBM DB2. A few of the
procedures require executing with isolation level RR ( ANSI
"SERIALIZABLE" ), not the default; default is CS (ANSI "Read
Committed")). The procedure language is SQL.
According to the documentation, I can adjust procedure *run*-time
isolation level by setting *compile*-time dataserver-wide option
|
by: upstart |
last post by:
Hi everyone…this is a tough one.
You guys have been such a help before, hopefully you can point me in the right direction now.
I have a Report I am working on that uses a stored procedure to pass along all off the parameters to the reporting generator (Crystal Reports) and I was currently trying to optimize it for my users, but am not sure of the best way to go about doing it. It took me forever to get it to work as it is now.
I...
|
by: metaperl |
last post by:
I would like to check the setting of this variable in our MS-SQL 2000
database. Also, is there info on what the default value of this
variable is?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
