ibm/trino-db2

Db2 JDBC connector for Trino

Trino Db2 connector

This is a plugin for Trino that allow you to use IBM Db2 Jdbc Connection

Notice that it requires the connected database to be Db2 10 or Db2 LUW 9.7+ or greater versions to meet the precision need of the timestamp data type.

See DEVELOPMENT for information on development process.

Limitation

It supports read/write Timestamp data type up to precision 9 while higher precision will not be preserved.

Connection Configuration

Create new properties file like <catalog-name>.properties inside etc/catalog dir:

connector.name=db2
connection-url=jdbc:db2://ip:port/database
connection-user=myuser
connection-password=mypassword

For a connection with SSL, uses following JDBC URL strings as connection-url:

connection-url=jdbc:db2://ip:port/database:sslConnection=true;

Notices:

• the trailing semi-colon is required. Or it will throw SQLException Invalid database URL syntax.
• You can use db2.iam-api-key to specify API Key instead of user/password if IAM authentication is supported.

See the official document of Db2 JDBC details from the article Connecting programmatically with JDBC.

Configuration Properties

Property Name	Description
db2.varchar-max-length	max length of VARCHAR type in a CREATE TABLE or ALTER TABLE command. default is 32672
db2.iam-api-key	API Key of IBM Cloud IAM. Use this when choosing IAM authentication instead of user/password

Notice: you may need to customize value of db2.varchar-max-length to 32592 when using Db2 warehouse.

Extra credentials Support

Since release 324, it starts to support the idea of extra credentials where it allows trino client user to provide Db2 username and password as extra credentials that are passed directly to the backend Db2 server when running a query.

1. configure this for the Db2 connector catalog properties file:

user-credential-name=db2_user
password-credential-name=db2_password

2. passing credentials directly to Db2 server:

trino --extra-credential db2_user=user1 --extra-credential db2_password=secret

See details from this answer.

trino-db2's Issues

Keeps using uppercase table name for 'CREATE TABLE' statement

From: https://developer.ibm.com/technologies/databases/articles/making-db2-case-insensitive/

Case sensitivity and object names

All database object names (tables, views, columns and so on) are stored in the catalog tables in uppercase unless the identifier is delimited. If you use a delimited name to create the identifier, the exact case of the name is stored in the catalog tables.

Problem

when creating a table from prestosql, the table name is in lowercase, however, when querying this new table, the table name becomes lowercase which causes error like table does not exist.

Picks up prestosql v332

See https://prestosql.io/docs/current/release/release-332.html

How to install?

How do I take the release of this driver and install with trino?

Support to SSL connection

Current version of db2 presto plugin does not support SSL connection.

Updated on June 17, 2019, 2:14PM:
Here is the settings I had for the plugin. To enable the ssl, I used the url with sslConnection set to true.

connector.name=db2
connection-url=jdbc:db2://db2whoc-flex-performance-riubnzs.services.dal.bluemix.net:50001/BLUDB:sslConnection=true
connection-user=myuser
connection-password=mypassword

And here is the error when I am running queries from Presto to access DB2 table:

java.sql.SQLException: Query failed (#20190617_180905_00000_jas3k): [jcc][10165][10051][4.25.13] Invalid database URL syntax: jdbc:db2://db2whoc-flex-performance-riubnzs.services.dal.bluemix.net:50001/BLUDB:sslConnection=true. ERRORCODE=-4461, SQLSTATE=42815
	at io.prestosql.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1839)
	at io.prestosql.jdbc.PrestoResultSet.getColumns(PrestoResultSet.java:1749)
	at io.prestosql.jdbc.PrestoResultSet.<init>(PrestoResultSet.java:118)
	at io.prestosql.jdbc.PrestoStatement.internalExecute(PrestoStatement.java:251)
	at io.prestosql.jdbc.PrestoStatement.execute(PrestoStatement.java:229)
	at org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:748)
	at org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:831)
	at org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:103)
	at org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:632)
	at org.apache.zeppelin.scheduler.Job.run(Job.java:188)
	at org.apache.zeppelin.scheduler.FIFOScheduler$1.run(FIFOScheduler.java:140)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: io.prestosql.spi.PrestoException: [jcc][10165][10051][4.25.13] Invalid database URL syntax: jdbc:db2://db2whoc-flex-performance-riubnzs.services.dal.bluemix.net:50001/BLUDB:sslConnection=true. ERRORCODE=-4461, SQLSTATE=42815
	at io.prestosql.plugin.jdbc.BaseJdbcClient.getSchemaNames(BaseJdbcClient.java:132)
	at io.prestosql.plugin.jdbc.JdbcClient.schemaExists(JdbcClient.java:34)
	at io.prestosql.plugin.jdbc.JdbcMetadata.schemaExists(JdbcMetadata.java:67)
	at io.prestosql.metadata.MetadataManager.lambda$schemaExists$0(MetadataManager.java:290)

So it seems the plugin does not recognize the sslConnection option appended to the end of URL.

Upgrade to trino 364

Upgrade to 364 to support newest trino version.

Support data type timestamp(3) with time zone

When using the function from_iso8601_timestamp(), it reports error:

Unsupported column type: timestamp(3) with time zone

Not able to delete or update

What happened

Given prestosql cluster configured with this connector to a db2 warehouse:

delete from <catalog_name>.<schema_name>.xxxxxx WHERE dt_month='2020-04'

It reports error information as USER_ERROR:

io.prestosql.spi.PrestoException: This connector does not support updates or deletes
	at io.prestosql.spi.connector.ConnectorMetadata.getUpdateRowIdColumnHandle(ConnectorMetadata.java:451)
	at io.prestosql.metadata.MetadataManager.getUpdateRowIdColumnHandle(MetadataManager.java:816)
	at io.prestosql.sql.planner.QueryPlanner.plan(QueryPlanner.java:266)
	at io.prestosql.sql.planner.LogicalPlanner.createDeletePlan(LogicalPlanner.java:557)
	at io.prestosql.sql.planner.LogicalPlanner.planStatementWithoutOutput(LogicalPlanner.java:243)
	at io.prestosql.sql.planner.LogicalPlanner.planStatement(LogicalPlanner.java:224)
	at io.prestosql.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:190)
	at io.prestosql.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:185)
	at io.prestosql.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:180)
	at io.prestosql.execution.SqlQueryExecution.doPlanQuery(SqlQueryExecution.java:395)
	at io.prestosql.execution.SqlQueryExecution.planQuery(SqlQueryExecution.java:383)
	at io.prestosql.execution.SqlQueryExecution.start(SqlQueryExecution.java:338)
	at io.prestosql.$gen.Presto_331____20200401_203625_2.run(Unknown Source)
	at io.prestosql.execution.SqlQueryManager.createQuery(SqlQueryManager.java:240)
	at io.prestosql.dispatcher.LocalDispatchQuery.lambda$startExecution$7(LocalDispatchQuery.java:132)
	at io.prestosql.$gen.Presto_331____20200401_203625_2.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)

Expected result

It should work since it supports updates statement via this connector already.

consumes upstream dependencies from prestosql.io

Today

in pom.xml:

...
<groupId>com.facebook.presto</groupId>
...

Expectation

...
<groupId>io.prestosql</groupId>
...

Notice it starts to use new release version since the next revision after 0.215 is 300.

Upgrade to Presto v0.215

To catch up with the latest release of Presto 0.215

Switch build environment to Java@11

This is for aligning with upstream dependency prestosql/presto where it uses Java@11 in the official container images.

see https://github.com/prestosql/presto/blob/master/docker/Dockerfile#L17

Make it usable

This is the first step to setup this repo:

1. reuse code from https://github.com/youngwookim/presto-db2
2. install DB2 driver db2jcc from remote maven repo
3. add some test code
4. enable CI via Travis CI

Support Presto v0.193

Now

it supports Presto v0.167

Expectation

support Presto v0.193 so that it can work with IBM/docker-prestodb

INSERT INTO <table_name> AS SELECT doesn't work

Given SQL:

INSERT INTO db2.<schema_name>.<table_name>
SELECT
        EMAIL,
        GITHUB_LOGIN
    FROM hive.default.<table_name>
    WHERE dt = '2020-03-01'
;

I got the following error message when running via presto:

Query 20200327_155309_00048_dirc7 failed: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;AS SELECT "EMAIL", "GITHUB_LO;WITH DATA, WITH NO DATA, DRIVER=4.25.13

It doesn't expect such error and data should be inserted into the target table

Supports use of extra credentials

Background

I raised the question anyway to support session level connector configuration for presto on stackoverflow to see if it can support scenario like two different presto users can use their own connector level credential. This answer pointed me to the class io.prestosql.plugin.jdbc.credential.ExtraCredentialProvider where the idea extra credential is.

Also see trinodb/trino#1910 for more context.

Plan

Will try to make it happen and document it.

Explore possibility of improving read performance by implementing baseJdbcClient.getSplits()

Out of this thread, I've learned that it could generate multiple splits for a query to boost read performance.

You can take a look at implementations of ConnectorSplitManager#getSplits , CassandraSplitManager is a decent example

Update release asset to support redistribution

Goal

Fixes IBM/docker-trino#5

It discover plugin under the folder ${PRESTO_HOME}/plugin on Presto server. E.g., ${PRESTO_HOME}/plugin/db2. Under this folder, it contains all runtime dependencies (jar files) of this plugin.

Expected result

1. zipball of this plugin and its dependencies under release asset.
   1. working steps in CONTRIBUTING.md on how to use this plugin. -> excluded into #4

Adopt recommended community standards

It reminds me when reviewing #3 that I need community standards to complete https://github.com/IBM/presto-db2/community :

1. contributing guide
2. issue template
3. PR template
4. code of conduct

DB2 Driver

Trying to load the connector into a local copy of the open-source prestoSql/presto repo. It can not find the db2Driver in client module. Does the jar file need to be placed in the module? I thought since it's in there as a Maven dependency it should be downloaded upon building. What is the issue here?

Failed to read column with data type "TIMESTAMP"

Given trino 356 and trino-db 356:

When query table table contains columns with data type TIMESTAMP(3), TIMESTAMP(6) and TIMESTAMP(9), when running select statement, it will report exception like:

May 5 11:43:20 trino-coordinator-cf87bd8bb-pz77v trino-coordinator ERROR remote-task-callback-32	io.trino.execution.StageStateMachine	Stage 20210505_154318_00007_zhr9s.1 failed
io.trino.spi.TrinoException: [jcc][t4][1092][13956][4.25.13] Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461, SQLSTATE=42815
	at io.trino.plugin.jdbc.JdbcRecordCursor.handleSqlException(JdbcRecordCursor.java:299)
	at io.trino.plugin.jdbc.JdbcRecordCursor.getObject(JdbcRecordCursor.java:238)
	at io.trino.spi.connector.RecordPageSource.getNextPage(RecordPageSource.java:116)
	at io.trino.operator.TableScanOperator.getOutput(TableScanOperator.java:301)
	at io.trino.operator.Driver.processInternal(Driver.java:387)
	at io.trino.operator.Driver.lambda$processFor$9(Driver.java:291)
	at io.trino.operator.Driver.tryWithLock(Driver.java:683)
	at io.trino.operator.Driver.processFor(Driver.java:284)
	at io.trino.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:1075)
	at io.trino.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:163)
	at io.trino.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:484)
	at io.trino.$gen.Trino_356____20210505_151828_2.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][t4][1092][13956][4.25.13] Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461, SQLSTATE=42815
	at com.ibm.db2.jcc.am.b6.a(b6.java:810)
	at com.ibm.db2.jcc.am.b6.a(b6.java:66)
	at com.ibm.db2.jcc.am.b6.a(b6.java:133)
	at com.ibm.db2.jcc.am.ResultSet.getObject(ResultSet.java:1961)
	at io.trino.plugin.jdbc.StandardColumnMappings.lambda$longTimestampReadFunction$24(StandardColumnMappings.java:508)
	at io.trino.plugin.jdbc.ObjectReadFunction$1.readObject(ObjectReadFunction.java:46)
	at io.trino.plugin.jdbc.JdbcRecordCursor.getObject(JdbcRecordCursor.java:235)
	... 13 more

It will work if the value of the row is NULL.

So I suspect it's a driver specific implementation that it can not use ResultSet.getObject() on data type like Timestamp. see https://github.com/trinodb/trino/blob/3219bc9c810c897dcfeb97ea1b0c5dc21ae5dd79/plugin/trino-base-jdbc/src/main/java/io/trino/plugin/jdbc/StandardColumnMappings.java#L496-L509

Update to 359

To support using trino 359 in https://github.com/IBM/docker-prestodb, we will need to upgrade this plugin.

Supports renaming table

What happened

When using CREATE TABLE AS, it throws SQL Error when target table contains column in int type:

CREATE TABLE IF NOT EXISTS bludb.xxx.summary_table_1
COMMENT 'summary table for test'
AS
SELECT
    CAST(total_members AS INT) AS total_members
FROM hive.default.fact_table_1
WHERE dt = '2020-02-02'

It will throw this error message when running ☝️SQL via prestosql:

Query 20200204_020247_00008_8ks7p failed: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=ALTER TABLE HCW88054.TMP_PRESTO_7;BEGIN-OF-STATEMENT;<rename_stogroup>, DRIVER=4.25.13

Expectation

It should create a new table summary_table_1 with data from ☝️ query from the Hive table fact_table_1.

Publish to maven central

Motivation

While trying to fix #26 it would be simpler for any user to consume this package from the maven central directly.

It could also be easier to consume thispackage from any maven repo like GitHub packages

Todos

• Document release process
• manage release process via maven

Reference

• Guide to uploading artifacts to the Central Repository
• How to Publish Artifacts on Maven Central

java.lang.IllegalArgumentException: Precision is out of range: 10

When querying a Db2 table with column contains type timestamp(10), I got:

Query 20210413_003738_00007_j43yx failed: com.google.common.util.concurrent.UncheckedExecutionException: java.lang.IllegalArgumentException: Precision is out of range: 10

The stack trace from server log:

Apr 12 20:32:50 prestosql-coordinator-bb478d9ff-5hxj5 presto-coordinator ERROR remote-task-callback-137	io.prestosql.execution.StageStateMachine	Stage 20210413_003248_00006_j43yx.2 failed
com.google.common.util.concurrent.UncheckedExecutionException: com.google.common.util.concurrent.UncheckedExecutionException: java.lang.IllegalArgumentException: Precision is out of range: 10
	at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2051)
	at com.google.common.cache.LocalCache.get(LocalCache.java:3951)
	at com.google.common.cache.LocalCache$LocalManualCache.get(LocalCache.java:4871)
	at io.prestosql.plugin.jdbc.CachingJdbcClient.get(CachingJdbcClient.java:535)
	at io.prestosql.plugin.jdbc.CachingJdbcClient.getColumns(CachingJdbcClient.java:134)
	at io.prestosql.plugin.jdbc.JdbcMetadata.getTableMetadata(JdbcMetadata.java:341)
	at io.prestosql.plugin.jdbc.JdbcMetadata.lambda$listTableColumns$5(JdbcMetadata.java:370)
	at java.base/java.util.Optional.ifPresent(Optional.java:183)
	at io.prestosql.plugin.jdbc.JdbcMetadata.listTableColumns(JdbcMetadata.java:370)
	at io.prestosql.metadata.MetadataManager.listTableColumns(MetadataManager.java:600)
	at io.prestosql.metadata.MetadataListing.listTableColumns(MetadataListing.java:135)
	at io.prestosql.connector.informationschema.InformationSchemaPageSource.addColumnsRecords(InformationSchemaPageSource.java:251)
	at io.prestosql.connector.informationschema.InformationSchemaPageSource.buildPages(InformationSchemaPageSource.java:216)
	at io.prestosql.connector.informationschema.InformationSchemaPageSource.getNextPage(InformationSchemaPageSource.java:183)
	at io.prestosql.operator.ScanFilterAndProjectOperator$ConnectorPageSourceToPages.process(ScanFilterAndProjectOperator.java:376)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:221)
	at io.prestosql.operator.WorkProcessorUtils$YieldingProcess.process(WorkProcessorUtils.java:181)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:306)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:306)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:306)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:221)
	at io.prestosql.operator.WorkProcessorUtils.lambda$processStateMonitor$2(WorkProcessorUtils.java:200)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils.lambda$flatten$6(WorkProcessorUtils.java:277)
	at io.prestosql.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:319)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:306)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:221)
	at io.prestosql.operator.WorkProcessorUtils.lambda$processStateMonitor$2(WorkProcessorUtils.java:200)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:221)
	at io.prestosql.operator.WorkProcessorUtils.lambda$finishWhen$3(WorkProcessorUtils.java:215)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorSourceOperatorAdapter.getOutput(WorkProcessorSourceOperatorAdapter.java:149)
	at io.prestosql.operator.Driver.processInternal(Driver.java:379)
	at io.prestosql.operator.Driver.lambda$processFor$8(Driver.java:283)
	at io.prestosql.operator.Driver.tryWithLock(Driver.java:675)
	at io.prestosql.operator.Driver.processFor(Driver.java:276)
	at io.prestosql.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:1076)
	at io.prestosql.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:163)
	at io.prestosql.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:484)
	at io.prestosql.$gen.Presto_348____20210412_143813_2.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: com.google.common.util.concurrent.UncheckedExecutionException: java.lang.IllegalArgumentException: Precision is out of range: 10
	at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2051)
	at com.google.common.cache.LocalCache.get(LocalCache.java:3951)
	at com.google.common.cache.LocalCache$LocalManualCache.get(LocalCache.java:4871)
	at io.prestosql.plugin.jdbc.CachingJdbcClient.get(CachingJdbcClient.java:535)
	at io.prestosql.plugin.jdbc.CachingJdbcClient.getColumns(CachingJdbcClient.java:134)
	at io.prestosql.plugin.jdbc.CachingJdbcClient.lambda$getColumns$3(CachingJdbcClient.java:134)
	at com.google.common.cache.LocalCache$LocalManualCache$1.load(LocalCache.java:4876)
	at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529)
	at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278)
	at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155)
	at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045)
	... 50 more
Caused by: java.lang.IllegalArgumentException: Precision is out of range: 10
	at com.google.common.base.Preconditions.checkArgument(Preconditions.java:190)
	at io.prestosql.plugin.jdbc.StandardColumnMappings.timestampColumnMappingUsingSqlTimestamp(StandardColumnMappings.java:442)
	at io.prestosql.plugin.db2.DB2Client.toPrestoType(DB2Client.java:98)
	at io.prestosql.plugin.jdbc.BaseJdbcClient.getColumns(BaseJdbcClient.java:290)
	at io.prestosql.plugin.jdbc.ForwardingJdbcClient.getColumns(ForwardingJdbcClient.java:85)
	at io.prestosql.plugin.jdbc.jmx.StatisticsAwareJdbcClient.lambda$getColumns$4(StatisticsAwareJdbcClient.java:101)
	at io.prestosql.plugin.jdbc.jmx.JdbcApiStats.wrap(JdbcApiStats.java:35)
	at io.prestosql.plugin.jdbc.jmx.StatisticsAwareJdbcClient.getColumns(StatisticsAwareJdbcClient.java:101)
	at io.prestosql.plugin.jdbc.CachingJdbcClient.lambda$getColumns$3(CachingJdbcClient.java:134)
	at com.google.common.cache.LocalCache$LocalManualCache$1.load(LocalCache.java:4876)
	at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529)
	at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278)
	at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155)
	at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045)
	... 60 more

Which points to this line of code:

trino-db2/src/main/java/io/trino/plugin/db2/DB2Client.java

Line 98 in a4953ce

TimestampType timestampType = createTimestampType(decimalDigits);

Include asset presto-db2-{tag_name}.tar to release

Today

the asset in release is a zip file.

Expectation

it should include both zip file and tar ball so that it can be consumed by instruction ADD of Dockerfile by docker. E.g., https://github.com/IBM/docker-prestodb/blob/60c9fa432f71d325fdde0e2563194e1328c35d81/Dockerfile

show tables doesn't work with db2 connector

Environment

presto v0.193 provisioned by docker image shawnzhu/prestodb:0.193 from repo IBM/docker-prestodb

Steps

I configured presto to connect to a db2 instance in dashdb.

presto:whitewater> use db2.BLUADMIN;
USE
presto:bluadmin> show tables;
Query 20190320_194229_00014_druzu failed: [jcc][t4][2034][11148][4.25.13] Execution failed due to a distribution protocol error that caused deallocation of the conversation.
A DRDA Data Stream Syntax Error was detected.  Reason: 0x3. ERRORCODE=-4499, SQLSTATE=58009

presto:bluadmin> select count(*) from foo_users;
Query 20190320_194253_00015_druzu failed: [jcc][t4][2034][11148][4.25.13] Execution failed due to a distribution protocol error that caused deallocation of the conversation.
A DRDA Data Stream Syntax Error was detected.  Reason: 0x3. ERRORCODE=-4499, SQLSTATE=58009

I thought it works before when I use another dashdb to try it out.

CREATE TABLE AS SELECT statement doesn't respect NOT NULL constraint

Given table with any column contains NOT NULL constraint
When trying to create a new table via statement CREATE TABLE AS SELECT
Then the create table contains the very column but no constraint NOT NULL

Supports IAM Authentication

Background

Db2 in cloud and Db2 warehouse in IBM Cloud support IAM:

https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.security.doc/doc/iam.html#iam__jdbc

However, it will need different configuration other than user/password. However, such IAM auth is supported by JDBC type 4 driver, just need to patch this repo to support such config like API Key and access token.

checkstyle error

dears,
when i run "mvn clean install " ,166 checkstyle errors happens, how to resolve it? thank u.

[INFO] --- maven-checkstyle-plugin:2.17:check (checkstyle) @ presto-db2 ---
[INFO] There are 166 errors reported by Checkstyle 8.7 with checkstyle/airbase-checks.xml ruleset.
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[1] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[2] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[3] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[4] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[5] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[6] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[7] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[8] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[9] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[10] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[11] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[12] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[13] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[14] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[15] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[16] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[17] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[18] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[19] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[20] (regexp) RegexpMultiline: Line contains carriage return
[ERROR] src\main\java\io\prestosql\plugin\db2\DB2Client.java:[21] (regexp) RegexpMultiline: Line contains carriage return

Make 'INLINE LENGTH' parameter configurable

Before

when creating table, It uses VARCHAR(32672) when given varchar type without length. this is a new feature introduced by #29. see:

https://github.com/IBM/presto-db2/blob/429f54358d1ffe1056c88e092c6b956757e548b3/src/main/java/io/prestosql/plugin/db2/DB2Client.java#L85-L87

Problem

The max length of VARCHAR type is actually different among different Db2 systems:

1. VARCHAR(32672) works when using a standalone db2 server or provisioned in IBM cloud.
2. VARCHAR(32672) does NOT work when using Db2 warehouse with default table space (32KB). See Specifying inline lengths for geospatial columns:

You specify an inline length using the INLINE LENGTH parameter of a CREATE TABLE or ALTER TABLE command.For a table in a 32 KB table space, you can specify an inline length of up to 32592 bytes.

Proposed fix

Make magic number for varchar max length configurable.

Uses Actions for CI

It usually takes me hours to get feedback from Travis CI on code changes.

Going to try GitHub Actions to see if it improves.

Support trinodb 351

it will need to support trinodb when upgrading to 351 or higher revision

ODBC connector or bridge for DB2

This is more of a query than an issue. I couldn't find a recommended channel for queries/questions. My team was looking for DB2 ODBC connector for one of the products, when we came across this connector. We were able to build it on ppc64le RHEL7.6 and the test execution was successful too. However, the product requirement is an ODBC connector. The documentation on the home page clearly mentions that this is a plugin for IBM DB2 Jdbc connection. I was wondering if there is something that can maybe act as a bridge for ODBC using this connector or if you have an ODBC DB2 connector. Thanks!

'CREATE TABLE AS' failed with varchar type column

What happened

When using CREATE TABLE AS, it throws SQL Error when target table contains column in varchar type:

CREATE TABLE IF NOT EXISTS bludb.xxx.summary_table_1
COMMENT 'summary table for test'
AS
SELECT
    last_day_of_week -- varchar
FROM hive.default.fact_table_1
WHERE dt = '2020-02-02'

It will throw this error message when running ☝️SQL via prestosql:

Query 20200204_020051_00006_8ks7p failed: DB2 SQL Error: SQLCODE=-604, SQLSTATE=42611, SQLERRMC=VARCHAR, DRIVER=4.25.13

Expectation

It should create a new table summary_table_1 with data from ☝️ query from the Hive table fact_table_1.

Support ANALYZE

When I run ANALYZE against a Db2 table, I got:

Query 20200904_150042_00043_qdavr failed: This connector does not support analyze

See details about ANALYZE: https://prestosql.io/docs/current/sql/analyze.html