FHG Volume: 7 Archives

Use SQL User-Defined Functions to Avoid Data Repetition Problems

December 12, 2007 Michael Sansoterra

By now, most System i and AS/400 developers know they can enhance the SQL language in DB2 for i5/OS by creating their own user-defined functions (UDFs). In this tip I’d like to focus on one common problem encountered when writing queries and how a simple UDF template can often solve this problem.

By way of review, in case you’re not familiar with them, UDFs are pieces of code that can bolt onto SQL. UDFs usually contain business logic for doing complex tasks that would be difficult or cumbersome to do using the SQL built-in functions. There are a variety of

…

Stuff I Didn’t Publish This Year

December 12, 2007 Ted Holt

Note: The code accompanying this article is available for download here.

My wife and I recently visited our local Cracker Barrel, which is one of our favorite restaurants. While we were waiting for our food, I picked up the peg game that unfailingly occupies patrons at the tables of that establishment and, for the umpteenth time, failed to solve it.

The Peg Game

In case you’re not familiar with a peg game, here’s how it works. The board has 15 holes arranged in a triangle and 14 of the holes are occupied by pegs. To

…

Admin Alert: Getting Started with Trial Capacity on Demand, Part 2

December 12, 2007 Joe Hertvik

In Part 1 of this article, Getting Started with Trial Capacity on Demand, Part 1, I described how I recently approached the decision to activate additional trial processors on a client’s System i 550 box. This week I’m completing the process by reviewing the activation process steps and the various published and unpublished steps you need to take to add temporary processing power to your system.

The Basic Situation

Capacity on Demand (CoD) is IBM‘s way of delivering (and selling) additional processors that can be activated in a very short time when your system is experiencing performance issues.

…

Create Multiple Directory Levels in One Swell Foop

December 5, 2007 Hey, Ted

Maybe I’m getting old, but I seem to remember being able to create several levels of directory structure with one command. I currently have such a need, in an application that creates XML files in the IFS. Yet CL’s MAKE DIRECTORY (MKDIR) command fails with error message CPFA0A9. Am I imaging things, or has something changed?

–Lynn

My guess is that you’re remembering MS-DOS’s md command. The following example contains a command that creates from one to five directories as needed.

C:>md salesreports20071RegionC

CL’s MKDIR command can only create one directory level at a time. However, Qshell’s mkdir command can

…

Update Only One of Duplicate Records

December 5, 2007 Hey, Ted

We have a file that has no unique key. This means that we sometimes get duplicate records in the file. This does not cause us problems in RPG programs because we can read a record, make changes to it, and update it, whether we’re using native I/O or an SQL cursor. However, having duplicate rows is a problem when we use SQL outside of programs. If two or more records are identical, there is no way to code a WHERE clause that will select only one record. Or is there?

–David

It is ugly, but possible, David. Assume table MYDATA,

…

Admin Alert: Getting Started with Trial Capacity on Demand, Part 1

December 5, 2007 Joe Hertvik

I recently activated a trial Capacity Upgrade on Demand (CoD) processor to increase my processing power on a System i 550 box. If you’re considering activating additional processors on your system, this week and next I’ll trace the steps I took to decide on activating another processor and how I got the process moving. Although there are a few pitfalls to avoid, adding extra processors to your machine isn’t that difficult.

What is CoD?

Capacity on Demand (CoD) is IBM‘s way of ensuring that you can quickly add extra capacity to later model System i servers, including models 550,

…

System i Developers and .NET 2.0, Part 2: Web Development Using ASP.NET AJAX

November 28, 2007 Michael Sansoterra

Note: The code accompanying this article is available for download here.

In System i Developers and .NET 2.0: ASP.NET and the Declarative Programming Model, which is Part 1 of this series, I did a jet tour of the basics of .NET 2.0 and tried to present compelling reasons why System i programmers might want to learn it. Among the listed reasons to learn .NET are popularity (with a large supporting community), versatility and the ability to integrate with the AS/400 and System i world. Part 1 also presented a step-by-step scenario on how to create a basic ASP.NET

…

ON vs. ON

November 28, 2007 Hey, Professional

In ON vs. WHERE, I demonstrated that an outer join yields different results depending on whether you place a selection expression in the ON clause or the WHERE clause for a secondary file. Now I want to follow up on that tip by looking at the difference between the ON and WHERE clauses for a primary table.

Here’s the invoicing data I used in the previous tip. We have header information:

SELECT H.* FROM INVHDR AS H

Invoice  Company   Customer      Date
47566      1           44     2004-05-03
47567      2            5     2004-05-03
47568      1        10001     2004-05-03
47569      7          777     2004-05-03
47570      7

…

Admin Alert: Basic Tools for the System i Admin Tool Chest

November 28, 2007 Joe Hertvik

Like it or not, iSeries, i5, and System i administrators usually have to write small Control Language (CL) programs to help them manage their systems. To make this task easier, I frequently use several common programming and IBM-sponsored techniques to perform different functions. This week, I’ll look at three basic programming and administrative tools for CL programming and demonstrate how they can benefit you when creating operational programs.

Tool #1: Sending Status Messages When a User is Running a Program

When you’re writing long-running code that has to be run interactively, it helps to flash status messages at the bottom

…

ON vs. WHERE

November 14, 2007 Hey, Ted

I need your help with an SQL query. I have two database files: a header file and a detail file. There may be many detail records for each header record, but I only want to see certain detail records. Here’s the rub. I want to retrieve all header records, even the headers for which I don’t select any corresponding detail. It seems to me this should be an easy query to write, but my efforts so far produce only the header records for the selected details.

–Wayne

There’s more to this query than meets the eye, Wayne. It is simple,

…

Search for:

Content archive

The Four Hundred
Four Hundred Stuff
Four Hundred Guru

Content archive

Recent Posts

Subscribe

Pages

Search