Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2013
    Posts
    4

    Question Unanswered: DB2 DPF redirected restore generate script

    Hi folks,

    I am running DB2 9.7 on AIX 6.1.0.0 with the Database Partitioning Feature. It is a 13 Node setup consiting of 1 catalog node and 12 data partition nodes.

    The customer wants to have certain tables restored into a specific point in time. I want to approach this by a redirected restore into a new database for verification and then crossloading data into the production database. The command used to generate the script is as follows:

    Code:
    db2_all "|| db2 restore db PROD1
    REBUILD WITH TABLESPACE \(
    	SYSCATSPACE,
    	TEMPSPACE1,
    	TEMPTS_8K,
    	SYSTOOLSTMPSPACE,
    	TEMPTS_16K,
    	B1_DATA,
    	B1_INDEX\)
    use tsm open 6 sessions taken at 20150620043002 into PROD1T redirect generate script test_restore_2.sql"
    I get following error messages

    Code:
    rah: primary monitoring process for db2 is 21430676
    
    
    ps1d: SQL1035N  The database is currently in use.  SQLSTATE=57019
    ps1d: db2 restore db PROD1 ... completed rc=4
    
    ps1d: SQL1035N  The database is currently in use.  SQLSTATE=57019
    ps1d: db2 restore db PROD1 ... completed rc=4
    
    ps1d: SQL1035N  The database is currently in use.  SQLSTATE=57019
    ps1d: db2 restore db PROD1 ... completed rc=4
    
    ps1d: SQL1035N  The database is currently in use.  SQLSTATE=57019
    ps1d: db2 restore db PROD1 ... completed rc=4
    
    ps1d: SQL1035N  The database is currently in use.  SQLSTATE=57019
    ps1d: db2 restore db PROD1 ... completed rc=4
    
    ps1d: SQL1035N  The database is currently in use.  SQLSTATE=57019
    ps1d: db2 restore db PROD1 ... completed rc=4
    
    ps1d: SQL1035N  The database is currently in use.  SQLSTATE=57019
    ps1d: db2 restore db PROD1 ... completed rc=4
    
    ps1d: SQL1035N  The database is currently in use.  SQLSTATE=57019
    ps1d: db2 restore db PROD1 ... completed rc=4
    
    ps1d: SQL1035N  The database is currently in use.  SQLSTATE=57019
    ps1d: db2 restore db PROD1 ... completed rc=4
    
    ps1d: SQL1035N  The database is currently in use.  SQLSTATE=57019
    ps1d: db2 restore db PROD1 ... completed rc=4
    
    ps1d: SQL1035N  The database is currently in use.  SQLSTATE=57019
    ps1d: db2 restore db PROD1 ... completed rc=4
    
    ps1d: SQL1035N  The database is currently in use.  SQLSTATE=57019
    ps1d: db2 restore db PROD1 ... completed rc=4
    
    rah: primary monitoring process for db2 is 7733570
    
    ps1d: DB20000I  The RESTORE DATABASE command completed successfully.
    ps1d: db2 restore db PROD1 ... completed ok
    and the generated script doesnt seem to include the partitioned tablespace B1_DATA,B1_INDEX.

    Code:
    -- *****************************************************************************
    -- ** automatically created redirect restore script
    -- *****************************************************************************
    UPDATE COMMAND OPTIONS USING S ON Z ON PROD1_NODE0000.out V ON;
    SET CLIENT ATTACH_DBPARTITIONNUM  0;
    SET CLIENT CONNECT_DBPARTITIONNUM 0;
    -- *****************************************************************************
    -- ** automatically created redirect restore script
    -- *****************************************************************************
    RESTORE DATABASE PROD1
    -- USER  <username>
    -- USING '<password>'
    REBUILD WITH TABLESPACE (
      SYSCATSPACE
    , TEMPSPACE1
    , SYSTOOLSTMPSPACE
    , TS_TEMP_16K
    )
    USE TSM
    OPEN 1 SESSIONS
    -- OPTIONS '<options-string>'
    TAKEN AT 20150620043002
    -- DBPATH ON '<target-directory>'
    INTO PROD1T
    -- LOGTARGET '<directory>'
    -- NEWLOGPATH '/psd1/db2/prod1ins/PROD1/logs/NODE0/NODE0000/'
    -- WITH <num-buff> BUFFERS
    -- BUFFER <buffer-size>
    -- REPLACE HISTORY FILE
    -- REPLACE EXISTING
    REDIRECT
    -- PARALLELISM <n>
    -- COMPRLIB '<lib-name>'
    -- COMPROPTS '<options-string>'
    -- WITHOUT ROLLING FORWARD
    -- WITHOUT PROMPTING
    ;
    -- *****************************************************************************
    -- ** table space definition
    -- *****************************************************************************
    -- *****************************************************************************
    -- ** Tablespace name                            = SYSCATSPACE
    -- **   Tablespace ID                            = 0
    -- **   Tablespace Type                          = System managed space
    -- **   Tablespace Content Type                  = All permanent data. Regular table space.
    -- **   Tablespace Page size (bytes)             = 4096
    -- **   Tablespace Extent size (pages)           = 32
    -- **   Using automatic storage                  = No
    -- **   Total number of pages                    = 207351
    -- *****************************************************************************
    SET TABLESPACE CONTAINERS FOR 0
    -- IGNORE ROLLFORWARD CONTAINER OPERATIONS
    USING (
      PATH   '/psd1/db2/prod1ins/PROD1/tbspace/NODE0/SYSCATSPACE'
    );
    -- *****************************************************************************
    -- ** Tablespace name                            = TEMPSPACE1
    -- **   Tablespace ID                            = 1
    -- **   Tablespace Type                          = System managed space
    -- **   Tablespace Content Type                  = System Temporary data
    -- **   Tablespace Page size (bytes)             = 4096
    -- **   Tablespace Extent size (pages)           = 32
    -- **   Using automatic storage                  = No
    -- **   Total number of pages                    = 1
    -- *****************************************************************************
    SET TABLESPACE CONTAINERS FOR 1
    -- IGNORE ROLLFORWARD CONTAINER OPERATIONS
    USING (
      PATH   '/psd1/db2/prod1ins/PROD1/tbspace/NODE0/TEMPSPACE1'
    );
    -- *****************************************************************************
    -- ** Tablespace name                            = SYSTOOLSTMPSPACE
    -- **   Tablespace ID                            = 4
    -- **   Tablespace Type                          = System managed space
    -- **   Tablespace Content Type                  = User Temporary data
    -- **   Tablespace Page size (bytes)             = 4096
    -- **   Tablespace Extent size (pages)           = 4
    -- **   Using automatic storage                  = No
    -- **   Total number of pages                    = 1
    -- *****************************************************************************
    SET TABLESPACE CONTAINERS FOR 4
    -- IGNORE ROLLFORWARD CONTAINER OPERATIONS
    USING (
      PATH   '/psd1/db2/prod1ins/PROD1/tbspace/NODE0/SYSTOOLSTMPSPACE'
    );
    -- *****************************************************************************
    -- ** Tablespace name                            = TS_TEMP_16K
    -- **   Tablespace ID                            = 5
    -- **   Tablespace Type                          = System managed space
    -- **   Tablespace Content Type                  = System Temporary data
    -- **   Tablespace Page size (bytes)             = 16384
    -- **   Tablespace Extent size (pages)           = 64
    -- **   Using automatic storage                  = No
    -- **   Total number of pages                    = 1
    -- *****************************************************************************
    SET TABLESPACE CONTAINERS FOR 5
    -- IGNORE ROLLFORWARD CONTAINER OPERATIONS
    USING (
      PATH   '/psd1/db2/prod1ins/PROD1/tbspace/NODE0/TS_TEMP_16K'
    );
    -- *****************************************************************************
    -- ** start redirected restore
    -- *****************************************************************************
    RESTORE DATABASE PROD1 CONTINUE;
    -- *****************************************************************************
    -- ** end of file
    -- *****************************************************************************
    I have tried to do the generate script with omitting the catalog node <<-0< and seperately only for <<+0<
    but it doesnt help. What am i missing here?
    Thanks for your help in advance

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    In the partitioned environment you have to restore the database on the catalog node first.
    After that you can restore the database on other nodes in parallel.

    i.e.:
    db2_all "<<+0< db2 restore ..."
    db2_all "||<<-0< db2 restore ..."
    Regards,
    Mark.

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    I see that you use the same script file (test_restore_2.sql) in the db2_all command. You must use different files for different nodes for that.
    Try something like this:

    restore.sh
    Code:
    db2_all "]||<<+0<\" db2 -vtf ~/scripts/rst_0.sql -z ~/scripts/rst_##.log"
    db2_all "]||<<-0<\" n=##; sed -e \"s/@/\${n}/g\" ~/scripts/rst_a.sql > ~/scripts/rst_\${n}.sql; db2 -vtf ~/scripts/rst_\${n}.sql -z ~/scripts/rst_\${n}.log"
    rst_0.sql
    Code:
    restore db mydb from '/db2path/db2inst1/NODE0000/backup' on /db2path
    replace existing
    redirect generate script /db2home/db2inst1/scripts/restore_gen0.sql
    without prompting
    ;
    rst_a.sql
    Code:
    restore db mydb from '/db2path/db2inst1/NODE $4N /backup'
    replace existing
    redirect generate script /db2home/db2inst1/scripts/restore_gen@.sql
    without prompting
    ;
    Regards,
    Mark.

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
  •