473,321 Members | 1,669 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,321 software developers and data experts.

jdbc: FETCH FIRST ? ROWS ONLY


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
Nov 12 '05 #1
5 9862
Bernd Hohmann wrote:

Is there any way to create a dynamic row limit like "fetch first ? rows
only" using the JDBC driver and a PreparedStatement?


The FETCH FIRST is a clause of a SQL statement, so you can simply add it to
your query that is executed through JDBC.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #2
I suppose, the question is whether it is possible to use a parameter
marker for the number of rows

Nov 12 '05 #3
juliane26 wrote:
I suppose, the question is whether it is possible to use a parameter
marker for the number of rows

No, here is how to do it:
SELECT c1 FROM (SELECT c1, ROW_NUMBER() OVER() AS rn FROM T) AS S WHERE
rn <= ?

this can be extended to e.g. ... OVER(ORDER BY c2)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4
Serge Rielau wrote:
No, here is how to do it:
SELECT c1 FROM (SELECT c1, ROW_NUMBER() OVER() AS rn FROM T) AS S WHERE
rn <= ?

this can be extended to e.g. ... OVER(ORDER BY c2)


Any idea about the performance in comparision to a simple "fetch first
10 rows only"?

If it makes a performance difference I prefer the static variant - I'm
not playing with toys here :)

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
Nov 12 '05 #5
Bernd Hohmann wrote:
Serge Rielau wrote:
No, here is how to do it:
SELECT c1 FROM (SELECT c1, ROW_NUMBER() OVER() AS rn FROM T) AS S
WHERE rn <= ?

this can be extended to e.g. ... OVER(ORDER BY c2)

Any idea about the performance in comparision to a simple "fetch first
10 rows only"?

If it makes a performance difference I prefer the static variant - I'm
not playing with toys here :)

Bernd

DB2 has "some" capability to recognise when the rn <= ? is false and
truncate. But I would not rely on it. Also I'm not 100% sure if you can
see that in the EXPLAIN. The static version is definitely preferred.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

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

Similar topics

0
by: David | last post by:
Does anybody have samples of how to use JDBC calls to work with a cursor on Oracle 8i? Can it even be done? I'd like to be able to do something like: String sql = "DECLARE CURSOR my_cursor...
1
by: Howie Goodell | last post by:
Hello -- I am trying to optimise a JDBC connection and an Oracle 9i database for reading millions of records at a time to a 1 Gig PC running Win2K or XP. Two questions: 1. Does anyone have...
1
by: bogachkov | last post by:
Hello Joe Over the past several years, I have found your responses to jdbc usage/driver related issues to be extremely helpful. I am sure that you're very busy so I will make my question as...
8
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...
13
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...
2
by: Michel Esber | last post by:
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...
3
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...
9
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: ...
4
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')...
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: 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...
1
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...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.