Select Data from table based on File Input

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
Vishwanath G N
Member
Posts: 7
Joined: Sat Apr 06, 2013 9:11 am
Location: Chicago

Select Data from table based on File Input

Post by Vishwanath G N » Mon Apr 15, 2013 9:11 am

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.
Visi

NicC
Active Member
Posts: 650
Joined: Sun Jul 24, 2011 5:27 pm
Location: Down on the pig farm

Post by NicC » Mon Apr 15, 2013 12:53 pm

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
Regards
Nic

Vishwanath G N
Member
Posts: 7
Joined: Sat Apr 06, 2013 9:11 am
Location: Chicago

Post by Vishwanath G N » Tue Apr 16, 2013 5:02 am

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
Visi

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Re: Select Data from table based on File Input

Post by Anuj Dhawan » Tue Apr 16, 2013 7:27 pm

Vishwanath G N wrote:The above has to be accomadated for all file input.
What are you saying/asking here?
Regards,
Anuj

Vishwanath G N
Member
Posts: 7
Joined: Sat Apr 06, 2013 9:11 am
Location: Chicago

Post by Vishwanath G N » Wed Apr 17, 2013 9:28 am

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
Visi

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Thu Apr 18, 2013 6:28 pm

Your approach sounds fine to me.
Regards,
Anuj

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others