Many businesses are looking for ways to modernize their IBM i applications while maximizing their existing code investments in RPG or COBOL applications. Some contend that using Microsoft development technologies is a bad way to go. They think it promotes replacement of existing IBM i applications or of IBM i itself.

My position has always been that PCs and Microsoft technology are a great way to enhance existing IBM i applications. XMLSERVICE and Microsoft Visual Studio are open source technologies your company can use to modernize, enhance, and augment your existing IBM i code base.

Using Microsoft .Net with XMLSERVICE

Occasionally I need to change part of a string value in a database table. SQL has three mechanisms that let me accomplish such a thing. Do you know what those three mechanisms are and how to use them?

First, we need a table for illustrative purposes.

create table Parts
  ( ID            char(6)       primary key,
    Description   varchar(40),
    Cost          dec (5,2),
    Price         dec (5,2));

insert into parts values                         
('XX-101', '20-inch Doodad',            0.12,    0.25),
('XX-105', '2-foot Doodad left-hand',   0.48,    0.75),
('XX-106', '2-foot Doodad right-hand',  0.48,    0.75),
('XX-118', '12-foot Doodad',            1.25,    3.00),
('BA-101', '5CM  Widget',               0.30,    0.45),
('BA-104', '15Cm Widget',               1.00,    1.35),
('BA-111', '150cm 

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

In Part 1 of this series, I introduced ParseFileContent, a 100 percent SQL user-defined table function (UDTF) that can parse delimited text (as a CLOB or IFS file) and format the results as rows and columns. As an advantage over tools like CPYFRMIMPF, this UDTF can streamline a text based import process by allowing CASTs, JOINs, and data integrity checks to be done while the import is running. As useful as ParseFileContent is, it does suffer a few drawbacks.

ParseFileContent is not particularly fast and it can’t handle