HWM on Reorg Questioned

bwmiller16

Hi All -

OSName: AIX
NodeName: dr_aixdb01
Version: 5
Release: 3

dr_aixdb01$db2l evel
DB21085I Instance "db2inst1" uses "64" bits and DB2 code release
"SQL08027"
with level identifier "03080106".
Informational tokens are "DB2 v8.1.1.128", "s061108", "U810098", and
FixPak
"14".
Product is installed at "/usr/opt/db2_08_01".

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

We're bringing this database in-house from a hosting service. I want
to ensure that once we get it here that it is well organized, etc. I
would like to see a well-sized set of tablespaces and so I'm running
some tests on reorg, etc. to help determine the optimal size for each
TS.

I've been testing with one table in tablespace TIVA_CANCEL_DT named
tivadsn.tiva_ca ncel. I do an offline reorg and get n value for HWM.
I do another offline reorg and get x value for HWM. Everytime I reorg
I get the previous HWM....they flip-flop in what I consider
'interesting' behavior. The INPLACE reorg doesn't exhibit this flip-
flop behavior as the offline reorg does.

Thoughts?

-B

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

dr_aixdb01$db2 reorg table tivadsn.tiva_ca ncel <<<===== <<<=====
DB20000I The REORG command completed successfully.

Tablespace ID = 10
Name = TIVA_CANCEL_DT
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 222528
Useable pages = 222496
Used pages = 110640
Free pages = 111856
High water mark (pages) = 221232
<<<===== <<<=====
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 128
Number of containers = 2
Minimum recovery time = 2008-01-16-23.36.16.000000

dr_aixdb01$db2 reorg table tivadsn.tiva_ca ncel <<<===== <<<=====
DB20000I The REORG command completed successfully.

Tablespace ID = 10
Name = TIVA_CANCEL_DT
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 222528
Useable pages = 222496
Used pages = 110640
Free pages = 111856
High water mark (pages) = 110640
<<<===== <<<=====
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 128
Number of containers = 2
Minimum recovery time = 2008-01-16-23.36.16.000000

dr_aixdb01$db2 reorg table tivadsn.tiva_ca ncel <<<===== <<<=====
DB20000I The REORG command completed successfully.

Tablespace ID = 10
Name = TIVA_CANCEL_DT
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 222528
Useable pages = 222496
Used pages = 110640
Free pages = 111856
High water mark (pages) = 221232
<<<===== <<<=====
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 128
Number of containers = 2
Minimum recovery time = 2008-01-16-23.36.16.000000

dr_aixdb01$db2 reorg table tivadsn.tiva_ca ncel <<<===== <<<=====
DB20000I The REORG command completed successfully.

Tablespace ID = 10
Name = TIVA_CANCEL_DT
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 222528
Useable pages = 222496
Used pages = 110640
Free pages = 111856
High water mark (pages) = 110640
<<<===== <<<=====
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 128
Number of containers = 2
Minimum recovery time = 2008-01-16-23.36.16.000000

inplace reorg here <<<===== <<<=====

Tablespace ID = 10
Name = TIVA_CANCEL_DT
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 222528
Useable pages = 222496
Used pages = 110640
Free pages = 111856
High water mark (pages) = 110640
<<<===== <<<=====
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 128
Number of containers = 2
Minimum recovery time = 2008-01-16-23.36.16.000000

dr_aixdb01$db2 reorg table tivadsn.tiva_ca ncel <<<===== <<<=====
DB20000I The REORG command completed successfully.

Tablespace ID = 10
Name = TIVA_CANCEL_DT
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 222528
Useable pages = 222496
Used pages = 110640
Free pages = 111856
High water mark (pages) = 221232
<<<===== <<<=====
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 128
Number of containers = 2
Minimum recovery time = 2008-01-16-23.36.16.000000

dr_aixdb01$db2 reorg table tivadsn.tiva_ca ncel <<<===== <<<=====
DB20000I The REORG command completed successfully.

Tablespace ID = 10
Name = TIVA_CANCEL_DT
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 222528
Useable pages = 222496
Used pages = 110640
Free pages = 111856
High water mark (pages) = 110640
<<<===== <<<=====
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 128
Number of containers = 2
Minimum recovery time = 2008-01-16-23.36.16.000000

Mar 12 '08 #1

Subscribe Reply

2248

Similar topics

7538

Reorg on UDB

by: Raquel | last post by:

This question if for UDB on LUW. Suppose I take regular database backups of my database Monday: database backup <ts1> Tuesday: Tablespace reorged and imagecopied <ts2> Friday: database restored and roll-forwarded to a timestamp <ts3> on Wednesday. Now my question is: because the entire database is being roll-forwarded, I suppose the database roll forward operation will

DB2 Database

5306

DB2 UDB V8 inplace reorg

by: Anna Hutter | last post by:

Hello, this question is about DB2 UDB V8 and its inplace reorganisation. It seems that in small tables, an inplace reorg doesn´t start. Is there a limit in size of a table when a reorg starts? How can I be sure that a reorg has successfully finished? I found three methods to watch if an asynchronous reorg has finished: a) db2 "select * from table(sysproc.snapshot_tbreorg('reo_test',0)) as t"

DB2 Database

7804

Online reorg and super exclusive lock cause many problems

by: Lara | last post by:

Hello freaks, we have many problems with our online reorg and no idea how to resolve it. We had to do an online reorg beacause of 24 h online business. We start the reorg-statements (table by table) in a loop with the following statement: reorg table xxx.yyy inplace allow write access.

DB2 Database

8309

Reorg table/index questions

by: TomHorner | last post by:

I have several quick questions about reorg's, and a request (favor). 1. The documentation says that reorg "Reorganizes an index or a table" I cannot see how one would reorg ONE particular index - is there a way to do this? More importantly, would you WANT to do this? 2. We plan on doing inplace reorg's due to the 24x7 nature of our database servers. Are there any "undocumented" issues with these for V8.1, FP2 or FP6?

DB2 Database

4810

reorgchk reorg interpretation

by: hikums | last post by:

1. I did a reorgchk, and found that F2 on table and F4 on index indicates a reorg is necessary. 2. Did a reorg. 3. Again I do a reorgchk, the report is the same as in Step 1 4. I do a runstats on all columns and key columns with distribution and indexes all

DB2 Database

5467

ONLINE REORG

by: andy.standley | last post by:

Hi, we are running DB2 V8.2 (8.1.0.80) on redhat linux and I am trying to set the reorg to be online. I use the control center on the box - db2cc and then configure automatic maintenance wizard - and can get runstats to be online but reorg only offers offline at the activities settings. I have tried to understand why - I thought that online was always offered ? I think that all my indexes are type 2 - I ran some reorg indexes all on the...

DB2 Database

4401

Load utility vs reorg

by: Yuri | last post by:

We have an update sql that was running OK for several months completing in under 2 minutes . Yesterday something changed and we had to kill this update after it ran more than 6 hours. Update input table contained the normal 100K rows and master table contained about 135 M rows. We reorged the master table specifying the index we wanted DB2 to use in the update, ran runstats and reran the update with the same results (had to cancel it)....

DB2 Database

7139

Reorg Not Reclaiming Anticipated Space

by: mike_dba | last post by:

I am using DMS raw. I have a tablespace which is defined with 5 containers each allocated at 1.5 GB for a total of about 7.5 GB. I did a major deletion followed by a reorg and a runstats. I am not regaining as much space as anticipated. Additionally, the syscat.tables and syscat.indexes show my usage to be small. How do I determine who is using all the space ? The list tablespaces shows - Usable pages = 7.4 GB Used pages = 6.2 GB

DB2 Database

3841

Seemingly Successful Inplace Reorg returns -1 Completion Status

by: sethwai | last post by:

Hi, I have a nightly script that executes inplace reorgs allow write access for several tables after a previous script does a large number of delete operations. It usually has been executing fine. The other night 3 of them failed due to the log file system filling up. I resumed them individually and they all completed successfully. To eliminate the log space problem I changed the script to wait for one to complete before startng...

DB2 Database

8761

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

9280

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9142

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

8144

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6016

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4525

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4795

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3238

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2677

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP