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.
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online