Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2016
    Posts
    17

    Answered: How to switch automatic storage to new location

    Hello,

    I'm running DB2 10.5 on Windows 2012 R2. I have installed it in the default C directory and the databases I created are all using automatic storage (which I believe will be utilizing the C drive). My question is can I keep the databases to automatic storage but instead of using drive C, I can somehow move/point the storage to drive D. If so how?

    Thanks.

  2. Best Answer
    Posted by mpaul

    "to check which storgroup and tablespace its using

    db2pd -db dbname -storagepaths
    db2pd -db dbname -tablespaces

    -> Create a new stogroup to the path u want to use
    -> Alter all tablespace to use the new stogroup

    After this get snapshot for database it will show you the automatic storage path and its STATE
    Drop pending means mostly temporary tablespace is using it you need to drop and recreate

    refer this link

    https://www.ibm.com/support/knowledg.../r0059433.html"


  3. #2
    Join Date
    Aug 2016
    Posts
    55
    Provided Answers: 2
    to check which storgroup and tablespace its using

    db2pd -db dbname -storagepaths
    db2pd -db dbname -tablespaces

    -> Create a new stogroup to the path u want to use
    -> Alter all tablespace to use the new stogroup

    After this get snapshot for database it will show you the automatic storage path and its STATE
    Drop pending means mostly temporary tablespace is using it you need to drop and recreate

    refer this link

    https://www.ibm.com/support/knowledg.../r0059433.html

  4. #3
    Join Date
    Dec 2016
    Posts
    17
    Hi,

    Thanks that seems to work for all tablespaces except temporary table space. I can see that the db has started using the new storage group however everytime I try to switch the storage group for temporary db I get the following error:

    The ALTER TABLESPACE statement failed because the change is not allowed for the type of table space. Table space name: "TEMPSPACE1". Table space type: "TEMPORARY". Incompatible clause: "USING STOGROUP".. SQLCODE=-20318, SQLSTATE=42858, DRIVER=4.18.60

    EDIT: Another issue is that after some time the db for which I switched the storage group has stopped connecting. Any idea what might caused it?
    Last edited by zhaider; 07-24-17 at 11:56.

  5. #4
    Join Date
    Aug 2016
    Posts
    55
    Provided Answers: 2
    you cannot alter temporary tablespace to new storage group.

    Create same page system or user temp tablespace with different name in new storage path

    drop the existing

    Recreate in new storage path

    Can you provide the error message related to stopped connecting ?

  6. #5
    Join Date
    Dec 2016
    Posts
    17
    Quote Originally Posted by mpaul View Post
    you cannot alter temporary tablespace to new storage group.

    Create same page system or user temp tablespace with different name in new storage path

    drop the existing

    Recreate in new storage path

    Can you provide the error message related to stopped connecting ?
    Hi,

    Thanks a ton for your response. Could you please also detail the procedure for dropping/creating temp tablespace? Also would that be safe?

    The error I'm getting while trying to connect to the db is quite strange, it throws the following error whenever I run db2 connect to db_name:

    SQL1768N Unable to start HADR. Reason code = "7".

    I should point out that the database in question was configured for HADR and it was working. Now it isn't.

    Thanks again.

Tags for this Thread

Posting Permissions

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