RENAMES for availability

Kenneth Kane

RENAMES for availability
Hello ,
I am fishing for ideas or proven solutions to a current problem. Early
on in the demand for virtually seamless availability, the DBA's here
embraced a procedure to keep a table online while loading to a shadow table,
then doing a series of renames to 'flip' the online table with the freshly
loaded table. We added the STOPs for stored procedures when they came along
to minimize abends, and the rename itself requires STOPs for the TS
involved. We also do REBINDS for all packages that were invalidated by the
RENAME process.
It has all become cumbersome, and we occasionally trip over contention for
the catalog when more than 1 of these is run concurrently (we have about a
dozen). I have scheduled mutual avoidance, but still we get occaional
contention with reorgs, etc.

Cut to the chase.. Can someone give me their solution, or thoughts on
how to keep a table online, when a load REPLACE is necessary to freshen data
daily. Or if maintaining 2 copies and flipping is not avoidable, how do we
minimize impact when we have contending access from SP's, CICS and batch.
(DB2V7)



Kenneth M. Kane

UBS Financial Sevices Inc.

DBA Group

Tel: 201 352-3845
Mailto:[login to unmask email] <Mailto:[login to unmask email]>







Please do not transmit orders or instructions regarding a UBS account by
email. The information provided in this email or any attachments is not an
official transaction confirmation or account statement. For your protection,
do not include account numbers, Social Security numbers, credit card
numbers, passwords or other non-public information in your email. Because
the information contained in this message may be privileged, confidential,
proprietary or otherwise protected from disclosure, please notify us
immediately by replying to this message and deleting it from your computer
if you have received this communication in error. Thank you.

UBS Financial Services Inc.
UBS International Inc.


---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Randy Bright

Re: RENAMES for availability
(in response to Kenneth Kane)
LoadPlus for DB2 from BMC Software has had a "shadow" load feature for some
time now. It will LOAD REPLACE a tablespace into shadow VSAM datasets while
allowing full access to the original tablespace. When the load is complete,
we will -STOP the objects (tablespace and indexes), rename the VSAM datasets
(multi-tasked in parallel for speed), and then -START all the objects.
Since the objects in the DB2 catalog do not change, no BINDing is necessary.
The object is immediately available to applications once the rename is
complete. We utilize drain technology before issuing the -STOP to help
prevent "STOPP" statuses and we provide full restart and backout support
should a failure occur part-way through the rename process. We plan on
enhancements in the near future to utilize "FASTSWITCH" technology to
eliminate the -STOP for the rename process thus eliminating any outage.

If you want more information, contact me offline.

Randy Bright

-----Original Message-----
From: Kane, Kenneth M. [mailto:[login to unmask email]
Sent: Wednesday, January 14, 2004 10:38 AM
To: [login to unmask email]
Subject: RENAMES for availability



Hello ,
I am fishing for ideas or proven solutions to a current problem. Early
on in the demand for virtually seamless availability, the DBA's here
embraced a procedure to keep a table online while loading to a shadow table,
then doing a series of renames to 'flip' the online table with the freshly
loaded table. We added the STOPs for stored procedures when they came along
to minimize abends, and the rename itself requires STOPs for the TS
involved. We also do REBINDS for all packages that were invalidated by the
RENAME process.

It has all become cumbersome, and we occasionally trip over contention for
the catalog when more than 1 of these is run concurrently (we have about a
dozen). I have scheduled mutual avoidance, but still we get occaional
contention with reorgs, etc.

Cut to the chase.. Can someone give me their solution, or thoughts on
how to keep a table online, when a load REPLACE is necessary to freshen data
daily. Or if maintaining 2 copies and flipping is not avoidable, how do we
minimize impact when we have contending access from SP's, CICS and batch.

(DB2V7)



Kenneth M. Kane

UBS Financial Sevices Inc.

DBA Group

Tel: 201 352-3845
<Mailto:[login to unmask email]> Mailto:[login to unmask email]







Please do not transmit orders or instructions regarding a UBS account by
email. The information provided in this email or any attachments is not an
official transaction confirmation or account statement. For your protection,
do not include account numbers, Social Security numbers, credit card
numbers, passwords or other non-public information in your email. Because
the information contained in this message may be privileged, confidential,
proprietary or otherwise protected from disclosure, please notify us
immediately by replying to this message and deleting it from your computer
if you have received this communication in error. Thank you.

UBS Financial Services Inc.
UBS International Inc.

----------------------------------------------------------------------------
----- 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Paul A Redhead

Re: RENAMES for availability
(in response to Randy Bright)




Ken,
There are so many unknowns about the table use, size, database design
etc..., but since you asked for it (thoughts that is), here's one with
little chance of success. Perhaps you could have an additional field in the
table, for want of something better it might be DATA_DATE, and you could
have the SQL which access that table either do something along the lines of
'AND DATA_DATE = CURRENT DATE' to avoid a join, or add 'AND
MY_REFRESH_TABLE.DATA_DATE = NEW_CONTROL_TABLE.DATA_DATE' whereby the
update of the value in the control table 'flips' use to the new data. This
may allow you to instigate a process whereby you LOAD RESUME SHRLEVEL
CHANGE the new data ... perhaps the old rows could be 'deleted' via a
delete or online REORG with discard.

Paul.





"Kane, Kenneth M." <[login to unmask email]>@IDUGDB2-L.ORG> on 15/01/2004
02:37:48 AM

Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: RENAMES for availability


Hello ,
I am fishing for ideas or proven solutions to a current problem. Early
on in the demand for virtually seamless availability, the DBA's here
embraced a procedure to keep a table online while loading to a shadow
table,
then doing a series of renames to 'flip' the online table with the
freshly
loaded table. We added the STOPs for stored procedures when they came
along
to minimize abends, and the rename itself requires STOPs for the TS
involved. We also do REBINDS for all packages that were invalidated by the
RENAME process.
It has all become cumbersome, and we occasionally trip over contention
for
the catalog when more than 1 of these is run concurrently (we have about a
dozen). I have scheduled mutual avoidance, but still we get occaional
contention with reorgs, etc.

Cut to the chase.. Can someone give me their solution, or thoughts on
how to keep a table online, when a load REPLACE is necessary to freshen
data
daily. Or if maintaining 2 copies and flipping is not avoidable, how do we
minimize impact when we have contending access from SP's, CICS and batch.
(DB2V7)



Kenneth M. Kane

UBS Financial Sevices Inc.

DBA Group

Tel: 201 352-3845
Mailto:[login to unmask email] <Mailto:[login to unmask email]>







Please do not transmit orders or instructions regarding a UBS account by
email. The information provided in this email or any attachments is not an
official transaction confirmation or account statement. For your
protection,
do not include account numbers, Social Security numbers, credit card
numbers, passwords or other non-public information in your email. Because
the information contained in this message may be privileged, confidential,
proprietary or otherwise protected from disclosure, please notify us
immediately by replying to this message and deleting it from your computer
if you have received this communication in error. Thank you.

UBS Financial Services Inc.
UBS International Inc.


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

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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. 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

(See attached file: C.htm)


************************************************************
Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads,
Queensland Transport or Maritime Safety Queensland, or
endorsed organisations utilising the same infrastructure.
If you have received this electronic mail message in error,
please immediately notify the sender and delete the message
from your computer.
************************************************************


---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Phil Grainger

Re: RENAMES for availability
(in response to Paul A Redhead)
Surely a LOAD REPLACE and concurrent availability are contradictory requirements?

If you are REPLACING the data why are you still allowing people to reference it???

Just wondering


Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Kane, Kenneth M.
Sent: 14 January 2004 16:38
To: [login to unmask email]
Subject: RENAMES for availability



Hello ,
I am fishing for ideas or proven solutions to a current problem. Early on in the demand for virtually seamless availability, the DBA's here embraced a procedure to keep a table online while loading to a shadow table, then doing a series of renames to 'flip' the online table with the freshly loaded table. We added the STOPs for stored procedures when they came along to minimize abends, and the rename itself requires STOPs for the TS involved. We also do REBINDS for all packages that were invalidated by the RENAME process.

It has all become cumbersome, and we occasionally trip over contention for the catalog when more than 1 of these is run concurrently (we have about a dozen). I have scheduled mutual avoidance, but still we get occaional contention with reorgs, etc.

Cut to the chase.. Can someone give me their solution, or thoughts on how to keep a table online, when a load REPLACE is necessary to freshen data daily. Or if maintaining 2 copies and flipping is not avoidable, how do we minimize impact when we have contending access from SP's, CICS and batch.

(DB2V7)



Kenneth M. Kane

UBS Financial Sevices Inc.

DBA Group

Tel: 201 352-3845
<Mailto:[login to unmask email]> Mailto:[login to unmask email]







Please do not transmit orders or instructions regarding a UBS account by email. The information provided in this email or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your email. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you.

UBS Financial Services Inc.
UBS International Inc.

--------------------------------------------------------------------------------- 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Phil Grainger

Re: RENAMES for availability
(in response to Phil Grainger)
Hmmm

My thoughts on whether this even makes sense still apply

If you LOAD REPLACE data whilst it is being processed, from an application point of view, how do you

- Cope with data that has been read, processed in other tables and then no longer exists after the LOAD
- Cope with rows that have been updated/deleted from the original table and are then replaced by the LOAD
- Handle rows that are inserted whilst the load is running but no longer exist after the LOAD

I just worry that this is one "on-line" utility too far (mind you, I think the same about the V8 On-Line LOAD with DISCARD!!!)


Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Bright, Randy
Sent: 14 January 2004 17:17
To: [login to unmask email]
Subject: Re: RENAMES for availability


LoadPlus for DB2 from BMC Software has had a "shadow" load feature for some time now. It will LOAD REPLACE a tablespace into shadow VSAM datasets while allowing full access to the original tablespace. When the load is complete, we will -STOP the objects (tablespace and indexes), rename the VSAM datasets (multi-tasked in parallel for speed), and then -START all the objects. Since the objects in the DB2 catalog do not change, no BINDing is necessary. The object is immediately available to applications once the rename is complete. We utilize drain technology before issuing the -STOP to help prevent "STOPP" statuses and we provide full restart and backout support should a failure occur part-way through the rename process. We plan on enhancements in the near future to utilize "FASTSWITCH" technology to eliminate the -STOP for the rename process thus eliminating any outage.

If you want more information, contact me offline.

Randy Bright

-----Original Message-----
From: Kane, Kenneth M. [mailto:[login to unmask email]
Sent: Wednesday, January 14, 2004 10:38 AM
To: [login to unmask email]
Subject: RENAMES for availability



Hello ,
I am fishing for ideas or proven solutions to a current problem. Early on in the demand for virtually seamless availability, the DBA's here embraced a procedure to keep a table online while loading to a shadow table, then doing a series of renames to 'flip' the online table with the freshly loaded table. We added the STOPs for stored procedures when they came along to minimize abends, and the rename itself requires STOPs for the TS involved. We also do REBINDS for all packages that were invalidated by the RENAME process.

It has all become cumbersome, and we occasionally trip over contention for the catalog when more than 1 of these is run concurrently (we have about a dozen). I have scheduled mutual avoidance, but still we get occaional contention with reorgs, etc.

Cut to the chase.. Can someone give me their solution, or thoughts on how to keep a table online, when a load REPLACE is necessary to freshen data daily. Or if maintaining 2 copies and flipping is not avoidable, how do we minimize impact when we have contending access from SP's, CICS and batch.

(DB2V7)



Kenneth M. Kane

UBS Financial Sevices Inc.

DBA Group

Tel: 201 352-3845
<Mailto:[login to unmask email]> Mailto:[login to unmask email]







Please do not transmit orders or instructions regarding a UBS account by email. The information provided in this email or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your email. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you.

UBS Financial Services Inc.
UBS International Inc.

--------------------------------------------------------------------------------- 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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: RENAMES for availability
(in response to Phil Grainger)
No, it does make sense. It simply means that, instead of having the table
unavailable while it's being loaded for possibly dozens of minutes, it's
not unavailable at all; and you don't have to worry about accesses failing
because it's in a UT restricted state. From the application side, it
simply looks like a nearly instant LOAD REPLACE. The objections you raise
apply to all LOAD REPLACEs.

One thing I'd dearly like to see is having incompatible utilities
serialise instead of failing... then I could automate all housekeeping
maintenance.

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



Hmmm

My thoughts on whether this even makes sense still apply

If you LOAD REPLACE data whilst it is being processed, from an application
point of view, how do you

- Cope with data that has been read, processed in other tables and then no
longer exists after the LOAD
- Cope with rows that have been updated/deleted from the original table
and are then replaced by the LOAD
- Handle rows that are inserted whilst the load is running but no longer
exist after the LOAD

I just worry that this is one "on-line" utility too far (mind you, I think
the same about the V8 On-Line LOAD with DISCARD!!!)

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Bright, Randy
Sent: 14 January 2004 17:17
To: [login to unmask email]
Subject: Re: RENAMES for availability

LoadPlus for DB2 from BMC Software has had a "shadow" load feature for
some time now. It will LOAD REPLACE a tablespace into shadow VSAM
datasets while allowing full access to the original tablespace. When the
load is complete, we will -STOP the objects (tablespace and indexes),
rename the VSAM datasets (multi-tasked in parallel for speed), and then
-START all the objects. Since the objects in the DB2 catalog do not
change, no BINDing is necessary. The object is immediately available to
applications once the rename is complete. We utilize drain technology
before issuing the -STOP to help prevent "STOPP" statuses and we provide
full restart and backout support should a failure occur part-way through
the rename process. We plan on enhancements in the near future to utilize
"FASTSWITCH" technology to eliminate the -STOP for the rename process thus
eliminating any outage.

If you want more information, contact me offline.
Randy Bright


---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Max Scarpa

Re: RENAMES for availability
(in response to Michael Ebert)
Michael , there's a WAD waiting for you........

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Kenneth Kane

Re: RENAMES for availability
(in response to Max Scarpa)
Thanks Phil,

The tables we 'flip' are primarilly reference data mined from several
sources daily. We replace 'Yesteday' with 'Today' and flip them online.
These table are not subject to updates as a rule.

Can you tell me where I can read more about V8 Online Load with DISCARD ?
Is there an English summary somewhere (outside of IBM documents) That
sounds good and dangerous, I am salivating.


-----Original Message-----
From: Grainger, Phil [mailto:[login to unmask email]
Sent: Thursday, January 15, 2004 4:52 AM
To: [login to unmask email]
Subject: Re: RENAMES for availability


Hmmm

My thoughts on whether this even makes sense still apply

If you LOAD REPLACE data whilst it is being processed, from an application
point of view, how do you

- Cope with data that has been read, processed in other tables and then no
longer exists after the LOAD
- Cope with rows that have been updated/deleted from the original table and
are then replaced by the LOAD
- Handle rows that are inserted whilst the load is running but no longer
exist after the LOAD

I just worry that this is one "on-line" utility too far (mind you, I think
the same about the V8 On-Line LOAD with DISCARD!!!)


Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Bright, Randy
Sent: 14 January 2004 17:17
To: [login to unmask email]
Subject: Re: RENAMES for availability


LoadPlus for DB2 from BMC Software has had a "shadow" load feature for some
time now. It will LOAD REPLACE a tablespace into shadow VSAM datasets while
allowing full access to the original tablespace. When the load is complete,
we will -STOP the objects (tablespace and indexes), rename the VSAM datasets
(multi-tasked in parallel for speed), and then -START all the objects.
Since the objects in the DB2 catalog do not change, no BINDing is necessary.
The object is immediately available to applications once the rename is
complete. We utilize drain technology before issuing the -STOP to help
prevent "STOPP" statuses and we provide full restart and backout support
should a failure occur part-way through the rename process. We plan on
enhancements in the near future to utilize "FASTSWITCH" technology to
eliminate the -STOP for the rename process thus eliminating any outage.

If you want more information, contact me offline.

Randy Bright

-----Original Message-----
From: Kane, Kenneth M. [mailto:[login to unmask email]
Sent: Wednesday, January 14, 2004 10:38 AM
To: [login to unmask email]
Subject: RENAMES for availability



Hello ,
I am fishing for ideas or proven solutions to a current problem. Early
on in the demand for virtually seamless availability, the DBA's here
embraced a procedure to keep a table online while loading to a shadow table,
then doing a series of renames to 'flip' the online table with the freshly
loaded table. We added the STOPs for stored procedures when they came along
to minimize abends, and the rename itself requires STOPs for the TS
involved. We also do REBINDS for all packages that were invalidated by the
RENAME process.

It has all become cumbersome, and we occasionally trip over contention for
the catalog when more than 1 of these is run concurrently (we have about a
dozen). I have scheduled mutual avoidance, but still we get occaional
contention with reorgs, etc.

Cut to the chase.. Can someone give me their solution, or thoughts on
how to keep a table online, when a load REPLACE is necessary to freshen data
daily. Or if maintaining 2 copies and flipping is not avoidable, how do we
minimize impact when we have contending access from SP's, CICS and batch.

(DB2V7)



Kenneth M. Kane

UBS Financial Sevices Inc.

DBA Group

Tel: 201 352-3845
<Mailto:[login to unmask email]> Mailto:[login to unmask email]







Please do not transmit orders or instructions regarding a UBS account by
email. The information provided in this email or any attachments is not an
official transaction confirmation or account statement. For your protection,
do not include account numbers, Social Security numbers, credit card
numbers, passwords or other non-public information in your email. Because
the information contained in this message may be privileged, confidential,
proprietary or otherwise protected from disclosure, please notify us
immediately by replying to this message and deleting it from your computer
if you have received this communication in error. Thank you.

UBS Financial Services Inc.
UBS International Inc.

----------------------------------------------------------------------------
----- 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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


Please do not transmit orders or instructions regarding a UBS account by
email. The information provided in this email or any attachments is not an
official transaction confirmation or account statement. For your protection,
do not include account numbers, Social Security numbers, credit card
numbers, passwords or other non-public information in your email. Because
the information contained in this message may be privileged, confidential,
proprietary or otherwise protected from disclosure, please notify us
immediately by replying to this message and deleting it from your computer
if you have received this communication in error. Thank you.

UBS Financial Services Inc.
UBS International Inc.


---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Phil Grainger

Re: RENAMES for availability
(in response to Kenneth Kane)
The only public domain stuff on V8 I have seen so far is in IBM documents (try the Version 8 Technical Preview red book - it's a good read, but does gloss over some stuff).
Alternatively you should have been at my V8 class at European IDUG in Nice (or perhaps my upcoming V8 half-day at IDUG Asia Pacific in Sydney.....)
Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Kane, Kenneth M.
Sent: 15 January 2004 14:22
To: [login to unmask email]
Subject: Re: RENAMES for availability

Thanks Phil,
The tables we 'flip' are primarily reference data mined from several sources daily. We replace 'Yesteday' with 'Today' and flip them online. These table are not subject to updates as a rule.
Can you tell me where I can read more about V8 Online Load with DISCARD ? Is there an English summary somewhere (outside of IBM documents) That sounds good and dangerous, I am salivating.


---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Randy Bright

Re: RENAMES for availability
(in response to Phil Grainger)
Very good explanation Dr. Ebert. If someone is worried about losing updates
to a tablespace, they will probably not be doing a LOAD REPLACE. I like to
point out that most DBA's want their LOAD jobs to run as fast as possible to
reduce the outage. I usually ask the question "If you could have a feature
that would cut each of your LOAD REPLACE jobs elapsed time in half, would
you want it?". Invariably the answer is "yes!". Follow that with "If you
could have a feature that would make all your LOAD REPLACE jobs run in
1/10th the elapsed time, would you want it?". Again, who could say "no"?
Finally, "If all your LOAD REPLACE jobs could run in only a few seconds,
would you want that?".

That is LOAD REPLACE SHRLEVEL CHANGE. It essentially compresses the elapsed
time of the replace operation down to the time it takes to rename the
objects in question. Why spend two hours with DB2 objects unavailable when
you could perform the same function with only a 5 second outage? Granted,
it is best suited for read-only data, but if you are replacing data that is
being updated, why worry about the last update just before a 5 second outage
when you don't worry about the last update just before a two hour outage?

Randy Bright
Architect, DB2 Utilities
BMC Software, Inc.

-----Original Message-----
From: Michael Ebert [mailto:[login to unmask email]
Sent: Thursday, January 15, 2004 4:38 AM
To: [login to unmask email]
Subject: Re: RENAMES for availability



No, it does make sense. It simply means that, instead of having the table
unavailable while it's being loaded for possibly dozens of minutes, it's not
unavailable at all; and you don't have to worry about accesses failing
because it's in a UT restricted state. From the application side, it simply
looks like a nearly instant LOAD REPLACE. The objections you raise apply to
all LOAD REPLACEs.

One thing I'd dearly like to see is having incompatible utilities serialise
instead of failing... then I could automate all housekeeping maintenance.

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



Hmmm

My thoughts on whether this even makes sense still apply

If you LOAD REPLACE data whilst it is being processed, from an application
point of view, how do you

- Cope with data that has been read, processed in other tables and then no
longer exists after the LOAD
- Cope with rows that have been updated/deleted from the original table and
are then replaced by the LOAD
- Handle rows that are inserted whilst the load is running but no longer
exist after the LOAD

I just worry that this is one "on-line" utility too far (mind you, I think
the same about the V8 On-Line LOAD with DISCARD!!!)


Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Bright, Randy
Sent: 14 January 2004 17:17
To: [login to unmask email]
Subject: Re: RENAMES for availability

LoadPlus for DB2 from BMC Software has had a "shadow" load feature for some
time now. It will LOAD REPLACE a tablespace into shadow VSAM datasets while
allowing full access to the original tablespace. When the load is complete,
we will -STOP the objects (tablespace and indexes), rename the VSAM datasets
(multi-tasked in parallel for speed), and then -START all the objects.
Since the objects in the DB2 catalog do not change, no BINDing is necessary.
The object is immediately available to applications once the rename is
complete. We utilize drain technology before issuing the -STOP to help
prevent "STOPP" statuses and we provide full restart and backout support
should a failure occur part-way through the rename process. We plan on
enhancements in the near future to utilize "FASTSWITCH" technology to
eliminate the -STOP for the rename process thus eliminating any outage.

If you want more information, contact me offline.


Randy Bright

----------------------------------------------------------------------------
----- 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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: RENAMES for availability
(in response to Randy Bright)
One question that just occurred to me (I just did a partition-level
RECOVER TOCOPY of a partitioned TS, which now forces me to REBUILD all ten
100M row NPIs) is, how is the BUILD (BUILD2) phase for NPIs handled?

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



Very good explanation Dr. Ebert. If someone is worried about losing
updates to a tablespace, they will probably not be doing a LOAD REPLACE. I
like to point out that most DBA's want their LOAD jobs to run as fast as
possible to reduce the outage. I usually ask the question "If you could
have a feature that would cut each of your LOAD REPLACE jobs elapsed time
in half, would you want it?". Invariably the answer is "yes!". Follow
that with "If you could have a feature that would make all your LOAD
REPLACE jobs run in 1/10th the elapsed time, would you want it?". Again,
who could say "no"? Finally, "If all your LOAD REPLACE jobs could run in
only a few seconds, would you want that?".

That is LOAD REPLACE SHRLEVEL CHANGE. It essentially compresses the
elapsed time of the replace operation down to the time it takes to rename
the objects in question. Why spend two hours with DB2 objects unavailable
when you could perform the same function with only a 5 second outage?
Granted, it is best suited for read-only data, but if you are replacing
data that is being updated, why worry about the last update just before a
5 second outage when you don't worry about the last update just before a
two hour outage?

Randy Bright
Architect, DB2 Utilities
BMC Software, Inc.
-----Original Message-----
From: Michael Ebert [mailto:[login to unmask email]
Sent: Thursday, January 15, 2004 4:38 AM
To: [login to unmask email]
Subject: Re: RENAMES for availability


No, it does make sense. It simply means that, instead of having the table
unavailable while it's being loaded for possibly dozens of minutes, it's
not unavailable at all; and you don't have to worry about accesses failing
because it's in a UT restricted state. From the application side, it
simply looks like a nearly instant LOAD REPLACE. The objections you raise
apply to all LOAD REPLACEs.

One thing I'd dearly like to see is having incompatible utilities
serialise instead of failing... then I could automate all housekeeping
maintenance.

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



Hmmm

My thoughts on whether this even makes sense still apply

If you LOAD REPLACE data whilst it is being processed, from an application
point of view, how do you

- Cope with data that has been read, processed in other tables and then no
longer exists after the LOAD
- Cope with rows that have been updated/deleted from the original table
and are then replaced by the LOAD
- Handle rows that are inserted whilst the load is running but no longer
exist after the LOAD

I just worry that this is one "on-line" utility too far (mind you, I think
the same about the V8 On-Line LOAD with DISCARD!!!)

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Larry Jardine

Re: RENAMES for availability
(in response to Michael Ebert)

I think you can limit your rebuild to the logical partition...



| Larry Jardine | Production DBA | Aetna |


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Michael Ebert
Sent: Thursday, January 15, 2004 10:49 AM
To: [login to unmask email]
Subject: Re: RENAMES for availability




One question that just occurred to me (I just did a partition-level
RECOVER TOCOPY of a partitioned TS, which now forces me to REBUILD all
ten 100M row NPIs) is, how is the BUILD (BUILD2) phase for NPIs handled?


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



Very good explanation Dr. Ebert. If someone is worried about losing
updates to a tablespace, they will probably not be doing a LOAD REPLACE.
I like to point out that most DBA's want their LOAD jobs to run as fast
as possible to reduce the outage. I usually ask the question "If you
could have a feature that would cut each of your LOAD REPLACE jobs
elapsed time in half, would you want it?". Invariably the answer is
"yes!". Follow that with "If you could have a feature that would make
all your LOAD REPLACE jobs run in 1/10th the elapsed time, would you
want it?". Again, who could say "no"? Finally, "If all your LOAD
REPLACE jobs could run in only a few seconds, would you want that?".

That is LOAD REPLACE SHRLEVEL CHANGE. It essentially compresses the
elapsed time of the replace operation down to the time it takes to
rename the objects in question. Why spend two hours with DB2 objects
unavailable when you could perform the same function with only a 5
second outage? Granted, it is best suited for read-only data, but if
you are replacing data that is being updated, why worry about the last
update just before a 5 second outage when you don't worry about the last
update just before a two hour outage?

Randy Bright
Architect, DB2 Utilities
BMC Software, Inc.
-----Original Message-----
From: Michael Ebert [mailto:[login to unmask email]
Sent: Thursday, January 15, 2004 4:38 AM
To: [login to unmask email]
Subject: Re: RENAMES for availability


No, it does make sense. It simply means that, instead of having the
table unavailable while it's being loaded for possibly dozens of
minutes, it's not unavailable at all; and you don't have to worry about
accesses failing because it's in a UT restricted state. From the
application side, it simply looks like a nearly instant LOAD REPLACE.
The objections you raise apply to all LOAD REPLACEs.

One thing I'd dearly like to see is having incompatible utilities
serialise instead of failing... then I could automate all housekeeping
maintenance.

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



Hmmm

My thoughts on whether this even makes sense still apply

If you LOAD REPLACE data whilst it is being processed, from an
application point of view, how do you

- Cope with data that has been read, processed in other tables and then
no longer exists after the LOAD
- Cope with rows that have been updated/deleted from the original table
and are then replaced by the LOAD
- Handle rows that are inserted whilst the load is running but no longer
exist after the LOAD

I just worry that this is one "on-line" utility too far (mind you, I
think the same about the V8 On-Line LOAD with DISCARD!!!)


Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]

------------------------------------------------------------------------
--------- 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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




This e-mail may contain confidential or privileged information. If you
think you have received this e-mail in error, please advise the sender by
reply e-mail and then delete this e-mail immediately. Thank you. Aetna

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Randy Bright

Re: RENAMES for availability
(in response to Michael Ebert)
LoadPlus for DB2 only supports LOAD REPLACE. I think you are referring to
LOAD RESUME YES INTO PART n REPLACE. This is not (yet) supported by our
product. We build the entire replaced tablespace and all indexes into
shadows and then perform a RENAME. You can REPLACE a partitioned
tablespace, but you have to replace the entire tablespace at this time.

Randy Bright
Architect, DB2 Utilities
BMC Software, Inc.

-----Original Message-----
From: Michael Ebert [mailto:[login to unmask email]
Sent: Thursday, January 15, 2004 9:49 AM
To: [login to unmask email]
Subject: Re: RENAMES for availability



One question that just occurred to me (I just did a partition-level RECOVER
TOCOPY of a partitioned TS, which now forces me to REBUILD all ten 100M row
NPIs) is, how is the BUILD (BUILD2) phase for NPIs handled?

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



Very good explanation Dr. Ebert. If someone is worried about losing updates
to a tablespace, they will probably not be doing a LOAD REPLACE. I like to
point out that most DBA's want their LOAD jobs to run as fast as possible to
reduce the outage. I usually ask the question "If you could have a feature
that would cut each of your LOAD REPLACE jobs elapsed time in half, would
you want it?". Invariably the answer is "yes!". Follow that with "If you
could have a feature that would make all your LOAD REPLACE jobs run in
1/10th the elapsed time, would you want it?". Again, who could say "no"?
Finally, "If all your LOAD REPLACE jobs could run in only a few seconds,
would you want that?".

That is LOAD REPLACE SHRLEVEL CHANGE. It essentially compresses the elapsed
time of the replace operation down to the time it takes to rename the
objects in question. Why spend two hours with DB2 objects unavailable when
you could perform the same function with only a 5 second outage? Granted,
it is best suited for read-only data, but if you are replacing data that is
being updated, why worry about the last update just before a 5 second outage
when you don't worry about the last update just before a two hour outage?

Randy Bright
Architect, DB2 Utilities
BMC Software, Inc.
-----Original Message-----
From: Michael Ebert [mailto:[login to unmask email]
Sent: Thursday, January 15, 2004 4:38 AM
To: [login to unmask email]
Subject: Re: RENAMES for availability


No, it does make sense. It simply means that, instead of having the table
unavailable while it's being loaded for possibly dozens of minutes, it's not
unavailable at all; and you don't have to worry about accesses failing
because it's in a UT restricted state. From the application side, it simply
looks like a nearly instant LOAD REPLACE. The objections you raise apply to
all LOAD REPLACEs.

One thing I'd dearly like to see is having incompatible utilities serialise
instead of failing... then I could automate all housekeeping maintenance.

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



Hmmm

My thoughts on whether this even makes sense still apply

If you LOAD REPLACE data whilst it is being processed, from an application
point of view, how do you

- Cope with data that has been read, processed in other tables and then no
longer exists after the LOAD
- Cope with rows that have been updated/deleted from the original table and
are then replaced by the LOAD
- Handle rows that are inserted whilst the load is running but no longer
exist after the LOAD

I just worry that this is one "on-line" utility too far (mind you, I think
the same about the V8 On-Line LOAD with DISCARD!!!)


Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]

----------------------------------------------------------------------------
----- 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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: RENAMES for availability
(in response to Larry Jardine)
Right... I briefly wondered whether that was possible, then decided to
check later and use an existing job. Rebuilding the entire NPI effectively
reorganises it as well and allows me to use inline Stats. Also, seeing how
long the BUILD2 phase normally takes, I probably wouldn't save much
wallclock time... maybe I should do some tests...

MfG, ME.




I think you can limit your rebuild to the logical partition?

| Larry Jardine | Production DBA | Aetna |
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Michael Ebert
Sent: Thursday, January 15, 2004 10:49 AM
To: [login to unmask email]
Subject: Re: RENAMES for availability


One question that just occurred to me (I just did a partition-level
RECOVER TOCOPY of a partitioned TS, which now forces me to REBUILD all ten
100M row NPIs) is, how is the BUILD (BUILD2) phase for NPIs handled?

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



Very good explanation Dr. Ebert. If someone is worried about losing
updates to a tablespace, they will probably not be doing a LOAD REPLACE. I
like to point out that most DBA's want their LOAD jobs to run as fast as
possible to reduce the outage. I usually ask the question "If you could
have a feature that would cut each of your LOAD REPLACE jobs elapsed time
in half, would you want it?". Invariably the answer is "yes!". Follow
that with "If you could have a feature that would make all your LOAD
REPLACE jobs run in 1/10th the elapsed time, would you want it?". Again,
who could say "no"? Finally, "If all your LOAD REPLACE jobs could run in
only a few seconds, would you want that?".

That is LOAD REPLACE SHRLEVEL CHANGE. It essentially compresses the
elapsed time of the replace operation down to the time it takes to rename
the objects in question. Why spend two hours with DB2 objects unavailable
when you could perform the same function with only a 5 second outage?
Granted, it is best suited for read-only data, but if you are replacing
data that is being updated, why worry about the last update just before a
5 second outage when you don't worry about the last update just before a
two hour outage?

Randy Bright
Architect, DB2 Utilities
BMC Software, Inc.
-----Original Message-----
From: Michael Ebert [mailto:[login to unmask email]
Sent: Thursday, January 15, 2004 4:38 AM
To: [login to unmask email]
Subject: Re: RENAMES for availability


No, it does make sense. It simply means that, instead of having the table
unavailable while it's being loaded for possibly dozens of minutes, it's
not unavailable at all; and you don't have to worry about accesses failing
because it's in a UT restricted state. From the application side, it
simply looks like a nearly instant LOAD REPLACE. The objections you raise
apply to all LOAD REPLACEs.

One thing I'd dearly like to see is having incompatible utilities
serialise instead of failing... then I could automate all housekeeping
maintenance.

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



Hmmm

My thoughts on whether this even makes sense still apply

If you LOAD REPLACE data whilst it is being processed, from an application
point of view, how do you

- Cope with data that has been read, processed in other tables and then no
longer exists after the LOAD
- Cope with rows that have been updated/deleted from the original table
and are then replaced by the LOAD
- Handle rows that are inserted whilst the load is running but no longer
exist after the LOAD

I just worry that this is one "on-line" utility too far (mind you, I think
the same about the V8 On-Line LOAD with DISCARD!!!)

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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



This e-mail may contain confidential or privileged information. If you
think you have received this e-mail in error, please advise the sender by
reply e-mail and then delete this e-mail immediately. Thank you. Aetna

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Isaac Yassin

Re: RENAMES for availability
(in response to Randy Bright)
Hi,

I take index copy as well against things like that.

Helped me many times in the last few months.

Isaac Yassin

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of
Michael Ebert
Sent: Thursday, January 15, 2004 5:49 PM
To: [login to unmask email]
Subject: Re: RENAMES for availability




One question that just occurred to me (I just did a partition-level RECOVER
TOCOPY of a partitioned TS, which now forces me to REBUILD all ten 100M row
NPIs) is, how is the BUILD (BUILD2) phase for NPIs handled?

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


---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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: RENAMES for availability
(in response to Isaac Yassin)
I meant LOAD INTO TABLE ... PART nn REPLACE (...) - a LOAD REPLACE of a
single partition. The BUILD2 phase for this operation has to scan the
entire NPI(s) in order to remove old RIDs and merge in the new ones, so it
takes a loooong time (often much longer than for a partition-level REORG.
Why? The REORG has the full set of keys to update, so it can use
skip-sequential processing. The LOAD REPLACE only has the new set - but
doesn't know what the old keys/RIDs were that it has to delete - so it has
to look at every RID).
The situation for a partition-level PIT recovery is the same as for a
part-level LOAD REPLACE...
I just discovered that if you cancel a REBUILD of a logical partition in
the UNLOAD phase, the LP is left in RW,RBDP, RBDP* status... even though
the NPI wasn't changed yet... another bug. Oh well...

MfG, ME.



LoadPlus for DB2 only supports LOAD REPLACE. I think you are referring to
LOAD RESUME YES INTO PART n REPLACE. This is not (yet) supported by our
product. We build the entire replaced tablespace and all indexes into
shadows and then perform a RENAME. You can REPLACE a partitioned
tablespace, but you have to replace the entire tablespace at this time.

Randy Bright
Architect, DB2 Utilities
BMC Software, Inc.


---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Kenneth Kane

Re: RENAMES for availability
(in response to Michael Ebert)
I have created a monster.. thanks to all who responded, I have alot of
possibilities, The BMC Online Load being the most intriguing.
Ken Kane UBS
-----Original Message-----
From: Jardine, Lawrence J [mailto:[login to unmask email]
Sent: Thursday, January 15, 2004 10:58 AM
To: [login to unmask email]
Subject: Re: RENAMES for availability



I think you can limit your rebuild to the logical partition...



| Larry Jardine | Production DBA | Aetna |

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Michael Ebert
Sent: Thursday, January 15, 2004 10:49 AM
To: [login to unmask email]
Subject: Re: RENAMES for availability




One question that just occurred to me (I just did a partition-level RECOVER
TOCOPY of a partitioned TS, which now forces me to REBUILD all ten 100M row
NPIs) is, how is the BUILD (BUILD2) phase for NPIs handled?

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



Very good explanation Dr. Ebert. If someone is worried about losing updates
to a tablespace, they will probably not be doing a LOAD REPLACE. I like to
point out that most DBA's want their LOAD jobs to run as fast as possible to
reduce the outage. I usually ask the question "If you could have a feature
that would cut each of your LOAD REPLACE jobs elapsed time in half, would
you want it?". Invariably the answer is "yes!". Follow that with "If you
could have a feature that would make all your LOAD REPLACE jobs run in
1/10th the elapsed time, would you want it?". Again, who could say "no"?
Finally, "If all your LOAD REPLACE jobs could run in only a few seconds,
would you want that?".

That is LOAD REPLACE SHRLEVEL CHANGE. It essentially compresses the elapsed
time of the replace operation down to the time it takes to rename the
objects in question. Why spend two hours with DB2 objects unavailable when
you could perform the same function with only a 5 second outage? Granted,
it is best suited for read-only data, but if you are replacing data that is
being updated, why worry about the last update just before a 5 second outage
when you don't worry about the last update just before a two hour outage?

Randy Bright
Architect, DB2 Utilities
BMC Software, Inc.
-----Original Message-----
From: Michael Ebert [mailto:[login to unmask email]
Sent: Thursday, January 15, 2004 4:38 AM
To: [login to unmask email]
Subject: Re: RENAMES for availability


No, it does make sense. It simply means that, instead of having the table
unavailable while it's being loaded for possibly dozens of minutes, it's not
unavailable at all; and you don't have to worry about accesses failing
because it's in a UT restricted state. From the application side, it simply
looks like a nearly instant LOAD REPLACE. The objections you raise apply to
all LOAD REPLACEs.

One thing I'd dearly like to see is having incompatible utilities serialise
instead of failing... then I could automate all housekeeping maintenance.

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



Hmmm

My thoughts on whether this even makes sense still apply

If you LOAD REPLACE data whilst it is being processed, from an application
point of view, how do you

- Cope with data that has been read, processed in other tables and then no
longer exists after the LOAD
- Cope with rows that have been updated/deleted from the original table and
are then replaced by the LOAD
- Handle rows that are inserted whilst the load is running but no longer
exist after the LOAD

I just worry that this is one "on-line" utility too far (mind you, I think
the same about the V8 On-Line LOAD with DISCARD!!!)


Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]

----------------------------------------------------------------------------
----- 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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




This e-mail may contain confidential or privileged information. If you

think you have received this e-mail in error, please advise the sender by

reply e-mail and then delete this e-mail immediately. Thank you. Aetna


----------------------------------------------------------------------------
----- 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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


Please do not transmit orders or instructions regarding a UBS account by
email. The information provided in this email or any attachments is not an
official transaction confirmation or account statement. For your protection,
do not include account numbers, Social Security numbers, credit card
numbers, passwords or other non-public information in your email. Because
the information contained in this message may be privileged, confidential,
proprietary or otherwise protected from disclosure, please notify us
immediately by replying to this message and deleting it from your computer
if you have received this communication in error. Thank you.

UBS Financial Services Inc.
UBS International Inc.


---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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