Simple Db2 Query for Connection Validation

Simple DB2 Query for connection validation

Try values 1.

Also, you can get the current date as

VALUES current date 

or

SELECT current date FROM sysibm.sysdummy1 

You can also get the version info as follows

SELECT service_level, fixpack_num, bld_level
FROM TABLE (sysproc.env_get_inst_info()) as A;

Efficient SQL test query or validation query that will work across all (or most) databases

The jOOQ manual's section about the DUAL table lists the following for jOOQ's select(inline(1)) query:

-- Access
SELECT 1 FROM (SELECT count(*) dual FROM MSysResources) AS dual

-- BigQuery, CockroachDB, Exasol, H2, Ignite, MariaDB, MySQL, PostgreSQL, 
-- Redshift, Snowflake, SQLite, SQL Server, Sybase ASE, Vertica
SELECT 1

-- MemSQL, Oracle
SELECT 1 FROM DUAL

-- CUBRID
SELECT 1 FROM db_root

-- Db2
SELECT 1 FROM SYSIBM.DUAL

-- Derby
SELECT 1 FROM SYSIBM.SYSDUMMY1

-- Firebird
SELECT 1 FROM RDB$DATABASE

-- HANA, Sybase SQL Anywhere
SELECT 1 FROM SYS.DUMMY

-- HSQLDB
SELECT 1 FROM (VALUES(1)) AS dual(dual)

-- Informix
SELECT 1 FROM (SELECT 1 AS dual FROM systables WHERE (tabid = 1)) AS dual

-- Ingres, Teradata
SELECT 1 FROM (SELECT 1 AS "dual") AS "dual"

How to test the connection to a db2 database

SELECT 1 FROM SYSIBM.SYSDUMMY1

cheaper then

SELECT CURRENT SQLID FROM SYSIBM.SYSDUMMY1

DBCP - validationQuery for different Databases

There is not only one validationQuery for all databases. On each database you have to use different validationQuery.

After few hours of googling and testing I have collected this table:

Database validationQuery notes

• hsqldb - select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
• Oracle - select 1 from dual
• DB2 - select 1 from sysibm.sysdummy1
• mysql - /* ping */ select 1
• microsoft SQL Server - select 1 (tested on SQL-Server 9.0, 10.5 [2008])
• postgresql - select 1
• ingres - select 1
• derby - values 1
• H2 - select 1
• Firebird - select 1 from rdb$database
• MariaDb - select 1
• Informix - select 1 from systables
• Hive - select 1
• Impala - select 1

I wrote about it on my blog - validation query for various databases.

In advance there is an example of class, which return validationQuery according to JDBC driver.

Or does anybody have better solution?

Specify schema/instance for DB2 query in connection string

Wow, this one was obvious. I just need CurrentSchema=mySCHEMA in the connection string.

For some reason I didn't connect that dot right away after working through http://www.connectionstrings.com/ibm-db2 (tried all sorts of variations like Schema, Default Schema, etc). Hopefully this helps someone in the future...

---

Related Topics