HELP!!! Table "xxx.yyy" cannot be accessed
ce1st
After a bulk update of a column in a table, the table
cannot be accessed any more. When I issue command
"select * from xxx.yyy", the following error is displayed.
SQL1477N Table "xxx.yyy" cannot be accessed. SQLSTATE=55019
The table contains more than 1,000,000 records.
During the bulk update, I issued the following commands.
db2=> update command options using c off
db2=> alter table xxx.yyy activate not logged initially
db2=> update xxx.yyy set fff=newvalue
db2=> commit
Then, the table cannot be accessed any more.
Can anyone help me please?
Sathyaram
SQL1477N Table "<table-name>" cannot be accessed.
Explanation: An attempt was made to access a table that is not
accessible. The table may not be accessible because of one of
the following reasons:
o The table had NOT LOGGED INITIALLY activated when the unit of
work was rolled back.
o The table is a partitioned declared temporary table and one
or more partitions failed since the temporary table was
declared (all declared temporary tables have the schema name
SESSION).
o ROLLFORWARD encountered the activation of NOT LOGGED
INITIALLY on this table or a NONRECOVERABLE load on this
table.
Access to this table is not allowed because its integrity cannot
be guaranteed.
User Response: One of the following actions can be taken.
o If the table had NOT LOGGED INITIALLY activated, drop the
table. If this table is required, re-create it.
o If the table is a declared temporary table, drop the table.
If this table is required, declare it again.
o Otherwise, restore from a tablespace or database backup. The
backup image must have been taken subsequent to the commit
point following the completion of the non-recoverable
operation (NOT LOGGED INITIALLY operation, or NONRECOVERABLE
load).
sqlcode: -1477
sqlstate: 55019
Lee
Based on the information provided it looks like an error was
encountered during the bulk update UOW (i.e one of the UPDATE
statements failed). This would explain why the UOW has been rolled
back and the table has now been marked inaccessible.
NOT LOGGED INITIALLY tables can also be placed in this state by
the rollforward utility see SQL Reference - CREATE TABLE for
more details.
Mike Bhola
correctly... you have an existing table and then you turn off
autocommit, activate NLI, run your updates and then commit.
If this is correct then what you are doing is unsupported ( unless the
DB2 docs are wrong ) and a minefield in terms of recoverability. As far
as I am aware, and according to the DB2 docs, NLI was only intended to
be used for first time load of a table ( hence the 'initially' ). This
bit is straight from the V7.1 Admin Guide....
Any changes made to the table by an Insert, Delete, Update, Create
Index,
Drop Index, or Alter Table operation IN THE SAME UNIT OF WORK IN WHICH
THE TABLE IS CREATED are not logged.
Regards,
Mike
Michael Bhola
had always created a table then activated NLI and loaded it up... hadn't
thought about activating NLI after this point... but it does work... I
just tried it. But the same goes for recoverability.... anything that
causes a rollback will render the table inaccessible. Presumably you are
doing this because the update blows your active log space ?
Lennart Jonsson
I use something similar when generating transitive closure for a huge tree in a
db (and therefor curious if I'm doing something wrong). After experimenting a
bit (this was back with V5, and I have assumed that it still is so) I came to
the conclusion that autocommit had to be turned off inorder to not log the
operation itself. Otherwise the ""alter table xxx.yyy activate not logged
initially" commited and turned on logging again. This is a fragment of the
script I use:
#!/bin/bash
[...]
# iter until failure
db2 -l ancestor.log +c -vf init_ancestor.sql
db2 -l ancestor.log -v commit work
db2 -l ancestor.log +c -vf iter_ancestor.sql
while [ $? = 0 ]; do
db2 -l ancestor.log -v commit work
db2 -l ancestor.log +c -vf iter_ancestor.sql
done
db2 -l ancestor.log -v commit work
[...]
both init_ancestor.sql and iter_ancestor.sql contains
ALTER TABLE ts_ancestor \
ACTIVATE NOT LOGGED INITIALLY \
APPEND ON \
VOLATILE
and an insert operation.
As mentioned, I'm curious if this is concidered as a wrong way of doing such a
thing (given that I can drop the table and start all over in case of failure)?
Cheers
/Lennart
In article <3B08F1DD...@uk.ibm.com>, Mike Bhola says...
ce1st
I tried without activating NOT LOGGED INITIALLY but the bulk update failed
with transaction log full!
Anyway, is there any method to recover my 1,000,000 data?
Thanks in advance.
Mike Bhola <bho...@uk.ibm.com> wrote in message news:<3B08F1DD...@uk.ibm.com>...
Michael Bhola
a point in time before you did the bulk update.
Another option might be to get the 'db2stset' tool from DB2 support
which should allow you to access the table and then you can export the
data, drop/recreate the table and import the data. Note that 'db2stset'
gets you access to your data but only so that you can export and then
drop the affected objects. You should not continue to use an object that
has been gained access to using 'db2stset'. You might also have problems
getting it... it's an internal tool and they don't like to give it out
except in emergencies... there is a good deal of scope for abuse.
Michael Bhola
used it in the past at the tablespace level... not the table level.
James a'Court
But you should be aware that you cannot be 100% sure that you have retrieved
all the rows, and this is not a supported way of retrieving data from your
table (I would think). You would want to have some other way of confirming that
you had all the data (eg knowledge of how many rows should exist in the table).
The restore and rollforward to point in time method is safer.
Regards
James a'Court