JCC LogMiner Loader and Mixed Case Names

Question

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
-----------                     ---------        ------
ROLE_NUMBER                     INTEGER
Comment:       A unique numeric identifier for a role.
ROLE_NAME                       CHAR(60)
Comment:       A descriptive name for the role.
ROLE_DESCRIPTION                CHAR(100)
Comment:       A short description of the role covering the responsibilities
                of the role and the people in that role.
OWNER                           CHAR(30)
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:
APP_PRIV_ROLE_PK
Primary Key constraint
Table constraint for SOURCE_TABLE_1
Evaluated on UPDATE, NOT DEFERRABLE
Source:
        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
(
       [RoleNumber] ASC
)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?

Answer

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
!
Table~SOURCE_TABLE_1~1~~NoMAPTABLE
Primary Key~SOURCE_TABLE_1~ROLE_NUMBER~1~4~0~8~0
Column~SOURCE_TABLE_1~ROLE_NAME~2~60~0~14~0

…and create an explicit MapTable definition mapping the source name to the target case sensitive names:

MapTable~SOURCE_TABLE_1~SOURCE_TARGET_1,TargetTable1~Replicate
MapColumn~SOURCE_TARGET_1~ROLE_NUMBER,[RoleNumber]
o
o
o
MapKey~SOURCE_TARGET_1~[RoleNumber]

Using this method on all of the tables will allow the JCC LogMiner Loader to replicate to targets using mixed case names.