Here at Ariadne Software we love the IBM i platform, its resilience and reliability are second to none, but sometimes it can seem difficult to share the data and reports that are stored there with other systems within your network. CoolSpools is uniquely placed to make that process straightforward – not only can simple CoolSpools commands extract the content of your database tables into a variety of output formats (including CSV, pipe-delimited, tab-delimited, Excel and XML), but CoolSpools also allows you to easily convert your historic spooled file reports to plain text files or PDF documents.
Whether you are extracting data into other business applications, BI systems, OLAP cubes, or are extracting your data and reports as part of a migration to a new system, CoolSpools makes it easy.
Extracting Data
CoolSpools makes it easy to extract data from your IBM i server’s database. For example, if your IBM i database were to contain a table named CUSTOMER, then extracting the content of that table is as simple as running one of the commands below:
Examples
| CVTDBFXLSX FROMFILE(CUSTOMER) | Exports the IBM i table to an Excel spreadsheet in the user’s home directory on the IFS. |
| CVTDBFCSV FROMFILE(CUSTOMER) | Exports the IBM i table to a comma-separated text file in the user’s home directory on the IFS. |
| CVTDBFCSV FROMFILE(CUSTOMER) CSV(*PIPE) | Exports the IBM i table to a pipe-delimited text file in the user’s home directory on the IFS. |
| CVTDBFCSV FROMFILE(CUSTOMER) CSV(*TAB) | Exports the IBM i table to a tab-delimited text file in the user’s home directory on the IFS. |
| CVTDBFXML FROMFILE(CUSTOMER) | Exports the IBM i data to an XML document in the user’s home directory on the IFS. |
Note that the XML conversion above will generate a very simple flat XML document with an element for each database column. If you require a more complex XML structure with attributes and nested elements, then this is also possible, but will require you to define and use a database map with command WRKDBFXML.
Often the dataset you require will be more complex than a simple dump of a single database table. CoolSpools allows you to use a SQL SELECT statement as the source of its data conversion, with the ability to join tables, select which records and columns to include, and to perform complex calculations to derive the output values (e.g. where some sort of translation is required due to differences between your old and new systems).
Example
| CVTDBFCSV FROMFILE(*SQL) TOSTMF(‘\Export\Customer_Address.csv’) SQL(‘SELECT A.CUSTNO, A.CUSTNAM, B.STREET, B.CITY, B.STATE FROM CUSTOMER A INNER JOIN CUSTADR B ON A.CUSTNO=B.CUSTNO’) | Exports data from the customer and address tables to a comma-separated text file in an IFS directory named “Export”. |
If the SQL SELECT statement becomes too complex to easily read and maintain within the CoolSpools command’s SQL() parameter, then you can use the parameter FROMFILE(*SQLSRC) instead, which allows you to use a SQL statement that is maintained within a source member.
If you are more familiar with QUERY/400 than SQL, then the good news is that CoolSpools can also use your query definitions as the source for data conversions with parameter FROMFILE(*QRYDFN). This allows you to leverage the many queries that already exist on your server, or to create new queries to extract the required record sets for migration.
If you require delivery of the converted files to a network file server rather than the IBM i server’s IFS then this is also easy to achieve by specifying TOSTMF(*FTP) within the CoolSpools command parameters.
Extracting Spooled Files
In many cases, extracting data from IBM i database tables is only part of the project, as the server also holds a large number of historic reports, invoices, statements and other documents in the form of IBM i spooled files. Even when your project involves decommissioning the IBM i server, this does not mean losing these valuable documents – CoolSpools also provides an easy way to export these to plain text files or PDF documents for safe keeping.
Examples
| RUNSPLFCMD USER(*ALL) OPTION(10) | Exports every spooled file on the server as PDF documents in the user’s home directory on the IFS. |
| RUNSPLFCMD USER(*ALL) SPLFNAME(INVOICE) OPTION(14) | Exports all spooled files with the name INVOICE as text files in the user’s home directory on the IFS. |
The above examples use the default conversion settings and file naming rules. If you need to vary the conversion settings by spooled file name, output queue, user or job name, then this is easily achievable. If you require delivery of the converted files to a network file server rather than the IBM i server’s IFS then this is also easy to achieve by specifying TOSTMF(*FTP) within the CoolSpools command parameters.
Case Study
See this case study for an example of a luxury car manufacturer who used CoolSpools to migrate data from their thirty-year-old COBOL system on IBM i to their new SAP Business One system, by extracting tab-delimited extract files formatted to SAP specifications.
In summary, whether your plans involve sharing data between systems or migrating business applications away from the IBM i server, extracting both your data and your reports into a variety of formats for onward processing into other systems can be made easy with CoolSpools.
Download CoolSpools FREE for 30 days
Take a free test drive of the entire CoolSpools Suite, including converter, database, email and admin FREE for 30 days.