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
689 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:m3developer2
Comment Utility
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
Comment Utility
Body
0
 
LVL 18

Expert Comment

by:daveslash
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 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

10 Experts available now in Live!

Get 1:1 Help Now