IBM Support

'Unsupported date format' when calling stored procedure from JDBC application

Troubleshooting


Problem

JDBC application may receive the following error when calling a stored procedure that runs a select query retrieving date type columns.
  
 "[jcc][converters][6010][10969]Unsupported date format.”
JDBC application that runs the select query directly without stored procedure does not get the error.

Cause

This happens when JDBC application receives the unsupported date format data ‘dd-mm-yyyy’ in JDBC.

Diagnosing The Problem

1. Check the database configuration if territory is set to a country using 'dd-mm-yyyy' date format.
$ db2 get db cfg for audb
..
 Database territory                                      = AU
..
The database might be created on a system where 'LANG=en_AU.UTF-8' is set.
2. Collect JCC trace and check the format is returned as 'dd-mm-yyyy' format. 
  
[jcc][t4][time:2023-09-21-16:06:24.154][Thread:Worker-11: call greg.test_func()][tracepoint:2][Reply.fill]^M
[jcc][t4]        RECEIVE BUFFER: QRYDTA              (ASCII)           (EBCDIC)^M
[jcc][t4]        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF^M
[jcc][t4] 0000   015ED05300010158  241BFF0036330800  .^.S...X$...63..  .;}.............^M
[jcc][t4] 0010   55C2160031322D30  362D32303233FF00  U...12-06-2023..  .B..............^M
[jcc][t4] 0020   36330800FBE41D00  31322D30362D3230  63......12-06-20  .....U..........^M
[jcc][t4] 0030   3233FF0021330800  554D230031322D30  23..!3..UM#.12-0  .........(……^M
...

Resolving The Problem

There are two options for the resolution.
1. Using DB2 registry
$ db2set DB2_SQLROUTINE_PREPOPTS="DATETIME ISO"
Restart Db2 instance.
Then recompile the stored procedure.
2. Calling internal built-in function 
 
$ db2 connect to <DBNAME>
$ db2 "call SYSPROC.SET_ROUTINE_OPTS('DATETIME ISO')"
Then, recompile the stored procedure.

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PmVAAU","label":"Programming Interface-\u003EJDBC"}],"ARM Case Number":"TS014236065","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

Modified date:
01 November 2023

UID

ibm17065425

Page Feedback