Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I am using Rational Application Developer to run querys on a database. We are unable to locate anyone who has a list of the tables on the schema and so far the queries we have found to get a list of tables are unsuccessful.

USE <DB>
GO
SELECT *
FROM sys.Tables
GO

Use breaks but I replaced it with CALL.

GO breaks as well and I cannot locate a function that does the same thing.

share|improve this question
 
How does GO break? Sounds painful. Probably you should mention what errors do you get? –  dezso Aug 20 at 15:29
 
Since there is divergent answers based upon the system you are running DB2 on can you answer that question? Are you running DB2 on Linux? Unix? Windows? i5/OS (aka AS/400)? z/OS? –  Chris Aldrich Aug 22 at 13:14

4 Answers

up vote 2 down vote

The given statement smells like MS SQL-Server, not like DB2.

According to IBM Documentation I think SYSCAT.TABLES is the view you are looking for.

share|improve this answer
 
Yes. Specifically you want select tabname from syscat.tables where tabschema = 'my_schema'; –  Chris Aldrich Aug 20 at 18:36
 
Again, this is not DB2 specific, and does not contain nearly as much information as SYSTABLES, or SQLTABLES. –  WarrenT Aug 20 at 21:26
 
@WarrenT -- What is not DB2-specific, if I may ask? DB2 documentation? –  mustaccio Aug 20 at 21:50
 
@WarrenT - either option will work fine (SYSTABLES or TABLES). One is the system catalog table, the other is a system catalog view. Given what Aaron has requested above, the view will be just as fine as the table. –  Chris Aldrich Aug 21 at 13:03
up vote 1 down vote

You were close... leave the period out of "SYSTABLES"

SELECT *
  FROM SYSTABLES;

SYSTABLES is the DB2 standard catalog view. Where you find it depends on which flavor of DB2 server you are using: DB2 for i, LUW, or z/OS.

You shouldn't need GO or other such fiddle-faddle.

Right click on a statement in the SQL file editor, and you'll see options like alt-S to execute current text.

share|improve this answer
up vote 1 down vote

While @user22610's answer will work great if your DB2 platform is Linux/Unix/Windows, it will not work on Mainframe DB2 (z/OS). If you're using that platform, this would be your command:

SELECT *
FROM SYSIBM.SYSTABLES
WHERE CREATOR = @schema
share|improve this answer
 
by mainframe DB2, do you mean DB2 for i, or DB2 for z? –  Chris Aldrich Aug 22 at 13:12
 
I meant z/OS, and edited the question. For some reason, I don't really think of iSeries as "Mainframe". I guess I'm a little biased. :) –  bhamby Aug 22 at 13:15
 
well...I guess technically you are correct. iSeries is probably what would be called a midrange server I think. –  Chris Aldrich Aug 22 at 16:36
up vote -1 down vote

Try this

select table_name from information_schema.tables
share|improve this answer
 
Aaron is looking for the specific query to execute (ie, specific to DB2). Not to be too blunt, but this generic statement is not helpful. –  Chris Aldrich Aug 20 at 20:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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