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?

do you have autocommit on ?
try a commit after the restore before rollforward

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.

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

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

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;

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