Export Data from AS400

I have a customer that is trying to retrieve data from an AS400 to import into a newer system.

I created an ODBC Connection and have been using MS Access to import, but some of the files that are in the DSPLIBL area are not showing up.

Is there another program/way to do this?
Who is Participating?

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

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:
There are numerous ways to accomplish this (all with their own advantages and disadvantages), but since you're not seeing the tables you're looking for, most likely your ODBC data-source needs to be adjusted. First, if you want to use the library-list, ensure that the naming-convention in the ODBC is set to *SYS (not *SQL). Then, ensure that all the requisite libraries are listed in the library-list dialog-box.

Personally, because of difficulties like this, I prefer to use *SQL naming-convention and always qualify my table with their library (a.k.a. schema) using a "dot".

That way, I know EXACTLY what I'm getting ... rather than relying on the library-list.

Also, ensure that the "Library View" on the Catalog pane is set appropriately.


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
Gary PattersonVP Technology / Senior Consultant Commented:
DSPLIBL shows a list of libraries (think of them as folders, but folders that can only be one level deep), and not files.  

I do a lot of data conversion work, and there are an endless number of ways to extract data from the IBM i.

If you don't have a target schema, than I'd probably suggest that you use IBM i Access for Windows (Navigator tool) to extract the DDL for all of the tables you're interested in, create them in SQL Server, and then just use SSIS to copy the data from IBM i DB2 to SQL Server.  Not terribly complex for a small number of tables.

For larger data migrations, I usually just write a series of simple scripts to copy the tables from IBM i DB2 to the target database (Access, SQL Server, MySQL, DB2 UDB, Oracle, etc).  First I create an IBM i program or SQL script to extract DDL for table creation.  Then I tweak that DDL, if required, and run it as an SQL script to create the tables on the new platform.  Another relatively simple script extracts all the IBM i DB2 data to CSVs, and then we just script the target database's native import facility to import from the CSVs.

There are some third-party tools you can buy to help with the process, but is is pretty simple once you get the hang of it and understand how to deal with the occasional oddness that comes up.
truth_talkerAuthor Commented:
Here's where I think my problem might be.  This is a very old system.  And part of the programming was done in S36 and runs an emulator (from what I am told) on the AS400 to make it work.

So, when I go into this particular file using DSPPFM the file is junk.  But when I go into other files I can read the data.

I think that's where my problem is.

When I use the iSeries Transfer on the file I can read on DSPPFM i get a clean .csv.  When i try on the other file.  I get junk again.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

DSPPFM cannot be used to read packed, numeric columns .

There are several ways to view file contents:
1) RUNQRY *N library/file
2) WRKQRY, then create a dummy/temp query in any library, then put a 1 on the first option to select the file to be viewed, then press F5
3) STRSQL, then type normal SQL commands (if the tool/command is installed)

The iSeries File Transfer can read packed fields, so that should work on either file.

If this was an EBCDIC vs. ASCII issue, then the download for all files would be broken. Both ODBC and File Transfer can be configured to convert EBCDIC to ACSII.
Gary PattersonVP Technology / Senior Consultant Commented:
Old S/36 style files are "flat files" - basically database tables with a single field containing an entire row.  Programs read these files and move the data into program-described fields.  

If the file looks meaningless in DSPPFM, it is probably because it just contains numeric data.

If I could see the file, I probably tell you in a minute what it contains and how to go about extracting it.

Does the file contain anything confidential?  If not, download a binary copy using file transfer function (no conversion) and post it here.
Gary PattersonVP Technology / Senior Consultant Commented:
DSPPFM -can- be used to read packed data, you just have to use F10 to put it into hex mode - and you have to know how to read packed numerics.  Packed isn't that complicated.  Decimal point is implied, so you have to know how the field is defined in a program or in the database in order to know where to put the decimal.  Positive numbers have a hex "F" as the last nibble, and negative numbers have a hex "D".

123.45 = x12345F  
-123.45 = x12345D

One big S/36 export problem is that S/36 files often held hierarchical data.

That is, a single file might hold records defining each STORE. Then for each STORE, the same file might hold a bunch of ORDERHEADER rows. Then for each ORDERHEADER, there might be a set of ORDERDETAIL rows, possibly intermixed with ORDERNOTE, ORDERSHIP, and other kinds of details.

Each row type would have a different structure, and the various structures would not be defined in any DBMS but only in each program. SQL has no good way to deal with such files. (They can't be described as "tables" for SQL.)

It's hard to know what a "best" or even "good" way might be to export that data. A description of table rows and data volumes for the S/36 files is a good start.

truth_talkerAuthor Commented:
The file does have social security numbers, etc.  So I can't send it.

Here are a couple screen shots of a file that opens and the one that won't if that will help.

I didn't see an F10 option to put it in hex mode.
Gary PattersonVP Technology / Senior Consultant Commented:
Yeah, WTHIST looks like it contains mostly packed numeric data.  Packed numeric fields don't display meaningful information on DSPPFM.

I didn't see an F10 option to put it in hex mode.

And yet that's exactly what F10 does :-).  

But I do understand why you'd want to confirm the function you're executing before hitting buttons blindly- that's just good sense.

Take a look at F24.  Or put the cursor on the function key row and press F1 for help.

Be aware that if this file contains any confidential info, and you post the hex display, many of us are going o be able to read it and understand it.

The file isn't really "bad" - it just contains numeric data.

Do a


and post the results.  If you see more than a single field listed in the DSPFFD, do a


as suggested by Thomas above and see if that gives you meaningful results.

Bad news for you is that if this -is- S/36 style flat-file data, the conversion process can be significantly more complicated than if it is DB2 data.  As Tom mentions above, one file can contain multiple different record types, and each record type can have a different layout.  Even your more "legible" files can still contain a packed field or two.

When dealing with DB2 files, we've got plenty of tools - like the IBM i Access for Windows File Transfer Function and the CPYTOIMPD command for example - to help get data extracted.  

With S/36 data, there is typically an intermediate step or two, or the need to write some custom data conversion programs.  Most of the time when I'm dealing with S/36 data, I'm converting it into IBM i DB2 tables / physical files, and I have 3rd-party tools I use to simplify that process.

I'm NOT a S/36 expert.  I've done some work on the S/36, the Advanced/36, and the IBM i S/36 Environment, but most of it has been conversion work.  I know that during conversions, we typically get file format information from one of two places:  

RPG program I and O specs.

When you do a S/36 to AS/400 (iSeries / IBM i) migration, there is a step where any IDDU (Interactive Data Definition Utility) data dictionaries are migrated (SAVEFLRD on S/36, RSTS36FLR on IBM i).  Data dictionaries define fields, records formats, and files.  Assuming we're dealing with migrated S/36 data, you can check and see if you have any migrated data dictionaries on your system using:


If not, then you have to harvest layout info directly from RPG program F and I specs.
Dave FordSoftware Developer / Database AdministratorCommented:
As mentioned above, DspPfm is really not the way to look at your data (since it doesn't work very well with some data-types). Stick with SQL or do:

runqry *none wtdataox/wtmemo

Either way, you know the table exists, and it's in the correct library, so using the  correct "naming convention" and library-list in your ODBC should allow you to find it.

truth_talkerAuthor Commented:
When I display the data using this command


The data is at least separated into columns and then I get at the bottom of the screen.

Cannot show this data at the display station.

This is the only machine I can work with remotely, is there a way to add this workstation/device to the file? Easily?
Dave FordSoftware Developer / Database AdministratorCommented:
In the original question, you said that your main problem was "files ... are not showing up" when exporting into Access. Now, you're talking about some kind of bad data or data-translation issue on the green-screen.

So, what's the real issue?
truth_talkerAuthor Commented:
i'm thinking it is a permissions issue now.  

When I run


I get that I can't access the data at this station which could explain the whole problem.
Dave FordSoftware Developer / Database AdministratorCommented:
Good point. If you don't have authority to view the data in the table, then NOTHING will work, will it?

Check the object-authorities:


Based on the results of that, I suppose the next step would be to get the object-owner to grant you authority.

truth_talkerAuthor Commented:
I still get that "Cannot show this data at the display station".

Is it possible the workstation/device ID doesn't have access to the file?  I know the developer had limitations so that certain terminals could only do certain tasks.
Dave FordSoftware Developer / Database AdministratorCommented:
> Is it possible the workstation/device ID doesn't have access to the file?

Yes. The creator of the file might have limited its accessibility. Confer with them on granting you access.
truth_talkerAuthor Commented:
That's the problem.  They aren't helping.

I have the SECOFFR account and the account used by the developer that I can try.
Gary PattersonVP Technology / Senior Consultant Commented:
"Cannot show this data at the display station" (QRY1041 - google it) message has nothing to do with security.  Query throws this message when you attempt to display a character field that contains binary (packed decimal, for example...) data - it thinks you are trying to display graphical data on a non-graphical workstation.

Put your cursor on the error message and press F1 to see the extended help text for the error message.  If you did that, you'd see something like this:

                         Display Formatted Message Text                        
                                                             System:   D001    
 Message ID . . . . . . . . . :   QRY1041                                      
 Message file . . . . . . . . :   QQRYMSG                                      
   Library  . . . . . . . . . :     QSYS                                        
 Message . . . . :   Cannot show this data at the display station.              
 Cause . . . . . :   Some output from your query cannot be shown at your        
   display station because it contains characters not supported by the device.  
   This may have occurred for one of the following reasons:                    
   -- Data in a field does not match the data type defined in the field        
     definitions (binary or packed decimal data may be stored in character      
   -- The record format allows data to be used in the wrong format.            
     A dup character (an asterisk with a dash above it) is shown in each        
   position that would have contained the character unable to be shown.        
 Recovery  . . . :   Do one of the following:                                  
   -- Ensure the data in each field matches the data type defined for the      
  -- Change the query or the record format definition to omit those records or
    fields from the query output.                                              
  -- Run the query output to the printer and view the spooled or printed      
Technical description . . . . . . . . :   To avoid the dump produced, use the  
  Work with Message Descriptions (WRKMSGD) command to change message CPF5192  
  to not dump data on this exception.                                          
    Performance of displayed reports that issue this message will be degraded  
  because each screen of data that causes this error will be presented twice.  
  The first attempt fails with the message CPF5192. Then the results are      
  displayed again after most of the data with an EBCDIC hexadecimal value less
  than X'40' is translated to be displayed as a X'1C'. To improve the          
  performance of the displayed report, the bad data should be corrected or    
   removed from the report.      

If you'd please just post the output from


like I asked earlier, it would end the mystery.  I think this is a program-described file.  If so, getting the data in usable format is liable to be little tricky, and will require access to the source code of one or more of the programs (most likely RPG) that access this file.  If there were IDDU definitions, I think RUNQRY would have picked them up.
truth_talkerAuthor Commented:
I have a screen shot of that file on DSPPFD on an earlier post
Gary PattersonVP Technology / Senior Consultant Commented:
I know the commands looks similar, but you posted a DSPPFM (Display Physical File Member).  I asked for a DSPFFD (Display File Field Description).
truth_talkerAuthor Commented:
Gary PattersonVP Technology / Senior Consultant Commented:
OK, this is a program-described file.  Might have been copied over from S/36 or Advanced/36 using DDM based on the field names - that's one place I can recall seeing this field naming pattern before.  

It contains 3 fields:

F00001 - 1 byte character field, probably a record type indicator

K00001 - 5 byte character field, originally used as a key field

F00002 - a 1294-byte character field.  This is most likely a variable format field that is program-described.  That means that you have to look at the programs that use it to determine how it is sub-divided in the program.  It is just described to DB2 as a 1294 byte chunk of data, and when it gets read into a program, the program looks at that first byte and uses that to determine how to break up this field.  Made-up example:


"H" is a header row with a key of "00001" and F00002 divided into 3 6-byte sub-fields.
"D" are detail rows with a key of "00001" and F00002 divided into 16, 10, and 6 byte sub-fields.
"S" is a summary row with a key "00001" with a single 15 byte sub-field

Together, this imaginary example might represent a purchase order, for example, with a header, detail rows, and an summary row containing the total for the order.  No idea what "WT" represents in the file name, but that may give a clue to the type of data it contains.  If I could see a hex dump of the file, I might be able to give you an educated guess about how the data is subdivided.  Without that (and maybe even with that), I'd need to see the source code for one or more of the programs that use this file to determine how F00002 is sub-divided.

From the DSPPFM screen shot, I see two F00001 values:  a blank and an "A".  Maybe there are more, you'd have to inspect the rest of the file.  I see K00001 values that look like packed numerics, and I see F0002 subfields that look like packed numerics before and after column 43, and a possible character column 43 subfield containing and "A" or an "I".  Of course F00002 is 1294 bytes long, so there is a lot of data off the right side of the screen that we can't see.

Generally, to convert a file like this into a relational database, I would create one table for each record type based on the record format found in the associated RPG (or COBOL, or whatever) programs.  Then I'd copy all the H records to a new HEADER table, D records to a DETAIL table, and "S" records to a SUMMARY table.

Maybe another expert has a better solution.
truth_talkerAuthor Commented:
Okay, this is way over my head.  I may just have to try to get the programmer to write something to dump the data out of the table if it's not something that be described how to do on the forum here.
Gary PattersonVP Technology / Senior Consultant Commented:
Not a simple task.  If you have access to a programmer familiar with the system, that could certainly save a lot of time and frustration - if nothing else they might be able to provide you with layout information.
truth_talkerAuthor Commented:
Okay, one last thought.  

I have attached what I get when I try to do a file transfer of the file through iseries.  Is that anything that can be worked with?
Worked with... yes and no. Not "worked with" in a sense that many would like to go through the effort. It'd be much easier to do on IBM i before a transfer.

It's somewhat frustrating not to be able to do for you what could be done. I'm sure a number of us could make (relatively) short work of it if we had the system. It's just not easy doing the needed background knowledge transfer.

E.g., we might simply write an "old style" RPG program using a "program described" input file. It would directly convert the input into however many output database tables are needed to handle the different formats that are coming in. Or we might use multiple instances of the CPYF command to do the same separating into tables, by making use of the selection capabilities of the command along with its ability to overlay record format images onto database formats. Or... well, it's just frustrating.

It's not easy coming up with a good method in modern languages of handling files with mixed formats. So much of today's technology expects structured database access for database records.
Gary PattersonVP Technology / Senior Consultant Commented:
Row 1:

F00001 - 1 byte character field = Blank

K00001 - 9-digit packed numeric field (decimal positions unknown, but since it is a key field it is probably 9,0 - "F" in the last nibble means it is a positive number) = 010150001

F00002 = Visible portion is a structure of packed numbers, all stuffed into a single column.  The "F" is the last position of each number, so what we see is probably a list of numeric zeroes:


But without looking at a program that uses this data, we still have no idea what it means.  Also, I recall that the F00002 column was 1294 bytes long, so there is no telling if the rest of it is laid out in a easy to decipher a fashion as this part is.  I remember from something you posted above that it isn't just a straight list of packed numbers.
Gary PattersonVP Technology / Senior Consultant Commented:
In the time we've spent going back and forth over what is clearly a program-described file, a programmer familiar with program-described files and with access to source code would have already been able to extract it into something meaningful.

Assuming just a couple of simple record formats, and access to an RPG program that contains the record formats, it would take me on the order of an hour to do a basic extract on a file like this.

Since it sounds like you've got access to a programmer, I think you'll save yourself a lot of headache if you just ask for a little assistance.
Degi PanjuCommented:
You can use iSeries Data Extractor Free. It will export data from AS400 to Microsoft SQL Server, Oracle, MySql, or PostgreSQL directly. It is free software but has several limitations. You can get it through this : http://degipanju.blogspot.co.id/2016/10/export-data-from-iseries-to-rdbms.html
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
IBM System i

From novice to tech pro — start learning today.