DB2 Driver Development - Date Issues

Hello,

I am working on getting a community version of DB2 working in the latest version of Metabase. Currently running into an issue with using dates/timestamp as parameters in a query. I currently get this error back in the UI:
[jcc][1091][10417][4.27.25] Invalid data conversion: Parameter instance 2021-08-25T00:00-04:00 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815

The console is showing this stack trace:
2021-08-25 10:11:20,635 ERROR middleware.catch-exceptions :: Error processing query: null {:database_id 4, :started_at #t "2021-08-25T10:11:18.967490100-04:00[America/New_York]", :state "42815", :json_query {:database 4, :native {:template-tags {:date {:id "e8af606e-dcce-3afd-3fa8-bde625a20d8d", :name "date", :display-name "Date", :type "date", :default "2021-08-25", :required true}}, :query "select *\nfrom ltl400tst3.frp001\njoin etllib.cymd_to_date\n on fhpdat = cymd_date \nwhere date = {{date}}"}, :type "native", :parameters [], :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}}, :status :failed, :class com.ibm.db2.jcc.am.SqlSyntaxErrorException, :stacktrace ["com.ibm.db2.jcc.am.b7.a(b7.java:810)" "com.ibm.db2.jcc.am.b7.a(b7.java:66)" "com.ibm.db2.jcc.am.b7.a(b7.java:116)" "com.ibm.db2.jcc.am.bh.a(bh.java:1496)" "com.ibm.db2.jcc.am.bh.a(bh.java:1436)" "com.ibm.db2.jcc.am.k7.a(k7.java:2603)" "com.ibm.db2.jcc.am.k7.setObject(k7.java:2493)" "com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:1034)" "--> driver.sql_jdbc.execute$set_object.invokeStatic(execute.clj:219)" "driver.sql_jdbc.execute$set_object.invoke(execute.clj:211)" "driver.sql_jdbc.execute$fn__37261.invokeStatic(execute.clj:243)" "driver.sql_jdbc.execute$fn__37261.invoke(execute.clj:241)" "driver.sql_jdbc.execute$fn__37267.invokeStatic(execute.clj:257)" "driver.sql_jdbc.execute$fn__37267.invoke(execute.clj:255)" "driver.sql_jdbc.execute$set_parameters_BANG_$fn__37269.invoke(execute.clj:267)" "driver.sql_jdbc.execute$set_parameters_BANG_.invokeStatic(execute.clj:263)" "driver.sql_jdbc.execute$set_parameters_BANG_.invoke(execute.clj:259)" "driver.sql_jdbc.execute$fn__37273.invokeStatic(execute.clj:282)" "driver.sql_jdbc.execute$fn__37273.invoke(execute.clj:270)" "driver.sql_jdbc.execute$prepared_statement_STAR_.invokeStatic(execute.clj:312)" "driver.sql_jdbc.execute$prepared_statement_STAR_.invoke(execute.clj:309)" "driver.sql_jdbc.execute$statement_or_prepared_statement.invokeStatic(execute.clj:336)" "driver.sql_jdbc.execute$statement_or_prepared_statement.invoke(execute.clj:333)" "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:476)" "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:463)" "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:472)" "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:463)" "driver.sql_jdbc$fn__87275.invokeStatic(sql_jdbc.clj:54)" "driver.sql_jdbc$fn__87275.invoke(sql_jdbc.clj:52)" "query_processor.context$executef.invokeStatic(context.clj:59)" "query_processor.context$executef.invoke(context.clj:48)" "query_processor.context.default$default_runf.invokeStatic(default.clj:68)" "query_processor.context.default$default_runf.invoke(default.clj:66)" "query_processor.context$runf.invokeStatic(context.clj:45)" "query_processor.context$runf.invoke(context.clj:39)" "query_processor.reducible$pivot.invokeStatic(reducible.clj:34)" "query_processor.reducible$pivot.invoke(reducible.clj:31)" "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__48772.invoke(mbql_to_native.clj:25)" "query_processor.middleware.check_features$check_features$fn__47886.invoke(check_features.clj:39)" "query_processor.middleware.limit$limit$fn__48758.invoke(limit.clj:37)" "query_processor.middleware.cache$maybe_return_cached_results$fn__47338.invoke(cache.clj:204)" "query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__49018.invoke(optimize_temporal_filters.clj:204)" "query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__50950.invoke(validate_temporal_bucketing.clj:50)" "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__46457.invoke(auto_parse_filter_values.clj:43)" "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__41867.invoke(wrap_value_literals.clj:161)" "query_processor.middleware.annotate$add_column_info$fn__41764.invoke(annotate.clj:608)" "query_processor.middleware.permissions$check_query_permissions$fn__47758.invoke(permissions.clj:81)" "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__49879.invoke(pre_alias_aggregations.clj:40)" "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__47959.invoke(cumulative_aggregations.clj:60)" "metabase_enterprise.sandbox.query_processor.middleware.row_level_restrictions$apply_row_level_permissions$fn__51468.invoke(row_level_restrictions.clj:335)" "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__50176.invoke(resolve_joined_fields.clj:102)" "query_processor.middleware.resolve_joins$resolve_joins$fn__50489.invoke(resolve_joins.clj:171)" "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__46033.invoke(add_implicit_joins.clj:190)" "query_processor.middleware.large_int_id$convert_id_to_string$fn__48722.invoke(large_int_id.clj:59)" "query_processor.middleware.format_rows$format_rows$fn__48703.invoke(format_rows.clj:74)" "query_processor.middleware.add_default_temporal_unit$add_default_temporal_unit$fn__45327.invoke(add_default_temporal_unit.clj:23)" "query_processor.middleware.desugar$desugar$fn__48025.invoke(desugar.clj:21)" "query_processor.middleware.binning$update_binning_strategy$fn__46844.invoke(binning.clj:229)" "query_processor.middleware.resolve_fields$resolve_fields$fn__47561.invoke(resolve_fields.clj:34)" "query_processor.middleware.add_dimension_projections$add_remapping$fn__45682.invoke(add_dimension_projections.clj:312)" "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__45911.invoke(add_implicit_clauses.clj:147)" "metabase_enterprise.sandbox.query_processor.middleware.row_level_restrictions$apply_row_level_permissions$fn__51468.invoke(row_level_restrictions.clj:335)" "query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__50899.invoke(upgrade_field_literals.clj:40)" "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__46196.invoke(add_source_metadata.clj:123)" "metabase_enterprise.sandbox.query_processor.middleware.column_level_perms_check$maybe_apply_column_level_perms_check$fn__50980.invoke(column_level_perms_check.clj:30)" "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__50051.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)" "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__46404.invoke(auto_bucket_datetimes.clj:147)" "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__47608.invoke(resolve_source_table.clj:45)" "query_processor.middleware.parameters$substitute_parameters$fn__49861.invoke(parameters.clj:111)" "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__47660.invoke(resolve_referenced.clj:79)" "query_processor.middleware.expand_macros$expand_macros$fn__48409.invoke(expand_macros.clj:184)" "query_processor.middleware.add_timezone_info$add_timezone_info$fn__46205.invoke(add_timezone_info.clj:15)" "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__50852.invoke(splice_params_in_response.clj:32)" "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__50062$fn__50066.invoke(resolve_database_and_driver.clj:31)" "driver$do_with_driver.invokeStatic(driver.clj:60)" "driver$do_with_driver.invoke(driver.clj:56)" "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__50062.invoke(resolve_database_and_driver.clj:25)" "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__48649.invoke(fetch_source_query.clj:274)" "query_processor.middleware.store$initialize_store$fn__50861$fn__50862.invoke(store.clj:11)" "query_processor.store$do_with_store.invokeStatic(store.clj:44)" "query_processor.store$do_with_store.invoke(store.clj:38)" "query_processor.middleware.store$initialize_store$fn__50861.invoke(store.clj:10)" "query_processor.middleware.validate$validate_query$fn__50906.invoke(validate.clj:10)" "query_processor.middleware.normalize_query$normalize$fn__48785.invoke(normalize_query.clj:22)" "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__46051.invoke(add_rows_truncated.clj:35)" "metabase_enterprise.audit.query_processor.middleware.handle_audit_queries$handle_internal_queries$fn__32061.invoke(handle_audit_queries.clj:162)" "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__50837.invoke(results_metadata.clj:147)" "query_processor.middleware.constraints$add_default_userland_constraints$fn__47902.invoke(constraints.clj:42)" "query_processor.middleware.process_userland_query$process_userland_query$fn__49948.invoke(process_userland_query.clj:134)" "query_processor.middleware.catch_exceptions$catch_exceptions$fn__47842.invoke(catch_exceptions.clj:173)" "query_processor.reducible$async_qp$qp_STAR___33932$thunk__33933.invoke(reducible.clj:103)" "query_processor.reducible$async_qp$qp_STAR___33932.invoke(reducible.clj:109)" "query_processor.reducible$sync_qp$qp_STAR___33941$fn__33944.invoke(reducible.clj:135)" "query_processor.reducible$sync_qp$qp_STAR___33941.invoke(reducible.clj:134)" "query_processor$process_userland_query.invokeStatic(query_processor.clj:241)" "query_processor$process_userland_query.doInvoke(query_processor.clj:237)" "query_processor$fn__51514$process_query_and_save_execution_BANG___51523$fn__51526.invoke(query_processor.clj:253)" "query_processor$fn__51514$process_query_and_save_execution_BANG___51523.invoke(query_processor.clj:245)" "query_processor$fn__51558$process_query_and_save_with_max_results_constraints_BANG___51567$fn__51570.invoke(query_processor.clj:265)" "query_processor$fn__51558$process_query_and_save_with_max_results_constraints_BANG___51567.invoke(query_processor.clj:258)" "api.dataset$run_query_async$fn__67441.invoke(dataset.clj:56)" "query_processor.streaming$streaming_response_STAR_$fn__67420$fn__67421.invoke(streaming.clj:72)" "query_processor.streaming$streaming_response_STAR_$fn__67420.invoke(streaming.clj:71)" "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)" "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)" "async.streaming_response$do_f_async$fn__20506.invoke(streaming_response.clj:84)"], :context :ad-hoc, :error "[jcc][1091][10417][4.27.25] Invalid data conversion: Parameter instance 2021-08-25T00:00-04:00 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815", :row_count 0, :running_time 0, :data {:rows [], :cols []}}

The driver I am basing this off of has the following functions implemented. Are any of these the one I need to modify to fix the issue I'm experiencing?

For reference, I'm mostly basing off of this https://github.com/alisonrafael/metabase-db2-driver/blob/master/src/metabase/driver/db2.clj

`;; Filtering with dates causes a -245 error. ;;v0.33.x
;; Explicit cast to timestamp when Date function is called to prevent db2 unknown parameter type.
;; Maybe it could not to be necessary with the use of DB2_DEFERRED_PREPARE_SEMANTICS
(defmethod sql.qp/->honeysql [:db2 Date]
[_ date]
(hx/->timestamp (t/format "yyyy-MM-dd" date))) ;;v0.34.x needs it?
;;(hx/->timestamp (du/format-date "yyyy-MM-dd HH:mm:ss" date))) ;;v0.33.x

(defmethod sql.qp/->honeysql [:db2 Timestamp]
[_ date]
(hx/->timestamp (t/format "yyyy-MM-dd HH:mm:ss" date)))

;; MEGA HACK from sqlite.clj ;;v0.34.x
;; Fix to Unrecognized JDBC type: 2014. ERRORCODE=-4228
(defn- zero-time? [t]
(= (t/local-time t) (t/local-time 0)))

(defmethod sql.qp/->honeysql [:db2 LocalDate]
[_ t]
(hsql/call :date (hx/literal (du/format-sql t))))

(defmethod sql.qp/->honeysql [:db2 LocalDateTime]
[driver t]
(if (zero-time? t)
(sql.qp/->honeysql driver (t/local-date t))
(hsql/call :datetime (hx/literal (du/format-sql t)))))

(defmethod sql.qp/->honeysql [:db2 LocalTime]
[_ t]
(hsql/call :time (hx/literal (du/format-sql t))))

(defmethod sql.qp/->honeysql [:db2 OffsetDateTime]
[driver t]
(if (zero-time? t)
(sql.qp/->honeysql driver (t/local-date t))
(hsql/call :datetime (hx/literal (du/format-sql t)))))

(defmethod sql.qp/->honeysql [:db2 OffsetTime]
[_ t]
(hsql/call :time (hx/literal (du/format-sql t))))

(defmethod sql.qp/->honeysql [:db2 ZonedDateTime]
[driver t]
(if (zero-time? t)
(sql.qp/->honeysql driver (t/local-date t))
(hsql/call :datetime (hx/literal (du/format-sql t)))))

;; (.getObject rs i LocalDate) doesn't seem to work, nor does (.getDate); ;;v0.34.x
;; Merged from vertica.clj e sqlite.clj.
;; Fix to Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461
(defmethod sql-jdbc.execute/read-column [:db2 Types/DATE]
[_ _ ^ResultSet rs _ ^Integer i]
(let [s (.getString rs i) t (du/parse s)]
t))

(defmethod sql-jdbc.execute/read-column [:db2 Types/TIME]
[_ _ ^ResultSet rs _ ^Integer i]
(let [s (.getString rs i) t (du/parse s)]
t))

(defmethod sql-jdbc.execute/read-column [:db2 Types/TIMESTAMP]
[_ _ ^ResultSet rs _ ^Integer i]
(let [s (.getString rs i) t (du/parse s)]
t))`

Hi @rcronin
While I have no understanding of Clojure, the timestamp it's failing on looks wrong. It only contains hours and minutes, but no seconds, and then timezone - 2021-08-25T00:00-04:00

I would recommend that you try looking at how other similar drivers might be handling this.
https://github.com/metabase/metabase/pull/17567/files

Is the IBM driver very different from LUW?
https://github.com/damienchambe/metabase-db2-driver
Or how about z/OS?
https://github.com/cabo40/metabase-db2-driver

@flamber - I built the driver from https://github.com/cabo40/metabase-db2-driver and still having the same issue unfortunately.

It seems all the drivers are handling it similarly. The specific parameter I'm trying to pass is a date so not sure why it's including the time component when preparing the statement too.

Something interesting here: I went into the admin panel and changed the timezone to database default. This yield the same error but the date was different this time:

[jcc][1091][10417][4.27.25] Invalid data conversion: Parameter instance 2021-08-17 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815

@rcronin So if all drivers are having the same problem, then perhaps you could try to contact the other developers, since I'm sure more people working with DB2 are able to understand the underlying problem better.
You are seeing this error: https://www.ibm.com/docs/en/db2/11.5?topic=errors-errorcode-4461
Though, I have zero knowledge about DB2 - I just created scripts that did stuff on AS400 terminals.

Thanks for pointing me in this direction. I turned the logging on metabase to trace and I believe I know what's going on. Looks like it's converting the dates to LocalDate Java Types and the IBM driver calls for java.sql.Dates/java.util.Date/java.calendar.Date. More information here: https://www.ibm.com/docs/en/db2/9.7?topic=jsri-data-types-that-map-database-data-types-in-java-applications

I was able to get working by reverting the driver to use (driver/register! :db2 :parent #{:sql-jdbc ::legacy/use-legacy-classes-for-read-and-set}).

This probably isn't ideal long term, but works for now on latest version.

@rcronin Awesome.

It might continue to work for a while, but something you will likely need to change at some point.
Using the same types as Metabase recommends, will normally make sure that it will work with all the functionality in Metabase (binning, filtering, etc).

May I also recommend that you give pointers to the other DB2 driver developers.

Agreed. Once IBM decides to be JDBC Type 4.2 compliant then it'll work natively as such. It appears that the Vertica, Snowflake, Redshift, and Presto drivers are doing the same thing for now.

I will certainly pass this information over to the other DB2ers.

1 Like

Please post full logs when doing the connection

What port # are you using?

Porta 36726

this link can make the connection GitHub - alisonrafael/metabase-db2-driver: DB2 for LUW Driver plugin for Metabase , but I can't include the date filter variable.

I'm trying with this drive but without success for connection GitHub - damienchambe/metabase-db2-driver: AS400 / IBM i DB2 Driver for Metabase

@rcronin is the expert here, but I think that the error indicates it's a networking problem

Can you post the error you're getting for the date filter issue?

I am using a custom fork of the DB2 driver for our organization needs. It borrows from the two repositories you mention. I can only speak of my experience against DB2 for iSeries.

[jcc][1091][10417][4.31.10]
Invalid data conversion: parameter instance 2023-01-01 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815

I'm currently using this plugin GitHub - alisonrafael/metabase-db2-driver: DB2 for LUW Driver plugin for Metabase
could you help me i would be very grateful


how did you make it work?