Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Simple Large Object Replication

  • 1.  Simple Large Object Replication

    Posted 11 days ago
    HI,
    I have a customer who wants to implement Informix Repication (not yet decided which kiind of). He is using lots of Simple Large Objects (Text).
    Taking into account the fact that TEXT data type are not replicated, he wants to know what is the best approach, storing them into dbspaces along with table data, or converting to Smart Larg Object (CLOB).
    Cons to 1st approch is Bufferpool and Logical Log consume, while Pros is that not to change to application code is needed to insert the TEXT data.
    Cons to 2nd approach is that application code has to be changed to insert CLOB data and and must alter table from text to clob data type. Pros is that CLOB indeed are replicated.
    I Don't have much experience in the field. What would you recocommend to my customer?
    Thank's a lot.
    Juan 


    ------------------------------
    juan luis roca
    ------------------------------


  • 2.  RE: Simple Large Object Replication

    Posted 11 days ago
    Juan:

    TEXT and BYTE data types do replicate but only if the column is defined IN TABLE not in a blobspace. So, all you have to do is to move the TEXT column(s) from the blobspace to tablespace and poof, they will replicate!

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: Simple Large Object Replication

    Posted 11 days ago

    Thank you Art., but, what are possible implications in performance? Bufferpool size, logical log size and rotation rate,etc...

    El 22/02/2021 a las 14:41, Art Kagel via IBM Community escribió:
    01000177c9f92356-2885faa0-f252-43ad-ad15-5a6db4f45733-000000@email.amazonses.com">
    Juan: TEXT and BYTE data types do replicate but only if the column is defined IN TABLE not in a blobspace. So, all you have to do is to move the... -posted to the "Informix" group
    IBM Community

    Informix

    Post New Message
    Re: Simple Large Object Replication
    Reply to Group Reply to Sender
    Art Kagel
    Feb 22, 2021 8:39 AM
    Art Kagel
    Juan:

    TEXT and BYTE data types do replicate but only if the column is defined IN TABLE not in a blobspace. So, all you have to do is to move the TEXT column(s) from the blobspace to tablespace and poof, they will replicate!

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------
      Reply to Group Online   View Thread   Recommend   Forward  




     
    You are receiving this notification because you followed the 'Simple Large Object Replication' message thread. If you do not wish to follow this, please click here.

    Update your email preferences to choose the types of email you receive

    Unsubscribe from all participation emails




    Original Message:
    Sent: 2/22/2021 8:39:00 AM
    From: Art Kagel
    Subject: RE: Simple Large Object Replication

    Juan:

    TEXT and BYTE data types do replicate but only if the column is defined IN TABLE not in a blobspace. So, all you have to do is to move the TEXT column(s) from the blobspace to tablespace and poof, they will replicate!

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------


  • 4.  RE: Simple Large Object Replication

    Posted 11 days ago
    Edited by Art Kagel 11 days ago
    Juan:

    You know that there is no such thing as a free lunch! Whatever you do with the data in the current TEXT column(s) if you want the data to be replicated then it must be logged (blobspaces are not logged - updates are handled by keeping both the old and new copy until the next archive cleans out the outdated versions). So, yes you will have to add logical log space whether you move the data to an IN TABLE TEXT type column or to a CLOB in a logged SmartBlob space as you will consume logs faster. I think that the logging overhead of the CLOB will be slightly less, but not significantly so. Yes, there will be more IO load to write out those logs. Archives will be slightly faster. Retrieving the BLOB data will be slightly faster, writing it may be slower as will updating the TEXT column itself (but not the rest of the row data - the BLOB content is kept out-of-row on separate pages). You may want to put the table into a dbspace with wider pages depending on the average size of the TEXT and BYTE type values you have. That may cause some space savings if you are wasting space on pages now, but that's just a side bonus.

    Oh, you may also want to make each of the logical logs larger since they will fill faster, but be careful that you don't put too much other data at risk that way if inserting and updating those blobs is infrequent. I would only make the logs bigger if blob activity is high.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 5.  RE: Simple Large Object Replication

    Posted 11 days ago

    Thank you so much Art. You have completely answered my question.

    El 22/02/2021 a las 15:39, Art Kagel via IBM Community escribió:
    01000177ca2e9e90-fd4fd32e-f033-468f-866e-f273a1a475da-000000@email.amazonses.com">
    Juan: You know that there is no such thing as a free lunch! Whatever you do with the data in the current TEXT column(s) if you want the data to... -posted to the "Informix" group
    IBM Community

    Informix

    Post New Message
    Re: Simple Large Object Replication
    Reply to Group Reply to Sender
    Art Kagel
    Feb 22, 2021 9:36 AM
    Art Kagel
    Juan:

    You know that there is no such thing as a free lunch! Whatever you do with the data in the current TEXT column(s) if you want the data to be replicated then it must be logged (blobspaces are not logged - updates are handled by keeping both the old and new copy until the next archive cleans out the outdated versions). So, yes you will have to add logical log space whether you move the data to an IN TABLE TEXT type column or to a CLOB in a logged SmartBlob space as you will consume logs faster. I think that the logging overhead of the CLOB will be slightly less, but not significantly so. Yes, there will be more IO load to write out those logs. Archives will be slightly faster. Retrieving the BLOB data will be slightly faster, writing it may be slower as will updating the TEXT column itself (but not the rest of the row data - the BLOB content is kept out-of-row on separate pages). You may want to put the table into a dbspace with wider pages depending on the average size of the TEXT and BYTE type values you have. That may cause some space savings if you are wasting space on pages now, but that's just a side bonus.

    Oh, you may also want to make each of the logical logs larger since they will fill faster, but be careful that you don't put too much other data at risk that way if inserting and updating those blobs is infrequent. I would only make the logs bigger if blob activity is high.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------
      Reply to Group Online   View Thread   Recommend   Forward  




     
    You are receiving this notification because you followed the 'Simple Large Object Replication' message thread. If you do not wish to follow this, please click here.

    Update your email preferences to choose the types of email you receive

    Unsubscribe from all participation emails




    Original Message:
    Sent: 2/22/2021 9:36:00 AM
    From: Art Kagel
    Subject: RE: Simple Large Object Replication

    Juan:

    You know that there is no such thing as a free lunch! Whatever you do with the data in the current TEXT column(s) if you want the data to be replicated then it must be logged (blobspaces are not logged - updates are handled by keeping both the old and new copy until the next archive cleans out the outdated versions). So, yes you will have to add logical log space whether you move the data to an IN TABLE TEXT type column or to a CLOB in a logged SmartBlob space as you will consume logs faster. I think that the logging overhead of the CLOB will be slightly less, but not significantly so. Yes, there will be more IO load to write out those logs. Archives will be slightly faster. Retrieving the BLOB data will be slightly faster, writing it may be slower as will updating the TEXT column itself (but not the rest of the row data - the BLOB content is kept out-of-row on separate pages). You may want to put the table into a dbspace with wider pages depending on the average size of the TEXT and BYTE type values you have. That may cause some space savings if you are wasting space on pages now, but that's just a side bonus.

    Oh, you may also want to make each of the logical logs larger since they will fill faster, but be careful that you don't put too much other data at risk that way if inserting and updating those blobs is infrequent. I would only make the logs bigger if blob activity is high.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message:
    Sent: Mon February 22, 2021 09:13 AM
    From: juan luis roca
    Subject: Simple Large Object Replication

    Thank you Art., but, what are possible implications in performance? Bufferpool size, logical log size and rotation rate,etc...

    El 22/02/2021 a las 14:41, Art Kagel via IBM Community escribió:
    01000177c9f92356-2885faa0-f252-43ad-ad15-5a6db4f45733-000000@email.amazonses.com">
    Juan: TEXT and BYTE data types do replicate but only if the column is defined IN TABLE not in a blobspace. So, all you have to do is to move the... -posted to the "Informix" group
    IBM Community

    Informix

    Post New Message
    Re: Simple Large Object Replication
    Reply to Group Reply to Sender
    Art Kagel
    Feb 22, 2021 8:39 AM
    Art Kagel
    Juan: TEXT and BYTE data types do replicate but only if the column is defined IN TABLE not in a blobspace. So, all you have to do is to move the TEXT column(s) from the blobspace to tablespace and poof, they will replicate! ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
      Reply to Group Online   View Thread   Recommend   Forward  
    Original Message: Sent: Mon February 22, 2021 07:34 AM
     
    You are receiving this notification because you followed the 'Simple Large Object Replication' message thread. If you do not wish to follow this, please click here.

    Update your email preferences to choose the types of email you receive

    Unsubscribe from all participation emails




    Original Message:
    Sent: 2/22/2021 8:39:00 AM
    From: Art Kagel
    Subject: RE: Simple Large Object Replication

    Juan:

    TEXT and BYTE data types do replicate but only if the column is defined IN TABLE not in a blobspace. So, all you have to do is to move the TEXT column(s) from the blobspace to tablespace and poof, they will replicate!

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------


  • 6.  RE: Simple Large Object Replication

    Posted 11 days ago
    Hi Juan Luis,

    in case "not yet decided which kiind of" should also include ER (Enterprise Replication) and they'd decide for that, they could even keep their blobspace blobs.  (And ER also supports non-logged smart blobs.)

    It really depends on what they want to achieve...

    Andreas

    ------------------------------
    Andreas Legner
    ------------------------------