Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


DB2 on AS/400 (iSeries) -- How do I list databases, tables, views?

Posted on 2007-10-11
Medium Priority
Last Modified: 2012-06-27
I am working on a tool to issue queries to DB2 on an AS/400 (iSeries) system.  http://sourceforge.net/projects/sqlqueryagent

Is there a way, using SQL, to:
1.  List all the databases on that instance of DB2
2.  List all the tables in a DB
3.  List all the view in a DB

Question by:Daniel Wilson
LVL 37

Accepted Solution

momi_sabag earned 1400 total points
ID: 20055521
get a list of all databases :
i don't know if you can do that using sql

list of tables
select table_name from qsys2.systables where table_type='T'

list view
select table_name from qsys2.systables where table_type='V'
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 600 total points
ID: 20057701

Momi's right on the money.

Personally, I'd add the schema to the select-list:

select table_schema,
from    systables    
where  table_type='T'

In AS/400, a database is the same thing as a schema. To get a list of all databases (a.k.a. schemas):

select distinct table_schema
from   systables


Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question