CODEWITHSUNDEEP
python-3.xdb2
Madhavan Natarajan
Madhavan Natarajan

Reputation: 17

Query to list the all database in db2

"SELECT name FROM master.sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')" In SQL server this above command used to retrieve the database list. similarly, what is the command to retrieve the database list in DB2 using python program?

db2 server platform : windows database : catalogued

Upvotes: 0

Views: 2816

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12299

The following statement (if you have permissions to use these table functions) returns a list of active databases managed by the same Db2 for LUW instance, which manages the database you are connected to.

-- Preferred method since Db2 9.7
SELECT DISTINCT DB_NAME 
FROM TABLE (MON_GET_MEMORY_SET ('DATABASE', NULL, -2))

-- For Db2 versions before 9.7
SELECT DISTINCT DB_NAME 
FROM TABLE (SNAP_GET_DB (NULL))

You won't see non-active databases (if any) in this list.

Upvotes: 0

mao
mao

Reputation: 12267

With Db2-LUW, you do not use SQL to detect which databases are available, instead that is done via commands, although other methods are available.

For Db2-LUW with catalogued databases, you can use python to read the database directory by parsing the output of the command db2 list db directory assuming that you have a Db2-client which includes the db2 command line processor.

Note that non-IBM (third party) Db2-clients might have other functionality, so this answer only concerns Db2 clients supplied from IBM that include the Db2 CLP program.

You will need to understand the legacy concepts of catalogued databases , all of which are explained in the free online Db2 Knowledge Centre. See commands (non-SQL) list database directory and catalog database and catalog tcpip node etc.

If your databases are not catalogued in the database directory, but instead are configured in XML file called db2dsdriver.cfg or equivalent, then you cannot use this method, although you can parse the XML file looking for DATABASE and DSN stanzas.

Additionally, if your databases are on i-series , then different considerations apply for IBM i access databases.

There is a sample code here, in the function query_sdb_dir (you can parse the output of the command db2 list db directory ). BUT this assumes that you have a Db2 client installed that includes the Db2 CLP (i.e on MS-Windows, that you have db2.exe on the PATH, this is the command line processor CLP). By default with Python ibm_db you do not get this executable because it is not part of the tiny footprint CLIDRIVER that comes by default with the python ibm_db module. But you can first install a Db2-driver that includes the CLP (db2.exe on MS-Windows), set environment variable IBM_DB_HOME to point to its installation location, then pip install ibm_db and it will use your pre-existing Db2-driver and will not install the default clidriver.

There are other ways, typically that involve a database connection and using the monitoring functionality. But the discovery action (without a database connection) seems to be the topic of your question.

Upvotes: 1

Related Questions