reorg on db2 ubd sever getting -209 error

[db2mainframer]

Here is my script that I run to get the qualifing rows that need reorged but I get this crazy -209 error while trying to use the TEMPSPACE1 tablespace to do my re-org....please help


select distinct
'REORG TABLE '||rtrim(char(t.tabschema,32))
||'.'||rtrim(char(t.tabname,32))
||' INDEX '||rtrim(char(i.indschema,32))
||'.'||rtrim(char(i.indname,32))
||' USE TEMPSPACE1'
||' RUNSTATS ON TABLE '||rtrim(char(t.tabschema,32))
||'.'||rtrim(char(t.tabname,32))
||' WITH DISTRIBUTION AND DETAILED INDEXES ALL SHRLEVEL CHANGE'
||'`DB2 COMMIT' as c1
from syscat.tables t
, syscat.indexes i
, syscat.indexes i2
where i.tabschema=t.tabschema
and i.tabname =t.tabname
and i2.tabschema=t.tabschema
and i2.tabname =t.tabname
and t.type ='T'
and t.tabschema not in ('SYSIBM')
and (i.indextype!='CLUS' and i.uniquerule ='P')
and i2.indextype!='CLUS'
and ( ( (t.npages>10 and i.clusterratio<95 and i.clusterfactor=-1)
or (t.npages>10 and i.clusterratio=-1 and i.clusterfactor<.95)
or ( (t.npages>10 and i.clusterratio between 0 and 94)
or (t.npages>10 and i.clusterfactor between 0 and .95)
)
)
or ((t.overflow)/(t.card+2.0) >.05
)
or (i.nleaf >10 and i.density < 80
)
or (i2.nleaf>10 and i2.density < 80
))
order by 1
;

 

[MarcLodge]

can you supply details of the error message?

 

[db2mainframer]

Here's my statement

REORG TABLE DB2GSE.GSE_COORD_REF INDEX DB2GSE.CO_REF_PK USE TEMPSPACE1;
RUNSTATS ON TABLE DB2GSE.GSE_COORD_REF WITH DISTRIBUTION AND DETAILED INDEXES ALL SHRLEVEL CHANGE;

here's the error

---------------------------------- Script -----------------------------------
Untitled2
-----------------------------------------------------------------------------
SQL2216N SQL error &quot;-290&quot; occurred while reorganizing a database table.



DB20000I The RUNSTATS command completed successfully.

 

[MarcLodge]

Found some info on this message which might be pertinent. The link can be found at:

https://aurora.vcu.edu/db2help/db2p0/frame3.htm#db2p092

Hope the following helps:

Troubleshooting Guide
Table Space OFFLINE
Before DB2 Universal Database Version 6, if a regular, long, or temporary table space was damaged or missing and circular logging was used, a connection to the database failed. If the problem was not corrected, the database would have to be restored from a backup.

DB2 Universal Database Version 6 has addressed this shortcoming. When a regular, long or temporary table space is damaged or missing, the table space reverts to OFFLINE state and the connection to the database will succeed, even if circular logging is used. This change improves your ability to handle temporary table spaces, because now there is nothing to recover from a temporary table space, except in a REORG situation. For example, once a connection is established, there are ways to bring your database up:

If you have multiple temporary table spaces, further operations requiring temporary table spaces will choose the good ones.
If you have only one temporary table space and it is bad, right after the connection you can create a new temporary table space and drop the bad one.

Archive Logging

ARCHIVE LOGGING before DB2 Universal Database Version 6
If log retain is on (log archival), and a regular, long, or temporary table space is damaged or missing, the connect succeeds and the table space is put in a rollforward pending state.

If the table space is accessed while the database is up, an error is returned (SQL0290N Table space access is not allowed).

For regular tables spaces, dropping may be possible, even if this is the only user table space.

If a table spans multiple table spaces, dropping is not possible.

If it is a temporary table space and there are no others, then anything that requires a temporary table to be created will fail with an error. For example, if you try to create an index over a table, you may get &quot;SQL0290N Table space access is not allowed&quot;. The reorganization of a table using this temporary table space may return &quot;SQL2216N SQL error --290 was found while reorganizing a database table.&quot; If the temporary table space is the only one, you cannot drop it. DB2 prevents a user from dropping the only temporary table space in the database. In this case, the error message is &quot;SQL0283N Temporary table space &quot;TEMPSPACE1&quot; cannot be dropped because it is the only temporary table space with a 4K page size in the database&quot;. The solution is to create a new temporary table space before dropping the old one.


ARCHIVE LOGGING after DB2 Universal Database Version 6
For log retain, the current log is applied without change, except OFFLINE status is added.

 

[db2mainframer]

Are you telling the database has to be down in order to run this reorg? How do I correct it.