IBM Navigator for i provides a rich, modern interface for managing IBM i. In this tip, I'll show you how to easily export data from IBM i database tables.

Have you ever wanted to export data from an IBM i table or physical file into a comma- or tab-delimited file? In the past, I've used commands such as CPYTOIMPF or, if the resulting file and logic to get a subset of the data was relatively complex, I'd write a custom record-reading and stream-file-generating program.

Once again, in the spirit of modernization, we'll talk about how to generate these types of files using IBM's strategic systems management tool: IBM Navigator for i.

Let's Export Some Tables

Open a web browser and go to http://<yoursystem>;:2001/ where <yoursystem> is replaced by either your server's IP address (i.e., 192.168.x.x) or its fully qualified address (server.domain.com). Log in using your IBM i user ID and password.

On the left pane, expand Database > Databases and then click on your database name (mine is listed as my system serial number). If you don't have a schema defined for a particular library, from the table on the right you can right-click Schemas and choose Select Schemas to Display.

Figure 1: Select schemas to display.

In the following wizard, you can search for existing schemas to display. In my example, I'll pick the IBM Notes Traveler schema, which is library LNT. On the filter, I simply type in LNT and hit the Search button.

Figure 2: Search for existing schemas.

When it's found, I simply select it by putting a check box next to it and hitting the Add button.

Figure 3: Add the schema.

Now, my LNT schema shows up on the right-hand table. At this point, I'm able to access anything in that schema: indexes, tables, views, aliases...all from a native graphical interface.

Figure 4: The selected schema shows up on the right.

Let's Export Some Data!

Since you've now displayed a schema and have access to its contents, pulling data from a table is easy. Let's say I want a list of all IBM Notes Traveler mobile device users dumped to a comma-separated value (.csv) file.

All I'm going to do is right-click my Device table and then choose Data > Export.

Figure 5: Start exporting data.

I can choose to export all rows or be a little granular with an SQL statement. If I use an SQL statement, I can validate that statement with the Check Syntax button and preview the result set using the Preview Results button. Once I've decided on my record selection choices, I press the Next button.

Figure 6: Select the data to export.

On the next screen, I'm going to choose the file name and where to generate it on the Integrated File System (IFS). Then, I can pick either a delimited or a fixed-format file type. Personally, I'm a CSV kind of guy, so I keep the default of Delimited and press the Next button.

Figure 7: Choose delimited or fixed format.

On the next screen, you can choose the different delimiters for string, column, and row; whether to preserve blanks; pad columns; and so on. Customize as you wish and press the Next button.

Figure 8: Customize it!

Choose your date and time formatting on the next screen and hit the Finish button.

Your CSV file will now be generated where you specified on the IFS.

I'll navigate to where I stored my data by expanding File Systems > Integrated File System > Root (because I generated my example file on the root of my IFS). I find the file, right-click it, and then click the Download button in order to pull it onto my laptop. Very simple.

Figure 9: Find the file and download it.

I keep encouraging people to use IBM Navigator for i because it's very simple to use. Of course, the CPYTOIMPF does some of the actual export work under the covers, but it's more complex to understand under a command setting than via a graphical wizard, especially for a new admin. The path of least resistance is definitely IBM Navigator for i. In the next tip, I'll show you how to do a data import from a CSV file into an IBM i table. Cheers.

Steve Pitcher works with iTech Solutions, an IBM Premier Business Partner. He is a specialist in IBM i and IBM Power Systems solutions since 2001. Feel free to contact him directly atspitcher@itechsol.com.