10 Million Values in a WHERE - IN clause of DB2 query

[Ranji]

Hello Experts,

I have a requirement to query DB2 for a 10 Million values in a where clause. For example, I have 10 million employee names in a dataset surrounded by Quotes and commas.  I need to get the employee number for all those employees from emp table with out going for a COBOL program.

I know this can be done by below JCL. But it is working only for 25,000 names in the input file, if it exceeds it is not working. And I need similar thing for 10 million records. Could someone help me to achieve this?

Data in the Input File:

MYFILE.EMPLOYEE.LIST
'Ranjith',
'Quasar',
'Mainframe360');

Sample SYSIN of the DB2 Unload JCL:

//SYSIN   DD *
        SELECT EMPLOYEE_NBR FROM EMPLOYEE TABLE
        WHERE EMPLOYEE_NAME IN (
//             DD  DSN=MYFILE.EMPLOYEE.LIST,DISP=SHR
         
--------
Please note that I dont want to go with a COBOL program as I will be getting N number of such requests and I cant keep coding a COBOL program all the times.

Thanks for your help in advance.

Ranjith

[Quasar Chunawala]

Hi Ranjith - 

One - //SYSIN DD * statement by itself doesn't have any limits. That's ruled out.

Two - DB2 for zOS has several system limits e.g. the maximum length of an SQL statement can't be larger than 2 MB. Think about it, your SQL would most likely exceed that limit. 

Here's the link : http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.ugref%2Fsrc%2Ftpc%2Fdb2z_limits.htm

Why don't you start off with two different SQL statements and then UNION them? That way, you should be able to query 25K + 25K rows. 

My suggestion - Split your input up using ICETOOL and then query(you're going to need a lot of SQL statements). 

On second thoughts, how about using a global temporary table(GTT) to store your input. 

Good day!

-Quasar.

--
You received this message because you are subscribed to the Google Groups "Mainframes 360" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mainframes36...@googlegroups.com.
To post to this group, send email to mainfr...@googlegroups.com.
Visit this group at http://groups.google.com/group/mainframes360.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Ranji]

Good Ideas Quasar.

On your first idea to split the input file, I already tried it and seems this is the costliest process because if we split the 10 million records into 25k each, it will be 400 input and 400 output files.

I already got an idea to use a GTT in the jcl, by declaring it and then inserting into it. But I dont know how to insert 10 million rows at one go.

Secondly, I thought of loading 10 million records to the GTT using load jcl and then use it in the next step to unload data by joining GTT with my db2 table. But I guess this will not work as the GTT is session specific and the data will be deleted after the completion of the first step.

Do you have any idea to declare, and load a GTT in one step and use it in the second step of a JCL?

[Ranji]

Can some one help!!!

To consolidate all of the below, I need to create a GTT in one program(or step of jcl) and use it in another program (next step of jcl)

Is there a way to do it?