Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2014
    Posts
    4

    Unanswered: DB2 Table partitioning and tuning

    Hi everyone,

    I need DB2 DBA help to tune database and table partitioning .I dindt perform table partitioning before .

    I am using Linux 64 bit server ,DB2 10.1 .requirement has come for me to create a dataware house database for which input is BIg data streams .

    daily approx 100GB data will get load to fact tables (which are used regularly there are 25 table) .

    I am following below settings to create database :

    1)Create db with 32K page size,utf-8 and autostorage with three seperate directories
    2)created 3 automatic bufferpools and tablespaces (each tablespace uses three directories created for database , )the purpose of creating three
    tablespaces is one will hold the highly used fact tables tehre are 3 . and one of reminaing fact tables and anotehr is for dimensional tables.
    3)allocated total 5GB transactional space(primary +sec logs =25)

    Other database parameters are set to automatic (heap size, STM etc) .

    Please let me know do I need to do any other settings ? whether the 32K tablespace created will also hold LOB data ?

    ........................

    I need to create 15 partions for each table , detach/drop the partition which is 2 days old , and attach partition for further use
    each day data will go to one parition , next day it has to take next parition like wise ..I am looking for a scrit which will delete 2 days old partition and attach new parition .

    And also Planning to schedule daily online backup for this table partition datbaase.how the script will be?
    Daily data will be moved to Netteza hence not planning for full db backup.

    Hope I am correct . If some one is come across this scenario pls share with me.

    Thanks in advance

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    we put each partition in separated tablespace with standard naming - partition that have standard names...
    we have a script that does all this
    we calculate the date for the detach
    calculate the name of the partition
    PartitionNameCurrentDateMinusSevenMonths=`db2 -x "select trim(substr(datapartitionname,5,2)) from syscat.datapartitions where tabname = '${TableName1}' and tabschema = '${TableSchema}' and substr(lowvalue,2,26) = '${YearCurrentDateMinusSevenMonths}-${MonthCurrentDateMinusSevenMonths}-01-00.00.00.000000'"`
    CurrentDatePlusOneMonths=`db2 -x "values(date('${CurrentDate}') + 1 Months)"`
    detach - wait for detach to be finished - attach....
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Oct 2014
    Posts
    4
    Thank you very much for your reply
    Think you are detaching partitions for every seven months ?
    how did you add partitions at the time of table creation ,how about partition keys ? , we need to select partiiton key column value from existing table ddl or we can add our own partition key column to table ?

  4. #4
    Join Date
    Oct 2014
    Posts
    4

    table partitioning

    Thank you very much for your reply
    Think you are detaching partitions for every seven months ?
    how did you add partitions at the time of table creation ,how about partition keys ? , we need to select partiiton key column value from existing table ddl or we can add our own partition key column to table ?

  5. #5
    Join Date
    Oct 2014
    Posts
    4

    table partitioning

    Thank you very much for your reply
    Think you are detaching partitions for every seven months ?
    how did you add partitions at the time of table creation ,how about partition keys ? , we need to select partiiton key column value from existing table ddl or we can add our own partition key column to table ?

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    we have 8 partitions : 6 months in past - current and current+1
    we have a timestamp column in table on which we partition
    each first day of month we detach oldest partition and add month for future
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •