All Apps and Add-ons

SPLUNK DB CONNECT - DB2

Sriram
Communicator

I am trying to setup a DB Connection to DB2 which is running in zos. The connection seem to be successful. However I see the following exception when i try to save the settings. Any Idea what the issue is ? I am able to connect to this database using other db client tools like db2 connect, squirrel etc.

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

1 Solution

btsay_splunk
Splunk Employee
Splunk Employee

"VALUES 1" to test db2 connection seems ok to me as:

http://stackoverflow.com/questions/2775184/simple-db2-query-for-connection-validation

If it doesn't work for z/OS DB2, you may try to provide a customized DB2 service class as:

public class MyDB2 extends com.splunk.dbx.sql.type.impl.DB2 {

  protected String testQuery;

  public MyDB2(com.splunk.config.SplunkConfigStanza config) {
    super(config);
    testQuery = config.get("testQuery", "VALUES 1");
  }

  public String getTestQuery() {
     return testQuery;
  }
}

compile it as a jar and place to dbx/bin/lib folder

in dbx/local folder create or update database_types.conf with

[db2]
displayName = MyDB2
typeClass = mypackage.MyDB2
testQuery = SELECT CURRENT SQLID FROM SYSIBM.SYSDUMMY1

It seems to take the "new" testQuery instead for the connection validation.

View solution in original post

rdelmark
Explorer

We are attempting something similar, we need to connect to a DB2 database running on Zos using the Splunk DB Connect App. Are you able to make this database connection, so far it fails for us. The error we are getting is "An attempt was made to access a database, T3COMN, which was either not found or does not support transactions. ERRORCODE=-4499, SQLSTATE=08004"....There is no record of any connection attempt at the Zos level. I have opened a support case and was told this was not tested on Zos

0 Karma

zindain24
Path Finder

Luan from support provided me another workaround without creating a new JAR. Wanted to share his findings with everyone:

In dbx/local/database_types.conf add the following for zOS:

[db2zOS]
displayName = DB2 on zOS
jdbcDriverClass = com.ibm.db2.jcc.DB2Driver
defaultPort = 3750
connectionUrlFormat = jdbc:db2://{0}:{1}/{2}
testQuery = SELECT 1 FROM SYSIBM.SYSDUMMY1;

Restart Splunk and use your newly added Database Type. It even validates properly.

dwaddle
SplunkTrust
SplunkTrust

DB2 for z/OS is slightly different from DB2 on every other platform. Its SQL support has slight differences, and not supporting the VALUES statement is not supported in every place that it is supported for DB2 on Linux, Unix, or Windows. Defining DB2 for z/OS as its own database type makes a lot of sense.

0 Karma

btsay_splunk
Splunk Employee
Splunk Employee

For DB2 case, it is fine to use generic dbx service class, so the configuration with generic driver class in database_types.conf works.

0 Karma

splunkIT
Splunk Employee
Splunk Employee

btsay_splunk
Splunk Employee
Splunk Employee

"VALUES 1" to test db2 connection seems ok to me as:

http://stackoverflow.com/questions/2775184/simple-db2-query-for-connection-validation

If it doesn't work for z/OS DB2, you may try to provide a customized DB2 service class as:

public class MyDB2 extends com.splunk.dbx.sql.type.impl.DB2 {

  protected String testQuery;

  public MyDB2(com.splunk.config.SplunkConfigStanza config) {
    super(config);
    testQuery = config.get("testQuery", "VALUES 1");
  }

  public String getTestQuery() {
     return testQuery;
  }
}

compile it as a jar and place to dbx/bin/lib folder

in dbx/local folder create or update database_types.conf with

[db2]
displayName = MyDB2
typeClass = mypackage.MyDB2
testQuery = SELECT CURRENT SQLID FROM SYSIBM.SYSDUMMY1

It seems to take the "new" testQuery instead for the connection validation.

Sriram
Communicator

Thank you for providing alternate solution. zOS DB2 doesn't like that "VALUES 1" query. For now I am not doing validation. Also after manually adding default.schema = XXXXX in local - database.conf, I am able to get past the issue and able to pull the tables. I will open up the case to document the issue, so it can be fixed in next release.

0 Karma

araitz
Splunk Employee
Splunk Employee

Can you please open a support case? It will be easier to troubleshoot your issue once we have a case open and you have submitted a diag file.

0 Karma

linu1988
Champion

So could you save the database connection without validating? i dont have a DB to test it on my own.

0 Karma

Sriram
Communicator

Here is overall log.

2014-01-02 17:09:06.812 dbx1240:DEBUG:Database - Validating database connection...
2014-01-02 17:09:06.812 dbx1240:DEBUG:Database - Executing test query: VALUES 1
2014-01-02 17:09:06.843 dbx1240:ERROR:Database - Database validation failed for database Database{name='database', databaseType=com.splunk.dbx.sql.type.impl.DB2@a23610, host='xxxx', port=xxxx, username='xxx', database='xxxx'}: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -142, SQLSTATE: 42612, SQLERRMC: null
..........
com.splunk.dbx.sql.Database.validateConnection(Database.java:479)

0 Karma

Sriram
Communicator

I am giving the correct database info. After enabling the logs to debug level. dbx.log shows the following. During validation, it is trying to execute a testquery and sending an invalid query. I tried to override the database.conf file with this sample testquery "testQuery = SELECT CURRENT SQLID FROM SYSIBM.SYSDUMMY1". It is not taking it. I wonder where this "VALUES 1" query is coming from ? Has anyone successfully tried and tested DB2 connectivity for this app ?

2014-01-02 17:09:06.812 dbx1240:DEBUG:Database - Executing test query: VALUES 1

0 Karma

linu1988
Champion

42612 The statement string is an SQL statement that is not acceptable in the context in which it is presented As per IBM. So are you giving the database info properly? Could you try saving without validate option?

0 Karma
Get Updates on the Splunk Community!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...