year(DATE) returning char

Massimiliano Campagnoli

year(DATE) returning char

Hello.

I've just discovered that queries below both work well on DB2 LUW V11, where I would have expected the first one to fail because YEAR() should return an INT and not a CHAR.

In previous version of DB2 (tested on V8.2) the first one fails as it should.

Can you please clarify ?

 

SELECT SALES_DATE, SALES_PERSON, REGION, SALES from DB2INST1.SALES where year( SALES_DATE) = '1996'
SALES_DATE SALES_PERSON REGION SALES 
---------- --------------- --------------- -----------
03/29/1996 LEE Ontario-North 2
1 record(s) selected.
SELECT SALES_DATE, SALES_PERSON, REGION, SALES from DB2INST1.SALES where year( SALES_DATE) = 1996
SALES_DATE SALES_PERSON REGION SALES 
---------- --------------- --------------- -----------
03/29/1996 LEE Ontario-North 2
1 record(s) selected.

 

 

Kal Sub

RE: year(DATE) returning char
(in response to Massimiliano Campagnoli)


Hi,

This is due to implicit conversion that got introduced I think in 9.7   Or maybe 10.1 . 

Regards 

Kals

In Reply to Massimiliano Campagnoli:

Hello.

I've just discovered that queries below both work well on DB2 LUW V11, where I would have expected the first one to fail because YEAR() should return an INT and not a CHAR.

In previous version of DB2 (tested on V8.2) the first one fails as it should.

Can you please clarify ?

 

SELECT SALES_DATE, SALES_PERSON, REGION, SALES from DB2INST1.SALES where year( SALES_DATE) = '1996'
SALES_DATE SALES_PERSON REGION SALES 
---------- --------------- --------------- -----------
03/29/1996 LEE Ontario-North 2
1 record(s) selected.
SELECT SALES_DATE, SALES_PERSON, REGION, SALES from DB2INST1.SALES where year( SALES_DATE) = 1996
SALES_DATE SALES_PERSON REGION SALES 
---------- --------------- --------------- -----------
03/29/1996 LEE Ontario-North 2
1 record(s) selected.

 

 

Philip Sevetson

year(DATE) returning char
(in response to Kal Sub)
To extend the analysis, this works in DB2 on z/OS (v11) as well:

SELECT COUNT(*)
FROM SYSIBM.SYSDUMMY1
WHERE YEAR(CURRENT DATE) = '2019';
SELECT COUNT(*)
FROM SYSIBM.SYSDUMMY1
WHERE YEAR(CURRENT DATE) = 2019;

1> SELECT COUNT(*)
2> FROM SYSIBM.SYSDUMMY1
3> WHERE YEAR(CURRENT DATE) = '2019'
4> go
1
-
1
1> SELECT COUNT(*)
2> FROM SYSIBM.SYSDUMMY1
3> WHERE YEAR(CURRENT DATE) = 2019
4> go
1
-
1
1>
2> go
1> Total execution time => 33 ms
2> go

[identifying data omitted]

From: Kal Sub [mailto:[login to unmask email]
Sent: Friday, March 01, 2019 1:06 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: year(DATE) returning char


Hi,

This is due to implicit conversion that got introduced I think in 9.7 Or maybe 10.1 .

Regards

Kals

In Reply to Massimiliano Campagnoli:

Hello.

I've just discovered that queries below both work well on DB2 LUW V11, where I would have expected the first one to fail because YEAR() should return an INT and not a CHAR.

In previous version of DB2 (tested on V8.2) the first one fails as it should.

Can you please clarify ?



SELECT SALES_DATE, SALES_PERSON, REGION, SALES from DB2INST1.SALES where year( SALES_DATE) = '1996'

SALES_DATE SALES_PERSON REGION SALES
---------- --------------- --------------- -----------
03/29/1996 LEE Ontario-North 2

1 record(s) selected.

SELECT SALES_DATE, SALES_PERSON, REGION, SALES from DB2INST1.SALES where year( SALES_DATE) = 1996

SALES_DATE SALES_PERSON REGION SALES
---------- --------------- --------------- -----------
03/29/1996 LEE Ontario-North 2

1 record(s) selected.





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

Massimiliano Campagnoli

year(DATE) returning char
(in response to Philip Sevetson)
THANKS A LOT FOR THE EXPLANATION.


On 01/03/19 14:04, Sevetson, Phil wrote:
>
> To extend the analysis, this works in DB2 on z/OS (v11) as well:
>
> SELECTCOUNT(*)
>
> FROMSYSIBM.SYSDUMMY1
>
> WHEREYEAR(CURRENTDATE) = '2019';
>
> SELECTCOUNT(*)
>
> FROMSYSIBM.SYSDUMMY1
>
> WHEREYEAR(CURRENTDATE) = 2019;
>
> 1> SELECT COUNT(*)
>
> 2> FROM SYSIBM.SYSDUMMY1
>
> 3> WHERE YEAR(CURRENT DATE) = '2019'
>
> 4> go
>
> 1
>
> -
>
> 1
>
> 1> SELECT COUNT(*)
>
> 2> FROM SYSIBM.SYSDUMMY1
>
> 3> WHERE YEAR(CURRENT DATE) = 2019
>
> 4> go
>
> 1
>
> -
>
> 1
>
> 1>
>
> 2> go
>
> 1> Total execution time => 33 ms
>
> 2> go
>
> [identifying data omitted]
>
> *From:*Kal Sub [mailto:[login to unmask email]
> *Sent:* Friday, March 01, 2019 1:06 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: year(DATE) returning char
>
>
> Hi,
>
> This is due to implicit conversion that got introduced I think in 9.7
>   Or maybe 10.1 .
>
> Regards
>
> Kals
>
> In Reply to Massimiliano Campagnoli:
>
> Hello.
>
> I've just discovered that queries below both work well on DB2 LUW
> V11, where I would have expected the first one to fail because
> YEAR() should return an INT and not a CHAR.
>
> In previous version of DB2 (tested on V8.2) the first one fails as
> it should.
>
> Can you please clarify ?
>
> SELECT SALES_DATE, SALES_PERSON, REGION, SALES from DB2INST1.SALES where year( SALES_DATE) = '1996'
>
> SALES_DATE SALES_PERSON REGION SALES
> ---------- --------------- --------------- -----------
> 03/29/1996 LEE Ontario-North 2
>
> 1 record(s) selected.
>
> SELECT SALES_DATE, SALES_PERSON, REGION, SALES from DB2INST1.SALES where year( SALES_DATE) = 1996
>
> SALES_DATE SALES_PERSON REGION SALES
> ---------- --------------- --------------- -----------
> 03/29/1996 LEE Ontario-North 2
>
> 1 record(s) selected.
>
> -----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-----
>
> --
> Il messaggio è stato analizzato alla ricerca di virus o
> contenuti pericolosi da *SKNT Srl http://sknt.it *, ed è
> risultato non infetto.
>
> This message has been checked for virus or dangerous content
> by *SKNT SRL http://www.sknt.it * and seems to be clean.

--
Massimiliano Campagnoli

Walter Janißen

AW: year(DATE) returning char
(in response to Philip Sevetson)
Hi Phil

And this can be a big problem, because when implicit casting comes into play, the performance can be a disaster.

e.g. SELECT something FROM T1 WHERE char-col = integer value

leads to a tablespace scan regardless if this char-col is the first column of an index and only one row would qualify. I am struggling for years to get an improvement here and made several suggestions. I also opened an RFE, nowerdays an idea, which I can’t find. The RFE-number is 56980

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Sevetson, Phil <[login to unmask email]>
Gesendet: Freitag, 1. März 2019 14:05
An: '[login to unmask email]'
Betreff: [DB2-L] - RE: year(DATE) returning char

To extend the analysis, this works in DB2 on z/OS (v11) as well:

SELECT COUNT(*)
FROM SYSIBM.SYSDUMMY1
WHERE YEAR(CURRENT DATE) = '2019';
SELECT COUNT(*)
FROM SYSIBM.SYSDUMMY1
WHERE YEAR(CURRENT DATE) = 2019;

1> SELECT COUNT(*)
2> FROM SYSIBM.SYSDUMMY1
3> WHERE YEAR(CURRENT DATE) = '2019'
4> go
1
-
1
1> SELECT COUNT(*)
2> FROM SYSIBM.SYSDUMMY1
3> WHERE YEAR(CURRENT DATE) = 2019
4> go
1
-
1
1>
2> go
1> Total execution time => 33 ms
2> go

[identifying data omitted]

From: Kal Sub [mailto:[login to unmask email]
Sent: Friday, March 01, 2019 1:06 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: year(DATE) returning char


Hi,

This is due to implicit conversion that got introduced I think in 9.7 Or maybe 10.1 .

Regards

Kals

In Reply to Massimiliano Campagnoli:

Hello.

I've just discovered that queries below both work well on DB2 LUW V11, where I would have expected the first one to fail because YEAR() should return an INT and not a CHAR.

In previous version of DB2 (tested on V8.2) the first one fails as it should.

Can you please clarify ?



SELECT SALES_DATE, SALES_PERSON, REGION, SALES from DB2INST1.SALES where year( SALES_DATE) = '1996'

SALES_DATE SALES_PERSON REGION SALES
---------- --------------- --------------- -----------
03/29/1996 LEE Ontario-North 2

1 record(s) selected.

SELECT SALES_DATE, SALES_PERSON, REGION, SALES from DB2INST1.SALES where year( SALES_DATE) = 1996

SALES_DATE SALES_PERSON REGION SALES
---------- --------------- --------------- -----------
03/29/1996 LEE Ontario-North 2

1 record(s) selected.





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

  • image001.png (2.6k)

Michael Hannan

RE: AW: year(DATE) returning char
(in response to Walter Janißen)

In Reply to Walter Janißen:

Hi Phil

And this can be a big problem, because when implicit casting comes into play, the performance can be a disaster.

e.g. SELECT something FROM T1 WHERE char-col = integer value

leads to a tablespace scan regardless if this char-col is the first column of an index and only one row would qualify. I am struggling for years to get an improvement here and made several suggestions. I also opened an RFE, nowerdays an idea, which I can’t find. The RFE-number is 56980

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

I voted for the RFE too. Implicit cast/conversion has bad performance traps, when Char is unexpectedly Cast to a numeric.

The example given was one of the simpler ones.

DB2 z/OS 10 brought us implicit cast numeric to Char for string operators like Concat and SUBSTR, etc. however for compare unfortunately it is the Char side that gets converted to DECFLOAT(34) for comparison to the numeric side. Performance can be woeful.

To explain a bit more deeply, for DB2 z/OS, a predicate can compare a column or expression that has a numeric Data Type to another that has a Char data type. However the way they decided to do that is not really with performance in mind. 

The logic was that the number could well in in almost any format in the Char field, and comparing the values numerically was desired, so the Char column or expression is cast to Decimal Float first, removing leading blanks etc., and then is compared to the numeric on the other side (for exact match). The predicate is a stage 2 one, and Dec Float is an underprivileged data type anyway, you can't do index on expression with it, as example.

If the Char expression on one side is a literal constant, like in the original example, DB2 may be able to do much better and cast it to the Integer value required to match the YEAR function. I would have to check the manual though and maybe even test access paths. It may still cast to Dec Float (not good).

Now imagine the situation when the Char expression is compared to a numeric with an equals predicate and it turns out to be a very strong filtering join predicate. We might want a Merge Scan Join ideally (Merge Scan only works for Equals predicates), given the expressions/columns were not indexed (incompatible anyway). 

With stage 2 predicate, we can't get the predicate to be one of  the "Merge Columns" matched, so the result is a Cartesian product (Nested Loop Join) with join predicate resolved finally in Stage 2. I won't mention the run time for this query, as not sure it ever would have completed. Cartesian Product style Nested Loop Join is good like that!

I wanted to see Merge Scan join enhanced to make it a bit more flexible, and able to do the equi-join once the data casting, and sort to join sequence was done. I could make an RFE, but maybe no supporters. Who is interested in the limitations of Merge Scan Join? Ha ha.

The bottom line is usually having to rewrite the SQL to explicitly cast one side of a compare to match in the way we want matched, and we may have to materialize the cast value in intermediate work-file (result of a sub-query) in order to get a matching column in an index or index filtering. It is very unlikely we would ever want to cast to Dec Float. Ha ha.

My advice to SQL writers and Programmers. Don't get lazy and allow hidden Casting. Code explicitly the Datatype Casting that you need. Don't store any numbers that will be used for numeric calculation or comparison in a Char Data Type. Char is O.K. for Identification numbers where arithmetic is meaningless. Never put Dates, Times etc. in Char data type. It causes bad predicates in the end. Don't know why any sites did that once we had DB 1.3 with Date/Time datatypes.

Makes little sense to allow implicit casting in V10 that results in horrible performance. Bad Feature! More rope to hang yourself with!

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Mar 12, 2019 - 02:24 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 12, 2019 - 02:30 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 12, 2019 - 02:39 AM (Europe/Berlin)