Unable to extend Mapping Index

Rakesh Adhikarapu

Unable to extend Mapping Index

Hello Team,

I am trying a Shrlevel change Reorg on PBG tablespace which is having 228 Mn data and 5 indexes. Reorg is failing in Sortbuild phase while extending mapping index.

DSNU342I GEMP 225 11:27:09.41 DSNURBXA - ERROR LOADING INDEX, ERROR = X'00E40322', INDEX = LCCADM1.MAPPING_INDEX 
DSNT500I 225 11:27:09.42 DSNUGSAT - RESOURCE UNAVAILABLE
REASON 00D70025
TYPE 00000220
NAME GEMPDB.DSNDBC.GE99EQPD.MAPPINGR.I0001.A003

I changed more Pri/Sec qty to mapping table and index and still the reorg failing in SORTBLD Phase. Help me from this situation.

Rakeysh

Lizette Koehler

Unable to extend Mapping Index
(in response to Rakesh Adhikarapu)
I always tell my DBAs, Look at the JOB log for other messages



Like IGD or IEC or other NON DB2 messages.



Then do a LISTC ENT(‘vsam linear dataset name here’) ALL



See if the HURBA or HARBA is at 4GB.


Then see if the VSAM define allows for EA/EF (Extended Addressability/Extended Format) to go beyond 4GB



Lizette





From: Rakesh Adhikarapu [mailto:[login to unmask email]
Sent: Sunday, August 13, 2017 10:11 AM
To: [login to unmask email]
Subject: [DB2-L] - Unable to extend Mapping Index



Hello Team,

I am trying a Shrlevel change Reorg on PBG tablespace which is having 228 Mn data and 5 indexes. Reorg is failing in Sortbuild phase while extending mapping index.

DSNU342I GEMP 225 11:27:09.41 DSNURBXA - ERROR LOADING INDEX, ERROR = X'00E40322', INDEX = LCCADM1.MAPPING_INDEX
DSNT500I 225 11:27:09.42 DSNUGSAT - RESOURCE UNAVAILABLE
REASON 00D70025
TYPE 00000220
NAME GEMPDB.DSNDBC.GE99EQPD.MAPPINGR.I0001.A003

I changed more Pri/Sec qty to mapping table and index and still the reorg failing in SORTBLD Phase. Help me from this situation.

Rakeysh

Lizette Koehler

Unable to extend Mapping Index
(in response to Lizette Koehler)
Also look for ICE messages in your output of your job that may also indicate an issue.


Lizette





From: Lizette Koehler [mailto:[login to unmask email]
Sent: Sunday, August 13, 2017 10:48 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index



I always tell my DBAs, Look at the JOB log for other messages



Like IGD or IEC or other NON DB2 messages.



Then do a LISTC ENT(‘vsam linear dataset name here’) ALL



See if the HURBA or HARBA is at 4GB.


Then see if the VSAM define allows for EA/EF (Extended Addressability/Extended Format) to go beyond 4GB



Lizette





From: Rakesh Adhikarapu [mailto:[login to unmask email]
Sent: Sunday, August 13, 2017 10:11 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - Unable to extend Mapping Index



Hello Team,

I am trying a Shrlevel change Reorg on PBG tablespace which is having 228 Mn data and 5 indexes. Reorg is failing in Sortbuild phase while extending mapping index.

DSNU342I GEMP 225 11:27:09.41 DSNURBXA - ERROR LOADING INDEX, ERROR = X'00E40322', INDEX = LCCADM1.MAPPING_INDEX
DSNT500I 225 11:27:09.42 DSNUGSAT - RESOURCE UNAVAILABLE
REASON 00D70025
TYPE 00000220
NAME GEMPDB.DSNDBC.GE99EQPD.MAPPINGR.I0001.A003

I changed more Pri/Sec qty to mapping table and index and still the reorg failing in SORTBLD Phase. Help me from this situation.

Rakeysh



-----End Original Message-----

Larry Jardine

Unable to extend Mapping Index
(in response to Rakesh Adhikarapu)
You may need to create a “larger” mapping table, UTS, Partition by Growth (DSSIZE > 4Gb). If you are on DB2 11 and have the right zparm settings, you can leave MAPPINGTABLE blank on your REORG and DB2 will create the correctly-sized temporary mapping table for your job.

Larry Jardine
Aetna

From: Rakesh Adhikarapu [mailto:[login to unmask email]
Sent: Sunday, August 13, 2017 1:11 PM
To: [login to unmask email]
Subject: [DB2-L] - Unable to extend Mapping Index


Hello Team,

I am trying a Shrlevel change Reorg on PBG tablespace which is having 228 Mn data and 5 indexes. Reorg is failing in Sortbuild phase while extending mapping index.

DSNU342I GEMP 225 11:27:09.41 DSNURBXA - ERROR LOADING INDEX, ERROR = X'00E40322', INDEX = LCCADM1.MAPPING_INDEX
DSNT500I 225 11:27:09.42 DSNUGSAT - RESOURCE UNAVAILABLE
REASON 00D70025
TYPE 00000220
NAME GEMPDB.DSNDBC.GE99EQPD.MAPPINGR.I0001.A003

I changed more Pri/Sec qty to mapping table and index and still the reorg failing in SORTBLD Phase. Help me from this situation.

Rakeysh

-----End Original Message-----

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

Roy Boxwell

Unable to extend Mapping Index
(in response to Larry Jardine)
Judging by the dsn you are already on the third index dataset so check your ACS routines to see if it stops you going multi volume.

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 14 Aug 2017, at 12:06, Jardine, Lawrence J <[login to unmask email]<mailto:[login to unmask email]>> wrote:

You may need to create a “larger” mapping table, UTS, Partition by Growth (DSSIZE > 4Gb). If you are on DB2 11 and have the right zparm settings, you can leave MAPPINGTABLE blank on your REORG and DB2 will create the correctly-sized temporary mapping table for your job.

Larry Jardine
Aetna

From: Rakesh Adhikarapu [mailto:[login to unmask email]
Sent: Sunday, August 13, 2017 1:11 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Unable to extend Mapping Index


Hello Team,

I am trying a Shrlevel change Reorg on PBG tablespace which is having 228 Mn data and 5 indexes. Reorg is failing in Sortbuild phase while extending mapping index.

DSNU342I GEMP 225 11:27:09.41 DSNURBXA - ERROR LOADING INDEX, ERROR = X'00E40322', INDEX = LCCADM1.MAPPING_INDEX
DSNT500I 225 11:27:09.42 DSNUGSAT - RESOURCE UNAVAILABLE
REASON 00D70025
TYPE 00000220
NAME GEMPDB.DSNDBC.GE99EQPD.MAPPINGR.I0001.A003

I changed more Pri/Sec qty to mapping table and index and still the reorg failing in SORTBLD Phase. Help me from this situation.

Rakeysh

-----End Original Message-----
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

-----End Original Message-----

Lynne Flatley

Unable to extend Mapping Index
(in response to Roy Boxwell)
Actually, the mapping table is not used. It's just a table to hang the
index off of. Follow suggestions that others have suggested for sizing the
index but don't worry about the size of the mapping table.

Lynne
+AMDG+

On Mon, Aug 14, 2017 at 2:07 PM, Boxwell, Roy <[login to unmask email]> wrote:

> Judging by the dsn you are already on the third index dataset so check
> your ACS routines to see if it stops you going multi volume.
>
> Roy Boxwell
> SOFTWARE ENGINEERING GmbH and SEGUS Inc.
> -Product Development-
> Heinrichstrasse 83-85
> 40239 Düsseldorf/Germany
> Tel. +49 (0)211 96149-675 <+49%20211%2096149675>
> Fax +49 (0)211 96149-32 <+49%20211%209614932>
> Email: [login to unmask email]
> http://www.seg.de
>
> Software Engineering GmbH
> Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert, Bettina Schubert
>
> On 14 Aug 2017, at 12:06, Jardine, Lawrence J <[login to unmask email]>
> wrote:
>
> You may need to create a “larger” mapping table, UTS, Partition by Growth
> (DSSIZE > 4Gb). If you are on DB2 11 and have the right zparm settings,
> you can leave MAPPINGTABLE blank on your REORG and DB2 will create the
> correctly-sized temporary mapping table for your job.
>
>
>
> *Larry Jardine*
> Aetna
>
>
>
> *From:* Rakesh Adhikarapu [mailto:[login to unmask email]
> <[login to unmask email]>]
> *Sent:* Sunday, August 13, 2017 1:11 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - Unable to extend Mapping Index
>
>
>
> Hello Team,
>
> I am trying a Shrlevel change Reorg on PBG tablespace which is having 228
> Mn data and 5 indexes. Reorg is failing in Sortbuild phase while extending
> mapping index.
>
> DSNU342I GEMP 225 11:27:09.41 DSNURBXA - ERROR LOADING INDEX, ERROR =
> X'00E40322', INDEX = LCCADM1.MAPPING_INDEX
> DSNT500I 225 11:27:09.42 DSNUGSAT - RESOURCE UNAVAILABLE
> REASON 00D70025
> TYPE 00000220
> NAME GEMPDB.DSNDBC.GE99EQPD.MAPPINGR.I0001.A003
>
> I changed more Pri/Sec qty to mapping table and index and still the reorg
> failing in SORTBLD Phase. Help me from this situation.
>
> Rakeysh
>
>
> -----End Original Message-----
> 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
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>

Rakesh Adhikarapu

RE: Unable to extend Mapping Index
(in response to Lynne Flatley)

Thanks everyone,

We have a user defined Stogroup with 3 volumes which have 6 GB of space left.

From utility guide I can see below formula.
The size of the table space for the mapping table can be as small as one track. Use
the following formula to estimate the minimum number of bytes to allocate for the
index on the mapping table:
1.1 * Number-of-rows-in-table-space * 27 

So based on this formula, mapping index is occupying close to 7 GB space which is not available under stogroup. So I created a new PBG Mapping table with SMS managed Stogroup, with multi volumes. I'm going to test this on sunday and hopefully it will sortout the issue.

Thanks again everyone for your quick responses.

Rakeysh

Phil Grainger

Unable to extend Mapping Index
(in response to Rakesh Adhikarapu)
Let us know how you get on Rakesh

I’d expected the mapping table to be a temporary ting when on-line reorgs first came about, but it looks like we are stuck with them. So not only do you need to adequately size the output dataset as the data is unloaded, the sortwork datasets as the data is sorted and the size of the resulting sort output dataset it seems that for large tables, the mapping index can also be a source of difficulty

Out of interest, could you remind me of the size of your table (row length and number of rows) as well as how many indexes?

________________________________

Phil Grainger

Lead Product Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[cid:[login to unmask email]






From: Rakesh Adhikarapu [mailto:[login to unmask email]
Sent: 15 August 2017 16:34
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index


Thanks everyone,

We have a user defined Stogroup with 3 volumes which have 6 GB of space left.

From utility guide I can see below formula.
The size of the table space for the mapping table can be as small as one track. Use
the following formula to estimate the minimum number of bytes to allocate for the
index on the mapping table:
1.1 * Number-of-rows-in-table-space * 27

So based on this formula, mapping index is occupying close to 7 GB space which is not available under stogroup. So I created a new PBG Mapping table with SMS managed Stogroup, with multi volumes. I'm going to test this on sunday and hopefully it will sortout the issue.

Thanks again everyone for your quick responses.

Rakeysh

-----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 (8k)
  • image002.png (5.9k)

Larry Jardine

Unable to extend Mapping Index
(in response to Phil Grainger)
Phil, we are not stuck with [maintaining] mapping tables:

In DB2® 11, support for the MAPPINGDATABASE keyword for the REORG TABLESPACE utility and the new REORG_MAPPING_DATABASE subsystem parameter helps to improve data availability.
Now, when the REORG utility processes a REORG TABLESPACE SHRLEVEL CHANGE request, it can create its own mapping table and mapping index instead of relying on user's input. The MAPPINGDATABASE keyword specifies the database in which REORG implicitly creates the mapping table and index objects. The REORG_MAPPING_DATABASE subsystem parameter specifies the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table.
The MAPPINGDATABASE keyword overrides the subsystem parameter value in the REORG_MAPPING_DATABASE subsystem parameter.


Larry Jardine
Aetna


From: Grainger, Phil [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 6:32 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Let us know how you get on Rakesh

I’d expected the mapping table to be a temporary ting when on-line reorgs first came about, but it looks like we are stuck with them. So not only do you need to adequately size the output dataset as the data is unloaded, the sortwork datasets as the data is sorted and the size of the resulting sort output dataset it seems that for large tables, the mapping index can also be a source of difficulty

Out of interest, could you remind me of the size of your table (row length and number of rows) as well as how many indexes?

-----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.
________________________________
Attachment Links: image001.jpg (8 k) https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D8587&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=jPJvslXCMDm592rZv2I7zcsLrHQaXerXY9HLEFN0Mu0&s=pZT3t8ysVq3AZMJFqL4hFwxrdwDiZLfhnbtb5c7b-NM&e= image002.png (6 k) https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D8588&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=jPJvslXCMDm592rZv2I7zcsLrHQaXerXY9HLEFN0Mu0&s=GJ2uUmVTz6vzIubMuXJs1xxWrbPB6NVSQkfs_sZ_Sto&e=
Site Links: View post online https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_st_-3Fpost-3D182593-26anc-3Dp182593-23p182593&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=jPJvslXCMDm592rZv2I7zcsLrHQaXerXY9HLEFN0Mu0&s=w9-_UdQ0xlHftGH2cgYqh0l9NfHwrhma1DaB0DctI7s&e= View mailing list online https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_si_-3Ftopic-3D19&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=jPJvslXCMDm592rZv2I7zcsLrHQaXerXY9HLEFN0Mu0&s=J7rjMP4oBt8nPtCCt-x9jT-4S5iDRVjODMaoOqyl51g&e= Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_us_to_&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=jPJvslXCMDm592rZv2I7zcsLrHQaXerXY9HLEFN0Mu0&s=B2xKJzo0aXs1HCbOujFQYqM-hvkhdRFYi1Q9mCq6Vgo&e=

This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>

Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
ttp://www.ESAIGroup.com/idug <ttp://www.ESAIGroup.com/idug%20>

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_cm_ld_fid-3D2&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=jPJvslXCMDm592rZv2I7zcsLrHQaXerXY9HLEFN0Mu0&s=nqPWdrSPreQcqXV4p0FSnDgdDG4wMcBYT33RVZ_Otpw&e=

________________________________



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

Phil Grainger

Unable to extend Mapping Index
(in response to Larry Jardine)
Thanks for the correction Lawrence

You’re right, this is a big improvement. Now all people have to worry about is whether they have enough space for DB2 to create the mapping index

________________________________

Phil Grainger

Lead Product Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[cid:[login to unmask email]






From: Jardine, Lawrence J [mailto:[login to unmask email]
Sent: 16 August 2017 12:21
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Phil, we are not stuck with [maintaining] mapping tables:

In DB2® 11, support for the MAPPINGDATABASE keyword for the REORG TABLESPACE utility and the new REORG_MAPPING_DATABASE subsystem parameter helps to improve data availability.
Now, when the REORG utility processes a REORG TABLESPACE SHRLEVEL CHANGE request, it can create its own mapping table and mapping index instead of relying on user's input. The MAPPINGDATABASE keyword specifies the database in which REORG implicitly creates the mapping table and index objects. The REORG_MAPPING_DATABASE subsystem parameter specifies the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table.
The MAPPINGDATABASE keyword overrides the subsystem parameter value in the REORG_MAPPING_DATABASE subsystem parameter.


Larry Jardine
Aetna

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 6:32 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Let us know how you get on Rakesh

I’d expected the mapping table to be a temporary ting when on-line reorgs first came about, but it looks like we are stuck with them. So not only do you need to adequately size the output dataset as the data is unloaded, the sortwork datasets as the data is sorted and the size of the resulting sort output dataset it seems that for large tables, the mapping index can also be a source of difficulty

Out of interest, could you remind me of the size of your table (row length and number of rows) as well as how many indexes?

-----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.
-----End Original Message-----


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

-----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 (8k)
  • image002.png (5.9k)

Philip Sevetson

Unable to extend Mapping Index
(in response to Phil Grainger)
**please note my email address change**
I’m absolutely thrilled with this one.

Previously, we pretty much had to create a distinct mapping table for every table where we wanted to run a REORG SHRLEVLE CHANGE, because of the possibility of collisions if we tried to combine “customers” for the mapping tables. This eliminates that and eliminates the need for permanent tables that don’t actually contain business data

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001-2632
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 10:48 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Thanks for the correction Lawrence

You’re right, this is a big improvement. Now all people have to worry about is whether they have enough space for DB2 to create the mapping index

-----End Original Message-----
-----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.
-----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)

Roy Boxwell

Unable to extend Mapping Index
(in response to Larry Jardine)
I hate it! Concurrent catalog updates are not good and it fragments your catalog. Stick with a nice set of job named mapping tables and a product that supports and uses them all! RTDX is your friend there...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 16 Aug 2017, at 15:30, Jardine, Lawrence J <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Phil, we are not stuck with [maintaining] mapping tables:

In DB2® 11, support for the MAPPINGDATABASE keyword for the REORG TABLESPACE utility and the new REORG_MAPPING_DATABASE subsystem parameter helps to improve data availability.
Now, when the REORG utility processes a REORG TABLESPACE SHRLEVEL CHANGE request, it can create its own mapping table and mapping index instead of relying on user's input. The MAPPINGDATABASE keyword specifies the database in which REORG implicitly creates the mapping table and index objects. The REORG_MAPPING_DATABASE subsystem parameter specifies the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table.
The MAPPINGDATABASE keyword overrides the subsystem parameter value in the REORG_MAPPING_DATABASE subsystem parameter.


Larry Jardine
Aetna


From: Grainger, Phil [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 6:32 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Let us know how you get on Rakesh

I’d expected the mapping table to be a temporary ting when on-line reorgs first came about, but it looks like we are stuck with them. So not only do you need to adequately size the output dataset as the data is unloaded, the sortwork datasets as the data is sorted and the size of the resulting sort output dataset it seems that for large tables, the mapping index can also be a source of difficulty

Out of interest, could you remind me of the size of your table (row length and number of rows) as well as how many indexes?

-----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.
-----End Original Message-----


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

-----End Original Message-----

Steen Rasmussen

Unable to extend Mapping Index
(in response to Roy Boxwell)
Ooops Roy – where’s the [AD] (its almost Friday).

Steen

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 3:04 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.
I hate it! Concurrent catalog updates are not good and it fragments your catalog. Stick with a nice set of job named mapping tables and a product that supports and uses them all! RTDX is your friend there...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=3GNBAZQ_eCc7ke0dLdXDFLJvhDAUeV337qW16q4oYEc&s=tqLAur8IvHomj3tyQfJDhF6eYS0yLW8gEiIbSytjJUk&e=

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 16 Aug 2017, at 15:30, Jardine, Lawrence J <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Phil, we are not stuck with [maintaining] mapping tables:

In DB2® 11, support for the MAPPINGDATABASE keyword for the REORG TABLESPACE utility and the new REORG_MAPPING_DATABASE subsystem parameter helps to improve data availability.
Now, when the REORG utility processes a REORG TABLESPACE SHRLEVEL CHANGE request, it can create its own mapping table and mapping index instead of relying on user's input. The MAPPINGDATABASE keyword specifies the database in which REORG implicitly creates the mapping table and index objects. The REORG_MAPPING_DATABASE subsystem parameter specifies the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table.
The MAPPINGDATABASE keyword overrides the subsystem parameter value in the REORG_MAPPING_DATABASE subsystem parameter.


Larry Jardine
Aetna



From: Grainger, Phil [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 6:32 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Let us know how you get on Rakesh

I’d expected the mapping table to be a temporary ting when on-line reorgs first came about, but it looks like we are stuck with them. So not only do you need to adequately size the output dataset as the data is unloaded, the sortwork datasets as the data is sorted and the size of the resulting sort output dataset it seems that for large tables, the mapping index can also be a source of difficulty

Out of interest, could you remind me of the size of your table (row length and number of rows) as well as how many indexes?

-----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.
-----End Original Message-----


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

-----End Original Message-----

-----End Original Message-----

Roy Boxwell

Unable to extend Mapping Index
(in response to Steen Rasmussen)
It was late in my time zone… ☺

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Rasmussen, Steen [mailto:[login to unmask email]
Sent: Thursday, August 17, 2017 12:12 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Ooops Roy – where’s the [AD] (its almost Friday).

Steen

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 3:04 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.
I hate it! Concurrent catalog updates are not good and it fragments your catalog. Stick with a nice set of job named mapping tables and a product that supports and uses them all! RTDX is your friend there...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=3GNBAZQ_eCc7ke0dLdXDFLJvhDAUeV337qW16q4oYEc&s=tqLAur8IvHomj3tyQfJDhF6eYS0yLW8gEiIbSytjJUk&e=

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 16 Aug 2017, at 15:30, Jardine, Lawrence J <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Phil, we are not stuck with [maintaining] mapping tables:

In DB2® 11, support for the MAPPINGDATABASE keyword for the REORG TABLESPACE utility and the new REORG_MAPPING_DATABASE subsystem parameter helps to improve data availability.
Now, when the REORG utility processes a REORG TABLESPACE SHRLEVEL CHANGE request, it can create its own mapping table and mapping index instead of relying on user's input. The MAPPINGDATABASE keyword specifies the database in which REORG implicitly creates the mapping table and index objects. The REORG_MAPPING_DATABASE subsystem parameter specifies the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table.
The MAPPINGDATABASE keyword overrides the subsystem parameter value in the REORG_MAPPING_DATABASE subsystem parameter.


Larry Jardine
Aetna


From: Grainger, Phil [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 6:32 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Let us know how you get on Rakesh

I’d expected the mapping table to be a temporary ting when on-line reorgs first came about, but it looks like we are stuck with them. So not only do you need to adequately size the output dataset as the data is unloaded, the sortwork datasets as the data is sorted and the size of the resulting sort output dataset it seems that for large tables, the mapping index can also be a source of difficulty

Out of interest, could you remind me of the size of your table (row length and number of rows) as well as how many indexes?

-----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.
-----End Original Message-----


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

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

Raymond Bell

Unable to extend Mapping Index
(in response to Roy Boxwell)
Or how about a reorg product that doesn’t need mapping tables? You can take the boy out of, well, you know who. :o)

Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: 17 August 2017 06:53
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************

It was late in my time zone… ☺

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Rasmussen, Steen [mailto:[login to unmask email]
Sent: Thursday, August 17, 2017 12:12 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Ooops Roy – where’s the [AD] (its almost Friday).

Steen

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 3:04 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.
I hate it! Concurrent catalog updates are not good and it fragments your catalog. Stick with a nice set of job named mapping tables and a product that supports and uses them all! RTDX is your friend there...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=3GNBAZQ_eCc7ke0dLdXDFLJvhDAUeV337qW16q4oYEc&s=tqLAur8IvHomj3tyQfJDhF6eYS0yLW8gEiIbSytjJUk&e=

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 16 Aug 2017, at 15:30, Jardine, Lawrence J <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Phil, we are not stuck with [maintaining] mapping tables:

In DB2® 11, support for the MAPPINGDATABASE keyword for the REORG TABLESPACE utility and the new REORG_MAPPING_DATABASE subsystem parameter helps to improve data availability.
Now, when the REORG utility processes a REORG TABLESPACE SHRLEVEL CHANGE request, it can create its own mapping table and mapping index instead of relying on user's input. The MAPPINGDATABASE keyword specifies the database in which REORG implicitly creates the mapping table and index objects. The REORG_MAPPING_DATABASE subsystem parameter specifies the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table.
The MAPPINGDATABASE keyword overrides the subsystem parameter value in the REORG_MAPPING_DATABASE subsystem parameter.


Larry Jardine
Aetna

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 6:32 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Let us know how you get on Rakesh

I’d expected the mapping table to be a temporary ting when on-line reorgs first came about, but it looks like we are stuck with them. So not only do you need to adequately size the output dataset as the data is unloaded, the sortwork datasets as the data is sorted and the size of the resulting sort output dataset it seems that for large tables, the mapping index can also be a source of difficulty

Out of interest, could you remind me of the size of your table (row length and number of rows) as well as how many indexes?

-----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.
-----End Original Message-----


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

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com

Steven Lamb

RE: Unable to extend Mapping Index
(in response to Roy Boxwell)

We're using the -1 option for the index secondary extent size - not had any problems with this (so far!)

Roy Boxwell

Unable to extend Mapping Index
(in response to Steven Lamb)
Wait till you hit *really*big tables….

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Steven Lamb [mailto:[login to unmask email]
Sent: Thursday, August 17, 2017 9:13 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index


We're using the -1 option for the index secondary extent size - not had any problems with this (so far!)

-----End Original Message-----

Steven Lamb

RE: Unable to extend Mapping Index
(in response to Roy Boxwell)

Our biggest objects are all partitioned and Reorged at the partition level, which reduces the space needs.

E.g. 4 billion rows in 120 parts, but admittedly only one partitioned index with a 90 byte key length.

2.5 billion rows, 120 parts, two indexes, one of which is an NPI - we have REORG_PART_SORT_NPSI = NO (stats collection bug currently).

At some point we'll be converting our biggest table (2 Tb) to UTS format. That has 5 indexes and could be interesting :)

Roy Boxwell

Unable to extend Mapping Index
(in response to Steven Lamb)
Well the problem was the good old limit of 32 LDs for the Index which limited the size of the index to 64GB. In DB2 11 you can allocate the mapping table to be a UTS PBG which eliminates this chance of dieing horribly:

3.4.2 Mapping tables up to 16 TB
Mapping tables must store information about each row’s source and target RID. Thus, a REORG job can hit the limit of 64 GB when very large table spaces are reorganized. Prior to DB2 11, this limit meant a mapping table had to be a segmented table space. With growing data needs, REORG jobs can hit the limit more often. DB2 11 allows you to use PBG table spaces for storing mapping tables, which practically eliminates the limit.
To determine how much space you need for a REORG, make the following calculation for the index:

1.1 * Number-of-rows-in-table-space * 31

From the Utility guide is the clarification that only the Index is used but watch out for 10 byte RBA/LRSN!

The size of the table space for the mapping table can be as small as one track. Use the following formula to estimate the minimum number of bytes to allocate for the index on the mapping table:
1.1 * Number-of-rows-in-table-space * 27
When the LRSN column is CHAR(10):

1.1 * Number-of-rows-in-table-space * 31



So working “backwards” you have 64 GB = 1.1 * row count * 31 so rewriting that solving for row count gives you:
64 GB / 1.1 / 31 = row count which gives you 2,015,233,922 rows as the “limit” which is pretty big!



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Steven Lamb [mailto:[login to unmask email]
Sent: Thursday, August 17, 2017 10:31 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index


Our biggest objects are all partitioned and Reorged at the partition level, which reduces the space needs.

E.g. 4 billion rows in 120 parts, but admittedly only one partitioned index with a 90 byte key length.

2.5 billion rows, 120 parts, two indexes, one of which is an NPI - we have REORG_PART_SORT_NPSI = NO (stats collection bug currently).

At some point we'll be converting our biggest table (2 Tb) to UTS format. That has 5 indexes and could be interesting :)

-----End Original Message-----

Phil Grainger

[AD]Unable to extend Mapping Index
(in response to Roy Boxwell)
OK I can resist temptation no longer

Isn't a better solution to use a reorg that doesn't need ANY mapping tables or indexes?

The mapping table is rapidly becoming yet another limitation of IBM Reorg when data volumes start to climb.....

If anyone wants to know more, you know where u am

Rant over

Sent using OWA for iPad
________________________________
From: Boxwell, Roy <[login to unmask email]>
Sent: 17 August 2017 11:11:17
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Well the problem was the good old limit of 32 LDs for the Index which limited the size of the index to 64GB. In DB2 11 you can allocate the mapping table to be a UTS PBG which eliminates this chance of dieing horribly:

3.4.2 Mapping tables up to 16 TB
Mapping tables must store information about each row's source and target RID. Thus, a REORG job can hit the limit of 64 GB when very large table spaces are reorganized. Prior to DB2 11, this limit meant a mapping table had to be a segmented table space. With growing data needs, REORG jobs can hit the limit more often. DB2 11 allows you to use PBG table spaces for storing mapping tables, which practically eliminates the limit.
To determine how much space you need for a REORG, make the following calculation for the index:

1.1 * Number-of-rows-in-table-space * 31

From the Utility guide is the clarification that only the Index is used but watch out for 10 byte RBA/LRSN!

The size of the table space for the mapping table can be as small as one track. Use the following formula to estimate the minimum number of bytes to allocate for the index on the mapping table:
1.1 * Number-of-rows-in-table-space * 27
When the LRSN column is CHAR(10):

1.1 * Number-of-rows-in-table-space * 31



So working "backwards" you have 64 GB = 1.1 * row count * 31 so rewriting that solving for row count gives you:
64 GB / 1.1 / 31 = row count which gives you 2,015,233,922 rows as the "limit" which is pretty big!



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=JcPEOE30gKoe7ntw694fVUfFGZqOCMWD7gYuMpqVgV8&s=iantmiCVX9G3YvE6kstl9f23lmhOnFlChBuFGg53y1Y&e=

Software Engineering GmbH
Amtsgericht D?sseldorf, HRB 37894
Gesch?ftsf?hrung: Gerhard Schubert, Bettina Schubert

From: Steven Lamb [mailto:[login to unmask email]
Sent: Thursday, August 17, 2017 10:31 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index


Our biggest objects are all partitioned and Reorged at the partition level, which reduces the space needs.

E.g. 4 billion rows in 120 parts, but admittedly only one partitioned index with a 90 byte key length.

2.5 billion rows, 120 parts, two indexes, one of which is an NPI - we have REORG_PART_SORT_NPSI = NO (stats collection bug currently).

At some point we'll be converting our biggest table (2 Tb) to UTS format. That has 5 indexes and could be interesting :)

-----End Original Message-----

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

Larry Jardine

Unable to extend Mapping Index
(in response to Roy Boxwell)
Counterpoint. I like it (dynamically created mapping tables/indexes)!

Perhaps a good compromise would be to convert your pre-defined mapping tables/indexes for scheduled REORG jobs to UTS PBG and allow DB2 to dynamically create mapping tables/indexes for ad-hoc REORGs.

That would minimize catalog contention. I’m not sure about catalog fragmentation being an issue. Can you elaborate?

Larry Jardine
Aetna
Senior Database Administrator

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 4:04 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index

I hate it! Concurrent catalog updates are not good and it fragments your catalog. Stick with a nice set of job named mapping tables and a product that supports and uses them all! RTDX is your friend there...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=nX0uSilWES8TKdzWsT_YGESipAdZSufZJiwMo7S22BY&s=pxYQvOGZgnyuRWOueN6J6PIaivFeCmIKzmhF0ItibZg&e=

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 16 Aug 2017, at 15:30, Jardine, Lawrence J <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Phil, we are not stuck with [maintaining] mapping tables:

In DB2® 11, support for the MAPPINGDATABASE keyword for the REORG TABLESPACE utility and the new REORG_MAPPING_DATABASE subsystem parameter helps to improve data availability.
Now, when the REORG utility processes a REORG TABLESPACE SHRLEVEL CHANGE request, it can create its own mapping table and mapping index instead of relying on user's input. The MAPPINGDATABASE keyword specifies the database in which REORG implicitly creates the mapping table and index objects. The REORG_MAPPING_DATABASE subsystem parameter specifies the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table.
The MAPPINGDATABASE keyword overrides the subsystem parameter value in the REORG_MAPPING_DATABASE subsystem parameter.


Larry Jardine
Aetna



From: Grainger, Phil [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 6:32 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Let us know how you get on Rakesh

I’d expected the mapping table to be a temporary ting when on-line reorgs first came about, but it looks like we are stuck with them. So not only do you need to adequately size the output dataset as the data is unloaded, the sortwork datasets as the data is sorted and the size of the resulting sort output dataset it seems that for large tables, the mapping index can also be a source of difficulty

Out of interest, could you remind me of the size of your table (row length and number of rows) as well as how many indexes?

-----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.
-----End Original Message-----


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

-----End Original Message-----

-----End Original Message-----

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

Roy Boxwell

Unable to extend Mapping Index
(in response to Larry Jardine)
I worked at a place where they had “reorg weekends” – They would reorg thousands of objects at the weekend *each* of them with its own mapping table. They created and dropped the DB, TS, TB and IX *every* time… This caused the Db2 Catalog to expand quite fast and required regular, pretty nasty, Catalog/Directory REORGs to keep it under control.
After this experience I vowed to only REORG things that need reorging and create the minimum amount of trouble for the Catalog and Directory!

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Jardine, Lawrence J [mailto:[login to unmask email]
Sent: Thursday, August 17, 2017 1:38 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Counterpoint. I like it (dynamically created mapping tables/indexes)!

Perhaps a good compromise would be to convert your pre-defined mapping tables/indexes for scheduled REORG jobs to UTS PBG and allow DB2 to dynamically create mapping tables/indexes for ad-hoc REORGs.

That would minimize catalog contention. I’m not sure about catalog fragmentation being an issue. Can you elaborate?

Larry Jardine
Aetna
Senior Database Administrator

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 4:04 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

I hate it! Concurrent catalog updates are not good and it fragments your catalog. Stick with a nice set of job named mapping tables and a product that supports and uses them all! RTDX is your friend there...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=nX0uSilWES8TKdzWsT_YGESipAdZSufZJiwMo7S22BY&s=pxYQvOGZgnyuRWOueN6J6PIaivFeCmIKzmhF0ItibZg&e=

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 16 Aug 2017, at 15:30, Jardine, Lawrence J <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Phil, we are not stuck with [maintaining] mapping tables:

In DB2® 11, support for the MAPPINGDATABASE keyword for the REORG TABLESPACE utility and the new REORG_MAPPING_DATABASE subsystem parameter helps to improve data availability.
Now, when the REORG utility processes a REORG TABLESPACE SHRLEVEL CHANGE request, it can create its own mapping table and mapping index instead of relying on user's input. The MAPPINGDATABASE keyword specifies the database in which REORG implicitly creates the mapping table and index objects. The REORG_MAPPING_DATABASE subsystem parameter specifies the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table.
The MAPPINGDATABASE keyword overrides the subsystem parameter value in the REORG_MAPPING_DATABASE subsystem parameter.


Larry Jardine
Aetna


From: Grainger, Phil [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 6:32 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Let us know how you get on Rakesh

I’d expected the mapping table to be a temporary ting when on-line reorgs first came about, but it looks like we are stuck with them. So not only do you need to adequately size the output dataset as the data is unloaded, the sortwork datasets as the data is sorted and the size of the resulting sort output dataset it seems that for large tables, the mapping index can also be a source of difficulty

Out of interest, could you remind me of the size of your table (row length and number of rows) as well as how many indexes?

-----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.
-----End Original Message-----


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

-----End Original Message-----

-----End Original Message-----
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

-----End Original Message-----

Peter Backlund

Unable to extend Mapping Index
(in response to Roy Boxwell)
I remember a customer a few years ago
We reorg, runstats, and rebind everything every weekend
My answer was
Why?
Their answer
Because you told us!
It took some time to get this in order,
especially the magic words "It depends"

It must be Friday somewhere...

Peter




On 2017-08-17 15:03, Boxwell, Roy wrote:
I worked at a place where they had “reorg weekends” – They would reorg thousands of objects at the weekend *each* of them with its own mapping table. They created and dropped the DB, TS, TB and IX *every* time… This caused the Db2 Catalog to expand quite fast and required regular, pretty nasty, Catalog/Directory REORGs to keep it under control.After this experience I vowed to only REORG things that need reorging and create the minimum amount of trouble for the Catalog and Directory! Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert From: Jardine, Lawrence J [mailto:[login to unmask email]
Sent: Thursday, August 17, 2017 1:38 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index Counterpoint.  I like it (dynamically created mapping tables/indexes)! Perhaps a good compromise would be to convert your pre-defined mapping tables/indexes for scheduled REORG jobs to UTS PBG and allow DB2 to dynamically create mapping tables/indexes for ad-hoc REORGs. That would minimize catalog contention. I’m not sure about catalog fragmentation being an issue. Can you elaborate? Larry Jardine Aetna
Senior Database Administrator From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 4:04 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index I hate it! Concurrent catalog updates are not good and it fragments your catalog. Stick with a nice set of job named mapping tables and a product that supports and uses them all! RTDX is your friend there...

Roy Boxwell SOFTWARE ENGINEERING GmbH and SEGUS Inc.-Product Development-Heinrichstrasse 83-8540239 Düsseldorf/GermanyTel. +49 (0)211 96149-675Fax +49 (0)211 96149-32Email: [login to unmask email]http://www.seg.de Software Engineering GmbHAmtsgericht Düsseldorf, HRB 37894Geschäftsführung: Gerhard Schubert, Bettina Schubert
On 16 Aug 2017, at 15:30, Jardine, Lawrence J <[login to unmask email]> wrote:Phil, we are not stuck with [maintaining] mapping tables: In DB2® 11, support for the MAPPINGDATABASE keyword for the REORG TABLESPACE utility and the new REORG_MAPPING_DATABASE subsystem parameter helps to improve data availability.Now, when the REORG utility processes a REORG TABLESPACE SHRLEVEL CHANGE request, it can create its own mapping table and mapping index instead of relying on user's input. The MAPPINGDATABASE keyword specifies the database in which REORG implicitly creates the mapping table and index objects. The REORG_MAPPING_DATABASE subsystem parameter specifies the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table.The MAPPINGDATABASE keyword overrides the subsystem parameter value in the REORG_MAPPING_DATABASE subsystem parameter. Larry JardineAetna

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 6:32 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index Let us know how you get on Rakesh I’d expected the mapping table to be a temporary ting when on-line reorgs first came about, but it looks like we are stuck with them. So not only do you need to adequately size the output dataset as the data is unloaded, the sortwork datasets as the data is sorted and the size of the resulting sort output dataset it seems that for large tables, the mapping index can also be a source of difficulty Out of interest, could you remind me of the size of your table (row length and number of rows) as well as how many indexes? -----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. -----End Original Message----- 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 -----End Original Message----- -----End Original Message-----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 -----End Original Message-----
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2
--

+--------------------------------+---------------------------------+
| Peter G Backlund | E-mail: [login to unmask email] |
+--------------------------------+---------------------------------+
| Phone: 0046(0)70 - 764 7554 | Skype: BacklundDB2 |
+--------------------------------+---------------------------------+
| May - September | October - April |
+------------------------------------------------------------------+
| Råsundavägen 94 | Calle Alella 55, Apt 2B |
| S-169 57 SOLNA | 03185 Torrevieja |
| Sweden | Spain |
+--------------------------------+---------------------------------+
| Playing with DB2 since 1981 ... and forever! |
+------------------------------------------------------------------+

Larry Jardine

Unable to extend Mapping Index
(in response to Roy Boxwell)
I think we can all agree, that it is not a very good idea to have individual temporary mapping tables for each object to be REORGed. Certainly LISTDEF, predefined mapping tables, and REORGs based on statistical needs are best practices in this case.

The best REORG is the one you don’t [need to] run.

Larry Jardine
Aetna
Senior Database Administrator

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Thursday, August 17, 2017 9:03 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index

I worked at a place where they had “reorg weekends” – They would reorg thousands of objects at the weekend *each* of them with its own mapping table. They created and dropped the DB, TS, TB and IX *every* time… This caused the Db2 Catalog to expand quite fast and required regular, pretty nasty, Catalog/Directory REORGs to keep it under control.
After this experience I vowed to only REORG things that need reorging and create the minimum amount of trouble for the Catalog and Directory!

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de_&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=IK-1FO7_Qv4sfKsOMSAMdaQ54ka_FVVrTRCIt1Ix5NY&s=xxi0Owep6W7uo0repj6oq5hGFkW77CZd5z8FhOKlEIQ&e=

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Jardine, Lawrence J [mailto:[login to unmask email]
Sent: Thursday, August 17, 2017 1:38 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Counterpoint. I like it (dynamically created mapping tables/indexes)!

Perhaps a good compromise would be to convert your pre-defined mapping tables/indexes for scheduled REORG jobs to UTS PBG and allow DB2 to dynamically create mapping tables/indexes for ad-hoc REORGs.

That would minimize catalog contention. I’m not sure about catalog fragmentation being an issue. Can you elaborate?

Larry Jardine
Aetna
Senior Database Administrator

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 4:04 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

I hate it! Concurrent catalog updates are not good and it fragments your catalog. Stick with a nice set of job named mapping tables and a product that supports and uses them all! RTDX is your friend there...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=nX0uSilWES8TKdzWsT_YGESipAdZSufZJiwMo7S22BY&s=pxYQvOGZgnyuRWOueN6J6PIaivFeCmIKzmhF0ItibZg&e=

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 16 Aug 2017, at 15:30, Jardine, Lawrence J <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Phil, we are not stuck with [maintaining] mapping tables:

In DB2® 11, support for the MAPPINGDATABASE keyword for the REORG TABLESPACE utility and the new REORG_MAPPING_DATABASE subsystem parameter helps to improve data availability.
Now, when the REORG utility processes a REORG TABLESPACE SHRLEVEL CHANGE request, it can create its own mapping table and mapping index instead of relying on user's input. The MAPPINGDATABASE keyword specifies the database in which REORG implicitly creates the mapping table and index objects. The REORG_MAPPING_DATABASE subsystem parameter specifies the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table.
The MAPPINGDATABASE keyword overrides the subsystem parameter value in the REORG_MAPPING_DATABASE subsystem parameter.


Larry Jardine
Aetna

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 6:32 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Let us know how you get on Rakesh

I’d expected the mapping table to be a temporary ting when on-line reorgs first came about, but it looks like we are stuck with them. So not only do you need to adequately size the output dataset as the data is unloaded, the sortwork datasets as the data is sorted and the size of the resulting sort output dataset it seems that for large tables, the mapping index can also be a source of difficulty

Out of interest, could you remind me of the size of your table (row length and number of rows) as well as how many indexes?

-----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.
-----End Original Message-----


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

-----End Original Message-----

-----End Original Message-----
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

-----End Original Message-----

-----End Original Message-----

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

Roy Boxwell

Unable to extend Mapping Index
(in response to Larry Jardine)
I hear that! But as we all know, "never change a running system" and "but it was always done like that" are nearly impenetrable fortresses of trollhood!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 17 Aug 2017, at 16:28, Jardine, Lawrence J <[login to unmask email]<mailto:[login to unmask email]>> wrote:

I think we can all agree, that it is not a very good idea to have individual temporary mapping tables for each object to be REORGed. Certainly LISTDEF, predefined mapping tables, and REORGs based on statistical needs are best practices in this case.

The best REORG is the one you don’t [need to] run.

Larry Jardine
Aetna
Senior Database Administrator

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Thursday, August 17, 2017 9:03 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

I worked at a place where they had “reorg weekends” – They would reorg thousands of objects at the weekend *each* of them with its own mapping table. They created and dropped the DB, TS, TB and IX *every* time… This caused the Db2 Catalog to expand quite fast and required regular, pretty nasty, Catalog/Directory REORGs to keep it under control.
After this experience I vowed to only REORG things that need reorging and create the minimum amount of trouble for the Catalog and Directory!

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de_&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=IK-1FO7_Qv4sfKsOMSAMdaQ54ka_FVVrTRCIt1Ix5NY&s=xxi0Owep6W7uo0repj6oq5hGFkW77CZd5z8FhOKlEIQ&e=

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Jardine, Lawrence J [mailto:[login to unmask email]
Sent: Thursday, August 17, 2017 1:38 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Counterpoint. I like it (dynamically created mapping tables/indexes)!

Perhaps a good compromise would be to convert your pre-defined mapping tables/indexes for scheduled REORG jobs to UTS PBG and allow DB2 to dynamically create mapping tables/indexes for ad-hoc REORGs.

That would minimize catalog contention. I’m not sure about catalog fragmentation being an issue. Can you elaborate?

Larry Jardine
Aetna
Senior Database Administrator

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 4:04 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

I hate it! Concurrent catalog updates are not good and it fragments your catalog. Stick with a nice set of job named mapping tables and a product that supports and uses them all! RTDX is your friend there...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=nX0uSilWES8TKdzWsT_YGESipAdZSufZJiwMo7S22BY&s=pxYQvOGZgnyuRWOueN6J6PIaivFeCmIKzmhF0ItibZg&e=

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 16 Aug 2017, at 15:30, Jardine, Lawrence J <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Phil, we are not stuck with [maintaining] mapping tables:

In DB2® 11, support for the MAPPINGDATABASE keyword for the REORG TABLESPACE utility and the new REORG_MAPPING_DATABASE subsystem parameter helps to improve data availability.
Now, when the REORG utility processes a REORG TABLESPACE SHRLEVEL CHANGE request, it can create its own mapping table and mapping index instead of relying on user's input. The MAPPINGDATABASE keyword specifies the database in which REORG implicitly creates the mapping table and index objects. The REORG_MAPPING_DATABASE subsystem parameter specifies the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table.
The MAPPINGDATABASE keyword overrides the subsystem parameter value in the REORG_MAPPING_DATABASE subsystem parameter.


Larry Jardine
Aetna

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 6:32 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Let us know how you get on Rakesh

I’d expected the mapping table to be a temporary ting when on-line reorgs first came about, but it looks like we are stuck with them. So not only do you need to adequately size the output dataset as the data is unloaded, the sortwork datasets as the data is sorted and the size of the resulting sort output dataset it seems that for large tables, the mapping index can also be a source of difficulty

Out of interest, could you remind me of the size of your table (row length and number of rows) as well as how many indexes?

-----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.
-----End Original Message-----


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

-----End Original Message-----

-----End Original Message-----
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

-----End Original Message-----

-----End Original Message-----
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

-----End Original Message-----

Steven Lamb

RE: Unable to extend Mapping Index
(in response to Roy Boxwell)

Out of idle curiosity I ran a reorg and let DB2 create the mapping table. In this case the index is created with PRIQTY and SECQTY both set to -1, DSSIZE 64Gb and a piecesize of 4Gb.

 

Regards,
Steve

Roy Boxwell

Unable to extend Mapping Index
(in response to Steven Lamb)
And MAXPARTITIONS ??

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Steven Lamb [mailto:[login to unmask email]
Sent: Wednesday, August 23, 2017 2:03 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index


Out of idle curiosity I ran a reorg and let DB2 create the mapping table. In this case the index is created with PRIQTY and SECQTY both set to -1, DSSIZE 64Gb and a piecesize of 4Gb.



Regards,
Steve

-----End Original Message-----

Steven Lamb

RE: Unable to extend Mapping Index
(in response to Steven Lamb)

Agghhh! I forgot to check that! I assume it'll use the defaults according to the SQL Reference.

The tablespace I was Reorging caused the mapping index to grow to at least 12 parts - I looked at actual datasets a couple of times whilst the job was running.

Steve

Phil Grainger

Unable to extend Mapping Index
(in response to Larry Jardine)
Then the second best one (thanks Raymond) is one that doesn’t require stupid mapping tables in the first place

(Sorry – had a rough week)
________________________________

Phil Grainger

Lead Product Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[cid:[login to unmask email]






From: Jardine, Lawrence J [mailto:[login to unmask email]
Sent: 17 August 2017 15:28
To: [login to unmask email]
Subject: [DB2-L] - RE: Unable to extend Mapping Index

I think we can all agree, that it is not a very good idea to have individual temporary mapping tables for each object to be REORGed. Certainly LISTDEF, predefined mapping tables, and REORGs based on statistical needs are best practices in this case.

The best REORG is the one you don’t [need to] run.

Larry Jardine
Aetna
Senior Database Administrator

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Thursday, August 17, 2017 9:03 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

I worked at a place where they had “reorg weekends” – They would reorg thousands of objects at the weekend *each* of them with its own mapping table. They created and dropped the DB, TS, TB and IX *every* time… This caused the Db2 Catalog to expand quite fast and required regular, pretty nasty, Catalog/Directory REORGs to keep it under control.
After this experience I vowed to only REORG things that need reorging and create the minimum amount of trouble for the Catalog and Directory!

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de_&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=IK-1FO7_Qv4sfKsOMSAMdaQ54ka_FVVrTRCIt1Ix5NY&s=xxi0Owep6W7uo0repj6oq5hGFkW77CZd5z8FhOKlEIQ&e=

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Jardine, Lawrence J [mailto:[login to unmask email]
Sent: Thursday, August 17, 2017 1:38 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Counterpoint. I like it (dynamically created mapping tables/indexes)!

Perhaps a good compromise would be to convert your pre-defined mapping tables/indexes for scheduled REORG jobs to UTS PBG and allow DB2 to dynamically create mapping tables/indexes for ad-hoc REORGs.

That would minimize catalog contention. I’m not sure about catalog fragmentation being an issue. Can you elaborate?

Larry Jardine
Aetna
Senior Database Administrator

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 4:04 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

I hate it! Concurrent catalog updates are not good and it fragments your catalog. Stick with a nice set of job named mapping tables and a product that supports and uses them all! RTDX is your friend there...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=nX0uSilWES8TKdzWsT_YGESipAdZSufZJiwMo7S22BY&s=pxYQvOGZgnyuRWOueN6J6PIaivFeCmIKzmhF0ItibZg&e=

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 16 Aug 2017, at 15:30, Jardine, Lawrence J <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Phil, we are not stuck with [maintaining] mapping tables:

In DB2® 11, support for the MAPPINGDATABASE keyword for the REORG TABLESPACE utility and the new REORG_MAPPING_DATABASE subsystem parameter helps to improve data availability.
Now, when the REORG utility processes a REORG TABLESPACE SHRLEVEL CHANGE request, it can create its own mapping table and mapping index instead of relying on user's input. The MAPPINGDATABASE keyword specifies the database in which REORG implicitly creates the mapping table and index objects. The REORG_MAPPING_DATABASE subsystem parameter specifies the default database that REORG TABLESPACE SHRLEVEL CHANGE uses to implicitly create the mapping table.
The MAPPINGDATABASE keyword overrides the subsystem parameter value in the REORG_MAPPING_DATABASE subsystem parameter.


Larry Jardine
Aetna

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Wednesday, August 16, 2017 6:32 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Unable to extend Mapping Index

Let us know how you get on Rakesh

I’d expected the mapping table to be a temporary ting when on-line reorgs first came about, but it looks like we are stuck with them. So not only do you need to adequately size the output dataset as the data is unloaded, the sortwork datasets as the data is sorted and the size of the resulting sort output dataset it seems that for large tables, the mapping index can also be a source of difficulty

Out of interest, could you remind me of the size of your table (row length and number of rows) as well as how many indexes?

-----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.
-----End Original Message-----


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

-----End Original Message-----

-----End Original Message-----
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

-----End Original Message-----

-----End Original Message-----
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

-----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 (8k)
  • image002.png (5.9k)