I want to use the JCC LogMiner Loader to replicate our Rdb data to an SQL Server database that uses mixed case names. Our source table looks like:
Columns for table SOURCE_TABLE_1:
Column Name Data Type Domain
----------- --------- ------
Comment: A unique numeric identifier for a role.
Comment: A descriptive name for the role.
Comment: A short description of the role covering the responsibilities
of the role and the people in that role.
Comment: The person that is in charge of this roles review and management.
REVIEW_DATE DATE VMS STANDARD_DATE
Comment: The date that this role will need to be reviewed by.
Oracle Rdb default: 17-NOV-1858 00:00:00.00
Table constraints for SOURCE_TABLE_1:
Primary Key constraint
Table constraint for SOURCE_TABLE_1
Evaluated on UPDATE, NOT DEFERRABLE
PRIMARY key (ROLE_NUMBER)
The target table looks like:
CREATE TABLE [dbo].[TargetTable1](
[RoleNumber] [int] NOT NULL,
[RoleName] [varchar](60) NULL,
[RoleDescription] [varchar](100) NULL,
[Owner] [varchar](30) NULL,
[ReviewDate] [datetime2](7) NULL,
CONSTRAINT [PK_TargetTable1] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA_1]
) ON [DATA_1]
How can I do this?
Writing to SQL Server and using case sensitive names involves a few steps in the initial setup beyond the default.
First, you must inform the Loader of the JDBC name delimiters used by the target and in the control files as described on page 153 in the 3.4 documentation. It requires the definition of 2 logical names in the DCL. There is even an example for SQL Server there.
$ define JCC_LML_JDBC_NAME_DELIM_START "["
$ define JCC_LML_JDBC_NAME_DELIM_END "]"
Next, you must use a MapTable to describe the target table and columns using the case sensitive names. To do this, ensure the table control file disables the implicit maptable…
! JCC LogMiner Loader Metadata (DDL) Control File
! Generated at 2014-03-18 15:36:20
…and create an explicit MapTable definition mapping the source name to the target case sensitive names:
Using this method on all of the tables will allow the JCC LogMiner Loader to replicate to targets using mixed case names.