Db2 On Premise and Cloud

 View Only
  • 1.  DB2 Storage layout for large databases

    IBM Champion
    Posted Tue March 08, 2022 10:05 AM

    Hi Everyone,

    Have been looking for some documentation on sizing recommendations and best practices for setting up the storage (LUNs size and number, filesystems layout …) but only found old documentation.

    We have three databases with 17T in total, can you suggest a rule of thumbs for DB211.5with BLU on AIX ?

    Thank you

    Largou walid

  • 2.  RE: DB2 Storage layout for large databases

    Posted Wed March 09, 2022 02:35 AM
    Hi Largou,

    I apologize for the most complete, yet simple answer: It depends!   (In reference to Bernie Schiefer!)

    Now, back to a different answer. Well, there are a lot of things to consider splitting up the database into filesystems, LUNs etc.
    You might want to use as much spindles in parallel as possible. Split data tablespaces and index tablespaces. Make sure, your large data (BLOB, CLOB, LONG) is in a separate tablespace. Define multiple bufferpools. Have lots of memory. And for BLU have lots of cores. It aint cheap.
    Partitioning is a good thing, if you have understood what to partition on and depending on your queries. So does the layout depend on your data and what is stored in your databases. If not partitioning, put your larges data in a separate tablespace. Not to forget fact tables, which are in referential integrity with it.

    A 17TB SAP system would require a different layout than a dump from some physics experiment.

    Don't disregard the old docs. Look for the "best practices" series within Db2 Community. If you don't find it yourself, I can lookup pointers for you.

    If you need more help, just give us some more details, about your environment.


    Roland Schock
    Distinguished Engineer
    ARS Computer und Consulting GmbH

  • 3.  RE: DB2 Storage layout for large databases

    Posted Wed March 09, 2022 04:35 AM

    Hi Largou,
    as Roland mentioned you can consider the old documentation.

    I also recommend in that case also the SAP BW documention since it describes  different topics to consider for a warehouse.
    Some details are SAP specific extensions which rely on Db2 procedures or functions or belong to the interfaces specific to SAP.
    But i also recommend to consider the configuration parameters.
    The issue maybe for some details to have an SAP account.

    Some basics from database perspective.

    Distribution starts on different levels.

    Tablespace layout (tablespace and nodegroups)
    Data space of table objects and indexes(which maybe rare in BLU when you don't use secondary indexes) should be separate from Large objects (CLOB BLOB ..) since these are not cached in bufferpool.
    You can also differentiate her between dimension and fact tables. Here we consider the concepts of nodegroups too. First only to separate tablespaces , but if you ever think about MPP that comes into play since then you have to define partitions where nodegroups reside.

    For LOB tablespaces you should use filesystem caching for the others not.

    A tablespace at the end belongs to a storage group
    A storage group can refer to several filesystems.
    On filesystems we do or avoid filesystem caching depending on tablespace definition

    Storage infrastructure
    Behind the filesystem you work with your storage infrastructure.
    There we can only give basic recommendations since we don't know anything of it.
    IO speed is one thing. So parallellism with several filesystems can help.  Maybe not so much if you use SSD.
    How much virtualization occur between these filesystems and the real storage (Logical volume -> RAID array -> physical volume) ?  Don't overcomplicate that.

    Joachim Stumpf
    IT Specialist

  • 4.  RE: DB2 Storage layout for large databases

    IBM Champion
    Posted Wed March 09, 2022 12:12 PM

    Thank you for your feedback, indeed the SAP documentation seems to be the most accurate and up to date, would expect IBM to release some guidelines as it did release recently documentation on BLU best practices. I do understand that the focus might be now on containerised DB2, IBM needs to have recommendations per case for customer and within the roadmap of the product.

    Largou Walid
    Solution Architect / IBM Champion
    Power Maroc

    Largou walid

  • 5.  RE: DB2 Storage layout for large databases

    Posted Thu March 10, 2022 07:21 AM
    Good morning Largou,

    As Roland says, the information is old, but it works very well.
    We have a client that will be migrated to db2 DPF with 50Tbytes next month, currently they are already on

    The competition (i.e. google) has been trying to knock us off db2 for years and they have not been able to, the response times are EXCELLENT.

    We use IBM SAN with NVme disks, excellent response with extraordinary time reductions.

    At another customer, we ran over 20,000 jobs on dataSTage against IBM i and db2 Warehouse DPF running tablespaces created with STORAGE GROUPS over multiple filesystems.

    At another customer, we use competitor storage, very good performance with db2 against online application running on IBM z15. There we have non-partitioned tables with over 100 million rows, everybody happy.

    I hope this encourages you.

    Translated with www.DeepL.com/Translator (free version)

    IBM Expert Labs, Data Consultant: Generalist
    Db2 Advanced Database Administrator LUW
    IBM Certified Database Associate - Db2 8.1,10.1 Fundamentals
    IBM Associate Certified DBA - Db2 12 for z/OS Fundamentals
    IBM i Applications Architect
    Nicolás Rafael Ascanio Peña

    IBM Professions and COGNITIVE CLASS (40) badges

    Cell phone: +58 4241406472         
    Tie-Line: 777-8685         
    e-mail: nascanio@ve.ibm.com

  • 6.  RE: DB2 Storage layout for large databases

    IBM Champion
    Posted Wed March 09, 2022 12:08 PM
    Hi Roland,

    Thanks a lot for your feedback, indeed the answer is it depends and since there is also the BLU Technology with recent improvements and changes, I thought that there should be also updates to the best practices and sizing guidelines.  I went also through some IDUG materials and still looking.

    Largou Walid
    Solution Architect / IBM Champion
    Power Maroc

    Largou walid