Solved

How can I write the best SQL query to process a WHERE IN list of 10,000 Strings

Posted on 2014-09-03
12
712 Views
Last Modified: 2014-09-03
The logic:
I am trying to query a DB2 Products table for a specific list of about 10,000 item #'s. The list of item's comes from another database. I am doing this in java, so the SQL query gets built dynamically, to connect to and query the DB2. But the WHERE IN list is too long and throws an error.

The error: [SQL0101] SQL statement too long or complex.

My query statement:

 SELECT m.MMITNO        
  FROM ItemMaster m
 WHERE m.mmitno IN ('AAZ4FTTBL+', 'AAZ4FTTBL+', 'AAZSHE99307', 'ABC100STOCKPOTA', 'ABC110001', 'ABC110002')

except add about 10,000 for item #'s in that WHERE list...

How do I query that many items in SQL?


Thank you!
0
Comment
Question by:m3developer2
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 40302053
Hi M3,

That's quite a filter that you're trying to build...

The limit on the query length has nothing to do with the amount of work that the DBMS will do.  It's a matter of practicality.

The IN predicate is a shortcut way to write "WHERE VarA ='a' or 'b' or 'c' or 'd'....  When the query is parsed, it is converted into as many individual tests as necessary to test for all of the possible acceptable values.  The filter will loop through the list searching for a match for every possible value that was not excluded by other filters.

If you can write the values to another file, even a temporary one, and join the this table to the results that you're selecting, you could see an improvement.  Especially if you can index the table.

Is there a connection between your DB2 system and the database with the list of values?  That would most certainly provide an easy an efficient solution.


Kent
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40302054
Can you load your 10,000 items into a temp table (whatever the DB2 equivalent of that is) and then

SELECT m.MMITNO        
  FROM ItemMaster m
 WHERE m.mmitno IN (select itemno from temp_table)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40302058
load your values into a temporary table

 SELECT m.MMITNO        
  FROM ItemMaster m
 WHERE m.mmitno IN (select your_column from your_temp_table);


your temp table might look something like this...
note, you will only create this table once, not once per user or per session, just once period.

CREATE GLOBAL TEMPORARY TABLE your_temp_table
   ( your_column varchar(10))
ON COMMIT PRESERVE ROWS
NOT LOGGED ON ROLLBACK DELETE ROWS;
0
 

Author Comment

by:m3developer2
ID: 40302171
Kent,
The two databases are on the same network, although different subnets (which shouldn't matter). The source db is a mySQL database, and the DB2 is an iSeries v6R1 for i. I understand the goal of uploading to a temp table. I have done it manually using iNavigator where I put the .csv file on the iSeries filesystem to do the bulk upload. But via jdbc, and a java SQL statement running on a seperate server, could I upload a csv thru the SQL code? Or am I way off?
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 40302213
Hi M3,

Being on different subnets is fine.  Moving the data from one database to the other when there is no logical connection is the challenge.  (But you know that already....)

Oh.  And not too many shops use MySQL and DB2 on iSeries!  :)  You might find this to be an interesting read:

  http://www.redbooks.ibm.com/redbooks/pdfs/sg247705.pdf

One solution is to query the MySQL database and generate INSERT statements to populate a table on the DB2 side.

SELECT 'INSERT INTO db2db.db2table (column_name) values (''' || mysqlcolumn || ''');''''
FROM mysqltable
WHERE xxx

Open in new window


That will generate one line for every row selected that looks like this:

  INSERT INTO db2db.db2table (column_name) values ('row1data');

Then just create the table on the DB2 side and run that.  And you can index it!

I'm sure one of the iSeries gurus would do it differently, but that's not my specialty so I'm resorting to something that I know will work for you.


Kent
0
 

Author Comment

by:m3developer2
ID: 40302270
Kent,
That is straight forward enough ! Thank you!

So, to clarify, our mySQL database is what serves our ecommerce platform. So, I'm building a product syncing tool to keep products synced up. It resides on a seperate Linux server elsewhere, but THANKS for that article. Fascinating!

On a further stretch, is there a SQL bulk upload statement for the i, that I could 'dump' the mysql table into a .csv file and read and upload it to the 'i' table?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:m3developer2
ID: 40302733
I've requested that this question be closed as follows:

Accepted answer: 0 points for m3developer2's comment #a40302270

for the following reason:

Solution was straight forward along with other suggestions too...
0
 

Author Comment

by:m3developer2
ID: 40302281
Body
0
 
LVL 18

Expert Comment

by:daveslash
ID: 40302297
I think Kent definitely helped m3developer2 develop his solution, so m3developer2 should give Kent the points. It's only fair.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40302349
Hi Dave,

I don't know the iSeries well enough to know if it can read and load an entire csv file.  I'm sure that it can, but I don't know the tools.

On the other platforms,

  IMPORT FROM filename OF DEL INSERT INTO mytable (column_name);

but that's probably no help here.
0
 
LVL 18

Expert Comment

by:daveslash
ID: 40302467
The "Copy From Import File" command (CpyFrmImpF) will most assuredly load a CSV-file (located in an IFS folder) into a database table.

e.g.
CPYFRMIMPF FROMSTMF('/MyFolder/CONDETL.TXT')
       TOFILE(mylib/condetl) RCDDLM(*CRLF)

Open in new window


HTH,
DaveSlash
0
 

Author Closing Comment

by:m3developer2
ID: 40302734
I initially hit the wrong link to award points. THis format has changed! Anyway, thank you Kent for the answer that worked for this scenario! Thank you everyone else for replying as well!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL query 4 47
Difference in number of minutes between 2 timestamps 16 39
SQL 2012 R2 Express report problem 2 83
How can I exclude some wording in a like statement? 39 66
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now