11 Creating New Platforms

Starting with Oracle Warehouse Builder 11g Release 2 (11.2), you can create new platforms to integrate with other systems and construct integration capabilities using code templates based on your requirements. A platform refers to a data source or target. By creating new platforms, you can thus connect to new databases in addition to those that are supported by default in Oracle Warehouse Builder.

This chapter contains the following topics:

Creating a New Platform

When you create a new platform, you must define properties including connection information, code generation options, data types supported by the platform, and how these data types map to the generic data types.

To create the platform and define its characteristics, you must use OMB*Plus scripting commands.

For more information about OMB*Plus scripting, see Oracle Warehouse Builder API and Scripting Reference.

See Also:

Oracle Warehouse Builder OMB*Plus Command Reference for a list of all OMB*Plus commands.

The OMBCREATE command creates a new platform:

OMBCREATE PLATFORM 'MICROSOFT_EXCEL' SET PROPERTIES (BUSINESS_NAME) VALUES ('Microsoft Excel')

The business name is displayed in the Projects Navigator.

To define the properties of this platform, use the OMBALTER command:

OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD PLATFORM_TYPE 'CHAR'

For a complete listing of the commands to create and define a new platform, see "Creating a Microsoft Excel Platform". You can add to this depending on your specific requirements.

When you create a platform from the OMB*Plus interface, the platform gets added under the Databases node in the Projects Navigator. The location of the platform gets added under the Databases node in the Locations Navigator. You can now create a module under this new platform.

Defining the Properties of a New Platform

While creating a new platform, you must define certain basic properties for the platform. These include:

  • Designer and run-time properties

  • Platform data types

  • CMI/MIV to be used for custom import

  • Data type definitions for the mappings

  • JDBC driver for the platform

Table 11-1 lists the properties that must be specified for a platform.

Table 11-1 Properties of a Platform

Property Data Type Description

NAME

STRING

The physical name of the platform.

BUSINESS_NAME

STRING

Business name of the platform.

DESCRIPTION

STRING

Description of the platform.

DRIVERCLASS

STRING

The default JDBC driver class. For example com.sunopsis.jdbc.driver.xml.SnpsXmlDriver for XML.

URL_TEMPLATE

STRING

The default URL for the driver. This is used as a template.

COL_ALIAS_WORD

STRING

The separator for a column and its alias. It is not mandatory to specify this property.

TAB_ALIAS_WORD

STRING

The separator for a table and its alias. It is not mandatory to specify this property.

DATE_FCT

STRING

The function that returns the date and time. For example sysdate for Oracle.

DDL_NULL

STRING

A column that can hold NULL value.

DEFAULT_MAX_NAME_LEN

INTEGER

The maximum length for a table name. If you specify a name longer than this limit, the name is truncated to this length.

DEFAULT_NAME_LEN_SEMANTIC

STRING

Whether the name length is specified in characters or bytes.

SPECIAL_MAX_NAME_LEN

STRING

Name length for second class objects (SCOs). (For example INDEX=18 or COLUMN=30).

SPECIAL_NAME_LEN_SEMANTICS

STRING

Whether SPECIAL_MAX_NAME_LEN is specified in terms of characters or bytes.

ESCAPE_CHAR

STRING

The escape character. For example, it is the double quotation mark (") for Oracle Database.

ENCLOSURE_CHAR

STRING

The enclosure character. For example, it is the backslash (\) for Oracle Database.

RESERVED_WORDS

STRING

Reserved words for a platform.

ILLEGAL_CHARS

STRING

Characters that cannot be used while naming objects in a platform.

ILLEGAL_LEADING_CHARS

STRING

Characters that cannot be used as the first character in the name of an object.

CUSTOM_IMPORTERS

 

The custom metadata definitions that are associated to a platform.

Data Types

 

The data types supported by a platform. You can specify the syntax to define a data type and any parameter that the data type uses. For example the length, precision, and scale.

Data Type map to Generic platform

 

The mapping of a platform's data types to the generic data types.

Data Type map from Generic platform

 

The mapping of generic data types to a platform's data types.

VARCHAR_MASK

STRING

Run-time properties used by the platform while executing Code Templates.

DATE_MASK

STRING

 

NUMERIC_MASK

STRING

The syntax to be used to describe the numeric data type in DDL. The tags %L (data length) and %P (precision) can be used.


Defining the Platform Type

Based on your requirements, you can provide support for various data types in a platform. The properties of the Data Type define the scope of a data type. These properties are listed in Table 11-2.

Table 11-2 Properties of Data Type

Property Type Description

NAME

STRING

The physical name of the data type.

BUSINESS_NAME

STRING

The business name of the data type.

DESCRIPTION

STRING

Description of the data type.

SYNTAX

STRING

The syntax to be used during code generation. For example CHAR[(%size)].

P1

STRING

Either size, precision, or scale.

P1TYPE

STRING

Specifies the range.

P1DEFAULT

STRING

Default value for P1.

P1MIN

STRING

Minimum value for P1.

P1MAX

STRING

Maximum value for P1.

P2

STRING

Specifies either Size, Precision, or Scale.

P2TYPE

STRING

Specifies the range.

P2DEFAULT

STRING

Default value for P2.

P2MIN

STRING

Minimum value for P2.

P2MAX

STRING

Maximum value for P2.


Defining the Data Type Map

After you define data types for a platform, you must also define how these data types map to other generic data types. You can define these data type mappings with the properties listed in Table 11-3.

Table 11-3 Data Type Mapping Properties

Property Description

NAME

The physical name of the map.

BUSINESS_NAME

Business name of the map.

DESCRIPTION

Description of the map.

FROM_DATATYPE

The data type to map from.

TO_DATATYPE

Data type to be mapped to.

CONDITION1

Defines the condition where the data type mapping is valid. For example, mapping from a generic CHAR data type to a DB2UDB CHAR data type is dependent on the size of the characters, and this size is specified in the CONDITION1 parameter.

CONDITION2

Used for setting additional condition.

CONDITION3

Used for setting additional condition.


Generic Data Types Supported in Oracle Warehouse Builder

The Generic data types supported in Oracle Warehouse Builder are listed in Table 11-4.

Table 11-4 Generic Data Types

Category Generic Data Type Parameters Description

Numbers

BIGINT

 

Range is -2^63 to 2^63-1

 

BINARY_FLOAT

 

Single precision floating point numbers

Minimum positive finite value = 1.17549E-38F

Maximum positive finite value = 3.40282E+38F

 

BINARY_DOUBLE

 

Double precision floating point numbers

Minimum positive finite value = 2.22507485850720E-308

Maximum positive finite value = 31.79769313486231E+308

 

BIT

 

An integer data type that can take a value of 1, 0, or NULL

 

FLOAT

FLOAT (precision)

-1.79769E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79769E+308

 

DOUBLE

DOUBLE (precision)

-1.79769E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79769E+308

 

INTEGER

 

NUMERIC(38)

 

INT10

 

The range of large integers is -2 147 483 648 to +2 147 483 647

 

MONEY

 

Ranges from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. (Used only by MS SQL Server)

 

NUMERIC

NUMERIC [(precision [, scale])]

The precision p can range from 1 to 38. The scale s can range from -84 to 127

 

DECIMAL

DECIMAL [(precision [, scale])]

The precision p can range from 1 to 38. The scale s can range from -84 to 127

 

REAL

 

Ranges from - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38

 

SMALLINT

 

Ranges from -2^15 (-32,768) to 2^15-1 (32,767)

 

SMALLMONEY

 

Ranges from - 214,748.3648 to 214,748.3647

 

TINYINT

 

Ranges from 0 to 255

Large Objects

BLOB

BLOB

A binary large object, with no limit on the maximum size

 

VARLOB

VARBLOB [(size[K|M|G])]

A binary large object is a varying-length binary string that can be up to 2 GB (2 147 483 647 bytes) long. The default value is 1 MB (1,048,576)

 

CLOB

 

Character large object. No limit on the maximum size

 

VARCLOB

VARCLOB [(size[K|M|G])]

A CLOB (character large object) value can be up to 2 GB (2 147 483 647 bytes) long. The default value is 1 MB (1,048,576)

 

DBCLOB

DBCLOB [(size[K|M|G])]

A DBCLOB (double-byte character large object) value can be up to 1 073 741 823 double-byte characters long. (Only used by IBM DB2 UDB)

 

NCLOB

 

Character large object in Unicode or double-byte. No limit on the maximum size

Character Strings

CHAR

CHAR[(size)]

Fixed length character data with size between 1 and 8000

 

GRAPHIC

GRAPHIC (size)

The size attribute must be between 1 and 127, inclusive. (Only used by IBM DB2 UDB)

 

LONGVARGRAPHIC

LONGVARGRAPHIC (size)

VARGRAPHIC value can be up to 16 350 double-byte characters long. (Only used by IBM DB2 UDB)

 

NCHAR

NCHAR (size)

Fixed length (1 to 2000) Unicode or double-byte character data

Size: 1-4000

 

NVARCHAR

NVARCHAR (size)

Variable length (1 to 4000) Unicode or double-byte character data

 

NVARCHARMAX

NVARCHARMAX

Variable length (1 to 2^31-1) Unicode or double-byte character data

 

VARCHAR

VARCHAR (size)

Variable length (1 to 8000) character data

 

VARCHARMAX

VARCHARMAX

Variable length (1 to 2^31) character data

 

VARGRAPHIC

VARGRAPHIC (size)

A VARGRAPHIC value can be up to 16 336 double-byte characters long. (Only used by IBM DB2 UDB)

Date and Time

DATE

 

Date in year, month, day, hour, minute, and second without fractional seconds precision or time zone

 

DATETIME

 

Range is January 1, 1753, through December 31, 9999, accuracy is 3.33 milliseconds. (MS SQL Server only)

 

INTERVAL YEAR TO MONTH

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

Stores a period in days, hours, minutes, and seconds, where

day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.

fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6. The size is fixed at 11 bytes.

 

SMALLDATETIME

 

Range is January 1, 1900, through June 6, 2079; accuracy is 1 minute

 

TIME

 

A TIME is a three-part value (hour, minute, and second) designating a time of day under a 24-hour clock. The range of the hour part is 0 to 24. The range of the other two parts is 0 to 59. If the hour is 24, the minute and second specifications are zero.

 

TIMESTAMP

TIMESTAMP [(fractional_seconds_precision)]

DATE with factional seconds precision

 

TIMESTAMP WITH TIME ZONE

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

TIMESTAMP plus time zone displacement value

 

TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP [(fractional_seconds_precision)]

WITH LOCAL TIME ZONE

TIMESTAMP normalized to the database time zone

Binary Strings

BINARY

BINARY [(size)]

Binary data up to 8000 bytes

 

VARBINARY

VARBINARY [(size)]

Binary data of variable length up to 8000

 

VARBINARYMAX

VARBINARY [(size)]

Binary data of variable length up to 2 GB

 

LONGVARBINARY

LONGVARBINARY

Raw binary data of variable length up to 2 gigabytes

 

IMAGE

IMAGE

Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes

Others

UNDEFINED

UNDEFINED

Represent all non-supported data types (catch-all)

 

XMLTYPE

XMLTYPE

Only mapped between Oracle/Oracle Work Flow and Generic

 

BOOLEAN

BOOLEAN

Only mapped between Oracle/Oracle Work Flow and Generic


DB2 Data Types Mapping

Table 11-5 lists the mapping of DB2 data types to generic data types.

Table 11-5 DB2 Data Types to Generic Data Types

DB2 Data Type Generic Data Type

CHARACTER, CHAR

CHAR

VARCHAR, CHARACTER VARYING, CHAR VARYING

VARCHAR

LONG VARCHAR

LONG VARCHAR

GRAPHIC

GRAPHIC

VARGRAPHIC

VARGRAPHIC

LONG VARGRAPHIC

LONG VARGRAPHIC

DBCLOB

DBCLOB

SMALLINT

SMALLINT

INTEGER, INT

INT10

BIGINT

BIGINT

NUMERIC, NUM

NUMERIC

DECIMAL, DEC

DECIMAL

REAL

REAL

FLOAT

FLOAT

DOUBLE

DOUBLE

DATE

DATE

TIMESTAMP

TIMESTAMP

TIME

TIME

BLOB, BINARY LARGE OBJECT

VARLOB

CLOB, CHARACTER LARGE OBJECT, CHAR LARGE OBJECT

VARCLOB


Table 11-6 lists the mapping of generic data types to DB2 data types.

Table 11-6 Generic Data Types to DB2 Data Types

Generic Data Type DB2 Data Type

BIGINT

BIGINT

BINARY_FLOAT

REAL

BINARY_DOUBLE

FLOAT(53)

BIT

NUMERIC(1)

FLOAT [(precision)]

FLOAT [(precision)]

DOUBLE [(precision)]

DOUBLE [(precision)]

INTEGER

NUMERIC(31)

INT10

INTEGER

MONEY

REAL

NUMERIC [(precision [, scale])]

NUMERIC [(precision [, scale])]

DECIMAL [(precision [, scale])]

DECIMAL [(precision [, scale])]

REAL

REAL

SMALLINT

SMALLINT

SMALLMONEY

REAL

TINYINT

SMALLINT

BLOB

BLOB

VARLOB

BLOB

CLOB

CLOB (2147483647)

VARCLOB

CLOB

DBCLOB [(size [K|M|G])]

DBCLOB [(size [K|M|G])]

NCLOB

DBCLOB

CHAR [(size)]

CHAR [(size)]

GRAPHIC (size)

GRAPHIC (size)

LONGVARCHAR

LONGVARCHAR

LONGVARGRAPHIC

LONGVARGRAPHIC

NCHAR [(size)]

VARGRAPHIC [(size)]

NVARCHAR (size)

VARGRAPHIC (size)

NVARCHARMAX

DBCLOB

VARCHAR (size)

size<=32,672: VARCHAR

32672<size<=32700:LONG VARCHAR

size>32,700: CLOB

VARCHARMAX

CLOB

VARGRAPHIC (n)

VARGRAPHIC (n)

DATE

DATE

DATETIME

TIMESTAMP

INTERVAL YEAR [(year_precision)] TO MONTH

VARCHAR

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

VARCHAR

SMALLDATETIME

TIMESTAMP

TIME

TIME

TIMESTAMP [(fractional_seconds_precision)]

TIMESTAMP

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

VARCHAR

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

VARCHAR

BINARY [(size)]

size<=254: CHAR (size) FOR BIT DATA

size>254:VARCHAR(size) FOR BIT DATA

VARBINARY [(size)]

size<=32,672:VARCHAR(size) FOR BIT DATA

size>32,672: BLOB

VARBINARYMAX

BLOB

LONGVARBINARY

BLOB

IMAGE

BLOB

UNDEFINED

VARCHAR(32672)

XMLTYPE

VARCHAR(32672)

BOOLEAN

VARCHAR(10)


There might be precision loss in the following cases:

  • Mapping a generic NUMERIC data type (up to 38) to DB2 NUMERIC data type with a maximum precision of 31

  • Mapping a generic DECIMAL data type (up to 38) to DB2 DECIMAL data type with a maximum precision of 31

  • Mapping a generic INTEGER data type (up to 38) to DB2 NUMERIC(31) data type with a maximum precision of 31

MS SQL Server Data Types Mapping

Table 11-7 lists the mapping of MS SQL Server data types to generic data types.

Table 11-7 MS SQL Server Data Types to Generic Data Types

MS SQL Server Data Type Generic Data Type

CHAR

CHAR

VARCHAR

VARCHAR

VARCHAR(MAX)

VARCHARMAX

TEXT

VARCHAR(2147483647)

NCHAR

NCHAR

NVARCHAR

NVARCHAR

NVARCHAR(MAX)

NVARCHARMAX

NTEXT

NVARCHAR

BINARY

BINARY

VARBINARY

VARBINARY

VARBINARY(MAX)

VARBINARYMAX

IMAGE

IMAGE

SMALLINT

SMALLINT

INT

INT10

BIGINT

BIGINT

TINYINT

TINYINT

BIT

BIT

MONEY

MONEY

SMALLMONEY

SMALLMONEY

NUMERIC

NUMERIC

DECIMAL

DECIMAL

REAL

REAL

FLOAT

FLOAT

DATETIME

DATETIME

SMALLDATETIME

SMALLDATETIME

UNIQUEIDENTIFIER

UNDEFINED

XML

UNDEFINED

TIMESTAMP

UNDEFINED

SQL_VARIANT

UNDEFINED


Table 11-8 lists the mapping of generic data types to MS SQL Server data types.

Table 11-8 Generic Data Types to MS SQL Server Data Types

Generic Data Type MS SQL Server Data Type

BIGINT

BIGINT

BINARY_FLOAT

REAL

BINARY_DOUBLE

FLOAT

BIT

BIT

FLOAT

FLOAT

DOUBLE

FLOAT

INTEGER

NUMERIC(38)

INT10

INT

MONEY

MONEY

NUMERIC

NUMERIC

DECIMAL

DECIMAL

REAL

REAL

SMALLINT

SMALLINT

SMALLMONEY

SMALLMONEY

TINYINT

TINYINT

BLOB

VARBINARY(MAX)

VARBLOB(N)

VARCHAR(MAX)

CLOB

VARCHAR(MAX)

VARCLOB(N)

VARCHAR(MAX)

DBCLOB

NVARCHAR(MAX)

NCLOB

NVARCHAR(MAX)

CHAR(N)

CHAR(N)

GRAPHIC(N)

NCHAR(254)

LONGVARCHAR

VARCHAR(MAX)

LONGVARGRAPHIC(N)

NVARCHAR(MAX)

NCHAR(N)

NCHAR(N)

NVARCHAR(N)

NVARCHAR(N)

NVARCHARMAX

NVARCHAR(MAX)

VARCHAR(N)

1<=n<=8000: varchar (n)

n>8000: varchar (max)

VARCHARMAX

VARCHAR(MAX)

VARGRAPHIC(N)

NVARCHAR(MAX)

DATE

DATETIME

DATETIME

DATETIME

INTERVAL DAY TO SECOND

VARCHAR

INTERVAL YEAR TO MONTH

VARCHAR

SMALLDATETIME

SMALLDATETIME

TIME

VARCHAR

TIMESTAMP

DATETIME

TIMESTAMP WITH TIME ZONE

DATETIME

TIMESTAMP WITH LOCAL TIME ZONE

DATETIME

BINARY (size)

BINARY (size)

VARBINARY (size)

1<=size<=8000: varbinary (size)

n>8000: varbinary (max)

VARBINARYMAX

VARBINARY(MAX)

LONGVARBINARY

VARBINARY(MAX)

IMAGE

IMAGE

UNDEFINED

VARCHAR(MAX)

XMLTYPE

VARCHAR(MAX)

BOOLEAN

VARCHAR(10)


Creating Modules Based on a Platform

When you run the OMBCREATE command to create a platform, the corresponding platform node is added under the Databases node in the Projects Navigator. You can now create a module under this new platform.

For example, to create a module under Microsoft_Excel, right-click Microsoft_Excel and select New Microsoft_Excel Module. The Create Module Wizard guides you through the steps to create a module. This includes:

  1. "Providing a Name and Access Method"

  2. "Providing Connection Information"

Providing a Name and Access Method

On the Name and Description page, provide a name for the module. By default, this is a Generic Access module. Select the access method as well. It can be either Native Database Connection or Gateway. For native database connectivity, Oracle Warehouse Builder supports importing metadata based on JDBC. So if a JDBC or ODBC driver is installed on the system, you can use this driver for the data import. Click Next to open the Connection Information page.

Providing Connection Information

Native Database Connection implies a generic JDBC connection. Click Edit to open the Edit Generic JDBC Location dialog box and provide the location information. You must provide a JDBC URL in the UrL field and a JDBC driver in the Driver Class field.

If you select Gateway connection, then provide the database connection details.

A platform module supports the following objects:

  • Transformations

  • Tables

  • Views

The data you can store in these objects depends on the data types you defined for the platform.

You can also import metadata by defining a Custom Metadata Interface (CMI) that is based on a custom API.

Creating a Microsoft Excel Platform

The following example creates a new platform to extract data from Microsoft Excel worksheets. This example lists only the most basic options to create the platform, including the connection information, and the supported data types. You can build on the platform by inserting additional features.

First, define the platform on the Projects Navigator:

OMBCREATE PLATFORM 'MICROSOFT_EXCEL' SET PROPERTIES (BUSINESS_NAME) VALUES ('Microsoft Excel')

Next, define the connection information for the platform. This includes specifying the ODBC:JDBC driver and the URL template:

OMBALTER PLATFORM 'MICROSOFT_EXCEL' SET PROPERTIES (DRIVER_CLASS,URI_TEMPLATE) VALUES
('sun.jdbc.odbc.JdbcOdbcDriver','jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)}\;
DBQ=<filename>\;DriverID=22\;READONLY=true')

We then define the properties for the platform:

OMBALTER PLATFORM 'MICROSOFT_EXCEL' SET PROPERTIES (LOCAL_OBJECT_MASK) VALUES ('%SCHEMA.%OBJECT')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' SET PROPERTIES (DATE_MASK) VALUES ('DATETIME')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' SET PROPERTIES (DDLNULL) VALUES ('null')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' SET PROPERTIES (NUMERIC_MASK) VALUES ('NUMBER')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' SET PROPERTIES (VARCHAR_MASK) VALUES ('VARCHAR(%L)')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' SET PROPERTIES (LOCAL_OBJECT_MASK) VALUES ('%OBJECT')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' SET PROPERTIES (DEFAULT_MAX_NAME_LEN) VALUES ('30')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' SET PROPERTIES (REMOTE_OBJECT_MASK) VALUES ('%OBJECT')

We finally define the data types supported by the platform:

OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD PLATFORM_TYPE 'LOGICAL'
OMBALTER PLATFORM 'MICROSOFT_EXCEL' MODIFY PLATFORM_TYPE 'LOGICAL' SET PROPERTIES(SYNTAX) VALUES ('LOGICAL')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD FROM_PLATFORM_TYPEMAP 'LOGICAL_TOG' SET PROPERTIES (FROM_DATATYPE, TO_DATATYPE) VALUES ('LOGICAL', 'CHAR')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD TO_PLATFORM_TYPEMAP 'LOGICAL_FROMG' SET PROPERTIES (FROM_DATATYPE, TO_DATATYPE) VALUES ('CHAR', 'LOGICAL')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD PLATFORM_TYPE 'CURRENCY'
OMBALTER PLATFORM 'MICROSOFT_EXCEL' MODIFY PLATFORM_TYPE 'CURRENCY' SET PROPERTIES(P1,P1MIN, P1MAX,P1DEFAULT,P1TYPE) VALUES ('precision','1', '64000', '1','range')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' MODIFY PLATFORM_TYPE 'CURRENCY' SET PROPERTIES(P2,P2MIN, P2MAX,P2DEFAULT,P2TYPE) VALUES ('scale','1', '18', '1','range')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' MODIFY PLATFORM_TYPE 'CURRENCY' SET PROPERTIES(SYNTAX) VALUES ('CURRENCY(%precision,%scale)')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD FROM_PLATFORM_TYPEMAP 'CURRENCY_TOG' SET PROPERTIES (FROM_DATATYPE, TO_DATATYPE) VALUES ('CURRENCY', 'NUMERIC')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD PLATFORM_TYPE 'NUMBER'
OMBALTER PLATFORM 'MICROSOFT_EXCEL' MODIFY PLATFORM_TYPE 'NUMBER' SET PROPERTIES(P1,P1MIN, P1MAX,P1DEFAULT,P1TYPE) VALUES ('precision','1', '64000', '1','range')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' MODIFY PLATFORM_TYPE 'NUMBER' SET PROPERTIES(P2,P2MIN, P2MAX,P2DEFAULT,P2TYPE) VALUES ('scale','1', '18', '1','range')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' MODIFY PLATFORM_TYPE 'NUMBER' SET PROPERTIES(SYNTAX) VALUES ('NUMBER(%precision,%scale)')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD FROM_PLATFORM_TYPEMAP 'NUMBER_TOG' SET PROPERTIES (FROM_DATATYPE, TO_DATATYPE) VALUES ('NUMBER', 'NUMERIC')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD TO_PLATFORM_TYPEMAP 'NUMBER_FROMG' SET PROPERTIES (FROM_DATATYPE, TO_DATATYPE) VALUES ('NUMERIC', 'NUMBER')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD PLATFORM_TYPE 'DATETIME'
OMBALTER PLATFORM 'MICROSOFT_EXCEL' MODIFY PLATFORM_TYPE 'DATETIME' SET PROPERTIES(SYNTAX) VALUES ('DATETIME')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD FROM_PLATFORM_TYPEMAP 'DATETIME_TOG' SET PROPERTIES (FROM_DATATYPE, TO_DATATYPE) VALUES ('DATETIME', 'DATETIME')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD TO_PLATFORM_TYPEMAP 'DATETIME_FROMG' SET PROPERTIES (FROM_DATATYPE, TO_DATATYPE) VALUES ('DATETIME', 'DATETIME')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD PLATFORM_TYPE 'VARCHAR'
OMBALTER PLATFORM 'MICROSOFT_EXCEL' MODIFY PLATFORM_TYPE 'VARCHAR' SET PROPERTIES(P1,P1MAX,P1DEFAULT,P1TYPE) VALUES ('size','64000', '1','range')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' MODIFY PLATFORM_TYPE 'VARCHAR' SET PROPERTIES(SYNTAX) VALUES ('VARCHAR(%size)')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD FROM_PLATFORM_TYPEMAP 'VARCHAR_TOG' SET PROPERTIES (FROM_DATATYPE, TO_DATATYPE) VALUES ('VARCHAR', 'VARCHAR')
OMBALTER PLATFORM 'MICROSOFT_EXCEL' ADD TO_PLATFORM_TYPEMAP 'VARCHAR_FROMG' SET PROPERTIES (FROM_DATATYPE, TO_DATATYPE) VALUES ('VARCHAR', 'VARCHAR')

Run this script from the OMB*Plus console in Oracle Warehouse Builder. The newly created platform is now visible as a node in the Projects Navigator.

Importing an Excel Worksheet

To import an Excel worksheet:

  1. Create a new Excel module from the MICROSOFT EXCEL platform node in the Projects Navigator.

  2. On the Name and Descriptions page of the Create Module wizard, specify Native Database Connection as the access method.

  3. On the Connection Information page, click Edit to open the Edit Generic JDBC Location dialog box.

    Provide a dummy user name and password as shown in Figure 11-1. You can provide any value for the user name and password, but cannot leave the fields blank. Values for Driver Class and URL fields are set, based on the values provided in the script. However, you must edit the UrL field to point to the location of the Excel file.

    The URL field contains the value:

    jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=<filename>;DriverID=22;READONLY=true
    

    The file name field must be altered to point to the location of the Excel file. For example:

    jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=c:/my_projects/excel/employees.xls;DriverID=22;READONLY=true
    

    In this example, the location points to the file employees.xls.

Figure 11-1 Connection Information for the Excel File

Description of Figure 11-1 follows
Description of "Figure 11-1 Connection Information for the Excel File"

After you create the Excel module and provide the location details for the excel file, you can import the table definitions from the file.

To import the definitions:

  1. Right-click the module and select Database Objects.

    The Import Metadata Wizard is displayed.

  2. Import the table from the Excel file in the same way you import tables from a database.

  3. To view the data in the table, right-click the imported table and select Data.

Using Custom Metadata Import in Platforms

Using a CMI mechanism, you can define how metadata from a database is to be imported into Oracle Warehouse Builder. You can define a CMI that leverages the SQL definitions or the API definitions of the database from which you want to import metadata.

Example: Implementing SQL-Based CMI Import for DB2 UDB

You can define a CMI that uses SQL to retrieve metadata from the SQL-based data dictionary of DB2 UDB and import tables from a DB2 platform. You can implement a similar mechanism to import metadata from any database that uses an SQL-based data dictionary.

To leverage on a CMI mechanism, you must define a CMI DEFINITION for the platform. CMI definitions can only be created from the root context. You can switch to the root context only from the OMB Plus console. You cannot switch to the root context using the OMB*Plus view from within the Oracle Warehouse Builder UI.

To use the OMB Plus console on a Windows system, select Start, then All Programs, <OWB>, Warehouse Builder, and then OMB Plus.

To switch to the root context, use the following command:

OMBCONNECT <repository user>/<password>@<host>:<port number>:<service name>

For example,

OMBCONNECT rep_user/password@localhost:1521:orcl

Where rep_user/password is the user name/password to connect to the repository, localhost indicates a local installation, 1521 is the port number, and orcl is the service name of the database.

Example 11-1 lists a platform definition for DB2 UDB. This lists only the basic definition and does not include the data types that can be added to the platform.

Example 11-1 Platform Definition for IBM DB2 UDB

set platformname IBM_DB2_UDB
set platformdisplay "IBM DB2 CMI Api"
 
puts "Creating Platform $platformdisplay"
 
OMBCREATE PLATFORM '$platformname' SET PROPERTIES (BUSINESS_NAME) VALUES ('$platformdisplay')
OMBALTER PLATFORM '$platformname' SET PROPERTIES (DRIVER_CLASS,URI_TEMPLATE) VALUES ('com.ibm.db2.jcc.DB2Driver','jdbc:db2://Host:Port/Database Name')
OMBALTER PLATFORM '$platformname' SET PROPERTIES (LOCAL_OBJECT_MASK) VALUES ('%SCHEMA.%OBJECT')
OMBALTER PLATFORM '$platformname' SET PROPERTIES (DATE_MASK) VALUES ('TIMESTAMP')
OMBALTER PLATFORM '$platformname' SET PROPERTIES (DDLNULL) VALUES ('null')
OMBALTER PLATFORM '$platformname' SET PROPERTIES (NUMERIC_MASK) VALUES ('NUMERIC(%L,%P)')
OMBALTER PLATFORM '$platformname' SET PROPERTIES (VARCHAR_MASK) VALUES ('VARCHAR(%L)')
OMBALTER PLATFORM '$platformname' SET PROPERTIES (LOCAL_OBJECT_MASK) VALUES ('%SCHEMA.%OBJECT')
OMBALTER PLATFORM '$platformname' SET PROPERTIES (DEFAULT_MAX_NAME_LEN) VALUES ('30')
OMBALTER PLATFORM '$platformname' SET PROPERTIES (REMOTE_OBJECT_MASK) VALUES ('%SCHEMA.%OBJECT')

Next, add a CMI definition to the platform.

OMBCREATE CMI_DEFINITION 'DB2_IMPORT_SQL' USING DEFINITION_FILE 'c:\\platformdef2_miv.xml'
OMBALTER CMI_DEFINITION 'DB2_IMPORT_SQL' SET PROPERTIES (MIV_TYPE) VALUES ('Databases')
OMBALTER PLATFORM '$platformname' SET REF CMI_DEFINITION 'DB2_IMPORT_SQL'

Save the changes using the command OMBCOMMIT.

Note:

You can store the entire script as a .tcl file and run the file from the OMB Plus console.

The file platformdef2_miv.xml contains the custom import definition to import metadata using SQL. Example 11-2 lists the CMI definition file.

Example 11-2 CMI Definition File

<?xml version="1.0"?>
<miv>

<miv_tables type="SQLStatement" default="true">
        SELECT rtrim(tabname) table_name 
        FROM syscat.tables  
        WHERE tabschema = <Parameter name="owner"/> 
                AND type = 'T'   
                AND status = 'N'   
        ORDER BY table_name
</miv_tables>

<miv_columns type="SQLStatement" default="true">
        SELECT rtrim(col.tabname) entity_name, 
               col.colno position, 
               rtrim(col.colname), 
               col.typename, 
               col.length, 
               col.length, 
               col.scale, 
               col.remarks, 
               col.nulls,
               '' datatypeowner,
               col.default,
               CHAR(col.codepage) charset,
               1 bytes_per_char,
               'N' use_char_semantics
        FROM syscat.columns col
        WHERE col.tabschema = <Parameter name="owner"/>
</miv_columns>
 
<miv_capabilities type="ResultSet">
        <table_supported>true</table_supported>
        <view_supported>false</view_supported>
        <sequence_supported>false</sequence_supported>
        <table_name_filter_supported>true</table_name_filter_supported>
        <view_name_filter_supported>false</view_name_filter_supported>
        <sequence_name_filter_supported>false</sequence_name_filter_supported>
        <business_area_supported>false</business_area_supported>      
        <business_area_table_supported>false</business_area_table_supported>    
        <business_area_view_supported>false</business_area_view_supported>      
        <business_area_sequence_supported>false</business_area_sequence_supported>      
        <application_owner_supported>true</application_owner_supported>
        <table_fklevel_supported>false</table_fklevel_supported>
        <reimport_supported>true</reimport_supported>
        <data_object_at_leaf_levels>false</data_object_at_leaf_levels>
        <multiple_tree_supported>false</multiple_tree_supported>
</miv_capabilities>
 
</miv>

The definition file contains queries to retrieve tables and columns. This MIV file is created using elements defined in an XML schema definition (XSD) file. This file is called cmi.xsd and is stored in the OWB_HOME/owb/misc folder.

After you create the platform, it is available under the Databases node in Projects Navigator. You can now connect to a DB2UDB database and import metadata. To connect to DB2, you require the appropriate driver files. For information on the driver file requirements, see "JDBC Connection Drivers for DB2".

Similarly, you can create a new platform for any database that uses an SQL-based data dictionary and then create a SQL-based CMI mechanism to import metadata from the database.

Defining Other Platforms

Table 11-9 lists the URL template specification and the driver class for other common platforms.

Table 11-9 JDBC Requirements for Different Platforms

Platform URL Template Driver Class

Teradata

jdbc:teradata://<host>:<port>/<server>

com.ncr.teradata.TeraDriver

Informix

jdbc:informix-sqli://<host>:<port>:informixserver=<servername>\;database=<dbname>\[\;<property>=<value>...\]

com.informix.jdbc.IfxDriver

Sybase

jdbc:sybase:Tds:<host>:<port>\[/<database>\]\[?<property>=<value>&<property>=<value>...\]

com.sybase.jdbc2.jdbc.SybDriver

MySQL

jdbc:mysql://<host>\[:<port>\]/\[<database>\]\[?<property>=<value>\[&<property>=<value>...\]\]

com.mysql.jdbc.Driver


For each database, you must also install the required JDBC driver. The JDBC driver for a database may be shipped with the product, or may require a separate download or purchase.