fetch first in full select

[RR]

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);

DB2 compains about this.

Does anyone have a workaround for this?

TIA,
RR

[Jan M. Nelken]

RR wrote:

Hi,

It appears that DB2 has an arbitrary restriction on the use of "fetch first
N rows".
When asking question about any software product, it is expected that platform,
OS and product version are provided.
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);

DB2 compains about this.
When asking question about any siftware product it is also expected that any
error or warning messages are quoted.
Does anyone have a workaround for this?

Yes - I used DB2 UDB V8.2 as identified by:

D:\Working>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08020" with
level identifier "03010106".
Informational tokens are "DB2 v8.1.7.445", "s040812", "WR21342", and FixPak "7".
Product is installed at "D:\SQLLIB".

I used SAMPLE database - table ORG. First I created a copy of ORG:

D:\Working>db2 create table org2 like org
DB20000I The SQL command completed successfully.

Then I inserted first 3 rows from ORG ordered by location:

D:\Working>db2 insert into org2 (select * from org order by location fetch first
3 rows only)
DB20000I The SQL command completed successfully.

Finally I displayed contents of ORG2:

D:\Working>db2 select * from org2

DEPTNUMB DEPTNAME MANAGER DIVISION LOCATION
-------- -------------- ------- ---------- -------------
38 South Atlantic 30 Eastern Atlanta
15 New England 50 Eastern Boston
42 Great Lakes 100 Midwest Chicago

I don't see any problems - do you?

Jan M. Nelken

[Serge Rielau]

RR wrote:

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);

DB2 compains about this.

Does anyone have a workaround for this?

Assuming you are on DB2 for LUW, yes: upgrade to at least V8.1 FP2
If you can't you can use the ROW_NUMBER() OVER() OLAP expression to
number rows and filter out all but the first n.
If you can't do that either You'll have to open a cursor with FFnR and
INSERT the rows one by one. Or you could write a counter() function
using the SCRATCHPAD in an external language to mimic ROW_NUMBER()

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[RR]

"Jan M. Nelken" <Un**********@Invalid.Domain> wrote in message
news:vL********************@rogers.com...

When asking question about any software product, it is expected that platform, OS and product version are provided.

Thanks for your reply:
$db2level
DB21085I Instance "db2inst1" uses DB2 code release "SQL07020" with level
identifier "03010105" and informational tokens "DB2 v7.1.0.41", "s010426"
and
"U475381".

Platform is Linux, on Pentium.

And here is it failing:

$ db2 "create table test1 (colA int, colB int)"
DB20000I The SQL command completed successfully.

$ db2 "create table test like test1"
DB20000I The SQL command completed successfully.

$ db2 "insert into test2 (select * from test1 fetchfirst 3 rows only)"
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 "fetchfirst" was found following "(select *
from
test1". Expected tokens may include: ")". SQLSTATE=42601

------

So, I guess it's a version problem (not solvable in the short term).

thanks,
RR

[RR]

Sorry, typos in last version.

Here is the correct one:

$ db2 "insert into test (select * from test1 fetch first 3 rows only)"
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 "fetch" was found following "(select * from
test1". Expected tokens may include: ")". SQLSTATE=42601

[RR]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3m*************@individual.net...

Assuming you are on DB2 for LUW, yes: upgrade to at least V8.1 FP2
If you can't you can use the ROW_NUMBER() OVER() OLAP expression to
number rows and filter out all but the first n.
If you can't do that either You'll have to open a cursor with FFnR and
INSERT the rows one by one. Or you could write a counter() function
using the SCRATCHPAD in an external language to mimic ROW_NUMBER()

Thanks for your reply.

Can't upgrade and can't use OLAP.

I'll look into the counter function.

The whole point of this is to avoid shifting rows unnecessarily from DB2 to
an app and back again, so the cursor solution isn't useful.

thanks,
RR

[Ian]

RR wrote:

The whole point of this is to avoid shifting rows unnecessarily from DB2 to
an app and back again, so the cursor solution isn't useful.

It could be done in a stored procedure, though.

[Serge Rielau]

RR wrote:

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3m*************@individual.net...

Assuming you are on DB2 for LUW, yes: upgrade to at least V8.1 FP2
If you can't you can use the ROW_NUMBER() OVER() OLAP expression to
number rows and filter out all but the first n.
If you can't do that either You'll have to open a cursor with FFnR and
INSERT the rows one by one. Or you could write a counter() function
using the SCRATCHPAD in an external language to mimic ROW_NUMBER()

Thanks for your reply.

Can't upgrade and can't use OLAP.

I'll look into the counter function.

The whole point of this is to avoid shifting rows unnecessarily from DB2 to
an app and back again, so the cursor solution isn't useful.

thanks,
RR

Why can't you use OLAP?

db2 "insert into test2 SELECT <blahcolumnswithoutrn> FROM (select
row_number() over() as rn, test1.* from test1) AS S WHERE rn <=3

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[RR]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3m*************@individual.net...

Why can't you use OLAP?

Very good question.

When I tried it, it didn't work.

With your example, however, I've managed to get it to work. It must have
previously been a syntax error (it just kept complaining about the statement
without telling me what exactly was wrong).

Thanks for your help! Problem solved!

regards,
RR

[RR]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3m*************@individual.net...

db2 "insert into test2 SELECT <blahcolumnswithoutrn> FROM (select
row_number() over() as rn, test1.* from test1) AS S WHERE rn <=3

One question though....

Will this actually skip rows? The source table has 30 million rows, and I
only want to select 1 million.

This isn't going to be useful if DB2 reads all 30 million anyway.

tia,
RR

[Pierre Saint-Jacques]

We definitely must find something better to do than be here at this
hour?????
Regards, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de news:
3m*************@individual.net...

RR wrote:

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3m*************@individual.net...

Assuming you are on DB2 for LUW, yes: upgrade to at least V8.1 FP2
If you can't you can use the ROW_NUMBER() OVER() OLAP expression to
number rows and filter out all but the first n.
If you can't do that either You'll have to open a cursor with FFnR and
INSERT the rows one by one. Or you could write a counter() function
using the SCRATCHPAD in an external language to mimic ROW_NUMBER()

Thanks for your reply.

Can't upgrade and can't use OLAP.

I'll look into the counter function.

The whole point of this is to avoid shifting rows unnecessarily from DB2
to
an app and back again, so the cursor solution isn't useful.

thanks,
RR

Why can't you use OLAP?

db2 "insert into test2 SELECT <blahcolumnswithoutrn> FROM (select
row_number() over() as rn, test1.* from test1) AS S WHERE rn <=3

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

RR wrote:

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3m*************@individual.net...

db2 "insert into test2 SELECT <blahcolumnswithoutrn> FROM (select
row_number() over() as rn, test1.* from test1) AS S WHERE rn <=3

One question though....

Will this actually skip rows? The source table has 30 million rows, and I
only want to select 1 million.

This isn't going to be useful if DB2 reads all 30 million anyway.

I was affraid of that question. Truth is I don't know.
There are some limited optimizations to do "early out" processing with
row_number(). I don't know whether they apply here.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[RR]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3m*************@individual.net...

RR wrote:
I was affraid of that question. Truth is I don't know.
There are some limited optimizations to do "early out" processing with
row_number(). I don't know whether they apply here.

I did a benchmark...

Inserting all 23 million rows took 34 minutes.

Using the "where rn <= 10" to limit to 10 rows took 7 minutes.

So, it looks like my version of DB2 will do an early out!

thanks heaps for your help,
RR

[Serge Rielau]

RR wrote:

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3m*************@individual.net...

RR wrote:
I was affraid of that question. Truth is I don't know.
There are some limited optimizations to do "early out" processing with
row_number(). I don't know whether they apply here.

I did a benchmark...

Inserting all 23 million rows took 34 minutes.

Using the "where rn <= 10" to limit to 10 rows took 7 minutes.

So, it looks like my version of DB2 will do an early out!

Uhm... not necessarily. The diff could be in the INSERT processing and
not in the table scan.
Here is a way to compare:
INSERT INTO .. SELECT * FROM .. WHERE RAND() < CAST(<n> AS FLOAT)
/<totalnumrows>
If that takes significantly longer than 7 min then you got early out.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab