How can I get the DB structure of DB2 database on AS/400

[juanfd]

I have a database with over 400 tables on an IBM iseries machine runnign AS400. I tried to use db2look but it told me that there was no such procedure (I did it from the AS/400 command line).

Any one has any idea on how to do this?

I am a complete ignorant on DB2 as well as AS400 so a dummy-like response is very much appreciated!

 

[Mercury2]

Check out

http://minilien.com/?XYwy42qoez

or Red Book 'DB2 Universal Database for iSeries SQL Programming
Concepts' at

http://minilien.com/?i8X8D3LN0o

to get the iSeries catalog that includes the views and tables on the system.

 

[juanfd]

Thanks for your post.. but I think I did not express well...

What I ultimately need is the SQL script for recreating all the tables and relationships... I want something like


CREATE TABLE ...

CREATE TABLE ...

thanks!

 

[Mercury2]

Actually I think that you'll have to create your own script from QSYS2/SYSTABLES and QSYS2/SYSCOLUMNS tables, starting with something like :
SELECT a.TABLE_NAME, a.TABLE_TYPE, a.COLUMN_COUNT,
b.COLUMN_NAME, b.DATA_TYPE, b.LENGTH, b.NUMERIC_SCALE,
b.COLUMN_HEADING FROM qsys2/SYSTABLES a JOIN qsys2/SYSCOLUMNS b on
a.TABLE_NAME = b.TABLE_NAME and a.TABLE_OWNER = b.TABLE_OWNER
AND a.FILE_TYPE = 'D' AND a.TABLE_TYPE in ('P', 'L') ; /* D=Data, P=Physical, L=Logical File */
then creating table or view depending on file-type.

 

[flapeyre]

If you have Client Access Operations Navigator installed, that will generate the SQL for the database files.

In the Database Navigator, right-click and select Generate SQL.

Me transmitte sursum, Caledoni!

 

[juanfd]

Thank you for your post, I tried to find the option in the Op Navigator but could not find it maybe it has something to do with the OS400 version or something...

thanks anyway I will have to do this manually.

Good luck to you all

 

[flapeyre]

Client Access Express is on a CD-Rom that came with the AS/400. You install that on a PC; it connects to the AS/400 and allows you to do a number of system-management type things in a GUI (as opposed to a green screen).

Look through all the CDs that came with the AS/400 - if you can't find it, IBM or a business partner can get the CD for you.

Me transmitte sursum, Caledoni!

 

[Shihab71]

Just saw your post.
Maybe you have already achieved what you wanted, but anyway another tip never hurts.

I have done the same by using Sybase PowerDesigner. You will have to purchase it or get an evaluation copy from your local dealer.

Just create an ODBC to the AS/400 library, open PowerDesigner connect to the ODBC and reverse engineer the database.
It will give you almost anything you ever wished for from the database structure.