db2 v8.2 reorg

[andy.standley@ecngroup.co.nz]

Hi,
I posted something a while ago about not being able to setup online
reorgs - the db2 control center only offers online runstats. So I
started adding tasks to reorg each table. This works fine, but
sometimes it does not! The reorg fails and alerts me that it failed.
The script is

CONNECT TO ECNDB;
REORG TABLE AIRLINE.FFMFWB INDEX AIRLINE.FFMFWB_ IX1 INPLACE ALLOW WRITE
ACCESS START ;
CONNECT RESET;

the retun code is -2219

and the output is

REORG TABLE AIRLINE.FFMFWB INDEX AIRLINE.FFMFWB_ IX1 INPLACE ALLOW WRITE
ACCESS START
SQL2219N The specified INPLACE table reorganization action on table
"AIRLINE.FFMFWB " is not allowed on one or more nodes. Reason code: "8".


These tasks are run as re-occuring scheduled tasks. From Iwhat I can
read it is telling me that it failed because of a previous failure,
even though the history of the tasks shows that the previous task was
OK. If I do a

db2 "select * from table(SNAPSHOT_ TBREORG('AIRLIN E', -1)) as snap "


it tells me the reorg has paused and not cmompleted - so I can and did
resume it.

Am I trying to do something that can not really be done online - and
why does the reorg stop - is it the INPLACE ALLOW WRITE ACCESS ? I run
it once a week. Is this too often ?


andy

[Jan M. Nelken]

Re: db2 v8.2 reorg

andy.standley@e cngroup.co.nz wrote:[color=blue]
> Hi,
> I posted something a while ago about not being able to setup online
> reorgs - the db2 control center only offers online runstats. So I
> started adding tasks to reorg each table. This works fine, but
> sometimes it does not! The reorg fails and alerts me that it failed.
> The script is
>
> CONNECT TO ECNDB;
> REORG TABLE AIRLINE.FFMFWB INDEX AIRLINE.FFMFWB_ IX1 INPLACE ALLOW WRITE
> ACCESS START ;
> CONNECT RESET;
>
> the retun code is -2219
>
> and the output is
>
> REORG TABLE AIRLINE.FFMFWB INDEX AIRLINE.FFMFWB_ IX1 INPLACE ALLOW WRITE
> ACCESS START
> SQL2219N The specified INPLACE table reorganization action on table
> "AIRLINE.FFMFWB " is not allowed on one or more nodes. Reason code: "8".
>
>
> These tasks are run as re-occuring scheduled tasks. From Iwhat I can
> read it is telling me that it failed because of a previous failure,
> even though the history of the tasks shows that the previous task was
> OK. If I do a
>
> db2 "select * from table(SNAPSHOT_ TBREORG('AIRLIN E', -1)) as snap "
>
>
> it tells me the reorg has paused and not cmompleted - so I can and did
> resume it.
>
> Am I trying to do something that can not really be done online - and
> why does the reorg stop - is it the INPLACE ALLOW WRITE ACCESS ? I run
> it once a week. Is this too often ?
>
>
> andy
>[/color]

SQL2219N The specified INPLACE table reorganization action on
table "<table-name>" is not allowed on one or more
nodes. Reason code: "<reason-code>".

Explanation:

The statement cannot be processed because it violates a
restriction as indicated by the following reason code:

....
8 INPLACE table reorganization is already in progress for the
specified table.
....

Are you trying to start reorg in place while previous reorg is still in progress?


Jan M. Nelken

[andy.standley@ecngroup.co.nz]

Re: db2 v8.2 reorg

Jan
I do not think so - the task runs once a week. Mostly they run ok then
occasionally I get an error like this - the previous reorg says it
ran fine, but the current one suggests that it can't run as a previous
one is not finished or did not complete as you point out. But I can see
that the previous one completed in the task center journal. Runstats is
also run online every week. Is there a chance than runstats fails to
complete and that impacts the reorg ?

thanks
andy

[Phil Sherman]

Re: db2 v8.2 reorg

I wonder if someone is running an additional reorg (that doesn't
complete) outside of the ones you've set up. This could cause exactly
what you are seeing.

Phil Sherman


andy.standley@e cngroup.co.nz wrote:[color=blue]
> Jan
> I do not think so - the task runs once a week. Mostly they run ok then
> occasionally I get an error like this - the previous reorg says it
> ran fine, but the current one suggests that it can't run as a previous
> one is not finished or did not complete as you point out. But I can see
> that the previous one completed in the task center journal. Runstats is
> also run online every week. Is there a chance than runstats fails to
> complete and that impacts the reorg ?
>
> thanks
> andy
>[/color]