REORG TABLESPACE

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mikael - 05 Jul 2006 20:18 GMT

Must admit, I am new to DB2:

Seems I can not issue command REORG TABLESPACE:

Commands Entered:
REORG TABLESPACE SBO_DB.TS_SBO_SAFE;

SQL0104N An unexpected token "TABLESPACE" was found following "REORG".

Expected tokens may include: "TABLE". SQLSTATE=42601

What am I doing wrong? According to helpfile it should work...

Mikael

Reply to this Message

Larry - 05 Jul 2006 20:41 GMT

> Must admit, I am new to DB2:
>
[quoted text clipped - 10 lines]
>
> Mikael

Only REORG format I can find in the doc is:

REORG TABLE
REORG INDEXES

Larry Edelstein

Reply to this Message

Mikael - 05 Jul 2006 20:46 GMT

Tks for your quick feedback

this link
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.
db2.doc.ugref/bjnbmstr28.htm

shows. Perhaps only under z/OS? We use DB2 V8.2.3 under Windows 2000
server.

Mikael Arhelger

> > Must admit, I am new to DB2:
> >
[quoted text clipped - 17 lines]
>
> Larry Edelstein

Reply to this Message

Gert van der Kooij - 05 Jul 2006 21:03 GMT

> Tks for your quick feedback
>
[quoted text clipped - 3 lines]
> shows. Perhaps only under z/OS? We use DB2 V8.2.3 under Windows 2000
> server.

That's right. The DB2 for Windows (and linux/unix) docs can be found at
http://publib.boulder.ibm.com/infocenter/db2help/index.jsp

Reply to this Message

Mikael - 05 Jul 2006 21:21 GMT

ok. So, can not REORG TABLESPACE then.

How can I then REORG all my TABLES and INDEX easily without much
hassle. I do not want to be faced with the daunting task to go through
one by one.

This would be needed from time to time obviously.

Any utility (or whatever) out there that does the job? Perhaps
Highlight all the TABLES and INDEX and off you go?

Mikael Arhelger

> > Tks for your quick feedback
> >
[quoted text clipped - 6 lines]
> That's right. The DB2 for Windows (and linux/unix) docs can be found at
> http://publib.boulder.ibm.com/infocenter/db2help/index.jsp

Reply to this Message

Jeroen van den Broek - 05 Jul 2006 21:35 GMT

> ok. So, can not REORG TABLESPACE then.
>
[quoted text clipped - 8 lines]
>
> Mikael Arhelger

Why not write a script that queries the DB2 Catalog Tables to generate REORG
TABLE / REORG INDEX statements for all your tables/indexes?

Signature

Jeroen

Reply to this Message

Mikael - 05 Jul 2006 21:41 GMT

tks. but as I said, I am quite new to DB2 and it seems not so easy for
me. Any sample codes out there or somebdy can show me?

Mikael

> > ok. So, can not REORG TABLESPACE then.
> >
[quoted text clipped - 11 lines]
> Why not write a script that queries the DB2 Catalog Tables to generate REORG
> TABLE / REORG INDEX statements for all your tables/indexes?

Reply to this Message

Gregor Kovač - 06 Jul 2006 07:03 GMT

> tks. but as I said, I am quite new to DB2 and it seems not so easy for
> me. Any sample codes out there or somebdy can show me?
[quoted text clipped - 19 lines]
>> --
>> Jeroen

SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = 'YOUR_SCHEMA'
SELECT INDNAME FROM SYSCAT.INDEXES WHERE INDSCHEMA = 'YOUR_SCHEMA'

Now you get table and index names in schema YOUR_SCHEMA.
Then you can write a sript that does the reorg:
SELECT 'REORG TABLE '|| RTRIM(LTRIM(TABSCHEMA)) || '.' ||
RTRIM(LTRIM(TABNAME)) FROM SYSCAT.TABLES WHERE TABSCHEMA = 'EMGSYS'
SELECT 'REORG INDEXES ALL FOR TABLE '|| RTRIM(LTRIM(TABSCHEMA)) || '.' ||
RTRIM(LTRIM(TABNAME)) FROM SYSCAT.TABLES WHERE TABSCHEMA = 'EMGSYS'

Now just copy the result (each column into one file) and run it:
DB2 CONNECT TO DB_NAME USER USER USING PASSWORD
DB2 -F your_table_file.sql
DB2 -F your_indexes_file.sql

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

Ian - 06 Jul 2006 07:52 GMT

> ok. So, can not REORG TABLESPACE then.
>
[quoted text clipped - 6 lines]
> Any utility (or whatever) out there that does the job? Perhaps
> Highlight all the TABLES and INDEX and off you go?

Use the autonomic features. Read the docs on automatic maintenance,
specifically for Automatic Reorganization (AUTO_REORG).

Reply to this Message

Mikael - 06 Jul 2006 17:11 GMT

Tks everybody! Pointed me in the right direction. Now I know where to
look.

By the way, it (REORG) does not flush out Orphans, correct? Got some
in the System Tables it seem after doing db2ckmig to preapre for
upgrade to V9.

Mikael

> > ok. So, can not REORG TABLESPACE then.
> >
[quoted text clipped - 9 lines]
> Use the autonomic features. Read the docs on automatic maintenance,
> specifically for Automatic Reorganization (AUTO_REORG).

Reply to this Message