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

db2 sql interview questions

Moderator: Moderator Group

Post Reply
fredrick andrews
Member
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:

abc123
def455
ghi347

Db2 Table data:

amnjgh
def798
anh762
ksj934
ghi121

Output file:

def798
ghi121


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
dbzTHEdinosauer
Moderator
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

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