• A Few Excel Export to CSV Tips

  January 27, 2010 Michael Sansoterra

  Comma separated variable (CSV) files are frequently used when exporting DB2 data for use with Microsoft Excel. However, because CSV files contain text without any additional formatting instructions, it can be somewhat time-consuming for users to format their worksheets whenever they get a new export. This tip will address a few of the shortcomings of using CSV files with Excel and present a few possible workarounds to make the lives of your Excel user community easier. Many of these ideas are only applicable to CSV exports intended for Excel usage. If the export file is dual purpose (one for users

  …

  Read more

• Admin Alert: Speeding Up i5/OS Access Path Rebuilds

  January 27, 2010 Hey, Joe

  Our i5/OS HTTP server jobs crash after we reorganize large production files because the servers can’t open the access paths the system rebuilds after the reorganization ends. The servers can’t run until re-indexing is finished, and some of these physical files have 60 logical files attached to them. How can I speed up access path recreation so I don’t hold up processing? –Rob Access path rebuilds can be a problem after regular file reorganizations. They can also be a problem with abnormal IPLs, where many access paths can become invalid and need to be rebuilt before they can be used

  …

  Read more

• Run SQL Scripts: Use Temporary JDBC Settings

  January 20, 2010 Skip Marchesani

  I consider myself to be a power user of Run SQL Scripts and use them for a variety of purposes, including ad hoc queries to do data analysis. Run SQL Scripts can be configured for your personal preferences (SQL default schema or current library, schema or library list, SQL or system naming convention, and other parameters) by clicking on Connection in the toolbar and then JDBC properties. Once you select your personal preferences and click the Connect button on the bottom of the JDBC Setup panel, your preferences are saved and become the default each time you invoke Run SQL

  …

  Read more

• Can a Function Return More Than One Value?

  January 20, 2010 Hey, Ted

  Is it possible for an RPG subprocedure to return more than one value? (I assume it isn’t; the RETURN opcode only allows for one argument.) I want to write a function subprocedure that will return a data structure of information taken from various database files. However, the subprocedure will also need to return a status code to indicate whether or not the I/O was successful. Do you have any suggestions? –Travis What you want to do is something like this: eval (MyDataStructure: Status) = GetData (KeyValue) No, that won’t work. But I can show you two other ways that will.

  …

  Read more

• Admin Alert: Erasing i5/OS Disk for Fun and Compliance

  January 20, 2010 Joe Hertvik

  When decommissioning iSeries, System i, or Power i systems, there may be legal considerations in erasing disk drive data. Decommissioning can occur when removing disk drives from a system; when completing a Disaster Recovery Test on an outside provider’s machine; or when an i5/OS machine is sold or returned to a leasing company. The problem is that decommissioning disk drives can easily violate legally mandated compliance standards. Compliance and Decommissioned Disk Drives Like most systems, i5/OS data doesn’t necessarily disappear when you reinitialize your disk drives. Clever and determined hackers may be able to reconstruct supposedly erased data if they

  …

  Read more

• Consume an IWS Web Service From a VB.NET Client

  January 13, 2010 Michael Sansoterra

  Note: Source code for RPG program CustListR can be downloaded here. In my article Publish Result Sets Using Web Services and IWS, I demonstrated how to write an RPG program that can return a result set (i.e., multiple rows of data) to a Web service client using an Integrated Web Services (IWS) server instance. The benefit of using RPG to publish data as a Web service is that any number of clients can consume the Web service, regardless of OS platform or language. This potentially makes your i data and business logic accessible from anywhere! This tip will

  …

  Read more

• Joining Incompatible Data

  January 13, 2010 Hey, Ted

  A physical file contains a nine-byte character acknowledgement number field that consists of a single letter followed by eight digits. I would like to change this field to two fields: a one-byte character and an eight-byte numeric field so that I could join the file to other files that have only the eight-digit numeric value. I realize that I could do a conversion to redefine the field, but that would require extensive changes to our applications. Is there an easier method to redefine the field, such as in a logical file? –David You probably don’t have to redefine the field,

  …

  Read more

• FTPING i5/OS Files to a Unix Server

  January 13, 2010 Hey, Joe

  Can I use FTP to back-up i5/OS libraries to a Unix server? Management wants to save money by not purchasing an LTO 4 tape library. Instead, they want to use Unix boxes to store i5/OS back-ups, and later send the data to tape. I’ve tried FTPing files between i5/OS and UNIX, but I can’t read the data when it’s transferred back. How do I approach this? –Charlene The answers to your questions are “yes” and “no”. Yes, you should be able to easily back-up and save production libraries to a Unix box. No, automated back-up to a Unix box may

  …

  Read more

• Let’s Start Over With a New Beginning

  January 6, 2010 Hey, Ted

  I’m writing in response to your article Let’s Start Over from the Beginning. Neither specifying CLOSQLCSR(*ENDMOD) nor running your program in a activation group *NEW (which is not even possible if your procedure is located in a service program and repeatedly called) is a good solution. In both situations the open data path (ODP) will be deleted after execution and cannot be reused. That means each time you execute your query, the system performs a full open, which is a time-consuming process. A full open is always necessary the first and second time a query is executed within the

  …

  Read more

• A Helpful Tool for Dealing with Unexpected Problems

  January 6, 2010 Ted Holt

  In the Pink Panther movies, Chief Inspector Dreyfus dreaded to hear the name Clouseau. Just the mention of that name was enough to cause Dreyfus to lose control of himself, and even landed him several stays in psychiatric hospitals. In the same way, there are certain words I hate to hear when I’m at work. In my day job, the words I hate to hear are “There’s a message on QSYSOPR.” A message on the QSYSOPR message queue is rarely good news. It means I’ve got to stop what I’m doing and deal with a problem. (I am proud to

  …

  Read more

Previous Articles Next Articles