#479 Extremely slow queries filtering with date 0001-01-01

[Darren S. Eyers]

The Problem
We are seeing simple date field query taking 12 seconds to complete. If we set the 0001-01-01 to null then would be 500ms! We can not convert to null yet as need to change dependant BE/UI code, so work in progress.

Background
We have backend db2 database on ibmi which has a date field that allows nulls.
The table has 62,256 rows. We using IBM i 7.4 and JTopen 11.2.

Values for the field and occurences count:

Value	Occurences
a date	59
null	3,176
0001-01-01	59,021

The latter is apparently db2 did not originaly support nulls a long time back.

Failed workaround
Tried, well hoped could use paramater: zeroDateTimeBehavior but looks like driver does not support feature. Each value tried seperately and reconnected. Tried as property and on JDBC URL.

Property	Value
zeroDateTimeBehavior	CONVERT_TO_NULL
zeroDateTimeBehavior	convertToNull

select date_col_here
from table_here
where date_col='0001-01-01';

Warning
[SQL0181] Value in date, time, or timestamp string not valid.

Area of code?
https://sourceforge.net/p/jt400/svn/HEAD/tree/tags/JTOpen11_2/src/com/ibm/as400/access/AS400Timestamp.java

Docs: https://www.ibm.com/docs/api/v1/content/ssw_ibm_i_74/rzahh/javadoc/com/ibm/as400/access/AS400Timestamp.htm?view=embed

[John Eberhard]

This project has moved to github: https://github.com/IBM/JTOpen

If the below information does not help, please report the problem there.

It looks like you need to configure your connection to use date format ISO to correctly process the 0001-01-01 date. Here is an example of the behavior without the date format setting and with the date format setting.

~> java -jar jt400.jar "jdbc:as400:$SYSTEM" $USERID $PASSWORD 
>select date('0001-01-01') from sysibm.sysdummy1
00001
null

 *** Warning ***

SQLState: 01534
Message:  [SQL0181] Value in date, time, or timestamp string not valid.
Vendor:   181

>exit
~> java -jar jt400.jar "jdbc:as400:$SYSTEM"";date format=iso" $USERID $PASSWORD 
>select date('0001-01-01') from sysibm.sysdummy1
00001
0001-01-01

[Darren S. Eyers]

Thank you so much, that really helps performance.
Would be nice to have the convertToNull option too, but i'll post in other forum you mentioned.