Building and Using Data Tables › Consolidating Tables › Demonstration

Demonstration

Objective

This example reads employee data from three tables that reside in different central versions and different dictionaries to produce a selected listing of employees in the data processing departments of three offices.

The SYSCTL= keyword values refer to ddnames that appear in an z/OS job control language stream:

//SYSTEM84 DD DSN=DBDC.SYSTEM84.SYSCTL,DISP=SHR
//SYSTEM85 DD DSN=DBDC.SYSTEM85.SYSCTL,DISP=SHR
//SYSTEM86 DD DSN=DBDC.SYSTEM86.SYSCTL,DISP=SHR

Parameters

INPUT TABLE=BOSTON-
EMPLOYEES TYPE=COPY 
USER=DOC1 PW=DOC1
*       OWNER=DRH
CATALOG=ASFDICT
SYSCTL=SYSTEM84 
*       WHERE DEPT-ID
EQ '1234'

TABLE=BOSTON-EMPLOYEES is the first required keyword on the INPUT parameter and specifies BOSTON-EMPLOYEES as the first table to be retrieved.

TYPE=COPY copies the BOSTON-EMPLOYEES table.

CATALOG=ASFDICT identifies the catalog that contains the table definition.

SYSCTL=SYSTEM84 specifies the central version z/OS that contains ASFDICT. (VM/ESA users should use CVMACH= option.)

WHERE DEPT-ID EQ '1234' specifies selection criteria applicable to all tables read.

INPUT TABLE=CHICAGO-
EMPLOYEES TYPE=CONSOL 
USER=DOC1 PW=DOC1
*       OWNER=DDR
CATALOG=TSTDICT
SYSCTL=SYSTEM85

TABLE=CHICAGO-EMPLOYEES is the first required keyword on the INPUT parameter and specifies CHICAGO-EMPLOYEES as a secondary table.

TYPE=CONSOL specifies consolidation of CHICAGO-EMPLOYEES with BOSTON-EMPLOYEES.

CATALOG=TSTDICT identifies the catalog that contains the table definition.

SYSCTL=SYSTEM85 specifies the central version that contains TSTDICT.

INPUT TABLE=DENVER-
EMPLOYEES TYPE=CONSOL 
USER=DOC1 PW=DOC1
*       OWNER=ADR
CATALOG=PRODICT
SYSCTL=SYSTEM86

TABLE=DENVER-EMPLOYEES is the first required keyword on the INPUT parameter and specifies DENVER-EMPLOYEES as a secondary table.

TYPE=CONSOL specifies consolidation of DENVER-EMPLOYEES with CHICAGO-EMPLOYEES.

CATALOG=PRODICT identifies the catalog that contains the table definition.

SYSCTL=SYSTEM86 specifies the central version that contains PRODICT.

Complete Code

 col. 2
 ▼
 INPUT TABLE=BOSTON-EMPLOYEES TYPE=COPY USER=DOC1 PW=DOC1
*       OWNER=DRH CATALOG=ASFDICT SYSCTL=SYSTEM84
*       WHERE DEPT-ID EQ '1234'
 INPUT TABLE=CHICAGO-EMPLOYEES TYPE=CONSOL USER=DOC1 PW=DOC1
*      OWNER=DDR CATALOG=TSTDICT SYSCTL=SYSTEM85
 INPUT TABLE=DENVER-EMPLOYEES TYPE=CONSOL USER=DOC1 PW=DOC1
*      OWNER=ADR CATALOG=PRODICT SYSCTL=SYSTEM86
 01OUTPUT D
 013EMPLOYEES IN DATA PROCESSING
 0151*010 TABLE-ID   HH 'TABLE-ID'
 0151*020 TABLE-NAME SZ=20 HH 'TABLE-NAME'
 0151*030 EMP-NAME   HH 'EMPLOYEE'
 0151*040 JOB-TITLE  HH 'TITLE'

Result

REPORT NO. 01         EMPLOYEES IN DATA PROCESSING    mm/dd/yy PAGE     1
              TABLE-ID                 TABLE-NAME                       EMPLOYEE                         TITLE

                  1               BOSTON-EMPLOYEES                JOHN SMYTH                      PROGRAMMER
                  1               BOSTON-EMPLOYEES                MARY JONES                      DBA
                  1               BOSTON-EMPLOYEES                JOE GREEN                       PROGRAMMER
                  2               CHICAGO-EMPLOYEES               JOAN WHITE                      DATA ENTRY CLERK
                  2               CHICAGO-EMPLOYEES               JAN HUBBARD                     DBA
                  2               CHICAGO-EMPLOYEES               DAVID KELLY                     REGIONAL MGR
                  3               DENVER-EMPLOYEES                MEL SMITH                       DATA ENTRY CLERK
                  3               DENVER-EMPLOYEES                MARIO JENI                      DBA

Table Extraction Statistics

mm/dd/yy              RUN TIME MESSAGES        volser Vnn.n  PAGE     1

         DATA TABLE EXTRACTION STATISTICS                                               ROWS
                                                                                         OBTAINED
                     TABLE-NAME                                           TABLE-ID       FROM IDMS/DB
                     ──────────                                           ────────       ───────────
          BOSTON-EMPLOYEES                                                   001                 3
          CHICAGO-EMPLOYEES                                                  002                 3
          DENVER-EMPLOYEES                                                   003                 2
                                                                                              ─────
          CONSOLIDATION TOTALS                                                                   8

***********  END OF FILE  ******************************************************
           8 INPUT RECORDS READ