 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / July 2005SQL Statement length exceeded
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! > Hi, > [quoted text clipped - 20 lines] > > 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/infocenter/db2v7luw/index.jsp curent V8 is available at: http://publib.boulder.ibm.com/infocenter/db2help/index.jsp You may want to search for "SQL Limits". Jan M.Nelken We cannot afford to upgrade - is there another solution or workaround? Thanks for the links. > Hi, > [quoted text clipped - 20 lines] > > 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. 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! > Thanks a lot for your help! > [quoted text clipped - 10 lines] > 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  SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab > 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. SignatureMichael Lueck Lueck Data Systems http://www.lueckdatasystems.com/ Remove the upper case letters NOSPAM to contact me directly. >> 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 SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab > Thanks a lot for your help! > [quoted text clipped - 13 lines] > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.