Cost of creating mapping objects by DB2 in REORG utility

Ebubekir Büyüktosun

Cost of creating mapping objects by DB2 in REORG utility

Hello all,

In our systems, scheduled REORG batch jobs with SHARELEVEL CHANGE option have MAPPINGTABLE option. Given mapping tables in the option have been already created in a specific database. So, these jobs always use the objects we have created before as mapping objects.

Except the scheduled REORG batch jobs, we also have reorg jobs that run when we need. Structure of the jobs can be figured as below:

STEP 1: CREATE mapping objects

STEP 2: REORG

STEP 3: DROP mapping objects 

Sometimes that can cause problems in our reorg jobs. For example, when such a reorg batch job fails and gets an abend in reorg step, we manually drop the mapping objects that have already being created in previous step OR remove the first step depending on the situation to run the job again. So, these operations are managed by us. 

As you know, with DB2 V11 the mapping objects can be internally created by DB2 in REORG utilities. We are interested in the feature and thinking of applying it for all our REORG jobs.

Therefore, we made some tests with usage of the feature with the option MAPPINGDATABASE dbname. We display the objects created by DB2 in the database we specified while running reorg and then the reorg was completed successfully. We observed the mapping objects have been automatically dropped by DB2. It was good. Secondly, we tested the scenario that is to fail and get abend in reorg process. After the case, we terminate the utility and then we again observed the mapping objects have been automatically dropped by DB2. It was also good.

However, we have a little concern about usage of the feature. We heard that giving the management of mapping objects to DB2 in REORG utilities can cause some contentions in DB2 catalog.

Do you think that the feature can cause any problem for our current REORG mechanisms ?

I couldn't find any related post with the case in IDUG. So, you can also redirect me if any exists.

Horacio Villa

Cost of creating mapping objects by DB2 in REORG utility
(in response to Ebubekir Büyüktosun)
Hi,

what I saw was that all the objects were dropped except the database. And
next time it creates a new one, and so forth.

Horacio Villa

don isenstadt

Cost of creating mapping objects by DB2 in REORG utility
(in response to Horacio Villa)
This can easily be solved with a zparm directive:
REORG_MAPPING_DATABASE=yourcommondatabase!





From: Horacio Villa <[login to unmask email]>
To: [login to unmask email]
Date: 10/01/2019 02:50 PM
Subject: [EXT] [DB2-L] - RE: Cost of creating mapping objects by
DB2 in REORG utility



Hi,

what I saw was that all the objects were dropped except the database. And
next time it creates a new one, and so forth.

Horacio Villa


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]
Discover the best cloning tool on the market. Try BCV5 & the new BCV5
Masking Tool.
ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis,
TDM, & more.
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




llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll
"PLEASE NOTE: The preceding information may be confidential or privileged. It only should be used or disseminated for the purpose of conducting business with Parker. If you are not an intended recipient, please notify the sender by replying to this message and then delete the information from your system. Thank you for your cooperation. Parker Hannifin Corporation and its subsidiaries, affiliates and associated companies ("Parker") process personal data in accordance with Parker’s Personal Data Privacy Policy which may be accessed from www.parker.com"

Ebubekir B&#252;y&#252;ktosun

RE: Cost of creating mapping objects by DB2 in REORG utility
(in response to don isenstadt)

Horacio and Don,

Thanks for your replies. I completely agree with your comments. My concern is any possible catalog contention due to creating the objects by DB2.

Thanks,

Ebubekir

Roy Boxwell

Cost of creating mapping objects by DB2 in REORG utility
(in response to Ebubekir Büyüktosun)
It definitely was a problem in Db2 11 – I haven’t heard of it in Db2 12 ...



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


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



From: Ebubekir Büyüktosun [mailto:[login to unmask email]
Sent: Wednesday, October 2, 2019 8:12 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility



Horacio and Don,

Thanks for your replies. I completely agree with your comments. My concern is any possible catalog contention due to creating the objects by DB2.

Thanks,

Ebubekir



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

Attachments

  • smime.p7s (5.1k)

Ebubekir B&#252;y&#252;ktosun

RE: Cost of creating mapping objects by DB2 in REORG utility
(in response to Roy Boxwell)

Thanks Roy. So, some improvements have been done for the feature with V12, right ?

Roy Boxwell

Cost of creating mapping objects by DB2 in REORG utility
(in response to Ebubekir Büyüktosun)
I think the major catalog contention problems were “solved”

I am still a big fan of hard coded mapping tables (up to 4000 of ‘em)

They take no space (remember the INDEX is used not the tablespace!)

They annoy no-one

They cause no logging or catalog updates

Of course you must have standards and software that sticks to the standards but once you are there I think it is better than CREATE/DROP CREATE/DROP... I simply cannot believe that that can be good for the catalog when you do over 5000 a night....for the odd one off “special” ok ok but for normal REORGs... nope... stick with static!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


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



From: Ebubekir Büyüktosun [mailto:[login to unmask email]
Sent: Wednesday, October 2, 2019 9:36 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility



Thanks Roy. So, some improvements have been done for the feature with V12, right ?



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

Attachments

  • smime.p7s (5.1k)

Raymond Bell

Cost of creating mapping objects by DB2 in REORG utility
(in response to Roy Boxwell)
Even better than fixed mapping tables, why not use a reorg utility that doesn’t need them at all? No need for standards to manage objects that don’t exist. You don’t have to make sure every new table has a mapping table ready and waiting for it, uniquely, so you can run parallel reorgs using different mapping tables – or, like one site I was at, have a bit of hand-cranked REXX to assign the next available mapping table to the utility. And when you decom’ objects you don’t have to remember to remove their mapping table too.

Gee, I wonder if there is such a utility out there somewhere…

Cheers,


Raymond
PS: Paypal account is the same as it always was, Frank. ;o)

From: Boxwell, Roy <[login to unmask email]>
Sent: 02 October 2019 09:15
To: [login to unmask email]
Subject: [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility

I think the major catalog contention problems were “solved”
I am still a big fan of hard coded mapping tables (up to 4000 of ‘em)
They take no space (remember the INDEX is used not the tablespace!)
They annoy no-one
They cause no logging or catalog updates
Of course you must have standards and software that sticks to the standards but once you are there I think it is better than CREATE/DROP CREATE/DROP... I simply cannot believe that that can be good for the catalog when you do over 5000 a night....for the odd one off “special” ok ok but for normal REORGs... nope... stick with static!

Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
Web http://www.seg.de http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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

From: Ebubekir Büyüktosun [mailto:[login to unmask email]
Sent: Wednesday, October 2, 2019 9:36 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility


Thanks Roy. So, some improvements have been done for the feature with V12, right ?

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. 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 understand that the content of your message may be monitored.

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

Marcus Davage

Cost of creating mapping objects by DB2 in REORG utility
(in response to Raymond Bell)
You can take the man out of the vendor, but you can’t take the vendor out of the man…

Regards,
Marcus Davage
Lead Product Developer
AMI-DevOps for Db2 – SQL Performance
Direct

+44 118 921 8517

[cid:[login to unmask email]



Mobile

+44 7840 023 560



Email

[login to unmask email]<mailto:[login to unmask email]>





From: Bell, Raymond (Hosting Services, Technology) <[login to unmask email]>
Sent: 02 October 2019 09:33
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility

Even better than fixed mapping tables, why not use a reorg utility that doesn’t need them at all? No need for standards to manage objects that don’t exist. You don’t have to make sure every new table has a mapping table ready and waiting for it, uniquely, so you can run parallel reorgs using different mapping tables – or, like one site I was at, have a bit of hand-cranked REXX to assign the next available mapping table to the utility. And when you decom’ objects you don’t have to remember to remove their mapping table too.

Gee, I wonder if there is such a utility out there somewhere…

Cheers,


Raymond
PS: Paypal account is the same as it always was, Frank. ;o)
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

  • image002.png (12.8k)

Ebubekir B&#252;y&#252;ktosun

RE: Cost of creating mapping objects by DB2 in REORG utility
(in response to Raymond Bell)

Thanks Raymond, our scheduled REORG jobs already run as your suggestion controlled by a rexx in parallel. 

So, i see that for the scheduled REORG jobs that cover so many objects for a "night" it is better to control mapping objects manually, and for other special REORGs DB2-controlled mapping object management can be used.

Thanks all for your interest.

 

 

Raymond Bell

Cost of creating mapping objects by DB2 in REORG utility
(in response to Ebubekir Büyüktosun)
Ha, I didn’t suggest it; I suggested you avoid it! But if it works for you, so be it. :o)

Cheers,


Raymond

From: Ebubekir Büyüktosun <[login to unmask email]>
Sent: 02 October 2019 11:33
To: [login to unmask email]
Subject: [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility


*********************************************
"This is an external email. Do you know who has sent it? Can you be sure that any links and attachments contained within it are safe? If in any doubt, use the Phishing Reporter Button in your Outlook client or forward the email as an attachment to ~ I've Been Phished"
*********************************************

Thanks Raymond, our scheduled REORG jobs already run as your suggestion controlled by a rexx in parallel.

So, i see that for the scheduled REORG jobs that cover so many objects for a "night" it is better to control mapping objects manually, and for other special REORGs DB2-controlled mapping object management can be used.

Thanks all for your interest.





-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. 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 understand that the content of your message may be monitored.

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

Russell Peters

RE: Cost of creating mapping objects by DB2 in REORG utility
(in response to Ebubekir Büyüktosun)

We've been letting db2 create the mapping tables since the option became available and have had no issues. We did have some contention issues when we were doing the same as you were doing prior to letting db2 do it: create objects, reorg, drop objects.

Ebubekir B&#252;y&#252;ktosun

RE: Cost of creating mapping objects by DB2 in REORG utility
(in response to Raymond Bell)

Raymond, i think a little bit misunderstanding between us.

From your reply, i got that running reorgs in parallel using different mapping tables that have been already created before is a good way. And to create and drop them each time is a bad way. That means not to give creation and drop operations of mapping objects to DB2. Because if we give the management of mapping tables to DB2 (i mean here, coding MAPPINGDATABASE OR nothing as mapping options in reorg utility control statements), they will be automatically created and dropped by DB2. And, this can be some contentions in catalog in cases that so many jobs run in a short period. Think about a lot of CREATE-DROP, CREATE-DROP, CREATE-DROP, CREATE-DROP... operations in short intervals.

This is also Roy's opinion.

Please, warn me if i am misunderstanding you.

Thanks.

Phil Grainger

Cost of creating mapping objects by DB2 in REORG utility
(in response to Ebubekir Büyüktosun)
I’m still amazed that even in Db2 12 we have to worry about mapping tables/indexes

If only there was another way

--

Oh wait – there IS

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: Ebubekir Büyüktosun <[login to unmask email]>
Sent: 02 October 2019 18:25
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility


Raymond, i think a little bit misunderstanding between us.

From your reply, i got that running reorgs in parallel using different mapping tables that have been already created before is a good way. And to create and drop them each time is a bad way. That means not to give creation and drop operations of mapping objects to DB2. Because if we give the management of mapping tables to DB2 (i mean here, coding MAPPINGDATABASE OR nothing as mapping options in reorg utility control statements), they will be automatically created and dropped by DB2. And, this can be some contentions in catalog in cases that so many jobs run in a short period. Think about a lot of CREATE-DROP, CREATE-DROP, CREATE-DROP, CREATE-DROP... operations in short intervals.

This is also Roy's opinion.

Please, warn me if i am misunderstanding you.

Thanks.

-----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)
  • image002.png (6.7k)
  • image003.jpg (1.6k)
  • image004.png (<1k)

James Campbell

Cost of creating mapping objects by DB2 in REORG utility
(in response to Ebubekir Büyüktosun)
Perhaps I am misunderstanding something.

If you set DSN6SPRM.REORG_MAPPING_DATABASE to blank, doesn't "REORG use[] an
implicitly defined database for the mapping table allocation." And doesn't that database cycle
from DSN00001 to DSN00002 to .... DSN10000 (unless you've changed those numbers).

So unless you are running 10,000 REORGs (or something else that uses an implicit
database) at the same time you are not going to get database level contention.

Yes? No?

And, yes, the databases themselves are not dropped. Is that such a terrible thing? Well, I
suppose if you run one of the ERPs you might run out out of DBDIDs. In which set the high
number to, say, 1000.

If, of course, you set REORG_MAPPING_DATABASE=MYMAPDB then of course you'll get
contention.

James Campbell


On 2 Oct 2019 at 10:24, Ebubekir Büyüktosun wrote:

> Raymond, i think a little bit misunderstanding between us.
> From your reply, i got that running reorgs in parallel using different mapping tables that have been already created before is a good way. And to create and drop them each time is a bad way. That means not to give creation and drop operations of mapping objects to DB2. Because if we give the management of mapping tables to DB2 (i mean here, coding MAPPINGDATABASE OR nothing as mapping options in reorg utility control statements), they will be automatically created and dropped by DB2. And, this can be some contentions in catalog in cases that so many jobs run in a short period. Think about a lot of CREATE-DROP, CREATE-DROP, CREATE-DROP, CREATE-DROP... operations in short intervals.
> This is also Roy's opinion.
> Please, warn me if i am misunderstanding you.
> Thanks.
>

--
This email has been checked for viruses by AVG.
https://www.avg.com

Raymond Bell

Cost of creating mapping objects by DB2 in REORG utility
(in response to Phil Grainger)
Ok, so I’ll try to be clear.

Having Db2 dynamically create new mapping tables into new DBs every time you do a reorg is not great, for reasons already covered. Having Db2 use a specific DB and dynamically create mapping tables there instead is also ‘not great’ for similar reasons. Telling Db2 which pre-created mapping table to use is better, if you can be @rsed with the hassle of managing them all. But the best solution in my opinion is to do away with mapping tables altogether and use an ISV-supplied Reorg utility that doesn’t need them. There’s at least one such product out there I’m aware of; other solutions may be available.

Cheers,


Raymond

From: Grainger, Phil <[login to unmask email]>
Sent: 02 October 2019 20:00
To: [login to unmask email]
Subject: [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility


*********************************************
"This is an external email. Do you know who has sent it? Can you be sure that any links and attachments contained within it are safe? If in any doubt, use the Phishing Reporter Button in your Outlook client or forward the email as an attachment to ~ I've Been Phished"
*********************************************
I’m still amazed that even in Db2 12 we have to worry about mapping tables/indexes

If only there was another way

--

Oh wait – there IS

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]<mailto:[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: Ebubekir Büyüktosun <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 02 October 2019 18:25
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility


Raymond, i think a little bit misunderstanding between us.

From your reply, i got that running reorgs in parallel using different mapping tables that have been already created before is a good way. And to create and drop them each time is a bad way. That means not to give creation and drop operations of mapping objects to DB2. Because if we give the management of mapping tables to DB2 (i mean here, coding MAPPINGDATABASE OR nothing as mapping options in reorg utility control statements), they will be automatically created and dropped by DB2. And, this can be some contentions in catalog in cases that so many jobs run in a short period. Think about a lot of CREATE-DROP, CREATE-DROP, CREATE-DROP, CREATE-DROP... operations in short intervals.

This is also Roy's opinion.

Please, warn me if i am misunderstanding you.

Thanks.

-----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-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. 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 understand that the content of your message may be monitored.

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
Attachments

  • image005.jpg (49.7k)
  • image006.png (6.7k)
  • image007.jpg (1.6k)
  • image008.png (<1k)

Ebubekir B&#252;y&#252;ktosun

RE: Cost of creating mapping objects by DB2 in REORG utility
(in response to James Campbell)

Thanks James,

Actually, our discussion here is possible contentions over DB2 catalog objects due to creating and droping mapping objects frequently (If DB2 controls creation and drop operations of mapping tables and indexes, possible contention can appear) for a bulk reorg job.

By the way, in our scheduled REORG jobs currently REORG_MAPPING_DATABASE is blank and MAPPINGTABLE is coded and the tables have been already created. This structure are managed by an rexx that is an inhouse solution.

Ebubekir,

In Reply to James Campbell:

Perhaps I am misunderstanding something.

If you set DSN6SPRM.REORG_MAPPING_DATABASE to blank, doesn't "REORG use[] an
implicitly defined database for the mapping table allocation." And doesn't that database cycle
from DSN00001 to DSN00002 to .... DSN10000 (unless you've changed those numbers).

So unless you are running 10,000 REORGs (or something else that uses an implicit
database) at the same time you are not going to get database level contention.

Yes? No?

And, yes, the databases themselves are not dropped. Is that such a terrible thing? Well, I
suppose if you run one of the ERPs you might run out out of DBDIDs. In which set the high
number to, say, 1000.

If, of course, you set REORG_MAPPING_DATABASE=MYMAPDB then of course you'll get
contention.

James Campbell


On 2 Oct 2019 at 10:24, Ebubekir Büyüktosun wrote:

> Raymond, i think a little bit misunderstanding between us.
> From your reply, i got that running reorgs in parallel using different mapping tables that have been already created before is a good way. And to create and drop them each time is a bad way. That means not to give creation and drop operations of mapping objects to DB2. Because if we give the management of mapping tables to DB2 (i mean here, coding MAPPINGDATABASE OR nothing as mapping options in reorg utility control statements), they will be automatically created and dropped by DB2. And, this can be some contentions in catalog in cases that so many jobs run in a short period. Think about a lot of CREATE-DROP, CREATE-DROP, CREATE-DROP, CREATE-DROP... operations in short intervals.
> This is also Roy's opinion.
> Please, warn me if i am misunderstanding you.
> Thanks.
>

--
This email has been checked for viruses by AVG.
https://www.avg.com

Ebubekir B&#252;y&#252;ktosun

RE: Cost of creating mapping objects by DB2 in REORG utility
(in response to Raymond Bell)

Raymond, i am very interested in your comments.

You said "the best solution in my opinion is to do away with mapping tables altogether and use an ISV-supplied Reorg utility that doesn’t need them."

How is possible not to use any mapping table with SHRLEVEL CHANGE option ?

Ebubekir

In Reply to Raymond Bell:

Ok, so I’ll try to be clear.

Having Db2 dynamically create new mapping tables into new DBs every time you do a reorg is not great, for reasons already covered. Having Db2 use a specific DB and dynamically create mapping tables there instead is also ‘not great’ for similar reasons. Telling Db2 which pre-created mapping table to use is better, if you can be @rsed with the hassle of managing them all. But the best solution in my opinion is to do away with mapping tables altogether and use an ISV-supplied Reorg utility that doesn’t need them. There’s at least one such product out there I’m aware of; other solutions may be available.

Cheers,


Raymond

From: Grainger, Phil <[login to unmask email]>
Sent: 02 October 2019 20:00
To: [login to unmask email]
Subject: [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility


*********************************************
"This is an external email. Do you know who has sent it? Can you be sure that any links and attachments contained within it are safe? If in any doubt, use the Phishing Reporter Button in your Outlook client or forward the email as an attachment to ~ I've Been Phished"
*********************************************
I’m still amazed that even in Db2 12 we have to worry about mapping tables/indexes

If only there was another way

--

Oh wait – there IS

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]<mailto:[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: Ebubekir Büyüktosun <[login to unmask email]<mailto:[login to unmask email]>>
Sent: 02 October 2019 18:25
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [EXTERNAL] [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility


Raymond, i think a little bit misunderstanding between us.

From your reply, i got that running reorgs in parallel using different mapping tables that have been already created before is a good way. And to create and drop them each time is a bad way. That means not to give creation and drop operations of mapping objects to DB2. Because if we give the management of mapping tables to DB2 (i mean here, coding MAPPINGDATABASE OR nothing as mapping options in reorg utility control statements), they will be automatically created and dropped by DB2. And, this can be some contentions in catalog in cases that so many jobs run in a short period. Think about a lot of CREATE-DROP, CREATE-DROP, CREATE-DROP, CREATE-DROP... operations in short intervals.

This is also Roy's opinion.

Please, warn me if i am misunderstanding you.

Thanks.

-----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-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. 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 understand that the content of your message may be monitored.

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

Raymond Bell

Cost of creating mapping objects by DB2 in REORG utility
(in response to Ebubekir Büyüktosun)
Hi Ebubekir,

That one’s probably best answered directly by the ISV I have in mind but the short answer is the utility does it all in-memory.

Cheers,


Raymond

From: Ebubekir Büyüktosun <[login to unmask email]>
Sent: 03 October 2019 09:05
To: [login to unmask email]
Subject: [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility


*********************************************
"This is an external email. Do you know who has sent it? Can you be sure that any links and attachments contained within it are safe? If in any doubt, use the Phishing Reporter Button in your Outlook client or forward the email as an attachment to ~ I've Been Phished"
*********************************************

Raymond, i am very interested in your comments.

You said "the best solution in my opinion is to do away with mapping tables altogether and use an ISV-supplied Reorg utility that doesn’t need them."

How is possible not to use any mapping table with SHRLEVEL CHANGE option ?

Ebubekir

In Reply to Raymond Bell:
Ok, so I’ll try to be clear.

Having Db2 dynamically create new mapping tables into new DBs every time you do a reorg is not great, for reasons already covered. Having Db2 use a specific DB and dynamically create mapping tables there instead is also ‘not great’ for similar reasons. Telling Db2 which pre-created mapping table to use is better, if you can be @rsed with the hassle of managing them all. But the best solution in my opinion is to do away with mapping tables altogether and use an ISV-supplied Reorg utility that doesn’t need them. There’s at least one such product out there I’m aware of; other solutions may be available.

Cheers,


Raymond

From: Grainger, Phil
Sent: 02 October 2019 20:00
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility


*********************************************
"This is an external email. Do you know who has sent it? Can you be sure that any links and attachments contained within it are safe? If in any doubt, use the Phishing Reporter Button in your Outlook client or forward the email as an attachment to ~ I've Been Phished"
*********************************************
I’m still amazed that even in Db2 12 we have to worry about mapping tables/indexes

If only there was another way

--

Oh wait – there IS

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]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>

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] https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png%5d



From: Ebubekir Büyüktosun <[login to unmask email]<mailto:[login to unmask email]>><mailto:[login to unmask email]%3e%3e>
Sent: 02 October 2019 18:25
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [EXTERNAL] [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility


Raymond, i think a little bit misunderstanding between us.

From your reply, i got that running reorgs in parallel using different mapping tables that have been already created before is a good way. And to create and drop them each time is a bad way. That means not to give creation and drop operations of mapping objects to DB2. Because if we give the management of mapping tables to DB2 (i mean here, coding MAPPINGDATABASE OR nothing as mapping options in reorg utility control statements), they will be automatically created and dropped by DB2. And, this can be some contentions in catalog in cases that so many jobs run in a short period. Think about a lot of CREATE-DROP, CREATE-DROP, CREATE-DROP, CREATE-DROP... operations in short intervals.

This is also Roy's opinion.

Please, warn me if i am misunderstanding you.

Thanks.

-----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-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. 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 understand that the content of your message may be monitored.

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 http://www.rbs.com

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. 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 understand that the content of your message may be monitored.

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

Phil Grainger

Cost of creating mapping objects by DB2 in REORG utility
(in response to Ebubekir Büyüktosun)
ONLY IBM require a dedicated mapping table to map old RIDs to new ones as rows are moved around by the reorg

“Everyone else” does the same mapping internally as part of the reorg. The exact mechanisms are proprietary, but to paraphrase Raymond “the mapping is done in memory rather than a dedicated mapping table/index”

I know Raymond is thinking of NGT Reorg (BMC), but I am pretty sure Broadcoms Reorg Plus doesn’t need a mapping table either

Which makes IBM kind of the odd one out

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: Ebubekir Büyüktosun <[login to unmask email]>
Sent: 03 October 2019 09:05
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility


Raymond, i am very interested in your comments.

You said "the best solution in my opinion is to do away with mapping tables altogether and use an ISV-supplied Reorg utility that doesn’t need them."

How is possible not to use any mapping table with SHRLEVEL CHANGE option ?

Ebubekir

In Reply to Raymond Bell:
Ok, so I’ll try to be clear.

Having Db2 dynamically create new mapping tables into new DBs every time you do a reorg is not great, for reasons already covered. Having Db2 use a specific DB and dynamically create mapping tables there instead is also ‘not great’ for similar reasons. Telling Db2 which pre-created mapping table to use is better, if you can be @rsed with the hassle of managing them all. But the best solution in my opinion is to do away with mapping tables altogether and use an ISV-supplied Reorg utility that doesn’t need them. There’s at least one such product out there I’m aware of; other solutions may be available.

Cheers,


Raymond

From: Grainger, Phil
Sent: 02 October 2019 20:00
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility


*********************************************
"This is an external email. Do you know who has sent it? Can you be sure that any links and attachments contained within it are safe? If in any doubt, use the Phishing Reporter Button in your Outlook client or forward the email as an attachment to ~ I've Been Phished"
*********************************************
I’m still amazed that even in Db2 12 we have to worry about mapping tables/indexes

If only there was another way

--

Oh wait – there IS

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]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>

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] https://urldefense.proofpoint.com/v2/url?u=https-3A__acclaim-2Dproduction-2Dapp.s3.amazonaws.com_images_2429c3cd-2Da1de-2D44fc-2Db4f3-2Dbc762bb2f963_IBM-252BChampion-252B-2D-252BAnalytics-252B2018.png-5D&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=208bRxZFQwGlFDTuzCCc1CAdB6Y_DYqAHjM5VYUFt14&s=ptsp03gr3JuSn82Q8uBAnLWfVmt7NiycAV1eCTuGOao&e=



From: Ebubekir Büyüktosun <[login to unmask email]<mailto:[login to unmask email]>><mailto:[login to unmask email]%3e%3e>
Sent: 02 October 2019 18:25
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [EXTERNAL] [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility


Raymond, i think a little bit misunderstanding between us.

From your reply, i got that running reorgs in parallel using different mapping tables that have been already created before is a good way. And to create and drop them each time is a bad way. That means not to give creation and drop operations of mapping objects to DB2. Because if we give the management of mapping tables to DB2 (i mean here, coding MAPPINGDATABASE OR nothing as mapping options in reorg utility control statements), they will be automatically created and dropped by DB2. And, this can be some contentions in catalog in cases that so many jobs run in a short period. Think about a lot of CREATE-DROP, CREATE-DROP, CREATE-DROP, CREATE-DROP... operations in short intervals.

This is also Roy's opinion.

Please, warn me if i am misunderstanding you.

Thanks.

-----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-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. 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 understand that the content of your message may be monitored.

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 http://www.rbs.com https://urldefense.proofpoint.com/v2/url?u=http-3A__www.rbs.com&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=208bRxZFQwGlFDTuzCCc1CAdB6Y_DYqAHjM5VYUFt14&s=2mKQLMTtXR6Cfny02UA_ksebomxNTDXzuvy322tQq5Q&e=

-----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)
  • image002.png (6.7k)
  • image003.jpg (1.6k)
  • image004.png (<1k)

Russell Peters

RE: Cost of creating mapping objects by DB2 in REORG utility
(in response to James Campbell)

I guess I don't understand all the concerns about letting db2 create/drop the mapping tables. We've been doing it for years with absolutely no issues. I do specify a reorg database in zparm. We run most of our reorgs on Sunday morning, with about six running concurrently controlled by the number of initiators available. We did have some contention issues back when we were creating/dropping the mapping tables ourselves but after we changed to let db2 manage it we've had zero issues and zero concerns. But we're not data sharing either so maybe that causes some contention issues?

Daniel Luksetich

Cost of creating mapping objects by DB2 in REORG utility
(in response to Russell Peters)
Same experience here, but with data sharing.

Dan



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Russell Peters <[login to unmask email]>
Sent: Thursday, October 3, 2019 7:40 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Cost of creating mapping objects by DB2 in REORG utility



I guess I don't understand all the concerns about letting db2 create/drop the mapping tables. We've been doing it for years with absolutely no issues. I do specify a reorg database in zparm. We run most of our reorgs on Sunday morning, with about six running concurrently controlled by the number of initiators available. We did have some contention issues back when we were creating/dropping the mapping tables ourselves but after we changed to let db2 manage it we've had zero issues and zero concerns. But we're not data sharing either so maybe that causes some contention issues?



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

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

Ebubekir B&#252;y&#252;ktosun

RE: Cost of creating mapping objects by DB2 in REORG utility
(in response to Russell Peters)

Russell,

How many object do you reorg on Sunday mornings, approximately ?


In Reply to Russell Peters:

I guess I don't understand all the concerns about letting db2 create/drop the mapping tables. We've been doing it for years with absolutely no issues. I do specify a reorg database in zparm. We run most of our reorgs on Sunday morning, with about six running concurrently controlled by the number of initiators available. We did have some contention issues back when we were creating/dropping the mapping tables ourselves but after we changed to let db2 manage it we've had zero issues and zero concerns. But we're not data sharing either so maybe that causes some contention issues?

Russell Peters

RE: Cost of creating mapping objects by DB2 in REORG utility
(in response to Ebubekir Büyüktosun)

We still use the old OFFPOSLIMIT and INDREFLIMIT tablespace parameters and LEAFDISTLIMIT index parameter to let the reorg utility decide whether a reorg is needed. Per SYSTABLESPACESTATS and SYSINDEXSPACESTATS, 213 tablespaces and 756 indexes were reorg'd last Sunday. Each reorg jobs lists the database as wildcarded so as to pick up all tablespaces (like AAADB.*), then we run another step to look at just the indexes and reorg those if needed. We run the index reorg because the tablespace reorg only looks at stats for the tablespace. We often find that although the tablespace is ok, the indexes may need a reorg.