Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2012
    Location
    Ukraine
    Posts
    24

    Unanswered: Migrate Database to purescale

    Hello everyone,

    I need to move the database from old DB2 9.7 server to the new purescale DB2 10.1 server. Standard backup-restore operation is not supported for restoring non-purescale environment backup in purescale environment as per SQL5099N error.

    What is the best way to do this? I need to preserve tablespaces, buffers and other database settings.

    Best regards,
    Xat

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    db2move utility
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  3. #3
    Join Date
    Dec 2012
    Location
    Ukraine
    Posts
    24
    Thanks for the reply.
    1. I issued "db2move export" on source database host.
    2. Tarred results and sent to purescale server.
    3. Untarred results and issued "db2move import" at the purescale host.

    As a result the tables were imported, but tablespaces, buffers did not.

    Default tablespaces at the source host had modified page sizes. These sizes also didn't change to new values at the purescale host.

    I was verifying this via
    SELECT varchar(tbsp_name, 30) as tbsp_name, member,
    tbsp_type,
    tbsp_page_size
    FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t
    ORDER BY pool_data_p_reads DESC;

    Maybe I should use db2move with copy argument first?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Use db2look -e -l to extract the table, tablespace, and bufferpool definitions from the source database, then run the generated script on the target. If you use db2move to create the tables you have no control over their placement in specific tablespaces.

    I would not apply the database configuration parameters from a non-pureScale database to a pureScale database, better start with STMM and automatic settings and adjust as needed.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by xat View Post
    Hello everyone,

    I need to move the database from old DB2 9.7 server to the new purescale DB2 10.1 server. Standard backup-restore operation is not supported for restoring non-purescale environment backup in purescale environment as per SQL5099N error.

    What is the best way to do this? I need to preserve tablespaces, buffers and other database settings.

    Best regards,
    Xat
    If you use the correct overrides for log path, mirror log path, etc, I thought it was OK to restore a non-pureScale db to a pureScale db. I think you should google that error code and see if you fix it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Marcus_A View Post
    If you use the correct overrides for log path, mirror log path, etc, I thought it was OK to restore a non-pureScale db to a pureScale db.
    Nope, not possible. I believe it has something to do with how logging (and therefore recovery) is implemented in DB2 pureScale, since each member has its own log stream.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by n_i View Post
    Nope, not possible. I believe it has something to do with how logging (and therefore recovery) is implemented in DB2 pureScale, since each member has its own log stream.
    The log paths must be the same for each server, but I don't see the problem with that (similar to DPF). In any case, the log paths can be changed in the restore, or even after the restore (before db is activated). But I am not saying you are wrong, but I have not heard that from IBM who wanted a backup of our database to restore to pureScale.

    Check out this link:
    http://publib.boulder.ibm.com/infoce.../c0056182.html
    Last edited by Marcus_A; 12-21-12 at 15:28.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by n_i View Post
    Use db2look -e -l to extract the table, tablespace, and bufferpool definitions from the source database,
    After loading the data, you can use db2look again to punch all the constraints and UDF's, stored procedures and so on and apply them to the new database as well. That should do the trick
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  9. #9
    Join Date
    Dec 2012
    Location
    Ukraine
    Posts
    24
    Thank you all for the support. Looks like db2look and the following db2move have done the trick.

    I've also tried to restore db along with setting newlogpath variable and redirect keyword, but the same SQL error appeared.

    As for the movement I made two db2looks and db2move:

    1. db2look -d <dbname> -e -l -o <ddl output>
    2. db2look -d <dbname> -x -o <ddl user auth output>
    3. db2move <dbname> export

    At the purescale host:
    1. created DB
    2. executed both ddls with db2 -td\; -vf <ddl file>
    3. db2move <created DB> load (It took hour and a half to load 80G DB)
    4. removed 'check pending state' for all tables. (integrity unchecked)

    I can now do successful operations across the DB, but the performance is sooo slow about 10 times slower than on an old lower HW server.

Posting Permissions

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