Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Location
    Columbus, OH
    Posts
    55

    Unanswered: anyone know how I can kill the restore that's in progress so I can connect?

    OS: aix 6.1
    DB: DB2 LUW 9.5

    Every month I load a test database via a redirected restore from an online backup of a prod database.

    I suspect thre redirected restore had issues because a tablespace needs increased on the target system.

    Thu Dec 1 07:23:05 EST 2011
    Stopping DB2
    12/01/2011 07:23:09 0 0 SQL1064N DB2STOP processing was successful.
    SQL1064N DB2STOP processing was successful.
    Starting DB2
    12/01/2011 07:23:13 0 0 SQL1063N DB2START processing was successful.
    SQL1063N DB2START processing was successful.
    Starting redirected restore
    restore db scdoltpp from /db2backup/SCDOLTPP/monthly into scdtptst logtarget /oltpprod/instance/db2log/scdtptst/ replace existing redirect without prompting
    SQL1277W A redirected restore operation is being performed. Table space
    configuration can now be viewed and table spaces that do not use automatic
    storage can have their containers reconfigured.
    DB20000I The RESTORE DATABASE command completed successfully.

    set tablespace containers for 0 using (Path '/oltpprod/instance/scitptst/NODE0000/SQL00001/SQLT0000.0')
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 1 using (File '/oltpprod/instance/scitptst/db2name/srmreq16k/siebts_srmreq16k.dat' 25600)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 2 using (Path '/oltpprod/instance/scitptst/db2ts/tempts/tbs_temp_4k')
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 3 using (Path '/oltpprod/instance/scitptst/NODE0000/SQL00001/QCTEMPTS')
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 4 using (File '/db2ts16spa/scdtptst/basets/ts16k/siebts_tbs16kspa.dat' 1156960 )
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 5 using (File '/oltpprod/instance/scitptst/db2idx/baseidx/sieb_idx.dat' 4608000)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 6 using (File '/oltpprod/instance/scitptst/db2ts/basets/ts4k/siebts_tbs4k.dat' 5222400)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 7 using (File '/oltpprod/instance/scitptst/db2ts16/basets/siebts_tbs16k.dat' 2068000)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 8 using (File '/oltpprod/instance/scitptst/db2ts/basets/ts32k/siebts_tbs32k.dat' 4096)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 9 using (File '/oltpprod/instance/scitptst/db2idx/eimidx/siebts_eimidx.dat' 4096)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 10 using (File '/oltpprod/instance/scitptst/db2ts/eimts/eim4k/siebts_eim4k.dat' 4096)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 11 using (File '/oltpprod/instance/scitptst/db2ts/eimts/eim16k/siebts_eim16k.dat' 4096)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 12 using (File '/oltpprod/instance/scitptst/db2ts/eimts/eim32k/siebts_eim32k.dat' 4096)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 14 using (File '/oltpprod/instance/scitptst/db2name/cxns4k/siebts_cxns4k.dat' 600000)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 15 using (Path '/oltpprod/instance/scitptst/db2ts/tempts/tbs_temp_16k')
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 16 using (File '/oltpprod/instance/scitptst/db2idx/cxidx/siebts_cxnsidx.dat' 184320)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 17 using (File '/db2ts16spb/scdtptst/basets/ts16k/siebts_tbs16kspb.dat' 520000)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 18 using (File '/db2ts16spa/scdtptst/basets/ts16k/siebts_tbs4kspa.dat' 420000)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 19 using (PATH '/oltpprod/instance/scitptst/db2ts/tempts/tbs_temp_32k')
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 20 using (File '/oltpprod/instance/scitptst/db2name/dock16k/siebts_tbsdock16k.dat' 26624)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 21 using (File '/oltpprod/instance/scitptst/db2idx/dockidx/siebts_tbsdockidx.dat' 4096)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 22 using (File '/oltpprod/instance/scitptst/db2name/dock4k/siebts_tbsdock4k.dat' 4096)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 23 using (File '/db2ts16spb/scdtptst/basets/ts16k/siebts_tbs4kspb.dat' 740000)
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    set tablespace containers for 24 using (PATH '/oltpprod/instance/scitptst/db2ts/explaints')
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    restore db scdoltpp continue
    SQL1277W A redirected restore operation is being performed. Table space
    configuration can now be viewed and table spaces that do not use automatic
    storage can have their containers reconfigured.
    DB20000I The RESTORE DATABASE command completed successfully.

    rollforward db scdtptst to end of logs and complete
    SQL0752N Connecting to a database is not permitted within a logical unit of
    work when the CONNECT type 1 setting is in use. SQLSTATE=0A001

    Thu Dec 1 07:23:21 EST 2011


    Here's what I have tried so far:

    mmtsdb2a02a:/usr/local/scripts/db2maint/restores $ db2 rollback
    SQL1350N The application is not in the correct state to process this request.
    Reason code="3".


    mmtsdb2a02a:/usr/local/scripts/db2maint/restores $ db2 terminate
    DB20000I The TERMINATE command completed successfully.

    mmtsdb2a02a:/usr/local/scripts/db2maint/restores $ db2 connect to scdtptst
    SQL1119N A connection to or activation of database "SCDTPTST" cannot be made
    because a previous restore is incomplete. SQLSTATE=57019


    mmtsdb2a02a:/usr/local/scripts/db2maint/restores $ db2stop
    12/01/2011 08:22:56 0 0 SQL1064N DB2STOP processing was successful.
    SQL1064N DB2STOP processing was successful.

    mmtsdb2a02a:/usr/local/scripts/db2maint/restores $ db2start
    12/01/2011 08:23:04 0 0 SQL1063N DB2START processing was successful.
    SQL1063N DB2START processing was successful.


    mmtsdb2a02a:/usr/local/scripts/db2maint/restores $ db2 connect to scdtptst
    SQL1119N A connection to or activation of database "SCDTPTST" cannot be made
    because a previous restore is incomplete. SQLSTATE=57019



    anyone know how I can kill the restore that's in progress?

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    do you have autocommit on ?
    try a commit after the restore before rollforward
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2011
    Location
    Columbus, OH
    Posts
    55
    I believe I have autommit on becasue I have never had to commit any statements. Do you know an easy way to tell if autocommit is on? Thanks for your response.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if clp
    check default options (see ic for detailed command)
    when executing : db2 -tvf xxx when autocommit is on by default
    or specify the c switch
    see this page for all details
    LIST COMMAND OPTIONS
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by Tracygirl View Post
    restore db scdoltpp continue
    SQL1277W A redirected restore operation is being performed. Table space
    configuration can now be viewed and table spaces that do not use automatic
    storage can have their containers reconfigured.
    DB20000I The RESTORE DATABASE command completed successfully.

    I believe that the restore failed or had issues somewhere. We do the same process. The message above is not correct. You would not get this entire message on a RESTORE ... CONTINUE. Are you sure you are capturing the output correctly?

    Andy

  6. #6
    Join Date
    Aug 2011
    Location
    Columbus, OH
    Posts
    55
    I execute the restore via -tvf, so autocommit's on by default, right?

    Yes, ARWinner, I too think something failed int he restore. I think it failed because I didn't have my tablespaces sized correctly.

    Here's what I execute:
    restore db scdoltpp

    from /db2backup/SCDOLTPP/monthly into scdtptst
    logtarget /oltpprod/instance/db2log/scdtptst/
    replace existing redirect without prompting;

    set tablespace containers for 0 using (Path '/oltpprod/instance/scitptst/NODE0000/SQL00001/SQLT0000.0');

    set tablespace containers for 1 using
    (File '/oltpprod/instance/scitptst/db2name/srmreq16k/siebts_srmreq16k.dat' 25600);

    set tablespace containers for 2 using
    (Path '/oltpprod/instance/scitptst/db2ts/tempts/tbs_temp_4k');

    set tablespace containers for 3 using
    (Path '/oltpprod/instance/scitptst/NODE0000/SQL00001/QCTEMPTS');

    set tablespace containers for 4 using
    (File '/db2ts16spa/scdtptst/basets/ts16k/siebts_tbs16kspa.dat' 1156960 );

    set tablespace containers for 5 using
    (File '/oltpprod/instance/scitptst/db2idx/baseidx/sieb_idx.dat' 4608000);

    set tablespace containers for 6
    using (File '/oltpprod/instance/scitptst/db2ts/basets/ts4k/siebts_tbs4k.dat' 5222400);

    set tablespace containers for 7
    using (File '/oltpprod/instance/scitptst/db2ts16/basets/siebts_tbs16k.dat' 2068000);

    set tablespace containers for 8
    using (File '/oltpprod/instance/scitptst/db2ts/basets/ts32k/siebts_tbs32k.dat' 4096);

    set tablespace containers for 9
    using (File '/oltpprod/instance/scitptst/db2idx/eimidx/siebts_eimidx.dat' 4096);

    set tablespace containers for 10
    using (File '/oltpprod/instance/scitptst/db2ts/eimts/eim4k/siebts_eim4k.dat' 4096);

    set tablespace containers for 11
    using (File '/oltpprod/instance/scitptst/db2ts/eimts/eim16k/siebts_eim16k.dat' 4096);

    set tablespace containers for 12
    using (File '/oltpprod/instance/scitptst/db2ts/eimts/eim32k/siebts_eim32k.dat' 4096);

    set tablespace containers for 14
    using (File '/oltpprod/instance/scitptst/db2name/cxns4k/siebts_cxns4k.dat' 600000);

    set tablespace containers for 15
    using (Path '/oltpprod/instance/scitptst/db2ts/tempts/tbs_temp_16k');

    set tablespace containers for 16
    using (File '/oltpprod/instance/scitptst/db2idx/cxidx/siebts_cxnsidx.dat' 184320);

    set tablespace containers for 17 using
    (File '/db2ts16spb/scdtptst/basets/ts16k/siebts_tbs16kspb.dat' 520000);

    set tablespace containers for 18 using
    (File '/db2ts16spa/scdtptst/basets/ts16k/siebts_tbs4kspa.dat' 420000);

    set tablespace containers for 19
    using (PATH '/oltpprod/instance/scitptst/db2ts/tempts/tbs_temp_32k');

    set tablespace containers for 20
    using (File '/oltpprod/instance/scitptst/db2name/dock16k/siebts_tbsdock16k.dat' 26624);

    set tablespace containers for 21
    using (File '/oltpprod/instance/scitptst/db2idx/dockidx/siebts_tbsdockidx.dat' 4096);

    set tablespace containers for 22
    using (File '/oltpprod/instance/scitptst/db2name/dock4k/siebts_tbsdock4k.dat' 4096);

    set tablespace containers for 23
    using (File '/db2ts16spb/scdtptst/basets/ts16k/siebts_tbs4kspb.dat' 740000);

    set tablespace containers for 24
    using (PATH '/oltpprod/instance/scitptst/db2ts/explaints');

    restore db scdoltpp continue;

    rollforward db scdtptst to end of logs and complete;

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try executing the script with -tvsf so that an error will terminate the process. You should also capture the output to a file. This should help you diagnose the problem easier.

    Andy

Posting Permissions

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