How to convert two tables in different databases into a single partitioned table in one database.

Document ID : KB000009566
Last Modified Date : 14/02/2018
Show Technical Document Details

When trying to combine two different tables from different databases into a single table that has been defined as partitioned, there are several approaches, which can all work.

However, before choosing the method to use, there are different factors to consider - how many records are in each table, how many different partitions will be loaded, how many source tables will be processed.

The ideal method to use to create a partitioned table is to use a DBUTLTY BACKUP-format file, but that may not possible in cases like this. Another option is to take an EXTRACT of each table to be combined and append them all into a single file which is then sorted. You can then use the Sort program to to split this sorted/merged file into a separate file for each partition, and then load each partition from that. Obviously, this is both labor intensive and requires handling the data multiple times.


While the above factors need to be considered, this next approach would be the most likely choice for many cases. This approach assumes that you have already built the partitioned table definition and initialized all the component areas.

In this approach, you would still need to run DBUTLTY EXTRACT to get the data into individual files - one for each source table.

You would then use DBUTLTY MASSADD to load each of the Extract-format files into the partitioned table, using the name of the parent table. The MUF would then put the data into the appropriate partition. Here is an example:

//SYSIN    DD  *
//EXTRFILE DD  DISP=SHR,DSN=<your extracted file>,BUFNO=50
The first function is a DEFRAG to help optimize the index before all the new data is loaded. If the partitioned tables will be in a new database, this is not needed. The source data is read from the DD statement EXTRFILE, and then the MASSADD will load it into table xxx, where xxx is the Parent table name. As a performance consideration, the data should be processed using the largest extract first, and the smallest last. Note the addition of BUFNO=50 on the EXTRFILE DD statement, to help improve performance reading the data.
Additional Information:

For more information about CA Datacom Table Partitioning, I would recommend the Knowledge Base article TEC1633438, titled "Overview of resources about CA Datacom Table Partitioning," which contains information about webcast videos, PDF files and references to the various CA Datacom documentation to help you set up and use your partitions most effectively.

As always, please contact CA Technologies support for CA Datacom if you have further questions.