Db2

 View Only

  • 1.  Restore database from S3 storage

    Posted Sat August 07, 2021 12:38 AM
    Hello,

    Did any one restored Db2 database from DB2REMOTE storage S3, if so what are the steps to follow? 
    We are doing PoC on s3 backup. It appears db2 creating multiple 5gb chunk of files on s3, but while restoring, db2 restore command doesn't like none of the file. Its throwing an exception below 

    ==>db2 "restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.001 taken at 20210806153019 into DBS3TEST redirect generate script red.sql"
    SQL2542N No match for a database image file was found based on the source database alias "DBNVMPOC" and timestamp "20210806153019" provided.

    We have all the files does exist in s3 as below 

    2021-08-06 15:30 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.001
    2021-08-06 15:33 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.002
    2021-08-06 15:36 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.003
    2021-08-06 15:39 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.004
    2021-08-06 15:42 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.005
    2021-08-06 15:45 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.006
    2021-08-06 15:48 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.007
    2021-08-06 15:51 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.008
    2021-08-06 15:54 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.009
    2021-08-06 15:56 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.010
    2021-08-06 15:59 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.011
    2021-08-06 16:02 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.012
    2021-08-06 16:05 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.013
    2021-08-06 16:08 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.014
    2021-08-06 16:11 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.015
    2021-08-06 16:14 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.016
    2021-08-06 16:17 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.017
    2021-08-06 16:20 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.018
    2021-08-06 16:23 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.019
    2021-08-06 16:26 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.020
    2021-08-06 16:28 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.021
    2021-08-06 16:31 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.022
    2021-08-06 16:34 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.023
    2021-08-06 16:37 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.024
    2021-08-06 16:40 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.025
    2021-08-06 16:43 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.026
    2021-08-06 16:46 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.027
    2021-08-06 16:49 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.028
    2021-08-06 16:52 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.029
    2021-08-06 16:55 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.030
    2021-08-06 16:58 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.031
    2021-08-06 17:01 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.032
    2021-08-06 17:04 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.033
    2021-08-06 17:06 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.034
    2021-08-06 17:10 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.035
    2021-08-06 17:12 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.036
    2021-08-06 17:15 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.037
    2021-08-06 17:18 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.038
    2021-08-06 17:21 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.039
    2021-08-06 17:24 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.040
    2021-08-06 17:27 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.041
    2021-08-06 17:30 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.042
    2021-08-06 17:33 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.043
    2021-08-06 17:36 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.044
    2021-08-06 17:39 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.045
    2021-08-06 17:42 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.046
    2021-08-06 17:45 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.047
    2021-08-06 17:48 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.048
    2021-08-06 17:51 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.049
    2021-08-06 17:54 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.050
    2021-08-06 17:57 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.051
    2021-08-06 18:00 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.052
    2021-08-06 18:02 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.053
    2021-08-06 18:05 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.054
    2021-08-06 18:08 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.055
    2021-08-06 18:11 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.056
    2021-08-06 18:14 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.057
    2021-08-06 18:17 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.058
    2021-08-06 18:20 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.059
    2021-08-06 18:22 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.060
    2021-08-06 18:25 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.061
    2021-08-06 18:28 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.062
    2021-08-06 18:31 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.063
    2021-08-06 18:34 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.064
    2021-08-06 18:37 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.065
    2021-08-06 18:39 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.066
    2021-08-06 18:42 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.067
    2021-08-06 18:45 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.068
    2021-08-06 18:48 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.069
    2021-08-06 18:51 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.070
    2021-08-06 18:54 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.071
    2021-08-06 18:57 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.072
    2021-08-06 19:00 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.073
    2021-08-06 19:03 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.074
    2021-08-06 19:06 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.075
    2021-08-06 19:09 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.076
    2021-08-06 19:12 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.077
    2021-08-06 19:14 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.078
    2021-08-06 19:17 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.079
    2021-08-06 19:20 5360324608 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.080
    2021-08-06 19:23 1132494848 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806153019.081

    ------------------------------
    Krishna Murakonda
    ------------------------------

    #Db2


  • 2.  RE: Restore database from S3 storage

    Posted Sat August 07, 2021 10:04 AM

    When restoring, do not specify specific filenames.  Just specify the location by directory (object name prefix, in this case.)

    This is the same as with local storage, where you specify the directory holding the image, not the image itself.

    For example:

    db2 "restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq/camadbq"



    ------------------------------
    Matthew Emmerton
    ------------------------------

    Original Message


  • 3.  RE: Restore database from S3 storage

    Posted Sat August 07, 2021 02:21 PM
    Hi Matthew,

    Thanks for the quick response. Followed the suggested step but the exception remain same.

    db2 -v "restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq/camadbq/ taken at 20210806070026 into DBS3TEST redirect generate script DB2REMOTE.restore"

    SQL2542N No match for a database image file was found based on the source database alias "DBNVMPOC" and timestamp "20210806070026" provided.


    ------------------------------
    Krishna Murakonda
    ------------------------------

    Original Message


  • 4.  RE: Restore database from S3 storage

    Posted Sat August 07, 2021 02:37 PM

    Have you tried your restore command using exactly the same path as you use in the backup command?  I made a guess of the correct restore path based on your command; it's possible that my guess contained too much information.



    ------------------------------
    Matthew Emmerton
    ------------------------------

    Original Message


  • 5.  RE: Restore database from S3 storage

    Posted Sat August 07, 2021 03:20 PM
    Thanks, Matthew

    Correct, I'm trying to provide the same path and different others, part of troubleshooting.

    The path on backup command: 

    db2 -v "backup database DBNVMPOC online to DB2REMOTE://s3direct//camadbq/ with 8 buffers buffer 2048 PARALLELISM 10 exclude logs without prompting"

    ==>s3cmd ls s3://camadbq/camadbq/
    DIR s3://camadbq/camadbq/backup/
    2021-08-06 07:00 5353242624 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806070026.001
    2021-08-06 07:03 5353242624 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806070026.002
    2021-08-06 07:06 5353242624 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806070026.003
    2021-08-06 07:08 5353242624 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806070026.004
    2021-08-06 07:11 5353242624 s3://camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806070026.005

    Restore commands

    ==>db2 -v "restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq into DBS3TEST redirect generate script DB2REMOTE.restore"
    restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq into DBS3TEST redirect generate script DB2REMOTE.restore
    SQL2522N More than one backup file matches the time stamp value provided for
    the backed up database image.


    ==>db2 -v "restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq taken at 20210806070026 into DBS3TEST redirect generate script DB2REMOTE.restore"
    restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq taken at 20210806070026 into DBS3TEST redirect generate script DB2REMOTE.restore
    SQL0902C A system error occurred. Subsequent SQL statements cannot be
    processed. IBM software support reason code: "". SQLSTATE=58005


    db2 -v "restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq/camadbq taken at 20210806070026 into DBS3TEST redirect generate script DB2REMOTE.restore"
    restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq/camadbq taken at 20210806070026 into DBS3TEST redirect generate script DB2REMOTE.restore
    SQL2542N No match for a database image file was found based on the source
    database alias "DBNVMPOC" and timestamp "20210806070026" provided.


    db2 -v "restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806070026.001 taken at 20210806070026 into DBS3TEST redirect generate script DB2REMOTE.res>
    restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq/camadbq/DBNVMPOC.0.db2psin1.DBPART000.20210806070026.001 taken at 20210806070026 into DBS3TEST redirect generate script DB2REMOTE.restore
    SQL2542N No match for a database image file was found based on the source
    database alias "DBNVMPOC" and timestamp "20210806070026" provided.

    ------------------------------
    Krishna Murakonda
    ------------------------------

    Original Message


  • 6.  RE: Restore database from S3 storage

    Posted Sat August 07, 2021 03:50 PM

    The first two restore commands don't match the path used for the backup - they are missing the trailing slash.



    ------------------------------
    Matthew Emmerton
    ------------------------------

    Original Message


  • 7.  RE: Restore database from S3 storage

    Posted Sat August 07, 2021 05:02 PM
    Hi Matthew,

    Adding tailing slash producing different exception 

    ==>db2 -v "restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq/ taken at 20210806070026 into DBS3TEST redirect generate script DB2REMOTE.restore"
    restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq/ taken at 20210806070026 into DBS3TEST redirect generate script DB2REMOTE.restore
    SQL0902C A system error occurred. Subsequent SQL statements cannot be processed. IBM software support reason code: "". SQLSTATE=58005

    ==>db2 -v "restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq/ taken at 20210806070026 into DBS3TEST redirect"
    restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq/ taken at 20210806070026 into DBS3TEST redirect
    SQL0902C A system error occurred. Subsequent SQL statements cannot be processed. IBM software support reason code: "". SQLSTATE=58005

    ==>db2 -v "restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq/ taken at 20210806070026 into DBS3TEST "
    restore database DBNVMPOC from DB2REMOTE://s3direct//camadbq/ taken at 20210806070026 into DBS3TEST
    SQL0902C A system error occurred. Subsequent SQL statements cannot be processed. IBM software support reason code: "". SQLSTATE=58005

    ------------------------------
    Krishna Murakonda
    ------------------------------

    Original Message