How can I check for database availability?

[sgursahaney]

Hello. I have a need for my application to verify that I am still connected to my DB2 database. I am using DB2 CLI under C to interface with DB2 7.1. Every 60 seconds or so, I need to verify that the database is still up and reconnect if necessary. Is there a status command I can issue that would provide me with the necessary information?

Thanks in advance.

 

[sgursahaney]

Alternately, does anybody know of a DB2 function that can tell you whether a database is available to connect to or not?

 

[matchum]

The D/B you are currently connected to can be derived from either:
db2 connect
or (preferably)
db2 get connection state

There are other commands that you may find useful
db2 list active databases
db2 list db directory

Of course, you may also want to begin with
db2ilist
to list the instances or
db2 get instance
to show the current ATTACHment.

 

[sgursahaney]

Do you know which Call Level Interface (CLI) function that the DB2 GET CONNECTION STATE relates to? I have a program written in C that needs to know when the database connection has been lost, or better yet, the state of the database before it attempts to connect. What would be the equivalent CLI function to do this?

Thanks in advance.

 

[matchum]

I take it you are in "SQL SERVER MODE" ?

To dump the CLI handle array, CALL QSYS/QSQDMPHA. Then work with the spooled files to examine your output. To dump the CLI handle array for a stream other than the one from which you are issuing the command, CALL QSYS/QSQDMPHA PARM('xxxxxx'), where 'xxxxxx' is the job number of the job that is currently running CLI.

You did not say if you were using local or remote. Try the WRKRDBDIRE for entries already known (which I think is what you mean). Otherwise, use ADDRDBDIRE RDB(SYSNAME) RMTLOCNAME(*LOCAL)

Assuming this is your own code and you are not dependant upon a shared executable/macro library, have you preserved the earlier SQLAllocHandle() - function area/values e.g. SQL_HANDLE_DBC? You will know the last results from SQLConnect(); SQLDriverConnect(); SQLBrowseConnect() functions.

Assuming your existing connect CLI is functioning OK, you can use the usual SQLGetConnectAttr() and SQLSetConnectAttr() functions. If you try SQLDriverConnect() with the DSN string and no associated value, it returns CLI connection info with a list of source-names. You can use the SQLDataSources() function to obtain an (alphabetically) ordered list of source-names+descriptions on an iterative basis.

 

[sgursahaney]

Thanks. I am using the SQLGetConnectAttr with the SQL_ATTR_CONNECTION_DEAD to determine if the connection I currently have has been lost. What I need is a CLI function that will tell me whether a database connection can be made to a defined database. The reason I need this function is because my code switches from a primary database to a backup database when the primary database is down. But, I have a requirement to immediately reconnect to the primary database when it becomes available. So, periodically I need to check to see if the primary database is back up. Any thoughts on how to do this without actually trying to reconnect to the database periodically?

 

[matchum]

The quick answer (as mentioned yesterday) is the command
db2 list active databases
but of course you want the CLI equivalent.

I will dig around and come back if I find it. In the mean time, can that db2 command be triggered to generate the list of available databases ?

 

[matchum]

I just realised that with UDB 7.2 you now get the "ping" command which tells you the elapsed time in microseconds between the client and (DB) server. This fits your scenario where you are connected to DB "B" but need to check for the availability of DB "A" without losing your current connect state.