If (Input File = Table) -> Write-to (Output file)

db2 sql interview questions

Moderator: Moderator Group

Post Reply
fredrick andrews
Posts: 15
Joined: Thu Feb 23, 2012 6:55 am

If (Input File = Table) -> Write-to (Output file)

Post by fredrick andrews » Sat Feb 25, 2012 8:29 am

Input File:


Db2 Table data:


Output file:


How will you do this with cursor?
Can we do this without cursor? If yes, how?

P.S: Not using the CODE tags for highlighting purposes...Thanks!

User avatar
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Sat Feb 25, 2012 9:51 am

1. in the future, you need to be a little more explicit with your explanations.
this one, though, seems somewhat easy to interpret.
i will, possibly wasting my time, interpret your cartoon.

db2 table has a char column, the first 3 char of which need to be compared to the 1st 3 char of input file.
if equal, write the contents (value) of the char column to a file.

essentially this is a two file match process.

to do it with a program, you will have to use a cursor, due to the potential of duplicate 1st 3 char in db2 column.

if the db2 table had few records/rows
you could use the input file as the 'needle ' file
and treat the a cursor (complete table) as the 'haystack' file

BUT if the db2 table had many rows,
i would dump the table
2 columns:
SELECT substr(col,1,3), col
creating a qsam file
and then use the 'needle' and 'haystack' logic to generate your output.
which would be the fastest and least resource intensive method.

'needle' and 'haystack' logic
open output file
open both input files
perform read 'needle'
perform read 'haystack'

1. if both end of file, end of program

2. compare the 'needle' to the 'haystack'

if equal, write column value, and then perform read next haystack, go to 1
if 'needle' is less than 'haystack', perform read next needle, go to 1
if 'needle' is greater than haystack', perform read next haystack, go to 1

for both reads,
if compare value equal to high-values, exit
if end of file, set compare field to high/values, exit
if not end of file, set create 'needle'/'haystack' equal to 1st 3 char of read value, exit

The above logic provides for finding all 'hits' in the db2 table
and ignoring potential duplicates of the input file.

The above is a gift.
next time, show what you have attempted, where you are stuck, and we will help.
we do not do it for you, we help you work thru your problems.

have a good weekend.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

Post Reply


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.
Mainframe Interview questions

Other References
Mainframe Tools and others