Need help to get data out of an as400 system

Question

I have a plenty of tables and databases. most of the tables have empty spaces and no table col has a descriptive name.. can somebody help me to get the data out of there? thanks

Answer 1

This statement will pull all fields and field names from all files in a library.

Select system_column_name, column_name, system_table_name, table_name, column_text, column_heading from QSYS2.Syscolumns where table_schema = 'LIBRARY';

Answer 2

Take a look at this link

For documentation purposes here is the key information.

If you need to search one field or one file, follow this code on SQL:

Select * from Syscolumns

or

Select * from Systables

I believe the specific library for this is QSYS2.

Update to add directions with F.R.O.G.:

1. Connect to your server.
2. On the DB2 tab, choose File | New.
3. In the middle pane, enter in your SQL statement. Select * from QSYS2.Syscolumns where table_schema = 'LIBRARY'
4. Press Enter
5. The Statement will run. Narrow your results as needed.
6. Once you get the results you want, you can right click on the bottom pane and see your options from there.

Answer 3

If you have Client Access (or iSeries Access) you have a tool called "Transfer Data from iSeries Server". This will allow you to easily dump all of the data into a CSV or file type of your choice.

Another option is to use CPYTOIMPF. There is a ton of documentation on that tool online.

A third option is F.R.O.G. for IBM i5. This is a free tool that allows you to run SQL statements. You can then export the data to a CSV.

Answer 4

I had to do exactly this a long time ago and ended up using a very crude solution - I used a terminal emulator which had screen recording functionality and then dumped the AS400 data to the terminal. I then wrote a simple program which took the captured terminal session and filtered the text fields into a database. It was ugly, but it was just for a one-off data export to a new system and most importantly it worked.