How To write the record dynamically

vishal_arora · New User Joined: 20 May 2005 Posts: 16

Hi All ,

My requirement is as follows .We have to create a db2 load utility which would read the master id from an input file and create a dynamic SQL queries and imbed these master id into the SQL Queries during run time.

For Ex :

We are having a input file having Master-Id 's . The input file is 80 byte FB with first 15 bytes as Master id as follows

GPX000087645678
HDI974748490948
PCI653356484874

Now at run time we want to read this master id from the input file and dynamically imbed these master id into SQL queries as follows :

For ex :

SELECT * FROM XYZ WHERE MASTER_ID IN ('GPX000087645678','HDI974748490948','PCI653356484874')
SELECT * FROM ABC WHERE WHERE MASTER_ID IN ('GPX000087645678','HDI974748490948','PCI653356484874')

LIKE THIS ...

Is there any way to to it thru Sort or can only be done thru COBOL pgm.

Thanks in advance

Arun Raj · Posted: Mon Sep 22, 2008 2:17 pm

Vishal,

Which sort product you are using? DFSORT or SYNCSORT? Just thought of asking since you posted a Syncsort solution in another topic.

Thanks,
Arun

guptae · Posted: Mon Sep 22, 2008 2:37 pm

Hello Vishal,

Please go throgh the following link

www.ibmmainframes.com/viewtopic.php?t=19045&highlight=sql+sort

vishal_arora · New User Joined: 20 May 2005 Posts: 16

Thanks Ekta for providing me the link ...

The problem and solution by Frank in that thread is excellent but in my case it needs some alteration which i am not able to make it write now.

Using that solution i can create SQL for a single table but so what if there is 100 different tables and i need the same concatenation .

P.S : Frank

Frank if you can provide me the changed sort wherein my input is FB/80 and output is also the same.
Just need a change to accompany the input to multiple tables.

For ex :

The input master_id be concatenated like :

SELECT * FROM ABC WHERE MASTER_ID IN ('CBX9834474744' , 'ILI9348474747' ) ;
SELECT * FROM XYZ WHERE MASTER_ID IN
('CBX9834474744' , 'ILI9348474747' ) ;
So on for 100 different tables.

Frank Yaeger · Posted: Mon Sep 22, 2008 8:53 pm

Vishal,

That other solution is not applicable to what you want to do. It's a different situation.

I suspect what you really want to do is more complicated than you've described, so if I give you a solution, you're going to come back and say, but I forgot to tell you that ...

Some of the questions I have are:

Do you always have three records in the input file? Or can you have a different number of records? If so, what is the maximum number of records?

Does each SELECT statement have to be on one line or can it be continued across lines?

You have SELECT * FROM xxx ...

where I assume only xxx will be different in the different SELECT statements. Do you want to hardcode the xxx values or get them from a table or what?

The more detail you can give about what the different variations, the better.

vishal_arora · New User Joined: 20 May 2005 Posts: 16

Frank ,

Here are your answers :

1) The input is dynamic it could be 3 , 10 or any numbers depending upon how much master-id to copy .

2) Select statement if on one line is better . for ex : Here is a sample of SQL query we generate .
SELECT * FROM LJVUA.ABRT WHERE MASTER_ID IN
(' 6802979ANU3',' 6904470ANU3',' 9605027ANV3',
' 7634201ANU3',' 6802746ANP3',' 101794A8NT3',
' 1020320ANH3',' 1253200ANN3',' 1003049ANA3' )
AND COMPANY_CODE = '001';

The master_id in the IN clauses are dynamically taken from the input file .

3) You are right on this the XXX will be different in SELECT statement . We can hardcode them or can get from a table either . There are about 135 tables for which SQL SELECT query need to be generated.

Hoping for a solution from ur side

Skolusu · Posted: Tue Sep 23, 2008 10:21 pm

vishal_arora,