ibmstreams / streamsx.jdbc Goto Github PK

• remove data dir
• set toolkit path

Add support to provide data encryption to the JDBCRun operator.

Added graduation status file, please update.

For process, please refer to here:
https://github.com/IBMStreams/administration/wiki/Project-Incubation-Process

Please create a release of the latest code from master .

Please create a release proposal with the following information:

Toolkit Version Number
What version of Streams will this support?
Confirmation about what testing is done and status
What's changed and new?

therad --> thread

streamsx.jdbc Globalization support - Update messages for translation
Add new error messages for translation

In the logs coming from JDBCRun when there is a data type mismatch:
CDIST2821W A SQL exception occurred. The exception is {0} .
Looks like the exception string is not being inserted into the message.

The documentation for the "credentials" parameter states: This optional parameter specifies the JSON string that contains the jdbc credentials: username, password and jdbcUrl

The code, however is: jdbcUrl = (String)obj.get("jdbcurl");

I have verified that json with 'jdbcUrl' does not work, however, 'jdbcurl' does work.

I believe by convention since json is case sensitive the correct value is 'jdbcurl', however, to be consistent with the parameter names and messages, I believe sticking with "jdbcUrl" is the best approach.

Currently if the value is not present in the json, the message that will be printed reflects: JDBC_URL_NOT_EXIST=CDIST2803E ''jdbcUrl'' is required to create JDBC connection.

I see some issues when I use jdbcRun . When I specify 'sqlFailureAction' to 'log , I do get log of the records but after some records created in the log file I see the pe getting into stopped and unhealthy state and I don't believe after that state it is processing any records. Is there any limit on how many errors one can have or some parameter that jdbcRun to terminate after an error ?

com.ibm.streamsx.jdbc version 1.2.2, oracle 11g , database codeset is amercian_america.utf8
ojdbc7.jar ( and also tried ojdbc6.jar, ojdbc14.jar)
jdbcurl : jdbc:oracle:thin:@192.168.0.1:1521:orcl
jdbcdrivername oracle.jdbc.driver.OracleDriver
JAVA_HOME streams java 1.8
very simple sqlstatement "select dummy from dual", JDBCRun without any error, but the printStringLn of result is empty. sqlstate=0
any hint to resolve this problem?

While creating instance of JDBCClientHelper, can we make fetch size configurable? It will help to tune database round trip according to various environment.

Appears as a single paragraph, making it very hard to read.

http://ibmstreams.github.io/streamsx.jdbc/doc/spldoc/html/tk$com.ibm.streamsx.jdbc/op$com.ibm.streamsx.jdbc$JDBCRun.html

JDBCRun operators failed because the connect exception is not catched. This occurred during a long run when the connection to a bluemix dashDB goes down. I assume this exception should be catched as done for SQLException. See the exception and code below:

at java.sql.DriverManager.getConnection(DriverManager.java:675)
at java.sql.DriverManager.getConnection(DriverManager.java:258)
at com.ibm.streamsx.jdbc.JDBCClientHelper.createConnection(JDBCClientHelper.java:111)
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.setupJDBCConnection(AbstractJDBCOperator.java:420)
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.initialize(AbstractJDBCOperator.java:253)
at com.ibm.streamsx.jdbc.JDBCRun.initialize(JDBCRun.java:363)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter.initialize(OperatorAdapter.java:713)
at com.ibm.streams.operator.internal.jni.JNIBridge.(JNIBridge.java:275)

105 try {
106 nConnectionAttempts ++;
107 if (jdbcConnectionProps != null){
108
109 connection = DriverManager.getConnection(jdbcUrl, jdbcConnectionProps);
110 }else if (jdbcUser != null && jdbcPassword != null){
111 connection = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
112 }else{
113 connection = DriverManager.getConnection(jdbcUrl);
114 }
115 break;
116 } catch (SQLException e) {
117 // If Reconnection Policy is NoRetry, throw SQLException
118 if (reconnectionPolicy == IJDBCConstants.RECONNPOLICY_NORETRY) {
119 throw e;
120 }

The JDBC toolkit has to check the JDBC connection.
In case of any connection problem, it has to try to create a new connection depending to the recontention policy parameter.
If it is not possible to setup a JDBC connection it has to send a sqlcode = -1 via error output port to SPL application.

I am trying to access the sql warnings on inserting records to dash db using the JDBC tookit.
We are using the following call to access the logs-
https://streams-console-c6d1.stage1.ng.bluemix.net/service/domain/instances/50c8e33c-b763-435a-92e6-9278149b123a/jobs/321/pes/805/applicationTraces/f0a91642-cb81-4513-85a4-6573975a9ca9?sortBy=-timeStr

However- I am not seeing any logs in application traces.
When i download the job logs (see attached) I see the following in the log-
13 Aug 2017 07:21:04.779 [11194] TRACE sslWriter M[SSLJsonWriter.cpp:serializeLogMsg:331] - Exit with msg: {"loglevel": "WARN","message": "CDIST2821W A SQL exception occurred. The exception is {0} .","request": "log","aspects": ["domain=standard1","instance=50c8e33c-

On Streams local-
09 Aug 2017 05:25:37.194 [10673] WARN #splapplog,J[0],P[0],n1502022268511repty6c5alilh.InsertRecord M[JavaOp.cpp:log:83] - CDIST2861W A SQL exception occurred. The exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][1091][10404][4.18.56] Invalid data conversion: Parameter instance 2017-08-08 09:48:21 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815

Seems like there is some localization issue where we don't see the full sql error as we should.

To summarise -

1. Not seeing the sql exception/warning logs in application traces.

2. The sql exception not being retrieved properly.

My customer would like to store the JDBC URL along with the username and password in the jdbcProperties file that currently available in the operator. This would go along way toward consistency between credentials, appConfigName, and jdbcProperties parameters.

Any issues with adding that?

https://github.com/IBMStreams/streamsx.jdbc/blob/master/samples/JDBCSample/com.ibm.streamsx.jdbc.sample.jdbcrun/JDBCRunErrorPort.spl
CDISP0051E ERROR: An unknown attribute was specified: sqlMessage.

Similiar to EventStore application configuration the JDBCRun operator should be able to read the stores keystore and truststore password from application configuration.

Support the following properties:
trustStorePassword
keyStorePassword

The docs are vague on how the JDBC driver jar location is specified.

Is jdbcDriverLib a runtime path, or can the driver be part of the sab file?

A section in namespace-info.spl would be really useful describing the setup.

Trying to debug why a JDBC connection cannot be established.

We currently swallow all SQLException, making it very difficult to debug connection problems.
It would be nice to log the SQLException and print the stack trace when the operator is running with DEBUG trace level. Otherwise, user cannot tell reason for connection failure and the application seems to be in unhealthy mode forever.

Unsure if there is something wrong with my app.

(stream<tuple<rstring query> inTuple, sqlStatusType error> Import_3_out0) as
        Import_3 = Import()
    {
        param
            subscription : kind == "JDBC.NOTIFICATION" ;
    }

The import operator is directly connected to an error port in the JDBCRun via export.

I converted the error attribute to Rstring and got the following:
{errorCode=0,sqlState=""}

Looks like both the errorCode and sqlState are empty. Please check code to see if it is properly initialized.

During a POC we had to pump a lot of data into dashDB. To improve throughput, we configured the batch size to 100 and transaction size to 1000. A side effect is that when there is not a constant input rate, the final transaction never gets committed.

One of the consequences is that the table remains locked (due to the open transaction) and no indexes can be created.

RFE: allow configuration of number of seconds when the pending transaction gets committed, regardless whether it has reached batch/transaction size.

Hi, is there any plan to provide an option to close connection after query finishes or after a certain amount of idle time? We added the ability to close connection in our local jdbc toolkit as we don't want the session to open forever.

Include the sql error message in addition to sqlError and sqlState in the data structure.

Please create a release of the latest code from master .

Please create a release proposal with the following information:

• Toolkit Version Number
• What version of Streams will this support?
• Confirmation about what testing is done and status
• What's changed and new?

We don't seem to be handling null correctly.
The database may have empty values, and we are passing null values into the tuple attribute. I got the following exception:
Exception in thread "Thread-14" java.lang.IllegalArgumentException: Null attribute value passed for index '4
at com.ibm.streams.operator.internal.object.ObjectOutputTuple.nullAttribute(ObjectOutputTuple.java:232)
at com.ibm.streams.operator.internal.object.ObjectOutputTuple.setString(ObjectOutputTuple.java:155)
at com.ibm.streams.operator.internal.object.ObjectOutputTuple.setString(ObjectOutputTuple.java:196)
at com.ibm.streamsx.jdbc.JDBCRun.submitOutputTuple(JDBCRun.java:596)
at com.ibm.streamsx.jdbc.JDBCRun.processTuple(JDBCRun.java:462)
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.process(AbstractJDBCOperator.java:277)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter.processTuple(OperatorAdapter.java:1465)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter$1.tuple(OperatorAdapter.java:803)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter$1.tuple(OperatorAdapter.java:796)
at com.ibm.streams.operator.internal.network.DeserializingStream.tuple(DeserializingStream.java:76)
at com.ibm.streams.operator.internal.network.DeserializingStream.processRawTuple(DeserializingStream.java:65)
at com.ibm.streams.operator.internal.runtime.api.InputPortsConduit.processRawTuple(InputPortsConduit.java:100)
at com.ibm.streams.operator.internal.jni.JNIBridge.process(JNIBridge.java:316)

Using an application configuration does not seem to work on the current Master branch. Don Fox created a forum post about this, and I have verified looking at the code and testing that it is not coded properly.

In the JDBCRun operator, when we finish processing the result set, the operator submits a punctuation to mark the end of the result set.

However, in reading the SPDoc, the output port of the JDBCRun is set as punctuation free, which is incorrect.

The operator JDBCRun should support phoenix jdbc.
Apache Phoenix takes your SQL query, compiles it into a series of HBase scans, and orchestrates the running of those scans to produce regular JDBC result sets.
https://phoenix.apache.org/

In my application, I would like to get a notification upon the following events:

1. when JDBCRun lost connection to the server.
2. when JDBCRun successfully connects to the remote server, mainly after a connection is lost.

Currently, we only emit a tuple on the error port upon errors. Is it possible to convert the error port to a notification port, so we get events on when we lose or establish connection?

@ankitpas @yiminy what do you think?

Thanks!

Operator parameter is using getThisToolkitDir() as prefix since the toolkit provides the jars and toolkit opt dir is different than application directory.

Exception in thread "Thread-13" java.lang.NullPointerException
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.setupClassPath(AbstractJDBCOperator.java:480)
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.initialize(AbstractJDBCOperator.java:320)
at com.ibm.streamsx.jdbc.JDBCRun.initialize(JDBCRun.java:417)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter.initialize(OperatorAdapter.java:745)
at com.ibm.streams.operator.internal.jni.JNIBridge.(JNIBridge.java:277)
Exception in thread "Thread-13" java.lang.NullPointerException
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.setupClassPath(AbstractJDBCOperator.java:480)
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.initialize(AbstractJDBCOperator.java:320)
at com.ibm.streamsx.jdbc.JDBCRun.initialize(JDBCRun.java:417)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter.initialize(OperatorAdapter.java:745)
at com.ibm.streams.operator.internal.jni.JNIBridge.(JNIBridge.java:277)
Exception in thread "Thread-13" java.lang.NullPointerException
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.setupClassPath(AbstractJDBCOperator.java:480)
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.initialize(AbstractJDBCOperator.java:320)
at com.ibm.streamsx.jdbc.JDBCRun.initialize(JDBCRun.java:417)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter.initialize(OperatorAdapter.java:745)
at com.ibm.streams.operator.internal.jni.JNIBridge.(JNIBridge.java:277)
Exception in thread "Thread-13" java.lang.NullPointerException
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.setupClassPath(AbstractJDBCOperator.java:480)
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.initialize(AbstractJDBCOperator.java:320)
at com.ibm.streamsx.jdbc.JDBCRun.initialize(JDBCRun.java:417)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter.initialize(OperatorAdapter.java:745)
at com.ibm.streams.operator.internal.jni.JNIBridge.(JNIBridge.java:277)
Exception in thread "Thread-13" java.lang.NullPointerException
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.setupClassPath(AbstractJDBCOperator.java:480)
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.initialize(AbstractJDBCOperator.java:320)
at com.ibm.streamsx.jdbc.JDBCRun.initialize(JDBCRun.java:417)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter.initialize(OperatorAdapter.java:745)
at com.ibm.streams.operator.internal.jni.JNIBridge.(JNIBridge.java:277)
Exception in thread "Thread-13" java.lang.NullPointerException
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.setupClassPath(AbstractJDBCOperator.java:480)
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.initialize(AbstractJDBCOperator.java:320)
at com.ibm.streamsx.jdbc.JDBCRun.initialize(JDBCRun.java:417)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter.initialize(OperatorAdapter.java:745)
at com.ibm.streams.operator.internal.jni.JNIBridge.(JNIBridge.java:277)
Exception in thread "Thread-13" java.lang.NullPointerException
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.setupClassPath(AbstractJDBCOperator.java:480)
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.initialize(AbstractJDBCOperator.java:320)
at com.ibm.streamsx.jdbc.JDBCRun.initialize(JDBCRun.java:417)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter.initialize(OperatorAdapter.java:745)
at com.ibm.streams.operator.internal.jni.JNIBridge.(JNIBridge.java:277)
Exception in thread "Thread-13" java.lang.NullPointerException
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.setupClassPath(AbstractJDBCOperator.java:480)
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.initialize(AbstractJDBCOperator.java:320)
at com.ibm.streamsx.jdbc.JDBCRun.initialize(JDBCRun.java:417)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter.initialize(OperatorAdapter.java:745)
at com.ibm.streams.operator.internal.jni.JNIBridge.(JNIBridge.java:277)
Exception in thread "Thread-13" java.lang.NullPointerException
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.setupClassPath(AbstractJDBCOperator.java:480)
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.initialize(AbstractJDBCOperator.java:320)
at com.ibm.streamsx.jdbc.JDBCRun.initialize(JDBCRun.java:417)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter.initialize(OperatorAdapter.java:745)
at com.ibm.streams.operator.internal.jni.JNIBridge.(JNIBridge.java:277)
Exception in thread "Thread-13" java.lang.NullPointerException
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.setupClassPath(AbstractJDBCOperator.java:480)
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.initialize(AbstractJDBCOperator.java:320)
at com.ibm.streamsx.jdbc.JDBCRun.initialize(JDBCRun.java:417)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter.initialize(OperatorAdapter.java:745)
at com.ibm.streams.operator.internal.jni.JNIBridge.(JNIBridge.java:277)
Exception in thread "Thread-13" java.lang.NullPointerException
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.setupClassPath(AbstractJDBCOperator.java:480)
at com.ibm.streamsx.jdbc.AbstractJDBCOperator.initialize(AbstractJDBCOperator.java:320)
at com.ibm.streamsx.jdbc.JDBCRun.initialize(JDBCRun.java:417)
at com.ibm.streams.operator.internal.runtime.api.OperatorAdapter.initialize(OperatorAdapter.java:745)
at com.ibm.streams.operator.internal.jni.JNIBridge.(JNIBridge.java:277)

The SPLDocs appear to be out of date.

JDBCSample writes its output data to a file, but users cannot access this file when running the sample in the cloud.

The "Use of FileSink to write output data" section of Getting your SPL application ready for the cloud says:

The FileSink operator will function correctly in the cloud, but since only the SPL application can access the file system on a Streams host in the cloud, writing data to files is of limited value.

Upgrade stresmsx.jdbc git pages and SPL documentation.
Add the link of wiki documents to User documentation

When writing an SPL blob, the following SQL error is logged

Invalid data conversion: Parameter instance 746573742D746573742D74657374 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815

The problem is that the jdbs driver is not converting spl blobs to java.sql.Blob when using the setObject() method on the prepared statement. We need ti use the setBlob() method instead.

Tasks

• add correction for writing blobs
• enhance testcase for writing different datatypes
• test behaviour when fetching blobs from DB2
• test with other databases than DB2

Hello,

https://github.com/IBMStreams/streamsx.jdbc/wiki/How-to-setup-a-SSL-configuration-in-DB2-and-in-JDBC-toolkit

The instance had not DB2 Install folder, or any other folder with the keytool tool, in it's $PATH variable and it caused the error on line 50: keytool could not be found.

Usually, the keytool is also in:

• <DB2INSTALL_FOLDER_VERSION>/java/jdk64/bin/keytool
• <DB2INSTALL_FOLDER_VERSION>/java/jdk64/jre/bin/keytool

Db2 EventStore is using com.ibm.db2.jcc.DB2BaseDataSource.PLUGIN_SECURITY

Provide additonal parameters to apply:

keyStoreType, trustStoreType, securityMechanism, pluginName

Provide log messages for different locales via unique message ids: German (de_DE), French (fr_FR), Italian (it_IT), Spanish (es_ES). Brazilian Portuguese (pt_BR), Japanese (ja_JP), Simplified Chinese (zh_CN), Russian (ru_RU), Traditional Chinese (zh_TW), Korean (ko_KR).

An SQL statement can optionally specify an alias for a column. For example, the following SELECT statement specifies "salary" as the alias for the "COL1":

select COL1 as "salary" from MY_TABLE

The JDBCRun operator retrieves the column name using the ResultSetMetaData.getColumnName(...)method. This method will return the actual name of the column (COL1) rather than the preferred name/alias (salary).

The operator should instead use the ResultSetMetaData.getColumnLabel(...) method to get the column name. This has the benefit that users can write SQL select statements and specify the mapping from column name -> attribute name directly in the SQL statement.

JDBCrun did not shut down cleanly in case of SQL error in ORACLE.
It tries to call rollback during the shut down even when the parameter sqlFailureAction is set to "log".

Please update SPLDoc, the toolkit version on it is 1.0.0.
But we have numerous new versions since then. Thanks!

Just noticed there are a couple of issues:

• Which databases and database versions are supported? I looked for a list of supported databases and their versions, but I could not find this information in the SPLDoc. Is this published somewhere?
• The samples all use a Makefile - Can we update to include how to build with VS Code?

as per guidelines the "main" branch shall no longer have the name "master"

• change develop to default branch
• remove master branch

sample application clean-up shall be done when calling "ant clean" in repo root directory

• Expose SPL operator in the com.ibm.streamsx.jdbc toolkit as Python methods
• Python package name could be streamsx.database
• Python wrapper should simplify the DB2 warehouse IBM Cloud service database access
• Find useful parameter set for the python function(s)

Currently the JDBCRun operator support only one jar file as jdbc driver.
But some databases like Teradata delivers 2 jar files as jdbc driver.
Therefor the JDBCRun operator has to support several files as jdbc driver

It would be good to understand how this component works under the hood.

For example, it would seem that database connection pooling would be key for this component to support asych writes to the database if using batch size of one. I have searched for the keyword “pool” in the connector github repo and didn't see any evidence that db connection polling is used.

Also, isn’t clear (unless looking through the java code) what happens when using a batch size greater than one and one or more records fail to commit. Does the whole batch fail, or just individual records?

Be useful to have application configurations be supported as the method to authentication, jdbc URL etc.

The endless loop happens when:
The JDBCRun trying to use the optional output parameter "Error" and the SQL statement is invalid.