IBM's online reorg/DB2 version 7

Steve Whittaker

IBM's online reorg/DB2 version 7
We're just starting to get into the online reorg process using IBM's tool. We ran into an interesting error here and I was wondering if anyone could shed some light on it. (We're using DB2 v7 OS/390 v2.10)
We got the following message when against one our of objects being reorg'd"

DSNU301I -SYST DSNURFIT - KEYWORD 'SORTDATA' SPECIFIED AND/OR KEYWORD 'NOSYSREC' WAS SPECIFIED BUT NO CLUSTERING INDEX EXISTS, KEYWORD IS IGNORED

We checked the catalog and its true no clustering index was defined on this table but I thought that the first index on the table in a nonpartitioned tablespace was considered 'clustered'. This was a new table built and the index was not defined with 'cluster' and it is the only index on the table.
Does that NOT apply when using IBM's online reorg??
Any info is appreciated.

Thanks:
--Steve....

Steve Whittaker
email: [login to unmask email]
Ph: 919-546-7267

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steen Rasmussen

Re: IBM's online reorg/DB2 version 7
(in response to Steve Whittaker)
Greetings Stephen,

Your statement will be correct once you're on DB2V8.
At this point - and it has always been so - if you don't have an index defined as CLUSTER, DB2 will mark the index with the lowest OBID (as far as I remember) as the clustered index, but it will not be used to ORDER the data. So - doing reorg of a tablespace without an explicitly cluster index will NOT change the order of data - only FREEPAGE and PCTFREE will be taken care of.

Kind regards
Steen Rasmussen
Computer Associates
Senior Consultant


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Whittaker, Stephen
Sent: Monday, December 22, 2003 2:15 PM
To: [login to unmask email]
Subject: IBM's online reorg/DB2 version 7


We're just starting to get into the online reorg process using IBM's tool. We ran into an interesting error here and I was wondering if anyone could shed some light on it. (We're using DB2 v7 OS/390 v2.10)
We got the following message when against one our of objects being reorg'd"

DSNU301I -SYST DSNURFIT - KEYWORD 'SORTDATA' SPECIFIED AND/OR KEYWORD 'NOSYSREC' WAS SPECIFIED BUT NO CLUSTERING INDEX EXISTS, KEYWORD IS IGNORED

We checked the catalog and its true no clustering index was defined on this table but I thought that the first index on the table in a nonpartitioned tablespace was considered 'clustered'. This was a new table built and the index was not defined with 'cluster' and it is the only index on the table.
Does that NOT apply when using IBM's online reorg??
Any info is appreciated.

Thanks:
--Steve....

Steve Whittaker
email: [login to unmask email]
Ph: 919-546-7267

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Ebert

Re: IBM's online reorg/DB2 version 7
(in response to Steen Rasmussen)
This is not an error, just a warning and "working as designed". It's not
new, but existed since OLR was introduced in V5. Also, the REORG utility
requires an explicit clustering index (unlike SQL INSERTs) if you want to
use SORTDATA/NOSYSREC keywords (which, like SORTKEYS, are implied for OLR
so you don't have to specify them).
For multi-table TSs, it is enough if any table (even an empty one) has a
clustering index to prevent this message. That way you don't need the
SYSREC DD card.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



We're just starting to get into the online reorg process using IBM's tool.
We ran into an interesting error here and I was wondering if anyone could
shed some light on it. (We're using DB2 v7 OS/390 v2.10)
We got the following message when against one our of objects being
reorg'd"

DSNU301I -SYST DSNURFIT - KEYWORD 'SORTDATA' SPECIFIED AND/OR KEYWORD
'NOSYSREC' WAS SPECIFIED BUT NO CLUSTERING INDEX EXISTS, KEYWORD IS
IGNORED

We checked the catalog and its true no clustering index was defined on
this table but I thought that the first index on the table in a
nonpartitioned tablespace was considered 'clustered'. This was a new table
built and the index was not defined with 'cluster' and it is the only
index on the table.
Does that NOT apply when using IBM's online reorg??
Any info is appreciated.

Thanks:
--Steve....

Steve Whittaker
email: [login to unmask email]
Ph: 919-546-7267


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Colin Clayton

Re: IBM's online reorg/DB2 version 7
(in response to Michael Ebert)
Stephen,

if you have no clustering index then I don't see that you can expect reorg
to know what order you want the data sorted into.

I believe (but I could be wrong) that DB2 used to use the index with the
lowest OBID to determine where to insert new rows but I have not tried it,
and that may have changed in v7, and will almost definitely change in v8.

I also don't think that it makes any difference whether the reorg is online
or not. I'll run some tests and let you know.

Colin

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm