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:
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.
***