ALTER PART / ALTER PARTITION

Philip Sevetson

ALTER PART / ALTER PARTITION
I think I just stumbled across a truly weird syntax glitch in DB2V11 on z. Follow along as I attempt to change a limit key on a partitioned table (successes are cancelled with ROLLBACK so that I can repeat the experiment).

****First attempt (fail):

ALTER TABLE TRTRRETH00 ALTER PART 1 ENDING AT ('1986-01-31');
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD ENDING. TOKEN VALUES
WAS EXPECTED

****Second attempt (success):

ALTER TABLE TRTRRETH00 ALTER PART 1 VALUES ('1986-01-31');
---------+---------+---------+---------+---------+---------+---------+-------
DSNT404I SQLCODE = 610, WARNING: A CREATE/ALTER ON OBJECT DB2FDB.TRTRRETH00
HAS PLACED OBJECT IN ADVISORY REORG PENDING
DSNT418I SQLSTATE = 01566 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXI14 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 250 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000000FA' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

****Third attempt (fail):

ALTER TABLE TRTRRETH00 ALTER PARTITION 1 VALUES ('1986-01-31');
---------+---------+---------+---------+---------+---------+---------+--------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD VALUES. TOKEN ENDING
HASH WAS EXPECTED

****Fourth attempt (success):

ALTER TABLE TRTRRETH00 ALTER PARTITION 1 ENDING AT ('1986-01-31');
---------+---------+---------+---------+---------+---------+---------+------
DSNT404I SQLCODE = 610, WARNING: A CREATE/ALTER ON OBJECT DB2FDB.TRTRRETH00
HAS PLACED OBJECT IN ADVISORY REORG PENDING
DSNT418I SQLSTATE = 01566 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXI14 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 250 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000000FA' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

It seems that, if you ALTER TABLE and use PARTITION, you must use "ENDING AT", not "VALUES". But if you ALTER TABLE and use PART, you must use "VALUES", not "ENDING AT".

Is there a subtlety which I'm missing here? This distinction, and incompatible syntax, seems quite wrong to me. PARTITION and PART ought to be synonyms, and have the same clauses. VALUES and ENDING AT ought to be synonyms, as well.

--Phil Sevetson
Puzzled DB2 DBA
**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.**

Raymond Bell

ALTER PART / ALTER PARTITION
(in response to Philip Sevetson)
Phil,

If I were a betting man, I'd have probably lost a fiver on betting on England getting knocked out on penalty shoot-outs. But I digress.

If I were a betting man, I'd say different areas within the Labs developed the Alter Table syntax. One looked in the SQL Reference for Create Table(space) syntax and used that, one just... did something different. Why? Because humans were involved and there's neither rhyme nor reason as to why folks do what they do. :o)

When I was at BMC, a friend at another company once told me how one of their customers complained to him about some system default value being set to, say, 6, for no apparent reason. This caused them issues, so when back at the Lab my friend asked the developer why he chose 6 for the number he said, 'No reason; it just popped into my head'. Good grief.

There's nowt so queer as folk. :o)

Cheers,


Raymond

Raymond Bell
Db2
Hosting Services, Technology
Royal Bank of Scotland Group
3rd Floor Regents House
40-42 Islington High Street
London N1 8XL
Mob: +44 (0) 7894 608214
Email: [login to unmask email]<mailto:[login to unmask email]>

The content of this email is confidential unless stated otherwise.
[cid:[login to unmask email]

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: 03 July 2018 17:13
To: [login to unmask email]
Subject: [DB2-L] - ALTER PART / ALTER PARTITION

I think I just stumbled across a truly weird syntax glitch in DB2V11 on z. Follow along as I attempt to change a limit key on a partitioned table (successes are cancelled with ROLLBACK so that I can repeat the experiment).

****First attempt (fail):

ALTER TABLE TRTRRETH00 ALTER PART 1 ENDING AT ('1986-01-31');
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD ENDING. TOKEN VALUES
WAS EXPECTED

****Second attempt (success):

ALTER TABLE TRTRRETH00 ALTER PART 1 VALUES ('1986-01-31');
---------+---------+---------+---------+---------+---------+---------+-------
DSNT404I SQLCODE = 610, WARNING: A CREATE/ALTER ON OBJECT DB2FDB.TRTRRETH00
HAS PLACED OBJECT IN ADVISORY REORG PENDING
DSNT418I SQLSTATE = 01566 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXI14 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 250 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000000FA' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

****Third attempt (fail):

ALTER TABLE TRTRRETH00 ALTER PARTITION 1 VALUES ('1986-01-31');
---------+---------+---------+---------+---------+---------+---------+--------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD VALUES. TOKEN ENDING
HASH WAS EXPECTED

****Fourth attempt (success):

ALTER TABLE TRTRRETH00 ALTER PARTITION 1 ENDING AT ('1986-01-31');
---------+---------+---------+---------+---------+---------+---------+------
DSNT404I SQLCODE = 610, WARNING: A CREATE/ALTER ON OBJECT DB2FDB.TRTRRETH00
HAS PLACED OBJECT IN ADVISORY REORG PENDING
DSNT418I SQLSTATE = 01566 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXI14 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 250 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000000FA' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

It seems that, if you ALTER TABLE and use PARTITION, you must use "ENDING AT", not "VALUES". But if you ALTER TABLE and use PART, you must use "VALUES", not "ENDING AT".

Is there a subtlety which I'm missing here? This distinction, and incompatible syntax, seems quite wrong to me. PARTITION and PART ought to be synonyms, and have the same clauses. VALUES and ENDING AT ought to be synonyms, as well.

--Phil Sevetson
Puzzled DB2 DBA
**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 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

  • image001.png (6.7k)

James Campbell

ALTER PART / ALTER PARTITION
(in response to Philip Sevetson)
Weird or not, it is documented : "PART integer VALUES can be specified as an alternative to
PARTITION integer ENDING AT." So it is the pair of keywords that has a synonym, not the
individual words.

In LUW the individual words are synonyms.


James Campbell


On 3 Jul 2018 at 16:13, Sevetson, Phil wrote:

>
> I think I just stumbled across a truly weird syntax glitch in DB2V11 on z.  Follow along as I attempt
> to change a limit key on a partitioned table (successes are cancelled with ROLLBACK so that I
> can repeat the experiment).
>  
> ****First attempt (fail):
>  
> ALTER TABLE TRTRRETH00 ALTER PART 1    ENDING AT ('1986-01-31');     
> ---------+---------+---------+---------+---------+---------+---------+-------
> DSNT408I SQLCODE = -199, ERROR:  ILLEGAL USE OF KEYWORD ENDING.  TOKEN
> VALUES
>          WAS EXPECTED                                                       
>  
> ****Second attempt (success):
>  
> ALTER TABLE TRTRRETH00 ALTER PART 1 VALUES ('1986-01-31');                  
> ---------+---------+---------+---------+---------+---------+---------+-------
> DSNT404I SQLCODE = 610, WARNING:  A CREATE/ALTER ON OBJECT
> DB2FDB.TRTRRETH00
>          HAS PLACED OBJECT IN ADVISORY REORG PENDING                        
> DSNT418I SQLSTATE   = 01566 SQLSTATE RETURN CODE                            
> DSNT415I SQLERRP    = DSNXI14 SQL PROCEDURE DETECTING ERROR                 
> DSNT416I SQLERRD    = 250 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION         
> DSNT416I SQLERRD    = X'000000FA'  X'00000000'  X'00000000'  X'FFFFFFFF'    
>          X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION                
>  
> ****Third attempt (fail):
>  
> ALTER TABLE TRTRRETH00 ALTER PARTITION 1 VALUES ('1986-01-31');              
> ---------+---------+---------+---------+---------+---------+---------+--------
> DSNT408I SQLCODE = -199, ERROR:  ILLEGAL USE OF KEYWORD VALUES.  TOKEN
> ENDING
>          HASH WAS EXPECTED                                                    
>  
> ****Fourth attempt (success):
>  
> ALTER TABLE TRTRRETH00 ALTER PARTITION 1    ENDING AT ('1986-01-31');      
> ---------+---------+---------+---------+---------+---------+---------+------
> DSNT404I SQLCODE = 610, WARNING:  A CREATE/ALTER ON OBJECT
> DB2FDB.TRTRRETH00
>          HAS PLACED OBJECT IN ADVISORY REORG PENDING                       
> DSNT418I SQLSTATE   = 01566 SQLSTATE RETURN CODE                           
> DSNT415I SQLERRP    = DSNXI14 SQL PROCEDURE DETECTING ERROR                
> DSNT416I SQLERRD    = 250 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION        
> DSNT416I SQLERRD    = X'000000FA'  X'00000000'  X'00000000'  X'FFFFFFFF'   
>          X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION               
>  
> It seems that, if you ALTER TABLE and use PARTITION, you must use "ENDING AT", not
> "VALUES".  But if you ALTER TABLE and use PART, you must use "VALUES", not "ENDING AT".
>  
> Is there a subtlety which I´m missing here?  This distinction, and incompatible syntax, seems
> quite wrong to me. PARTITION and PART ought to be synonyms, and have the same clauses. 
> VALUES and ENDING AT ought to be synonyms, as well.
>  
> --Phil Sevetson
> Puzzled DB2 DBA


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

Philip Sevetson

ALTER PART / ALTER PARTITION
(in response to James Campbell)
James,

1. Of course - when all else fails, read the manual. Is that egg on my face?
2. < sarcasm > I guess, if it's documented, it must be good < / sarcasm >
3. It's consistent, I guess, if you're trying to save bytes (say, in a 72-column input field for a SQL processor!).
4. It's semantically ugly. There's no reason other than aesthetic to create a two-word synonym, and I betcha it makes the code more complicated than the other solution.
5. Thanks. As noted in (1), I didn't think to RTFM.

--Phil S.

-----Original Message-----
From: James Campbell [mailto:[login to unmask email]
Sent: Thursday, July 05, 2018 3:58 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: ALTER PART / ALTER PARTITION

Weird or not, it is documented : "PART integer VALUES can be specified as an alternative to
PARTITION integer ENDING AT." So it is the pair of keywords that has a synonym, not the
individual words.

In LUW the individual words are synonyms.


James Campbell


On 3 Jul 2018 at 16:13, Sevetson, Phil wrote:

>
> I think I just stumbled across a truly weird syntax glitch in DB2V11 on z. Follow along as I attempt
> to change a limit key on a partitioned table (successes are cancelled with ROLLBACK so that I
> can repeat the experiment).
>
> ****First attempt (fail):
>
> ALTER TABLE TRTRRETH00 ALTER PART 1 ENDING AT ('1986-01-31');
> ---------+---------+---------+---------+---------+---------+---------+-------
> DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD ENDING. TOKEN
> VALUES
> WAS EXPECTED
>
> ****Second attempt (success):
>
> ALTER TABLE TRTRRETH00 ALTER PART 1 VALUES ('1986-01-31');
> ---------+---------+---------+---------+---------+---------+---------+-------
> DSNT404I SQLCODE = 610, WARNING: A CREATE/ALTER ON OBJECT
> DB2FDB.TRTRRETH00
> HAS PLACED OBJECT IN ADVISORY REORG PENDING
> DSNT418I SQLSTATE = 01566 SQLSTATE RETURN CODE
> DSNT415I SQLERRP = DSNXI14 SQL PROCEDURE DETECTING ERROR
> DSNT416I SQLERRD = 250 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
> DSNT416I SQLERRD = X'000000FA' X'00000000' X'00000000' X'FFFFFFFF'
> X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
>
> ****Third attempt (fail):
>
> ALTER TABLE TRTRRETH00 ALTER PARTITION 1 VALUES ('1986-01-31');
> ---------+---------+---------+---------+---------+---------+---------+--------
> DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD VALUES. TOKEN
> ENDING
> HASH WAS EXPECTED
>
> ****Fourth attempt (success):
>
> ALTER TABLE TRTRRETH00 ALTER PARTITION 1 ENDING AT ('1986-01-31');
> ---------+---------+---------+---------+---------+---------+---------+------
> DSNT404I SQLCODE = 610, WARNING: A CREATE/ALTER ON OBJECT
> DB2FDB.TRTRRETH00
> HAS PLACED OBJECT IN ADVISORY REORG PENDING
> DSNT418I SQLSTATE = 01566 SQLSTATE RETURN CODE
> DSNT415I SQLERRP = DSNXI14 SQL PROCEDURE DETECTING ERROR
> DSNT416I SQLERRD = 250 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
> DSNT416I SQLERRD = X'000000FA' X'00000000' X'00000000' X'FFFFFFFF'
> X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
>
> It seems that, if you ALTER TABLE and use PARTITION, you must use "ENDING AT", not
> "VALUES". But if you ALTER TABLE and use PART, you must use "VALUES", not "ENDING AT".
>
> Is there a subtlety which I´m missing here? This distinction, and incompatible syntax, seems
> quite wrong to me. PARTITION and PART ought to be synonyms, and have the same clauses.
> VALUES and ENDING AT ought to be synonyms, as well.
>
> --Phil Sevetson
> Puzzled DB2 DBA


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

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