Db2 On Premise and Cloud

 View Only
  • 1.  DB2 BLU migration and best practices documentation

    IBM Champion
    Posted Fri February 18, 2022 05:24 AM
    Hi Everyone,

    I've been going through the available documentation on the BLU technology, but unfortunately it always goes back to many years ago (2015, 2011), also the sizer is no more available.

    - Can IBM db2 user (currently on 10.5), benefit from a free assessment on storage benefits they get from BLU on 11.5 ?
    - Is there a sizer available ?
    - Is the rule of thumb still applied for 11.5 (8 core / 16G per core for 3 TB of raw uncompressed data )
    - Any best practices document for 11.1 or 11.5 ?

    Thank you in advance

    Largou walid

  • 2.  RE: DB2 BLU migration and best practices documentation

    Posted Fri February 18, 2022 01:29 PM

    Hi Largou,
    What is your goal why you want to use BLU?

    Where do you come from?
    You mentioned Db2 V10.5. Is this a Warehouse?
    Is it row-based?
    Does it use compression?


    BLU technology is mostly used in warehouse environments.
    If you want to improve compression you find a document here.
    Compression rates vary depending on data types and data. If you compare on table level it can be above 80% for for several tables , but overall for the database you have to calculate with a lower number between 30 and 70%

    Hardware sizing
    For sizing you find information in the documentation i.e. for linux x86
    Initial recommendation 8 cores 64 GB Memory.
    About how much data do talk. Is this 3 TB overall?

    Do you have any connection into SAP?
    There are documents SAP business warehouse on DB6. That gives also some more background using BLU.

    Kind regards

    Joachim Stumpf

    Joachim Stumpf
    IT Specialist

  • 3.  RE: DB2 BLU migration and best practices documentation

    IBM Champion
    Posted Fri February 18, 2022 01:34 PM

    Thank you for your feedback, it's a data warehouse with row based tables running 10.5 without enabling BLU. The overall size is 17T and it's not connected to SAP.

    Largou walid

  • 4.  RE: DB2 BLU migration and best practices documentation

    Posted Mon February 21, 2022 02:25 PM

    i only mentioned SAP due to the description i found helpfull.

    We also had a evolution over time there.

    I don't know how your warehouse is build and loaded.
    I use SAP BW evolution here as a sample.
    The warehouse had several levels.

    Operational data store entry level to the warehouse
    The lowest level was tables of the so called operational data store , which is more or less the base and contained raw data replicated from operational systems.

    Reporting layer
    The next level with Fact and dimension tables is the first level for reporting.
    In special cases these level was condensed in additional tables to improve query performance.

    At the beginning we only converted the reporting level to columnar (BLU). This was due to performance issues in the step from ODS to especially dimensions update.

    Also LOAD is something which has regressions using BLU. So we had to change  load process from db2 LOAD to db2 insert.
    Insert has made a big leap in BLU. It has some parallel improvements.

    NON-SAP environments

    Also in non-SAP warehouses we had problems with the load since customers are using third party tools , which are not necessarily optimized for BLU.
    But using BLU sizing in row based environment can improve performance.

    As you see in my description you really should analyze your complete warehouse.

    My recommendation:

    Analyze your processes in your warehouse. Check the tools used.
    Take some time doing a POC (Proof of concept) to evaluate processes of your warehouse using blu sized resources. This normally means more CPU'S and more memory than your existing warehouse. Maybe new storage would also help a bit.

    Start converting tables to  columnar (BLU) using ADMIN_MOVE_TABLE procedure. I would start with the tables used for reporting. You have to convert all tables involved in a query to columnar.
    In the next step i would check the load processes on the tables.

    In the next step you can try to tweek using
    - Columnar db2set parameters besides DB2_WORKLOAD  (this is why i also like refer to SAP since there is a parameter proposal for columnar)
    - Some db2 dbm and db parameter should also be taken into account (dft_degree, max_query_degree)
    - secondary indices in BLU.

    Additional topics:
    You mention a size of 17TB(compresssed or uncompressed?)
    I don't know if you run db2 single instance or MPP. This is depending on resources and management.  MPP needs a little bit more on resources and  a little bit more effort in management. And also in the layout you maybe did some optimizations on placing data. I don't want to elaborate to much here, since i don't know if it's necessary.

    These are my five cents here.

    Joachim Stumpf
    IT Specialist

  • 5.  RE: DB2 BLU migration and best practices documentation

    IBM Champion
    Posted Tue February 22, 2022 02:27 AM

    Thank you for your feedback, the size is uncompressed with all tables row based. We are running on Power8 with single instance, and we plan to deploy a new machine where we would upgrade and enable BLU.

    Does the 8 cores recommendation also applies to Power ? Also we are thinking of migrating using :
    - Backup source
    - Restore to destination (in the same version)
    - Upgrade version to 11.5
    - Convert tables

    Any advice or better way to perform that ?

    Thank you

    Largou walid

  • 6.  RE: DB2 BLU migration and best practices documentation

    Posted Tue February 22, 2022 10:35 AM


    Topic: sizing
    a recommendation isn't really a sizing .
    I would see the 8 cores as an entry level to start. In some older docs i see also the disclaimer  8 cores per 3 TB raw data for medium requirements.
    SMT gives some improvement. There are some test on SMT 8 vs SMT 4 for Power9

    How many cores do you use today with which SMT level?

    With your information here, which is not enough to give sizing recommendation,
    I would expect that you need more (maybe from 24 to 48 cores)  since  BLU allows a higher level of parallelism.

    Topic: Migration
    Your plan is the safe way up to conversion.
    You can restore a backup also direct into a newer version see documentation.
    I recommend doing a testmigration.
    I would also try to setup a conversion script.

    Topic POC or pre testing
    I don't think that your warehouse is static and self sufficient.
    There are application around for
    - loading complete or delta
    - aggregating

    Normally there a windows for such procedures . Please check since BLU works different than row.
    This mostly improves query performance , but can have negative impact on load or aggregation. On the other hand maybe some aggregates are no longer needed.

    So moving to BLU technology needs some adaption.

    I recommend sizing at minimum and a small POC to test some processes after  conversion.

    kind regards

    Joachim Stumpf
    IT Specialist

  • 7.  RE: DB2 BLU migration and best practices documentation

    IBM Champion
    Posted Wed February 23, 2022 08:25 AM
    Thank you for your time, much appreciated !

    Largou walid

  • 8.  RE: DB2 BLU migration and best practices documentation

    Posted Wed February 23, 2022 05:22 AM
    Hi Largou,

    One thing you should really take in account considering you are on big dwh database:

    A column-organized table cannot be a:
    • range-partitioned table
    • multi-dimensional clustered table
    • typed table

    Columnar tables cannot be partitioned as range-partitioned table which is usually core feature in dwh.
    Columnar based tables use internal mechanism called synopsis tables which are basically "internal indexes" and they help to skip data for certain type of predicates.


    So, my advice for you is to test properly main features you are using in or dwh, compare them with features that BLU has and find balance/compromise. 


    Ivan Milojevic
    Comtrade System Integration

  • 9.  RE: DB2 BLU migration and best practices documentation

    IBM Champion
    Posted Wed February 23, 2022 08:27 AM

    Thank you for advice, indeed we are planning to do some testing, I'm more worried about data format and encoding since we have French characters in the database.


    Largou walid