Table list in DB2 Database.

I am into an IBM AS400 in the Query Utilities for DB2. I have many options but primarily before I can use the Query Manager and Utilities I need to know how to locate the tables in the database.
tmcmasterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:

The easiest way to locate your tables is to use the GUI ... iSeries Navigator (or whatever they call it now). Once you have the connection defined, you just expand the "Databases" tab, then expand the "Schemas" tab, then choose which schema you're interested in. All the objects show up in the right-hand pane.

Alternately, if you like the text-based green-screen, you can find which library a specific object lives in by using WrkObj command.

e.g.

wrkobj *all/MyTable

-- HTH, DaveSlash
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tmcmasterAuthor Commented:
Great answer!  However I don't know which Schemas I need. Is there a way to show the table names. I'm hoping that the table names will make sense for my conversion.

0
Dave FordSoftware Developer / Database AdministratorCommented:

If you don't know the schema, do you at least know the table name?
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

tmcmasterAuthor Commented:
No and that's our problem we get no cooperation from the developer
0
Dave FordSoftware Developer / Database AdministratorCommented:

So, let me see if I understand. You don't know the schema, and you don't know the table name. So, how do you know what you're looking for?
0
tliottaCommented:
tmcmaster:

Query the system catalog. E.g.:

 ==>  select * from qsys2.systables

or

 ==>  select * from qsys2.systables

...depending on your naming convention.

Once you know about the system catalog, which is mostly in library QSYS2 (or schema QSYS2), lots of stuff can be found.

You'll probably want to substring the longer names and run various SELECTs to different tables and views such as SYSVIEWS, SYSPROCS and others.

Tom
0
tmcmasterAuthor Commented:
Generally the tables have common names such as customers, inventory etc. So if I can look at all the schemas I should be able to determine which one their using.  Sounds tough I know but I have absolutely no help from other side.

0
tliottaCommented:
Agggh! Should have been:

 ==>  select * from qsys2/systables

or

 ==>  select * from qsys2.systables

...depending on your naming convention.

Apologies.

Tom
0
tliottaCommented:
And also note:

 ==>  select * from qsys2/schemata

Tom
0
tmcmasterAuthor Commented:
I can get to the iseries navigator and select Databases
Then I get S10ade1b which expands with a schemas button which expands to four (4) Schemas

The one I am interested in is the WFMENU which  will expand and have a list of tables which expand to more menu systems.  When I expand those I get a long list of application menus indicating for example that APM is the Accounts Payable..

The top of the page gives me the ip address xxx.xxx.xxx.xxx(S10ade1b) which I guess is the instance.

I know this is asking a lot but I have not worked on an OS400 system nor used the query manager.

Whatever knowledge you can impart on me would be greatly appreciated.
0
tmcmasterAuthor Commented:
We are getting closer. QSYS2 library has shown up.  However;  I am at the OS/400 Main Menu with a lots of selections.  i.e. selection 4 is files, libraries and folders. Green Screen
I haven't found an opportunity to use the select * statement anywhere.

0
Shalom CarmelCTOCommented:
>  haven't found an opportunity to use the select * statement anywhere.

STRSQL


0
Shalom CarmelCTOCommented:
> STRSQL

You may find that the iseries navigator tools are more convenient :)


0
tmcmasterAuthor Commented:
This working out great.  I got into the database using the iSeries Navigator. I selected a schema  'WFMENU, expanded it and selected tables.  At the bottom of the screen under 'database task' I selected 'Run an SQL script'. This presented the screen that allows the input of SQL statements.

I entered 'SELECT * FROM QSYS2.SYSTABLES;  and this presented all the tables for table owner MSSGROUP which also in this listing I found the schema for tables is  MSSF.  

I then did a 'SELECT * FROM MSSF. APCONT1; ( ONE OF THE TABLES I'M INTERESTED IN), and it showed me the table with coloumns and values. which is fantastic.

My next move is to export these tables into Excel or CSV files, and that should be the home run answer.

All of you are great!

Thanks,
Ted

0
tmcmasterAuthor Commented:
This working out great.  I got into the database using the iSeries Navigator. I selected a schema  'WFMENU, expanded it and selected tables.  At the bottom of the screen under 'database task' I selected 'Run an SQL script'. This presented the screen that allows the input of SQL statements.

I entered 'SELECT * FROM QSYS2.SYSTABLES;  and this presented all the tables for table owner MSSGROUP which also in this listing I found the schema for tables is  MSSF.  

I then did a 'SELECT * FROM MSSF. APCONT1; ( ONE OF THE TABLES I'M INTERESTED IN), and it showed me the table with coloumns and values. which is fantastic.

My next move is to export these tables into Excel or CSV files, and that should be the home run answer.

All of you are great!

Thanks,
Ted

0
tmcmasterAuthor Commented:
Does anybody know how to export table data from the iSeries Navigator
0
Shalom CarmelCTOCommented:
cpytoimpf native command can export a table to csv.

you may be better off to use PC based tools. For example, you can link an excel spreadsheet to an external database via MS-query. All you need is a working ODBC connection.

to export from iSeries Navigator :

In the menu, select "view" and make sure taskpad is checked.
You now get a screen split in two: the lower part contains context sensitive tasks.

open the databases management. Select your database - the one tagged as "local"

In the taskpad, You now have a task called "export data".

Alternatively, find your table in the database manager, right click and select data --> export.


0
tmcmasterAuthor Commented:
Everything is correct except when I select the local database, the taskpad does not give me the 'export data ' option.

Under database tasks I have a help icon i.e. ? and when I expand it i'ts just help.



0
Shalom CarmelCTOCommented:
> the taskpad does not give me the 'export data ' option.

then find your table in the database manager and right click to export.
If you need to automate the file export procedure, you can use a variety of methods.

1. CPYTOIMPF tool.
2. Excel + MSQuery
3. Client Access rtopcb and rxferpcb tools
4. custom code either on iSeries or on another server
5. ETL tools

ShalomC
0
tmcmasterAuthor Commented:
I have awarded all points on this question. Please stop Monitoring.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Operating Systems

From novice to tech pro — start learning today.