HELP in merging two files

Roshnii · New User Joined: 30 Sep 2008 Posts: 39 Location: bangalore

I have two tables , table A and table B.
Table b is the child of table a. I need to unload the data
Of both the tables and put it in a file.
Say the data in the tables are like this

TABLE A

ID(PK) NAME SEMESTER
001 THOMAS THIRD
002 SMITH FOURTH
003 JOHN FIRST

TABLE B
ID(PK) SEQ(PK) SUBJECTS
001 1 MATHS
001 2 GEOGRAPHY
001 3 PSYCHOLOGY
002 1 HISTORY
003 1 ENGLISH
003 2 ELECTRONICS

OUTPUT FILE
A.ID A.NAME A.SEMESTER B.ID B.SEQ B.SUBJECTS
001 THOMAS THIRD 001 1 MATHS
001 THOMAS THIRD 001 2 GEOGRAPHY
001 THOMAS THIRD 001 3 PSYCHOLOGY
002 SMITH FOURTH 002 1 HISTORY
003 JOHN FIRST 003 1 ENGLISH
003 JOHN FIRST 003 2 ELECTRONICS

The output should be such that when there are multiple TABLE B entries then one record would be created for each TABLE B with the TABLE A repeating at the beginning or each record.

I do an unload of the data from these two tables and keep them in two files. Then while merging, is it possible to obtain the above results using sort?

expat · Posted: Wed Jul 01, 2009 3:25 pm Post subject:

The usual questions ........................
For both input files - what is the RECFM and LRECL.
In which position and format are the key fields.

Roshnii · New User Joined: 30 Sep 2008 Posts: 39 Location: bangalore

Both the files are FB and the rec length for file A is 38.
and for file B is 35
and the key for table A is ID which is defined as DECIMAL(18,0) in the database
and the key for table B are ID and seq number which are defined as DECIMAL(18,0) and SMALLINT

expat · Posted: Wed Jul 01, 2009 3:55 pm Post subject:

Where the key fields live on the database is of no consequence, it is where they are in the unloaded file that matters.

sudhakarreddy.ibm · New User Joined: 20 Apr 2009 Posts: 15 Location: chennai

Hello,

I think, following query will help you to get your required output.

select A.ID, A.NAME, A.SEMESTER, B.ID, B.SEQ, B.SUBJECTS from A JOIN B ON A.ID=B.ID
WHERE GROUP BY A.ID,B.ID

Please correct me if i am wrong?

Sudha................

sudhakarreddy.ibm · New User Joined: 20 Apr 2009 Posts: 15 Location: chennai

Inaccurate response removed - Expat

Roshnii · New User Joined: 30 Sep 2008 Posts: 39 Location: bangalore

I will be unloading the data from table A to file A which is of length 38 and from table B to a file B which is of 35
and the the first 18 characters of each file will be the key X(18). I hope my answer is clear.

Skolusu · Posted: Wed Jul 01, 2009 11:49 pm Post subject: