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:
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. |
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. |
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:
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 The tags available for this mask are the same as for local objects, to which must be added |
Partition Mask | Shows how to name a partition in a SELECT statement.
In Oracle Data Integrator, the tags available for this mask are:
For example: |
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: |
Local Sequence Mask | Shows the SQL code to use when partitioning a native sequence that is on the current (local) data server.
For example: |
Remote Sequence Mask | Shows the SQL code to use when partitioning a native sequence that is on the remote data server.
For example: |
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: |
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: |
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. |
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. |
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.
|
Name | Name of the physical schema in Oracle Data Integrator. Process name cannot exceed 8 characters and only upper case is allowed. |
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. |
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. |
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