Help on replication from SQL 2005 to DB2/AS400

I was trying to set up a replication process to move data from SQL 2005 to DB2/AS400.

To get it started, I have create a simplest table (say, PERSON) on SQL 2005 server, and the same table (PERSON) on DB2/AS400. I have only one column SSN (of type int). I have journaled the table on DB2 end. I was able to have successfully configured publisher (on SQL 2005) and subscriber (i.e. a pushed subscription). I used transactional replication. And I also installed HIS 2004 to have DB2OLEDB ready. I was able to use query/insert/delete against table on DB2 based the linked server (that I created for testing purpose).

I did a lot of reading on this and my impression is that replicating to DB2/AS400 is supported from SQL 2005. But I always have this error from replication monitor. Can anyone enlighten me what was wrong.

Command attempted:

CREATE TABLE MY_DB.PERSON(
SSN
INT NOT NULL)

(Transaction sequence number: 0x00000020000000F9000100000004, Command ID: 4)

Error messages:

A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42710, SQLCODE: -601 (Source: MSSQL_REPL_DB2, Error number: -601)
Get help: http://help/-601

A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42710, SQLCODE: -601 (Source: MSSQL_REPL_DB2, Error number: -601)
Get help: http://help/-601

I assume you've read Books Online topic IBM DB2 Subscribers already? If so, try this KB article: http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;Q239458

|||

Hi Steve,

Couple questions?

Did you setup the replication through user interface or replication stored procedures?

It seems like that you are trying to publisher data from SQL server to an "existing" DB2 table, is this a safe assumption to make? If this is what you are attempting, UI does not support this scenario, you will have to set it up through the stored procedures.

Let me know if you need more help.

Gary

|||

Hi Gary and Greg:

1). To answer Gary's questions -

Yes. i setup replication through user interface (i.e. SQL 2005 management studio). Your assumption is correct. I was trying to replicate data from SQL 2005 to DB2/AS400.

I have always used UI to set up the replication process (like SQL to SQL). Unfortunately I have rarely used stored procedure to setup replication although i know it is doable. In another word, i do not have any hands-on experience yet to set up replication from stored procedures. Can you post some samples here or can you enlighten me in a different way please?

2) Greg - i have installed SQL 2005 SP1 on my machine. I wonder if i need to download the special fix that the article refers to.

I have read all of Books Online topics of IBM DB2 Subscribers. It is a kind of generic guidance but not specific enough to help solve my issue. Plus, some of description is a bit vague.

Thank you very much for your attention anyway. Please help me more if you can. I do appreciate it.

Steve

Thanks in advance.

|||

Hi Steve,

Here ia a sample script for setuping replication for DB2 subscriber without dropping and re-creating the table on DB2 subscriber side.

Please note:

For sp_addpublication, the following have to be set
@.sync_method=N'character',
@.enabled_for_het_sub='true'

For sp_addarticle, the following have to be set
@.creation_script=null,
@.pre_creation_cmd=N'none',
@.schema_option=0x00,
@.ins_cmd=N'SQL',
@.del_cmd=N'SQL',
@.upd_cmd=N'SQL',

For sp_addsubscription , the following have to be set
@.update_mode=N'read only',
@.subscriber_type=3

The above list is the ones I think are the more critical ones to get them right (hopefully I didn't miss anything). The sample will require some modification to your environment such as server name, user account, password and DB2 connection info.

After running the following script, if you chose to run the snapshot, logread, and distribution agents manually, I have included the command line statement at the bottom of the script.

And don't forget to create a table schema on the DB2 side first.

Let me know if this solve your problem.

Cheers,

Gary

/* This posting is provided as is. */

Sample Script

USE master
EXEC sp_adddistributor 'myservername'
go

EXEC sp_adddistributiondb 'distribution', @.data_folder='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA', @.data_file='ddata.dat', @.data_file_size=32, @.log_folder='c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA', @.log_file='dlog.dat', @.log_file_size=16, @.security_mode=0, @.login='sa', @.password='******'
go

EXEC sp_adddistpublisher @.publisher='myservername', @.distribution_db='distribution', @.security_mode=0, @.login='sa', @.password='******', @.working_directory='\\myservername\MSSQL\REPLDATA'
go

create database [pubdb]
go

pubdb..sp_replicationdboption 'pubdb', 'publish', 'true'
go

-- This table is used to store the DB2 connection string, it can save you to time to copy/paste the string.
create table [_provider] ([string] varchar(1000))
go
-- Please replace with your DB2 connection string
insert into [_provider] ([string]) values ('Provider=DB2OLEDB;........')
go

use pubdb
go
-- Please replace with a valid user account
exec sp_addlogreader_agent @.job_login='MYDOMAIN\USERID' , @.job_password='******' , @.publisher_security_mode=1
go
-- Create a sample table on SQL server, assuming a same table called [MYTEST] already exist on DB2
create table [MYTEST] ([PKCOL] tinyint primary key)
insert into [MYTEST] ([PKCOL]) values (77)
go

exec sp_addpublication
@.publication=N'DTYPETEST',
@.sync_method=N'character',
@.retention=0,
@.allow_push=N'true',
@.allow_anonymous=N'false',
@.autogen_sync_procs='false',
@.enabled_for_internet=N'false',
@.snapshot_in_defaultfolder=N'true',
@.compress_snapshot=N'false',
@.allow_subscription_copy=N'false',
@.add_to_active_directory=N'false',
@.repl_freq=N'continuous',
@.status=N'active',
@.independent_agent=N'true',
@.immediate_sync=N'true',
@.allow_sync_tran=N'false',
@.allow_queued_tran=N'false',
@.allow_dts=N'false',
@.replicate_ddl=0,
@.allow_initialize_from_backup=N'false',
@.enabled_for_het_sub='true'
go

exec sp_addarticle
@.publication=N'DTYPETEST',
@.article=N'MYTEST',
@.source_owner=N'dbo',
@.source_object=N'MYTEST',
@.type=N'logbased',
@.description=null,
@.creation_script=null,
@.pre_creation_cmd=N'none',
@.schema_option=0x00,
@.identityrangemanagementoption=N'none',
@.destination_table=N'MYTEST',
@.destination_owner=null,
@.vertical_partition=N'false',
@.ins_cmd=N'SQL',
@.del_cmd=N'SQL',
@.upd_cmd=N'SQL',
@.force_invalidate_snapshot=1,
@.fire_triggers_on_snapshot='false'
go

exec sp_addsubscription
@.publication=N'DTYPETEST',
@.subscriber=N'DB2NTOLEDB',
@.destination_db=N'(default destination)',
@.subscription_type=N'Push',
@.frequency_type=1,
@.frequency_interval=0,
@.frequency_relative_interval=0,
@.frequency_recurrence_factor=0,
@.frequency_subday=0,
@.frequency_subday_interval=0,
@.active_start_time_of_day=0,
@.active_end_time_of_day=0,
@.active_start_date=0,
@.active_end_date=20070101,
@.enabled_for_syncmgr=N'False',
@.offloadagent=0,
@.sync_type=N'Automatic',
@.article=N'all',
@.update_mode=N'read only',
@.dts_package_location=N'Distributor',
@.subscriber_type=3
go

declare @.provstr varchar(1000)
select @.provstr=[string] from [master]..[_provider]
exec sp_addpushsubscription_agent
@.publication='DTYPETEST',
@.subscriber='DB2NTOLEDB',
@.subscriber_db='(default destination)',
@.subscriber_security_mode=0,
@.subscriber_login='Your DB2 User ID',
@.subscriber_password='Your DB2 Password',
@.job_login='Windows account or SQL server account',
@.job_password='******' ,
@.subscriber_provider_string=@.provstr,
@.subscriber_provider='DB2OLEDB',
@.subscriber_catalog='DB2 catalog'

--"c:\Program Files\Microsoft SQL Server\90\COM\SNAPSHOT.EXE" -Publisher MYSERVER -PublisherDB pubdb -Publication DTYPETEST -Distributor MYSERVER -OutputVerboseLevel 1 -DistributorLogin sa -DistributorPassword password

--"c:\Program Files\Microsoft SQL Server\90\COM\LOGREAD.EXE" -Publisher MYSERVER -PublisherDB pubdb -Distributor MYSERVER -OutputVerboseLevel 1 -DistributorLogin sa -DistributorPassword password

--"c:\Program Files\Microsoft SQL Server\90\COM\DISTRIB.EXE" -Subscriber DB2NTOLEDB -Publisher MYSERVER -PublisherDB pubdb -Distributor MYSERVER -Publication DTYPETEST -SubscriberType 3 -OutputVerboseLevel 2 -DistributorLogin sa -DistributorPassword password
THE END -

|||

Wow, a super nice reply and posting. Thanks much Gary. Seems you are quite knowledgeable in using replication to DB2.

I will give it a shot today to see how it works.

- Steve

Incidently I wonder why Microsoft does not elaborate this on Books Online in detailed.

|||

Hi Gary -

Thank you for the very detailed info and sample scripts. That is very helpful in getting me started to use script to create replication/subscription.

I have tried over and over, and unfortunately i was not able to get it to work. Shawn told me that he talked to you and you two are going to help me out of this trouble.

Thanks a lot.

Steve

|||

What is the problem you are encountering? Can you provide more detail? At what point does it fail? Maybe the schema of your table may help.

Thanks,

Gary

|||

Hi Gary -

I worked on this with a couple of your Microsoft engineers last Friday. Finally I got it to work using scripts. But there are many limitations for replication from SQL server 2005 to DB2 as compared with the replication from SQL 2005 to SQL 2005. The following are the main limitations i found:

1) The replication can NOT replicate schema change from SQL server to DB2. It makes replication to fail.

2) type problems – when replicating data from a type of either nchar(10) or nvarchar(10) to BD2's GRAPHIC(10). The replication ALWAYS FAILED.

QUESTIONS:

Currently I tested DB2OLEDB using connection string like “……Units of Work=RUW; … “, I wonder how replication supports data consistence and reliability. In case of replication failure, how does the rollback work? Without using distributed transcation enabled (like above), how the replication support guaranteed data delivery ?

- Steve

|||

My SQL side table scema:

CREATE TABLE [dbo].[MYTEST](

[PKCOL] [tinyint] NOT NULL,

[NEW_COL1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_MYTEST_NEW_COL] DEFAULT (N'nchar'),

[NEW_COL2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_MYTEST_NEW_COL2] DEFAULT (N'nvarchar'),

CONSTRAINT [PK__MYTEST__7C8480AE] PRIMARY KEY CLUSTERED

(

[PKCOL] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

The DB2's table scema is:

CREATE TABLE PUBDB.MYTEST (

PKCOL INTEGER DEFAULT NULL ,

NEW_COL1 GRAPHIC(10) CCSID 835 DEFAULT NULL ,

NEW_COL2 GRAPHIC(10) CCSID 835 DEFAULT NULL ,

CONSTRAINT PUBDB.Q_PUBDB_MYTEST_PKCOL_00001 PRIMARY KEY( PKCOL ) ) ;

|||

1) SQL server does not support DDL (schema change) for DB2 subscriber.

2) Replication failed from nchar(10) or nvarchar(10) to DB2's GRAPHIC(10) is because of the default datatype mapping from SQL to DB2. By default, both nchar(10) and nvarchar(10) are mapped to VARCHAR on DB2 side. I believe this can be changed. Please check out the following article,

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/bfd2e111-cada-4304-80a8-c1e1061573b5.htm

When you said rollback, do you mean rollback on SQL publisher? Or DB2 subscriber?

Cheers,

Gary

The posting is provided as is

|||

1) SQL server does not support DDL (schema change) for DB2 subscriber.

IT IS REALLY TRUE. I HAVE TRIED THIS AND IT DID EXACTLY THE SAME AS WHAT YOU SAID HERE. ONE OF YOUR MICROSOFT ENGINEER ALSO PROVED THIS AS WELL.

2) As of datatype mapping, i have tried to modify the following file:

C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles\SqlClientToDB2.xml

and

C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles\SqlClientToIBMDB2.xml

Can you confirm if these files are the ones that need to customize for datatype mapping?

3) Regarding rollback-- while in the process of replication of a record or a batch of several records, network fails (i.,e. broken), does publisher roll back those records that have been propergated to the subscriber? How does publisher know which record fails so that it knows where to start next time when network is restored.

Thanks.