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

Products

• Datacom
• DATACOM - AD
• CIS
• COMMON SERVICES FOR Z/OS
• 90S SERVICES
• DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS
• COMMON PRODUCT SERVICES COMPONENT
• Common Services
• CA ECOMETER SERVER COMPONENT FOC
• Easytrieve Report Generator for Common Services
• INFOCAI MAINTENANCE
• IPC
• UNICENTER JCLCHECK COMMON COMPONENT
• Mainframe VM Product Manager
• CHORUS SOFTWARE MANAGER
• CA ON DEMAND PORTAL
• CA Service Desk Manager - Unified Self Service
• PAM CLIENT FOR LINUX ON MAINFRAME
• MAINFRAME CONNECTOR FOR LINUX ON MAINFRAME
• GRAPHICAL MANAGEMENT INTERFACE
• WEB ADMINISTRATOR FOR TOP SECRET
• Xpertware

Issue/Introduction

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.

Environment

Release: DBCISF00200-15.1-Datacom-Database-Option for CICS Services
Component:

Resolution

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  *

  DEFRAG  DBID=###

  MASSADD DBID=###,TABLE=xxx,MULTUSE=NO,DDNAME=EXTRFILE 

  REPORT  DBID=###,AREA=CXX

/*

//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.

***