2 Configuring the OracleAS Adapter for IMS/DB

This chapter describes how to configure Oracle Connect using Oracle Studio.

All modeling of Oracle Connect is performed using Oracle Studio. To use Oracle Studio, you first configure it to enable access to the IBM z/OS platform where the IMS/DB data resides.

This chapter contains the following topics:

Note:

The following tasks assume you have permission to access the IBM z/OS platform and that the Oracle Connect daemon is running on this computer.

Check with the system administrator to ensure these requirements are fulfilled.

Setting Up the IBM z/OS Platform in Oracle Studio

Using Oracle Studio, perform the following steps to configure the IBM z/OS platform:

  1. From the Start menu, select Programs, Oracle, and then select Studio. Oracle Studio opens, displaying the Design perspective.

  2. Right-click Machines in the Configuration Explorer and select Add Machine. The Add Machine screen is displayed (see Figure 2-1).

  3. Enter the name of the computer you want to connect to, or click Browse to select the computer from the list of computers that is displayed and which use the default port (2551).

  4. Specify the user name and password of the user who was specified as the administrator when Oracle Connect was installed.

    Note:

    Selecting Anonymous connection enables anyone having access to the computer to be an administrator, if this was defined for the computer.

    The Add Machine screen is shown in the following figure:

    Figure 2-1 The Add Machine screen

    The Add Machine screen, used to define new machines
    Description of "Figure 2-1 The Add Machine screen"

  5. Click Finish.

    The computer is displayed in the Configuration Explorer.

Securing Access to Oracle Connect

Oracle Studio includes mechanisms to secure access to Oracle Connect both during modeling and at run time.

During modeling, the following security mechanisms can be applied:

At run time client access to Oracle Connect is provided by the user profile:

Setting Password Access to Oracle Studio

Initially, any operation performed using Oracle Studio does not require a password. You can set a password so that the first operation that involves accessing the server from Oracle Studio requires a password to be entered.

Perform the following steps to set password access to Oracle Studio:

  1. From the Start menu, select, Programs, Oracle, and then select Studio. Oracle Studio opens.

  2. From the Window menu, select Preferences. The Preferences screen is displayed.

  3. Select Studio as shown in the following figure:

    Figure 2-2 The Preferences screen

    Studio Preferences screen, used to set preferences
    Description of "Figure 2-2 The Preferences screen"

  4. Click Change Studio Master Password. The Change Master Password screen is displayed, as shown in the following figure:

    Figure 2-3 The Change Master Password Screen

    This screen is used to set or change the master password
    Description of "Figure 2-3 The Change Master Password Screen"

  5. Leave the Enter Current Master Password field blank and type a new master password.

  6. Confirm the password.

  7. Click OK.

Specifying Users with Administrative Rights

By default, only the user who was specified during the installation as an administrator has the authorization to modify settings on that computer from Oracle Studio. This user can then authorize other users to make changes or to view the definitions for a selected computer. Adding a computer to Oracle Studio is described in "Setting Up the IBM z/OS Platform in Oracle Studio".

Note:

The default during installation is to enable all users to be administrators.
  1. From the Start menu, select, Programs, Oracle, and then select Studio. Oracle Studio opens.

  2. In the Design perspective Configuration view, Right-click the computer and select Administration Authorization.

    The Administration Authorization screen is displayed as shown in the following figure:

    Figure 2-4 The Administration Authorization Tab

    The Administration authorization screen
    Description of "Figure 2-4 The Administration Authorization Tab"

    The screen has the following sections:

    Administrators: Administrators can view and modify all the definitions in Oracle Studio for the selected computer. On initial entry to Oracle Studio, every user is defined as a system administrator.

    Designers: Designers can view all the definitions for the computer in Oracle Studio and can modify any of the definitions under the Bindings and Users nodes for the selected computer. For example, Oracle Studio database administrator can add new data sources and adapters and can change metadata definitions for a table in a data source.

    Users: Users can view all the definitions for the computer in Oracle Studio for the selected computer. Regular users cannot modify any of the definitions.

  3. Add users or groups of users by clicking Add User or Add Group for the relevant sections.

    The user or group that is added must be recognized as a valid user or group for the computer. Once a name has been added to a section, only the user or group that logs on with that user name has the relevant authorization.

Setting Up Run-Time User Access to the IBM z/OS Platform

During run time, client access to Oracle Connect is provided by the user profile. A user profile contains name and password pairs that are used to access a computer, data source or application at run time, when anonymous access is not allowed.

  1. From the Start menu, select, Programs, Oracle, and then select Studio. Oracle Studio opens.

  2. From the Design perspective, Configuration view, expand the Machines folder, then expand the machine where you want to set the user name and password.

  3. Expand Users.

  4. Right-click the NAV user profile and select Edit User. The NAV user profile editor is displayed as shown in the following figure:

    Figure 2-5 The User Editor pane

    The user editor pane
    Description of "Figure 2-5 The User Editor pane"

  5. In the User editor, click Add. The Add Authenticator screen is displayed as shown in the following figure:

    Figure 2-6 The Add Authenticator screen

    The Add Authenticator screen
    Description of "Figure 2-6 The Add Authenticator screen"

  6. Select Remote Machine from the Resource Type list.

  7. Enter the name of the IBM z/OS computer defined in Oracle Studio.

  8. Enter the name and password used to access the computer and confirm the password.

  9. Click OK.

Modeling Interactions for OracleAS Adapter for IMS/DB

Modeling interactions for OracleAS Adapter for IMS/DB involves defining an Oracle Connect back-end adapter using Oracle Studio. All the definitions specified in Oracle Studio are written to the IBM z/OS platform.

This section contains the following:

Setting Up the IMS/DB Data Source

Oracle Connect requires you to specify the IMS/DB data source as the first step in setting up the adapter.

Do the following to set up the IMS/DB data source:

  1. From the Start menu, select, Programs, Oracle, and then select Studio.

  2. In the Design perspective, Configuration view, expand the Machine folder.

  3. In the Configuration Explorer, expand the node of the computer defined in "Setting Up the IBM z/OS Platform in Oracle Studio".

  4. Expand the Bindings folder. The binding configurations available on this computer are listed.

  5. Expand the NAV binding. The NAV binding configuration includes folders for data sources and adapters that are located on the computer.

  6. Right-click Data sources and select New data source, to open the New data source wizard.

  7. In the New dialog box, enter a name for the IMS/DB data source. The name can contain letters and numbers and the underscore character only.

  8. Select the data source type from the Type list, as follows:

    • If you are accessing IMS/DB data under CICS, then select IMS-DBCTL

    • If you are accessing IMS/DB data under IMS/TM, then select IMS-DBDC

    • If you are accessing IMS/DB data directly, then select IMS-DLI

      Note:

      Only use the IMS-DLI option to connect directly to the IMS/DB data in the following circumstances:
      • The IMS/DB records are not managed by CICS or by IMS/TM.

      • The IMS/DB records are required for read-only purposes and changes to the data buffered by CICS or IMS/TM while reading the data, are not expected.

    The New Data Source screen is shown in the following figure:

    Figure 2-7 The New Data Source screen

    Adding an IMS/DB data source
    Description of "Figure 2-7 The New Data Source screen"

  9. Click Next. The Data Source Connect String screen for the selected data source type is displayed.

  10. Enter the connect string for the selected data source, as follows:

    If you select IMS-DBCTL, then the following screen is displayed:

    Figure 2-8 The Data Source Connect String screen (for IMS-DBCTL)

    The data source connect string information
    Description of "Figure 2-8 The Data Source Connect String screen (for IMS-DBCTL)"

    Where:

    • PSB Name: Specify the name of the PSB file that contains details of all the IMS/DB databases to access.

    • Target System: Specify the VTAM APPLID of the CICS target system. The default value is CICS. This parameter is used when updating IMS/DB data. You can determine this value by activating the CEMT transaction on the target CICS system. The legend APPLID=target_system appears in the bottom right corner of the screen.

    • 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

      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 select IMS-DBDC, then the following screen is displayed:

    Figure 2-9 The Data Source Connect String screen (for IMS/TM)

    The connect string.
    Description of "Figure 2-9 The Data Source Connect String screen (for IMS/TM)"

    Where:

    • XCF group: The Cross System Coupling Facility collection of XFC members the connection belongs to. A group may consist of up to eight characters, and may span between multiple systems.

    • XCF member: The Cross System Coupling Facility group member.

    • Tpipe prefix: The transaction pipe prefix used to associate between the transaction and the transaction pipe it is using. The default value is ATTU.

    • User name: The security facility user identification.

    • Group name: The security facility group identification.

    • imsTrans name: The name of the IMS transaction that points to the program that is used to access the PSB used to access the IMS/DB data. The default name of the transaction is ATYIMSTM.

    If you select IMS-DLI, then no connection information is required.

  11. Click Finish. The new data source is displayed in the Configuration Explorer.

Configuring the Data Source Driver

After setting up the data source, you can set its driver properties according to specific requirements, as follows:

  1. Right-click the required data source in the Configuration Explorer and select Edit Data source.

  2. Click the Properties tab.

    For IMS/DB under CICS, the following configuration properties are available:

    • cicsProgname: This parameter specifies the ATYDBCTL program that is supplied with Oracle Connect to enable updating the IMS data source. To use the ATYDBCTL program, copy the program from NAVROOT.LOAD to a CICS DFHRPL library (such as CICS.USER.LOAD) and then define the ATYDBCTL program under CICS using any available group such as ATY group:

      NAVROOT is the high-level qualifier where Oracle Connect is installed.

      After defining the ATYDBCTL program to a group, install it as follows:

      CEDA IN G(ATY)

    • cicsTraceQueue: This parameter specifies the e name of queue for output which is defined under CICS when tracing the output of the ATYDBCTL 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: This parameter specifies the CICS TRANSID. This value must be EXCI or a copy of this transaction.

    • pbsName=string: The PSB Name in the connect string, this parameter contains details of all the IMS/DB databases to access.

    • targetSystemApplid: The Target System in the connect string, this parameter specifies the 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. The legend APPLID=target_system appears in the bottom right corner of the screen.

    • vtamNetname: 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. The default value is ATYCLIEN.

    For IMS/DB under IMS/TM, the following configuration properties are available:

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

    • imsTransname: This parameter specifies the name of the IMS transaction that points to the program that is used to access the PSB used to access the IMS/DB data. The default name of the transaction is ATYIMSTM.

    • maxSessions: This parameter specifies the maximum number of sessions allowed. The default value is 5.

    • racfGroupId: This parameter specifies the security facility group identification (for example, the RACF group identification).

    • racfUserId: This parameter specifies the security resource user name.

    • tpipePrefix: The TPipe prefix in the connect string, this parameter is used to associate between the transaction and the transaction pipe it is using. The default is ATTU.

    • xcfClient: This parameter specifies the client name for the Cross System Coupling Facility to which the connection belongs.

    • xcfGroup: The XCF group in the connect string, this parameter specifies the Cross System Coupling Facility collection of XCF members the connection belongs to. A group may consist of up to eight characters, and may span between multiple systems.

    • xcflmsMember: This parameter specifies the Cross System Coupling Facility group member.

    • xcfServer: The XCF server in the connect string, this parameter specifies the Cross System Coupling Facility group member.

    For IMS/DB direct, the following configuration property is available:

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

  3. Click Save to save the changes you made to the configuration properties.

Configuring the Data Source Driver Advanced Properties

You configure the advanced properties for a data source in the Advanced tab of the data source editor. The advanced settings are the same for every data source. Advanced settings let you do the following:

  • Define the transaction type

  • Edit the syntax name

  • Provide a table owner

  • Determine if a data source is updatable or readable

  • Provide repository information

  • Set the virtual view policy

Use the following procedure to configure the data source advanced features.

  1. Open Oracle Studio.

  2. In the Design Perspective Configuration View, expand the Machine folder and then expand the machine where you want to configure the data source.

  3. Expand the Data sources folder, right click the data source you are configuring, then select Open.

  4. Click the Advanced tab and make changes. The table below describes the available fields:

Table 2-1 Data Source Advanced Configuration

Field Description

Properties

 

Transaction type

The transaction level (0PC, 1PC or 2PC) that is applied to this data source, no matter what level the data source supports. The default is the data source's default level.

Syntax name

A section name in the NAV.SYN file that describes SQL syntax variations. The default syntax file contains the following predefined sections:

  • OLESQL driver and the SQL Server 7 OLE DB provider (SQLOLEDB):

    syntaxName="OLESQL_SQLOLEDB"

  • OLESQL driver and JOLT:

    syntaxName="OLESQL_JOLT"

  • Rdb driver and Rdb version:

    syntaxName="RDBS_SYNTAX"

  • ODBC driver and EXCEL data:

    syntaxName="excel_data"

  • ODBC driver and SQL/MX data:

    syntaxName="SQLMX_SYNTAX"

  • ODBC driver and SYBASE SQL AnyWhere data:

    syntaxName="SQLANYS_SYNTAX"

  • Oracle driver and Oracle case sensitive data:

    syntaxName="ORACLE8_SYNTAX" or,

    syntaxName="ORACLE_SYNTAX"

    For case sensitive table and column names in Oracle, use quotes (") to delimit the names. Specify the case sensitivity precisely.

Default table owner

The name of the table owner that is used if an owner is not indicated in the SQL

Read/Write information

Select one of the following:

  • Updatable data: Select this to update the data on the data source.

  • Read only data: Select this to allow users to only view the data on the data source.

Repository Directory

 

Repository directory

Enter the location for the data source repository.

Repository name

Enter the name of a repository for a data source. The name is defined as a data source in the binding configuration. It is defined as the type Virtual and is used to store Oracle Connect views and stored procedures for the data source, if required instead of using the default SYS data.

Virtual View Policy

 

Generate sequential view

Select this to map a non-relation file to a single table. This parameter is valid only if you are using virtual array views.

Generate virtual views

Select this to have an individual table created for every array in the non-relational file. This parameter is valid only if you are using virtual array views.

Include row number column

Select this to include a column that specifies the row number in the virtual or sequential view. This parameter is valid only if you are using virtual array views.

All parent columns

Select this for virtual views to include all the columns in the parent record. This parameter is valid only if you are using virtual array views.


Importing Metadata for the IMS/DB Data Source

Oracle Connect requires metadata describing the IMS/DB data source records and the fields in these records. Use the Import Metadata procedure in Oracle Studio Design perspective to import metadata for the IMS/DB data source from DBD, COBOL copybooks and PSB files, which describe the data.

The following information is needed during the import procedure:

  • DBD files: These files are copied to the computer running Oracle Studio as part of the import procedure.

  • COBOL copybooks: These copybooks are copied to the computer running Oracle Studio as part of the import procedure.

  • PSB file: This file is copied to the computer running Oracle Studio as part of the import procedure.

The metadata import procedure has the following steps:

Selecting the Imput Files

  1. From the Start menu, select, Programs, Oracle, and then select Studio.

  2. In the Configuration view, expand the computer defined in "Setting Up the IBM z/OS Platform in Oracle Studio".

  3. Expand Bindings. The binding configurations available on this computer are listed.

  4. Expand NAV binding.

  5. Expand Data sources.

  6. Right-click the IMS/DB data source defined in Setting Up the IMS/DB Data Source.

  7. Select Show in Metadata View, to open the Metadata tab, with the IMS/DB data source displayed under the data sources list.

  8. Right-click the IMS/DB data source and select New Import.

    The New Import screen is displayed.

  9. Enter a name for the import. The name can contain letters and numbers and the underscore character only.

  10. From the Import Type list select, there should be only one choice in the list, which depends on the type of IMS data source that you selected. The New Import wizard is shown in the following figure:

    Figure 2-10 The Metadata Import dialog box

    The selected IMS/DB
    Description of "Figure 2-10 The Metadata Import dialog box"

  11. Click Finish. The Metadata Import wizard opens.

  12. Click Add in the Import Wizard to add DBD files. The Add Resource screen is displayed, providing the option of selecting files from the local machine or copying the files from another machine.

    Figure 2-11 The Select Resources screen

    The added machine.
    Description of "Figure 2-11 The Select Resources screen"

  13. Click Add.

    The Select Resources screen is displayed, which provides the option to select files from the local computer or copy the files from another computer.

  14. If the files are on another computer, right-click My FTP Sites and select Add. Optionally, double-click Add FTP site. The Add FTP Site screen is displayed.

  15. Set the FTP data connection by entering the server name where the DBD files reside and, if not using anonymous access, enter a valid user name and password to access the machine.

  16. To browse and transfer files required to generate the metadata, access the machine using the user name as the high-level qualifier.

    After accessing the machine, you can change the high-level qualifier by right-clicking the machine and selecting Change Root Directory.

  17. Select the files to import and click Finish to start the transfer.

  18. Repeat the procedure for COBOL copybooks.

    The format of the COBOL copybooks must be the same. For example, you cannot import a COBOL copybook that uses the first six columns with a COBOL copybook that ignores the first six columns. In this type of case, repeat the import process.

    You can import the metadata from one COBOL copybook and later add to this metadata by repeating the import using different COBOL copybooks.

  19. Click Add in the Import wizard to add a PSB file, if necessary.

    The selected files are displayed in the Get Input Files screen, as shown in the figure below.

    Figure 2-12 Get Input Files Screen

    The image shows the Get Input Files screen
    Description of "Figure 2-12 Get Input Files Screen"

  20. Click Next to go to the Applying Filters step.

Applying Filters

This section describes the steps required to apply filters on the COBOL Copybook files used to generate the Metadata. It continues the Selecting the Imput Files step.

Perform the following steps to apply filters.

  1. Apply filters to the copybooks, as needed.

    The following is theApply Filters editor.

    Figure 2-13 Apply Filters Screen

    Apply Filters Wizard
    Description of "Figure 2-13 Apply Filters Screen"

    The following COBOL filters are available:

    • 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: The compiler vendor.

    • Storage mode: The MicroFocus Integer Storage Mode. Specify either NOIBMCOMP for byte storage mode or IBMCOMP for word storage mode.

    • Ignore after column 72: Ignore columns 73 to 80 in the COBOL copybooks.

    • Ignore first 6 columns: Ignore the first six columns in the COBOL copybooks.

    • Prefix nested column: Prefix all nested columns with the previous level heading.

    • Replace hyphens (-) in record and field names with underscores (_): A hyphen, which is an invalid character in Oracle metadata, is replaced with an underscore.

    • Case sensitive: Specifies whether to consider case sensitivity or not.

    • Find: Searches for the specified value.

    • Replace with: Replaces the value specified for in the Find field with the value specified here.

    The following DBD filters are available:

    • Ignore after column 72: Ignore columns 73 to 80 in the COBOL copybooks.

    • Ignore first 6 columns: Ignore the first six columns in the COBOL copybooks.

    • Ignore labels: Ignore labels in the DBD files.

    The following PSB filters are available:

    • Ignore after column 72: Ignore columns 73 to 80 in the COBOL copybooks.

    • Ignore first 6 columns: Ignore the first six columns in the COBOL copybooks.

  2. Click Next to go to the Selecting Tables step.

Selecting Tables

This section describes the steps required to select the tables from the COBOL Copybooks.

The following procedure continues the Applying Filters step. Perform these steps to select the tables.

  1. From the Select Tables screen, select the tables to access. To select all tables, click Select All. To clear all the selected tables, click Unselect All.

    The Select Tables screen is shown in the following figure:

    Figure 2-14 Select Tables Screen

    Select Tables Screen
    Description of "Figure 2-14 Select Tables Screen"

    The import manager identifies the names of the segments in the DBD files that are imported as tables.

  2. Click Next (the Import Manipulation screen opens) to continue to the Matching DBD to COBOL step.

Matching DBD to COBOL

This step lets you match the DBD file to your COBOL copybook. It is a continuation of the Selecting Tables step. The following figure shows the DBD to COBOL step that is displayed in the Editor.

Figure 2-15 Match DBD to COBOL Screen

Matching DBD files to COBOL copybooks
Description of "Figure 2-15 Match DBD to COBOL Screen"

  1. Match each table selected from the DBD file with the COBOL copybook that contains the relevant table structure. Select the files and tables from the dropdown lists for each DBD entry.

  2. Click Next (the Import Manipulation screen opens) to continue to the Import Manipulation step.

Import Manipulation

This section describes the operations available for manipulating the imported records (tables). It continues the Matching DBD to COBOL step.

The import manager identifies the names of the records in the DDM Declaration files that are 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 more information, see the Import Manipulation Screen figure), right-click the table record marked with a validation error, and select the relevant operation. For more information, see the table, Table Manipulation Options for the available operations.

  2. Repeat step 1 for all table records 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 Metadata Model Selection.

Import Manipulation Screen

The Import Manipulation screen is shown in the following figure:

Figure 2-16 Import Manipulation Screen

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

The upper area of the screen lists the DDM Declaration files and their validation status. The metadata source and location are also listed.

The Validation tab at the lower area of the screen displays information about what must 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 are determined by the array dimension.

    • Creating arrays and setting the array dimension.

The following table lists and describes the available operations when you right-click a table entry:

Table 2-2 Table Manipulation Options

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 through the Import Manipulation Screen. The Field Manipulation screen is shown in the following figure.

Figure 2-17 Field Manipulation Screen

This image shows the field manipulation screen
Description of "Figure 2-17 Field Manipulation Screen"

You can perform 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.

Table 2-3 Field Manipulation Screen Commands

Command Description

General menu

 

Undo Field manipulation undo button

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

Select fixed offset Field manipulation select fixed offset button

The offset of a field is usually calculated dynamically by the server at run time according 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 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 Field manipulation Test import tables button

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 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 in the middle of the screen, you can add the operator, value and other information. Click 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 lets you 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 Field manipulation Set field attribute button

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 Field manipulation Add button

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 Field manipulation Delete field button

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 Field manipulation Move up or down button

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.

Sturctures menu

 

Columnwise Normalization

Select Columnwise Normalization to create new fields instead of the array field where the number of generated fields are 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:

Field manipulation Combining sequential fields dialog box

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. Ensure 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 are 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.

Metadata Model Selection

This section lets you generate virtual and sequential views for imported tables containing arrays. In addition, you can configure the properties of the generated views. It continues the Import Manipulation procedure. This lets you flatten tables that contain arrays.

In the Metadata Model Selection step, you can select configure values that apply to all tables in the import or set specific settings for each table. The following describes how to configurations available in the The Metadata Model Selection editor.

  • Select one of the following:

    • Default values for all tables: Select this to configure the same values for all the tables in the import. Make the following selections when using this option:

      • Generate sequential view: Select this to map non-relational files to a single table.

      • Generate virtual views: Select this to have individual tables created for each array in the non-relational file.

      • Include row number column: Select one of the following:

        true: Select true, to include a column that specifies the row number in the virtual or sequential view. This is true for this table only, even in the data source is not configured to include the row number column.

        false: Select false, to not include a column that specifies the row number in the virtual or sequential view for this table even if the data source is configured to include the row number column.

        default: Select default to use the default data source behavior for this parameter.

        For information on how to configure these parameters for the data source, see Configuring the Data Source Driver Advanced Properties.

      • Inherit all parent columns: Select one of the following:

        true: Select true, for virtual views to include all the columns in the parent record. This is true for this table only, even in the data source is not configured to include all of the parent record columns.

        false: Select false, so virtual views do not include the columns in the parent record for this table even if the data source is configured to include all of the parent record columns.

        default: Select default to use the default data source behavior for this parameter.

        For information on how to configure these parameters for the data source, see Configuring the Data Source Driver Advanced Properties.

    • Specific virtual array view settings per table: Select this to set different values for each table in the import. This overrides the data source default for that table. Make the selections in the table under this selection.

When you are finished, click Next to go to the Import the Metadata step.

The Metadata Model Selection editor is shown in the following figure:

Figure 2-18 The Metadata Model Selection editor

The image shows the Metadata Model Selection screen.
Description of "Figure 2-18 The Metadata Model Selection editor"

Import the Metadata

This section describes the steps required to import the metadata to the target computer. It continues the Metadata Model Selection step.

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 transfer the metadata.

  1. Select Yes to immediately transfer the metadata from the Windows computer to the z/OS platform, or No to transfer the metadata later.

    The metadata is imported based on the options selected in the previous steps and it is stored on the IBM z/OS platform computer. An XML representation of the metadata is generated. This XML file can be viewed by expanding the Output node.

  2. Click Finish.

After performing the import, you can view the metadata in the Metadata tab in Oracle Studio Design perspective. You can also make any fine adjustments to the metadata and maintain it, as necessary.

See Also:

Metadata for the IMS/DB Data Source for details about the data source metadata.

The Import Metadata screen is shown in the following figure:

Figure 2-19 The Import Metadata screen

This image shows the Import Metadata screen
Description of "Figure 2-19 The Import Metadata screen"

After performing the import, you can view the metadata in the Metadata tab in Oracle Studio. You can also make any fine adjustments to the metadata and maintain it, as necessary.

Setting Up an Oracle Connect Adapter

To work with the Oracle Connect against the IMS/DB data source from Oracle Application Server, you must set up an adapter definition on the IBM z/OS platform to handle the interactions to and from the IMS/DB data.

Perform the following steps to set up the adapter:

  1. From the Start menu, select, Programs, Oracle, and then select Studio.

  2. From the Design perspective, Configuration view expand the Machines folder.

  3. In the Configuration view, expand the computer defined in "Setting Up the IBM z/OS Platform in Oracle Studio".

  4. Expand the Bindings folder.

  5. Expand the NAV binding.

  6. Right-click Adapters and select New Adapter to open the New Adapter wizard.

  7. Enter a name for the back-end adapter.

    Note:

    The word event is a reserved word and cannot be used when naming an adapter.
  8. Select Database as the back-end adapter type from the Type list. The Database adapter enables accessing the IMS/DB data source from Oracle Application Server.

  9. Select Events.

  10. Click Finish. The back-end adapter is added to the Configuration view and the Configuration properties open in the Editor.

    Note:

    Other adapters that are displayed in the Type list are not supported with the version of Oracle Connect installed at the site.
  11. You can change any of the properties for the adapter, as required.

    The Properties tab is shown in the following figure:

    Figure 2-20 The Properties tab

    The Database adapter properties.
    Description of "Figure 2-20 The Properties tab"

    The following properties are available:

    Table 2-4 Adapter Properties

    Property Description

    ConnectString

    Leave this value blank.

    DefaultDatasource

    The name of the data source defined in Oracle Studio to access with the Database adapter.

    For example, Legacy.

    multipleResults

    Leave this value as true.


Generating Outbound Interactions

Oracle Connect requires metadata describing the adapter interactions, including the structures used to pass information to and from the adapter.

Use the Metadata Import wizard in Oracle Studio to generate interaction metadata, as follows:

  1. From the Start menu, select, Programs, Oracle, and then select Studio.

  2. In the Design perspective, Configuration view expand the Machines folder.

  3. In the Configuration view, expand the computer defined in "Setting Up the IBM z/OS Platform in Oracle Studio".

  4. Expand the Bindings folder. The binding configurations available on this computer are listed.

  5. Expand the NAV binding.

  6. Expand the Adapters folder.

  7. Right-click the Database back-end adapter defined in "Setting Up an Oracle Connect Adapter".

  8. Select Show Metadata View to open the Metadata tab, with the database back-end adapter displayed under the Adapters list.

  9. Right-click Interactions and select New to open the New Interaction wizard. The wizard opens with the following options displayed:

    • Automatic: Four interactions are generated for each IMS/DB table, enabling to execute the SELECT, INSERT, UPDATE, DELETE command.

    • Manual: One interaction is generated, based on the type of SQL selected such as database query (a SELECT statement) or Database Modification (an INSERT, UPDATE, or DELETE statement).

      Note:

      IMS/DB does not support the stored procedure option.
  10. Select how you want to generate interactions (Automatic or Manual).

    If you select Automatic generation, perform the following steps:

    1. Click Next. The Select Tables screen opens, enabling you to add tables from the IMS/DB data source to access with the interaction.

    2. Click Add to include tables.

      The data sources that have been defined and all the tables, for each data source, that have had metadata defined for them are displayed.

      Select the tables to access with the interaction and click the right-pointing arrow to move these tables to the right-hand pane.

    3. Click Finish. The selected tables are displayed.

    4. Click Finish. Four interactions are generated for each table selected (SELECT, INSERT, UPDATE, DELETE) with the record structures to support the interactions and the responses from the IMS/DB data source.

    5. Click Yes to complete the task. The interactions and the record structures that relate to the interactions are displayed in the Metadata tab.

    If you select Manual generation, perform the following steps:

    1. Select the type of SQL (query or modification) for the interaction and click Next. The Interaction Name screen is displayed.

    2. Enter a name for the interaction, and select Create new query.

      Note:

      The option to use a previously saved query is not applicable.
    3. Click Next. The Define Interaction screen is displayed enabling you to build the query.

      Note:

      If the database query option was selected in step a, then the Define Interaction screen is displayed, enabling you to build a SELECT statement only, as indicated in the Query type field. If the database modification option was selected, then this field enables you to select the required SQL modification statement from a list (INSERT, UPDATE, or DELETE).
    4. Click Next. The Interaction Properties screen is displayed, enabling you to define the interaction parameters. You can set the following interaction prameters:

      Table 2-5 Interaction Definition Parameters

      Parameter Description

      passThrough

      Defines whether the query is passed directly to the back-end database for processing or processed by the Query Processor.

      Reuse compiled query

      Defines whether the query is saved in cache for reuse.

      Encoding

      Sets the encoding method used to return binary data in text format. You can select between the base 64 and the hexadecimal encoding methods.

      Event

      Defines whether the interaction mode is sync-send or sync-receive.

      Fail on no rows return

      Defines whether an error is returned in case no data is returned

      Root element

      Defines the root element name for records returned by the query, using the <root> \ <record> format.

      Record element

      Defines the record element name for records returned by the query, using the <root> \ <record> format.

      Max. records

      Sets the maximum number of record returned by the query.

      Null string

      Sets the string returned for a null value. If not specified, the column is skipped.


    5. Click Next. The Interaction Parameters screen is displayed, where you specify input parameters for the interaction. The following parameters are specified:

      Table 2-6 Interaction Parameters

      Parameter Description

      Name

      The name of the parameter.

      Type

      The type of parameter (such as string, number, binary).

      Nullable

      The nullable value (true or false).

      Default

      The default value for the parameter.

      Context Field

      This field is not applicable.

      Bind to Sqls

      This field is not applicable.


    6. Click Finish to generate the interaction, including the record schema required to support the interaction input and output.

See Also:

"Adapter Metadata" for details about the data source metadata.

Modifying Existing Interactions

You can modify the interaction definitions to the exact requirements of the application, in the Design perspective Metadata tab.

The following example uses the DELETE interaction, generated in the previous task, to describe how the interactions can be modified:

Note:

The interaction modification procedure is the same for all types of SQL statements (INSERT and UPDATE) as described, using a DELETE SQL statement.
  1. In the Metadata tab, right-click the interaction to modify and select Open.

    The adapter metadata editor opens, displaying the General tab.

    The General tab displays general information about the way the interaction is executed. You can add a description of the interaction and define the mode of operation for the interaction. The following modes are available:

    • sync-send-receive: The interaction sends a request and expects to receive a response.

    • sync-send: The interaction sends a request and does not expect to receive a response.

    • sync-receive: The interaction expects to receive a response.

    The information for a request is passed in the input record. The information for the response from the IMS/DB data source is passed in the output record.

    For more information, see "Adapter Metadata General Properties".

  2. Click the Advanced tab to display specific information about the interaction.

    As required, change the SQL and the parameters associated with the SQL.

    Parameters are specified in a SET clause or in a WHERE clause with the following format:

    :parameter_name
    

    For more information, see "Interaction Advanced Tab"

  3. Depending on the changes made to the SQL, when you close the editor, or when clicking Save, the Context Selection screen is displayed.

    Select the required adapter from the Adapters list and select Update interaction-related records. Any changes that need making to the record structures in the schema part of the metadata are done automatically.

    The Context Selection screen is shown in the following figure:

    Figure 2-21 The Context Selection screen

    Selecting the context.
    Description of "Figure 2-21 The Context Selection screen"

    Note:

    The interaction records are built based on all the fields in the table and cannot be changed manually, even if you change the SQL so that less fields are involved.
  4. Click Finish to implement the modifications made to the interaction definitions.