When using the db2pd command it will not function/work with a catalog-ed database alias as shown below:-
/u03/db2inst1>db2pd -db csnxhadr -tablespaces
Database CSNXHADR not activated on database member 0 or this database name cannot be found in the local database directory.
Option -tablespaces requires -db <database> or -alldbs option and active database.
/u03/db2inst1>db2pd -db csnxhadr -hadr
Database CSNXHADR not activated on database member 0 or this database name cannot be found in the local database directory.
Option -hadr requires -db <database> or -alldbs option and active database.
It should work but it doesn't, can this be looked into please as it is causing the monitor script in an IBM PowerHA AIX setup to fail and constantly causing the service to failover to the other node in a continuous loop.
Setup info:
/u03/db2inst1>db2 list db directory
System Database Directory
Number of entries in the directory = 2
Database 1 entry:
Database alias = CSNXHADR
Database name = CSNXDB
Local database directory = /u02
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = CSNXDB
Database name = CSNXDB
Local database directory = /u02
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname = TPRdb02
Alternate server port number = 50000
The database was created as CSNXDB.
The database alias CSNXHADR was created with the command
db2 catalog db csnxdb as csnxhadr
As the database is activated on the CSNXDB entry which is the main database entry and the one used by the client application, this is correct and the intended behaviour.
You can not activate the database on the alias CSNXHADR as shown below.
/u03/db2inst1>db2 activate db csnxhadr
SQL1490W Activate database is successful, however, the database has already been activated on one or more nodes.
As recommended in the Db2 HADR Automatic Client Reroute (ACR) documentation at
https://www.ibm.com/docs/en/db2/11.5?topic=reroute-automatic-client-limitations
- Do not run high availability disaster recovery (HADR) commands (START HADR, STOP HADR, or TAKEOVER HADR) on client reroute-enabled database aliases. HADR commands are implemented to identify the target database using database aliases. Consequently, if the target database has an alternative database defined, it is difficult for HADR commands to determine the database on which the command is actually operating. A client might need to connect using a client reroute-enabled alias, but HADR commands must be applied on a specific database. To accommodate this, you can define aliases specific to the primary and standby databases and only run HADR commands on those aliases.
This is also recommended on the Db2 HADR Wiki Client Reroute
https://ibm.github.io/db2-hadr-wiki/clientReroute.html
Usage Notes
HADR Admin Commands
The HADR commands (start/stop/takeover HADR) connects to the database via the usual client-server connection. If the database alias used in an HADR command has ACR enabled, you don't have control on whether the command is directed to the primary or the standby. Thus HADR commands should run on a database alias that has no ACR (or any other reroute method). For admin purpose, you may define an alias with ACR for application access, and an alias with no ACR for HADR commands.
On following the advice in these two locations for IBM Db2 HADR Client Reorute , it turns out that the db2pd -db XXXXX will not work with the alias and fails.
Thus making the failover scripting in an PowerHA setup more complicated/complex than is necessary.
This will also benefit other db2pd command options as they could use the alias as well, particularly if aliases have been configured and users have been granted connect or other permissions via the an alias and not via other db directories.
Can db2pd -db XXXX please be fixed so that it will accept a catalog-ed alias?
Thank you.
