SPLUNK DB CONNECT - DB2

[Sriram]

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

[rdelmark]

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

[zindain24]

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]

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.

[btsay_splunk]

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

[splunkIT]

More information for creating a custom database here:

http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/DefineanewDB
http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/database_typesspec

[btsay_splunk · Accepted solution]

"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]

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.

[araitz]

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.

[linu1988]

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

[Sriram]

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)

[Sriram]

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

[linu1988]

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?