1

Is it possible to find the instance name of a DB2 database by querying the catalog metadata? For instance, we can find the columns of tables using SELECT tbname, column_name FROM SYSIBM.SYSCOLUMNS. Is there an analogous query that can get the instance name?

I need this because I am running a query to get the remaining free space in the DB, across several instances. I would prefer to have the query itself tell me the name of the instance.

Running DB2 10.5 on Linux.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
radumanolescu
  • 4,059
  • 2
  • 31
  • 44

2 Answers2

3

For DB2 LUW you can use ENV_INST_INFO. The instance name is in the column INST_NAME:

SELECT INST_NAME FROM SYSIBMADM.ENV_INST_INFO
data_henrik
  • 16,724
  • 2
  • 28
  • 49
  • `select inst_name FROM sysibmadm.env_inst_info` returns a name I recognize, but not the one I need. Let's say my JDBC URL is `jdbc:db2://hostname.company.com:portNumber/InstanceName`. I need the InstanceName. The INST_NAME returned above is a name I can see in the IDE (IBM Data Studio) at "level 2 in the tree". (Sorry - don't know what to call it.) The InstanceName I want is at level 3 in the tree. – radumanolescu Jul 26 '17 at 16:03
  • You don't connect to an instance, but to a database. – data_henrik Jul 26 '17 at 16:06
  • In that case, I want the database name, because the instance name is the same across all our environments. – radumanolescu Jul 26 '17 at 16:08
  • 1
    Mark this as answered and ask another question. – data_henrik Jul 26 '17 at 16:09
0

Depending on your DB2-server version and platform, you might use MON_GET_INSTANCE table function (see IBM DB2 knowledge center for details and example). For the instance name you can use PDLOGMSGS_LAST24HOURS

mao
  • 11,321
  • 2
  • 13
  • 29
  • The documentation does not seem to say that the instance name is returned. Also, I don't have the privileges to run that function, so I cannot confirm or disprove your statement. Thank you for answering, though. https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0060770.html – radumanolescu Jul 26 '17 at 15:24
  • See also PDLOGMSGS_LAST24HOURS (returns column INSTANCENAME) – mao Jul 26 '17 at 15:29
  • `select * FROM sysibmadm.pdlogmsgs_last24hours` runs, but returns nothing for my DB – radumanolescu Jul 26 '17 at 15:57