2 Configuring Access to VSAM Data

This chapter explains how to configure access to VSAM data by using Oracle Studio for IMS, VSAM, and Adabas Gateways. It contains the following sections:

Setting Up Access to a Computer Running Oracle Connect for IMS, VSAM, and Adabas Gateways

To be able to access VSAM data, you must have a working connection to Oracle Connect for IMS, VSAM, and Adabas Gateways, which is installed on the IBM z/OS computer where your VSAM files are located. You establish this connection by using Oracle Studio for IMS, VSAM, and Adabas Gateways.

Whenever needed, you can also use Oracle Studio for IMS, VSAM, and Adabas Gateways to configure Oracle Connect for IMS, VSAM, and Adabas Gateways.

Note:

The following task assumes that you have permission to access the IBM z/OS platform and that the Oracle Connect for IMS, VSAM, and Adabas Gateways daemon is running on this computer. Check with the system administrator to ensure these requirements are fulfilled.

Perform the following steps to set up access to a computer that runs Oracle Connect for IMS, VSAM, and Adabas Gateways.

  1. From the Start menu, select Programs, point to Oracle, and select Studio. Oracle Studio for IMS, VSAM, and Adabas Gateways opens.

  2. In the Design perspective's Configuration view, right-click the Machines folder and select Add Machines.

    The Add machine screen opens, as shown in Figure 2-1.

    Figure 2-1 Add Machine Screen.

    The Add Machine screen.
    Description of "Figure 2-1 Add Machine Screen."

    Enter the following information in each field:

    • Host name/IP address: Enter the name of the computer on the network or click Browse, to browse all the systems running a daemon listener on the specified port currently accessible over the network.

    • Port: Enter the port number where the daemon is running. The default port is 2551.

    • Display name: Enter an alias used to identify the system when different from the host name (optional).

    • User name: Enter the system administrator's user name.

      Note:

      You indicate the system administrator when the system is installed or by using the NAV_UTIL utility ADD_ADMIN operation. For details, see Oracle Connect for IMS, VSAM, and Adabas Gateways Installation and Configuration Guide for IBM z/OS.
    • Password: Enter the system administrator's password. This is the password for the user entered in the User name field. If no password is necessary to access this system, do not enter anything.

    • Connect via NAT with fixed IP address: Select this if the system uses the NAT (Network Address Translation) firewall protocol, with a fixed configuration, mapping each external IP to one internal IP, regardless of the port specified.

  3. Click Finish.

    The computer is displayed in the Configuration view, in the Machine folder. You can edit the system's login information, or configure bindings, daemons, and users for each system.

Setting Up a VSAM Data Source

Oracle Connect for IMS, VSAM, and Adabas Gateways supports the following types of VSAM data sources:

  • VSAM (CICS): The VSAM under CICS data source accesses VSAM by making calls from the external CICS interface (EXCI) to a CICS program that is provided as part of the Oracle Connect for IMS, VSAM, and Adabas Gateways installation. This CICS program does the actual VSAM reads and writes from within CICS.

    When you access VSAM data using this data source, the following restrictions apply:

    • SQL DELETE operations are not supported for ESDS files.

    • Using an alternate index to access an entry sequenced data set (ESDS) file is not supported.

    • A non-unique alternate index for a key sequenced data set (KSDS) file is not supported.

  • VSAM/Batch: This data source connects directly to the VSAM data and is limited if the VSAM files are managed by CICS. In this case, it is recommended to use this data source for read-only access to VSAM files. However, this may not give you adequate read integrity if some changes are buffered by CICS. Another alternative is to use the VSAM under CICS data source.

    When you access VSAM data using the VSAM data source, the following restrictions apply:

    • Transactions are not supported when you access VSAM directly. When you access VSAM under CICS, global transactions are supported.

    • Locking is not supported.

    • You cannot update an array value (a child record in a hierarchical table) when the parent record is included in the SQL in a subquery.

    • SQL DELETE operations are not supported for ESDS files.

    • A relative record data set (RRDS) file cannot have an alternate index.

    • The primary key of a KSDS file must be one segment only (however, it can be several consecutive fields). You cannot modify the primary key value of a KSDS file.

This section describes how to set up a VSAM data source using Oracle Studio for IMS, VSAM, and Adabas Gateways. It includes the following tasks:

Defining the VSAM Data Source Connection

Perform the following steps to define the connection to the VSAM data source.

  1. In Oracle Studio for IMS, VSAM, and Adabas Gateways, in the Configuration view of the Design perspective, open the binding of the system where the data is located.

  2. Right-click Data Source and select New Data Source.

    The New Data Source screen opens, as shown in Figure 2-2.

    Figure 2-2 New Data Source Screen

    New Data Source screen
    Description of "Figure 2-2 New Data Source Screen"

  3. Specify a name for the data source in the Name field.

    Note:

    The name you assign to each data source that you define must be noted and used consistently throughout all phases of the gateway configuration.
  4. From the Type list, select VSAM and click Next.

    Click Next.

  5. Specify the connect string as follows:

    • Data HLQ: The high-level qualifier where the data files are located.

    • Disk Volume Name: The high-level qualifier (volume) where the data is located.

      The values specified are used in the Data File field in the Oracle Studio for IMS, VSAM, and Adabas Gateways Design Perspective Metadata tab. For tables created using the CREATE TABLE statement, the values specified are used to create the data files. If values are not specified, then data files are written to the DEF high-level qualifier under the high-level qualifier where Oracle Connect for IMS, VSAM, and Adabas Gateways is installed.

      When SMS is used to manage the volumes, leave this value empty and set the newFileSMSStorageClass and newFileSMSDataClass properties as described in VSAM Data Source Parameters.

  6. Click Finish.

Defining the VSAM (CICS) Data Source Connection

Perform the following steps to define the connection to the VSAM (CICS) data source:

  1. In Oracle Studio for IMS, VSAM, and Adabas Gateways, in the Configuration view of the Design perspective, open the binding of the system where the data is located.

  2. Right-click Data Source and select New Data Source.

    The New Data Source screen opens, as shown in Figure 2-3.

    Figure 2-3 New Data Source Screen

    New Data Source screen
    Description of "Figure 2-3 New Data Source Screen"

  3. Specify a name for the data source in the Name field.

  4. From the Type list, select VSAM (CICS) and click Next.

    The Data Source Connect String screen is displayed.

  5. Specify the connect string as follows:

    • CICS Application ID: VTAM applID of the CICS target system. The default value is CICS. This parameter is used when updating VSAM data. You can determine this value by activating the CEMT transaction on the target CICS system. In the bottom right corner of the screen appears the legend APPLID=target_system.

    • Transaction ID: The mirror transaction within CICS that receives control through MRO, which transfers the transaction from the Oracle Connect for IMS, VSAM, and Adabas Gateways environment to CICS. The default value is EXCI.

    • VTAM Netname: The VTAM netname of the specific connection being used by EXCI (and MRO) to relay the program call to the CICS target system. For example, if you issue the following command to CEMT:

      CEMT INQ CONN

      Then you see on the display screen that the netname is BATCHCLI (this is the default connection supplied by IBM upon the installation of CICS). The default value is ATYCLIEN.

      If you plan to use the IBM defaults, specify BATCHCLI as the VTAM_netname parameter, otherwise define a specific connection (with EXCI protocol) and use the netname you provided there for this parameter.

      • Oracle provides a netname, ATYCLIEN that can be used after the following procedure is followed:

        Either, use the JCL in the NAVROOT.USERLIB(CICSCONF) member to submit the DFHCSDUP batch utility program to add the resource definitions to the DFHCSD dataset (see the IBM CICS Resource Definition Guide for further details).

        or:

        Use the instream SYSIN control statements in the NAVROOT.USERLIB(CICSCONF) member as a guide to defining the resources online using the CEDA facility.

      • After the definitions have been added (through batch or using the CEDA facility), logon to CICS and issue the following command to install the resource definitions under CICS:

        CEDA INST GROUP(ATYI)

        Henceforth, specify ATYCLIEN as the NETNAME.

    • Program Name: The UPDTRNS program that is supplied with Oracle Connect for IMS, VSAM, and Adabas Gateways to enable updating VSAM data. To use the UPDTRNS program, copy the program from NAVROOT.LOAD to a CICS DFHRPL library (such as CICS.USER.LOAD) and then define the UPDTRNS program under CICS using any available group:

      CEDA DEF PROG(UPDTRNS) G(group_name) LANG(C) DA(ANY) DE(ATTUNITY VSAM UPDATE PROG)

      NAVROOT is the high-level qualifier where Oracle Connect for IMS, VSAM, and Adabas Gateways is installed.

      After defining the UPDTRNS program, install it as follows:

      CEDA IN G(group_name)

    • Trace Queue: The name of the queue for output that is defined under CICS when tracing the output of the UPDTRNS program. When not defined, the default CICS queue is used.

  6. Click Finish.

Configuring the VSAM or VSAM (CICS) Data Source Properties

After defining the connection to the VSAM (CICS) data source, perform the following steps to configure the data source properties.

  1. In Oracle Studio for IMS, VSAM, and Adabas Gateways, in the Configuration view of the Design perspective, right-click the data source and select Edit Data Source.

  2. In the editor, on the Properties tab, configure the data source parameters as required. For a description of the available parameters, see "VSAM Data Source Parameters" or "VSAM (CICS) Data Source Parameters".

  3. If you want to configure VSAM (CICS) to support rollback, on the Advanced tab, from the Transaction type list, select 1PC or 2PC, depending on the data source usage.

    If you do not use 2PC, see the Oracle Database Heterogeneous Connectivity User's Guide for configuration information.

  4. On the Advanced tab, configure how arrays will be handled by selecting the relevant check boxes in the Virtual View Policy section. The following options are available:

    • Generate sequential view: Select this option if you want to map a non-relational file to a single table.

    • Generate virtual views: Select this option if you want to have an individual table created for every array in the non-relational file.

    • Include row number column: Select this option if you want to include a column that specifies the row number in the virtual or sequential view.

    • Inherit all parent columns: Select this option if you want the virtual views to include all the columns of the parent record.

  5. Save your settings.

VSAM Data Source Parameters

The following parameters can be configured for VSAM data sources in the Properties tab of the Configuration Properties screen:

  • disableExplicitSelect=true|false: When set to true, this parameter disables the ExplicitSelect ADD attribute; every field is returned by a SELECT * FROM... statement.

  • filepoolCloseOnTransaction=true|false: This parameter specifies that all files in the file pool for this data source close at the end of each transaction (commit or rollback).

  • filepoolSize=n: This parameter specifies how many instances of a file from the file pool may be open concurrently.

  • newFileLocation=string: This parameter is the Data HLQ in the connect string that specifies the high-level qualifier for the file.

  • newFileSMSStorageClass=string: This parameter specifies the storage class when SMS is used to manage volumes.

  • newFileSMSDataClass=string: This parameter specifies the data class when SMS is used to manage volumes.

  • newFileVolume=string: This parameter specifies the file volume where the file is catalogued (data disk in the connect string).

  • useGlobalFilepool=true|false: This parameter specifies whether or not a global file pool that can span more than one session is used.

VSAM (CICS) Data Source Parameters

The following parameters can be configured for VSAM (CICS) data sources in the Properties tab of the Configuration Properties screen:

  • allowUpdateKey=true|false: When set to true, this parameter specifies that the key is updatable.

  • cicsProgname=string: The ProgramName in the connect string, this parameter specifies the UPDTRNS program that is supplied with Oracle Connect for IMS, VSAM, and Adabas Gateways to enable updating VSAM data.

  • cicsTraceQueue=string: The TraceQueue in the connect string, this parameter indicates the name of queue for output that is defined under CICS when tracing the output of the UPDTRNS program. When not defined, the default CICS queue is used.

  • disableExplicitSelect=true|false: When set to true, this parameter disables the ExplicitSelect ADD attribute; every field is returned by a SELECT * FROM... statement.

  • exciTransid=string: The Transaction ID in the connect string, this parameter indicates the CICS TRANSID. This value must be EXCI or a copy of this transaction.

  • targetSystemApplid=string: The CICS Application ID in the connect string, this parameter specifies the VTAM applid of the CICS target system.

  • TransactionType=true|false: When set to true, this parameter specifies that the key is updatable.

  • vtamNetname=string: The VTAM Netname in the connect string, this parameter specifies the connection being used by EXCI (and MRO) to relay the program call to the CICS target system.

Setting Up the Metadata of the VSAM Data Source

Defining VSAM metadata by using the Import Manager of Oracle Studio for IMS, VSAM, and Adabas Gateways requires a COBOL copybook that contains the COBOL definition of the VSAM records.

If the metadata is provided in a number of COBOL copybooks with different filter settings, such as whether the first 6 columns are ignored or not (see Apply Filters), first import the metadata from copybooks with the same settings, and then import the metadata from the other copybooks.

The import process includes the following tasks:

  1. Import the COBOL Copybooks

  2. Apply Filters

  3. Select Tables

  4. Manipulate the Imported Tables

  5. Create VSAM Indexes

  6. Assign Index File Names

  7. Select the Metadata Model

  8. Import the Metadata

Import the COBOL Copybooks

This section describes the steps required to import the COBOL copybooks that will be used to generate the metadata. You can import new copybooks or use copybooks that you already imported earlier.

To import new copybooks, perform the following procedure.

  1. In the Configuration view, right-click the data source and select Edit Metadata.

    The Metadata tab is displayed with the data source displayed in the Metadata view.

  2. Right-click the Imports node under the data source and select New Import.

    The Metadata Import screen opens, as shown in Figure 2-4:

    Figure 2-4 Metadata Import Screen

    New Import screen, used to define import name and type
    Description of "Figure 2-4 Metadata Import Screen"

  3. Enter a name for the import. The name can contain letters, numbers and the underscore character.

  4. Select VSAM Import Manager or VSAM Under CICS Import Manager as the import type as required.

  5. Click Finish.

    The Metadata import wizard opens with the Get Input Files screen, as shown in Figure 2-5:

    Figure 2-5 The Get Input Files Screen

    Get Input Files screen where you define files to import
    Description of "Figure 2-5 The Get Input Files Screen"

  6. Click Add.

    The Add Resource screen opens, as shown in Figure 2-6:

    Figure 2-6 Add Resource Screen

    Add Resource screen used to navigate to COBOL files
    Description of "Figure 2-6 Add Resource Screen"

  7. If the files are located on another computer, do any of the following:

    • Right-click My FTP Sites and select Add.

      The Add FTP Site screen opens, as shown in the following figure.

      Add FTP Site screen
      Description of the illustration vsamcx_3.gif

    • Enter the server name where the COBOL copybooks are located and, if not using an anonymous connection, enter a valid username and password to access the computer. The username is then used as the high-level qualifier.

    • Click OK. After accessing the remote computer, you can change the high-level qualifier by right-clicking the system in the Add Resource screen and selecting Change Root Directory.

    • Select the files to import and click Finish to start the file transfer. When complete, the selected files are displayed in the Get Input Files screen.

      To remove any of these files, select the required file and click Remove.

  8. Click Next.

    The Apply Filters screen opens (see Apply Filters).

To use copybooks that you imported earlier, perform the following procedure.

  1. In the Configuration view, right-click the data source and select Edit Metadata.

    The Metadata tab is displayed with the data source displayed in the Metadata view.

  2. Expand the Imports node under the data source, right-click the existing import, and select Open.

    The Metadata import wizard opens with the Get Input Files screen, showing the copybooks that you imported.

  3. Click Next.

    The Apply Filters screen opens (see Apply Filters).

Apply Filters

This section describes the steps required to apply filters on the COBOL copybooks used to generate the Metadata. It continues the Import the COBOL Copybooks procedure. Perform the following procedure.

  1. Expand all nodes in the Apply Filters screen.

    Figure 2-7 Apply Filters Screen

    Apply Filters screen used to apply filters on COBOL
    Description of "Figure 2-7 Apply Filters Screen"

  2. Apply the required filter attributes to the COBOL copybooks.

    The following table describes the available filters.

    Table 2-1 Metadata Filters

    Filter Description

    COMP_6 switch

    The MicroFocus COMP-6 compiler directive. Specify either COMP-6'1' to treat COMP-6 as a COMP data type or COMP-6'2' to treat COMP-6 as a COMP-3 data type.

    Compiler source

    z/OS

    Storage mode

    The MicroFocus Integer Storage Mode. Specify one of the following:

    • NOIBMCOMP for byte storage mode.

    • IBMCOMP for word storage mode.

    Ignore after column 72

    When set to true, ignores columns 73 to 80 in the COBOL copybook.

    IgnoreFirst6

    When set to true, ignores the first six columns in the COBOL copybook.

    Replace hyphens (-) in record and field names with underscores (_)

    When set to true, replaces all hyphens in either the record or field names in the metadata generated from the COBOL with underscore characters.

    Prefix nested columns

    When set to true, prefix all nested columns with the previous level heading.

    Case sensitive

    Specifies whether to be sensitive to the search string case.

    Find

    Searches for the specified value.

    Replace with

    Replaces the value specified for Find with the value specified here


  3. Click Next.

    The Select Tables screen opens (see Select Tables).

Select Tables

This section describes the steps required to select the tables from the COBOL copybooks. The import manager identifies the names of the records in the COBOL copybooks that will be imported as tables.

This task continues the Apply Filters task. Perform these steps to select the required tables.

  1. From the Select Tables screen, select the tables that you want to access. To select all tables, click Select All. To clear the selection, click Unselect All.

    Figure 2-8 Select Tables Screen

    Select Tables screen used to select required tables
    Description of "Figure 2-8 Select Tables Screen"

  2. Click Next.

    The Import Manipulation screen opens (see Manipulate the Imported Tables).

Manipulate the Imported Tables

If required, you can now manipulate the imported tables. This is an optional task.

This section describes the operations available for manipulating the imported tables. It continues the Select Tables task.

The import manager identifies 01 level names in the copybooks that will be imported as tables. You can manipulate the general table data in the Import Manipulation Screen.

Perform the following steps to manipulate the table metadata.

  1. In the Data Location column, click the browse button to specify the data file location. The Select Resources dialog box opens.

  2. In the File Name box, enter the location of the data file and click Finish.

  3. From the Import Manipulation screen (for details, see Import Manipulation Screen), right-click the table record marked with a validation error and select the relevant operation. Table 2-2 describes the available operations.

  4. Repeat step 1 for all tables marked with a validation error. You resolve the issues in the Import Manipulation Screen.

    Once all the validation error issues have been resolved, the Import Manipulation screen is displayed with no error indicators.

  5. Click Next to continue to the Create VSAM Indexes.

Import Manipulation Screen

Figure 2-9 shows the Import Manipulation screen.

Figure 2-9 Import Manipulation Screen

Import Manipulation Screen
Description of "Figure 2-9 Import Manipulation Screen"

The upper area of the screen lists the table names and their validation status. The metadata source specifies the copybook in which the table is defined.

The Validation tab at the lower area of the screen displays information about what needs to be resolved to validate the tables and fields generated from the COBOL. The Log tab displays a log of what has been performed (such as renaming a table or specifying a data location).

The following operations are available in the Import Manipulation screen:

  • Resolving table names, where tables with the same name are generated from different files during the import.

  • Selecting the physical location for the data.

  • Selecting table attributes.

  • Manipulating the fields generated from the COBOL, as follows:

    • Merging sequential fields into one (for simple fields).

    • Resolving variants by either marking a selector field or specifying that only one case of the variant is relevant.

    • Adding, deleting, hiding, or renaming fields.

    • Changing a data type.

    • Setting the field size and scale.

    • Changing the order of the fields.

    • Setting a field as nullable.

    • Selecting a counter field for array for fields with dimensions (arrays). You can select the array counter field from a list of potential fields.

    • Setting column-wise normalization for fields with dimensions (arrays). You can create new fields instead of the array field where the number of generated fields will be determined by the array dimension.

    • Creating arrays and setting the array dimension.

The following table describes the available operations when you right-click a table entry.

Option Description
Fields Manipulation Customizes the field definitions, using the Field Manipulation screen. You can also access this screen by double-clicking the required table record.
Rename Renames a table. This option is used especially when more than one table with the same name is generated from the COBOL.
Set data location Sets the physical location of the data file for the table.
Set table attributes Sets the table attributes.
XSL manipulation Specifies an XSL transformation or JDOM document that is used to transform the table definitions.
Remove Removes the table record.

You can manipulate the data in the table fields in the Field Manipulation Screen. Double-click a line in the Import Manipulation Screen to open the Field Manipulation Screen.

Field Manipulation Screen

The Field Manipulation screen lets you make changes to fields in a selected table. You get to the Field Manipulation screen by double-clicking a table in the Import Manipulation Screen. The Field Manipulation screen is shown in Figure 2-10.

Figure 2-10 Field Manipulation Screen

Field Manipulation Screen
Description of "Figure 2-10 Field Manipulation Screen"

The values displayed in the table on the Field Manipulation screen are calculated by the import process.

You can carry out all of the available tasks in this screen through the menu or toolbar. You can also right-click anywhere in the screen and select any of the options available in the main menus from a shortcut menu. The following table describes the tasks that are done in this screen. If a toolbar button is available for a task, it is pictured in the table.

The following table describes the commands that are available in the Field Manipulation screen.

Table 2-2 Field Manipulation Screen Commands

Command Description

General menu

 

Undo

Undo icon

Click to undo the last change made in the Field Manipulation screen.

Select fixed offset

Select fixed offset icon

The offset of a field is usually calculated dynamically by the server at run time according to the offset and size of the proceeding column. Select this option to override this calculation and specify a fixed offset at design time. This can happen if there is a part of the buffer that you want to skip.

When you select a fixed offset you pin the offset for that column. The indicated value is used at run time for the column instead of a calculated value. Note that the offset of following columns that do not have a fixed offset are calculated from this fixed position.

Test import tables

Test import tables icon

Select this table to create an SQL statement to test the import table. You can base the statement on the Full table or Selected columns. When you select this option, the following screen opens with an SQL statement based on the table or column entered at the bottom of the screen.

The Test Import Table screen.

Enter the following in this screen:

  • Data file name: Enter the name of the file that contains the data you want to query.

  • Limit query results: Select this if you want to limit the results to a specified number of rows. Enter the amount of rows you want returned in the following field. 100 is the default value.

  • Define Where Clause: Click Add to select a column to use in a Where clause. In the table below, you can add the operator, value and other information. Click on the columns to make the selections. To remove a Where Clause, select the row with the Where Clause you want t remove and then click Remove.

The resulting SQL statement with any Where Clauses that you added are displayed at the bottom of the screen.

Click OK to send the query and test the table.

Attribute menu

 

Change data type

Select Change data type from the Attribute menu to activate the Type column, or click the Type column and select a new data type from the list.

Create array

This command allows you to add an array dimension to the field. Select this command to open the Create Array screen.

The Create Array screen.

Enter a number in the Array Dimension field and click OK to create the array for the column.

Hide/Reveal field

Select a row from the Field manipulation screen and select Hide field to hide the selected field from that row. If the field is hidden, you can select Reveal field.

Set dimension

Select this to change or set a dimension for a field that has an array. Select Set dimension to open the Set Dimension screen.

Edit the entry in the Array Dimension field and click OK to set the dimension for the selected array.

Set field attribute

Set field attribute icon

Select a row to set or edit the attributes for the field in the row. Select Set field attribute to open the Field Attribute screen.

The Field Attributes screen.

Click in the Value column for any of the properties listed and enter a new value or select a value from a list.

Nullable/Not nullable

Select Nullable to activate the Nullable column in the Field Manipulation screen. You can also click in the column.

Select the check box to make the field Nullable. Clear the check box to make the field Not Nullable.

Set scale

Select this to activate the Scale column or click in the column and enter the number of places to display after the decimal point in a data type.

Set size

Select this to activate the Size column or click in the column and enter the number of total number of characters for a data type.

Field menu

 

Add

Add icon

Select this command or use the button to add a field to the table. If you select a row with a field (not a child of a field), you can add a child to that field. Select Add Field or Add Child to open the following screen:

The Set Field Name screen.

Enter the name of the field or child, and click OK to add the field or child to the table.

Delete field

Delete field icon

Select a row and then select Delete Field or click the Delete Field button to delete the field in the selected row.

Move up or down

Move up or down icon

Select a row and use the arrows to move it up or down in the list.

Rename field

Select Rename field to make the Name field active. Change the name and then click outside of the field.

Structures menu

 

Columnwise Normalization

Select Columnwise Normalization to create new fields instead of the array field where the number of generated fields will be determined by the array dimension.

Combining sequential fields

Select Combining sequential fields to combine two or more sequential fields into one simple field. The following dialog box opens:

Combining sequential fields screen

Enter the following information in the Combining sequential fields screen:

  • First field name: Select the first field in the table to include in the combined field

  • End field name: Select the last field to be included in the combined field. Make sure that the fields are sequential.

  • Enter field name: Enter a name for the new combined field.

Flatten group

Select Flatten Group to flatten a field that is an array. This field must be defined as Group for its data type. When you flatten an array field, the entries in the array are spread into a new table, with each entry in its own field. The following screen provides options for flattening.

The Flatten Group screen.

Do the following in this screen:

  • Select Recursive operation to repeat the flattening process on all levels. For example, if there are multiple child fields in this group, you can place the values for each field into the new table when you select this option.

  • Select Use parent name as prefix to use the name of the parent field as a prefix when creating the new fields. For example, if the parent field is called Car Details and you have a child in the array called Color, when a new field is created in the flattening operation it will be called Car Details_Color.

Mark selector

Select Mark selector to select the selector field for a variant. This is available only for variant data types. Select the Selector field form the following screen.

The mark selector screen.

Replace variant

Select Replace variant to replace a variant's selector field.

Select counter field

Select Counter Field opens a screen where you select a field that is the counter for an array dimension.

The Select Counter screen.

Create VSAM Indexes

Oracle Studio for IMS, VSAM, and Adabas Gateways automatically creates the VSAM indexes from the physical data file. Click Next to continue to the Select the Metadata Model task.

Note:

Oracle Studio for IMS, VSAM, and Adabas Gateways does not create any indexes for design systems.

Select the Metadata Model

This screen allows you to generate virtual and sequential views for tables containing arrays. In addition, you can configure the properties of the generated views.

This screen continues the Create VSAM Indexes procedure.

In the Metadata Model Selection screen, you can select default values that apply to all tables or set specific settings per table. This screen inherits its default settings from the settings that you defined for the virtual view policy when you configured the data source properties (see Configuring the VSAM or VSAM (CICS) Data Source Properties).

Note:

Selecting the Default values for all tables check box discards any table-specific settings that you set.

Perform the following steps to select the metadata model.

  1. Select from the following:

    • Default values for all tables: Select this option if you want to apply the same values to all tables to be imported.

      • Generate sequential view: Select this option if you want to map a non-relational file to a single table.

      • Generate virtual views: Select this option if you want to have an individual table created for every array in the non-relational file.

      • Virtual views include row number: Select this option if you want to include a column that specifies the row number in the virtual or sequential view.

      • Virtual views inherit all parent columns: Select this option if you want the virtual views to include all the columns of the parent record.

    • Specific virtual array view settings per table: Select this option if you want to apply different values to the tables to be imported. Then select the relevant check boxes.

      Note:

      After importing a table, you can change these settings on the Modeling tab of the table editor.
  2. Click Next to continue to the Import the Metadata task.

Figure 2-11 shows the Metadata Model Selection screen.

Figure 2-11 Metadata Model Selection Screen

The image shows the Metadata Model Selection screen.
Description of "Figure 2-11 Metadata Model Selection Screen"

Import the Metadata

This section describes the steps required to import the metadata to the target computer. It continues the Assign Index File Names task.

You can now import the metadata to the computer where the data source is located, or import it later (in case the target computer is not available).

Perform the following steps to import the metadata.

  1. Specify Yes to transfer the metadata to the target computer or No to transfer the metadata later.

  2. Click Finish.

If you specified Yes, the metadata is imported to the target computer immediately.

Figure 2-12 shows the Import Metadata screen.

Figure 2-12 Import Metadata Screen

Import Metadata screen: Decide when to import metadata.
Description of "Figure 2-12 Import Metadata Screen"

Setting Up the Metadata of the VSAM (CICS) Data Source

Defining VSAM metadata by using the Import Manager of Oracle Studio for IMS, VSAM, and Adabas Gateways requires a COBOL copybook that contains the COBOL definition of the VSAM records.

If the metadata is provided in a number of COBOL copybooks with different filter settings, such as whether the first 6 columns are ignored or not (see Apply Filters), first import the metadata from copybooks with the same settings, and then import the metadata from the other copybooks.

The import process includes the following tasks:

  1. Import the COBOL Copybooks

  2. Apply Filters

  3. Select Tables

  4. Manipulate the Imported Tables

  5. Create VSAM Indexes

  6. Assign Index File Names

  7. Select the Metadata Model

  8. Import the Metadata

Import the COBOL Copybooks

This section describes the steps required to import the COBOL copybooks that will be used to generate the metadata. You can import new copybooks or use copybooks that you already imported earlier.

To import new copybooks, perform the following procedure.

  1. In the Configuration view, right-click the data source and select Edit Metadata.

    The Metadata tab is displayed with the data source displayed in the Metadata view.

  2. Right-click the Imports node under the data source and select New Import.

    The Metadata Import screen opens, as shown in Figure 2-4:

    Figure 2-13 Metadata Import Screen

    New Import screen, used to define import name and type
    Description of "Figure 2-13 Metadata Import Screen"

  3. Enter a name for the import. The name can contain letters, numbers and the underscore character.

  4. Select VSAM Import Manager or VSAM Under CICS Import Manager as the import type as required.

  5. Click Finish.

    The Metadata import wizard opens with the Get Input Files screen, as shown in Figure 2-5:

    Figure 2-14 The Get Input Files Screen

    Get Input Files screen where you define files to import
    Description of "Figure 2-14 The Get Input Files Screen"

  6. Click Add.

    The Add Resource screen opens, as shown in Figure 2-6:

    Figure 2-15 Add Resource Screen

    Add Resource screen used to navigate to COBOL files
    Description of "Figure 2-15 Add Resource Screen"

  7. If the files are located on another computer, do any of the following:

    • Right-click My FTP Sites and select Add.

      The Add FTP Site screen opens, as shown in the following figure.

      Add FTP Site screen
    • Enter the server name where the COBOL copybooks are located and, if not using an anonymous connection, enter a valid username and password to access the computer. The username is then used as the high-level qualifier.

    • Click OK. After accessing the remote computer, you can change the high-level qualifier by right-clicking the system in the Add Resource screen and selecting Change Root Directory.

    • Select the files to import and click Finish to start the file transfer. When complete, the selected files are displayed in the Get Input Files screen.

      To remove any of these files, select the required file and click Remove.

  8. Click Next.

    The Apply Filters screen opens (see Apply Filters).

To use copybooks that you imported earlier, perform the following procedure.

  1. In the Configuration view, right-click the data source and select Edit Metadata.

    The Metadata tab is displayed with the data source displayed in the Metadata view.

  2. Expand the Imports node under the data source, right-click the existing import, and select Open.

    The Metadata import wizard opens with the Get Input Files screen, showing the copybooks that you imported.

  3. Click Next.

    The Apply Filters screen opens (see Apply Filters).

Apply Filters

This section describes the steps required to apply filters on the COBOL copybooks used to generate the Metadata. It continues the Import the COBOL Copybooks procedure. Perform the following procedure.

  1. Expand all nodes in the Apply Filters screen.

    Figure 2-16 Apply Filters Screen

    Apply Filters screen used to apply filters on COBOL
    Description of "Figure 2-16 Apply Filters Screen"

  2. Apply the required filter attributes to the COBOL copybooks.

    The following table describes the available filters.

    Table 2-3 Metadata Filters

    Filter Description

    COMP_6 switch

    The MicroFocus COMP-6 compiler directive. Specify either COMP-6'1' to treat COMP-6 as a COMP data type or COMP-6'2' to treat COMP-6 as a COMP-3 data type.

    Compiler source

    z/OS

    Storage mode

    The MicroFocus Integer Storage Mode. Specify one of the following:

    • NOIBMCOMP for byte storage mode.

    • IBMCOMP for word storage mode.

    Ignore after column 72

    When set to true, ignores columns 73 to 80 in the COBOL copybook.

    IgnoreFirst6

    When set to true, ignores the first six columns in the COBOL copybook.

    Replace hyphens (-) in record and field names with underscores (_)

    When set to true, replaces all hyphens in either the record or field names in the metadata generated from the COBOL with underscore characters.

    Prefix nested columns

    When set to true, prefix all nested columns with the previous level heading.

    Case sensitive

    Specifies whether to be sensitive to the search string case.

    Find

    Searches for the specified value.

    Replace with

    Replaces the value specified for Find with the value specified here


  3. Click Next.

    The Select Tables screen opens (see Select Tables).

Select Tables

This section describes the steps required to select the tables from the COBOL copybooks. The import manager identifies the names of the records in the COBOL copybooks that will be imported as tables.

This task continues the Apply Filters task. Perform these steps to select the required tables.

  1. From the Select Tables screen, select the tables that you want to access. To select all tables, click Select All. To clear the selection, click Unselect All.

    Figure 2-17 Select Tables Screen

    Select Tables screen used to select required tables
    Description of "Figure 2-17 Select Tables Screen"

  2. Click Next.

    The Import Manipulation screen opens (see Manipulate the Imported Tables).

Manipulate the Imported Tables

If required, you can now manipulate the imported tables. This is an optional task.

This section describes the operations available for manipulating the imported tables. It continues the Select Tables task.

The import manager identifies 01 level names in the copybooks that will be imported as tables. You can manipulate the general table data in the Import Manipulation Screen.

Perform the following steps to manipulate the table metadata.

  1. From the Import Manipulation screen (for details, see Import Manipulation Screen), right-click the table record marked with a validation error and select the relevant operation. Table 2-2 describes the available operations.

  2. Repeat step 1 for all tables marked with a validation error. You resolve the issues in the Import Manipulation Screen.

    Once all the validation error issues have been resolved, the Import Manipulation screen is displayed with no error indicators.

  3. Click Next to continue to the Create VSAM Indexes.

Import Manipulation Screen

Figure 2-9 shows the Import Manipulation screen.

Figure 2-18 Import Manipulation Screen

Import Manipulation Screen
Description of "Figure 2-18 Import Manipulation Screen"

The upper area of the screen lists the table names and their validation status. The metadata source specifies the copybook in which the table is defined.

The Validation tab at the lower area of the screen displays information about what needs to be resolved to validate the tables and fields generated from the COBOL. The Log tab displays a log of what has been performed (such as renaming a table or specifying a data location).

The following operations are available in the Import Manipulation screen:

  • Resolving table names, where tables with the same name are generated from different files during the import.

  • Selecting the physical location for the data.

  • Selecting table attributes.

  • Manipulating the fields generated from the COBOL, as follows:

    • Merging sequential fields into one (for simple fields).

    • Resolving variants by either marking a selector field or specifying that only one case of the variant is relevant.

    • Adding, deleting, hiding, or renaming fields.

    • Changing a data type.

    • Setting the field size and scale.

    • Changing the order of the fields.

    • Setting a field as nullable.

    • Selecting a counter field for the dimension of an array. You can select the array counter field from a list of potential fields.

    • Setting column-wise normalization for fields with dimensions (arrays). You can create new fields instead of the array field where the number of generated fields will be determined by the array dimension.

    • Creating arrays and setting the array dimension.

The following table describes the available operations when you right-click a table entry.

Option Description
Fields Manipulation Customizes the field definitions, using the Field Manipulation screen. You can also access this screen by double-clicking the required table record.
Rename Renames a table. This option is used especially when more than one table with the same name is generated from the COBOL.
Set data location Sets the physical location of the data file for the table.
Set table attributes Sets the table attributes.
XSL manipulation Specifies an XSL transformation or JDOM document that is used to transform the table definitions.
Remove Removes the table record.

You can manipulate the data in the table fields in the Field Manipulation Screen. Double-click a line in the Import Manipulation Screen to open the Field Manipulation Screen.

Field Manipulation Screen

The Field Manipulation screen lets you make changes to fields in a selected table. You get to the Field Manipulation screen by double-clicking a table in the Import Manipulation Screen. The Field Manipulation screen is shown in Figure 2-10.

Figure 2-19 Field Manipulation Screen

Field Manipulation Screen
Description of "Figure 2-19 Field Manipulation Screen"

The values displayed in the table on the Field Manipulation screen are calculated by the import process.

You can carry out all of the available tasks in this screen through the menu or toolbar. You can also right-click anywhere in the screen and select any of the options available in the main menus from a shortcut menu. The following table describes the tasks that are done in this screen. If a toolbar button is available for a task, it is pictured in the table.

The following table describes the commands that are available in the Field Manipulation screen.

Table 2-4 Field Manipulation Screen Commands

Command Description

General menu

 

Undo

Undo icon

Click to undo the last change made in the Field Manipulation screen.

Select fixed offset

Select fixed offset icon

The offset of a field is usually calculated dynamically by the server at run time according to the offset and size of the proceeding column. Select this option to override this calculation and specify a fixed offset at design time. This can happen if there is a part of the buffer that you want to skip.

When you select a fixed offset you pin the offset for that column. The indicated value is used at run time for the column instead of a calculated value. Note that the offset of following columns that do not have a fixed offset are calculated from this fixed position.

Test import tables

Test import tables icon

Select this table to create an SQL statement to test the import table. You can base the statement on the Full table or Selected columns. When you select this option, the following screen opens with an SQL statement based on the table or column entered at the bottom of the screen.

The Test Import Table screen.

Enter the following in this screen:

  • Data file name: Enter the name of the file that contains the data you want to query.

  • Limit query results: Select this if you want to limit the results to a specified number of rows. Enter the amount of rows you want returned in the following field. 100 is the default value.

  • Define Where Clause: Click Add to select a column to use in a Where clause. In the table below, you can add the operator, value and other information. Click on the columns to make the selections. To remove a Where Clause, select the row with the Where Clause you want t remove and then click Remove.

The resulting SQL statement with any Where Clauses that you added are displayed at the bottom of the screen.

Click OK to send the query and test the table.

Attribute menu

 

Change data type

Select Change data type from the Attribute menu to activate the Type column, or click the Type column and select a new data type from the list.

Create array

This command allows you to add an array dimension to the field. Select this command to open the Create Array screen.

The Create Array screen.

Enter a number in the Array Dimension field and click OK to create the array for the column.

Hide/Reveal field

Select a row from the Field manipulation screen and select Hide field to hide the selected field from that row. If the field is hidden, you can select Reveal field.

Set dimension

Select this to change or set a dimension for a field that has an array. Select Set dimension to open the Set Dimension screen.

Edit the entry in the Array Dimension field and click OK to set the dimension for the selected array.

Set field attribute

Set field attribute icon

Select a row to set or edit the attributes for the field in the row. Select Set field attribute to open the Field Attribute screen.

The Field Attributes screen.

Click in the Value column for any of the properties listed and enter a new value or select a value from a list.

Nullable/Not nullable

Select Nullable to activate the Nullable column in the Field Manipulation screen. You can also click in the column.

Select the check box to make the field Nullable. Clear the check box to make the field Not Nullable.

Set scale

Select this to activate the Scale column or click in the column and enter the number of places to display after the decimal point in a data type.

Set size

Select this to activate the Size column or click in the column and enter the number of total number of characters for a data type.

Field menu

 

Add

Add icon

Select this command or use the button to add a field to the table. If you select a row with a field (not a child of a field), you can add a child to that field. Select Add Field or Add Child to open the following screen:

The Set Field Name screen.

Enter the name of the field or child, and click OK to add the field or child to the table.

Delete field

Delete field icon

Select a row and then select Delete Field or click the Delete Field button to delete the field in the selected row.

Move up or down

Move up or down icon

Select a row and use the arrows to move it up or down in the list.

Rename field

Select Rename field to make the Name field active. Change the name and then click outside of the field.

Structures menu

 

Columnwise Normalization

Select Columnwise Normalization to create new fields instead of the array field where the number of generated fields will be determined by the array dimension.

Combining sequential fields

Select Combining sequential fields to combine two or more sequential fields into one simple field. The following dialog box opens:

Combining sequential fields screen

Enter the following information in the Combining sequential fields screen:

  • First field name: Select the first field in the table to include in the combined field

  • End field name: Select the last field to be included in the combined field. Make sure that the fields are sequential.

  • Enter field name: Enter a name for the new combined field.

Flatten group

Select Flatten Group to flatten a field that is an array. This field must be defined as Group for its data type. When you flatten an array field, the entries in the array are spread into a new table, with each entry in its own field. The following screen provides options for flattening.

The Flatten Group screen.

Do the following in this screen:

  • Select Recursive operation to repeat the flattening process on all levels. For example, if there are multiple child fields in this group, you can place the values for each field into the new table when you select this option.

  • Select Use parent name as prefix to use the name of the parent field as a prefix when creating the new fields. For example, if the parent field is called Car Details and you have a child in the array called Color, when a new field is created in the flattening operation it will be called Car Details_Color.

Mark selector

Select Mark selector to select the selector field for a variant. This is available only for variant data types. Select the Selector field form the following screen.

The mark selector screen.

Replace variant

Select Replace variant to replace a variant's selector field.

Select counter field

Select Counter Field opens a screen where you select a field that is the counter for an array dimension.

The Select Counter screen.

Assign File Names

This section describes the steps required to specify the physical file name and the corresponding logical CICS file name for each VSAM table. It continues the Manipulate the Imported Tables task.

Perform the following to assign file names.

  1. In the Assign File Names screen, enter the name of an existing physical VSAM file for each table listed.

    Oracle Studio for IMS, VSAM, and Adabas Gateways needs the physical VSAM file name to be able to create a list of its alternate indexes in the Assign Index File Names task.

  2. Specify the CICS logical file name for each physical file.

  3. Click Next to continue to the Assign Index File Names task.

    The Assign Index File Names screen opens.

Figure 2-20 shows the Assign File Names screen.

Figure 2-20 Assign File Names Screen

The Assign Index File Names screen.
Description of "Figure 2-20 Assign File Names Screen"

Assign Index File Names

This section describes the steps required to specify the logical CICS names of the alternate indexes. It continues the Create VSAM Indexes task.

To assign the logical CICS index names

  1. Specify the logical CICS alternate index name for each alternate index listed.

  2. Click Next to continue to the Select the Metadata Model task.

    The Import Metadata screen opens.

Figure 2-21 shows the Assign Index File Names screen.

Figure 2-21 Assign Index File Names Screen

Assign Index File Names screen
Description of "Figure 2-21 Assign Index File Names Screen"

Select the Metadata Model

This screen allows you to generate virtual and sequential views for tables containing arrays. In addition, you can configure the properties of the generated views.

This screen continues the Assign Index File Names procedure.

In the Metadata Model Selection screen, you can select default values that apply to all tables or set specific settings per table. This screen inherits its default settings from the settings that you defined for the virtual view policy when you configured the data source properties (see Configuring the VSAM or VSAM (CICS) Data Source Properties).

Note:

Selecting the Default values for all tables check box discards any table-specific settings that you set.

Perform the following steps to select the metadata model.

  1. Select from the following:

    • Default values for all tables: Select this option if you want to apply the same values to all tables to be imported.

      • Generate sequential view: Select this option if you want to map a non-relational file to a single table.

      • Generate virtual views: Select this option if you want to have an individual table created for every array in the non-relational file.

      • Virtual views include row number: Select this option if you want to include a column that specifies the row number in the virtual or sequential view.

      • Virtual views inherit all parent columns: Select this option if you want the virtual views to include all the columns of the parent record.

    • Specific virtual array view settings per table: Select this option if you want to apply different values to the tables to be imported. Then select the relevant check boxes.

      Note:

      After importing a table, you can change these settings on the Modeling tab of the table editor.
  2. Click Next to continue to the Import the Metadata task.

Figure 2-11 shows the Metadata Model Selection screen.

Figure 2-22 Metadata Model Selection Screen

The image shows the Metadata Model Selection screen.
Description of "Figure 2-22 Metadata Model Selection Screen"

Import the Metadata

This section describes the steps required to import the metadata to the target computer. It continues the Assign Index File Names task.

You can now import the metadata to the computer where the data source is located, or import it later (in case the target computer is not available).

Perform the following to import the mentadata.

  1. Select Yes to transfer the metadata to the target computer or No to transfer the metadata later.

  2. Click Finish.

If you selected Yes, the metadata is imported to the target computer immediately.

Figure 2-12 shows the Import Metadata screen.

Figure 2-23 Import Metadata Screen

Import Metadata screen: Decide when to import metadata.
Description of "Figure 2-23 Import Metadata Screen"