Get DB2 instance name using SQL

Question

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.

What Db2 platform/version?

mustaccio
– mustaccio

2017-07-26 15:25:11 +00:00
Commented Jul 26, 2017 at 15:25 — mustaccio
– mustaccio, Commented Jul 26, 2017 at 15:25

data_henrik · Accepted Answer · 2017-07-26 15:29:04Z

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

answered Jul 26, 2017 at 15:29

data_henrik

17.3k2 gold badges33 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

radumanolescu Over a year ago

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.

data_henrik Over a year ago

You don't connect to an instance, but to a database.

radumanolescu Over a year ago

In that case, I want the database name, because the instance name is the same across all our environments.

data_henrik Over a year ago

Mark this as answered and ask another question.

mao · Accepted Answer · 2017-07-26 15:28:40Z

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

edited Jul 26, 2017 at 15:28

answered Jul 26, 2017 at 15:21

mao

12.4k2 gold badges15 silver badges32 bronze badges

3 Comments

radumanolescu Over a year ago

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. ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/…

mao Over a year ago

See also PDLOGMSGS_LAST24HOURS (returns column INSTANCENAME)

radumanolescu Over a year ago

select * FROM sysibmadm.pdlogmsgs_last24hours runs, but returns nothing for my DB

Collectives™ on Stack Overflow

Get DB2 instance name using SQL

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related