[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1367
  • Last Modified:

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.
0
tmcmaster
Asked:
tmcmaster
  • 10
  • 4
  • 3
  • +1
7 Solutions
 
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
 
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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
 
shalomcCommented:
>  haven't found an opportunity to use the select * statement anywhere.

STRSQL


0
 
shalomcCommented:
> 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
 
shalomcCommented:
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
 
shalomcCommented:
> 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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now