7.27 Physical Schema Editor

Use to define the physical schema.

The physical schema is a decomposition of the data server, allowing the datastores (tables, files, and so forth) to be classified. Objects stored in data servers with this mode of classification can be accessed by specifying the name of the schema attached to the object name.

Examples:

Oracle classifies its tables by schema (or user). Each table is linked to a schema, thus, SCOTT.EMP represents the EMP table in the SCOTT schema.
Microsoft Access does not have schemas.
DB2/400 has schemas called Libraries.
Microsoft SQL Server has a schema called Owner for each database. By default the owner is named: dbo. A table is accessed under the form HR.dbo.EMP to access the EMP table belonging to the user dbo in the HR database.

Notes:

To access the data in a data server, all the schemas containing the datastores used in your project must be declared.
Technologies that do not really have schemas must still undergo a default physical schema creation.

The Physical Schema Editor has the following tabs:

7.27.1 Definition

Properties	Description
Name	Name of the physical schema, as it appears in the graphic interface. It is calculated automatically and is read-only.
Datasource (Catalog)	Only appears if the Technology selected supports Catalogs. Name of the catalog in the data server.
(Schema)	Name of the schema in the data server. Schema, owner, or library where the required data is stored. Caution: Oracle Data Integrator lists all the schemas present in the data server. Sometimes, Oracle Data Integrator cannot draw up this list. In this case, you should enter the schema name, respecting the case.
Datasource (Work Catalog)	Only appears if the Technology selected supports Catalogs. Indicate the catalog in which you want to create these objects. For some data validation or transformation operations, Oracle Data Integrator may require work objects to be created.
(Work Schema)	Indicate the schema in which you want to create these objects. For some data validation or transformation operations, Oracle Data Integrator may require work objects to be created. It is recommended that you create a specific schema dedicated to any work tables. By creating a schema named SAS or ODI in all your data servers, you ensure that all Oracle Data Integrator activity remains totally independent from your applications. Caution: Oracle Data Integrator lists all the schemas present in the data server. Sometimes, Oracle Data Integrator cannot draw up this list. In this case, you should enter the schema name, respecting the case. Note: If you do not set a Work Schema, it defaults to the Schema during the execution.
Default	If this check box is selected, then the physical schema is the data server default schema, when no schema has been specified. Only one physical schema can be marked by default.

Work Tables Prefix

The following table details the work table prefixes that Oracle Data Integrator is likely to create in the work schema of this physical schema.

Properties	Description
Errors	Prefix used to create the tables that contain erroneous data. These tables are created or updated during a data quality control and can be consulted from the graphic interface.
Loading	Prefix used to create the objects (tables, views, files, and so forth) that allow data loading between two data servers.
Integration	Prefix used to create the objects (tables, files, and so forth) dedicated to data integration during execution of an mapping.
Temporary Indexes	Prefix used for the temporary indexes.

Journalizing Elements Prefixes

The following table details the prefixes that Oracle Data Integrator is likely to use to create elements for journalizing in this schema.

Properties	Description
Datastores	Prefix used to create the journalizing datastores (containing the change indications).
Views	Prefix used to create the views that link journalizing tables and the data tables.
Triggers	Prefix used to create triggers on the data tables that enable the journalizing tables to be updated.

Naming Rules

The following table shows how to locate and name (term) the data containers for this technology.

Properties	Description
Local Object Mask	Shows how to name an object in the physical schema, when you are connected to the data server on which the object is based. For example, for Oracle, you should indicate %SCHEMA.%OBJECT to symbolize the syntax "SCOTT.EMP" giving access to the EMP table belonging to the user SCOTT. In Oracle Data Integrator, the tags available for this mask are: `%CATALOG` to symbolize the catalog name (property of the physical schema). `%SCHEMA` to symbolize the schema name (property of the physical schema). `%OBJECT` to symbolize the datastore name. Caution: These tags are case-sensitive.
Remote Object Mask	Shows how to name an object in the physical schema, when you are connected to a different data server from the one on which this object is based. For example, for Oracle, you should indicate `%SCHEMA.%OBJECT@DSERVER` to symbolize the syntax `SCOTT.EMP@NYORK` allowing access to the EMP table of the user SCOTT of the instance NYORK (remote data server). The tags available for this mask are the same as for local objects, to which must be added `%DSERVER` that represents the name of the data server (property of the connection).
Partition Mask	Shows how to name a partition in a SELECT statement. In Oracle Data Integrator, the tags available for this mask are: `%CATALOG` to symbolize the catalog name (property of the physical schema). `%SCHEMA` to symbolize the schema name (property of the physical schema). `%OBJECT` to symbolize the datastore name. `%PARTITION` to symbolize a partition name. For example: `%SCHEMA.%OBJECT PARTITION(%PARTITION)`
Sub-Partition Mask	Shows how to name a sub-partition in a SELECT statement. In Oracle Data Integrator, the tags available for this mask are the same as for the Partition Mask. For example: `%SCHEMA.%OBJECT SUBPARTITION(%PARTITION)`
Local Sequence Mask	Shows the SQL code to use when partitioning a native sequence that is on the current (local) data server. For example: `%SCHEMA.%OBJECT.nextval`
Remote Sequence Mask	Shows the SQL code to use when partitioning a native sequence that is on the remote data server. For example: `%SCHEMA.%OBJECT.nextval@%DSERVER`
Local Sequence Current Value Mask	Shows the SQL code to use when accessing a native sequence current value that is on the current (local) data server. For example: `%SCHEMA.%OBJECT.currval`
Remote Sequence Current Value Mask	Shows the SQL code to use when accessing a native sequence current value that is on the remote data server. For example: `%SCHEMA.%OBJECT.currval@%DSERVER`

Configuration

This section details the configuration related fields of the SOAP Web Service data server, which represent the mapping to a WSDL binding and enables you to specify OWSM security policies. It is only displayed when defining a physical schema for an SOAP Web Service data server.

Properties	Description
Service	Unique name that differentiates a service from the other services defined in the WSDL document.
Port	WSDL port to which you want to connect.
EndPoint URL	EndPoint URL of the service that is being invoked.
Binding	Binding WSDL port to which you want to connect.
User	User name used to connect to the service. Note: If a user name is not specified, use the user name defined at the data server level.
Password	Password linked with the user name.
Override EndPoint URL	Option to override the EndPoint URL specified in the WSDL.
OWSM Policy Configuration	Displays the OWSM security policies. You can add, edit, remove, enable, or disable OWSM security policies by clicking the corresponding toolbar icon.

7.27.2 Context

In a project, the datastores of a physical schema are always accessed by specifying the logical schema and the context.

Caution: To be able to use a physical schema in Oracle Data Integrator, it is imperative that it is associated with a physical schema in a given context.

Properties	Description
Context	Shows the contexts in which this physical schema is represented.
Logical Schema	Shows the name of the logical schema through which you can access the physical schema for the specified context. Note: If there is no suitable logical schema name in the list, entering a new name automatically creates a new logical schema.

7.27.3 Process Definition

Use process definition tab to perform Changed Data Capture in Oracle GoldenGate.

Properties	Description
Process Type	Type of the process that performs Changed Data Capture in Oracle GoldenGate. Capture: Reads the changes from database log files and writes them to trail files, a common Oracle GoldenGate format. This process is database dependent, it needs to understand the format of log files corresponding to the database. One process per database. A capture process can extract from any number of tables the single access details in the configuration. Delivery: Reads the content from a common trail file and populates the target database. This process can be database dependent or common. A delivery process can update only one database, but it can update multiple tables accessed through the same account.
Name	Name of the physical schema in Oracle Data Integrator. Process name cannot exceed 8 characters and only upper case is allowed.

Properties

Description

Process Type

Type of the process that performs Changed Data Capture in Oracle GoldenGate.

Capture: Reads the changes from database log files and writes them to trail files, a common Oracle GoldenGate format. This process is database dependent, it needs to understand the format of log files corresponding to the database. One process per database. A capture process can extract from any number of tables the single access details in the configuration.
Delivery: Reads the content from a common trail file and populates the target database. This process can be database dependent or common. A delivery process can update only one database, but it can update multiple tables accessed through the same account.

Name

Name of the physical schema in Oracle Data Integrator. Process name cannot exceed 8 characters and only upper case is allowed.

Delivery Process Properties

Properties	Description
Trail File Path	Location of trail file. The file name part allows only two characters.
Discard File Path	Location of the discard file.
Definition File Path	Location of the definition file.
AllowTargetDups	Allows the same source table to be replicated to more than one target table.
ReportDetail	Enables report information to include any collision counts.
Report Count Frequency	Reports the total operations count at specific intervals. If the interval is not specified the entry is not added to the parameter file.

Capture Process Properties

Properties	Description
Trail File Path	Location of the trail file. The file name part allows only two characters.
Remote Trail File Path	Target trail file path used by the Pump process. Content from this trail file is consumed by a Delivery process.
Trail File Size	Maximum size of the trail file. If the trail file size exceeds the limit, this process creates another trail file.
Report Fetch	Enables the Oracle GoldenGate REPORTFETCH parameter.
Report Count Frequency	Reports the total operations count at specific intervals. If the interval is not specified, the entry is not added to the parameter file.

Additional Options

Properties	Description
Select a parameter	List of available parameters. Select a parameter and click Add. A template of the selected parameter is added to the text box.

"Creating a Physical Schema" in the "Setting Up a Topology" chapter in Oracle Fusion Middleware Administering Oracle Data Integrator