Page 1 of 1

Select Data from table based on File Input

Posted: Mon Apr 15, 2013 9:11 am
by Vishwanath G N
Hi All,

I have got a requirement where i need to read values from the file and select data from db2 table based on this.

I can do it through a simple cobol-db2 program but i wanted to acheive this through any of db2 Utilty/JCL.

Example :

Sample File

Col1 Col2
AA BBB
CC DDD


I should select data from the table for this file input like

Select * from Table where col1= AA and Col2=BBB with ur;

The above has to be accomadated for all file input.

Any help here is greatly appreciated .Thanks.

Posted: Mon Apr 15, 2013 12:53 pm
by NicC
Well, you cannot do it using JCL as JCL does not process data from SQL or anywhere else. And, if this is production then do not use SELECT * always use SELECT coln1..colnn

Posted: Tue Apr 16, 2013 5:02 am
by Vishwanath G N
Thanks a lot Nicc.Sure I would follow and select only the columns required else it would be a overhead for DB2.

At this point then i will stick on to the below method
-->Create SQL first through sort
-->Execute this SQL

Re: Select Data from table based on File Input

Posted: Tue Apr 16, 2013 7:27 pm
by Anuj Dhawan
Vishwanath G N wrote:The above has to be accomadated for all file input.
What are you saying/asking here?

Posted: Wed Apr 17, 2013 9:28 am
by Vishwanath G N
My question was ,

Is there any db2 utility which would read a input file(Control card or Sysrec),fetch the records for all these input values and write the same onto the file.

Currently I am doing the below steps and wanted to know if something better can be done

Step1:

Create a header file with below details
GLOBAL
LOCK NO
DB2 FORCE;
UNLOAD TABLESPACE

SELECT COL4 INTO COL01 CHAR
FROM TABLENAME
WHERE COL1 IN (

Step 2:

A flat file having all Input values that has to be passed to the step1 In clause

Step 3:
Trailer record detail

)
WITH UR;
OUTDDN SYSREC01
LOADDDN SYSCTL01
FORMAT DSNTIAUL STRICT
OPTIONS
NULLID NO
PIC('P',LEAD,',','00.0')

I concatenate all step 1 to 3 to create the final sql and then execute the same

Posted: Thu Apr 18, 2013 6:28 pm
by Anuj Dhawan
Your approach sounds fine to me.