Watson Studio Local - Error when using small dates (eg. "0001-01-01"): DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=2, DRIVER=4.22.29

Troubleshooting

Problem

You are querying a DB2 table using Spark (com.ibm.db2.jcc.DB2Driver ) and you get an error when date fields with small dates (e.g. 01-01-0001) are present.

For example below query returns an error:

query = f"(SELECT Record, VARCHAR_FORMAT(TIMESTAMP(Date), 'YYYYMMDDHHMI') FROM (VALUES ('A', DATE('0001-01-01'))) AS TEMP(Record, Date)) AS TMP"

Py4JJavaError: An error occurred while calling o928.collectToPython.
: org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 19.0 failed 4 times, most recent failure: Lost task 0.3 in stage 19.0 (TID 71, 9.242.48.115, executor 1): com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=2, DRIVER=4.22.29

Resolving The Problem

The issue has been identified as a serialization issue between Python and Java. In other words the issue lies somewhere in the middle of the JayDeBe API / WSL Python / JDBC or in a combination of all of these.

No errors are produced when using the ibm_db driver. This driver is working fine with all dates, hence this is not a DB2 issue .

Document Location

Worldwide

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSHGWL","label":"IBM Watson Studio Local"},"ARM Category":[{"code":"a8m0z0000001f8iAAA","label":"Modeling->Dataset"}],"ARM Case Number":"TS004412821","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]

Was this topic helpful?

Document Information

More support for:
IBM Watson Studio Local

Component:
Modeling->Dataset

Software version:
All Version(s)

Document number:
6380736

Modified date:
10 December 2020

UID

ibm16380736

IBM Support

Tips