FHG Volume: 15 Archives

Detecting A “Job End” Condition in DB2 for i

December 1, 2015 Hey, Mike

An ETL process extracts data from our ERP system and places it in our Business Intelligence systems. It runs 24/7 except for the End of Day process that shuts down the subsystem that it’s in. The SQL process we run ends up dying violently and the parent process fails (without recovering on startup). A graceful exit would be as simple as issuing a RETURN statement! Can SQL detect a shutdown process?

–D.S.

The ANSI SQL standard offers no specific guideline for implementing some kind of host shutdown process, so you’ll have to rely on the other special OS related views

…

How Many Interfaces Are Enough (To Print A CSV File)?

December 1, 2015 Ted Holt

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

CSV (comma-separated values) files have been part of my working world for years. I like them because they’re easy to build. Users like them because they can easily open them in Microsoft Excel. I’ve written about this before. Let’s end the year with a utility I wrote to print CSV files. I had fun writing it. Maybe you’ll find it useful in your shop.

When I need to read a CSV file on an IBM i system, I typically FTP the file from the IFS to a temporary

…

End of Year Feedback

December 1, 2015 Ted Holt

We’ve made it through another year. I hope it’s been a good one for you in spite of the hardships and challenges. Let’s read some of the feedback that I received in 2015 and see what we can learn.

Hey, Ted:

To make a grep search case-insensitive, use the “-i” switch. I also add the “-n” switch to show the line number.

catsplf -j 681206/MYSELF/MYJOB QSYSPRT 1 | grep -in 'smith'

I also have a SCANSPLF command. It is not case insensitive, but that would not be hard to implement.

Keep the great articles coming.

–Bryan

Bryan wrote in

…

Data Scrubbing Functions In DB2 For i

November 17, 2015 Hey, Mike

Imagine a character database field that stores a phone number. No formatting rules are involved, so the values in the column vary such as 8370155738, 837/015-5738, 837-015.5738, etc. We’d like to write an RPG program that allows the user to enter a number, formatted or not, onto the screen and if that string of numbers is found in the table’s phone column, show it to the user. Is there a way to use SQL to strip the non-numeric characters from the phone field, and select the record if the result matches the user input?

–Brad

Hi, Brad.

Scrubbing data is

…

Formatting Dates with SQL, Take 3

November 17, 2015 Hey, Ted

Great technique shared! Function overloading is a real boon to SQL programming. Here’s another way that requires less code and is less invasive (i.e., you won’t have to recompile anything.)

Leave your original FMTDATE function alone and add the following to the SQL source:

create function xxx/fmtdate
(inDate varchar(8), inFromFmt varchar(8), inToFmt varchar(8))
returns varchar(10)
language SQL
specific FMTDATEA
deterministic
returns null on null input

begin
 declare DateNum numeric (8,0);
 declare OutDate varchar(10);
 set DateNum = decimal(inDate);
 set OutDate = FmtDate(DateNum, inFromFmt, inToFmt);
 return OutDate;
end

I’ve used this technique to overload many user-defined functions and keep the HLL programming

…

An Introduction to Processing XML with RPG, Part 4

November 17, 2015 Jon Paris

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

In the first three parts of this series, I focused on the basics of using RPG’s XML-INTO. In this episode I want to wrap things up by covering two of the more recent additions to this support.

Let’s start with namespaces. I haven’t got time to go into all the whys and wherefores of namespaces. For now let’s just say that they allow you avoid name collisions by qualifying element and attribute names. Since you will encounter them in many documents it is important to understand the

…

Extracting Sample Data From A DB2 for i Table

November 3, 2015 Michael Sansoterra

The need to retrieve a sample or subset of data from a database is a relatively common (and somewhat annoying) task. For example, DB2 for i developers often want to extract data and create a scaled down copy of a library (a.k.a. schema) to develop with. Additionally, sometimes code testing can be done using random data, such as creating a battery of orders to process with varying customers, order terms, etc. This tip won’t attempt to tackle the full breadth of either of these scenarios. However, the aim is to show how some simple queries can be used to create

…

Why And How To Update The HMC

November 3, 2015 Larry Bolhuis

I deliver a session on the HMC to many user groups, including COMMON. Attendance had been falling, so COMMON’s education team had decided to give the session just one more go. This time though I walked into a packed house, with people carrying in chairs and standing along the walls! “What brings you all here at 8 a.m.?” I asked. “You’re going to talk about updating the HMC, right?” was the response. You see IBM had just announced POWER6 severs, and that mandated newer code on the HMC.

Some of you may wonder what an HMC is, so let’s

…

Tuning SQL Environments On i

November 3, 2015 Paul Tuohy

One of the frustrating things about being a speaker at conferences is that when you want to attend another session with a topic that peaks your interest, it invariably clashes with when you are speaking. But every now and again, the scheduling gods work in your favor. Such was the case recently, when I was speaking at the excellent International i-Power 2015 conference.

Scott Forstie, IBM‘s DB2 for i business architect, was giving a presentation, “IBM i Services – SQL interfaces into the IBM i operating system.” I had recently done an iTalk with Scott where we had chatted

…

Questions About AES Encryption In RPG, DB2 for i, And The Web

October 27, 2015 Michael Sansoterra

Hey, FHG:

We have never used encryption. Our new manager wants us to start using it on our IBM i 7.1 system. (For instance, we store passwords for our website members). I use the ENCRYPT_AES and DECRYPT_CHAR SQL functions in my RPG programs. However, with respect to AES encryption, our PHP web programmer is asking about how to generate an initialization vector and then save it later for decryption. Can you point us in the right direction? Finally, our manager doesn’t like the fact that the encryption key is in plain text in RPG source code. What are your

…

Search for:

Content archive

The Four Hundred
Four Hundred Stuff
Four Hundred Guru

Content archive

Recent Posts

Subscribe

Pages

Search