Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2016
    Posts
    9

    Question Answered: How to create a DB2 copy from an existing DB after an AIX OS reinstallation

    I have a specific problem at my end:

    I had a crash of my system and the AIX OS was reinstalled and restored from the last available backup.

    There were two databases MCINT and MCINTWI.

    Database 1 entry:
    Database alias = MCINT
    Database name = MCINT
    Local database directory = /p_vdata/db2padm


    Database 2 entry:
    Database alias = MCINTWI
    Database name = MCINTWI
    Local database directory = /db_data_mcintwi


    MCINTWI is a exact copy of MCINTWI.

    The MCINT DB is also working fine. I can list tables from the schema.

    But during the system restore MCINTWI was not fully restored and the /db_data_mcintwi filesystem is empty

    When i connect to the MCINTWI DB i get the following error: (even though the /db_data_mcintw location exists)

    $ db2 connect to mcintwi user db2padm using db2padm
    SQL1031N The database directory cannot be found on the indicated file system.
    SQLSTATE=58031

    Now, i want to create the exact same copy of MCINT DB at the same location where MCINTWI was present.

    Please help.

  2. Best Answer
    Posted by mark.bb

    "Hello,

    If your database is not enabled for automatic storage, you must use TO instead of ON. You don't need to create a new database before.
    Create a directory for logs: /db_data_mcintwi/db2logs

    You should to the following changes in the script:
    Code:
    -- DBPATH ON '<target-directory>' -> TO /db_data_mcintwi
    -- NEWLOGPATH '/p_vpmdata/db2padm/db2padm...' -> NEWLOGPATH /db_data_mcintwi/db2logs
    Change everywhere in the script:
    Code:
    PATH   '/p_vpmdata/NODE0000/SQL00001/ -> PATH ' (for example, '/p_vpmdata/NODE0000/SQL00001/SQLT0000.0' -> 'SQLT0000.0')
    PATH   '/p_vpmdata -> PATH '/db_data_mcintwi (for example, '/p_vpmdata/Tbs_DICORDB' -> '/db_data_mcintwi/Tbs_DICORDB')
    Run this script."


  3. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Code:
    db2 backup db MCINT to /somepath
    db2 restore db MCINT from /somepath on /db_data_mcintwi into MCINTWI redirect generate script MCINTWI.ddl
    Change every non-relative absolute path in the script MCINTWI.ddl to a new path desired which doesn't interfere with the paths of your original MCINT database.
    Execute the script afterwards.
    Show both original and changed scripts if you get errors.
    Regards,
    Mark.

  4. #3
    Join Date
    Dec 2016
    Posts
    9
    Hello Mark,

    When i try to run the command, i get the following error:

    $ db2 restore db MCINT from /backup2 on /db_data_mcintwi into MCINTWI redirect generate script MCINTWI.ddl
    SQL0901N The SQL statement failed because of a non-severe system error.
    Subsequent SQL statements can be processed. (Reason "DB Name specified
    matches the SPM Name. Please use a different name".) SQLSTATE=58004

    I uncatalogued the database:

    $ db2 uncatalog database MCINTWI
    DB20000I The UNCATALOG DATABASE command completed successfully.
    DB21056W Directory changes may not be effective until the directory cache is
    refreshed.

    But i still get the same error

    Please help.
    Last edited by zoh; 12-20-16 at 13:15.

  5. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    What appears in the db2diag.log after issuing the restore command?
    Regards,
    Mark.

  6. #5
    Join Date
    Dec 2016
    Posts
    9
    Hello Mark,

    I observed the db2diag.log and there were many "Access denied" issues, i removed them and re-tried the command.

    I have also created a new MCINTWI database on the same path:

    $ db2 create database MCINTWI AUTOMATIC STORAGE YES on /db_data_mcintwi
    DB20000I The CREATE DATABASE command completed successfully.

    Again i tried executing the command, but there is the following error:

    $ db2 restore db MCINT from /backup2 on /db_data_mcintwi into MCINTWI redirect generate script MCINTWI.ddl
    SQL20321N Storage paths cannot be provided because the database is not
    enabled for automatic storage. SQLSTATE=55062


    Here is the extract from the db2 log:

    Code:
    2016-12-21-10.51.38.631742+060 E18137721A615      LEVEL: Info
    PID     : 3866804              TID  : 7625        PROC : db2sysc 0
    INSTANCE: db2padm              NODE : 000         DB   : MCINTWI
    APPHDL  : 0-86                 APPID: *LOCAL.db2padm.161221095138
    AUTHID  : DB2PADM 
    EDUID   : 7625                 EDUNAME: db2agent (MCINTWI) 0
    FUNCTION: DB2 UDB, database utilities, sqludValidateUserOptionsAgainstMediaHead, probe:459
    DATA #1 : <preformatted>
    The database detected in the backup image requires that this restore
    be done WITHOUT ROLLING FORWARD.  No rollforward will be required
    following this restore.
    
    2016-12-21-10.51.38.631957+060 E18138337A501      LEVEL: Info
    PID     : 3866804              TID  : 7625        PROC : db2sysc 0
    INSTANCE: db2padm              NODE : 000         DB   : MCINTWI
    APPHDL  : 0-86                 APPID: *LOCAL.db2padm.161221095138
    AUTHID  : DB2PADM 
    EDUID   : 7625                 EDUNAME: db2agent (MCINTWI) 0
    FUNCTION: DB2 UDB, database utilities, sqludPrintStartingMsg, probe:1286
    DATA #1 : <preformatted>
    Starting  redirect restore generate script.
    Agent EDU ID: 7625
    
    2016-12-21-10.51.38.632137+060 E18138839A553      LEVEL: Warning
    PID     : 3866804              TID  : 7625        PROC : db2sysc 0
    INSTANCE: db2padm              NODE : 000         DB   : MCINTWI
    APPHDL  : 0-86                 APPID: *LOCAL.db2padm.161221095138
    AUTHID  : DB2PADM 
    EDUID   : 7625                 EDUNAME: db2agent (MCINTWI) 0
    FUNCTION: DB2 UDB, database utilities, sqluCompareDB, probe:1390
    DATA #1 : Sqlcode, PD_TYPE_SQLCODE, 4 bytes
    2529
    DATA #2 : Hexdump, 4 bytes
    0x07000000775848B8 : 0000 09E1                                  ....
    
    2016-12-21-10.51.38.632329+060 E18139393A1389     LEVEL: Warning
    PID     : 3866804              TID  : 7625        PROC : db2sysc 0
    INSTANCE: db2padm              NODE : 000         DB   : MCINTWI
    APPHDL  : 0-86                 APPID: *LOCAL.db2padm.161221095138
    AUTHID  : DB2PADM 
    EDUID   : 7625                 EDUNAME: db2agent (MCINTWI) 0
    FUNCTION: DB2 UDB, database utilities, sqluCompareDB, probe:1390
    MESSAGE : SQL2529W  Warning!  Restoring to an existing database that is 
              different from the backup image database, and the alias name "" of 
              the existing database does not match the alias name "" of the backup 
              image, and the database name "" of the existing database does not 
              match the database name "" of the backup image. The target database 
              will be overwritten by the backup version. The Roll-forward recovery 
              logs associated with the target database will be deleted.
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
     sqlcaid : SQLCA     sqlcabc: 136   sqlcode: 2529   sqlerrml: 27
     sqlerrmc: MCINTWI MCINT MCINTWI MCINT
     sqlerrp : sqluComp
     sqlerrd : (1) 0x00000000      (2) 0x00000000      (3) 0x00000000
               (4) 0x00000000      (5) 0x00000000      (6) 0x00000000
     sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)    
               (7)      (8)      (9)      (10)        (11)     
     sqlstate:      
    
    2016-12-21-10.51.38.645412+060 E18140783A442      LEVEL: Severe
    PID     : 3866804              TID  : 8395        PROC : db2sysc 0
    INSTANCE: db2padm              NODE : 000
    EDUID   : 8395                 EDUNAME: db2bm.7625.0 (MCINTWI) 0
    FUNCTION: DB2 UDB, database utilities, sqludProcessSGF, probe:3785
    MESSAGE : ZRC=0x800201AB=-2147352149=SQLB_AS_INVALID_RESTORE_OPTION
              "Only one path or drive is allowed with the TO option...."
    
    2016-12-21-10.51.38.645730+060 E18141226A456      LEVEL: Severe
    PID     : 3866804              TID  : 8395        PROC : db2sysc 0
    INSTANCE: db2padm              NODE : 000
    EDUID   : 8395                 EDUNAME: db2bm.7625.0 (MCINTWI) 0
    FUNCTION: DB2 UDB, database utilities, sqludProcessSGF, probe:3788
    DATA #1 : Sqlcode, PD_TYPE_SQLCODE, 4 bytes
    -20321
    DATA #2 : Hexdump, 8 bytes
    0x0000000110C60298 : 8002 01AB 0000 01AB                        ........
    
    2016-12-21-10.51.38.645902+060 E18141683A852      LEVEL: Severe
    PID     : 3866804              TID  : 8395        PROC : db2sysc 0
    INSTANCE: db2padm              NODE : 000
    EDUID   : 8395                 EDUNAME: db2bm.7625.0 (MCINTWI) 0
    FUNCTION: DB2 UDB, database utilities, sqludProcessSGF, probe:3788
    MESSAGE : SQL20321N  Storage paths cannot be provided because the database is 
              not enabled for automatic storage.
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
     sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -20321   sqlerrml: 3
     sqlerrmc: 427
     sqlerrp : sqludPro
     sqlerrd : (1) 0x00000000      (2) 0x00000000      (3) 0x00000000
               (4) 0x00000000      (5) 0x00000000      (6) 0x00000000
     sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)    
               (7)      (8)      (9)      (10)        (11)     
     sqlstate:      
    
    2016-12-21-10.51.38.646142+060 E18142536A447      LEVEL: Severe
    PID     : 3866804              TID  : 8395        PROC : db2sysc 0
    INSTANCE: db2padm              NODE : 000
    EDUID   : 8395                 EDUNAME: db2bm.7625.0 (MCINTWI) 0
    FUNCTION: DB2 UDB, database utilities, sqludbuf, probe:713
    DATA #1 : Sqlcode, PD_TYPE_SQLCODE, 4 bytes
    -2044
    DATA #2 : Hexdump, 8 bytes
    0x0000000110C60298 : FFFF F804 0000 0002                        ........
    
    2016-12-21-10.51.38.651285+060 E18142984A548      LEVEL: Severe
    PID     : 3866804              TID  : 7625        PROC : db2sysc 0
    INSTANCE: db2padm              NODE : 000         DB   : MCINTWI
    APPHDL  : 0-86                 APPID: *LOCAL.db2padm.161221095138
    AUTHID  : DB2PADM 
    EDUID   : 7625                 EDUNAME: db2agent (MCINTWI) 0
    FUNCTION: DB2 UDB, database utilities, sqludrsa, probe:701
    DATA #1 : Sqlcode, PD_TYPE_SQLCODE, 4 bytes
    -2036
    DATA #2 : Hexdump, 5 bytes
    0x07000000775848B8 : FFFF F80C 00                               .....
    
    2016-12-21-10.51.38.651522+060 E18143533A431      LEVEL: Severe
    PID     : 3866804              TID  : 7625        PROC : db2sysc 0
    INSTANCE: db2padm              NODE : 000         DB   : MCINTWI
    APPHDL  : 0-86                 APPID: *LOCAL.db2padm.161221095138
    AUTHID  : DB2PADM 
    EDUID   : 7625                 EDUNAME: db2agent (MCINTWI) 0
    FUNCTION: DB2 UDB, database utilities, sqludrsa, probe:781
    MESSAGE : Restore Terminated.
    Last edited by zoh; 12-21-16 at 06:05.

  7. #6
    Join Date
    Dec 2016
    Posts
    9
    Hello Mark,

    After some online search, i tried the command without the 'on' option and it has generated the ddl:

    $ db2 restore db MCINT from /backup2 into MCINTWI redirect generate script MCINTWI.ddl
    DB20000I The RESTORE DATABASE command completed successfully.

    Here is the file:
    MCINTWI.txt

    Please let me know what further steps should be followed.

  8. #7
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hello,

    If your database is not enabled for automatic storage, you must use TO instead of ON. You don't need to create a new database before.
    Create a directory for logs: /db_data_mcintwi/db2logs

    You should to the following changes in the script:
    Code:
    -- DBPATH ON '<target-directory>' -> TO /db_data_mcintwi
    -- NEWLOGPATH '/p_vpmdata/db2padm/db2padm...' -> NEWLOGPATH /db_data_mcintwi/db2logs
    Change everywhere in the script:
    Code:
    PATH   '/p_vpmdata/NODE0000/SQL00001/ -> PATH ' (for example, '/p_vpmdata/NODE0000/SQL00001/SQLT0000.0' -> 'SQLT0000.0')
    PATH   '/p_vpmdata -> PATH '/db_data_mcintwi (for example, '/p_vpmdata/Tbs_DICORDB' -> '/db_data_mcintwi/Tbs_DICORDB')
    Run this script.
    Regards,
    Mark.

  9. #8
    Join Date
    Dec 2016
    Posts
    9
    Thanks Mark,

    I have done the changes, MCINTWI_ch.txt
    Could you please check and let me know what specific command i need to execute.

    I also have a doubt:

    The original DB Node is /p_vpmdata/NODE0000

    And the newly created one is /db_data_mcintwi/db2padm/NODE0000/

    Will this cause any issue?

  10. #9
    Join Date
    Dec 2016
    Posts
    9
    Hello Mark,

    The restore seems to work.

    I am checking the schema and trying few operations on my side.

    I will check and let you know soon.

    Thanks a lot for your help.

  11. #10
    Join Date
    Dec 2016
    Posts
    9
    Thank you again mark.

    All the db restore process was successful.

    I can well access my data and all applications pointing to the db are working well.

    You were of great help!

    Wish you a happy 2017

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
  •