v11 quotes in limit key

william giannelli

v11 quotes in limit key

since migrating to v11 we now have programs abending due to processing limit keys that now have quotes.

Prior to this in v10 there were no quotes in the Limit keys. Now we have tablespaces with a mix of limit keys (without quotes and wtih quotes).

I am not sure how to proceed and the limit keys consistent.

any advice would be greatly appreciated!

thanks

Bill 

Paul Ogborne

v11 quotes in limit key
(in response to william giannelli)
Bill,

This is working 'as designed' and you can find it stated in the IBM manuals that limit key quoting is different between V10 and V11.

We hit this problem when rotating partitions in lots of places. We were fortunate that we use a 'ROTATE' REXX in a proc, which I was able to amend to cope with both formats. With some of the larger history type tables I expect that we will live with some limit keys with quotes and some without for some time.

Others on the list may have other solutions?

What I am unsure about is why IBM made this change; no doubt there was a good reason and perhaps somebody can tell us please?

Regards,
Paul Ogborne.


> On 3 Sep 2017, at 01:01, william giannelli <[login to unmask email]> wrote:
>
> since migrating to v11 we now have programs abending due to processing limit keys that now have quotes.
>
> Prior to this in v10 there were no quotes in the Limit keys. Now we have tablespaces with a mix of limit keys (without quotes and wtih quotes).
>
> I am not sure how to proceed and the limit keys consistent.
>
> any advice would be greatly appreciated!
>
> thanks
>
> Bill
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Peter Vanroose

RE: v11 quotes in limit key
(in response to william giannelli)

I suppose you know you are processing the LIMITKEY of systablepart for a DATE column.

In that case there can now indeed be a mix of 10-byte and 12-byte values in LIMITKEY: without or with single quotes.

(See http://www-01.ibm.com/support/docview.wss?uid=swg21999051)

Replace your current SQL query's use of LIMITKEY by:

       CASE WHEN substr(LIMITKEY,1,1) = X'27' THEN DATE(substr(LIMITKEY,2,10)) ELSE DATE(LIMITKEY) END

In Reply to william giannelli:

since migrating to v11 we now have programs abending due to processing limit keys that now have quotes.

Prior to this in v10 there were no quotes in the Limit keys. Now we have tablespaces with a mix of limit keys (without quotes and wtih quotes).

I am not sure how to proceed and the limit keys consistent.

any advice would be greatly appreciated!

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

Edited By:
Peter Vanroose[Organization Members] @ Sep 03, 2017 - 08:44 PM (Europe/Brussels)

Philip Sevetson

v11 quotes in limit key
(in response to Peter Vanroose)
**please note my email address change**
William Gianelli,
We’re changing our LIMITKEY logic for these. Like this:
SELECT DATE(MAX(STRIP(LIMITKEY,BOTH,''''))) FROM SYSIBM.SYSTABLEPART
WHERE TSNAME = "x" AND DBNAME = "y" AND LIMITKEY <> 'MAXVALUE'

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: Sunday, September 03, 2017 1:14 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: v11 quotes in limit key


I suppose you know you are processing the LIMITKEY of systablepart for a DATE column.

In that case there can now indeed be a mix of 10-byte and 12-byte values in LIMITKEY: without or with single quotes.

(See http://www-01.ibm.com/support/docview.wss?uid=swg21999051)

Modify the current SQL query's use of LIMITKEY by:

CASE WHEN substr(LIMITKEY,1,1) = X'27' THEN DATE(substr(LIMITKEY,2,10)) ELSE DATE(LIMITKEY) END

In Reply to william giannelli:

since migrating to v11 we now have programs abending due to processing limit keys that now have quotes.

Prior to this in v10 there were no quotes in the Limit keys. Now we have tablespaces with a mix of limit keys (without quotes and wtih quotes).

I am not sure how to proceed and the limit keys consistent.

any advice would be greatly appreciated!

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

Philip Sevetson

v11 quotes in limit key
(in response to Philip Sevetson)
**please note my email address change**
Sorry, apparently I should clarify.

1) The third argument in the STRIP function is four consecutive apostrophes, which is how you comply with DB2’s syntax rules to create a SINGLE apostrophe as an argument.

2) I used double quotes around the TSNAME and DBNAME values -- apparently, I’m not awake yet. Those should be single quotes / apostrophes. Thus, the revised statement would be:

SELECT DATE(MAX(STRIP(LIMITKEY,BOTH,''''))) FROM SYSIBM.SYSTABLEPART
WHERE TSNAME = 'x' AND DBNAME = 'y' AND LIMITKEY <> 'MAXVALUE'
;

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: Tuesday, September 05, 2017 8:34 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: v11 quotes in limit key

**please note my email address change**
William Gianelli,
We’re changing our LIMITKEY logic for these. Like this:
SELECT DATE(MAX(STRIP(LIMITKEY,BOTH,''''))) FROM SYSIBM.SYSTABLEPART
WHERE TSNAME = "x" AND DBNAME = "y" AND LIMITKEY <> 'MAXVALUE'

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: Sunday, September 03, 2017 1:14 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: v11 quotes in limit key


I suppose you know you are processing the LIMITKEY of systablepart for a DATE column.

In that case there can now indeed be a mix of 10-byte and 12-byte values in LIMITKEY: without or with single quotes.

(See http://www-01.ibm.com/support/docview.wss?uid=swg21999051)

Modify the current SQL query's use of LIMITKEY by:

CASE WHEN substr(LIMITKEY,1,1) = X'27' THEN DATE(substr(LIMITKEY,2,10)) ELSE DATE(LIMITKEY) END

In Reply to william giannelli:

since migrating to v11 we now have programs abending due to processing limit keys that now have quotes.

Prior to this in v10 there were no quotes in the Limit keys. Now we have tablespaces with a mix of limit keys (without quotes and wtih quotes).

I am not sure how to proceed and the limit keys consistent.

any advice would be greatly appreciated!

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