real-time replication DB2 zOS to SQLServer 2005?

Alma Belfield

real-time replication DB2 zOS to SQLServer 2005?
We're considering our options for replication from mainframe DB2 zOS V8 to SQLServer 2005 on a remote Wintel platform.
Hope to achieve near real-time, a couple of minutes lag for the insert/update/delete to flow thru to SQLServer.

Anyone have suggests as to suitable software, hopefully not too costly?

_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Wayne Campbell

Re: real-time replication DB2 zOS to SQLServer 2005?
(in response to Alma Belfield)
We're currently replicating between DB2 zOS V9 to SQLServer 2008. We're currently using IBMs InfoSphere Change Data Capture V6 (formerly Datamirror' transformation server). We just migrated from V5 to V6. V6 seems to be quite a bit faster than V5. The volume that can be replicate seems to be dependent on the size of the unit of work. Most of ours are small with only a few row being committed at a time (<10). With V5 we would process approximately 10,000 to 15,000 rows per 5 minute interval, with V6, we have seen in the range of 50,000 in a five minute interval. We have a monthly process that inserts approximately 400,000 rows in a single commit group that’s replicates over in less than 10 minutes. We only have one subscription running. You can increase throughput by running mutable subscriptions.

Things to think about:

Volume of data to be replicated.

CDC does not replicate load data. That’s why we have a process that’s inserts 400,000 records in a single unit of work.

Customer Expectations. They have to realize that the data is being replicated. Although it may normally be "real time" there will be times that it may fall behind.

Network Capabilities, when we started replicating we went to Jumbo Packet.

CPU resources. Were running a Z10 with 3 CP and a Ziip and Zaap. During the day when online transactions are taking place, CPU usage seems to bounce around the single digits, on average I would guess around 4% to 5% of a CPU.

Hopes this helps.


Wayne Campbell
DB2 DBA
Administrative Office of the Courts
(360) 705-5268
Email: [login to unmask email]
-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Alma Belfield
Sent: Wednesday, July 21, 2010 2:43 PM
To: [login to unmask email]
Subject: [DB2-L] real-time replication DB2 zOS to SQLServer 2005?

We're considering our options for replication from mainframe DB2 zOS V8 to SQLServer 2005 on a remote Wintel platform.
Hope to achieve near real-time, a couple of minutes lag for the insert/update/delete to flow thru to SQLServer.

Anyone have suggests as to suitable software, hopefully not too costly?

_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Larry D'Agostino

Re: real-time replication DB2 zOS to SQLServer 2005?
(in response to Wayne Campbell)
Hi Wayne,

I have a client who is looking for options to access DB2 z/OS V8 order and invoice data from a Microsoft SQL/Server 2008. Two of the options they came up with are Infosphere and SQL/Server Linked Server. I saw your post about Infosphere and was intrigued. When you write the CPU usage averaged around 4-5 % of CPU, how many MIPs are you running on the LPAR where Infosphere resides ? Are all processors available to this LPAR ?

I am asking, because my client is MIP-constrained .. running a Z10 box with 300 MIPs and 3 CPs ...... already running it flat out for large chunks of the day. Aside from the license cost of Infosphere, resource utilization is a concern. This client is probably going to end up trying to obtain near-real-time data and pull frequently. The largest DB2 tables are 300 M rows. DB2 updates would be 500,000 + an hour.

Any input would be appreciated

Thanks,


Larry

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Philip Sevetson

Re: real-time replication DB2 zOS to SQLServer 2005?
(in response to Larry D'Agostino)
Larry,



While I lack expertise in the products you are interested in, it seems to me that your key problem is inside DB2 and DDF; you need to minimize the amount of data which is returned across the network, and optimize the query activity as much as possible.



Are the customers (1) mainly interested in the same information (relatively few queries) over and over again, or (2) is their interest analytic/exploratory?



The first can be well served by a mix of views with optimized SQL, and stored procedures, which collectively return the minimum necessary data (minimizing network transport costs and times). The objective here is to see that all queries from the SQL Server client do not significantly load the mainframe.



The second should not be served by a distributed architecture with a constrained CPU, as the resulting workloads and CPU costs are unpredictable -- you don't know what they'll be asking and it's unlikely that the basic queries will be optimized. If they want to do Data Warehouse/BI querying using SQL Server, they should ante up for a separate Wintel database server machine with a time-delayed copy of the OLTP database and/or a data-warehouse, as their business needs dictate; they should be warned _not_ to expect to get DW/BI results from their current configuration without harming their production service levels.



--Phil Sevetson



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Larry D'Agostino
Sent: Wednesday, December 29, 2010 12:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] real-time replication DB2 zOS to SQLServer 2005?



Hi Wayne,



I have a client who is looking for options to access DB2 z/OS V8 order and invoice data from a Microsoft SQL/Server 2008. Two of the options they came up with are Infosphere and SQL/Server Linked Server. I saw your post about Infosphere and was intrigued. When you write the CPU usage averaged around 4-5 % of CPU, how many MIPs are you running on the LPAR where Infosphere resides ? Are all processors available to this LPAR ?



I am asking, because my client is MIP-constrained .. running a Z10 box with 300 MIPs and 3 CPs ...... already running it flat out for large chunks of the day. Aside from the license cost of Infosphere, resource utilization is a concern. This client is probably going to end up trying to obtain near-real-time data and pull frequently. The largest DB2 tables are 300 M rows. DB2 updates would be 500,000 + an hour.



Any input would be appreciated



Thanks,





Larry



_____________________________________________________________________

* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *

* Your only source for independent, unbiased, and trusted DB2 information. *

** DB2 certification -> no additional charge

** Meet fellow DB2 users and leading DB2 consultants

_____________________________________________________________________



If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv