Load a new table from data of 2 tables

Anney · New User Joined: 06 Jan 2007 Posts: 7 Location: Chennai

Hi,

I have got 2 tables, one partition and other normal. I have to create a new table (partition) with rows from the two tables after a join subquery. There are certain colums which I need to update in the new table. Could any one tell me if the below steps are fine? Or is there a better way to do this.

1. Create a temporary view for the join of 2 tables for each partition of the first table
2. Unload the data to a sequential file
3. Make changes to the sequential file
4. Load it to the new table for each partiton of the first table

Thanks,
Anney

mkk157 · Active User Joined: 17 May 2006 Posts: 310

Hi Anney,

The steps for ur requirement are fine. Just continue with those steps, and let us know if u face any problem in between the processing.

Anney · New User Joined: 06 Jan 2007 Posts: 7 Location: Chennai

Thanks M K K. I will get back.

Thanks,
Anney

Suryanarayana.tadala · New User Joined: 03 Nov 2005 Posts: 43 Location: St.Louis

Any update Anney !!!

stodolas · Posted: Sat Jun 23, 2007 7:13 am

Doesn't DB2 handles the Insert into TABLE SELECT ..... FROM table A, Table B? Without going through an unload/reload cycle?

Johnson_Jiang · Posted: Thu Jun 28, 2007 12:36 pm

Use 'INSERT INTO <new table> SELECT xxxx... FROM tb1, tb2 ... can simplify this process, one SQL statement is enough!

vini_srcna · Posted: Thu Jun 28, 2007 4:31 pm

well, look at the amount of data. If it is of huge data then UNLOAD & LOAD will be best than INSERT into table select...

I dont think you have to create a View firstly. Just put the view DDL in the unload input. It would get the data for you in the flat file.

As per my understanding its not a good idea to edit the data in the unload file. ( As per your step: Make changes to the sequential file ). Do a load on to the new table and use SQL to make whatever the changes you want to do. It would be easy for you and its a preffered method.

Anney · New User Joined: 06 Jan 2007 Posts: 7 Location: Chennai

Many Thanks All,

It took a while for me to work on this. This is what I did at the end.

1.Unloaded the first and second tables into 2 separate sequential files. This was done part by part.
2. Then the two files were joined using SYNCSORT Join with a common key to a third file
3. Load append this third file to the new table for each partiton (similary partitioned as the first table)

Thanks,
Anney