Tag: MySQL, Case Sensitive, Special Character, Oracle GoldenGate 12c, Oracle GoldenGate 11g
If using Oracle GoldenGate 11g and prior version, the case sensitive column names (which includes name with space) are not supported. Therefore, you have to create case insensitive name alias before using Oracle GoldenGate.
In Oracle GoldenGate 12c, two parameters in GLOBAL file can be used to manage processing setup and they are:
USEANSISQLQUOTES | NOUSEANSISQLQUOTES
The default is USEANSISQLQUOTES. With this parameter, contents within double quote are case sensitive column names, content within single quote are literals. With NOUSEANSISQLQUOTES, the content within double quotes are literals.
The following is an example using Oracle GoldenGate 12c with the default USEANSISQLQUOTES setup. The source database table is defined as follows:
If using Oracle GoldenGate 11g and prior version, the case sensitive column names (which includes name with space) are not supported. Therefore, you have to create case insensitive name alias before using Oracle GoldenGate.
In Oracle GoldenGate 12c, two parameters in GLOBAL file can be used to manage processing setup and they are:
USEANSISQLQUOTES | NOUSEANSISQLQUOTES
The default is USEANSISQLQUOTES. With this parameter, contents within double quote are case sensitive column names, content within single quote are literals. With NOUSEANSISQLQUOTES, the content within double quotes are literals.
The following is an example using Oracle GoldenGate 12c with the default USEANSISQLQUOTES setup. The source database table is defined as follows:
mysql> desc TCUSTMER; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | CUST_CODE | varchar(4) | NO | PRI | NULL | | | NAME | varchar(30) | YES | | NULL | | | region code | varchar(30) | YES | | NULL | | | CITY | varchar(20) | YES | | NULL | | | STATE | char(2) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
The "region code" column has space in the name. The target database table is defined as follows:
mysql> desc TCUSTMER; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | CUST_CODE | varchar(4) | NO | PRI | NULL | | | NAME | varchar(30) | YES | | NULL | | | region_code | varchar(30) | YES | | NULL | | | CITY | varchar(20) | YES | | NULL | | | STATE | char(2) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 5 rows in set (0.01 sec)
The "region_code" column instead use underscore ("_") to replace the space character. The Oracle GoldenGate extract is setup as follows:
EXTRACT demoex01 SETENV (MYSQL_UNIX_PORT='/home/oracle/software/mysql_5.5.38/data/mysql.sock') DBOPTIONS CONNECTIONPORT 3305 DBOPTIONS HOST oraclelinux6.localdomain SOURCEDB test USERID root, PASSWORD mysql EXTTRAIL ./dirdat/extract/de TRANLOGOPTIONS ALTLOGDEST "/home/oracle/software/mysql_5.5.38/data/binlog/bin-lo g.index" FILTERTABLE test.checkpoint_tbl REPORTROLLOVER AT 05:30 ON saturday TABLE test.TCUSTMER; TABLE test.TCUSTORD;
The replicat on the target side is shown as follows:
replicat demorp01 setenv (MYSQL_UNIX_PORT='/home/oracle/software/mysql_5.6.19/data/mysql.sock') dboptions host oraclelinux6.localdomain, connectionport 3306 targetdb test, userid root, password mysql --assumetargetdefs sourcedefs ./dirdat/replicat/democust.def discardfile ./dirrpt/demprp01.dsc, purge map test.TCUSTMER, target test.TCUSTMER, colmap(usedefaults, "region code"=regio n_code); map test.TCUSTORD, target test.TCUSTORD;
(Created 05/15/2014)