System scenario: Linux DB2 Workgroup Server V7 FixPack 13.
I have a java application (JDBC v2 driver) that reads into memory a
considerable amount of data (100k-300k rows), summarizes
the results and then deletes data from the DB. This is done hourly.
According to my developer, the application opens a cursor and (fetch
next) until the end. I have created an event monitor and the
SQL sent to the DB is not poorly written (it uses the table PK).
The time required to read data from the DB is considerably higher than
deleting it. I was wondering if there is any configuration
within JDBC and/or db2 parameters to better handle prefetching, and
decrease my DB read time ?
Thanks in advance,
Michel
2  2032 
Have you looked at an EXPLAIN PLAN of the statement?
Personally, i usually keep away from CURSORs (unless i am passing
information to a program), with the assumption that CURSORs are much
slower.
B.
It's not surprising that the retrievals take longer than the delete.
Retrieval requires formatting data for presentation to the application
and handling all of the "service me" calls (fetch) that the application
issues.
Try writing a complex SQL statement that performs the "summarizat ion" in
the database manager. Retrieving only the summary results should be much
faster than fetching the source data and summarizing it in your program.
Phil Sherman
Michel Esber wrote: System scenario: Linux DB2 Workgroup Server V7 FixPack 13.
I have a java application (JDBC v2 driver) that reads into memory a
considerable amount of data (100k-300k rows), summarizes
the results and then deletes data from the DB. This is done hourly.
According to my developer, the application opens a cursor and (fetch
next) until the end. I have created an event monitor and the
SQL sent to the DB is not poorly written (it uses the table PK).
The time required to read data from the DB is considerably higher than
deleting it. I was wondering if there is any configuration
within JDBC and/or db2 parameters to better handle prefetching, and
decrease my DB read time ?
Thanks in advance,
Michel This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Evan Smith |
last post by:
During a routine performance check using an event monitor, I
discovered a class of query whose performance has me baffled.
The monitor captured:
SELECT * FROM EWM_CASE fetch first 1 rows only
It took 14 seconds of CPU time to execute. After looking up the
documentation on the FETCH FIRST notation I find "Limiting the result
table to the first integer rows can improve performance. The database
|
by: RR |
last post by:
Hi,
It appears that DB2 has an arbitrary restriction on the use of "fetch first
N rows".
I want to insert into a table from a large table query, but only insert the
first N rows:
insert into target (select colA,colB from source where conditions fetch
first 1000 rows only);
|
by: becoolmun |
last post by:
It seems with V7 of DB2 I can now use Fetch First 1 Rows Only in a
Select stmt, whic is great, because I don't have to use a cursor.
Unfortunately, it doesn't allow ORDER BY in the same Select and the
order is important because I need to pick the next supplier in a
sequence that can be changed by the user. Interestingly enough, I can
execute the Select with Order By and Fetch First 1 Rows Only in SPUFI,
with no problem.
Is there some...
|
by: alan_sec |
last post by:
I' m sending query to DB2 7.2 (installed on Win 2000) using JDBC driver
COM.ibm.db2.jdbc.net.DB2Driver like this:
String query = "SELECT dercert from vacert "
+ "WHERE vrijediod=(select max(vrijediod) from vacert where
vrijediod<?)";
java.sql.Timestamp timeStamp = new
java.sql.Timestamp(System.currentTimeMillis());
|
by: Bernd Hohmann |
last post by:
Is there any way to create a dynamic row limit like "fetch first ? rows
only" using the JDBC driver and a PreparedStatement?
Bernd
--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin
| | |
by: Acupuncture |
last post by:
Hi,
I am developing a JDBC application and I encountered this problem (DB2
for ISeries).
I want to do a select for update and also use the fetch first rows clause.
This is my sql statement:
SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte FROM
USERS WHERE UsrNbr = ? FETCH FIRST 1 ROWS ONLY FOR UPDATE
This runs fine (the cursor gets this name 'P00022'), but when I execute the
|
by: db2admin |
last post by:
hi,
For the following SQL and plan
==============================================================
SELECT Q1.PLCY_KY, Q1.PLCY_TRM_EFCTV_DT, Q1.ACNTG_BGN_DT,
CASE
WHEN ((Q1.PLCY_SRC_CD = '02') AND ((DAYS(Q1.PLCY_TRM_EXPRTN_DT) -
DAYS(Q1.PLCY_TRM_EFCTV_DT)) <= 180))
THEN 'Y'
|
by: scoonie999 |
last post by:
I'm having a problem that I can't seem to find any solution for online.
I'm using a cursor in a cobol program to fetch some data. I know for a
fact that the select should return 2 rows. The fetch works as I would
expect it to on the 2 rows, but then when I do my next fetch, the
program ends abnormally.
My next step is to check for sqlcode 100, as I would expect it to be,
but my program never even gets to this step.
|
by: Frank Swarbrick |
last post by:
Is there a way to do a multi-row fetch in to a COBOL table with DB2/LUW?
Apparently the following is supported in z/OS, but not LUW (or at least I
couldn't get it to work):
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
BEGIN DECLARE SECTION
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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: 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: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
