By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

SQL Statement length exceeded

P: n/a
Hi,

I'm relatively new to DB2. I'm running v 7.2 on Windows XP Pro. I have
a SQL statement which is very long (almost 90,000 characters). I have a
couple of questions:

1. What is the limit for SQL statments? And if so, is this a
non-configurable limit?
2. My SQL statement is a bunch of unions and I have to check to make
sure the the items selected from the table are in the list of given
barcodes. I finally want all the results to be in 1 ResultSet - how can
I solve this problem? As much detail as possible would be appreciated:

SELECT *approximately 244 columns* from ST1_r2g_SLGP2STAT,
ST1_r2g_SLGP3STAT WHERE SL2_SlideBARCODE=SL3_SlideBARCODE AND
ST1_r2g_SLGP2STAT.SL2_SlideBARCODE IN ( 'L1032101', 'L1032102',
*another 11,000 or so barcodes*)
UNION
SELECT *approximately 244 columns* from SF1_r2g_SLGP2STAT,
SF1_r2g_SLGP3STAT WHERE SL2_SlideBARCODE=SL3_SlideBARCODE AND
SF1_r2g_SLGP2STAT.SL2_SlideBARCODE IN ( '20101101', '20101102',
*another 5000 or so barcodes*)

Thanks in advance!

Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Andy S. wrote:
Hi,

I'm relatively new to DB2. I'm running v 7.2 on Windows XP Pro. I have
a SQL statement which is very long (almost 90,000 characters). I have a
couple of questions:

1. What is the limit for SQL statments? And if so, is this a
non-configurable limit?
2. My SQL statement is a bunch of unions and I have to check to make
sure the the items selected from the table are in the list of given
barcodes. I finally want all the results to be in 1 ResultSet - how can
I solve this problem? As much detail as possible would be appreciated:

SELECT *approximately 244 columns* from ST1_r2g_SLGP2STAT,
ST1_r2g_SLGP3STAT WHERE SL2_SlideBARCODE=SL3_SlideBARCODE AND
ST1_r2g_SLGP2STAT.SL2_SlideBARCODE IN ( 'L1032101', 'L1032102',
*another 11,000 or so barcodes*)
UNION
SELECT *approximately 244 columns* from SF1_r2g_SLGP2STAT,
SF1_r2g_SLGP3STAT WHERE SL2_SlideBARCODE=SL3_SlideBARCODE AND
SF1_r2g_SLGP2STAT.SL2_SlideBARCODE IN ( '20101101', '20101102',
*another 5000 or so barcodes*)

Thanks in advance!


1.
DB2 UDB V7 is out of support since September, 2004. Please upgrade to V8 -
current version is V8.2; current fixpack level is FP2 for V8.2 a.k.a. FixPack 9
and retry.

2.
Search product online documentation - V7 is available at:
http://publib.boulder.ibm.com/infoce...7luw/index.jsp
curent V8 is available at:
http://publib.boulder.ibm.com/infoce...help/index.jsp

You may want to search for "SQL Limits".

Jan M.Nelken
Nov 12 '05 #2

P: n/a
We cannot afford to upgrade - is there another solution or workaround?

Thanks for the links.

Nov 12 '05 #3

P: n/a
Andy S. wrote:
Hi,

I'm relatively new to DB2. I'm running v 7.2 on Windows XP Pro. I have
a SQL statement which is very long (almost 90,000 characters). I have a
couple of questions:

1. What is the limit for SQL statments? And if so, is this a
non-configurable limit?
2. My SQL statement is a bunch of unions and I have to check to make
sure the the items selected from the table are in the list of given
barcodes. I finally want all the results to be in 1 ResultSet - how can
I solve this problem? As much detail as possible would be appreciated:

SELECT *approximately 244 columns* from ST1_r2g_SLGP2STAT,
ST1_r2g_SLGP3STAT WHERE SL2_SlideBARCODE=SL3_SlideBARCODE AND
ST1_r2g_SLGP2STAT.SL2_SlideBARCODE IN ( 'L1032101', 'L1032102',
*another 11,000 or so barcodes*)
UNION
SELECT *approximately 244 columns* from SF1_r2g_SLGP2STAT,
SF1_r2g_SLGP3STAT WHERE SL2_SlideBARCODE=SL3_SlideBARCODE AND
SF1_r2g_SLGP2STAT.SL2_SlideBARCODE IN ( '20101101', '20101102',
*another 5000 or so barcodes*)

Thanks in advance!

The way I would shorten this is to put the two sets of bar codes into
tables and change the IN clauses to (SELECT BARCODE FROM TABLE{1,2}).
Problem solved. Another approach, which I can only suggest since the
samples of 2 from 11,000 and 5,000 respectively is too small, is range
testing with BETWEEN and OR, or pattern matching with % and OR. I'd need
to see a much large sample to make this suggestion though.
Nov 12 '05 #4

P: n/a
Thanks a lot for your help!

So, just to clarify, you're suggesting that I put the set of barcodes
in separate tables so that for the above SQL, I'd have 2 tables with
the desired barcodes? Then, the resulting query would look something
like:

SELECT *approximately 244 columns* from ST1_r2g_SLGP2STAT,
ST1_r2g_SLGP3STAT WHERE SL2_SlideBARCODE=SL3_SlideBARCODE AND
ST1_r2g_SLGP2STAT.SL2_SlideBARCODE IN ( SELECT BARCODE from TABLE_1)
UNION
SELECT *approximately 244 columns* from SF1_r2g_SLGP2STAT,
SF1_r2g_SLGP3STAT WHERE SL2_SlideBARCODE=SL3_SlideBARCODE AND
SF1_r2g_SLGP2STAT.SL2_SlideBARCODE IN ( SELECT BARCODE from TABLE_2)

The barcodes are too varied to use pattern matching but thanks for the
suggestions!

Nov 12 '05 #5

P: n/a
Andy S. wrote:
Thanks a lot for your help!

So, just to clarify, you're suggesting that I put the set of barcodes
in separate tables so that for the above SQL, I'd have 2 tables with
the desired barcodes? Then, the resulting query would look something
like:

SELECT *approximately 244 columns* from ST1_r2g_SLGP2STAT,
ST1_r2g_SLGP3STAT WHERE SL2_SlideBARCODE=SL3_SlideBARCODE AND
ST1_r2g_SLGP2STAT.SL2_SlideBARCODE IN ( SELECT BARCODE from TABLE_1)
UNION
SELECT *approximately 244 columns* from SF1_r2g_SLGP2STAT,
SF1_r2g_SLGP3STAT WHERE SL2_SlideBARCODE=SL3_SlideBARCODE AND
SF1_r2g_SLGP2STAT.SL2_SlideBARCODE IN ( SELECT BARCODE from TABLE_2)

Correct.
In V7 the limit is 32k If these codes are fixed, you may create a view
per union all branch. Then union all the views. Not ethat you will need
to crank up the statement heap size, likeley also package cache.
There is a known APAR when a total of 32k constants are referenced in a
statement. This APAR is not available in V7, so you'll be limited to
some 8 UNION ALL branches with 5000 constants each.

In V8.1 The limit is 64k. In V8.2 the limit is 2MB.

I recommend you go with the side table approach.

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

P: n/a
Serge Rielau wrote:
In V8.2 the limit is 2MB.


DB2 has come a long way since the days of 3270... I can't imagine a client/server app firing that kind of SQL over WAN links as part of its normal operation... wow.

--
Michael Lueck
Lueck Data Systems
http://www.lueckdatasystems.com/

Remove the upper case letters NOSPAM to contact me directly.
Nov 12 '05 #7

P: n/a
Michael Lueck wrote:
Serge Rielau wrote:
In V8.2 the limit is 2MB.

DB2 has come a long way since the days of 3270... I can't imagine a
client/server app firing that kind of SQL over WAN links as part of its
normal operation... wow.

The in-lists as described in the original posts are classic.
Also stored procedures. Until V8.2 the 32k/64k limit was a major obstacl
migrating Sybase/SQL Server ISVs which are often procedure driven.
Mucho spaghetti...

Cheers
Serge

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

P: n/a
Andy S. wrote:
Thanks a lot for your help!

So, just to clarify, you're suggesting that I put the set of barcodes
in separate tables so that for the above SQL, I'd have 2 tables with
the desired barcodes? Then, the resulting query would look something
like:

SELECT *approximately 244 columns* from ST1_r2g_SLGP2STAT,
ST1_r2g_SLGP3STAT WHERE SL2_SlideBARCODE=SL3_SlideBARCODE AND
ST1_r2g_SLGP2STAT.SL2_SlideBARCODE IN ( SELECT BARCODE from TABLE_1)
UNION
SELECT *approximately 244 columns* from
SF1_r2g_SLGP3STAT WHERE SL2_SlideBARCODE=SL3_SlideBARCODE AND
SF1_r2g_SLGP2STAT.SL2_SlideBARCODE IN ( SELECT BARCODE from TABLE_2)

The barcodes are too varied to use pattern matching but thanks for the
suggestions!

Exactly.

Another possibility, depending on the number of values in
SL2_SlideBARCODE would be to put all the UNwanted values in TABLE_1 and
TABLE_2 then change IN to NOT IN. Presumably, since SL2_SlideBARCODE is
in your database, you know the set of possible values.

Another possibility would be to add a column or two to ST1_r2g_SLGP2STAT
(and SF1_r2g_SLGP2STAT) like INCLUDE_IN_SELECT CHAR(1) with 'Y' meaning
include the given barcode in the select. The the IN clause is replaced
by INCLUDE_IN_SELECT='Y'. This last suggestion gives the best
performance but complicates the maintenance of the base tables.
Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.