Redbrick List Forum

Re: testing 6.20, difference in reorg with dimensions

Posted By:
Date:

Bridget,

Hmmmm ... the REORG operation for 6.2 should have produced similar results
as that of 6.11.

The only realistic reason that the indexes would not be included by default
is if they were built 'DEFERRED'. By default, unless otherwise noted,
deferred indexes are not included unless specifically included in the REORG
control file syntax. Being that the 6.11 worked fine, I assume that the
indexes are not deferred.

That being said, I would call tech support and see if they can determine
what is happening.

Take care,
-- Bill

*********************************************************************
William S. Gates IBM
Consulting I/T Specialist 300 Nichols Avenue
Data Management Svcs. Wilmington, DE 19803

Business Intelligence
IBM Red Brick Data Warehouse

Cell: (302) 438-0779 www.ibm.com
Office: (302) 477-1805 gatesw@us.ibm.com
Fax: (302) 477-1806
eFax: (707) 516-7055

*********************************************************************




"Bridget.Man...."
<Bridget.Mansfield@voda To: redbrick-list@iiug.org
fone.co.nz> cc:
Sent by: Subject: testing 6.20, difference in reorg with dimensions [24]
forum.subscriber@iiug.o
rg


02/12/2003 06:39 PM






Hi all,

I am running through all main functions we use in Red Brick as part of
testing before upgrading to 6.2. I tried a reorg of a dimension, using no
other options, i.e.
'reorg cust_dim;'
In 6.11 all indexes are included, with the output looking like
(C) Copyright 1991-2001 IBM Corp.
All rights reserved.
Version 6.11.0(0)
** INFORMATION ** (409) Reorganizing table CUST_DIM in parallel mode.
** INFORMATION ** (1038) Testing segment structure of index
CUST_DIM_PK_IDX.
** INFORMATION ** (1038) Testing segment structure of index
CUST_DIM_CUSTOMER_ID_IDX.
** INFORMATION ** (1038) Testing segment structure of index
CUST_DIM_ACCOUNT_ID_IDX.
** INFORMATION ** (1038) Testing segment structure of index
CUST_DIM_CONN_ACTIVE_DATE_IDX.
** INFORMATION ** (1038) Testing segment structure of index
CUST_DIM_CONN_DEACT_DATE_IDX.
** INFORMATION ** (1038) Testing segment structure of index
CUST_DIM_CUST_SEGMENT_IDX.
** INFORMATION ** (417) Rebuilding index CUST_DIM_PK_IDX.
** INFORMATION ** (417) Rebuilding index CUST_DIM_CUSTOMER_ID_IDX.
** INFORMATION ** (417) Rebuilding index CUST_DIM_ACCOUNT_ID_IDX.
** INFORMATION ** (417) Rebuilding index CUST_DIM_CONN_ACTIVE_DATE_IDX.
** INFORMATION ** (417) Rebuilding index CUST_DIM_CONN_DEACT_DATE_IDX.
** INFORMATION ** (417) Rebuilding index CUST_DIM_CUST_SEGMENT_IDX.
** INFORMATION ** (513) Starting merge phase of index building
CUST_DIM_PK_IDX.
** INFORMATION ** (513) Starting merge phase of index building
CUST_DIM_CUSTOMER_ID_IDX.
** INFORMATION ** (513) Starting merge phase of index building
CUST_DIM_ACCOUNT_ID_IDX.
** INFORMATION ** (513) Starting merge phase of index building
CUST_DIM_CONN_ACTIVE_DATE_IDX.
** INFORMATION ** (513) Starting merge phase of index building
CUST_DIM_CONN_DEACT_DATE_IDX.
** INFORMATION ** (513) Starting merge phase of index building
CUST_DIM_CUST_SEGMENT_IDX.
** INFORMATION ** (8622) Total number of rows processed: 617116.
** INFORMATION ** (8614) Index CUST_DIM_PK_IDX successfully rebuilt.
** INFORMATION ** (8614) Index CUST_DIM_CUSTOMER_ID_IDX successfully
rebuilt.
** INFORMATION ** (8614) Index CUST_DIM_ACCOUNT_ID_IDX successfully
rebuilt.
** INFORMATION ** (8614) Index CUST_DIM_CONN_ACTIVE_DATE_IDX successfully
rebuilt.
** INFORMATION ** (8614) Index CUST_DIM_CONN_DEACT_DATE_IDX successfully
rebuilt.
** INFORMATION ** (8614) Index CUST_DIM_CUST_SEGMENT_IDX successfully
rebuilt.
** INFORMATION ** (414) Table CUST_DIM reorganization completed.
** STATISTICS ** (500) Time = 00:00:59.92 cp time, 00:00:23.93 time,
Logical IO count=113662, Blk Reads=291, Blk Writes=33

The exact same command, with the same database having been upgraded gives
the following output

** INFORMATION ** (8602) Reorganization of table CUST_DIM terminating early
because table does not have any indexes to rebuild and no referential
integrity checking was requested.
** STATISTICS ** (500) Time = 00:00:00.00 cp time, 00:00:00.00 time,
Logical IO count=43, Blk Reads=0, Blk Writes=0

Seems rather strange. Does anyone have any ideas why? If you change to
specify 'reorg cust_dim index ( CUST_DIM_PK_IDX ) it will rebuild that
index.

Has anyone upgraded to 6.20. What major differences are there? I have
noticed that query rewrites are different, in most cases for the better,
except for the following anomoly
select xxxxxxxx from yyyyy where month_sequence_number = (select
max(month_sequence_number) from date_dim where real_date = current_date)
in 6.1 this query is rewritten to use the agg built by
month_sequence_number
in 6.2 the query is not rewritten. It seems as if the reference to
real_date anywhere in the query messes things up. If I run the sub_query to
get the month_sequence_number, and substitute this in 6.2 the query is
rewritten. Since the query is something that is run often I need the value
to be dynamic.

Any explanations, ideas of where I may be making an error, etc will be
appreciated.

Thanks

Bridget Mansfield
______________________
DBA - Business Intelligence
09-355-7967
021-522-181





-----------------------------------------------------------------------------------------------

Have you seen our website?.... http://www.vodafone.co.nz

CAUTION: This correspondence is confidential and intended for the named
recipient(s) only.
If you are not the named recipient and receive this correspondence in
error, you must not copy,
distribute or take any action in reliance on it and you should delete it
from your system and
notify the sender immediately. Thank you.

Unless otherwise stated, any views or opinions expressed are solely those
of the author and do
not represent those of Vodafone New Zealand Limited.

Vodafone New Zealand Limited
21 Pitt Street, Private Bag 92161, Auckland, 1020, New Zealand
Telephone + 64 9 357 5100
Facsimile + 64 9 377 0962

Messages In This Thread

• testing 6.20, difference in reorg with dimensions
  Bridget.Mansfield@vodafone.co.nz -- Wednesday, 12 February 2003, at 6:39 p.m.
  • Re: testing 6.20, difference in reorg with dimensions
    William Gates -- Thursday, 6 March 2003, at 9:27 a.m.

Redbrick List Forum is maintained by Administrator with WebBBS 5.12.