LIMITKEY in SYSIBM.SYSTABLEPART V11 (Z/OS)

Robert Hall

LIMITKEY in SYSIBM.SYSTABLEPART V11 (Z/OS)


We have an application that uses the LIMITKEY column from the
SYSIBM.SYSTABLEPART catalog table and in Version 11, data values are
enclosed in quotes. As you can imagine, having a mixed result set of quoted
and unquoted data from this column caused an application issue with parsing
the data. The change is documented in the manuals without stating the
reason for the change.



First, it would be nice to know why this change was made, but I'm also
posting this as a PSA in case this change creates problems for other folks
out there in DB2 land.





Thanks,


Robert



Paul Ogborne

LIMITKEY in SYSIBM.SYSTABLEPART V11 (Z/OS)
(in response to Robert Hall)
Robert,
Yes, we hit the same problem and we had to ‘code’ around it.
So far, I have not seen any explanation why this change was introduced.
Regards,
Paul Ogborne.

> On 2 Feb 2018, at 00:38, Robert Hall <[login to unmask email]> wrote:
>
>
> We have an application that uses the LIMITKEY column from the SYSIBM.SYSTABLEPART catalog table and in Version 11, data values are enclosed in quotes. As you can imagine, having a mixed result set of quoted and unquoted data from this column caused an application issue with parsing the data. The change is documented in the manuals without stating the reason for the change.
>
> First, it would be nice to know why this change was made, but I’m also posting this as a PSA in case this change creates problems for other folks out there in DB2 land.
>
>
> Thanks,
>
> Robert
>
>
> 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]
> ** ** ** IDUG DB2 Data and Analytics Technical Summit in Bengaluru, India 2018 ** ** **
> ---> Bengaluru, India, March 27, 2018 <---
> http://ibm.biz/IDUGBengaluru2018
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Philip Sevetson

LIMITKEY in SYSIBM.SYSTABLEPART V11 (Z/OS)
(in response to Paul Ogborne)
We hit that as well. I had a remark about it at the time, back in September.

The change probably simplifies future SQL in the matter of identifying date-and-time columns in the LIMITKEY field. Future uses of LIMITKEY in a DDL-copying process will not need to separately identify and preprocess the date values in LIMITKEY in order to pass them along into a constructed ENDING AT clause.

However, for PBR and other tablespaces with LIMITKEY, until all of the values are represented in the new format, we wound up taking the value as:
SELECT DATE(STRIP(PART.LIMITKEY,BOTH,''''))
FROM SYSIBM.SYSTABLEPART PART, (etc)


From: Paul Ogborne [mailto:[login to unmask email]
Sent: Thursday, February 01, 2018 8:12 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: LIMITKEY in SYSIBM.SYSTABLEPART V11 (Z/OS)

Robert,
Yes, we hit the same problem and we had to ‘code’ around it.
So far, I have not seen any explanation why this change was introduced.
Regards,
Paul Ogborne.

On 2 Feb 2018, at 00:38, Robert Hall <[login to unmask email]<mailto:[login to unmask email]>> wrote:

We have an application that uses the LIMITKEY column from the SYSIBM.SYSTABLEPART catalog table and in Version 11, data values are enclosed in quotes. As you can imagine, having a mixed result set of quoted and unquoted data from this column caused an application issue with parsing the data. The change is documented in the manuals without stating the reason for the change.

First, it would be nice to know why this change was made, but I’m also posting this as a PSA in case this change creates problems for other folks out there in DB2 land.


Thanks,

Robert


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

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

Michael Hannan

RE: LIMITKEY in SYSIBM.SYSTABLEPART V11 (Z/OS)
(in response to Robert Hall)

Robert,

Many years ago, I wrote a large SQL query to generate DDL out of Catalog information. One stumbling block was the Limit Key information. One Catalog Table contained Limitkey in internal format. Another SYSTABLEPART contained external format, almost! Unfortunately the Date, Time, and Timestamp columns were in text display format but lacking Quotes.

I had to put up with this anomaly, and post process the generated DDL to put the quotes back in again when necessary, (using REXX). This was in days before Recursive SQL would have allowed a possible pure SQL solution. It was very annoying. I haven't had time to reinvent and update my DDL generator for several years. Other more important things came up, so it is too out of date to supply to anyone. I do have a SYSPACKSTMT SQL Text and Host Variable Datatype information formatter written as just an SQL Query (with recursion). I may decide that a version of this is  publishable sometime soon,probably as an SQL Table Function.

Perhaps they finally decided to rectify LIMITKEY in the correct External format, however I recognise it is not much use when there is a mixture of the old and new format. Any parser, finding the limit keys separated by commas, should be able to easily see that date/time info is not included in quotes and insert the quotes. If a process accidentally put in extra quotes, so there were two quotes in a row, that should be easily modified to just one quote. 

  
In Reply to Robert Hall:

We have an application that uses the LIMITKEY column from the
SYSIBM.SYSTABLEPART catalog table and in Version 11, data values are
enclosed in quotes. As you can imagine, having a mixed result set of quoted
and unquoted data from this column caused an application issue with parsing
the data. The change is documented in the manuals without stating the
reason for the change. First, it would be nice to know why this change was made, but I'm also
posting this as a PSA in case this change creates problems for other folks
out there in DB2 land. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd