tablespace queisced after load fail in partioned Database

Hi.

I'm on DB2 9.5.0.1 on Linux AMD 64 and the database has four
partitions
where catalog is on partion 0 and user data on 1 thru 3.

After a load failure (path to load file was wrong) I cannot access the
tables, even though the loads have been rerun with success.

set integrity for ..table..
results in SQL0290N Table space access is not allowed.
SQLSTATE=55039

Load query table says
Tablestate:
Set Integrity Pending

db2tbst 18 says
State = Load Pending

db2tbst 19
State = Quiesced Share
+ Load Pending

quiesce tablespaces for table ..HIST reset
says:
SQL3805N The state of the application or of one or more table spaces
for the
table specified prohibits the loadapi action or quiescemode "9".
Reason code =
"1".

I don't know how to get out of this state.
Any ideas?
/dg

On Aug 21, 4:37 pm, danfa...@hotmail.com wrote:
> Hi.
>
> I'm on DB2 9.5.0.1 on Linux AMD 64 and the database has four
> partitions
> where catalog is on partion 0 and user data on 1 thru 3.
>
> After a load failure (path to load file was wrong) I cannot access the
> tables, even though the loads have been rerun with success.
>
> set integrity for ..table..
> results in SQL0290N Table space access is not allowed.
> SQLSTATE=55039
>
> Load query table says
> Tablestate:
> Set Integrity Pending
>
> db2tbst 18 says
> State = Load Pending
>
> db2tbst 19
> State = Quiesced Share
> + Load Pending
>
> quiesce tablespaces for table ..HIST reset
> says:
> SQL3805N The state of the application or of one or more table spaces
> for the
> table specified prohibits the loadapi action or quiescemode "9".
> Reason code =
> "1".
>
> I don't know how to get out of this state.
> Any ideas?
> /dg


u need to take backup of your tablespace to get out of this state.
make sure to give "nonrecoverable or copy yes" option while
performing Load operation.


Regards,
Situ.

On Aug 21, 4:40 pm, situ wrote:
> On Aug 21, 4:37 pm, danfa...@hotmail.com wrote:
>
>
>
> > Hi.
>
> > I'm on DB2 9.5.0.1 on Linux AMD 64 and the database has four
> > partitions
> > where catalog is on partion 0 and user data on 1 thru 3.
>
> > After a load failure (path to load file was wrong) I cannot access the
> > tables, even though the loads have been rerun with success.
>
> > set integrity for ..table..
> > results in SQL0290N Table space access is not allowed.
> > SQLSTATE=55039
>
> > Load query table says
> > Tablestate:
> > Set Integrity Pending
>
> > db2tbst 18 says
> > State = Load Pending
>
> > db2tbst 19
> > State = Quiesced Share
> > + Load Pending
>
> > quiesce tablespaces for table ..HIST reset
> > says:
> > SQL3805N The state of the application or of one or more table spaces
> > for the
> > table specified prohibits the loadapi action or quiescemode "9".
> > Reason code =
> > "1".
>
> > I don't know how to get out of this state.
> > Any ideas?
> > /dg
>
> u need to take backup of your tablespace to get out of this state.
> make sure to give "nonrecoverable or copy yes" option while
> performing Load operation.
>
> Regards,
> Situ.

Hey sorry my mistake, since its load pending state just invoke Load
terminate or Load replace command

Regards,
Situ

On Aug 21, 1:44*pm, situ wrote:
> On Aug 21, 4:40 pm, situ wrote:
>
>
>
>
>
> > On Aug 21, 4:37 pm, danfa...@hotmail.com wrote:
>
> > > Hi.
>
> > > I'm on *DB2 9.5.0.1 *on Linux AMD 64 and the database has four
> > > partitions
> > > where catalog is on partion 0 and *user data on 1 thru 3.
>
> > > After a load failure (path to load file was wrong) I cannot access the
> > > tables, even though the loads have been rerun with success.
>
> > > set integrity for ..table..
> > > results in SQL0290N *Table space access is not allowed.
> > > SQLSTATE=55039
>
> > > Load query table says
> > > Tablestate:
> > > * Set Integrity Pending
>
> > > db2tbst 18 *says
> > > State = Load Pending
>
> > > db2tbst 19
> > > State = Quiesced Share
> > > * * * + Load Pending
>
> > > quiesce tablespaces for table ..HIST reset
> > > says:
> > > SQL3805N *The state of the application or of one or more table spaces
> > > for the
> > > table specified prohibits the loadapi action or quiescemode "9".
> > > Reason code =
> > > "1".
>
> > > I don't know how to get out of this state.
> > > Any ideas?
> > > /dg
>
> > u need to take backup of *your tablespace to get out of this state.
> > make sure to give "nonrecoverable or copy yes" *option while
> > performing Load operation.
>
> > Regards,
> > Situ.
>
> Hey sorry my mistake, since its load pending state just invoke *Load
> terminate or Load replace command
>
> Regards,
> Situ- Hide quoted text -
>
> - Show quoted text -

Hi.

I did run load .. replace , but the tablespaces still show load
pending.

I'll try to run a tablespace backup.
/dg

On Aug 21, 7:59*am, danfa...@hotmail.com wrote:
> On Aug 21, 1:44*pm, situ wrote:
>
>
>
>
>
> > On Aug 21, 4:40 pm, situ wrote:
>
> > > On Aug 21, 4:37 pm, danfa...@hotmail.com wrote:
>
> > > > Hi.
>
> > > > I'm on *DB2 9.5.0.1 *on Linux AMD 64 and the database has four
> > > > partitions
> > > > where catalog is on partion 0 and *user data on 1 thru 3.
>
> > > > After a load failure (path to load file was wrong) I cannot access the
> > > > tables, even though the loads have been rerun with success.
>
> > > > set integrity for ..table..
> > > > results in SQL0290N *Table space access is not allowed.
> > > > SQLSTATE=55039
>
> > > > Load query table says
> > > > Tablestate:
> > > > * Set Integrity Pending
>
> > > > db2tbst 18 *says
> > > > State = Load Pending
>
> > > > db2tbst 19
> > > > State = Quiesced Share
> > > > * * * + Load Pending
>
> > > > quiesce tablespaces for table ..HIST reset
> > > > says:
> > > > SQL3805N *The state of the application or of one or more table spaces
> > > > for the
> > > > table specified prohibits the loadapi action or quiescemode "9".
> > > > Reason code =
> > > > "1".
>
> > > > I don't know how to get out of this state.
> > > > Any ideas?
> > > > /dg
>
> > > u need to take backup of *your tablespace to get out of this state.
> > > make sure to give "nonrecoverable or copy yes" *option while
> > > performing Load operation.
>
> > > Regards,
> > > Situ.
>
> > Hey sorry my mistake, since its load pending state just invoke *Load
> > terminate or Load replace command
>
> > Regards,
> > Situ- Hide quoted text -
>
> > - Show quoted text -
>
> Hi.
>
> I did run load *.. replace , but the tablespaces still show load
> pending.
>
> I'll try to run a tablespace backup.
> /dg- Hide quoted text -
>
> - Show quoted text -

I doubt the tbspce backup will succeed as the state is load pending
which is not normal state (required for backup).
As someone previously suggested, you need to cancel that load with a
LOAD TERMINATE command.
The result will be:
Tablespace is normal state. The table will also come out normal. If
the initial load was an INSERT then the table will come out as it was
before the load insert command. If the initial was a REPLACE then the
table will come out normal but empty.

You can then correct whatever caused the table load command to not
finish properly and reissue a load insert command.
Regards, Pierre.

Hi!

I had re-run the load successfully, so there was no Load to cancel or restart.
A tablespace backup cleared the problem.

:-)

/dg