26

I'm looking for a simple DB2 query that can be used to test if a database connection in pool is still valid. It needs to be a generic query that would execute regardless of which databases exist.

For other database servers, I've used something like 'SELECT 1' or 'SELECT version();'

What would be an equivalent for DB2?

Thanks!

Eric Tuttleman
  • 1,320
  • 1
  • 9
  • 16

1 Answers1

35

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;
DVK
  • 126,886
  • 32
  • 213
  • 327
  • 1
    Thanks for the response. Select 1 returns: ILLEGAL SYMBOL "". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: SQL Code: -104, SQL State: 42601 but adding the (FROM sysibm.sysdummy1) works Thanks a lot for your help! – Eric Tuttleman May 05 '10 at 17:32
  • 3
    First let me say that I'm not very familiar with DB2, so I may have boned up the syntax. I tried "values 1", "SELECT values 1", and "VALUES current date " all of which returned an error. Both "SELECT current date FROM sysibm.sysdummy1" and "SELECT 1 FROM sysibm.sysdummy1" worked well though. This is all through JDBC using the JCC driver on a zOS based server. – Eric Tuttleman May 05 '10 at 19:25