A
The Oracle Connect VSAM Metadata Definitions

Oracle Studio includes an import utility to generate metadata for the VSAM data source, using COBOL copybooks. Once imported, the metadata is maintained in the Metadata perspective of Oracle Studio.

Oracle Studio also includes a wizard to help define the adapter inbound and outbound interactions and input and output structures used for these interactions. The interactions and input and output structures are maintained as metadata in the Metadata perspective of Oracle Studio.

This appendix contains the following sections:

• Metadata for the VSAM Data Source

  Note: The Oracle Application Server Integration Adapter for VSAM provides a relational model for the VSAM data. Thus, relational terminology is used. For example, the metadata for a VSAM record is referred to as table metadata.

• Interaction Metadata for the Adapter

Metadata for the VSAM Data Source

Use Oracle Studio to maintain the metadata for the VSAM data source.

1. On the machine where Oracle Studio is installed, from the Start menu, choose Start > Programs > Oracle > Studio > Studio.

2. Select the machine defined in "Configuring Oracle Connect for VSAM".

3. Click the + next to the Bindings node.

4. Click the + next to the NAV binding node.

5. Click the + next to the Data sources node to display the data sources.

6. Right-click the VSAM data source in the Configuration Explorer and choose Edit Metadata to open the Metadata perspective.

   

   Text description of the illustration md_persp.gif

7. Right-click the table in the Metadata Explorer you want to view and choose Edit.

The metadata editor opens, displaying the General tab, with general table details. The following tabs are used to edit the metadata:

• General Tab

• Columns Tab

• Indexes Tab

• Statistics Tab

The following tab provides an XML representation of the data source metadata:

• Source Tab

General Tab

Use the General tab to maintain information about the whole table, such as the table name and the way the table is organized.

Text description of the illustration addgen.gif

The General tab has the following fields:

Table name - The name of the table.

Comment - An optional description of the table.

Data File - The location of the file containing the table.

Organization - How the record represented by the table is organized. The options that are displayed depend on the record. The following options are available:

• Index

• Sequential

• Relative - used with RRDS files. Access to a specific record number of a relative file is performed by using a pseudo column to specify the record position. The hash symbol (#) is used to specify a pseudo column. For example:

  SELECT * FROM colleges WHERE # = 6
  
  

Record format - How the record represented by the table is formatted. The options that are displayed depend on the record. The VSAM records have a fixed format.

Maximum record length - How the record represented by the table is formatted:

Filter Expression - A WHERE clause. This clause is added to every query accessed using this metadata. The filter is specified without the WHERE keyword. Specify a filter when more than one logical table is stored in the same physical file.

VSAM specific commands are displayed in the DB Command window, which you open by clicking DB Command.

Text description of the illustration adddbcmd.gif

The information displayed is generated automatically when the metadata is generated and should not be modified.

Columns Tab

Use the Columns tab to specify metadata describing the columns of the table.

Text description of the illustration addcol.gif

The Columns tab has the following fields:

Column Name - The name of the column.

Data Type - The datatype of the data the column contains. Selecting this field displays a list of the possible datatypes.

See Also: "VSAM Data Source Metadata Datatypes" for details about the available datatypes

Size - The size allocated for the column.

Scale - The definition depends on the datatype. For decimal datatypes this value is the number of digits to the right of the decimal place. This number must not be greater than the number of digits. The default value is 0. For scaled datatypes this value is the total number of digits. The number must be negative.

Offset - An absolute offset for the field in a record.

The Column Properties relate to the selected column.

Alias - A name used to replace the default virtual table name for an array. Virtual table names are generated by appending the array name to the record name. Thus, when an array includes another array the name of the nested array is the name of the record and the parent array and the nested array. When the default generated virtual table name is too long to be usable, specify an Alias to replace the long name.

Array dimension - The maximum number of occurrences of the group of columns that make up the array.

Comment - A comment about the specific column.

OnBit - The position of the bit in a BIT field and the starting bit in a BITS field.

Empty value - The value for the field during an insert operation, when a value is not specified

Null value - The null value for the field during an insert operation, when a value is not specified.

DBcommand - VSAM-specific commands for the column. The information displayed is generated automatically when the metadata is generated and should not be modified.

Nullable - The current field can contain NULL values.

Updateable - The current field can be updated.

Explicit select - The current field is not returned when you execute a SELECT * FROM... statement. To return this field, you must explicitly ask for it (in a query such as SELECT NATION_ID, SYSKEY FROM NATION where SYSKEY is a field defined with Explicit Select).

Note: You cannot include the asterisk (*) in a query where you want to retrieve a field defined with Explicit Select. That is, a statement such as SELECT *, SYSKEY FROM NATION will not return SYSKEY. You can disable this attribute by specifying the disableExplicitSelect attribute in Oracle Studio, in the properties for the data source.

Auto increment - The current field is updated automatically by the data source during an INSERT statement and should not be explicitly specified in the INSERT statement. The INSERT statement should include an explicit list of values. This attribute is used for fields such as an order number field whose value is incremented each time a new order is entered to the data source.

Hidden - The current field is hidden from users.

Indexes Tab

Use the Indexes tab to specify metadata describing the indices of a table.

Note: The Indexes tab contains information only if the Organization field in the Table tab is set to Index.

Text description of the illustration addind.gif

The Indexes tab has the following fields:

Name - The names of existing indexes for the current table.

Order - The ordering of the rows retrieved by the index.

DB Command - VSAM-specific commands for the index. The information displayed is generated automatically when the metadata is generated and should not be modified.

The following index details can be specified:

Comment - An optional comment on the selected index.

Type - Indicates the type of the index:

Unique - Indicates that the current index is unique.

Clustered - Indicates that the current index is clustered.

Hierarchical - Indicates that the current index is hierarchical.

Hashed - Indicates that the current index is hashed.

Statistics Tab

Use the Statistics tab to update statistics for a table.

Text description of the illustration addstat.gif

The Statistics tab has the following fields:

No. of rows - The approximate number of rows in the table. If the value is -1, the number of rows in the table is unknown (a value was not supplied and the update statistics utility was not run to update the value). A value of 0 indicates that this table is empty.

No. of blocks - The approximate number of blocks in the table.

Warning: If neither the number of rows nor the number of blocks is specified for a table, queries over the table might be executed in a nonoptimal manner.

Use the Columns group box to specify cardinality for each of the columns in the table:

Column name - The columns in the table.

Cardinality - The number of distinct values for the column. If the value is -1, the number of distinct values for the column is unknown (a value was not supplied and the update statistics utility was not run to update the value). A value of 0 indicates that there are no distinct values for the column.

Use the Indexes group box to specify cardinality for the columns in each of the indexes in the table:

Indexes and segments - The indexes and segments in the table.

Cardinality - The number of distinct key values in the index. If the value is -1, the number of distinct key values in the index is unknown (a value was not supplied and the update statistics utility was not run to update the value). A value of 0 indicates that there are no distinct key values in the index.

Generating Statistics

Click the Update button in the Statistics tab to generate updated statistics for the table.

Text description of the illustration addstat1.gif

The following fields are used to update statistics:

Use the Type group box to specify the following:

Estimated - An estimation of the amount of statistical information returned.

Estimated with rows - An estimation of the amount of statistical information returned. The estimate includes an estimation of the number of rows in the table. Specify the number in the text box. This number is used to shorten the time to produce the statistics, assuming that the value specified here is the correct value, or close to the correct value.

Use the Resolution group box to specify the following:

Exact - The exact statistical information returned. Note that this can be a lengthy task and can lead to disk space problems with large tables.

Default - Only information about the table and indexes is collected. Information for partial indexes and columns is not collected.

All columns and indexes - Information about the table, indexes, partial indexes and columns is collected.

Select columns and indexes - Enables you to select the columns and indexes for which you want to collect statistics. In the enabled list of columns or indices left click those columns you want included (you can use shift-click and control-click to select a number of columns or indices).

Source Tab

The Source tab displays the XML representation of the metadata.

Interaction Metadata for the Adapter

Use Oracle Studio to maintain the metadata for the VSAM adapter.

1. On the machine where Oracle Studio is installed, from the Start menu, choose Start > Programs > Oracle > Studio > Studio.

2. Select the machine defined in "Configuring Oracle Connect for VSAM".

3. Click the + next to the Bindings node. The binding configurations available on this machine are listed.

4. Click the + next to the NAV binding node to display the adapters.

5. Right-click the VSAM adapter in the Configuration Explorer and choose Edit Metadata to open the Metadata perspective.

6. Right-click the adapter in the Metadata Explorer and choose Edit definition.

The metadata editor opens, displaying the General tab, with general table details. The following tabs are used to edit the metadata:

• General Tab

• Interaction General Tab

• Interaction Advanced Tab

• Schema General Tab

• Schema Record Tab

The following tab provides an XML representation of the metadata:

• Source Tab

General Tab

Use the General tab to maintain information generally describing the adapter and the connection to it.

Text description of the illustration adaptgen.gif

The General tab has the following fields:

Adapter definition name - The name of the adapter definition.

Description - An identifying description of the adapter.

Version - The adapter version.

Vendor - The vendor which the adapter came from.

Authentication mechanism - The authentication to access the adapter. The available mechanisms are:

• kerbv5

• none

• basic-password

Max request size - The maximum size in bytes for an XML request or reply. Larger messages are rejected with an error.

Max active connections - The maximum number of simultaneous connections for an adapter (per process).

Max idle timeout - The maximum time, in seconds, that an active connection can stay idle. After that time, the connection is closed.

Adapter Specifications - Adapter-specific properties.

Interaction General Tab

Use the Interaction General tab to define the general details of the interaction in addition to its input and output definitions.

Text description of the illustration intgen.gif

The Interaction General tab has the following fields:

Interaction name - The name of the interaction.

Description - A descriptive identifier for the interaction.

Mode - The interaction mode:

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

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

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

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

Input record - Identifies an input record.

Output record - Identifies an output record for the results of an outbound interaction.

Interaction Advanced Tab

Use the Interaction Advanced tab to define specific interaction properties.

Text description of the illustration intadv.gif

The Interaction Advanced tab has the following fields:

SQL Statement - The SQL statement that is executed on the VSAM data source by the interaction. Parameters are specified in the SQL, in a SET clause or in a WHERE clause, with the following format:

:parameter_name

Pass Through - For internal use.

Parameters - Defines the property and value of parameters specific to the interaction.

Name - The name of the parameter.

Type - The type of the parameter (such as number or string).

Nullable - Whether the value can be null or not.

Default - A default value for the parameter.

Schema General Tab

Use the Schema General tab to define the general details of the input and output record structures for the interaction.

Text description of the illustration schemgen.gif

The Schema General tab has the following fields:

Schema name - The name of the adapter.

Version - The schema version.

Header - A C header, file to map between the data structure and the adapter.

Schema Record Tab

Use the Schema Record tab to define the input and output record structures for the interaction.

Text description of the illustration schemrec.gif

The Schema Record tab has the following fields:

The Fields List defines single data items within a record.

Name - The name of the field.

Type - The datatype of the field. The following are valid datatypes:

Binary

Boolean

Byte

Date

Double

Enum

Float

Int

Long

Numeric[(p[,s])]

Short

String

Time

Timestamp

Length - The size of the field including a null terminator, when the datatype supports null termination (such as the cstring data type).

Use the Specifications box to specify field properties.

Source Tab

The Source tab displays the XML representation of the adapter metadata.