DB2XTool

DB2 open source utility for iSeries

Convert database files to PC formats

DB2XTool is an Open Source utility written il ILE-RPG and ILE-CL, originated by an anonymous contributor.

This utility allows to convert a database file to a stream file, in one of four PC formats.

When running from a 5250 interactive job, after converting the database file to a stream file, it may invoke the appropriate PC utility to display the stream file.
Available PC formats and related PC utilities:

.csv EXCEL

.html Internet Explorer

.doc WORD

plain text Notepad

.xml Notepad

As an alternative, the utility may run as a result of an HTTP request (for instance, called from a CGI) and provide in response a link to the generated stream file. This could for instance result into EXCEL directly opening the stream file.
Here is a small example.

Prerequisites

System library QIWS (5722SS1 option 12)
System library QSQL (Product number 5722ST1 option *BASE)
RPG ILE-Compiler (Product number 5722WDS option 31)
OS/400 Release 5.1 or subsequent

Installation

unzip PC file db2xtool.zip
transmit (binary) PC file db2xtool.savf
to AS/400 save file qgpl/db2xtool
rstlib lib(db2xtool) dev(*savf) savf(qgpl/db2xtool)
strrexprc srcmbr(install) srcfile(db2xtool/qrexsrc)

Note 1- The install REXX procedure prompts for execution command INSTALL. This command has a parameter, named WINDOWS (Dft type of clients) which tells the Microsoft OS (*XP or *2000) used by your clients. Depending on the choice, four data areas (EXCEL, IEX, NOTEPAD, WINWORD) are initialized with the C standard path to reach the corresponding PC utilities). See PC Command later in this page.

Preparing user profiles

It is highly recommended that user profiles, intended to take advantage of DB2XTOOL, are properly documented in files QGPL/USRPCCFG and QGPL/USRPCPRD. This is done as follow:

chgcurlib db2xtool
Enter and prompt command SETUSER .
This command defines, for a given user profile, the IFS path where the stream file - result of database conversion - will be created.
Please use the help key to better understand the meaning of the input fields.
Enter and prompt command SETUSERPRD .
This command defines, for a given user profile, the client paths to MS office products.
Also here, the use of the help key is recommended.

Operation

Be sure to enter command chgcurlib db2xtool

There are five basic commands you should play with:

Command Converts DB
to a Runs PC utility

RUNEXCEL .csv stream file EXCEL

RUNWORD .doc stream file WORD

RUNIEX .html stream file Internet Explorer

RUNNOTEPAD .txt stream file Notepad

RUNXML .xml stream file Notepad

In all these commands there are two critical parameters:

PC Path Name (PCPPATH) - This is the path through which the PC (via a Windows Network Drive) can access the target stream file

IFS Path Name (IFSPATH) - This is the path through which the AS/400 can access the same target stream file

As an example, we suggest you run the following (E is the Microsoft network drive able to access our AS/400 directories):

MD DIR('/temp') DTAAUT(*RWX) OBJAUT(*ALL)

RUNEXCEL FILENAME(DB2XTOOL/SALES) TOFILENAME(SALES)
   PCPATH('E:\temp') IFSPATH('/temp')
   COLHEADING(1) FLDDLM(';') DECPNT(*PERIOD) OPENWITH(EXCEL)
    (press the help key to fully understand the meaning of parameters PCPATH and IFSPATH)

There is also a supercommand, named cpytopcfmt. This command includes the four basic commands mentioned above. May we suggest that you use this command after getting familiar with the four basic ones.

PC command

In order to run a PC utility (e.g. EXCEL), the AS/400 utility issues a STRPCCMD (Start PD Command) command. In doing this, the path to the PC utility is specified. This path is taken from one of four data areas (EXCEL, IEX, NOTEPAD, WINWORD).
Should one of these paths result wrong, thus not allowing a Windows utility to be called, just find out what the correct path would be in your client environments, and update the appropriate data area.

Download

To download this utility, just press the "Download now" button.
Though everything is free, you must register.