Error in DB2 connectivity with splunk DB2

[jimjohn]

When i try to configure a connection to DB2 using splunk DB connect i am getting below exception.
I have coped db2jcc4.jar in the folder...\Splunk\etc\apps\dbx\bin\lib.I am using windows 7 64 bit machine.

Encountered the following error while trying to save: In handler 'databases': Error validating database: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-142, SQLSTATE=42612, SQLERRMC=null, DRIVER=4.16.53

[dwaddle]

What type of DB2 database is it? The error codes suggest it is DB2 on Z/OS, and you (or DBX) are attempting to run an SQL statement that is not supported.

-142

THE SQL STATEMENT IS NOT SUPPORTED
Explanation

An SQL statement was detected that is
not supported by the database. The
statement might be valid for other
IBM® relational database products or
it might be valid in another context.
For example, statements such as VALUES
and SIGNAL or RESIGNAL SQLSTATE can be
used only in certain contexts, such as
in a trigger body or in an SQL
Procedure.

I would take a guess that Splunk DBX is attempting to use an SQL VALUES statement like VALUES CURRENT TIMESTAMP or VALUES 1 to validate database connectivity, and this does not work with DB/2 for Z/OS.

This looks like the same issue:

http://answers.splunk.com/answers/117024/splunk-db-connect-db2

Use one of the solutions provided there, like defining a new database type just for z/OS.

[jimjohn]

Thanks ,
I am able to connect with DB with the solution.

[mgranger1]

I'm having the same problem, and while the fixes mentioned in the linked post seemed to clear things up for DB Connect 1.x, it doesn't seem to be working for DB Connect 2.x. Anyone have any thoughts?