detach and attach partition in db2 z/os

Sara Ahmadi

detach and attach partition in db2 z/os

Hello

We have a large table that its size grows fast and reaches the maximum size of large table spaces (64G), the application refers only to the last 6 month of data but we need to preserve the old data. We have created a partition by growth table as an archive table and we load old data to this table and delete those from the base table to keep the base table space size below the maximum, Is there any other solution for this problem? We haven’t defined the base table space as partition by growth because we want to have less expensive queries and we want to spend less time on administrative tasks such as copy, recovery, … We can detach a partition and attach it to an archive table in DB2 LUW is there any similar option in DB2 Z/OS?

Thanks

Gautam Sahu

detach and attach partition in db2 z/os
(in response to Sara Ahmadi)
Hi Sara,

Have you considered using archive-enabled tables?

Regards,
Gautam

On Mon, 29 Jul 2019 at 12:19 PM, Sara Ahmadi <[login to unmask email]> wrote:

> Hello
>
> We have a large table that its size grows fast and reaches the maximum
> size of large table spaces (64G), the application refers only to the last 6
> month of data but we need to preserve the old data. We have created a
> partition by growth table as an archive table and we load old data to this
> table and delete those from the base table to keep the base table space
> size below the maximum, Is there any other solution for this problem? We
> haven’t defined the base table space as partition by growth because we want
> to have less expensive queries and we want to spend less time on
> administrative tasks such as copy, recovery, … We can detach a partition
> and attach it to an archive table in DB2 LUW is there any similar option in
> DB2 Z/OS?
>
> Thanks
>
> -----End Original Message-----
>
--
Thanks,
Gautam

Sara Ahmadi

RE: detach and attach partition in db2 z/os
(in response to Gautam Sahu)

We have DB2 10 and as I know this is a new feature in DB2 11

Edited By:
Sara Ahmadi[Organization Members] @ Jul 29, 2019 - 06:04 AM (America/Eastern)

Philip Sevetson

detach and attach partition in db2 z/os
(in response to Sara Ahmadi)
Sara,


· As of V10 the maximum table size is 128TB.

· The documentation for CREATE TABLESPACE in DB2 V10 for z/OS is at: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_sql_createtablespace.html

· Create your table as partitioned by range, using the relevant DATE column as partitioning key.

· Specify DSSIZE 32GB or DSSIZE 64GB

· To use DSSIZE larger than 4GB, you’ll have to use extended addressability on your DASD. Check with your systems programmer/s about whether that’s enabled for your DASD pool.

· Set up your partitions for three or four months in size, to allow for application growth.

· Have enough DASD in your storage group to support the amount of data which you’re going to keep!

· Read up on the relationship between DSSIZE, Buffer Pool Size, and number of partitions (in the above link, subject “MAXPARTITIONS”).

· Before going live, fill up a partition with data, and test access to the higher-numbered pages. If extended addressability isn’t set up right, you won’t be able to read pages above a 4-gigabyte RBA value (location in the dataset).


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Sara Ahmadi <[login to unmask email]>
Sent: Monday, July 29, 2019 6:04 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: detach and attach partition in db2 z/os


We have DB2 10 and as I know this is a new feature in db2 11

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Philip Sevetson

detach and attach partition in db2 z/os
(in response to Philip Sevetson)
Additional information –
An in depth tabular presentation of your options for partition size is present at: https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_sql_createtablespace.html#db2z_sql_createtablespace__dsspg (This is known as “Table 5” in the manual.)

Depending on how much total size (Terabytes) you need to hold your data, you may wish to consider using larger page sizes (BP8K[0-9], BP16K[0-9], BP32K[0-9]).

Use of 4K BPs will allow you a maximum size of 16TB (DSSIZE 32GB/512 partitions, or DSSIZE 64GB/256 partitions);
Use of 8K BPs will allow 32TB (DSSIZE 32GB/1024 partitions, or DSSIZE 64GB/512 partitions);
Use of 16K BPs will allow 64TB (DSSIZE 32GB/2048 partitions, or DSSIZE 64GB/1024 partitions);
Use of 32K BPs will allow 128TB (DSSIZE 32GB/4096 partitions, or DSSIZE 64GB/2048 partitions)

-phil (sevetson)


From: Sevetson, Phil <[login to unmask email]>
Sent: Monday, July 29, 2019 9:18 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: detach and attach partition in db2 z/os

Sara,


· As of V10 the maximum table size is 128TB.

· The documentation for CREATE TABLESPACE in DB2 V10 for z/OS is at: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_sql_createtablespace.html

· Create your table as partitioned by range, using the relevant DATE column as partitioning key.

· Specify DSSIZE 32GB or DSSIZE 64GB

· To use DSSIZE larger than 4GB, you’ll have to use extended addressability on your DASD. Check with your systems programmer/s about whether that’s enabled for your DASD pool.

· Set up your partitions for three or four months in size, to allow for application growth.

· Have enough DASD in your storage group to support the amount of data which you’re going to keep!

· Read up on the relationship between DSSIZE, Buffer Pool Size, and number of partitions (in the above link, subject “MAXPARTITIONS”).

· Before going live, fill up a partition with data, and test access to the higher-numbered pages. If extended addressability isn’t set up right, you won’t be able to read pages above a 4-gigabyte RBA value (location in the dataset).


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Sara Ahmadi <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, July 29, 2019 6:04 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: detach and attach partition in db2 z/os


We have DB2 10 and as I know this is a new feature in db2 11

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Sara Ahmadi

RE: detach and attach partition in db2 z/os
(in response to Philip Sevetson)

Thanks for your guidance, but I want to find a simple way of archiving the table and we prefer to have a non partitioned  table for application and a partitioned table for archiving old data.

Philip Sevetson

detach and attach partition in db2 z/os
(in response to Sara Ahmadi)
Sara,

If that train has already left the station, there’s not much I can suggest. An unpartitioned 64GB table is a lot of work to maintain.

If you periodically archive data from it, your choices to remove the archived data from the current-data table are:
REORG with DISCARD
DELETE
Selective UNLOAD followed by an outage to LOAD/REPLACE
All three of these require immense amounts of I/O for a table this large. All would require significantly less I/O (no reads of data which is too recent to archive) if a date partitioning scheme is used to support archiving.

From: Sara Ahmadi <[login to unmask email]>
Sent: Monday, July 29, 2019 11:31 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: detach and attach partition in db2 z/os


Thanks for your guidance, but I want to find a simple way of archiving the table and we prefer to have a non partitioned table for application and a partitioned table for archiving old data.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Michael Hannan

RE: detach and attach partition in db2 z/os
(in response to Sara Ahmadi)

In Reply to Sara Ahmadi:

Thanks for your guidance, but I want to find a simple way of archiving the table and we prefer to have a non partitioned  table for application and a partitioned table for archiving old data.

Partitioned by Growth tables are not really partitioned in a logical sense. So that is a solution to allowing table to grow much larger. In fact DB2 zOS is heading to all tables being partitioned by range or by growth (Universal tablespaces), so preferring "non partitioned" is an anti-solution for you. A self imposed restriction with no purpose, if you like. No sites are trying to avoid Partitioned by Growth tables. Makes no sense. Eventually there won't be any simple tables, nor old segmented tables.

Your simple way to archive has been spelled out.  Have a delete indicator column and update to set it for Delete, then Reorg with Discard. O.K. your table maybe too big for that. Range partitioning would make it easier, allowing Reorg of selected Partitions.

Otherwise you just make a program to go through with Deletes and Insert to another archive table, with regular Commit, every 2 seconds for example. That is the "simple" way before V11 provides archive enabled.  It could even be a Stored Procedure you use to do that. You may suffer random I/O if you do not process data in physical sequence and/or have random low clustered indexes. So the archive process may be slow.

If necessary put the keys rows to be archived in another table to drive the delete/archive  process.

There is no magic for you. The previous advice was pretty good.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jul 31, 2019 - 05:35 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jul 31, 2019 - 05:37 AM (Europe/Berlin)

Phil Grainger

detach and attach partition in db2 z/os
(in response to Michael Hannan)
“Partitioned by Growth tables are not really partitioned in a logical sense.” – it’s a big shame IBM used the word “partitioned” in relation to PBGs – it causes too much confusion (and explanations of how they’re not really partitioned)

Maybe we should just refer to “Growth Tablespaces”

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)] [cid:[login to unmask email] [https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Michael Hannan <[login to unmask email]>
Sent: 31 July 2019 04:34
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: detach and attach partition in db2 z/os


In Reply to Sara Ahmadi:

Thanks for your guidance, but I want to find a simple way of archiving the table and we prefer to have a non partitioned table for application and a partitioned table for archiving old data.

Partitioned by Growth tables are not really partitioned in a logical sense. So that is a solution to allowing table to grow much larger. In fact DB2 zOS is heading to all tables being partitioned by range or by growth (Universal tablespaces), so preferring "non partitioned" is an anti-solution for you. A self imposed restriction with no purpose, if you like. No sites are trying to avoid Partitioned by Growth tables. Makes no sense. Eventually there won't be any simple tables.

Your simple way to archive has been spelled out. Have a delete indicator column and update to set it for Delete, then Reorg with Discard. O.K. your table maybe too big for that. Range partitioning would make it easier, allowing Reorg of selected Partitions.

Otherwise you just make a program to go through with Deletes and Insert to another archive table, with regular Commit, every 2 seconds for example. That is the "simple" way before V11 provides archive enabled. It could even be a Stored Procedure you use to do that. You may suffer random I/O if you do not process data in physical sequence and/or have random low clustered indexes. So the archive process may be slow.

If necessary put the keys rows to be archived in another table to drive the delete/archive process.

There is no magic for you.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (49.7k)
  • image008.png (6.7k)
  • image009.jpg (1.6k)
  • image010.png (<1k)

Michael Hannan

RE: detach and attach partition in db2 z/os
(in response to Phil Grainger)



In Reply to Phil Grainger:

“Partitioned by Growth tables are not really partitioned in a logical sense.” – it’s a big shame IBM used the word “partitioned” in relation to PBGs – it causes too much confusion (and explanations of how they’re not really partitioned)

Maybe we should just refer to “Growth Tablespaces”

Phil Grainger
Principal Enablement Manager

Yes, good one Phil, another misnomer. As we don't have limitkeys, why refer to to PBG as partitioned at all. O.K. We have Max Partitions to specify (in order to determine some things maybe like size of the RIDs), but really the underlying physical structure is not a big concern to most application developers.

I love Growth tables, because there are no silly thoughts of keeping partitions "balanced". I never really wanted to have balanced partitions since that may cause non performance partitioning schemes to be chosen, completely random or unrelated to the major business oriented desirable table clustering for best query performance.

In fact one tuning technique for me has been to change range partitioned tables to Growth tables, when the partitioning scheme was quite unwise, causing many rows required by the same high cost query to be spread randomly amongst the partitions, causing excessive Getpages on the data via the important NPI index.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Aug 01, 2019 - 08:52 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Aug 01, 2019 - 08:55 AM (Europe/Berlin)