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 Warehouse Builder.

This chapter contains the following topics:

Creating a New Platform
Defining the Properties of a New Platform
Creating a Microsoft Excel Platform
Using Custom Metadata Import in Platforms

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.

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 will be 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 as well as 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 Warehouse Builder

The Generic data types supported in 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 of time 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 new module. This includes:

Providing a Name and Access Method
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, 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 we define the platform on the Projects Navigator:

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

Next, we 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 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:

Create a new Excel module from the MICROSOFT EXCEL platform node in the Projects Navigator.
On the Name and Descriptions page of the Create Module wizard, specify Native Database Connection as the access method.
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. Note that 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 already 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 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:

Right-click the module and select Database Objects.

The Import Metadata Wizard is displayed.
Import the table from the Excel file in the same way you import tables from a database.
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 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. Note that 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 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. Note that 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.

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. Note that 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.