Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    Jan 20
    2011

    Best practices for databases being replicated [SEC=UNCLASSIFIED]

    Steve Tennant
    Hi Lynne,
    we currently replicate, though not to DB2, and one thing I'd like to
    point out is that as you are probably removing RI and some (unique)
    indexes you will need a robust method of verifying that the data you
    extracted (we use CA Log Analyzer) is complete and that it's application
    in the target system is also complete.

    Row counts may be enough if you are going table-at-a-time. The
    point is that dropping all of our usual controls allows data errors to
    creep in which will not be picked up by the target RDBMS and so you need
    to build the verification if you are not going to replicate the entire
    data set each time.

    Steve T

    ________________________________

    From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Lynne Flatley
    Sent: Friday, 21 January 2011 2:55 AM
    To: [login to unmask email]
    Subject: Re: [DB2-L] Best practices for databases being replicated


    Wow, lots of things to think about...

    Regarding your question, do you use ICs or use replication to re-sync
    problem tables, we're only at the the planning stage right now.
    Supposedly, it's going to be a small Data Mart, under 40GB so if/when a
    problem with a specific table, I think we would just unload the source
    table and load to the target table. They will be on different DB2
    subsystems.

    I am a contractor at this company and they do not have DRDA set up
    between subsystems (unfortunately). Is this something they will need for
    replication using QREP?


    On Thu, Jan 20, 2011 at 10:42 AM, Lyon, Lockwood <[login to unmask email]>
    wrote:


    Lynne,

    There are several things you need to configure differently in
    your target tables, depending on their intended use.

    Among these are RI (usually left OFF), columns with the IDENTITY
    attribute (usually REMOVED), triggers (usually REMOVED). Sometimes there
    are lots of index changes -- some are REMOVED (ex. you no longer require
    an index to enforce PK or uniqueness, since this is (supposedly)
    guaranteed in the target system), some changed, some re-configured
    vis-a-vis CLUSTER, etc. for performance, and so forth.

    Another ODS-to-DW issue involves time-series data, purging, and
    partitioning. You may have segmented TS in ODS, but partitioned in DW
    (by date, region, etc.) due to analytic requirements and needs to purge
    old data (perhaps by partition rotation).

    Yet another issue involves backup and recovery in the target
    system. Do you use ICs, or use replication to re-sync problem tables?

    HTH

    - Lock Lyon
    Fifth Third Bancorp



    From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell,
    Raymond
    Sent: Thursday, January 20, 2011 10:09 AM

    To: [login to unmask email]

    Subject: Re: [DB2-L] Best practices for databases being
    replicated

    Don't wonder about it; it's definitely a good idea - to NOT have
    RI on the target tables. The relationships have already been
    validated/enforced in the source system - why do it in the target as
    well?

    More practically, you'll save a little MIPS by not having DB2
    re-check the RI at the target, plus make the replication process easier
    (and easier to restart) without it.

    Cheers,


    Raymond

    ________________________________________
    Raymond Bell
    Senior Software Consultant
    BMC Software

    Phone: +44 (0) 1784 478 558
    Mobile: +44 (0) 7894 608 214
    Fax: +44 (0) 1784 478 753

    Assurance House
    Vicarage Road, Egham
    Surrey TW20 9JY, United Kingdom

    From: Lynne Flatley [mailto:[login to unmask email]
    Sent: Thursday, January 20, 2011 08:56 AM
    To: [login to unmask email] <[login to unmask email]>
    Subject: Re: [DB2-L] Best practices for databases being
    replicated

    Also, I'm wondering if I should 'suggest' or make the strong
    case for not having RI on the target tables (RI does exist on the source
    tables). Because the replication is asynchronous, a child table's row
    could potentially arrive before the parent table's row and if there were
    RI in the database, the child insert would fail?



    This e-mail transmission contains information that is
    confidential and may be privileged. It is intended only for the
    addressee(s) named above. If you receive this e-mail in error, please do
    not read, copy or disseminate it in any manner. If you are not the
    intended recipient, any disclosure, copying, distribution or use of the
    contents of this information is prohibited. Please reply to the message
    immediately by informing the sender that the message was misdirected.
    After replying, please erase it from your computer system. Your
    assistance in correcting this error is appreciated.



    _____________________________________________________________________
    * IDUG North America * Anaheim, California * May 2-6 2011 *
    http://IDUG.ORG/NA *
    * Your only source for independent, unbiased, and trusted DB2
    information. *

    _____________________________________________________________________
    http://www.IDUG.org/mentor
    Mentoring should be a rewarding experience for everyone...
    IDUG is offering up to 80% off when you both come to the
    conference!

    _____________________________________________________________________

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




    ________________________________

    Introducing IBM(r) DB2(r) 10 for z/OS
    < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

    The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
    are not already an IDUG member, please register here.
    < http://www.idug.org/register >


    ***************************************************************************************************
    IMPORTANT:

    * This transmission is intended for the use of the addressee only and might contain sensitive or legally privileged information. If you are NOT the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error, please notify the author immediately by telephone and delete all copies of this transmission together with any attachments.

    * The Australian Customs and Border Protection Service DOES NOT AUTHORISE the recipient to further disclose this email or its contents without permission of the originator.

    * Unsolicited commercial emails MUST NOT be forwarded to the originator of this transmission unless prior consent has been given.

    ***************************************************************************************************

    _____________________________________________________________________
    * IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
    * Your only source for independent, unbiased, and trusted DB2 information. *
    _____________________________________________________________________
    http://www.IDUG.org/mentor
    Mentoring should be a rewarding experience for everyone...
    IDUG is offering up to 80% off when you both come to the conference!
    _____________________________________________________________________

    If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
    Phil Grainger
    [BMC Software]
    I suppose you have determined that this is the only viable approach to take? And you can't use the operational data also as your data store?

    I'm constantly amazed at how many copies of the same data we toss around and then constantly worry about which is the "most correct" copy

    As far as possible, I'm inclined to suggest that one best practice for data replication might be "DON'T" - now I'll duck before I get things thrown at me :)

    Phil Grainger
    Cogito Ltd.
    [login to unmask email]
    +44 (0) 1298 872 148
    +44 (0) 7505 266 768
    www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

    Attend IDUG 2011 - the premiere events for DB2 professionals.
    IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
    IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic


    From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Lynne Flatley
    Sent: 20 January 2011 14:03
    To: [login to unmask email]
    Subject: [DB2-L] Best practices for databases being replicated

    Good morning!

    I'm going to be responsible for maintaining both a small data mart and its operational data store. Both are on DB2 V9 z/OS. I've worked in several shops which used replication but I'm never been directly responsible for a database using replication. We'll be using QREP. What are some of the procedures/best practices used when maintaining a replicated database?

    I know of two; when altering a replicated table to add a column, its tablespace must be reorged immediately (or as soon as possible?), if the tablespace is compressed, keepdictionary must be specified when doing the reorg.

    Can anyone else think of any others?

    Thanks!

    ________________________________

    [ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

    The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

    _____________________________________________________________________
    * IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
    * If you are going to attend only one conference this year, this is it! *
    ** The most DB2 technical sessions of any conference
    ** Access IBM experts and developers
    _____________________________________________________________________

    If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
    Lynne Flatley
    [Northrop Grumman]
    Well, I'm a contractor on the project *and* we're hosting the
    subsystems/databases so I have zero say in design issues.

    I agree, I've always been a proponent of "read it where it sits" but that
    train left the station a long time ago. I view this as another opportunity
    to add a skill to my resume thereby making me more marketable whether I
    agree with the idea or not. <also ducking>


    On Fri, Jan 21, 2011 at 10:51 AM, Phil Grainger
    <[login to unmask email]>wrote:

    > I suppose you have determined that this is the only viable approach to
    > take? And you can’t use the operational data also as your data store?
    >
    >
    >
    > I’m constantly amazed at how many copies of the same data we toss around
    > and then constantly worry about which is the “most correct” copy
    >
    >
    >
    > As far as possible, I’m inclined to suggest that one best practice for data
    > replication might be “DON’T” – now I’ll duck before I get things thrown at
    > me J
    >
    >
    >
    > *Phil Grainger*
    > Cogito Ltd.
    >
    > [login to unmask email]
    > +44 (0) 1298 872 148
    > +44 (0) 7505 266 768
    >
    > www.cogito.co.uk
    >
    >
    >
    > Attend IDUG 2011 - the premiere events for DB2 professionals.
    >
    > IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
    > IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech
    > Republic
    >
    >
    >
    > *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Lynne
    > Flatley
    > *Sent:* 20 January 2011 14:03
    >
    > *To:* [login to unmask email]
    > *Subject:* [DB2-L] Best practices for databases being replicated
    >
    >
    >
    > Good morning!
    >
    >
    > I'm going to be responsible for maintaining both a small data mart and its
    > operational data store. Both are on DB2 V9 z/OS. I've worked in several
    > shops which used replication but I'm never been directly responsible for a
    > database using replication. We'll be using QREP. What are some of the
    > procedures/best practices used when maintaining a replicated database?
    >
    > I know of two; when altering a replicated table to add a column, its
    > tablespace must be reorged immediately (or as soon as possible?), if the
    > tablespace is compressed, keepdictionary must be specified when doing the
    > reorg.
    >
    > Can anyone else think of any others?
    >
    > Thanks!
    >
    > ------------------------------
    >
    > [image: Introducing IBM® DB2® 10 for z/OS ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >
    >
    > The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
    > not already an IDUG member, please register here. < http://www.idug.org/register >
    >
    > ------------------------------
    >
    > [image: Independent, not-for-profit, User Run - the IDUG difference! ] < http://www.idug.org >
    >
    > The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
    > not already an IDUG member, please register here. < http://www.idug.org/register >
    >

    _____________________________________________________________________
    * IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
    * If you are going to attend only one conference this year, this is it! *
    ** The most DB2 technical sessions of any conference
    ** Access IBM experts and developers
    _____________________________________________________________________

    If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
    Myron Miller
    [NONE OF YOUR BUSINESS]
    I'm late in this game but why the need for QREP? If the tables are identical in
    the Operational data store and the data mart, then DSN1COPY works equally as
    well and significantly faster with a whole lot less fuss. Lots of shops create
    copies of production operational data this way on both the same Z/OS machine and
    on different machines.

    Myron



    ________________________________
    From: Lynne Flatley <[login to unmask email]>
    To: [login to unmask email]
    Sent: Fri, January 21, 2011 11:11:42 AM
    Subject: Re: [DB2-L] Best practices for databases being replicated

    Well, I'm a contractor on the project *and* we're hosting the
    subsystems/databases so I have zero say in design issues.


    I agree, I've always been a proponent of "read it where it sits" but that train
    left the station a long time ago. I view this as another opportunity to add a
    skill to my resume thereby making me more marketable whether I agree with the
    idea or not. <also ducking>



    On Fri, Jan 21, 2011 at 10:51 AM, Phil Grainger <[login to unmask email]>
    wrote:

    I suppose you have determined that this is the only viable approach to take? And
    you can’t use the operational data also as your data store?
    >
    >I’m constantly amazed at how many copies of the same data we toss around and
    >then constantly worry about which is the “most correct” copy
    >
    >As far as possible, I’m inclined to suggest that one best practice for data
    >replication might be “DON’T” – now I’ll duck before I get things thrown at me J
    >
    >Phil Grainger
    >Cogito Ltd.
    >[login to unmask email]
    >+44 (0) 1298 872 148
    >+44 (0) 7505 266 768
    >www.cogito.co.uk
    >
    >Attend IDUG 2011 - the premiere events for DB2 professionals.
    >IDUG North America, 2-6 May, Anaheim California
    >IDUG EMEA, 14-18 November, Prague Czech Republic
    >
    >
    >
    >From:IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Lynne Flatley
    >Sent: 20 January 2011 14:03
    >
    >To: [login to unmask email]
    >Subject: [DB2-L] Best practices for databases being replicated
    >
    >Good morning!
    >
    >
    >I'm going to be responsible for maintaining both a small data mart and its
    >operational data store. Both are on DB2 V9 z/OS. I've worked in several shops
    >which used replication but I'm never been directly responsible for a database
    >using replication. We'll be using QREP. What are some of the procedures/best
    >practices used when maintaining a replicated database?
    >
    >
    >I know of two; when altering a replicated table to add a column, its tablespace
    >must be reorged immediately (or as soon as possible?), if the tablespace is
    >compressed, keepdictionary must be specified when doing the reorg.
    >
    >Can anyone else think of any others?
    >
    >Thanks!
    >
    >
    >
    ________________________________

    >The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
    >already an IDUG member, please register here.
    >
    >________________________________

    >The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
    >already an IDUG member, please register here.
    >

    ________________________________

    The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
    already an IDUG member, please register here.


    _____________________________________________________________________
    * IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
    * If you are going to attend only one conference this year, this is it! *
    ** The most DB2 technical sessions of any conference
    ** Access IBM experts and developers
    _____________________________________________________________________

    If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
    Lynne Flatley
    [Northrop Grumman]
    Well, the designers of this system want it to be near real-time reporting.
    My organization is hosting the databases so design is not done here. I'm
    just happy to have the opportunity to work with QREP.

    On Fri, Jan 21, 2011 at 11:39 AM, Myron Miller <[login to unmask email]>wrote:

    > I'm late in this game but why the need for QREP? If the tables are
    > identical in the Operational data store and the data mart, then DSN1COPY
    > works equally as well and significantly faster with a whole lot less fuss.
    > Lots of shops create copies of production operational data this way on both
    > the same Z/OS machine and on different machines.
    >
    > Myron
    >
    > ------------------------------
    > *From:* Lynne Flatley <[login to unmask email]>
    >
    > *To:* [login to unmask email]
    > *Sent:* Fri, January 21, 2011 11:11:42 AM
    > *Subject:* Re: [DB2-L] Best practices for databases being replicated
    >
    > Well, I'm a contractor on the project *and* we're hosting the
    > subsystems/databases so I have zero say in design issues.
    >
    > I agree, I've always been a proponent of "read it where it sits" but that
    > train left the station a long time ago. I view this as another opportunity
    > to add a skill to my resume thereby making me more marketable whether I
    > agree with the idea or not. <also ducking>
    >
    >
    > On Fri, Jan 21, 2011 at 10:51 AM, Phil Grainger <
    > [login to unmask email]> wrote:
    >
    >> I suppose you have determined that this is the only viable approach to
    >> take? And you can’t use the operational data also as your data store?
    >>
    >>
    >>
    >> I’m constantly amazed at how many copies of the same data we toss around
    >> and then constantly worry about which is the “most correct” copy
    >>
    >>
    >>
    >> As far as possible, I’m inclined to suggest that one best practice for
    >> data replication might be “DON’T” – now I’ll duck before I get things thrown
    >> at me J
    >>
    >>
    >>
    >> *Phil Grainger*
    >> Cogito Ltd.
    >>
    >> [login to unmask email]
    >> +44 (0) 1298 872 148
    >> +44 (0) 7505 266 768
    >>
    >> www.cogito.co.uk
    >>
    >>
    >>
    >> Attend IDUG 2011 - the premiere events for DB2 professionals.
    >>
    >> IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
    >> IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech
    >> Republic
    >>
    >>
    >>
    >> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Lynne
    >> Flatley
    >> *Sent:* 20 January 2011 14:03
    >>
    >> *To:* [login to unmask email]
    >> *Subject:* [DB2-L] Best practices for databases being replicated
    >>
    >>
    >>
    >> Good morning!
    >>
    >>
    >> I'm going to be responsible for maintaining both a small data mart and its
    >> operational data store. Both are on DB2 V9 z/OS. I've worked in several
    >> shops which used replication but I'm never been directly responsible for a
    >> database using replication. We'll be using QREP. What are some of the
    >> procedures/best practices used when maintaining a replicated database?
    >>
    >> I know of two; when altering a replicated table to add a column, its
    >> tablespace must be reorged immediately (or as soon as possible?), if the
    >> tablespace is compressed, keepdictionary must be specified when doing the
    >> reorg.
    >>
    >> Can anyone else think of any others?
    >>
    >> Thanks!
    >>
    >> ------------------------------
    >>
    >> [image: Introducing IBM® DB2® 10 for z/OS ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >
    >>
    >> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
    >> are not already an IDUG member, please register here. < http://www.idug.org/register >
    >>
    >> ------------------------------
    >>
    >> [image: Independent, not-for-profit, User Run - the IDUG difference! ] < http://www.idug.org >
    >>
    >> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
    >> are not already an IDUG member, please register here. < http://www.idug.org/register >
    >>
    >
    >
    > ------------------------------
    >
    > [image: Independent, not-for-profit, User Run - the IDUG difference! ] < http://www.idug.org >
    >
    > The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
    > not already an IDUG member, please register here. < http://www.idug.org/register >
    >
    > ------------------------------
    >
    > [image: Independent, not-for-profit, User Run - the IDUG difference! ] < http://www.idug.org >
    >
    > The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
    > not already an IDUG member, please register here. < http://www.idug.org/register >
    >

    _____________________________________________________________________
    * IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
    * If you are going to attend only one conference this year, this is it! *
    ** The most DB2 technical sessions of any conference
    ** Access IBM experts and developers
    _____________________________________________________________________

    If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
    Avram Friedman
    Have you considered the options (that may be the best practice) that provides 100% currency at no replication cost?
    Use the data as is, where is.

    Most potencial targets of replication can communicate to the source of replication.
    If this is not true then replication would not work well.
    If it is true why replicate at all.

    Sometimes I find questions like this really are not best practice questions at the database level but how to handle questionable new design at the application and end user level that enjoys pre-approval of a big shot that no one wants to talk to again. Of course there is no way for me to know for sure.

    Happy weekend
    Avram Friedman

    On Fri, 21 Jan 2011 12:05:23 -0500, Lynne Flatley <[login to unmask email]> wrote:

    >Well, the designers of this system want it to be near real-time reporting.
    >My organization is hosting the databases so design is not done here. I'm
    >just happy to have the opportunity to work with QREP.
    >
    >On Fri, Jan 21, 2011 at 11:39 AM, Myron Miller <[login to unmask email]>wrote:
    >
    >> I'm late in this game but why the need for QREP? If the tables are
    >> identical in the Operational data store and the data mart, then DSN1COPY
    >> works equally as well and significantly faster with a whole lot less fuss.
    >> Lots of shops create copies of production operational data this way on both
    >> the same Z/OS machine and on different machines.
    >>
    >> Myron
    >>
    >> ------------------------------
    >> *From:* Lynne Flatley <[login to unmask email]>
    >>
    >> *To:* [login to unmask email]
    >> *Sent:* Fri, January 21, 2011 11:11:42 AM
    >> *Subject:* Re: [DB2-L] Best practices for databases being replicated
    >>
    >> Well, I'm a contractor on the project *and* we're hosting the
    >> subsystems/databases so I have zero say in design issues.
    >>
    >> I agree, I've always been a proponent of "read it where it sits" but that
    >> train left the station a long time ago. I view this as another opportunity
    >> to add a skill to my resume thereby making me more marketable whether I
    >> agree with the idea or not. <also ducking>
    >>
    >>
    >> On Fri, Jan 21, 2011 at 10:51 AM, Phil Grainger <
    >> [login to unmask email]> wrote:
    >>
    >>> I suppose you have determined that this is the only viable approach to
    >>> take? And you can�t use the operational data also as your data store?
    >>>
    >>>
    >>>
    >>> I�m constantly amazed at how many copies of the same data we toss around
    >>> and then constantly worry about which is the �most correct� copy
    >>>
    >>>
    >>>
    >>> As far as possible, I�m inclined to suggest that one best practice for
    >>> data replication might be �DON�T� � now I�ll duck before I get things thrown
    >>> at me J
    >>>
    >>>
    >>>
    >>> *Phil Grainger*
    >>> Cogito Ltd.
    >>>
    >>> [login to unmask email]
    >>> +44 (0) 1298 872 148
    >>> +44 (0) 7505 266 768
    >>>
    >>> www.cogito.co.uk
    >>>
    >>>
    >>>
    >>> Attend IDUG 2011 - the premiere events for DB2 professionals.
    >>>
    >>> IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
    >>> IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech
    >>> Republic
    >>>
    >>>
    >>>
    >>> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Lynne
    >>> Flatley
    >>> *Sent:* 20 January 2011 14:03
    >>>
    >>> *To:* [login to unmask email]
    >>> *Subject:* [DB2-L] Best practices for databases being replicated
    >>>
    >>>
    >>>
    >>> Good morning!
    >>>
    >>>
    >>> I'm going to be responsible for maintaining both a small data mart and its
    >>> operational data store. Both are on DB2 V9 z/OS. I've worked in several
    >>> shops which used replication but I'm never been directly responsible for a
    >>> database using replication. We'll be using QREP. What are some of the
    >>> procedures/best practices used when maintaining a replicated database?
    >>>
    >>> I know of two; when altering a replicated table to add a column, its
    >>> tablespace must be reorged immediately (or as soon as possible?), if the
    >>> tablespace is compressed, keepdictionary must be specified when doing the
    >>> reorg.
    >>>
    >>> Can anyone else think of any others?
    >>>
    >>> Thanks!
    >>>
    >>> ------------------------------
    >>>
    >>> [image: Introducing IBM� DB2� 10 for z/OS ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >
    >>>
    >>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
    >>> are not already an IDUG member, please register here. < http://www.idug.org/register >
    >>>
    >>> ------------------------------
    >>>
    >>> [image: Independent, not-for-profit, User Run - the IDUG difference! ] < http://www.idug.org >
    >>>
    >>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
    >>> are not already an IDUG member, please register here. < http://www.idug.org/register >
    >>>
    >>
    >>
    >> ------------------------------
    >>
    >> [image: Independent, not-for-profit, User Run - the IDUG difference! ] < http://www.idug.org >
    >>
    >> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
    >> not already an IDUG member, please register here. < http://www.idug.org/register >
    >>
    >> ------------------------------
    >>
    >> [image: Independent, not-for-profit, User Run - the IDUG difference! ] < http://www.idug.org >
    >>
    >> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
    >> not already an IDUG member, please register here. < http://www.idug.org/register >
    >>
    >
    >_____________________________________________________________________
    >* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
    >* If you are going to attend only one conference this year, this is it! *
    >** The most DB2 technical sessions of any conference
    >** Access IBM experts and developers
    >_____________________________________________________________________
    >
    >If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
    >

    _____________________________________________________________________
    * IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
    * If you are going to attend only one conference this year, this is it! *
    ** The most DB2 technical sessions of any conference
    ** Access IBM experts and developers
    _____________________________________________________________________

    If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
    David Tolleson
    [IBM]
    The two most common reasons I hear about why people want to replicate to a reporting system:

    1) The source system doesn't have the capacity to add the workload expected by reporting and analysis.

    2) The replication target will be a backup of the source, allowing for fail over (or even load balancing).

    The latter has gotten hugely popular over the past few years. However, I agree that it's good to make sure you have solid reasons for making copies of data.


    --------------------------------------------------------

    Have you considered the options (that may be the best practice) that provides 100% currency at no replication cost?
    Use the data as is, where is.

    Most potencial targets of replication can communicate to the source of replication.
    If this is not true then replication would not work well.
    If it is true why replicate at all.

    Sometimes I find questions like this really are not best practice questions at the database level but how to handle questionable new design at the application and end user level that enjoys pre-approval of a big shot that no one wants to talk to again. Of course there is no way for me to know for sure.

    Happy weekend
    Avram Friedman

    _____________________________________________________________________
    * IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
    * If you are going to attend only one conference this year, this is it! *
    ** 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
    Tsui Yuk Kai
    [bochk]
    our shop also plan to have q-rep in order to achieve HA & 24x7. ibm
    recommend. actually i am not agreed with this design but our
    development team insist to go with this design to achieve 24x7.

    2011/1/24, David T <[login to unmask email]>:
    > The two most common reasons I hear about why people want to replicate to a
    > reporting system:
    >
    > 1) The source system doesn't have the capacity to add the workload expected
    > by reporting and analysis.
    >
    > 2) The replication target will be a backup of the source, allowing for fail
    > over (or even load balancing).
    >
    > The latter has gotten hugely popular over the past few years. However, I
    > agree that it's good to make sure you have solid reasons for making copies
    > of data.
    >
    >
    > --------------------------------------------------------
    >
    > Have you considered the options (that may be the best practice) that
    > provides 100% currency at no replication cost?
    > Use the data as is, where is.
    >
    > Most potencial targets of replication can communicate to the source of
    > replication.
    > If this is not true then replication would not work well.
    > If it is true why replicate at all.
    >
    > Sometimes I find questions like this really are not best practice questions
    > at the database level but how to handle questionable new design at the
    > application and end user level that enjoys pre-approval of a big shot that
    > no one wants to talk to again. Of course there is no way for me to know for
    > sure.
    >
    > Happy weekend
    > Avram Friedman
    >
    > _____________________________________________________________________
    > * IDUG North America * Anaheim, California * May 2-6 2011 *
    > http://IDUG.ORG/NA *
    > * If you are going to attend only one conference this year, this is it!
    > *
    > ** 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
    >

    --
    ±q§Úªº¦æ°Ê¸Ë¸m¶Ç°e

    _____________________________________________________________________
    * IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
    * If you are going to attend only one conference this year, this is it! *
    ** 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
    Avram Friedman
    The usual reason for replcation is to protect old investments in application systems.
    Say replication is between DB2 on Z/OS to someplace else even a diffrent DB2 on Z/OS system.
    It is useful to ask if it is the sender or receiver the old system?
    In the case of the original posting this thread its the sender, DB2 for z/os that is the old system.
    The fact that DB2 for z/os is being staged for replacement should not be considered good news to a DB2 for z/os person.

    This reminds me a lot of the famous discussion where some one is given the oppertunity to train there unexpected replacement.
    Great news now ones resume can list both revieved and delivered training.
    Bad news, your resume may need updating.


    Now concerning the capacity, performance, availability, backup and application exposure advantages, who is everyone trying to kid.
    EXCEL and ACCESS work just fine with data in DB2 for z/os no reason to replicate.

    Lets look at the capacity, performace and availability parts.
    First let me make it clear that SYSPLEX works just fine with DB2 for z/os.
    Second DASD performance is rarly the application performance bottleneck DB2 logging would max out long before an application data store.
    Third the potencial bottlenecks line CPU, channels, I/O paths, memory are all better addressed by system 390 and SYSPLEX in a solution of far less parts, complexity and cost than any hetrogenious approach.

    Lets look at the cost issue (you may want to get out pencil and paper at this point so you can follow along with the math)

    Say update insert and delete activity follows the classic 80 / 20 rule
    80% Read
    20% everything else.

    Now at this point we are allready at a spot where everything else CPU cost is greater than read cost.
    Why because a single row SQL insert, delete or update costs 4 to 5 times the cost of a singleton select.

    On the surface it would seem that replication alone would double the cost because all the writes (everything else) is being done twice.

    Now if only the world was that effecient, but the reality is not even close.
    Here is what one has to deal with in the QREP world

    a. Double the cost of logging before replication even starts.
    Db2 typically logs changed data only.
    With Change Data Capture the entire changing row is logged in diffrent log records ... the old stuff continues to be logged in the old way.

    b. Host supense file inserts. All write activity is captured to a suspence file which in QREP are DB2 tables.

    c. Unsent parts of the supence file are read and transmitted usually over a network connection to the target.

    d. Target gets the transmission of the updates and writes them to a suspence file. In the case of the taget being DB2 and QREP this suspence file is a DB2 table.

    e. Target reads its suspense file and updates the target destination tables

    Wow lots of work but at least we are done with all of that right?
    WRONG!!!!!!

    f. Target suspence file row gets deleted

    g. Message is sent to the host to delete suspence file row

    If each of the a. through g. items cost as much as the original host write then we have introduced a 7x cost.

    Some people may claim that via careful software development some of these steps may be improved from very bad to just bad.
    This is true with operative remaining word being bad. Sort of like saying it is wonderful that my stock has been upgraded from liquidate to unfavorable. Let me give you a quick example of one of these improvements in QREP. My step g. deleting from the host suspence file. It seems some users have pointed out that this delete happens at exactly the wrong business time. We just spent a big amount of resources getting the original insert delete and update. Then we spent 5x that cost getting the data replicated. Now you want us to spend big cleaning up the suspence files???!!!!!! Well your hardware and software sales people are looking out for you. The suspence file clean up can be defered to a time when it will interfear with a diffrent workload.

    There is one claim that I have not discussed yet QREP style replication as a type of backup.
    What can I say, these people are smoking a diffrent brand of the same herbal suplement.
    DB2 SQL DML is not a backup tool.
    DB2 image copy that opperates on a block/ci/page level is much faster than DB2 SQL DML.
    Yet it is so bad that when DB2 became GA 30 years ago it created an industry of independent software vendors to replace it with tools that were not dependent on DB2 buffer management all of which advertised improved preformance and provided supporting test results.
    Today DB2 image copy is so bad that in DB2 V8 the vendor added a new backup facility / command to snap copy at the control unit level all the DB2 objects
    In DB2 V9 the backup facility was improved to remove the ALL restriction that required everything to be backed up.

    Let me summerize
    1. The most defencable reason for replication like QREP is to support incompatable legacy systems.
    This happens to be the same justification for MQ and may be the reason why replication was moved to the MQ family.
    2. If you happen to be a DB2 for z/os person hope/pray your system is not the legacy system.
    3. While replication through software like QREP may be easy and fast to implement it is associated with an astromical operation costs and is thus best suited as a temporary solution.
    4. Growth via SYSPLEX deployment is a highly recommended, effective, cost reducing method to preserve the life of your system 390 investment dollars.

    Avram Friedman






    On Sun, 23 Jan 2011 16:43:27 -0500, David T <[login to unmask email]> wrote:

    >The two most common reasons I hear about why people want to replicate to a reporting system:
    >
    >1) The source system doesn't have the capacity to add the workload expected by reporting and analysis.
    >
    >2) The replication target will be a backup of the source, allowing for fail over (or even load balancing).
    >
    >The latter has gotten hugely popular over the past few years. However, I agree that it's good to make sure you have solid reasons for making copies of data.
    >
    >
    >--------------------------------------------------------
    >
    >Have you considered the options (that may be the best practice) that provides 100% currency at no replication cost?
    >Use the data as is, where is.

    _____________________________________________________________________
    * IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
    * If you are going to attend only one conference this year, this is it! *
    ** 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

    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact