Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2003
    Posts
    60

    Unanswered: partitioned database set up

    Hi,

    we are trying to create a partitioned database server with 4 partitions using ver 8 on AIX.

    Installed the db2 software. Whats the next step?
    Update the db2nodes.cfg or create an instance?

    Thanks,
    j

  2. #2
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186

    Re: partitioned database set up

    Look at the DB2start command and use it to create the new partitions, you might also need to edit the db2nodes.cfg after your done running that, Then you'll need to create partition groups, then tablespaces in those partions groups. don't forget your bufferpools, temporary tablespaces, If its an new db, just edit the db2nodes.cfg and then do your create db, other wise it very manual
    David Quigley

  3. #3
    Join Date
    Mar 2003
    Posts
    60

    Re: partitioned database set up

    Thanks for the insight. So I have to create an instance, then update the db2nodes.cfg. Right?

    Do I have to update db2nodes.cfg on all partitions or only on partition 0?

    Thanks,
    j

    Originally posted by quigleyd
    Look at the DB2start command and use it to create the new partitions, you might also need to edit the db2nodes.cfg after your done running that, Then you'll need to create partition groups, then tablespaces in those partions groups. don't forget your bufferpools, temporary tablespaces, If its an new db, just edit the db2nodes.cfg and then do your create db, other wise it very manual

  4. #4
    Join Date
    Mar 2004
    Posts
    448
    you should understand some things.

    1. partitioned can be added to an eee instance after its creation.

    2. each partition creates its own directory structure.

    3. all the partitioned must be in db2nodes.cfg file.

    4. if you add partition after the db2instance instance , this partiton is automatically added to system tempororay group.you need to add it to other groups in your database.

    I follow these steps if I am adding a new partiton to my already existed database.

    /************************************************** *****************************************/
    * ADDING PARTITION TO A NODE
    *
    ************************************************** *****************************************/


    1. change the /etc/services file

    DB2_db2inst1 60000/tcp
    DB2_db2inst1_1 60001/tcp
    DB2_db2inst1_2 60002/tcp
    DB2_db2inst1_END 60003/tcp

    2. Change the db2nodes.cfg file

    3. Log on as instance owner and into the node and start the new partition

    db2start dbpartitionnum(1)

    You can automatically add the entry into the db2nodes.cfg with this command

    db2start dbpartitionnum 2 add dbpartitionnum hostname udblnx02 port 2 without tablespaces

    4. Create the node directory structure

    db2inst1@udblnx02:~/sqllib> export DB2NODE=1
    db2inst1@udblnx02:~/sqllib> db2 terminate
    db2inst1@udblnx02:~/sqllib> db2 add dbpartitionnum without tablespaces

    5. Add the file to the tempspace01 tablespace

    db2inst1@udblnx02:~> db2 "alter tablespace tempspace01 add
    ('/db2temp/db2inst1/itsodb/NODE0002/tempspace01') on dbpartitionnum (1)"

    6. Add the partition to any partition group that wants to use that partition

    db2inst1@udblnx02:~> db2 'alter database partition group mpl_01 add
    dbpartitionnum(1) without tablespaces'

    7. Add the tablespace contaniers to the tablesspaces in that partition group

    db2inst1@udblnx02:~> db2 “alter TABLESPACE CUSTOMER_TSP add
    ('/tablespaces/db2inst1/itsodb/NODE0002/customer_tsp') on dbpartitionnum (2)”

    8. Check the node
    list db directory on /database

    9. Log in to the catalog node and issue the command

    db2inst1@udblnx02:~> db2 redistribute database partition group mpl_01 uniform


    For each table that has already been processed, the redistribution utility writes a
    message to file in $INSTHOME/sqllib/redist/<dbmame>.<part-group>.<time>.



    Redistribute messages file
    Data Redistribution Utility
    ___________________________
    The following options have been specified:
    Nodegroup name : MPL_01
    Data Redistribution option : U
    Redistribute Nodegroup : uniformly
    No. of nodes to be added : 1
    List of nodes to be added :2
    No. of nodes to be dropped : 0
    List of nodes to be dropped :
    Delete will be done in parallel with the insert.
    The execution of the Data Redistribution operation on:

    10. rebind the packages
    11. runstats


    Important point to note is that all the directory structure is there.
    db2start showing all the partitions

    Regards,

    Mujeeb

  5. #5
    Join Date
    Mar 2003
    Posts
    60
    Actuallt this is a new set up. The only thing installed is db2 software on a server with 4 logical partitions.

    Thanks,
    j

    Originally posted by bmujeeb
    you should understand some things.

    1. partitioned can be added to an eee instance after its creation.

    2. each partition creates its own directory structure.

    3. all the partitioned must be in db2nodes.cfg file.

    4. if you add partition after the db2instance instance , this partiton is automatically added to system tempororay group.you need to add it to other groups in your database.

    I follow these steps if I am adding a new partiton to my already existed database.

    /************************************************** *****************************************/
    * ADDING PARTITION TO A NODE
    *
    ************************************************** *****************************************/


    1. change the /etc/services file

    DB2_db2inst1 60000/tcp
    DB2_db2inst1_1 60001/tcp
    DB2_db2inst1_2 60002/tcp
    DB2_db2inst1_END 60003/tcp

    2. Change the db2nodes.cfg file

    3. Log on as instance owner and into the node and start the new partition

    db2start dbpartitionnum(1)

    You can automatically add the entry into the db2nodes.cfg with this command

    db2start dbpartitionnum 2 add dbpartitionnum hostname udblnx02 port 2 without tablespaces

    4. Create the node directory structure

    db2inst1@udblnx02:~/sqllib> export DB2NODE=1
    db2inst1@udblnx02:~/sqllib> db2 terminate
    db2inst1@udblnx02:~/sqllib> db2 add dbpartitionnum without tablespaces

    5. Add the file to the tempspace01 tablespace

    db2inst1@udblnx02:~> db2 "alter tablespace tempspace01 add
    ('/db2temp/db2inst1/itsodb/NODE0002/tempspace01') on dbpartitionnum (1)"

    6. Add the partition to any partition group that wants to use that partition

    db2inst1@udblnx02:~> db2 'alter database partition group mpl_01 add
    dbpartitionnum(1) without tablespaces'

    7. Add the tablespace contaniers to the tablesspaces in that partition group

    db2inst1@udblnx02:~> db2 “alter TABLESPACE CUSTOMER_TSP add
    ('/tablespaces/db2inst1/itsodb/NODE0002/customer_tsp') on dbpartitionnum (2)”

    8. Check the node
    list db directory on /database

    9. Log in to the catalog node and issue the command

    db2inst1@udblnx02:~> db2 redistribute database partition group mpl_01 uniform


    For each table that has already been processed, the redistribution utility writes a
    message to file in $INSTHOME/sqllib/redist/<dbmame>.<part-group>.<time>.



    Redistribute messages file
    Data Redistribution Utility
    ___________________________
    The following options have been specified:
    Nodegroup name : MPL_01
    Data Redistribution option : U
    Redistribute Nodegroup : uniformly
    No. of nodes to be added : 1
    List of nodes to be added :2
    No. of nodes to be dropped : 0
    List of nodes to be dropped :
    Delete will be done in parallel with the insert.
    The execution of the Data Redistribution operation on:

    10. rebind the packages
    11. runstats


    Important point to note is that all the directory structure is there.
    db2start showing all the partitions

    Regards,

    Mujeeb

  6. #6
    Join Date
    Mar 2004
    Posts
    448
    1. The instance home must be on NFS.



    2 Check that NFS is running by using ps.

    You can create instance now.



    3. the instance home directory contain .rhosts file
    // check its structure and its chmod

    4. Check that other machines communicate with each other by using
    rsh <machine name> ls
    // permission denied then check rhosts and NFS

    5. edit the db2nodes.cfg file to add the nodes.

    6. Edit the /etc/services file , check my last post.remember that db2 uses MPI to communicate.

    7. start the instance
    db2start

    it will show all the nodes started successfully.

    8. Create the database on any node.

    9. Now tailored it to your need to add partition group.


    Regards,

    Mujeeb

  7. #7
    Join Date
    Mar 2004
    Posts
    448
    sorry since you have logical partitions , sharing of home directory is not an issue, but all the partitions must be able to communicate using any remote shell command

  8. #8
    Join Date
    Mar 2003
    Posts
    60
    Thanks for all the useful info. I am able to create a partitioned database.
    One more thing I need to set up.

    I need to move the log files to diff location. Where will the log files reside? How does someone handle log files on partitioned database.
    Will it be on one server or all the partitions. How does one specify newlog path parameter.

    Thanks,
    j

    Originally posted by bmujeeb
    sorry since you have logical partitions , sharing of home directory is not an issue, but all the partitions must be able to communicate using any remote shell command

  9. #9
    Join Date
    Mar 2004
    Posts
    448
    Every database partition has its own log files.

    The newlogpath paramter at the database at each partition can be used to change that.

    You can use

    db2_all "update db cfg for <database name> using newlogpath < >"

    I always keep it same on all partitions.

    One more thing what ever path you use database add automatically to the NODExxxx.also check whether it will add the database name to the path, I forget that.

    run

    db2_all "get db cfg for <database name> show detail|grep -i LOG"

    to verify that.

    enjoy your partition database and try to learn commands that run to specific partitions

    You are most welcome


    regards,

    mujeeb

    Certified IBM DBA 6/8,OCP certified 7.3/8/8i/9i
    Sun certified system admin
    MS Computer Sciences,Wright State

  10. #10
    Join Date
    Sep 2012
    Posts
    177
    Hi all,

    I am new for the Db2 partition, MPP setup:

    In our setup, We have two development servers:
    Installation has been completed for the two servers, by using the response file of 1st server installation has been completed for the second server.

    This is a new set up. The only thing installed is db2 software has been installed.

    Our Hardware stock for both the servers:

    CPU: 1 x Intel Xeon 4-Core X5687 Processors (3.60GHz)
    RAM:32 GB
    Local drive size: 2 x 146 GB
    OS: Red Hat Linux version 5.5

    Setup look is db2inst1 having 4 databases with partition.

    How many partitions i have to make? and How to create the partition group and tablespace group for this setup?

    ISSUES:

    Instance is created for the 1st server only:

    [db2inst1@vrdtisdbhcl01 root]$ db2 connect to SAMPLE

    Database Connection Information

    Database server = DB2/LINUXX8664 9.7.4
    SQL authorization ID = DB2INST1
    Local database alias = SAMPLE


    rlogin to second server:
    ========================

    [db2inst1@vrdtisdbhcl01 root]$ rlogin -l db2inst1 vrdtisdbhcl02
    Last login: Sat Sep 22 20:59:31 from devdw


    creating the database in 1st server only (-----------------------------------------------)


    But i can't able to connect to the database:
    ==============================================

    [db2inst1@vrdtisdbhcl02 ~]$ db2 connect to SAMPLE
    SQL1031N The database directory cannot be found on the indicated file system.
    SQLSTATE=58031


    Where i went wrong?

    Could you please give me the suggestions?

    Thanks,
    laxman.

Posting Permissions

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