1

Is there a way to get a list of the databases on a DB2 server through a linked server query? I tried using an openquery to run "list database directory" but that didn't work.

I am attempting to do this because I do not have direct access to the DB2 server.

Please note that I already have the linked server set up.

3

You cannot get a list of databases via ODBC; ODBC requires a connection to a specific database, and a database does not know anything about the outside world, specifically, if there are any other databases. Depending on a particular DB2 platform, even the concept of "other databases" may be nonexistent.

list database directory is a DB2 CLP command, not an SQL statement, and as such is not understood by the SQL engine.

As for the list of tables in the connected database, you can query the catalog view SYSCAT.TABLES (provided we're talking about DB2 for LUW; refer to the appropriate product manual if not).

1

I was able to query Catalogs, Schemas, and Tables on a Linked Server to DB2 on AS/400 or iSeries from SQL Server 2008 using openquery() and QSYS2.TABLES. Substitute {linked_server} with the name of your Linked Server.

To list Catalogs:

select * from openquery({linked_server}, 'select distinct table_catalog from qsys2.tables')

To list Schemas:

select * from openquery({linked_server}, 'select distinct table_schema from qsys2.tables')

To list Tables

select * from openquery({linked_server}, 'select * from qsys2.tables where table_type = ''BASE TABLE''')

To search for a specific table

select * from openquery({linked_server}, 'select * from qsys2.tables where table_catalog = ''{catalog_name}'' and table_schema = ''{schema_name}'' and table_type = ''BASE TABLE'' and table_name like ''%{name_criteria}%''')
-1

This worked for me. I needed to get a list of tables so I could export the names. I am using a SQL Management Studio and had the DBA setup a linked server to AS/400. I replaced {linked_server} with the name of my linked server

select * from openquery({linked_server}, 'select * from qsys2.tables where table_type = ''BASE TABLE''')
  • 1
    What does your answer add to the existing one? You just copy-pasted a part of it. – mustaccio May 13 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.