Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How To write the record dynamically

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
vishal_arora

New User


Joined: 20 May 2005
Posts: 16

PostPosted: Mon Sep 22, 2008 2:03 pm    Post subject: How To write the record dynamically
Reply with quote

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
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2284
Location: @my desk

PostPosted: Mon Sep 22, 2008 2:17 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Mon Sep 22, 2008 2:37 pm    Post subject:
Reply with quote

Hello Vishal,

Please go throgh the following link

http://www.ibmmainframes.com/viewtopic.php?t=19045&highlight=sql+sort
Back to top
View user's profile Send private message
vishal_arora

New User


Joined: 20 May 2005
Posts: 16

PostPosted: Mon Sep 22, 2008 3:43 pm    Post subject: Reply to: How To write the record dynamically
Reply with quote

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.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Sep 22, 2008 8:53 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
vishal_arora

New User


Joined: 20 May 2005
Posts: 16

PostPosted: Tue Sep 23, 2008 10:40 am    Post subject: Reply to: How To write the record dynamically
Reply with quote

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
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Sep 23, 2008 10:21 pm    Post subject: Reply to: How To write the record dynamically
Reply with quote

vishal_arora,

Quote:

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


You do realize that the max length of a single sql statement is 32768 bytes. Since we are generating 80 byte sql control cards then max you can have is around 400 lines. Once you cross that limit your sql will end up in error

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


Are you trying to combine 3 rows into a single record and then generate the IN statement?

Quote:
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.


How do you differentiate the table names? and where is the table name stored in the file? is there a field in the input file to pick?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Write out NODUPS but just from one file Jay Villaverde DFSORT/ICETOOL 8 Fri Jul 14, 2017 12:44 am
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts Need to write record of PS File in ex... Chandan1993 JCL & VSAM 1 Wed Jun 07, 2017 1:35 am
No new posts Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us