Replicate DB2 TABLE EBCDIC data to SQL TABLE - ASIS (MIRROR Image, no data transformation)

[ravirao68]

We want to do CDC from source IBM DB2 to target Microsoft SQL Server.  My queries are as follows:

a. Is it possible to copy IBM DB2 EBCDIC table data to be copied ASIS (i.e. MIRROR Image, no data transformation) to Microsoft SQL Server.?  We are using Collate SQL_EBCDIC037_CP1_CS_AS for char() and varchar() fields to store the EBCDIC data in the Microsoft SQL Table.

b. In the current CDC process, we see that IBM DB2 char() datatype is converted to varchar() in the Microsoft SQL Server - how can we retain the char() datatype in Microsoft SQL Server?

Could you please provide some solution to the above.

[DesmondWOO]

Hi @ravirao68 ,

1) EBCDIC
Please try if following transformation meets your need:

However, I am not sure if there might be any potential issues in this way.

2) CHAR/VARCHAR
Qlik Replicate treats both CHAR and VARCHAR as STRING and, by design, creates tables with VARCHAR columns. If you need to define both CHAR and VARCHAR columns in the same table, I recommend creating the target table manually.

Regards,
Desmond

[ravirao68]

Thanks Desmond for your answers.

On the point 1 - if the Source table is having VARCHAR datatype and if we change it to BYTES - will it have any issues?

On the point 2 - if we create the target table schema manually.  During the initial load - how to specify not to re-create the table at the target end?  Also for the CDC, I presume it should be fine.  Could you please confirm .

Regards,

Ravi

[john_wang]

Hello @ravirao68 ,

On the point 1 - We strongly recommend conducting a thorough acceptance test before implementing any changes to the production system.

On the point 2 - You can modify the Target Table Preparation setting, such as setting it to TRUNCATE before loading. For example:

Hope this helps.

John.

[ravirao68]

Thanks John for your answer. 

For Point 2, I was able to do TRUNCATE before loading.  It works fine.

For Point 1, I was able to create the same schema in the Target Database and performed the Replication.  However, I have noticed that the Source Data (IBM DB2 Table) VARCHAR data type is not replicating the MIRROR data to the Target Database (SQL Server Table).  Some source characters in the VARCHAR datatype gets converted always converted to X'6F'. 

for ex.  The following are few characters in their HEX form which are not replicated in original in the target table.

Source - IBM DB2 - VARCHAR (1800)	Target - SQL Server - VARCHAR (1800)
Mainframe IBM DB2 - EBCDIC - HEX	Windows SQL Server - EBCDIC - HEX
X'04'	X'6F'
X'06'	X'6F'
X'062C'	X'6F6F'
X'08'	X'6F'
X'15'	X'6F'
X'17'	X'6F'
X'20'	X'6F'
X'22'	X'6F'
X'23'	X'6F'
X'24'	X'6F'
X'28'	X'6F'
X'29'	X'6F'
X'31'	X'6F'
X'33'	X'6F'
X'34'	X'6F'
X'35'	X'6F'
X'36'	X'6F'
X'38'	X'6F'
X'39'	X'6F'

Source character data is EBCDIC X'22' and it is converted to EBCDIC X'6F' in target (SQL Server table).  most of the characters are converted to EBCDIC X'6F' in the target database.

Has anybody faced the same issue while replicating the DB2 data which has VARCHAR data.

PS:  The VARCHAR data has different data structure content in it.

Regards,

Ravi

[john_wang]

Hello @ravirao68 ,

I'm not sure how your test done however it seems works for me.

1. Create an EBCDIC table in DB2z

2. Change the data type to let Qlik Replicate retrieve the data as is(do not do encoding transform)

3. Create an EBCDIC database in SQL Server

4. Run a Full Load ONLY task, then check the result.

The detailed information as is:

1. Create table and insert hex value directly

2. The transform in table level

3. Create database with collation SQL_EBCDIC037_CP1_CS_AS

4. The hex values in SQL Server

If you still cannot manage it to work, please open a support ticket and attach task Diagnostics Package, and also the target database creation DDL, Our support team will be more than happy to assist you.

Regards,

John.

[ravirao68]

Hi John,

Thanks for your response.

I am yet to try this (need to get access to create the tables on the mainframe).  However, If I take the same above example, I have to make the following :

In the above point 1 & 2 - the changes would be as follows:

NOTES varchar field should contain the hex values.

insert into johnw.testebcdic values (4,'ebcdic=x04',x'04');
insert into johnw.testebcdic values (6,'ebcdic=x06',x'06');
insert into johnw.testebcdic values (33,'ebcdic=x33',x'33');

Transformation in table level would be as follows:
Source table (Input)
Name    Type       Character Set
NOTES STRING ibm-37_P100-1995

Target table (Output)
Name    Type       Subtype
NOTES STRING STRING(200)

Once after running, we need to have the same hex values in the target sql table (notes column).

I will check and get back to you.

Regards,

Ravi

[john_wang]

Hello Ravi, @ravirao68 

CHAR and VARCHAR datatypes are processed by the same transform in Qlik Replicate, the result should be similar.

Regards,

John.

[ravirao68]

Hi John,

We wanted to use Replicate CDC to get data from Mainframe in EBCDIC format into SQL Server.  There is a Windows Applications written in Micro Focus COBOL, which can consume the EBCDIC data from SQL Server.

Based on your inputs, we tried the following:

1. Insert HEX Values - X'00' to X'FF' into VARCHAR FIELD in DB2 Table in Mainframe
2. Ran Attunity Full Load to copy the DB2 Table in SQL Server
3. To validate the loaded data in SQL Table, we have Micro Focus COBOL Program to UNLOAD SQL Table in EBCDIC Format
4. We reviewed the Download File for the HEX Values and found there were few mismatch as follows:

Mismatch HEX Summary

EBCDIC Meaning	EBCDIC	Attunity Conversion
SEL	4	6F
RNL	6	6F
GE	8	6F
RPT	0A	6F
NL	15	6F
POC	17	6F
DS	20	6F
FS	22	6F
WUS	23	6F
BYP/INP	24	6F
SA	28	6F
SFE	29	6F
SM/SW	2A	6F
CSP	2B	6F
MFA	2C	6F
	31	6F
IR	33	6F
PP	34	6F
TRN	35	6F
NBS	36	6F
SBS	38	6F
IT	39	6F
RFF	3A	6F
CU3	3B	6F
	3E	6F
	FE	40
	FF	40

Our DB2 source table matches with SQL target table (column names & data types)

Mainframe DB2 table structure (SOURCE).

CREATE TABLE VTGDATA4.CSHF2
(COMPANY_CODE CHARACTER(3) FOR SBCS DATA
NOT NULL
WITH DEFAULT
,MASTER_ID CHARACTER(15) FOR SBCS DATA
NOT NULL
WITH DEFAULT
,SEGMENT_ID CHARACTER(2) FOR SBCS DATA
NOT NULL
WITH DEFAULT
,SEQUENCE_NUM DECIMAL(7, 0)
NOT NULL
WITH DEFAULT
,NUM_OCCURS DECIMAL(3, 0)
NOT NULL
WITH DEFAULT
,CSHF2_DATA VARCHAR(1800) FOR SBCS DATA
NOT NULL
WITH DEFAULT
,CONSTRAINT COMPANY_CODE PRIMARY KEY
(COMPANY_CODE
,MASTER_ID
,SEGMENT_ID
,SEQUENCE_NUM
)
)
IN VTGDATA4P.TCSHF2
APPEND NO
NOT VOLATILE CARDINALITY
DATA CAPTURE NONE
AUDIT NONE
CCSID EBCDIC;

SQL table structure (TARGET).

CREATE TABLE [VTGTEST3][CSHF2](
[COMPANY_CODE] [char](3) collate SQL_EBCDIC037_CP1_CS_AS NOT NULL ,
[MASTER_ID] [char](15) collate SQL_EBCDIC037_CP1_CS_AS NOT NULL,
[SEGMENT_ID] [char](2) collate SQL_EBCDIC037_CP1_CS_AS NOT NULL,
[SEQUENCE_NUM] [numeric](7, 0) NOT NULL,
[NUM_OCCURS] [numeric](3, 0) NOT NULL,
[CSHF2_DATA] [varchar](1800) collate SQL_EBCDIC037_CP1_CS_AS NOT NULL ,
CONSTRAINT [CSHF2_CSHF2_PK] PRIMARY KEY CLUSTERED
(
[COMPANY_CODE] ASC,
[MASTER_ID] ASC,
[SEGMENT_ID] ASC,
[SEQUENCE_NUM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

NOTE:  We tested the data into SQL Server with another process using COBOL program as follows:

1. Unload Mainframe DB2 table data to a Flat File.
2. Binary FTP the Flat File into Micro Focus Windows Environment - Compare File(1)
3. Run Micro Focus COBOL Program to LOAD data from File (1) into SQL Server Table in EBCDIC Format
4. Verify, if the data was loaded successfully
    Run Micro Focus COBOL Program to UNLOAD SQL Table in EBCDIC Format to Flat File - Compare File(2)
    Compare both Flat Files - File(1) & File(2) using Micro Focus Data File Compare Utility
    Both the files were successfully matched - thereby proving that SQL Server is able to load EBCDIC Data.

Could you please suggest, if we have missed out anything here.

Regards,

Ravi

[john_wang]

Hello Ravi, @ravirao68 ,

Thank you for the information. I'm unsure of the exact collation used in your SQL Server database. In my test, I set the database-level collation to SQL_EBCDIC037_CP1_CS_AS and left the column-level collations as default, which should inherit the same collation.

In my testing, the three hex values (0x'04', 0x'06', 0x'33') worked fine, so I believe other characters should also work as expected.

I recommend opening a support ticket for further assistance. Our support team will be glad to help, though please note that professional services (PS) might be required as this issue may fall outside of regular support scope.

Thanks,

John.