[Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column

Philip Sevetson

[Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column
I believe it's now fair to characterize how DB2 handles dates in DB2 and especially LIMITKEY to be counterintuitive, perhaps even contradictory. Let me present a series of SQL statements run just now in DB2V11 through Data Studio.

Short version: If you use a literal date value as argument for DATE(), you must use apostrophes. However, if you use a column value as argument for date, you must not use apostrophes.

********

Read that again, please. This is the core of my frustration at the moment. It's a bloody headache, which I invite you to share with me. Also, I'd appreciate suggestions for how to explain this to my managers, who aren't ignorant of DB2 but probably aren't prepared for this.


1. Some information about definition of SYSTABLEPART

Column Name Col No Col Type Length
------------------ ------ -------- ------
PARTITION 1 SMALLINT 2
LIMITKEY 17 VARCHAR 765
LOGICAL_PART 37 SMALLINT 2

2. Results of certain queries in DB2 V11

--query 1
SELECT DATE(2001-01-01) FROM SYSIBM.SYSDUMMY1;
--result 1
1
----------
0006-06-22
(DB2 treated the argument as a compound difference, subtracted 1 from 2001, subtracted 1 from the result, and treated the resulting value, 1999, as the input to the date function)

--query 2
SELECT DATE('2001-01-01') FROM SYSIBM.SYSDUMMY1;
--result 2
1
----------
2001-01-01
(DB2 handled this as the writer intended)
--query 3
SELECT DATE(TESTVAL) FROM
(SELECT '2001-01-01' FROM SYSIBM.SYSDUMMY1);
--result 3
ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61

(I don't pretend to understand this. I'd appreciate help finding APPLCOMPAT for the subsystem or for Data Studio, whichever is causing this. However... Db2 is mishandling a CHAR result from a subselect??)

--query 4
SELECT LOGICAL_PART, LIMITKEY
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 4
LOGICAL_PART LIMITKEY
------------ ------------
1 '2016-01-31'

(Here is the raw value from LIMITKEY for a table which is partitioned by date. The same partition and table are used for all remaining queries in this discussion. DB2 adds apostrophes to all values for partitioning columns in LIMITKEY, whose type is DATE, beginning in version 11)

--query 5
SELECT DATE(LIMITKEY) FROM
(SELECT LOGICAL_PART, LIMITKEY
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
);
--result 5
ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61

(Here we are again with APPLCOMPAT. Help!)

--query 6
SELECT LOGICAL_PART, DATE(LIMITKEY)
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 6
An error occurred while processing the results. - THE DATE, TIME, OR TIMESTAMP VALUE *N IS INVALID. SQLCODE=-180, SQLSTATE=22007, DRIVER=3.68.61

(ditto.)

--query 7
SELECT LOGICAL_PART, STRIP(LIMITKEY,BOTH,'''')
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 7
LOGICAL_PART 2
------------ ----------
1 2016-01-31

(We can get a result which looks like a DATE from LIMITKEY and doesn't have apostrophes. This is to set up our final query:)

--query 8
SELECT LOGICAL_PART, DATE(STRIP(LIMITKEY,BOTH,''''))
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 8
LOGICAL_PART 2
------------ ----------
1 2016-01-31

(We have successfully used a DATE function to reproduce the DATE value from LIMITKEY. Now, in order to handle a date value as represented in LIMITKEY, we apparently have to STRIP the apostrophes. KEY QUESTION FOR THIS DESIGN DECISION BY IBM: Why is this a good thing???)


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]
212-857-1688 w
917-991-7052 m
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 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column
(in response to Philip Sevetson)
A couple of trivial fixes to the early results.

Query 1 discussion - tightened up a couple of technical terms
Query 3 - fixed the query but unchanged results.

Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, July 19, 2019 9:05 AM
To: [login to unmask email]
Subject: [DB2-L] - [Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column

I believe it's now fair to characterize how DB2 handles dates in DB2 and especially LIMITKEY to be counterintuitive, perhaps even contradictory. Let me present a series of SQL statements run just now in DB2V11 through Data Studio.

Short version: If you use a literal date value as argument for DATE(), you must use apostrophes. However, if you use a column value as argument for date, you must not use apostrophes.

********

Read that again, please. This is the core of my frustration at the moment. It's a bloody headache, which I invite you to share with me. Also, I'd appreciate suggestions for how to explain this to my managers, who aren't ignorant of DB2 but probably aren't prepared for this.


1. Some information about definition of SYSTABLEPART

Column Name Col No Col Type Length
------------------ ------ -------- ------
PARTITION 1 SMALLINT 2
LIMITKEY 17 VARCHAR 765
LOGICAL_PART 37 SMALLINT 2

2. Results of certain queries in DB2 V11

--query 1
SELECT DATE(2001-01-01) FROM SYSIBM.SYSDUMMY1;
--result 1
1
----------
0006-06-22
(DB2 treated the expression 2001-01-01 as a compound difference, subtracted 1 from 2001, subtracted 1 from the result, and treated the resulting value, 1999, as the argument to the date function)

--query 2
SELECT DATE('2001-01-01') FROM SYSIBM.SYSDUMMY1;
--result 2
1
----------
2001-01-01
(DB2 handled this as the writer intended)

--query 3
SELECT DATE(TESTVAL) FROM
(SELECT '2001-01-01' AS TESTVAL FROM SYSIBM.SYSDUMMY1);
--result 3
ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61

(I don't pretend to understand this. I'd appreciate help finding APPLCOMPAT for the subsystem or for Data Studio, whichever is causing this. However... Db2 is mishandling a CHAR result from a subselect??)

--query 4
SELECT LOGICAL_PART, LIMITKEY
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 4
LOGICAL_PART LIMITKEY
------------ ------------
1 '2016-01-31'

(Here is the raw value from LIMITKEY for a table which is partitioned by date. The same partition and table are used for all remaining queries in this discussion. DB2 adds apostrophes to all values for partitioning columns in LIMITKEY, whose type is DATE, beginning in version 11)

--query 5
SELECT DATE(LIMITKEY) FROM
(SELECT LOGICAL_PART, LIMITKEY
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
);
--result 5
ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61

(Here we are again with APPLCOMPAT. Help!)

--query 6
SELECT LOGICAL_PART, DATE(LIMITKEY)
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 6
An error occurred while processing the results. - THE DATE, TIME, OR TIMESTAMP VALUE *N IS INVALID. SQLCODE=-180, SQLSTATE=22007, DRIVER=3.68.61

(ditto.)

--query 7
SELECT LOGICAL_PART, STRIP(LIMITKEY,BOTH,'''')
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 7
LOGICAL_PART 2
------------ ----------
1 2016-01-31

(We can get a result which looks like a DATE from LIMITKEY and doesn't have apostrophes. This is to set up our final query:)

--query 8
SELECT LOGICAL_PART, DATE(STRIP(LIMITKEY,BOTH,''''))
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 8
LOGICAL_PART 2
------------ ----------
1 2016-01-31

(We have successfully used a DATE function to reproduce the DATE value from LIMITKEY. Now, in order to handle a date value as represented in LIMITKEY, we apparently have to STRIP the apostrophes. KEY QUESTION FOR THIS DESIGN DECISION BY IBM: Why is this a good thing???)


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]
212-857-1688 w
917-991-7052 m
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)

Sam Baugh

[Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column
(in response to Philip Sevetson)
Query 1, you passed a mathematical formula that appears as if its a date,
but is not different than date(1+2+3+4);
Query 3, the subselect needs to be qualified select x.* from (select col
from table) as x, so add the 'AS X' to the query will get you past the
application compatibility error

On Fri, Jul 19, 2019 at 8:11 AM Sevetson, Phil <[login to unmask email]> wrote:

> A couple of trivial fixes to the early results.
>
>
>
> Query 1 discussion - tightened up a couple of technical terms
>
> Query 3 – fixed the query but unchanged results.
>
>
>
> Philip Sevetson
>
> Computer Systems Manager
>
> FISA-OPA
>
> 5 Manhattan West
>
> New York, NY 10001
>
> [login to unmask email]
>
> 212-857-1688 w
>
> 917-991-7052 m
>
> 212-857-1659 f
>
> [image: cid:[login to unmask email]
>
>
>
> *From:* Sevetson, Phil [mailto:[login to unmask email]
> *Sent:* Friday, July 19, 2019 9:05 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - [Db2 V11 z/OS] (Mis) Handling Dates in the
> SYSTABLEPART.LIMITKEY column
>
>
>
> I believe it’s now fair to characterize how DB2 handles dates in DB2 and
> especially LIMITKEY to be counterintuitive, perhaps even contradictory. Let
> me present a series of SQL statements run just now in DB2V11 through Data
> Studio.
>
>
>
> Short version: If you use a literal date value as argument for DATE(), you
> *must* use apostrophes. However, if you use a column value as argument
> for date, you *must not* use apostrophes.
>
>
>
> ********
>
>
>
> Read that again, please. This is the core of my frustration at the
> moment. It’s a bloody headache, which I invite you to share with me. Also,
> I’d appreciate suggestions for how to explain this to my managers, who
> aren’t ignorant of DB2 but probably aren’t prepared for this.
>
>
>
>
>
> 1. Some information about definition of SYSTABLEPART
>
>
>
> Column Name Col No Col Type Length
>
> ------------------ ------ -------- ------
>
> PARTITION 1 SMALLINT 2
>
> LIMITKEY 17 VARCHAR 765
>
> LOGICAL_PART 37 SMALLINT 2
>
>
>
> 2. Results of certain queries in DB2 V11
>
>
>
> --query 1
>
> SELECT DATE(2001-01-01) FROM SYSIBM.SYSDUMMY1;
>
> --result 1
>
> *1*
>
> ----------
>
> 0006-06-22
>
> *(DB2 treated the expression 2001-01-01 as a compound difference,
> subtracted 1 from 2001, subtracted 1 from the result, and treated the
> resulting value, 1999, as the argument to the date function)*
>
>
>
> --query 2
>
> SELECT DATE('2001-01-01') FROM SYSIBM.SYSDUMMY1;
>
> --result 2
>
> 1
>
> ----------
>
> 2001-01-01
>
> *(DB2 handled this as the writer intended)*
>
>
>
> --query 3
>
> SELECT DATE(TESTVAL) FROM
>
> (SELECT '2001-01-01' AS TESTVAL FROM SYSIBM.SYSDUMMY1);
>
> --result 3
>
> ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS
> SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61
>
> *(I don’t pretend to understand this. I’d appreciate help finding
> APPLCOMPAT for the subsystem or for Data Studio, whichever is causing this.
> However... Db2 is mishandling a CHAR result from a subselect??)*
>
>
>
> --query 4
>
> SELECT LOGICAL_PART, LIMITKEY
>
> FROM SYSIBM.SYSTABLEPART
>
> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>
> AND LOGICAL_PART = 1
>
> ;
>
> --result 4
>
> LOGICAL_PART LIMITKEY
>
> ------------ ------------
>
> 1 '2016-01-31'
>
> *(Here is the raw value from LIMITKEY for a table which is partitioned by
> date. The same partition and table are used for all remaining queries in
> this discussion. DB2 adds apostrophes to all values for partitioning
> columns in LIMITKEY, whose type is DATE, beginning in version 11)*
>
>
>
> --query 5
>
> SELECT DATE(LIMITKEY) FROM
>
> (SELECT LOGICAL_PART, LIMITKEY
>
> FROM SYSIBM.SYSTABLEPART
>
> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>
> AND LOGICAL_PART = 1
>
> );
>
> --result 5
>
> ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS
> SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61
>
> *(Here we are again with APPLCOMPAT. Help!)*
>
>
>
> --query 6
>
> SELECT LOGICAL_PART, DATE(LIMITKEY)
>
> FROM SYSIBM.SYSTABLEPART
>
> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>
> AND LOGICAL_PART = 1
>
> ;
>
> --result 6
>
> An error occurred while processing the results. - THE DATE, TIME, OR
> TIMESTAMP VALUE *N IS INVALID. SQLCODE=-180, SQLSTATE=22007, DRIVER=3.68.61
>
> *(ditto.)*
>
>
>
> --query 7
>
> SELECT LOGICAL_PART, STRIP(LIMITKEY,BOTH,'''')
>
> FROM SYSIBM.SYSTABLEPART
>
> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>
> AND LOGICAL_PART = 1
>
> ;
>
> --result 7
>
> LOGICAL_PART 2
>
> ------------ ----------
>
> 1 2016-01-31
>
> *(We can get a result which looks like a DATE from LIMITKEY and doesn’t
> have apostrophes. This is to set up our final query:)*
>
>
>
> --query 8
>
> SELECT LOGICAL_PART, DATE(STRIP(LIMITKEY,BOTH,''''))
>
> FROM SYSIBM.SYSTABLEPART
>
> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>
> AND LOGICAL_PART = 1
>
> ;
>
> --result 8
>
> LOGICAL_PART 2
>
> ------------ ----------
>
> 1 2016-01-31
>
> *(We have successfully used a DATE function to reproduce the DATE value
> from LIMITKEY. Now, in order to handle a date value as represented in
> LIMITKEY, we apparently have to STRIP the apostrophes. KEY QUESTION FOR
> THIS DESIGN DECISION BY IBM: Why is this a good thing???)*
>
>
>
>
>
> Philip Sevetson
>
> Computer Systems Manager
>
> FISA-OPA
>
> 5 Manhattan West
>
> New York, NY 10001
>
> [login to unmask email]
>
> 212-857-1688 w
>
> 917-991-7052 m
>
> 212-857-1659 f
>
> [image: 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-----
>

Phil Grainger

[Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column
(in response to Sam Baugh)
I think that Philips point was that as a LITERAL a date has to be enclosed in quotes to PREVENT it from being viewed as a formula

BUT a VALUE IN A ROW CANNOT have quotes around it – if it’s a DATE column, Db2 intuitively knows it’s a date NOT a formula

This is a seemingly inconsistent treatment of the same data

01-01-2001 is a FORMULA as a literal, but a DATE when in a column
’01-01-2001’ is a DATE as a literal, but an error when treated as a date column

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: Sam Baugh <[login to unmask email]>
Sent: 19 July 2019 14:40
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: [Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column

Query 1, you passed a mathematical formula that appears as if its a date, but is not different than date(1+2+3+4);
Query 3, the subselect needs to be qualified select x.* from (select col from table) as x, so add the 'AS X' to the query will get you past the application compatibility error

On Fri, Jul 19, 2019 at 8:11 AM Sevetson, Phil <[login to unmask email]<mailto:[login to unmask email]>> wrote:
A couple of trivial fixes to the early results.

Query 1 discussion - tightened up a couple of technical terms
Query 3 – fixed the query but unchanged results.

Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f

From: Sevetson, Phil [mailto:[login to unmask email]<mailto:[login to unmask email]>]
Sent: Friday, July 19, 2019 9:05 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - [Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column

I believe it’s now fair to characterize how DB2 handles dates in DB2 and especially LIMITKEY to be counterintuitive, perhaps even contradictory. Let me present a series of SQL statements run just now in DB2V11 through Data Studio.

Short version: If you use a literal date value as argument for DATE(), you must use apostrophes. However, if you use a column value as argument for date, you must not use apostrophes.

********

Read that again, please. This is the core of my frustration at the moment. It’s a bloody headache, which I invite you to share with me. Also, I’d appreciate suggestions for how to explain this to my managers, who aren’t ignorant of DB2 but probably aren’t prepared for this.


1. Some information about definition of SYSTABLEPART

Column Name Col No Col Type Length
------------------ ------ -------- ------
PARTITION 1 SMALLINT 2
LIMITKEY 17 VARCHAR 765
LOGICAL_PART 37 SMALLINT 2

2. Results of certain queries in DB2 V11

--query 1
SELECT DATE(2001-01-01) FROM SYSIBM.SYSDUMMY1;
--result 1
1
----------
0006-06-22
(DB2 treated the expression 2001-01-01 as a compound difference, subtracted 1 from 2001, subtracted 1 from the result, and treated the resulting value, 1999, as the argument to the date function)

--query 2
SELECT DATE('2001-01-01') FROM SYSIBM.SYSDUMMY1;
--result 2
1
----------
2001-01-01
(DB2 handled this as the writer intended)

--query 3
SELECT DATE(TESTVAL) FROM
(SELECT '2001-01-01' AS TESTVAL FROM SYSIBM.SYSDUMMY1);
--result 3
ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61

(I don’t pretend to understand this. I’d appreciate help finding APPLCOMPAT for the subsystem or for Data Studio, whichever is causing this. However... Db2 is mishandling a CHAR result from a subselect??)

--query 4
SELECT LOGICAL_PART, LIMITKEY
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 4
LOGICAL_PART LIMITKEY
------------ ------------
1 '2016-01-31'

(Here is the raw value from LIMITKEY for a table which is partitioned by date. The same partition and table are used for all remaining queries in this discussion. DB2 adds apostrophes to all values for partitioning columns in LIMITKEY, whose type is DATE, beginning in version 11)

--query 5
SELECT DATE(LIMITKEY) FROM
(SELECT LOGICAL_PART, LIMITKEY
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
);
--result 5
ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61

(Here we are again with APPLCOMPAT. Help!)

--query 6
SELECT LOGICAL_PART, DATE(LIMITKEY)
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 6
An error occurred while processing the results. - THE DATE, TIME, OR TIMESTAMP VALUE *N IS INVALID. SQLCODE=-180, SQLSTATE=22007, DRIVER=3.68.61

(ditto.)

--query 7
SELECT LOGICAL_PART, STRIP(LIMITKEY,BOTH,'''')
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 7
LOGICAL_PART 2
------------ ----------
1 2016-01-31

(We can get a result which looks like a DATE from LIMITKEY and doesn’t have apostrophes. This is to set up our final query:)

--query 8
SELECT LOGICAL_PART, DATE(STRIP(LIMITKEY,BOTH,''''))
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 8
LOGICAL_PART 2
------------ ----------
1 2016-01-31

(We have successfully used a DATE function to reproduce the DATE value from LIMITKEY. Now, in order to handle a date value as represented in LIMITKEY, we apparently have to STRIP the apostrophes. KEY QUESTION FOR THIS DESIGN DECISION BY IBM: Why is this a good thing???)


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f

**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.**
________________________________
Attachment Links: image001.png (3 k) https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D10303&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=dFBviVwQKBmeyWm0bwfCGAVF7x2-FTn2Gbneo3wwd6c&s=8mzu0w1-zv6Dw4g9MpLK29TRli4vx7_MghAETv6RlUg&e=
Site Links: View post online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_st_-3Fpost-3D189657-26anc-3Dp189657-23p189657&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=dFBviVwQKBmeyWm0bwfCGAVF7x2-FTn2Gbneo3wwd6c&s=68U3soB9HwMxEaMICt_3caAyHjxFV3rmGstqKaUcybE&e= View mailing list online https://urldefense.proofpoint.com/v2/url?u=https-3A__www.idug.org_p_fo_si_-3Ftopic-3D19&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=dFBviVwQKBmeyWm0bwfCGAVF7x2-FTn2Gbneo3wwd6c&s=KNSrZ1-FZj8y6d2zFrqNiqK1oL_vfCjtNtqyY4g0nBE&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=https-3A__www.idug.org_p_us_to_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=dFBviVwQKBmeyWm0bwfCGAVF7x2-FTn2Gbneo3wwd6c&s=EmWLO00EFdLYfEZzdWDBEJCD5DkIpJU9LmJnN7K-R10&e=

This email has been sent to: [login to unmask email]<mailto:[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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ESAIGroup.com_idug&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=dFBviVwQKBmeyWm0bwfCGAVF7x2-FTn2Gbneo3wwd6c&s=1eiRRmtG310WOh2ZCyY2qQdn5iZECPfBz3wxb-8O0Pc&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=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=dFBviVwQKBmeyWm0bwfCGAVF7x2-FTn2Gbneo3wwd6c&s=_LQflVImRGza5VP_uCTDHokCsz_VbDA6csdLni1AFn0&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)

Philip Sevetson

[Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column
(in response to Sam Baugh)
Ah. Thank you, Sam. The fix with Q3 also works for Q5 (although it reveals the ‘DATE, TIME, OR TIMESTAMP VALUE OF *N IS INVALID) error there).

I understood the problem with Q1, but while it illuminates the _reason_ that works that way, and the reason that IBM uses delimiters, it doesn’t simplify the problem of reading SQL for this situation. Ugh.

Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Sam Baugh [mailto:[login to unmask email]
Sent: Friday, July 19, 2019 9:40 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column

Query 1, you passed a mathematical formula that appears as if its a date, but is not different than date(1+2+3+4);
Query 3, the subselect needs to be qualified select x.* from (select col from table) as x, so add the 'AS X' to the query will get you past the application compatibility error

On Fri, Jul 19, 2019 at 8:11 AM Sevetson, Phil <[login to unmask email]<mailto:[login to unmask email]>> wrote:
A couple of trivial fixes to the early results.

Query 1 discussion - tightened up a couple of technical terms
Query 3 – fixed the query but unchanged results.

Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
Error! Filename not specified.

From: Sevetson, Phil [mailto:[login to unmask email]<mailto:[login to unmask email]>]
Sent: Friday, July 19, 2019 9:05 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - [Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column

I believe it’s now fair to characterize how DB2 handles dates in DB2 and especially LIMITKEY to be counterintuitive, perhaps even contradictory. Let me present a series of SQL statements run just now in DB2V11 through Data Studio.

Short version: If you use a literal date value as argument for DATE(), you must use apostrophes. However, if you use a column value as argument for date, you must not use apostrophes.

********

Read that again, please. This is the core of my frustration at the moment. It’s a bloody headache, which I invite you to share with me. Also, I’d appreciate suggestions for how to explain this to my managers, who aren’t ignorant of DB2 but probably aren’t prepared for this.


1. Some information about definition of SYSTABLEPART

Column Name Col No Col Type Length
------------------ ------ -------- ------
PARTITION 1 SMALLINT 2
LIMITKEY 17 VARCHAR 765
LOGICAL_PART 37 SMALLINT 2

2. Results of certain queries in DB2 V11

--query 1
SELECT DATE(2001-01-01) FROM SYSIBM.SYSDUMMY1;
--result 1
1
----------
0006-06-22
(DB2 treated the expression 2001-01-01 as a compound difference, subtracted 1 from 2001, subtracted 1 from the result, and treated the resulting value, 1999, as the argument to the date function)

--query 2
SELECT DATE('2001-01-01') FROM SYSIBM.SYSDUMMY1;
--result 2
1
----------
2001-01-01
(DB2 handled this as the writer intended)

--query 3
SELECT DATE(TESTVAL) FROM
(SELECT '2001-01-01' AS TESTVAL FROM SYSIBM.SYSDUMMY1);
--result 3
ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61

(I don’t pretend to understand this. I’d appreciate help finding APPLCOMPAT for the subsystem or for Data Studio, whichever is causing this. However... Db2 is mishandling a CHAR result from a subselect??)

--query 4
SELECT LOGICAL_PART, LIMITKEY
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 4
LOGICAL_PART LIMITKEY
------------ ------------
1 '2016-01-31'

(Here is the raw value from LIMITKEY for a table which is partitioned by date. The same partition and table are used for all remaining queries in this discussion. DB2 adds apostrophes to all values for partitioning columns in LIMITKEY, whose type is DATE, beginning in version 11)

--query 5
SELECT DATE(LIMITKEY) FROM
(SELECT LOGICAL_PART, LIMITKEY
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
);
--result 5
ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61

(Here we are again with APPLCOMPAT. Help!)

--query 6
SELECT LOGICAL_PART, DATE(LIMITKEY)
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 6
An error occurred while processing the results. - THE DATE, TIME, OR TIMESTAMP VALUE *N IS INVALID. SQLCODE=-180, SQLSTATE=22007, DRIVER=3.68.61

(ditto.)

--query 7
SELECT LOGICAL_PART, STRIP(LIMITKEY,BOTH,'''')
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 7
LOGICAL_PART 2
------------ ----------
1 2016-01-31

(We can get a result which looks like a DATE from LIMITKEY and doesn’t have apostrophes. This is to set up our final query:)

--query 8
SELECT LOGICAL_PART, DATE(STRIP(LIMITKEY,BOTH,''''))
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 8
LOGICAL_PART 2
------------ ----------
1 2016-01-31

(We have successfully used a DATE function to reproduce the DATE value from LIMITKEY. Now, in order to handle a date value as represented in LIMITKEY, we apparently have to STRIP the apostrophes. KEY QUESTION FOR THIS DESIGN DECISION BY IBM: Why is this a good thing???)


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
Error! Filename not specified.

**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-----
**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 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column
(in response to Phil Grainger)
Nicely restated, Phil G. – that’s my point…

and the cause of three hours of analysis and recoding yesterday afternoon.

-phil (sevetson)


From: Grainger, Phil [mailto:[login to unmask email]
Sent: Friday, July 19, 2019 9:45 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column

I think that Philips point was that as a LITERAL a date has to be enclosed in quotes to PREVENT it from being viewed as a formula

BUT a VALUE IN A ROW CANNOT have quotes around it – if it’s a DATE column, Db2 intuitively knows it’s a date NOT a formula

This is a seemingly inconsistent treatment of the same data

01-01-2001 is a FORMULA as a literal, but a DATE when in a column
’01-01-2001’ is a DATE as a literal, but an error when treated as a date column

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: Sam Baugh <[login to unmask email]>
Sent: 19 July 2019 14:40
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: [Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column

Query 1, you passed a mathematical formula that appears as if its a date, but is not different than date(1+2+3+4);
Query 3, the subselect needs to be qualified select x.* from (select col from table) as x, so add the 'AS X' to the query will get you past the application compatibility error

On Fri, Jul 19, 2019 at 8:11 AM Sevetson, Phil <[login to unmask email]<mailto:[login to unmask email]>> wrote:
A couple of trivial fixes to the early results.

Query 1 discussion - tightened up a couple of technical terms
Query 3 – fixed the query but unchanged results.

Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f


From: Sevetson, Phil [mailto:[login to unmask email]<mailto:[login to unmask email]>]
Sent: Friday, July 19, 2019 9:05 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - [Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column

I believe it’s now fair to characterize how DB2 handles dates in DB2 and especially LIMITKEY to be counterintuitive, perhaps even contradictory. Let me present a series of SQL statements run just now in DB2V11 through Data Studio.

Short version: If you use a literal date value as argument for DATE(), you must use apostrophes. However, if you use a column value as argument for date, you must not use apostrophes.

********

Read that again, please. This is the core of my frustration at the moment. It’s a bloody headache, which I invite you to share with me. Also, I’d appreciate suggestions for how to explain this to my managers, who aren’t ignorant of DB2 but probably aren’t prepared for this.


1. Some information about definition of SYSTABLEPART

Column Name Col No Col Type Length
------------------ ------ -------- ------
PARTITION 1 SMALLINT 2
LIMITKEY 17 VARCHAR 765
LOGICAL_PART 37 SMALLINT 2

2. Results of certain queries in DB2 V11

--query 1
SELECT DATE(2001-01-01) FROM SYSIBM.SYSDUMMY1;
--result 1
1
----------
0006-06-22
(DB2 treated the expression 2001-01-01 as a compound difference, subtracted 1 from 2001, subtracted 1 from the result, and treated the resulting value, 1999, as the argument to the date function)

--query 2
SELECT DATE('2001-01-01') FROM SYSIBM.SYSDUMMY1;
--result 2
1
----------
2001-01-01
(DB2 handled this as the writer intended)

--query 3
SELECT DATE(TESTVAL) FROM
(SELECT '2001-01-01' AS TESTVAL FROM SYSIBM.SYSDUMMY1);
--result 3
ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61

(I don’t pretend to understand this. I’d appreciate help finding APPLCOMPAT for the subsystem or for Data Studio, whichever is causing this. However... Db2 is mishandling a CHAR result from a subselect??)

--query 4
SELECT LOGICAL_PART, LIMITKEY
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 4
LOGICAL_PART LIMITKEY
------------ ------------
1 '2016-01-31'

(Here is the raw value from LIMITKEY for a table which is partitioned by date. The same partition and table are used for all remaining queries in this discussion. DB2 adds apostrophes to all values for partitioning columns in LIMITKEY, whose type is DATE, beginning in version 11)

--query 5
SELECT DATE(LIMITKEY) FROM
(SELECT LOGICAL_PART, LIMITKEY
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
);
--result 5
ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61

(Here we are again with APPLCOMPAT. Help!)

--query 6
SELECT LOGICAL_PART, DATE(LIMITKEY)
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 6
An error occurred while processing the results. - THE DATE, TIME, OR TIMESTAMP VALUE *N IS INVALID. SQLCODE=-180, SQLSTATE=22007, DRIVER=3.68.61

(ditto.)

--query 7
SELECT LOGICAL_PART, STRIP(LIMITKEY,BOTH,'''')
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 7
LOGICAL_PART 2
------------ ----------
1 2016-01-31

(We can get a result which looks like a DATE from LIMITKEY and doesn’t have apostrophes. This is to set up our final query:)

--query 8
SELECT LOGICAL_PART, DATE(STRIP(LIMITKEY,BOTH,''''))
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
AND LOGICAL_PART = 1
;
--result 8
LOGICAL_PART 2
------------ ----------
1 2016-01-31

(We have successfully used a DATE function to reproduce the DATE value from LIMITKEY. Now, in order to handle a date value as represented in LIMITKEY, we apparently have to STRIP the apostrophes. KEY QUESTION FOR THIS DESIGN DECISION BY IBM: Why is this a good thing???)


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f


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

Sam Baugh

[Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column
(in response to Sam Baugh)
As for the last few queries, at this point you are working with a text
string, and not something that the SQL is going to parse as a formula.

On Fri, Jul 19, 2019 at 8:40 AM Sam Baugh <[login to unmask email]> wrote:

> Query 1, you passed a mathematical formula that appears as if its a date,
> but is not different than date(1+2+3+4);
> Query 3, the subselect needs to be qualified select x.* from (select col
> from table) as x, so add the 'AS X' to the query will get you past the
> application compatibility error
>
> On Fri, Jul 19, 2019 at 8:11 AM Sevetson, Phil <[login to unmask email]>
> wrote:
>
>> A couple of trivial fixes to the early results.
>>
>>
>>
>> Query 1 discussion - tightened up a couple of technical terms
>>
>> Query 3 – fixed the query but unchanged results.
>>
>>
>>
>> Philip Sevetson
>>
>> Computer Systems Manager
>>
>> FISA-OPA
>>
>> 5 Manhattan West
>>
>> New York, NY 10001
>>
>> [login to unmask email]
>>
>> 212-857-1688 w
>>
>> 917-991-7052 m
>>
>> 212-857-1659 f
>>
>> [image: cid:[login to unmask email]
>>
>>
>>
>> *From:* Sevetson, Phil [mailto:[login to unmask email]
>> *Sent:* Friday, July 19, 2019 9:05 AM
>> *To:* [login to unmask email]
>> *Subject:* [DB2-L] - [Db2 V11 z/OS] (Mis) Handling Dates in the
>> SYSTABLEPART.LIMITKEY column
>>
>>
>>
>> I believe it’s now fair to characterize how DB2 handles dates in DB2 and
>> especially LIMITKEY to be counterintuitive, perhaps even contradictory. Let
>> me present a series of SQL statements run just now in DB2V11 through Data
>> Studio.
>>
>>
>>
>> Short version: If you use a literal date value as argument for DATE(),
>> you *must* use apostrophes. However, if you use a column value as
>> argument for date, you *must not* use apostrophes.
>>
>>
>>
>> ********
>>
>>
>>
>> Read that again, please. This is the core of my frustration at the
>> moment. It’s a bloody headache, which I invite you to share with me. Also,
>> I’d appreciate suggestions for how to explain this to my managers, who
>> aren’t ignorant of DB2 but probably aren’t prepared for this.
>>
>>
>>
>>
>>
>> 1. Some information about definition of SYSTABLEPART
>>
>>
>>
>> Column Name Col No Col Type Length
>>
>> ------------------ ------ -------- ------
>>
>> PARTITION 1 SMALLINT 2
>>
>> LIMITKEY 17 VARCHAR 765
>>
>> LOGICAL_PART 37 SMALLINT 2
>>
>>
>>
>> 2. Results of certain queries in DB2 V11
>>
>>
>>
>> --query 1
>>
>> SELECT DATE(2001-01-01) FROM SYSIBM.SYSDUMMY1;
>>
>> --result 1
>>
>> *1*
>>
>> ----------
>>
>> 0006-06-22
>>
>> *(DB2 treated the expression 2001-01-01 as a compound difference,
>> subtracted 1 from 2001, subtracted 1 from the result, and treated the
>> resulting value, 1999, as the argument to the date function)*
>>
>>
>>
>> --query 2
>>
>> SELECT DATE('2001-01-01') FROM SYSIBM.SYSDUMMY1;
>>
>> --result 2
>>
>> 1
>>
>> ----------
>>
>> 2001-01-01
>>
>> *(DB2 handled this as the writer intended)*
>>
>>
>>
>> --query 3
>>
>> SELECT DATE(TESTVAL) FROM
>>
>> (SELECT '2001-01-01' AS TESTVAL FROM SYSIBM.SYSDUMMY1);
>>
>> --result 3
>>
>> ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS
>> SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61
>>
>> *(I don’t pretend to understand this. I’d appreciate help finding
>> APPLCOMPAT for the subsystem or for Data Studio, whichever is causing this.
>> However... Db2 is mishandling a CHAR result from a subselect??)*
>>
>>
>>
>> --query 4
>>
>> SELECT LOGICAL_PART, LIMITKEY
>>
>> FROM SYSIBM.SYSTABLEPART
>>
>> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>>
>> AND LOGICAL_PART = 1
>>
>> ;
>>
>> --result 4
>>
>> LOGICAL_PART LIMITKEY
>>
>> ------------ ------------
>>
>> 1 '2016-01-31'
>>
>> *(Here is the raw value from LIMITKEY for a table which is partitioned by
>> date. The same partition and table are used for all remaining queries in
>> this discussion. DB2 adds apostrophes to all values for partitioning
>> columns in LIMITKEY, whose type is DATE, beginning in version 11)*
>>
>>
>>
>> --query 5
>>
>> SELECT DATE(LIMITKEY) FROM
>>
>> (SELECT LOGICAL_PART, LIMITKEY
>>
>> FROM SYSIBM.SYSTABLEPART
>>
>> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>>
>> AND LOGICAL_PART = 1
>>
>> );
>>
>> --result 5
>>
>> ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS
>> SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61
>>
>> *(Here we are again with APPLCOMPAT. Help!)*
>>
>>
>>
>> --query 6
>>
>> SELECT LOGICAL_PART, DATE(LIMITKEY)
>>
>> FROM SYSIBM.SYSTABLEPART
>>
>> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>>
>> AND LOGICAL_PART = 1
>>
>> ;
>>
>> --result 6
>>
>> An error occurred while processing the results. - THE DATE, TIME, OR
>> TIMESTAMP VALUE *N IS INVALID. SQLCODE=-180, SQLSTATE=22007, DRIVER=3.68.61
>>
>> *(ditto.)*
>>
>>
>>
>> --query 7
>>
>> SELECT LOGICAL_PART, STRIP(LIMITKEY,BOTH,'''')
>>
>> FROM SYSIBM.SYSTABLEPART
>>
>> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>>
>> AND LOGICAL_PART = 1
>>
>> ;
>>
>> --result 7
>>
>> LOGICAL_PART 2
>>
>> ------------ ----------
>>
>> 1 2016-01-31
>>
>> *(We can get a result which looks like a DATE from LIMITKEY and doesn’t
>> have apostrophes. This is to set up our final query:)*
>>
>>
>>
>> --query 8
>>
>> SELECT LOGICAL_PART, DATE(STRIP(LIMITKEY,BOTH,''''))
>>
>> FROM SYSIBM.SYSTABLEPART
>>
>> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>>
>> AND LOGICAL_PART = 1
>>
>> ;
>>
>> --result 8
>>
>> LOGICAL_PART 2
>>
>> ------------ ----------
>>
>> 1 2016-01-31
>>
>> *(We have successfully used a DATE function to reproduce the DATE value
>> from LIMITKEY. Now, in order to handle a date value as represented in
>> LIMITKEY, we apparently have to STRIP the apostrophes. KEY QUESTION FOR
>> THIS DESIGN DECISION BY IBM: Why is this a good thing???)*
>>
>>
>>
>>
>>
>> Philip Sevetson
>>
>> Computer Systems Manager
>>
>> FISA-OPA
>>
>> 5 Manhattan West
>>
>> New York, NY 10001
>>
>> [login to unmask email]
>>
>> 212-857-1688 w
>>
>> 917-991-7052 m
>>
>> 212-857-1659 f
>>
>> [image: 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-----
>

Isaac Yassin

[Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column
(in response to Philip Sevetson)
Well,
at least the frustration is consistent ��

Isaac Yassin
Sent from my Galaxy Note 5

בתאריך יום ו׳, 19 ביולי 2019, 15:05, מאת Sevetson, Phil ‏<
[login to unmask email]>:

> I believe it’s now fair to characterize how DB2 handles dates in DB2 and
> especially LIMITKEY to be counterintuitive, perhaps even contradictory. Let
> me present a series of SQL statements run just now in DB2V11 through Data
> Studio.
>
>
>
> Short version: If you use a literal date value as argument for DATE(), you
> *must* use apostrophes. However, if you use a column value as argument
> for date, you *must not* use apostrophes.
>
>
>
> ********
>
>
>
> Read that again, please. This is the core of my frustration at the
> moment. It’s a bloody headache, which I invite you to share with me. Also,
> I’d appreciate suggestions for how to explain this to my managers, who
> aren’t ignorant of DB2 but probably aren’t prepared for this.
>
>
>
>
>
> 1. Some information about definition of SYSTABLEPART
>
>
>
> Column Name Col No Col Type Length
>
> ------------------ ------ -------- ------
>
> PARTITION 1 SMALLINT 2
>
> LIMITKEY 17 VARCHAR 765
>
> LOGICAL_PART 37 SMALLINT 2
>
>
>
> 2. Results of certain queries in DB2 V11
>
>
>
> --query 1
>
> SELECT DATE(2001-01-01) FROM SYSIBM.SYSDUMMY1;
>
> --result 1
>
> *1*
>
> ----------
>
> 0006-06-22
>
> *(DB2 treated the argument as a compound difference, subtracted 1 from
> 2001, subtracted 1 from the result, and treated the resulting value, 1999,
> as the input to the date function)*
>
>
>
> --query 2
>
> SELECT DATE('2001-01-01') FROM SYSIBM.SYSDUMMY1;
>
> --result 2
>
> 1
>
> ----------
>
> 2001-01-01
>
> *(DB2 handled this as the writer intended)*
>
> --query 3
>
> SELECT DATE(TESTVAL) FROM
>
> (SELECT '2001-01-01' FROM SYSIBM.SYSDUMMY1);
>
> --result 3
>
> ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS
> SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61
>
> *(I don’t pretend to understand this. I’d appreciate help finding
> APPLCOMPAT for the subsystem or for Data Studio, whichever is causing this.
> However... Db2 is mishandling a CHAR result from a subselect??)*
>
>
>
> --query 4
>
> SELECT LOGICAL_PART, LIMITKEY
>
> FROM SYSIBM.SYSTABLEPART
>
> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>
> AND LOGICAL_PART = 1
>
> ;
>
> --result 4
>
> LOGICAL_PART LIMITKEY
>
> ------------ ------------
>
> 1 '2016-01-31'
>
> *(Here is the raw value from LIMITKEY for a table which is partitioned by
> date. The same partition and table are used for all remaining queries in
> this discussion. DB2 adds apostrophes to all values for partitioning
> columns in LIMITKEY, whose type is DATE, beginning in version 11)*
>
>
>
> --query 5
>
> SELECT DATE(LIMITKEY) FROM
>
> (SELECT LOGICAL_PART, LIMITKEY
>
> FROM SYSIBM.SYSTABLEPART
>
> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>
> AND LOGICAL_PART = 1
>
> );
>
> --result 5
>
> ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS
> SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=3.68.61
>
> *(Here we are again with APPLCOMPAT. Help!)*
>
>
>
> --query 6
>
> SELECT LOGICAL_PART, DATE(LIMITKEY)
>
> FROM SYSIBM.SYSTABLEPART
>
> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>
> AND LOGICAL_PART = 1
>
> ;
>
> --result 6
>
> An error occurred while processing the results. - THE DATE, TIME, OR
> TIMESTAMP VALUE *N IS INVALID. SQLCODE=-180, SQLSTATE=22007, DRIVER=3.68.61
>
> *(ditto.)*
>
>
>
> --query 7
>
> SELECT LOGICAL_PART, STRIP(LIMITKEY,BOTH,'''')
>
> FROM SYSIBM.SYSTABLEPART
>
> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>
> AND LOGICAL_PART = 1
>
> ;
>
> --result 7
>
> LOGICAL_PART 2
>
> ------------ ----------
>
> 1 2016-01-31
>
> *(We can get a result which looks like a DATE from LIMITKEY and doesn’t
> have apostrophes. This is to set up our final query:)*
>
>
>
> --query 8
>
> SELECT LOGICAL_PART, DATE(STRIP(LIMITKEY,BOTH,''''))
>
> FROM SYSIBM.SYSTABLEPART
>
> WHERE DBNAME = '[redacted]' AND TSNAME = '[redacted]'
>
> AND LOGICAL_PART = 1
>
> ;
>
> --result 8
>
> LOGICAL_PART 2
>
> ------------ ----------
>
> 1 2016-01-31
>
> *(We have successfully used a DATE function to reproduce the DATE value
> from LIMITKEY. Now, in order to handle a date value as represented in
> LIMITKEY, we apparently have to STRIP the apostrophes. KEY QUESTION FOR
> THIS DESIGN DECISION BY IBM: Why is this a good thing???)*
>
>
>
>
>
> Philip Sevetson
>
> Computer Systems Manager
>
> FISA-OPA
>
> 5 Manhattan West
>
> New York, NY 10001
>
> [login to unmask email]
>
> 212-857-1688 w
>
> 917-991-7052 m
>
> 212-857-1659 f
>
> [image: 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-----
>

Michael Hannan

RE: [Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column
(in response to Philip Sevetson)

In Reply to Philip Sevetson:

(We have successfully used a DATE function to reproduce the DATE value from LIMITKEY. Now, in order to handle a date value as represented in LIMITKEY, we apparently have to STRIP the apostrophes. KEY QUESTION FOR THIS DESIGN DECISION BY IBM: Why is this a good thing???)

Philip,

I don't agree with you. I was very frustrated in the old days when Limitkey did not have the quotes around the Dates and Timestamps, because I was trying to generate DDL from the Catalog, and LIMITKEY contains a series of limtkey values separated by Commas and lack of quotes around dates, caused me a lot of problems to try to insert the quotes back in using my SQL, because DDL specifying a Limitkey needs the quotes. So nothing strange about quotes delimiting something inside a string.

Literals always need the quotes to ensure no arithmetic is implied, as was said before.

Internally IBM stores Dates in a binary form, so not a Character string at all. The external representation is different (fine we want to make it readable) and so is the SQL literal representation. This is quite normal!

Yes, the DATE function in SQL is supposed to much more flexible about the argument contents/format.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Philip Sevetson

[Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column
(in response to Michael Hannan)
Michael,

For your purposes, I can see the current specification being a good fit. On our side, it’s really not.

We use LIMITKEY values to do things like purge data from the oldest partition, so you see “DISCARD FROM [tablename] WHEN [keycolumn, of DATE type] <= DATE([limit key of LOGICAL_PART 1])”. So I have to compose the DISCARD statement using:
SELECT DATE(STRIP(LIMITKEY,BOTH,'''')) FROM SYSIBM.SYSTABLEPART WHERE LOGICAL_PART = 1 AND [specify TSNAME/DBNAME].

On the other end, it’s worse. Generating the ALTER statement based on a table name is a UNION or a JOIN so that you have the physical partition of the MAXVALUE partition, and a value of one month greater than the most recent partition (other than MAXVALUE).

Putting DATE(STRIP(LIMITKEY,BOTH,'''')), instead of DATE(LIMITKEY), in the middle of all of that logic, is not my idea of making things easier to read.

-phil (sevetson)

From: Michael Hannan [mailto:[login to unmask email]
Sent: Friday, July 19, 2019 1:54 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column


In Reply to Philip Sevetson:
(We have successfully used a DATE function to reproduce the DATE value from LIMITKEY. Now, in order to handle a date value as represented in LIMITKEY, we apparently have to STRIP the apostrophes. KEY QUESTION FOR THIS DESIGN DECISION BY IBM: Why is this a good thing???)

Philip,

I don't agree with you. I was very frustrated in the old days when Limitkey did not have the quotes around the Dates and Timestamps, because I was trying to generate DDL from the Catalog, and LIMITKEY contains a series of limtkey values separated by Commas and lack of quotes around dates, caused me a lot of problems to try to insert the quotes back in using my SQL, because DDL specifying a Limitkey needs the quotes. So nothing strange about quotes delimiting something inside a string.

Literals always need the quotes to ensure no arithmetic is implied, as was said before.

Internally IBM stores Dates in a binary form, so not a Character string at all. The external representation is different (fine we want to make it readable) and so is the SQL literal representation. This is quite normal!

Yes, the DATE function in SQL is supposed to much more flexible about the argument contents/format.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----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: [Db2 V11 z/OS] (Mis) Handling Dates in the SYSTABLEPART.LIMITKEY column
(in response to Philip Sevetson)



In Reply to Philip Sevetson:

Michael,

We use LIMITKEY values to do things like purge data from the oldest partition, so you see “DISCARD FROM [tablename] WHEN [keycolumn, of DATE type] <= DATE([limit key of LOGICAL_PART 1])”. So I have to compose the DISCARD statement using:
SELECT DATE(STRIP(LIMITKEY,BOTH,'''')) FROM SYSIBM.SYSTABLEPART WHERE LOGICAL_PART = 1 AND [specify TSNAME/DBNAME].

On the other end, it’s worse. Generating the ALTER statement based on a table name is a UNION or a JOIN so that you have the physical partition of the MAXVALUE partition, and a value of one month greater than the most recent partition (other than MAXVALUE).

Putting DATE(STRIP(LIMITKEY,BOTH,'''')), instead of DATE(LIMITKEY), in the middle of all of that logic, is not my idea of making things easier to read.

LIMITKEY contains a series of Key values of different data types, in an external representation, separated by commas, and quotes around strings and dates, to distinguish from numerics like INTEGER, DECIMAL etc. Makes absolute sense to me.

Parsing it is slightly tricky, but doable for multiple columns limit keys using recursive SQL,

I don't really sympathize. You want to do fancy things, at least it is possible, and only slightly complex for your example.

I wanted to use an SQL to strip out the hostvars and datatypes from the BLOB in SYSPACKSTMT. Yes it was possible, but extremely complex and thn length of hostvar entries varied, so had to use a recursive SQL to chain through them, with starting offsets quite variable. There are no datatype conversion functions that enable a BLOB or binary string to be cast to normal CHAR datatype or even CHAR FOR BIT DATA. However my report needs to be text readable (CHAR). Only functions usable to go to CHAR were HEX and PACK/UNPACK, so processing that BLOB was  maybe 100 times more complex, and you complain about such minor difficulties. Ha ha.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd