June 13, 2008 at 10:49 am
I am trying to setup transactional replication between SQL 2005 and DB2 on MVS platform.
I am using Microsoft's OLE DB provider for DB2 driver to setup the connection. (Data Access tool)
Below is the error message that i am get:
"The process could not connect to Subscriber 'DB2'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084)
Get help: http://help/MSSQL_REPL20084
The parameter is incorrect. (Source: MSSQL_REPL_DB2, Error number: 268435456)
Get help: http://help/268435456"
Any help would be appreciated.
Thanks
Prakash BhojeGowda
Prakash B
March 18, 2011 at 7:36 am
I got the same error and can't find info about that on Internet.
How I can solve this problem?
March 18, 2011 at 9:28 am
Not sure if your environment is same as ours.
Our DB2 databases reside on shared environments. Also, we are having 64 bit operating system + 64 bit SQL Server Enterprise Edition server.
Are you using the conenction string created using the DB2 Data access tool that comes with the MS OLEDB provider? Are you able to test connection using that tool?
Issue that we had has been resolved. Microsoft sent a new OLEDB provider which included the option to enter the name of the database.
We had to install OLDEB provider for 64 bit + MSOLEDBProviderforDB2V2-KB960072-x64-ENU.exe which was provided to us by microsoft specifically for our environment.
Microsoft had promised that this special hot fix will be incorporated in future OLE DB providers.
The ID used to connect to DB2 should have priviliges to creat packages and bind packages. This task has to be done only once.
Let me know if you have any further questions.
Prakash B
March 21, 2011 at 2:30 am
Prakash.Bhojegowda (3/18/2011)
Not sure if your environment is same as ours.
We have 32 bit operating system + 32 bit SQL Server Enterprise Edition server.
Are you using the conenction string created using the DB2 Data access tool that comes with the MS OLEDB provider? Are you able to test connection using that tool?
Yes I'm using it and I can do a test connection, a test package and a test query with no problems.
The ID used to connect to DB2 should have priviliges to creat packages and bind packages. This task has to be done only once.
Yes, the used ID have the required privileges to create and blind packages.
When I start the replication I get the following:
"The process could not connect to the subscriber" (MSSQL_REPL20084)
"The parameter is incorrect" (Error number: 268435456)
March 21, 2011 at 11:05 am
HOW TO BUILD TRANSACTIONAL REPLICATION FROM SQL2005 TO DB2
One time tasks per subsystem on the host (DB2)
Use a properly installed Data Access Tool.
We installed Host Integration Services (trial) on Andy's machine and used the Microsoft OLE DB Provider for DB2 --> Data Access Tool to bind the packages on the host. Tsoid that will be used for binding the packages on the host needs “BINDADD” authority, and “CREATEIN SCHEMA” privilege. See the article below for more detailed information.
“Create Packages for Use with ODBC Driver for DB2 and OLE DB Provider for DB2”
http://support.microsoft.com/kb/216810/EN-US/
Tasks that will need to be repeated for each replication setup.
Make the appropriate substitutions to the items below in accordance with your needs.
•Generate the connection string from the Data Access Tool.
•Create the tables on the SQLServer 2005 and the host.
•Run the replication scripts. (scripts will have to be customized according to your needs, but the scripts provide a solid starting point.)
POC DETAILS FOR 2005 TO DB2 REPLICATION.
1. We created a test table on SQL 2005 database for test.
USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TABLE_1](
[COL1] [int] NOT NULL,
[COL2] [int] NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[COL1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
2.Also built an identical table on the host (DB2)
3. We used the following script to build the publication.
use [TestDB]
exec sp_replicationdboption @dbname = N'TestDB', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [MiscellaneousReports]
exec sp_addpublication @publication = N'yyyy', @description = N'Transactional publication of database ''TestDB'' from Publisher ''Server name''.', @sync_method = N'native', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @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'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
Prakash B
March 28, 2011 at 2:28 am
The problem still persist.
I generate the following provider string:
Provider=DB2OLEDB;User ID=****;Password=****;Initial Catalog=PALAMOS;Network Transport Library=TCP;Host CCSID=1252;PC Code Page=1252;Network Address=SERVERADRESS;Network Port=446;Package Collection=RESTAURA;Default Schema=RESTAURA;Process Binary as Character=False;Units of Work=RUW;Default Qualifier=RESTAURA;DBMS Platform=DB2/AS400;Defer Prepare=False;Persist Security Info=True;Connection Pooling=False;Derive Parameters=False;
In the Data Acccess Tool I created the packages and I checked in the table from AS400 called QSYS2.SYSPACKAGE that the packages have been created.
I do a sample query with Data Acess Tool and I can see the table_catalog, table_schema, table_name etc and all is correct.
Then I try something more easy than a sync/replication, I try to do a linked server (is just a name and the provider string from Data Acess Tool) and I don't get any error but I can't expand the navigation to move inside the tables in my AS400 server.
I belive that if I can't do a linked server, then I can't do the sync/replication between the servers.
Someone have a solution for this?
March 28, 2011 at 3:21 am
Good news everyone, I can do querys to the tables in the AS400 with the SQL Server Managament Studio, then my provider string is good and I have a link to the server.
Now we go to the big problem: The replication of the tables from SQL Server 2005 to DB2 AS400.
March 31, 2011 at 12:52 am
Hi again 🙂
I think I'm just in the last step for do the replication, now I get this error:
"The job failed. The job was invoked by user . THe last step to run was step 3 (Detect nonlogged agent shutdown)."
April 6, 2011 at 12:28 am
Problem resolved.
Solution: Activate journaling on the tables and use of the microsoft ole db provider for odbc
November 15, 2016 at 9:00 am
i configuring replications to DB2 and have errors when inserteing data:
error:
"Table1" ?BUF ?3 in *N not valid for operation. SQLSTATE: 55019, SQLCODE: -7008 (Source: MSSQL_REPL_DB2, Error number: -7008)
What is and where ?
data format like as:
1
<x$3>
<x$3>
-1
<x$3>
Unable
<,@g>
2
<x$3>
<x$3>
0
<x$3>
TypeCategory
<,@g>
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply