FHG Archives - Page 104 of 144

Using Parameter Markers with SQL Functions

October 25, 2006 Michael Sansoterra

SQL is a superb data access tool in terms of ease of use and versatility. Data extraction routines that used to take hours to write in RPG or COBOL often takes minutes to write in SQL. The one draw back to dynamic SQL (as compared to data access routines written in a high level language), is that the DB2 query optimizer often takes precious time to figure out how to best execute an SQL request. To take the edge off this performance hit, dynamic SQL statements can be made “reusable” by using parameter markers so that the query optimizer can

…

Stuffing Five Digits Into Four, Take 2

October 25, 2006 Hey, Ted

Your recent article, Stuffing Five Digits into Four, is a trip in the way, way back machine for me. I had to do this very thing on System/3 in RPG II! I’m writing to let you know that there is a way to stuff those five digits into four using native I/O.

As you’ve discovered, RPG’s ‘B’ data type limits you to 4 digits; it’s in the manual, but that’s where data types ‘B’ and ‘I’ differ. ‘B’ is intended to preserve x digits, from 0000 to 9999 (including sign) but the newer ‘I’ is intended to emulate the

…

Admin Alert: The Pitfalls of Duplicating Data to a Test Library

October 25, 2006 Joe Hertvik

i5, iSeries, and AS/400 administrators frequently need to set up test libraries containing files that were duplicated from a live environment. While creating test environment data is not difficult, there are some pitfalls and shortcuts to the process that you should be aware of. This week, I will look at the method behind duplicating files and how you can easily maneuver files from one library to another.

The best way to illustrate i5/OS file duplication techniques is to create a sample scenario that you can modify for your own test library creation. For my situation, let’s assume that I am

…

The SPACE Function Takes Up a Lot of Space

October 18, 2006 Hey, Ted

I have to build a file for input to another system. All fields are fixed-length, and numeric values are to be edited and right-adjusted. SQL’s CHAR function takes care of the editing requirement, but the result is left-adjusted. How do I right-adjust an edited numeric value? Or will I have to rewrite my program using native I/O operations?

–Sarah

On the surface, this seemed like an easy request. It turned to be a little more complicated than I had thought.

I suggested Sarah use the SPACE function to generate enough leading blanks to force right alignment. For example, the following

…

Declare the BINARY Data Type Using DDS

October 18, 2006 Michael Sansoterra

We’ve all heard by now that defining tables using DDS is becoming outdated and that developers need to use SQL in order to access the relatively new data types, including DATALINK and Large Objects (BLOB, CLOB, and DBCLOB).

I recently ran into a situation where I wanted to change a field in an existing table to use the new (as of V5R3) BINARY data type for encrypting credit card data. (Recall that the BINARY data type is similar to character data tagged with a CCSID of 65535. However, data in a BINARY column will never be translated whereas the character

…

Using FTP to Transfer Multiple Files Between Windows and the i5

October 18, 2006 Hey, Joe

Every day, I have to transfer a variable number of files from my Windows server to my AS/400. I need to FTP all the files in the same directory and each transferable file begins with the same prefix, ABCFILE (ABCFILE.2006.09.11.2, ABCFILE.2006.09.11.3, etc). Once I get the files to the AS/400, I’d like to combine them into one file that’s simply called ABCFILE. Got any ideas?

–Mark

I can tell you how to FTP multiple files at the same time, but I’m not too sure about the best method for combining them. Here’s what I’m thinking.

For transferring multiple Windows files

…

CL Odds and Ends

October 11, 2006 Hey, Ted

I hope this question is not too simple for a newsletter that claims to be for gurus. An RPG program or module retains variables, open data paths, and other resources from one invocation to the next, as long as the LR indicator is not on. CL programs and modules, on the other hand, suffer from Alzheimer’s disease. They don’t remember anything from one invocation to the next. How do you handle this limitation?

–Richard

I can help you with part of this problem, Richard. Create a temporary data area to hold the variable values from one call to the next.

…

Use SQL to Remove Extra Spaces

October 11, 2006 Hey, Ted

The SQL sorting tip you published on September 13 was a good one. I had seen a similar tip written by Craig Mullins in Quest Software’s Pipeline Newsletter. Mullins had two other interesting tips on the same page. I thought your readers might like to see them as well.

–Tom

The Web page to which Tom refers is at http://www.quest-pipelines.com/newsletter-v7/0606_F.htm. Craig Mullins uses the technique about which I wrote to sort data on three-letter day abbreviations.

Another tip he presents involves using the REPLACE function to remove extra spaces within a character string. IBM added the REPLACE function

…

Admin Alert: The Ins and Outs of Loading Licensed Program Products

October 11, 2006 Joe Hertvik

When installing third-party i5 software, most vendors provide a list of required i5/OS licensed programs that must also be installed so that their product works properly. However, not every IBM licensed program will always be available on your i5 or iSeries partition, and sometimes you have to locate installation media to add additional products. This week’s Admin Alert will help guide you through that process.

Introducing Our Example

To illustrate how to install i5/OS licensed program products (LPPs), I’ll review an example that recently happened in my shop. All the concepts described in this situation are applicable to most i5/OS

…

Kevin Juenemann’s Quick Query Utility

October 4, 2006 Hey, Ted

Note: The source code for this article is available for download

Your QF command works great when you want to see the raw data, but sometimes you need only some of the columns, or need to join files to get usable data rather than codes. Many of our customers don’t have interactive SQL installed on their systems, so I created this utility and sent it to all our customers for those times a one-off report is needed quickly.

I can enter an SQL Select statement and a printer ID, and the utility will create a Query Management Query and run

…

Previous Articles Next Articles

Search for: