Guide: Extracting data from an IBM i Spooled File to a Database Table

Using CoolSpools to Extract data from a Spooled File to a Database Table

You may have a scenario where you need to interrogate data that is only available to you as an IBM i Spooled File (e.g. a standard report generated by a third party software package), or have an existing report containing complex calculated values whose output has been verified, but needs to be presented in a different way without duplicating complex report logic. CoolSpools allows you to convert the content of any spooled file report into an IBM i database table, allowing the report’s content to be interrogated by a query tool of your choosing. Conversion from a spooled file to a database table can be achieved using the following CoolSpools commands.

WRKRPTDFN – Work With Report Definition

Since the structure of the report may be complex, it is necessary to create a report definition to identify different types of lines within the report (e.g. headings, details and totals) and the position of data elements within those lines. The report definition allows you to convert a simple transaction listing with header and detail lines, or a complex report with multiple sub-headings and details presented over multiple lines. Once defined, the report definition can also be used for conversion to other output types, such as MS Excel and XML.

Run command WRKRPTDFN, and press F6 to add a new report definition or select option 2 to change an existing one. You will be presented with a list of spooled files – select the spooled file that you wish to use as a template for the conversion (or press F14 to change the spooled file selection criteria if the required spooled file does not appear).

The content of the selected spooled file will be presented on screen – you can use F7 and F8 to move backwards and forwards through the report pages.

Position the cursor on the line that you wish to define and press F10 to define the line type. Give the line type a name (e.g. “Detail”) and specify the page and/or line numbers on which it can appear. Optionally you can apply additional criteria in the form of rules (e.g. this line type always contains specific characters at a fixed position on the line). When you have defined your line type you can press F9 to save it. You will see that the colour of the defined lines will change – if too many lines have changed colour then you may need to apply stricter rules. You can position the cursor on the line and press F10 again to modify the definition of the line type.

Once you are happy that the line type is correctly identified, you can then define the position and type of the items (data elements) within that line. Place the cursor on any line of the required type and press F10 to open the line definition – the content of the selected line will be displayed near the top of the screen. Position the cursor to the start of an item within that line and press F10 to define it. Enter a name, length and type for the item and press F9 to save the definition. Repeat this process until all items are defined. Keep a note of the name that you assign to each line and item, as you will need these for the next step.
Creating the report definition should be a one-off process, and should only need to be revised if the layout of the spooled file report changes.

See the CoolSpools documentation for a full description of the commands and parameters relating to Report Definitions.

WRKRPTDBF – Work With Report to Database Map

In order to map the data elements from your report definition to the fields of a database file, a report to database map definition is required. Run command WRKRPTDBF and press F6 to add, or option 2 to change, a map definition. Enter the report definition name, and then specify each report item (line and item name) that is to be mapped, along with the corresponding database field name and type. If you are planning to converting the data into a file that already exists on your system, then the field names and attributes you specify here need to match exactly.

Creating the report to database map should be a one-off process, and should only need to be revised if the content of the spooled file report and/or database file changes.

See the CoolSpools documentation for a full description of the commands and parameters relating to Report to Database maps.

CVTSPLDBF – Convert Spooled File to Database Table

To run the conversion from spooled file to database file run the command CVTSPLDBF, specifying the spooled file to convert and the report to database map name. Optionally you can specify the file and library for output – by default the file specified within the map definition will be used. If the database file does not already exist then it will be created using the field attributes of the map definition. If the file already exists then the field names and attributes must match those within the map definition – you can select *REPLACE to replace any existing records or *ADD to append additional records.

See the CoolSpools documentation for a full description of the CVTSPLDBF command and its parameters.