Valerio Di Giampietro

Personal Web Site

Linux/AS400: download and convert database files from ebcdic to ascii and excel

I was involved in trasferring data from an AS/400 and to do a minimal datawarehouse, loading that data on an Oracle database. The datawarehouse system was my responsability, the AS/400 was managed by another group located in another city. The AS400 database had packed and zoned fields.
At that time I had been involved in Unix and TC/IP administration and configuration since 1992. However I never worked in an AS/400 or IBM Host environment, this means that I knew something about Network and Unix, but I knew almost nothing about the AS/400.
For this low budget project, after evaluating alternatives, I choose RedHat Linux for its stability and flexibility, the Oracle database for its features, stability and performance, and Perl for its developing speed and abundance of specialized modules.
In the early days of this project, I had the following issues:

  • how to script the transfer of database files from AS400 to the linux box to load them on the Oracle database
  • how to automate some interactive activities on the AS400 terminal emulator

I tried to find the solution to this kind of problems on the web, but it seemed that there were some AS400 experts, many Linux/Oracle/Perl experts, but the two groups had a very small intersection. At the end I decided to develop some scripts and some techniques that I am now happy to share with everybody interested.

Basic AS400/Linux tools used

To interface the Linux box to the AS/400 I used:

  • x3270 a very nice 3270 terminal emulator, using tips on his home page it can be used also as a 5250 emulator. It has the feature to be controlled by another program via standard input/standard output and it has also a companion program, s3270 that doesn’t display the emulator window and is perfect to be used only to be controlled by another program;
  • curl a nice utility to download ftp or http pages unattended
  • the Perl language with the following modules:
    Expect allows to either spawn a process or take an existing filehandle and interact with it such that normally interactive tasks can be done without operator assistance. This concept makes more sense if you are already familiar with the versatile Tcl version of Expect;
    Perl module for string conversion between EBCDIC and ASCII;
    Perl module to write to a cross-platform Excel binary file

Downloading database files from AS/400


Initially we tried to transfer database files from AS/400 to our linux box using ftp in ascii mode. This was almost ok: we got a text file with each record converted on a single line, within each line, each field had his own lenght. The problem was with some files due to the way the AS/400 manages packed and zoned or signed fields.
The fact is that in the AS/400 each field in the database file is stored according to his type:

Strings, Integer and Decimals
are stored using the ebcdic standard codes, during an ftp ascii transfer these fields are transferred without any errors, one minor problem is that decimals are transferred without the decimal point;
Packed fields
AS/400 allows the definition of packed fields, these are decimal fields, but each decimal digit is stored in a nibble (4 bits, half of a byte) to save space. The sign is stored on the last nibble using the convention that the hex digit ‘D’ (debit) means negative value, the hex digit ‘C’ (credit) means positive value. During an ftp ascii transfer these fields are converted to pure garbage;
Zoned or Signed fields
In signed fields each digit is stored on a single byte, but on the last byte one nibble (the first 4 bits) is used to store the sign: the hex digit ‘D’ means negative value, the hex digit ‘C’ means positive value. During an ftp ascii transfer these fields are transferred almost correctly: only the last digit is garbage.

I solved this problem in the following way:

  • download in binary mode the AS/400 database file with the curl utility using a command similar to the following:
    curl -o mytest \

    I now have the ebcdic downloaded file on the file mytest

  • download the file layout, I downloaded this giving the dspffd command and using the screen capture function of the x3270 terminal emulator (function file -> Save screen(s) in file) and converted these screens to the file mytest.layout. Later I developed a Perl/Expect script ( and that automated this downloading with a command similar to the following: -u userid -p password -l library \
                 -f file > file.layot
  • convert the downloaded ebcdic file in an ASCII file with one record per line and each field separated by the “|” character. I used a command similar to the following to get this result: mytest.layout mytest > mytest.txt
  • convert the text file mytest.txt in an excel file using a command similar to the following: mytest.txt mytest.xls

Layout file

To be able to convert the downloaded database from AS/400 I need the layout information stored on a file. The perl script does expect a layout file in the format similar to the following mytest.layout file:

 MYTRK      Record type                          A     1          1      1    1
 MYPLT      Plant code                           A     2          2      2    3
 MYIDF      Part number                          A    20         20      4   23
 MYWHA      Wharehouse code                      A     1          1     24   24
 MYOTY      Owner type                           A     1          1     25   25
 MYSUP      Supplier code                        A     7          7     26   32
 MYLOC      Location                             A     6          6     33   38
 MYBOY      Begin of Year quantity               P    11  3       6     39   44
 MYCUR      Current quantity                     P    11  3       6     45   50
 MYBOM      Begin of month quantity              P    11  3       6     51   56
 MYLDA      Last fill in date                    P     8  0       5     57   61

This information is same information you can get with the AS/400 command dspffd to display file layout information. This file was generated by an utility (dsptrk) that was installed on one of the AS/400 I used. At the time I tought that this was a standard AS/400 command, instead it was a command locally developed.

Because my script does expect the layout file in this format, if you have the layout file in a different format you should convert it to this format or change the script or use the expect based script to get this file in this format from the AS400.

If we look at the row related to the field MYBOM we will found the following information on the layout file:

Field name
Begin of month quantity
Field description
Field type, in this case it is a Packed Decimal
Field total lenght
Number of decimals
Field lenght in byte. It is a packed field (two digit per byte) so we have that this lenght is half of the field lenght
this field start at column 51
this field end at column 56

Database conversion to an ASCII text file

To convert the AS400 database file just downloaded in binary mode we use the command The format of this command is: [-s c] [-d lev] [-f|-b] file.layout file-ebcdic

it reads layout information from the file.layout and convert the AS400 file-ebcdic to an ASCII, pipe delimited text file on standard output. (To have an idea on what this command do you can look at the files mytest (AS400 file trasferred with ftp in binary mode), mytest.layout and mytest.txt ). The options of this command are the followings:

-d lev
set debug level to lev
use field names instead of descriptions on the heading row of the text file
use both field names and descriptions on the heading row of the text file
-s c
use c as field separator (default is ‘|’)
use “.” as the decimal point (default is “,” because this script was developed in Italy where the use of “.” and “,” in numbers is reversed)

If we look at the source of this file (see ) we can see:

  • from line 1 to line 25 there are some comments and
    use statments;
  • from line 26 to line 43 there are assignments to arrays used to correctly read the layout file: @descr(11,35), for example, means that we are storing in this array the starting column number and ending column number of the field description in
    the layout file;
  • from line 44 to line 79 there is the function packed2ascii: this function has two input parameters: $str (the packed decimal field) and $decimals (the number of
    decimal digits); it returns a decimal value. Some examples:
    packed2ascii(0x1234567c,2) # returns  12345.67
    packed2ascii(0x1234567d,2) # returns -12345.67
    packed2ascii(0x1234567f,2) # returns  12345.67
  • from line 80 to line 121 there is the function signed2ascii: this function has two input parameters: $str (the siged or zoned decimal field) and $decimals (the number of decimal digits); it returns a decimal value. Some examples:
    signed2ascii(0xf1f2f3f4f5f6c7,2) # returns  12345.67
    signed2ascii(0xf1f2f3f4f5f6d7,2) # returns -12345.67
  • from line 122 to line 134 ther is the function autocrop used to strip off leading and trailing white spaces from a string;
  • from line 135 to line 146 there is the function usage used to print some usage text;
  • from line 147 the main program starts and till the line 175 it processes options and parameters
  • from line 176 to line 213 the layout file is processed and the following main variables are filled in:
    • @campi a list of field names;
    • many hashes (%campo, %descr, %dacol ... ), the keys of this hashes are the field names
  • from line 214 to line 231 the header row is printed on the standard output;
  • from line 232 to line 255 the database file is processed:
    • the file is read record by record (line 240)
    • each field of the record is processed (lines 243-255) and is converted to an ASCII string according to his type
  • from line 256 to the end each ASCII converted field is printed on the standard output

Download the layout file with a “screen scraper” application

To download the mytest.layout layoutfile easily I wrote a script that using x3270 or s3270 is able to connect to the AS/400, to logon, to give the DSPFFD command (Display File Format Description), capture the terminal screens, reformat this information and print, on the standard output, the file layout.
Using the same approach described here it is easy to write CGI web interface to “green screen” AS/400 applications (I did this). The application to download the layout file is composed by two little perl script: and

The script

This is the main script, the one that is executed do download the file layout. The format of this command is: [-d debuglevel] [-x] -u userid \
  -p password -l library -f file

it connects to the AS/400, logon to the system using the userid and password specified on the comman line, gives the DSPFFD command, captures the screens and print the
layout file on the standard output. It gives some information on what it is doing on the standard error.

-d debuglevel
set debug level to debuglevel
-u userid
specify the user profile to use to logon to the AS/400
-p password
specify the password to use to logon to the AS/400
-l library
the library where the file is stored
-f file
the file we are interested in

If we look at the source file of this script we can see what it does:

  • include the;
  • process command line options and set global variables. In particular the variable $as400 defines the AS/400 hostname or IP address (you shold change this if you want to use this script), $x3270cmd defines the emulator program to use: x3270 for the option -x the s3270 without the -x option;
  • define the usual usage subroutine;
  • define the ffd2layout function (File field description to layout); this function takes as input a string that is a result of “screen scraping” the output of the DSPFFD command and gives back, as output, a string in the usual mytest.layout format;
  • the main program, using the Expect module and some functions defined inside the included does:
    • spawn the 3270 emulator (x3270 or s3270) and assign the handle of this Expect object to the global variable $x3270
    • connect to the AS/400 (function x3270_connect);
    • login to the AS/400 (function x3270_login)
    • store on the string $s the “screen scraping” output of the AS/400 DSPFFD command (function x3270_dspffd);
    • convert the output of the previous string to a different format (function ffd2layout) and print it on the standard output.

The script

The main functions used to interact with the AS/400 and with the x3270 or with the s3270 emulator are grouped here.

If we look at the source of this script we will find the following interesting things (description of not used or very simple functions is omissed)

  • it uses the following global variables that are defined on the
    parent script that it is including this file:
    • $as400 the hostname or IP address of the AS/400 host
    • $x3270 the handle for the Expect object
    • $user and $password the user profile and the password to use to logon to the AS/400;
  • the use Expect statement to use the Perl Expect module;
  • the x3270_die function that prints the error string passed to it, the current content of the x3270 emulator screen and exit;
  • the x3270_connect function that connects to the AS/400 controlling the x3270 application;
  • the x3270_login function that logins on the AS/400:
    • before logging in this function checks that on the login screen on the row 1, column 69 there is the 8 characters string “S65DD4AA” (in my case this is the name of my AS/400). This check is done to be sure to have a login prompt on the screen.
    • After this check the function “type” the user profile and password to logon (statement print $x3270 "String($user)\nTab()\nString($password)\nEnter()\n").
    • it looks at the screen contents and if it find the string "Press enter to continue" it “type” Enter to continue;
    • it checks to have the “AS/400 Main Menu” on the screen to be sure to have successfully logon on the system;
  • the function x3270_asciiscreen returns, in a multiline string, the entire emulator screen;
  • the function x3270_asciiregion returns a rectangular region of the screen;
  • the function x3270_ascii returns a string, part of a line, from the screen;
  • the function x3270_dspffd gives the DSPFFD command (statement “print $x3270 "String(\"dspffd file($library/$file)\")\nEnter()\n"“), “type” the “F8” key to move forward, page by page, till the "More ..." information disappear from the screen and store the lines between 5 and 20 of each screen on a multiline string that is returned to the caller;


Using the examples shown here it is possible not only to download and convert, unattended, AS/400 database files, but also design “screep scraping” applications that gives a web interface to the old fashioned applications of the AS/400.
I currently use these applications on Linux and Solaris, but they can be easily ported to the Windows environmet because Perl, and its modules, are available for this environment and using Cygwin should be possible to compile s3270 and c3270 also on this platform.

I would appreciate if someone using the scripts and tips described here is so kind to drop me an e-mail to let me know, to ask me some question or to post a comment here.

My email address: Valerio _at_ (replace ‘ _at ‘ with ‘@’).

11 responses to “Linux/AS400: download and convert database files from ebcdic to ascii and excel”

  1. Anonymous says:

    Useful article + alternatives
    About 9 months ago I was faced with a similar requirement and used CPYTOIMPF to convert the AS/400 database files and kermit to script the interaction with the AS/400. I mirror about a dozen database files daily which comprise the key data of our core banking system. The whole system has proved very effective if a bit crude and I have a number of shell and PHP scripts to interact with the data.

    Since I am familiar with tcl probably expect would have been a better choice than kermit but for some reason did not occur to me at the time.

    I also came across this surprisingly recent article regarding using unixODBC which is an approach I had been searching for months previous to implementing the above:

    Now that I have the whole framework in place and because the AS/400 should be phased out this year I am unsure whether I will use this fully on-demand capability but it would have been great to have known about it last year. The free availability of these downloads from IBM is great but they could do more to advertise their features/benefits.

    By the way the equivalent package for Informix works great as well. In my case Informix is running on a remote RS/6000 under AIX.


    • Anonymous says:

      Timestamp data is not supported.
      The script does not support Timestamp data. Does anyone has the script to convert timestamp? I tried to use unpack function, does not work.

  2. Anonymous says:

    Variable length records
    Hi Valerio,

    I know this post is quite old, but I’m hoping you’re still around to read this.

    I downloaded and modified your script to work for testing purposes. I think a version of this script may fill a niche in our env. I successfully converted some of our files using manually created layouts.

    We already have applications that handle the ftp process, but I don’t have access to the source’s mainframe computer, so I’m writing a perl script that will generated the layout files from copybooks. I’m pretty sure I can make it work, at least for fixed length records, but I forsee some issues. For example, how would you handle variable length records?

    Thank you for creating these scripts and taking time to provide the instructions and examples. You have been most helpful.

    Gary Powell
    Senior Operations Analyst
    INGENIX Government Solutions

  3. Anonymous says:

    The script is good and useful.
    Can you help following issue ?
    if the file in AS400 is empty or no data, how to handle this situation ?

    • Anonymous says:

      this script really great.. i wanna use to that .. özel turlar can u help as.. for how can i use that .. thanks

      • Anonymous says:

        To detect an Empty file…
        You can create a condition, or check, that uses/Runs the DSPMBRD (display member description) to an *Outfile, And return the
        &RRN variable, (RRN- relative record number), and create a condition , if the value of RRN is 0, then there are no records in the file, and so you know its empty, then simply end the script, and wait till the next time it runs…

    • Anonymous says:

      detecting an empty file condition…
      You can create a condition, or check, that uses/Runs the DSPMBRD (display member description) to an *Outfile, And return the
      &RRN variable, (RRN- relative record number), and create a condition , if the value of RRN is 0, then there are no records in the file, and so you know its empty, then simply end the script, and wait till the next time it runs…

  4. Anonymous says:

    infinity downline
    Cool stuff great post! I love finding little hidden gem blogs like this!

  5. Anonymous says:

    Thanks for article…

    Oyun Hileleri

  6. Anonymous says:

    Thank you! it works!
    Thank you for sharing your code, this really helped me!

  7. Anonymous says:

    Any help from AS400 Group/Side possible? would save much time..

      Only have a few thoughts, In order to possibly save time Valerio..

    Hi Valerio, This is Byron, ( And I have been working on both Linux and AS400/Iseries platforms for almost 15 yrs.. and I am surprised that group did not do much to assist, they could have saved you a lot of time.. from what i have just read, or were they even asked ? i know a lot of ways/tools to Create/convert to ASCII and transfer the data strait from AS400, And send DB2 files to Linux or Windows systems w/ half of the effort i just read, and just a few hours of coding. .. please dont under-estimate your as400, and also please be aware that everything you have mentioned that you did from your RED HAT Linux box, is also avail on the as400, V6R1 or later! …Even the entire Korn shell is avail on AS400, and all of the C/C++ Libraries and functions, and Java, and other stuff that Linux uses, is on as400 now, and you can even run Linux from inside as400, you can even run SAP off of an as400 db2 database, and there is already a Linux and windows file system avail. on your as400 also , and its called the IFS, and you could have used that on this project,to save a lot of time,

      Did you know the following:

    – Even Linux shell scripting and the Korn shell itself is in the AS400!!!, and all scripting languages like PERL, itself is Avail. can now be done on the as400 now,
    -so everything you did on the Linux side could have been done, Natively, right in the AS400 KORN shell itself. including creating the ascii file, and just pushing it over to the ORACLE/Linux box…

    some.. of the many methods offered from modern as400 shops, that could have saved you time, To move data in ASCII format from an as400, to any other Server on the planet…including Unix/Linux, or Windows server…are listed below, and that could have cut the project time in half, and saved you from having to do all of the screen scraping and Perl, and saved a lot of time: w/ just a Little help from the as400 side, and by doing a push from the AS400, using it’s Native FTP commands, you could have saved time, and done the following: >

    1- Could have used the %char() function, and %Dec – to re-write/convert the data, and placed it in a work table, without any PACKED OR ZONED data!, and wrote all the data to a work file or SQL table, and then used as400 FTP commands from the as400 side, to log into the Linux box, and hand shake, and hand off the file, in one step, and put all of the data in a string, which would be in a readable format for Linux, and ORACLE after the function runs, and w/ the decimal points and everything!, & no garbage data, and then you could have also stored the file in the as400 IFS file system, in ASCII format, and then u could have pulled the data from the IFS, anytime u needed, or just created an FTP script on the as400 to do it for you, and you could have even scheduled an as400 job to run an FTP script, and pull the data for you, and place it on the Linux box, or moved data to ORACLE, w/ driver, and *Daily, and told it the exact time you needed the data, and as400 could populate the Linux file, or ORACLE SQL TABLE, for you every day .

    2- You Could have used an SQL table on the as400 to transfer the data, and in the IFS to store the data in ascii format, and/Or just used an OBDC, or JDBC driver to move the data strait in to ORACLE, And also could have scheduled an as400 job to do this daily for you! at any time you chose, (Like a chron job in Linux/Unix) and no screen scraping would have been necessary… and the convert perl scripts , were also not really needed.

    ( NOTE: I use the IFS file system on the as400 everyday to store XLS spread sheets, PDF’S , And Images, and JPEG files, and text files, and any other ascii objects, every day, it is a very powerful File system, and please note that any file type can now be stored on the AS400, even WORD docs and HTML, and XML, and you can even process it natively in the as400.

    3 – You could have used the IFS File system, which exists on the AS400- as an intermediate location … and put the data in a spread sheet there, and then the as400 could just moved the spread sheet, and the data over to the Linux box everday, and from there you could used a linux command or program, to get the spread sheet data, and populate the ORACLE table anytime.

    4-or just try using the CPYTOIMPF to convert the AS/400 database files to Ascii and store it in the IFS, and then simply using the FTP commands avail from an AS400 cl Scripting (as400 command language script) , you could have populated the Linux file, everyday, without ever even logging into the as400 box, from Linux… and saved a lot of money and time…

    And 5. Please note all the Little tools avail on AS400, that also exist in Linux, with this knowledge below, you can clearly see that, anything u can do in Linux, u can also do on as400:
    -All of the native C/C++, and Java Libraries & Functions and compilers, that are on Linux, are also avail on AS400, remember that both OPERATING SYSTEMS (Linux & AS400 & Unix, were all written on a lot of C/C++ code, and the Korn shell, and so its’ all the same:
    -and windows/Linux files systems Now exist now on the as400, so u can have ascii db2 files.
    -so please take advantage of these tools, these are also avail. on all modern as400’s,
    -and even Linux shell scripting and the Korn shell itself , and scripting itself is Avail. on the as400 now,
    -so everything you did on the Linux side could have been done, right in the AS400 KORN shell itself.

    Im sorry to say that this project could have be done more easier from just little help from AS400 side, using Native commands, and File systems that exist with the 0S/400 operating system,

    ,And this would have been much easier by doing an FTP push from as400, insead of a pull from Linux side, and there is actually even a process for creating the ASCII file right on the AS400, and even in XML format, to transfer the data, if you want from as400, to port into ORACLE directly that way, without Unix/Linux people even having to get involved with PACKED & ZONED IBM data types, and such non-sense, im very sorry the as400 group there was not more helpful on this project; we always help our Unix/Linux and Windows Dev groups in my company as much as possible, and save them from a lot of headaches, so they DONT have to deal with scraping, and IBM DB2 numeric data types, and such non-sense, because why should they?… its not fair to them. And actually these Mainframe & AS400 numeric data types should not even be used anymore even on AS400, only Legacy code still uses these, because they were trying to save disk and memory space, way back in the 1970’s and 1980’s, but they are not used by modern shops anymore, because portability is now the main concern, not disk space, because disk space is now very cheap for as400..and because as400 now offers Native SQL tables also, just like ORACLE, and so u dont have to use DDS files, and strange IBM datatypes if u dont want to, there are now more common SQL data types that are avail in as400 DB2, and you can port the entire file over to Linux now, w/out converting EBCDIC to ASCII, IT’S done automatically, were you aware of this? And you can now use these SQL data types for your file, on AS400, which are below: CHAR
    Long Int , etc… which is very easy to port/ or FTP over to a Linux or Unix box frm as400. (AND NO PACKED or zoned data types have to be used, in your process at all.

    Just ask the as400 team to write the Legacy data to a new work table for you nxt time…and you can actually create an SQL table on the as400, for this process next time, and get the data to go strait into ORACLE, with little effort, and as400 will take care of all of it, automaticaly)

    – And the modern shops are only using these now, because saving Disk space with PACKED is not a concern anymore, because portability, and putting data on the web, and in spreadsheets is the big thing now.. for email, which all can be done, right from the AS400, using V6R1, if you simply know the tools and packages now avail for AS400, its really easy.

    – And after creating the ASCII version of the file in AS400 IFS FILE, then you could have just created a subsystem job, or created a scheduled job (using Work with job schedule entries, command) , which is like creating chron jobs on Linux/Unix boxes, and from the as400 you could have moved/copied the file at a scheduled time, *daily, or *weekly, * or *Monthly, whenever you need it.. over to your Linux box/Oracle at a certain time or daily, without you having to waste time on all that screen scraping, and perl scripting, my only concern is why the as400 group did not offer to do this little bit of work, from the as400 side, would have saved a lot of time. and only 3 hours of coding !

    – But, in any case, this is very interesting research and all, but it could have done so much easier, with just a little push from the AS400 side,
    – Were they not even willing to make you at least a little work file?, with the data already in Char / ASCII format ?, for the Linux box? that is only one hour of programming on as400 side..! and just that alone would have saved you so much time!@, and from the main DB2 file you were converting to ASCII, it could have also been stored in the IFS file system, and you would not have needed any of those convert:EBCIDIC scripts, that as400 group could have handed you an ascii file already!!!, with the same data, and not in ‘GARBAGE ‘ Format, but in a readable format… for Linux, or they could have left the file in the AS400 IFS for you, and you could have just coded an FTP pull from the as400 IFS, and that probably would have taken you an hour? right, instead of all of that coding and scraping you had to do, w/ little time.

    I am feeling very bad…
    i know you said the AS400 was managed by another group, but however,the AS400 was not used to its full potential in this project, and a lot of time and research could have been saved, with just one expert from the as400 side, unless there are no modern experts there? and then I understand… some of the legacy as400 Dev staff does not know how to do modern code on as400, but never the less, all of what i said, is avail, w/ OS400 release v6r1 OR Later, and all Also, all of the native C/C++, and Java Libraries & Functions and compilers, and windows / Linux files systems exist now on the as400, so please take advantage of this, these are also all avail. on all modern as400’s, and even shell scripting and the Korn shell , and scripting itself is Avail. on the as400 now, so everything you did on the Linux side, you could have just as easily done, on the as400 side itself. Just see the link below:

      Korn shell scripting – from IBM AS400 and AIX systems:‎

    I have converted so many DB2 as400 database files, to ASCII for windows and Linux using the above native AS400 tools and File system above, daily , even have been creating ascii XML files right on the AS400, and then FTP the XML data over to windows or Linux servers from an AS400 FTP script, at different times, to get AS400 data into my windows and Linux systems, in order to move some of our core Manufacturing system data, over to the linux box for archiving and reporting , and creating a Dashboard, and spreadsheets/PDF’s , for the sales team, using data-warehouse data to do sales forecasts.

    Please reach out to me, Anytime, i would be glad to give any insight I can, for projects involving any as400, and for free, just reach me at:

    , or Catch me on facebook, or Gtalk, I am on there all of the time, and its Public. just use my name above..

    And maybe we can share ideas/thoughts, and system knowledge – to make the communications / and file Transfers between AS400 and Windows / Linux systems almost transparent…!!! I am glad to help anyone realize the new tools and power avail on an as400,

    Maybe even create our own package / application.. Lol…
    (But i am serious about the above… )

Leave a Reply

Your email address will not be published. Required fields are marked *