[Db2 V11 on z/OS] ALTER not working as expected

Philip Sevetson

[Db2 V11 on z/OS] ALTER not working as expected
**please note my email address change**
z/OS DBAs,

I'm working a problem at the moment. I ran DSNTEP2 to issue a series of ALTER statements to move the LIMITKEYs for a date-partitioned table (dates stored as integer value) forward, in test, so that I could copy more recent prod data into the table.


1) The DSNTEP2 appeared to succeed.

2) The subsequent LOAD REPLACE LOG NO NOCOPYPEND blew up, filling the max-date (MAXVALUES) partition in a way which it should not have done, if the LIMITKEYs had correctly advanced (we know this with certainty, as the production limitkeys are the same, the data comes from production, and does not come near exceeding max partition size in production).

3) On examination using the IBM DB2 Administration Tool and SPUFI against the catalog, the new/advanced date limitkeys were _not_ applied.

4) Using the DDL command in the IBM DB2 Admin tool yielded extremely strange results, with a large number of empty ENDING AT values, and one less than twice as many partitions as actually were created for the table. It might be coincidence that the number of entries was equal to the number of original partitions, plus the number of ALTERS which were issued.

I'm trying a REORG SHRLEVEL CHANGE, to see if satisfying the AREO* condition on the tablespace (because of the altered LIMITKEYs) will resolve the apparent problem. I'll keep people advised; if anyone knows what I'm doing wrong here, I'd appreciate a heads-up.

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]

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

  • image001.png (3.3k)

Philip Sevetson

[Db2 V11 on z/OS] ALTER not working as expected
(in response to Philip Sevetson)
**please note my email address change**
The REORG may have made the difference. Limit keys appear correct in the DB2 Admin Tool view of the catalog. Also, a second tablespace, which I have not yet REORGed, is displaying the same failure mode as the first one did-previously-but-no-longer-does.

**** Lesson: for DB2V11, in at least some circumstances, a LOAD REPLACE does NOT materialize partition limit key changes!!! ****

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: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, October 23, 2017 1:31 PM
To: [login to unmask email]
Subject: [DB2-L] - [Db2 V11 on z/OS] ALTER not working as expected

**please note my email address change**
z/OS DBAs,

I'm working a problem at the moment. I ran DSNTEP2 to issue a series of ALTER statements to move the LIMITKEYs for a date-partitioned table (dates stored as integer value) forward, in test, so that I could copy more recent prod data into the table.


1) The DSNTEP2 appeared to succeed.

2) The subsequent LOAD REPLACE LOG NO NOCOPYPEND blew up, filling the max-date (MAXVALUES) partition in a way which it should not have done, if the LIMITKEYs had correctly advanced (we know this with certainty, as the production limitkeys are the same, the data comes from production, and does not come near exceeding max partition size in production).

3) On examination using the IBM DB2 Administration Tool and SPUFI against the catalog, the new/advanced date limitkeys were _not_ applied.

4) Using the DDL command in the IBM DB2 Admin tool yielded extremely strange results, with a large number of empty ENDING AT values, and one less than twice as many partitions as actually were created for the table. It might be coincidence that the number of entries was equal to the number of original partitions, plus the number of ALTERS which were issued.

I'm trying a REORG SHRLEVEL CHANGE, to see if satisfying the AREO* condition on the tablespace (because of the altered LIMITKEYs) will resolve the apparent problem. I'll keep people advised; if anyone knows what I'm doing wrong here, I'd appreciate a heads-up.

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]

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

  • image001.png (3.3k)

Raymond Bell

[Db2 V11 on z/OS] ALTER not working as expected
(in response to Philip Sevetson)
Hey Phil

Sorry, been a while since I looked closely at this but the SQL Reference seems to suggest you need a Reorg to instantiate the part key alters. It says if you alter a specific partition, '...both the identified partition and the partition following it are placed in advisory REORG-pending (AREOR) status...'

I'm used to feeding the part key alters into the Reorg and have it do them as part of the Reorg. But as I've already had a shot across the bow I'll stop there., :o)

Bottom like looks like you're right. Load isn't enough to instantiate part key changes (unless the data can't move parts as a result of the alter); you need Reorg.

Cheers,


Raymond

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

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: 23 October 2017 18:43
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [Db2 V11 on z/OS] ALTER not working as expected


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

**please note my email address change**
The REORG may have made the difference. Limit keys appear correct in the DB2 Admin Tool view of the catalog. Also, a second tablespace, which I have not yet REORGed, is displaying the same failure mode as the first one did-previously-but-no-longer-does.

**** Lesson: for DB2V11, in at least some circumstances, a LOAD REPLACE does NOT materialize partition limit key changes!!! ****

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: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, October 23, 2017 1:31 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - [Db2 V11 on z/OS] ALTER not working as expected

**please note my email address change**
z/OS DBAs,

I'm working a problem at the moment. I ran DSNTEP2 to issue a series of ALTER statements to move the LIMITKEYs for a date-partitioned table (dates stored as integer value) forward, in test, so that I could copy more recent prod data into the table.


1) The DSNTEP2 appeared to succeed.

2) The subsequent LOAD REPLACE LOG NO NOCOPYPEND blew up, filling the max-date (MAXVALUES) partition in a way which it should not have done, if the LIMITKEYs had correctly advanced (we know this with certainty, as the production limitkeys are the same, the data comes from production, and does not come near exceeding max partition size in production).

3) On examination using the IBM DB2 Administration Tool and SPUFI against the catalog, the new/advanced date limitkeys were _not_ applied.

4) Using the DDL command in the IBM DB2 Admin tool yielded extremely strange results, with a large number of empty ENDING AT values, and one less than twice as many partitions as actually were created for the table. It might be coincidence that the number of entries was equal to the number of original partitions, plus the number of ALTERS which were issued.

I'm trying a REORG SHRLEVEL CHANGE, to see if satisfying the AREO* condition on the tablespace (because of the altered LIMITKEYs) will resolve the apparent problem. I'll keep people advised; if anyone knows what I'm doing wrong here, I'd appreciate a heads-up.

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]

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

  • image001.png (3.3k)

Peter Vanroose

Re: [Db2 V11 on z/OS] ALTER not working as expected
(in response to Raymond Bell)

And even more specifically: "you need REORG without SHRLEVEL NONE", i.e., you need an online reorg.

In Response to Raymond Bell's remark:

[...] Load isn't enough to instantiate part key changes (unless the data can't move parts as a result of the alter); you need Reorg. [...]

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Philip Sevetson

[Db2 V11 on z/OS] ALTER not working as expected
(in response to Peter Vanroose)
**please note my email address change**
Yes, exactly (to Peter and Raymond).

This is a change in behavior between DB2V10 and Db2V11. We have a lot of code which has been doing a series of ALTER/ENDING AT changes, followed by LOAD/REPLACE, to populate our test environments (that part of them with date-partitioned tables). It just-now started making recognizable trouble.

Fortunately, the process for doing that here relies on a single PROC (Altering the limits using generated SQL) and a bunch of IEBGENER/FAMVS replication of JCL to cover various testing environments. It’ll be easy to implement across our testing environments, once I have the “how to” for this figured out (ALTER; LOAD/EMPTY (to minimize REORG times, and we’re not using that data anyway); REORG SHRLEVEL REFERENCE; LOAD/REPLACE), with substitutions for all of the different tablespaces/tables at each step.

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: Peter Vanroose [mailto:[login to unmask email]
Sent: Tuesday, October 24, 2017 4:40 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [Db2 V11 on z/OS] ALTER not working as expected


And even more specifically: "you need REORG without SHRLEVEL NONE", i.e., you need an online reorg.

In Response to Raymond Bell's remark:
[...] Load isn't enough to instantiate part key changes (unless the data can't move parts as a result of the alter); you need Reorg. [...]

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
http://www.abis.be/ http://www.abis.be/html/enDB2Calendar.html

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

Raymond Bell

[Db2 V11 on z/OS] ALTER not working as expected
(in response to Philip Sevetson)
That’s odd, Phil. The V10 Admin Guide also says you need to run Reorg to ‘materialize’ (sp) any pending DDL changes. No reason Load Replace couldn’t also, and in your case it seems it was, but that’s not how it’s documented. Hmmm…

Oh well, at least you’ve only one common proc to change. Small mercies ‘n’ all that.

Cheers,


Raymond

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

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: 24 October 2017 14:00
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [Db2 V11 on z/OS] ALTER not working as expected


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

**please note my email address change**
Yes, exactly (to Peter and Raymond).

This is a change in behavior between DB2V10 and Db2V11. We have a lot of code which has been doing a series of ALTER/ENDING AT changes, followed by LOAD/REPLACE, to populate our test environments (that part of them with date-partitioned tables). It just-now started making recognizable trouble.

Fortunately, the process for doing that here relies on a single PROC (Altering the limits using generated SQL) and a bunch of IEBGENER/FAMVS replication of JCL to cover various testing environments. It’ll be easy to implement across our testing environments, once I have the “how to” for this figured out (ALTER; LOAD/EMPTY (to minimize REORG times, and we’re not using that data anyway); REORG SHRLEVEL REFERENCE; LOAD/REPLACE), with substitutions for all of the different tablespaces/tables at each step.

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: Peter Vanroose [mailto:[login to unmask email]
Sent: Tuesday, October 24, 2017 4:40 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [Db2 V11 on z/OS] ALTER not working as expected


And even more specifically: "you need REORG without SHRLEVEL NONE", i.e., you need an online reorg.

In Response to Raymond Bell's remark:
[...] Load isn't enough to instantiate part key changes (unless the data can't move parts as a result of the alter); you need Reorg. [...]

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
http://www.abis.be/ http://www.abis.be/html/enDB2Calendar.html

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

  • image001.png (3.3k)

James Campbell

[Db2 V11 on z/OS] ALTER not working as expected
(in response to Philip Sevetson)
PI42999 ?

James Campbell


On 23 Oct 2017 at 17:42, Sevetson, Phil wrote:

>
> **please note my email address change**
> The REORG may have made the difference.  Limit keys appear correct in the DB2 Admin
> Tool view of the catalog. Also, a second tablespace, which I have not yet REORGed, is
> displaying the same failure mode as the first one did-previously-but-no-longer-does.
>  
> **** Lesson: for DB2V11, in at least some circumstances, a LOAD REPLACE does
> NOT materialize partition limit key changes!!! ****
>  
> 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: Sevetson, Phil [mailto:[login to unmask email]
> Sent: Monday, October 23, 2017 1:31 PM
> To: [login to unmask email]
> Subject: [DB2-L] - [Db2 V11 on z/OS] ALTER not working as expected
>  
> **please note my email address change**
> z/OS DBAs,
>  
> I´m working a problem at the moment. I ran DSNTEP2 to issue a series of ALTER
> statements to move the LIMITKEYs for a date-partitioned table (dates stored as integer
> value) forward, in test, so that I could copy more recent prod data into the table.
>  
> 1)     The DSNTEP2 appeared to succeed.
> 2)     The subsequent LOAD REPLACE LOG NO NOCOPYPEND blew up, filling the max-date
> (MAXVALUES) partition in a way which it should not have done, if the LIMITKEYs had
> correctly advanced (we know this with certainty, as the production limitkeys are the
> same, the data comes from production, and does not come near exceeding max
> partition size in production).
> 3)     On examination using the IBM DB2 Administration Tool and SPUFI against the catalog, the
> new/advanced date limitkeys were _not_ applied.
> 4)     Using the DDL command in the IBM DB2 Admin tool yielded extremely strange results, with
> a large number of empty ENDING AT values, and one less than twice as many partitions
> as actually were created for the table.  It might be coincidence that the number of entries
> was equal to the number of original partitions, plus the number of ALTERS which were
> issued.
>  
> I´m trying a REORG SHRLEVEL CHANGE, to see if satisfying the AREO* condition on
> the tablespace (because of the altered LIMITKEYs) will resolve the apparent problem.  I´ll
> keep people advised; if anyone knows what I´m doing wrong here, I´d appreciate a
> heads-up.
>  
> 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]
>  

Peter Vanroose

Re: [Db2 V11 on z/OS] ALTER not working as expected
(in response to Raymond Bell)

The reason is that in version 10, altering a partition limit key value was not (yet) a pending change.

In Reply to Raymond Bell:

That’s odd, Phil. The V10 Admin Guide also says you need to run Reorg to ‘materialize’ (sp) any pending DDL changes. No reason Load Replace couldn’t also, and in your case it seems it was, but that’s not how it’s documented. Hmmm…

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Raymond Bell

[Db2 V11 on z/OS] ALTER not working as expected
(in response to Peter Vanroose)
A-ha, that explains that, then. :o)

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

From: Peter Vanroose [mailto:[login to unmask email]
Sent: 25 October 2017 11:32
To: [login to unmask email]
Subject: [DB2-L] - RE: [Db2 V11 on z/OS] ALTER not working as expected


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


The reason is that in version 10, altering a partition limit key value was not (yet) a pending change.

In Reply to Raymond Bell:
That’s odd, Phil. The V10 Admin Guide also says you need to run Reorg to ‘materialize’ (sp) any pending DDL changes. No reason Load Replace couldn’t also, and in your case it seems it was, but that’s not how it’s documented. Hmmm…

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
http://www.abis.be/ http://www.abis.be/html/enDB2Calendar.html

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

Philip Sevetson

[Db2 V11 on z/OS] ALTER not working as expected
(in response to Peter Vanroose)
**please note my email address change**
That’s going to be even more interesting.

1) Is ALTER PARTITION ROTATE FIRST TO LAST now a pending change as well?

2) Better question; is there a list of new pending changes in the technical overview somewhere?

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: Peter Vanroose [mailto:[login to unmask email]
Sent: Wednesday, October 25, 2017 6:32 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [Db2 V11 on z/OS] ALTER not working as expected


The reason is that in version 10, altering a partition limit key value was not (yet) a pending change.

In Reply to Raymond Bell:
That’s odd, Phil. The V10 Admin Guide also says you need to run Reorg to ‘materialize’ (sp) any pending DDL changes. No reason Load Replace couldn’t also, and in your case it seems it was, but that’s not how it’s documented. Hmmm…

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
http://www.abis.be/ http://www.abis.be/html/enDB2Calendar.html

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

Steen Rasmussen

[Db2 V11 on z/OS] ALTER not working as expected
(in response to Philip Sevetson)
ROTATE isn’t a PENDING change – at least not yet, but personally I hope everything will be pending in one way or the other so you have better control.

Steen

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Wednesday, October 25, 2017 8:07 AM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: [Db2 V11 on z/OS] ALTER not working as expected

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.
**please note my email address change**
That’s going to be even more interesting.

1. Is ALTER PARTITION ROTATE FIRST TO LAST now a pending change as well?
2. Better question; is there a list of new pending changes in the technical overview somewhere?

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: Peter Vanroose [mailto:[login to unmask email]
Sent: Wednesday, October 25, 2017 6:32 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [Db2 V11 on z/OS] ALTER not working as expected


The reason is that in version 10, altering a partition limit key value was not (yet) a pending change.

In Reply to Raymond Bell:
That’s odd, Phil. The V10 Admin Guide also says you need to run Reorg to ‘materialize’ (sp) any pending DDL changes. No reason Load Replace couldn’t also, and in your case it seems it was, but that’s not how it’s documented. Hmmm…

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
http://www.abis.be/ https://urldefense.proofpoint.com/v2/url?u=http-3A__www.abis.be_html_enDB2Calendar.html&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=hgSe4BZ7PXcwpTA9jeN9pEG-YTZcXR4Ubb_HRjNMVMI&s=xqtHEDtz6toUwDGgOKREYo5_qXGloe_w3hd2tIcZatg&e=

-----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.**
________________________________
Attachment Links: image001.png (3 k) https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D8867&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=hgSe4BZ7PXcwpTA9jeN9pEG-YTZcXR4Ubb_HRjNMVMI&s=5277ZI4nB76P9NGf-gLnrVbkEgYC1ogNFn_eXoOzfOA&e=
Site Links: View post online https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_st_-3Fpost-3D183472-26anc-3Dp183472-23p183472&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=hgSe4BZ7PXcwpTA9jeN9pEG-YTZcXR4Ubb_HRjNMVMI&s=IIyxMmqYOIRH7mwv05AKbQctI7ZZs8eiBjznaBEE6Nw&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=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=hgSe4BZ7PXcwpTA9jeN9pEG-YTZcXR4Ubb_HRjNMVMI&s=VcM0Hlx6p_roL0uuxBHE1iM-YsVaR71J3e-OzorwUZs&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=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=hgSe4BZ7PXcwpTA9jeN9pEG-YTZcXR4Ubb_HRjNMVMI&s=SDPzhJ3JzXDbIt0DssmcJHXOtFrehikT0rMRIk3ySDg&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
http://www.ESAIGroup.com/idug https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ESAIGroup.com_idug&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=hgSe4BZ7PXcwpTA9jeN9pEG-YTZcXR4Ubb_HRjNMVMI&s=JxVt1PJgC_FTKs4R33Lq2FxnF9Hp6GTD73MUzHW8yQ8&e=

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=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=hgSe4BZ7PXcwpTA9jeN9pEG-YTZcXR4Ubb_HRjNMVMI&s=AD_gwBQxNGNcgll1fOf2y-fkzUfKmfYcr7Z85jGVHKs&e=

________________________________

Peter Vanroose

Re: [Db2 V11 on z/OS] ALTER not working as expected
(in response to Philip Sevetson)

No, that's the only existing ALTER that became a pending change when migrating to version 11.

The only other (but new) pending change in Db2 11 is "drop column".

See https://www.ibm.com/support/knowledgecenter/SSEPEK/pdf/db2z_11_wnewbook.pdf

In Reply to Philip Sevetson:

1) Is ALTER PARTITION ROTATE FIRST TO LAST now a pending change as well?
2) Better question; is there a list of new pending changes in the technical overview somewhere?

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/