Creating virtual tables for CA IDMS data

Edit online

Create virtual tables that map to the CA IDMS data that you want to access and from which the SQL used to access the data is generated and executed.

Before you begin

The Data Virtualization Manager server must be configured for CA IDMS access, and the CA IDMS central version referenced by the data server SYSCTL DD statement must be active.

About this task

CA IDMS schema records are mapped using the CA IDMS data dictionary. Each record is mapped as a separate virtual table using the COBOL names to derive the SQL column names. In addition to records, schema sets can be mapped as well. Virtual tables created for CA IDMS sets serve as correlation tables between CA IDMS records so SQL joins can navigate the CA IDMS schema.

Procedure

  1. On the Server tab, explore the CA IDMS metadata information by expanding the Discovery > IDMS node, and then navigating down the appropriate subtree. The hierarchy begins with the data dictionary, followed by the CA IDMS schema, the CA IDMS subschema, and then the associated records and sets.
  2. Select one or more records, as follows:
    • To select individual records, hold down the Ctrl key and click each record to include.
    • To select a range of records, click the first record in the range, and then hold the Shift key and select the last record in the range. All records within the range will be included.
    • To select all child records under a parent, click the parent record.
  3. Right-click the selected records and select Create Virtual Table(s). The New Virtual Tables Wizard launches.
    Note: You can map the relevant CA IDMS sets in the wizard.
  4. On the Create IDMS virtual tables page, complete the following Common Virtual Table Settings:
    FieldDescription
    Metadata Library From the drop-down list, select the target library where the virtual table metadata will be stored (for example, hlq.USER.MAP). The target libraries are specified in the server's started task JCL.
    Schema Name From the list of defined Schemas, select a Schema for the virtual table.
    Description Enter an optional description.
    Arrays Handling Select one of the following options:
    • Flatten arrays into a single fixed table at runtime (Y): This option supports both OCCURS and OCCURS DEPENDING ON statements.
    • Return arrays into separate tables at runtime (N): This option supports both OCCURS and OCCURS DEPENDING ON statements. A subtable is generated for each array. Subtables support SQL read access only.
    Virtual Table Naming Patterns Specify the format to use for the generated virtual table names. You can specify different patterns for records and sets. Use the following variables to create naming patterns that are derived from the IDMS metadata:
    • {SubSchema}: Subschema name
    • {Record}: Record name
    • {Set}: Set name
    Prune IDMS record field suffix from column names Select this option to remove the IDMS record field suffix from the column names.
  5. In the table that lists the IDMS records, review the list of selected entries. Modify the selections as needed.
    Tip: Use the check box in the header row of the table to control the selection of all entries.
  6. To map the sets, click Fetch Related IDMS Sets. The studio collects additional metadata from the server and displays the relevant items in the table that lists the IDMS sets.
  7. In the table that lists the IDMS sets, review the list of selected entries. Modify the selections as needed.
  8. To disable MapReduce, click Advanced and select Disable MapReduce.
  9. Click Finish.

Results

The studio creates the virtual tables (the metadata maps) on the server.

What to do next

Use the studio to easily compose and execute SQL queries using your new virtual tables. See Generating and executing SQL queries.