DB2 z/OS - DB2 Compression & DB2 DPropR Risk of Losing Data

Michael N M'Cartney

DB2 z/OS - DB2 Compression & DB2 DPropR Risk of Losing Data
All,
We have an issue/problem with IBM DataPropagator. We need to compress
some tables in DB2 z/OS(OS/390). Changes to these tables are propagated
to another application using CCD tables as targets. We are using DPropR
V8.1 but this issue/problem also applies to subsequent versions. The
issue/problem is that we will lose data if a reorg is accidentally run
without using the "KEEPDICTIONARY" option when DPropR is behind and
there are changes to the table in the log which require the old
dictionary.

What I am interested in is how many other sites use DPropR to propagate
to CCD tables (rather than replica tables) and have either already
implemented compression or would benefit from implementing compression
but cannot take the risk in these circumstances.

Where compression has already been implemented in these circumstances,
I'd be interested in knowing how the risk of data-loss is managed or
whether it is simply an accepted risk.

A detailed description of the problem follows (I have assumed no prior
knowledge so I apologise to those of you familiar with compression
and/or DPropR):

HOW IT WORKS
* Each compressed tablespace has a compression dictionary built
specifically for the data in that tablespace. DB2 log records for
updates to compressed tablespaces are also compressed using this same
dictionary.
> * Data Propagator, reads the DB2 logs and decompresses them using
> the compression dictionary stored in that tablespace.
> * When reorganising a tablespace the optional parameter
> "KEEPDICTIONARY" ensures that the existing dictionary is retained.
> Therefore data and DB2 log records for that tablespace continue being
> compressed/decompressed using the same dictionary.
> * Under normal circumstances we code "KEEPDICTIONARY" in all our
> reorganisation jobs but it is not the IBM default. The default is to
> build a completely new dictionary which overwrites the existing
> dictionary. As a consequence, after a reorganisation without
> KEEPDICTIONARY, it is and would no longer be possible to
> read/decompress old DB2 log records for that tablespace.
THE PROBLEM
> * With Data Propagator V8, IBM changed the way that Data
> propagator handles a situation where it has old log records that it
> needs to decompress but the dictionary is no longer available.
> Under V7, DPropR simply failed immediately, with the result that it
> was possible to restore the old dictionary enabling decompression of
> the old log records. Capture would then fail when it reached log
> records that required the later dictionary. At that point you would
> restore back to the point before the first restore, start CAPTURE and
> continue as normal. The only consequence being downtime, missed SLAs
> and possibly some embarrassment.
> Under V8, DPropR simply issues a message, ignores the problem, stops
> propagating changes for the affected tablespace and continues with
> everything else. There is no going back. From this point onward, all
> the logged delta changes will be ignored and will be irretrievably
> lost to DpropR. This will continue until APPLY signals that a full
> refresh has been performed or it is done manually and CAPTURE begins
> capturing for that table once again.
> This would not be such a big a problem if we were using DPropR for
> replication because we, or DPropR, would simply refresh all the data
> from the source table rather than using the logs to maintain the
> replica. The most serious consequence would be possible downtime (if
> the table is very big). However, as we use DPropR to propagate deltas
> (before and after images) for another application and auditing there
> is nothing to 'refresh' and we therefore cannot afford to lose the
> deltas.
>
> Thanks,
>
> Mike M'Cartney,
> ORGANISATION: DB2 OS/390 Production DBA Team, Database Design &
> Support, Infrastructure Support, SMC Support, ITO - EMEA, EDS.
> TEL: 01253 688222 (external) 88222 (internal)
> EMAIL: [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

Tim R - CNF Ohling

Re: DB2 z/OS - DB2 Compression & DB2 DPropR Risk of Losing Data
(in response to Michael N M'Cartney)
We have a very similar situation in that Dprop is real-time and we
cannot refresh without damaging the target tables.

However, we have the Reorg process fully automated on the z/OS side so:
All generated reorgs use KEEPDICTIONARY
Reorgs run during a maintenance window where very few changes are
being made on the source tables, and we try to dry up replication before
the reorg jobs run
We don't do manual reorgs often if at all

We still have had a couple of problems where we manually identified any
changes that took place inside the window that Dprop couldn't capture
and (going outside Dprop) manually refreshed just those rows while
skipping the bad window for the table in question.

I'd love to see Dprop V8 become "image copy aware" like some of the
other products out there (log analyzers?) that pull in an old
compression dictionary from prior to a reorg so they may burp for a
while but they won't stop working (especially without telling you).

Overall, we've managed the risk so it's low enough to not be a
consideration when we compress a source table. But we're nervous about
the issues with Dprop administration anyway.

HTH,
Tim

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of M'Cartney, Michael N
Sent: Thursday, September 15, 2005 9:45 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS - DB2 Compression & DB2 DPropR Risk of Losing
Data



All,
We have an issue/problem with IBM DataPropagator. We need to compress
some tables in DB2 z/OS(OS/390). Changes to these tables are propagated
to another application using CCD tables as targets. We are using DPropR
V8.1 but this issue/problem also applies to subsequent versions. The
issue/problem is that we will lose data if a reorg is accidentally run
without using the "KEEPDICTIONARY" option when DPropR is behind and
there are changes to the table in the log which require the old
dictionary.

What I am interested in is how many other sites use DPropR to propagate
to CCD tables (rather than replica tables) and have either already
implemented compression or would benefit from implementing compression
but cannot take the risk in these circumstances.

Where compression has already been implemented in these circumstances,
I'd be interested in knowing how the risk of data-loss is managed or
whether it is simply an accepted risk.

A detailed description of the problem follows (I have assumed no prior
knowledge so I apologise to those of you familiar with compression
and/or DPropR):

HOW IT WORKS

* Each compressed tablespace has a compression dictionary built
specifically for the data in that tablespace. DB2 log records for
updates to compressed tablespaces are also compressed using this same
dictionary.
* Data Propagator, reads the DB2 logs and decompresses them using
the compression dictionary stored in that tablespace.
* When reorganising a tablespace the optional parameter
"KEEPDICTIONARY" ensures that the existing dictionary is retained.
Therefore data and DB2 log records for that tablespace continue being
compressed/decompressed using the same dictionary.
* Under normal circumstances we code "KEEPDICTIONARY" in all our
reorganisation jobs but it is not the IBM default. The default is to
build a completely new dictionary which overwrites the existing
dictionary. As a consequence, after a reorganisation without
KEEPDICTIONARY, it is and would no longer be possible to read/decompress
old DB2 log records for that tablespace.

THE PROBLEM

* With Data Propagator V8, IBM changed the way that Data
propagator handles a situation where it has old log records that it
needs to decompress but the dictionary is no longer available.
Under V7, DPropR simply failed immediately, with the result that
it was possible to restore the old dictionary enabling decompression of
the old log records. Capture would then fail when it reached log records
that required the later dictionary. At that point you would restore back
to the point before the first restore, start CAPTURE and continue as
normal. The only consequence being downtime, missed SLAs and possibly
some embarrassment.
Under V8, DPropR simply issues a message, ignores the problem,
stops propagating changes for the affected tablespace and continues with
everything else. There is no going back. From this point onward, all the
logged delta changes will be ignored and will be irretrievably lost to
DpropR. This will continue until APPLY signals that a full refresh has
been performed or it is done manually and CAPTURE begins capturing for
that table once again.

This would not be such a big a problem if we were using DPropR for
replication because we, or DPropR, would simply refresh all the data
from the source table rather than using the logs to maintain the
replica. The most serious consequence would be possible downtime (if the
table is very big). However, as we use DPropR to propagate deltas
(before and after images) for another application and auditing there is
nothing to 'refresh' and we therefore cannot afford to lose the deltas.

Thanks,

Mike M'Cartney,
ORGANISATION: DB2 OS/390 Production DBA Team, Database Design & Support,
Infrastructure Support, SMC Support, ITO - EMEA, EDS.

TEL: 01253 688222 (external) 88222 (internal)
EMAIL: [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 N M'Cartney

Re: DB2 z/OS - DB2 Compression & DB2 DPropR Risk of Losing Data
(in response to Tim R - CNF Ohling)
Tim,
Our regular reorgs are all automated as well and always have
KEEPDICTIONARY coded as well as being the default in our automation.
When we do manual reorgs we should also always code KEEPDICTIONARY. The
problem is that there is no way to guarantee KEEPDICTIONARY is coded,
the IBM utilities don't allow us to change the default and owing to the
way DPropR works, we can't afford to make a mistake.

Also, by the way, just in case anyone thinks STOP_ON_ERROR='Y' will do
the trick, it won't, it clearly states that in the manual and the IBM
developers have confirmed it. We are now hopeful that IBM will provide
us with a satisfactory solution, but I wonder how many other z/OS DB2
DPropR sites actually need a solution and perhaps have a problem they
didn't realise existed.




Thanks,

Mike M'Cartney,



---------------------------------------------------------------------------------
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

Barbara Nigh

DB2 Compression
(in response to Michael N M'Cartney)
If a compression dictionary has already been built on a tablespace, do rows
inserted into a table within the tablespace via a COBOL program
or LOAD SHRLEVEL CHANGE get compressed?


Barbara J. Nigh - CAL261
Consultant, Database Management Systems - North America
Phone: 831.754.1400 Ext. 2048
Pager: 877.406.7868
[login to unmask email]


-----------------------------------------
***********************************************************************
This E-mail is confidential. It may also be legally privileged. If you
are not the addressee you may not copy, forward, disclose or use any
part of it. If you have received this message in error, please delete
it and all copies from your system and notify the sender immediately
by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
***********************************************************************

---------------------------------------------------------------------------------
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

Philip Sevetson

Re: DB2 Compression
(in response to Barbara Nigh)
Barbara,

I assume you're talking z/OS, and my answers are probably particular to that
environment.

1) Once the compression dictionary has been created, all rows inserted
via DB2 facilities (i.e. DB2 SQL, or a utility process such as LOAD
RESUME) will be tried for compression against the compression dictionary,
and compressed appropriately if match strings are found.

2) No match with the strings in the dictionary = no compression, so:

2a) if you create the compression dictionary with LOAD/REPLACE, and you've
got two or more tables in the tablespace with heterogeneous data, you're
likely to get a poor compression ratio on any tables after the first one
LOADED.

2b) If you REORG a tablespace after multiple tables have been LOADed, you'll
usually get a better compression dictionary than you would with the
LOAD/REPLACE being applied to the first table.

2c) Even a one-table tablespace, with data that changes significantly over
time, will need a REORG or similar to rebuild the compression dictionary to
make a good fit.

Hope this all helps.

--Phil Sevetson

On 12/6/05, Barbara Jo Nigh <[login to unmask email]> wrote:
>
> If a compression dictionary has already been built on a tablespace, do
> rows
> inserted into a table within the tablespace via a COBOL program
> or LOAD SHRLEVEL CHANGE get compressed?
>
>
> Barbara J. Nigh - CAL261
> Consultant, Database Management Systems - North America
> Phone: 831.754.1400 Ext. 2048
> Pager: 877.406.7868
> [login to unmask email]
>
> --
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
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

Ray Janes

Re: DB2 Compression
(in response to Philip Sevetson)
Note: leave off the Keep Dictionary on the reorg, to build a new
dictionary.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Philip Sevetson
Sent: Tuesday, December 06, 2005 1:44 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Compression


Barbara,

I assume you're talking z/OS, and my answers are probably particular to
that environment.

1) Once the compression dictionary has been created, all rows inserted
via DB2 facilities (i.e. DB2 SQL, or a utility process such as LOAD
RESUME) will be tried for compression against the compression
dictionary, and compressed appropriately if match strings are found.

2) No match with the strings in the dictionary = no compression, so:

2a) if you create the compression dictionary with LOAD/REPLACE, and
you've got two or more tables in the tablespace with heterogeneous data,
you're likely to get a poor compression ratio on any tables after the
first one LOADED.

2b) If you REORG a tablespace after multiple tables have been LOADed,
you'll usually get a better compression dictionary than you would with
the LOAD/REPLACE being applied to the first table.

2c) Even a one-table tablespace, with data that changes significantly
over time, will need a REORG or similar to rebuild the compression
dictionary to make a good fit.

Hope this all helps.

--Phil Sevetson

On 12/6/05, Barbara Jo Nigh <[login to unmask email]> wrote:

If a compression dictionary has already been built on a tablespace, do
rows
inserted into a table within the tablespace via a COBOL program
or LOAD SHRLEVEL CHANGE get compressed?


Barbara J. Nigh - CAL261
Consultant, Database Management Systems - North America
Phone: 831.754.1400 Ext. 2048
Pager: 877.406.7868
[login to unmask email]



--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for
zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.
------------------------------------------------------------------------
--------- 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

Barbara Nigh

Re: DB2 Compression
(in response to Ray Janes)
I am talking about a partitioned tablespace which only contains one table.
Sorry--I should have made that clear.

Barbara J. Nigh - CAL261
Consultant, Database Management Systems - North America
Phone: 831.754.1400 Ext. 2048
Pager: 877.406.7868
[login to unmask email]



Philip Sevetson
<[login to unmask email] To: [login to unmask email]
OM> cc:
Sent by: DB2 Data Subject: Re: [DB2-L] DB2 Compression
Base Discussion List
<[login to unmask email]>


12/06/2005 11:44 AM
Please respond to DB2
Database Discussion
list at IDUG







Barbara,

I assume you're talking z/OS, and my answers are probably particular to
that environment.

1)  Once the compression dictionary has been created, all rows inserted
via DB2 facilities (i.e. DB2 SQL, or a utility process such as LOAD
RESUME) will be tried for compression against the compression dictionary,
and compressed appropriately if match strings are found.

2)  No match with the strings in the dictionary = no compression, so:

2a) if you create the compression dictionary with LOAD/REPLACE, and you've
got two or more tables in the tablespace with heterogeneous data, you're
likely to get a poor compression ratio on any tables after the first one
LOADED.

2b) If you REORG a tablespace after multiple tables have been LOADed,
you'll usually get a better compression dictionary than you would with the
LOAD/REPLACE being applied to the first table.

2c) Even a one-table tablespace, with data that changes significantly over
time, will need a REORG or similar to rebuild the compression dictionary to
make a good fit.

Hope this all helps.

--Phil Sevetson

On 12/6/05, Barbara Jo Nigh <[login to unmask email]> wrote:If a
compression dictionary has already been built on a tablespace, do rows
inserted into a table within the tablespace via a COBOL program
or LOAD SHRLEVEL CHANGE get compressed?


Barbara J. Nigh - CAL261
Consultant, Database Management Systems - North America
Phone: 831.754.1400 Ext. 2048
Pager: 877.406.7868
[login to unmask email]

--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for
zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.
---------------------------------------------------------------------------------
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 message originated from the Internet. Its originator may or
may not be who they claim to be and the information contained in
the message and any attachments may or may not be accurate.
******************************************************************




-----------------------------------------
*******************************************************************
****
This E-mail is confidential. It may also be legally privileged. If
you
are not the addressee you may not copy, forward, disclose or use
any
part of it. If you have received this message in error, please
delete
it and all copies from your system and notify the sender
immediately
by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
*******************************************************************
****


---------------------------------------------------------------------------------
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: DB2 Compression
(in response to Barbara Nigh)
All rows inserted into a tablespace with a compression dictionary will be
compressed (the same for updated rows), if the compressed row is shorter
than the uncompressed one. Rows loaded via the LOAD RESUME utility are
also compressed; I've no doubt this is also true if you use LOAD RESUME
SHRLEVEL CHANGE (which is just a bulk INSERT).

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








Barbara Jo Nigh <[login to unmask email]>
Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
06-12-05 20:26

To
[login to unmask email]
cc



Subject
[DB2-L] DB2 Compression






If a compression dictionary has already been built on a tablespace, do
rows
inserted into a table within the tablespace via a COBOL program
or LOAD SHRLEVEL CHANGE get compressed?


Barbara J. Nigh - CAL261
Consultant, Database Management Systems - North America
Phone: 831.754.1400 Ext. 2048
Pager: 877.406.7868
[login to unmask email]


-----------------------------------------
***********************************************************************
This E-mail is confidential. It may also be legally privileged. If you
are not the addressee you may not copy, forward, disclose or use any
part of it. If you have received this message in error, please delete
it and all copies from your system and notify the sender immediately
by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
***********************************************************************

---------------------------------------------------------------------------------
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

Richard Fazio

Re: DB2 Compression
(in response to Michael Ebert)
LOAD SHRLEVEL CHANGE also compresses rows.

faz

Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn


>>> [login to unmask email] 2005-12-06 2:00:24 PM >>>


All rows inserted into a tablespace with a compression dictionary will
be compressed (the same for updated rows), if the compressed row is
shorter than the uncompressed one. Rows loaded via the LOAD RESUME
utility are also compressed; I've no doubt this is also true if you use
LOAD RESUME SHRLEVEL CHANGE (which is just a bulk INSERT).

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







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

Sent by: DB2 Data Base Discussion List <[login to unmask email]>
06-12-05 20:26 To
[login to unmask email] cc

Subject
[DB2-L] DB2 Compression




If a compression dictionary has already been built on a tablespace, do
rows
inserted into a table within the tablespace via a COBOL program
or LOAD SHRLEVEL CHANGE get compressed?


Barbara J. Nigh - CAL261
Consultant, Database Management Systems - North America
Phone: 831.754.1400 Ext. 2048
Pager: 877.406.7868
[login to unmask email]


-----------------------------------------
***********************************************************************
This E-mail is confidential. It may also be legally privileged. If you
are not the addressee you may not copy, forward, disclose or use any
part of it. If you have received this message in error, please delete
it and all copies from your system and notify the sender immediately
by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
***********************************************************************

---------------------------------------------------------------------------------
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

Richard Fazio

Re: DB2 Compression
(in response to Richard Fazio)
LOAD SHRLEVEL CHANGE also compresses rows.

faz

Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn


>>> [login to unmask email] 2005-12-06 2:00:24 PM >>>


All rows inserted into a tablespace with a compression dictionary will
be compressed (the same for updated rows), if the compressed row is
shorter than the uncompressed one. Rows loaded via the LOAD RESUME
utility are also compressed; I've no doubt this is also true if you use
LOAD RESUME SHRLEVEL CHANGE (which is just a bulk INSERT).

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







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

Sent by: DB2 Data Base Discussion List <[login to unmask email]>
06-12-05 20:26 To
[login to unmask email] cc

Subject
[DB2-L] DB2 Compression




If a compression dictionary has already been built on a tablespace, do
rows
inserted into a table within the tablespace via a COBOL program
or LOAD SHRLEVEL CHANGE get compressed?


Barbara J. Nigh - CAL261
Consultant, Database Management Systems - North America
Phone: 831.754.1400 Ext. 2048
Pager: 877.406.7868
[login to unmask email]


-----------------------------------------
***********************************************************************
This E-mail is confidential. It may also be legally privileged. If you
are not the addressee you may not copy, forward, disclose or use any
part of it. If you have received this message in error, please delete
it and all copies from your system and notify the sender immediately
by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
***********************************************************************

---------------------------------------------------------------------------------
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

---------------------------------------------------------------------------------
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

Walter Jani&#223;en

Re: DB2 Compression
(in response to Richard Fazio)
There is a BUT. Because you are talking about partitioned tablespace. Each
partition has its own compression dictionary and if there are empty
partitions and you do a LOAD RESUME YES for the entire tablespace and
records get loaded into these empty partitions, then a compression
dictionary will not be built.

---------------------------------------------------------------------------------
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

Philip Sevetson

Re: DB2 Compression
(in response to Walter Janißen)
...and, an ATTABOY!!!!! to Walter for the best discovery of a hidden
"Gotcha" in my advice. Thanks, Walter, I'd known that but wasn't thinking
of it when offering my advice to Barbara.

--Phil, sheepishly


On 12/7/05, Walter Janißen <[login to unmask email]> wrote:
>
> There is a BUT. Because you are talking about partitioned tablespace. Each
> partition has its own compression dictionary and if there are empty
> partitions and you do a LOAD RESUME YES for the entire tablespace and
> records get loaded into these empty partitions, then a compression
> dictionary will not be built.
>
>
--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
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