We are running into an issue where DB2 more or less uses 2 digit dates…1940 to 2039.
"The Toolbox JDBC driver uses the date format that is set up as the default on the IBM i system. This default is usually set to “mdy” which only supports dates between 1940 and 2039. You can override the date format by specifying the "date format " property when opening the JDBC connection. The best choice is "iso “, which supports a full four-digit date.”
If we do: SELECT CHAR( BIRTH_DATE, ISO) blah blah…it works (i.e. the older dates are in there, but they come back scrambled without doing that)
So, it appears we can pass a connection parameter to of ‘dateFormat’ with value ‘iso’
But, where does that go?
In the entry for db2i in MoquiDefaultConf.xml
For XA-aware drivers most config parameters like these are on the class in the driver that implements XADataSource, looks like for db2i that is: com.ibm.as400.access.AS400JDBCXADataSource
If there is a setter on that class like “setDateFormat()” then there is effectively a database driver property called “dateFormat” so you can add that as an attribute (like dateFormat=“iso”) to the jdbc-inline.xa-parameters element, probably easiest on the existing default element for the db which is on line 598.
If that does work maybe it’s a good default to have there, so let me know and I’ll add it to the OOTB MoquiDefaultConf.xml file.
Thank you, David. That works…we tried it in Websphere and the dates all magically showed up. We will try it on the production machines tonight.
We put the change in production today and it works as well…I think it is a keeper for the db2i xa-properties.
Sounds good, this is in MoquiDefaultConf.xml in commit 04f48df6