Friday, June 23, 2017

On my way to OCEAN's Technical Conference

ocean technical conference 2017

I will be attending this year's OCEAN Technical Conference in Costa Mesa, California. In my opinion this is the best IBM i technical conference in the western United States, and if you are in So Cal you really have no excuse not to attend. I always come back full of tips and ideas I can immediately use in my daily work.

The conference starts on Thursday July 20 and goes through Saturday July 22. For more information you should go to OCEAN's website and see their conference flyer.

I look forward to seeing you all there, and feel free introduce yourself and say "Hi".

Wednesday, June 21, 2017

Happy birthday AS400

happy 29th birthday as400

Today is the 29th anniversary of the launch of the AS400. It was June 21, 1988, when IBM announced their latest midsize server. You can see a video recording of the announcement here.

At its launch the AS400 server and its OS400 operating were ground breaking.

Tuesday, June 20, 2017

IBM VP reaffirms commitment to RPG and IBM i

ibm vp reaffirms future of rpg

In my opinion it is always good when IBM publishes something about their future commitment to PowerSystems servers, IBM i operating system, and the RPG programming language. Steve Pitcher sent me a copy of a letter, from June 9 2017, by Steve Sibley, the Vice President of IBM Cognitive Systems, that hits this trifecta.

At the start of his letter Steve leaves us in no doubt IBM's regard for RPG:

Wednesday, June 14, 2017

SQL Views for Authorization Lists

views for authorization lists to view user authorities and objects belonging to them

Many companies that use IBM i secure their objects using Authorization Lists. The Authorization List is its own object type, *AUTL. If you have not used or heard of them before I found this good description of them in IBM's KnowledgeCenter.

Like a group profile, an authorization list allows you to group objects with similar security requirements and associate the group with a list of users and user authorities.

Authorization lists provide an efficient way to manage the authority to similar objects on the system and aid in the recovery of security information.

I am not going to discuss in this post how to create Authorization Lists, how to add objects to the lists, or anything similar. I am just going to describe two new SQL Views that were added in the latest IBM i Technical Refreshes, 7.3 TR2 and 7.2 TR6:

Tuesday, June 13, 2017

Using SQL to load data into a test instance

using 3 part sql path to update test files

Today's post is written by David Taylor.

From time to time, we need to load data from a production system to a development system. To make the process more complex, the data often resides in more than one file. Using SQL, we finally found a way to load the data into multiple files from the same cursor. We include in the SELECT clause any fields from any of the files in the data set to refine the cursor. Once we have the right data set, we can use the final version for the multiple file loads. Keep in mind, we need the data from all three files for the process test to work correctly. We need not just any set of records; we need the data that matches the relationships and limits defined in the cursor. In the samples each file ends with a letter. That same letter is the first character for the field names. For example, FILEA has fields like AKEYFLD1, AFIELD1, and so forth.

Wednesday, June 7, 2017

Repositioning file pointer after EOF in CL

stop end of file from happening in a cl program

I am always grateful for the feedback I receive from you, the readers of this web site. There are many times you mention ways of doing things that either did not think of or was unaware of. Today is a good example. I have Andrew Norton to thank for bringing to my attention a command that is a better solution for the scenario I described in the previous version of this post.

Those of us who have programmed in CL have found that when read a file when the end of file has been encountered I thought there was no way I could use the file again in the same CL program. In RPG I could use the Set Lower Limits operation, SETLL operation to reposition the file pointer. But in CL I was stuck.

Tuesday, June 6, 2017

Display files in CL

how to code dspf in clp or clle

I could give this a subtitle: "How to put a bad date in a date field", but this is not the reason of this post, just an accidental discovery. Having previously written about how to use database files in a CL program I have to mention using display files in CL.

You cannot use CL for anything as complex as a subfile. But you can for what I call "report submission screens", you know the type: a screen is presented to the user, they enter the selection criteria they desire and press Enter, and a program is submitted to batch to generate either a paper report or an email attachment. My example display file has two record formats, and is very simple:

Wednesday, May 31, 2017

Using OPNQRYF for file I/O in CL

opnqryf and cpyfrmqry examples

In my last post about the various way I could perform file I/O in CL code I deliberately omitted mention of the Open Query file command, OPNQRYF, as I wanted to cover it in its own post. I am sure everyone who has been programming on the IBM i or its predecessors have encountered and used this command.

I used it too, but not anymore. In the days before embedded SQL become efficient and fast within RPG programs I would often use OPNQRYF. This command allowed me to select fields and records, sort the data in a different order to the files' access paths, etc. thereby reducing the amount of checking and processing the following RPG program would need to perform. But it was always slow as it used the Classic Query Engine, CQE, to access the IBM i's database. In version 7.2 OPNQRYF was moved from using CQE to the faster and more efficient, SQL Query Engine, SQE. While this did improve its speed and performance it is still more efficient, and in my opinion easier and clearer, to do the equivalent using SQL embedded in RPG.

I am going to be giving examples of how to use OPNQRYF to do some of the things I talked about in yesterday's post.

Tuesday, May 30, 2017

Read, write, and update a file in CL

equivalent of rpg read, write, update, setll, chain in cl

Someone messaged me telling me that there were not, in their opinion, many good articles about CL programming, and asked if I would write some. Personally I think the majority of IBM i developers under utilize this language, keeping it just as a simple control language. I use CL programs and procedures a lot, and wrote about my thoughts on the need to modernize your CL in a post last year.

Where to start? Coincidentally I received an email from another asking about reading, writing, and updating a file just using CL. Let's start with data base access. In this post I will use the RPG file operation's name and then describe how to do the equivalent in CL.

Wednesday, May 24, 2017

Determining the screen size

determine screen size if it is *ds3 24x80 or *ds4 27x132

Most of us are aware that TN5250 sessions (displays) on the IBM i can be configured in two sizes:

  • 24 x 80 commonly known as *DS3
  • 27 x 132 commonly known as *DS4

Most of the time IBM i operating system handles this for us, only displaying "wide" screens when my session is *DS4 and the information to be displayed is wider than 80 columns.

Tuesday, May 23, 2017

My favorite CL shortcuts

using cl shortcuts rather than long hand code

If I am going to write and give examples of programming in CL I need to start by explaining some of the CL language shortcuts I use. Perhaps "shortcuts" are not the right word, maybe "shorthand". Oh well, I am sure you will work out what I mean as you read this.

I use these shortcuts extensively in my CL code as for a couple of reasons:

  1. I think it is easier for someone else to understand the code I have written.
  2. It is just quicker to write, the faster I can write code the more I can get done.

I am going to start with my favorite three.

Wednesday, May 17, 2017

Defining LDA in free format RPG

how to define the lda in a free format rpg program

It has been pointed out that this web site lacks a good description of how to retrieve the data from the Local Data Area, LDA, in the post about defining variables in free format RPG. I agree, which is why I have written this about retrieving and updating the LDA.

I am going assume that we all know what the LDA is. If you do not then you can read IBM's definition of what it is.

There are two common ways to bring in and update the data from the LDA in fixed format RPG.

Monday, May 15, 2017

Why is my RPG program not writing to the file?

rpg output buffer

I have been asked this question a lot recently:

When I am in debug my RPG program does not write to the file. What is wrong?

I have decided to write this post so I can, in the future, refer people here.

Let me give a very simple example RPG program so I can explain what is going on.

Wednesday, May 10, 2017

Using SQL for message queue data

new view for viewing messages in message queues

One of the new enhancements that came with the latest TRS for IBM i 7.3 and 7.2 is a new SQL View: MESSAGE_QUEUE_INFO. This view returns one line for each message in a message queue, in a similar manner to what is returned by the Display Messages command, DSPMSG.

This will allow me to have a way to quickly, and easily, search a message queue for a message. I must have *USE authority to the message queue, and *EXECUTE authority to the library it is in. One thing to remember is that message queues do get full, and when they do messages from them will be deleted. Also certain message queues are cleared when the IBM i partition is IPL-ed. If you are looking for a message that may have been issued sometime ago you might have better luck searching the History log via SQL.

Wednesday, May 3, 2017

Viewing object authority using a SQL view

getting authority data from object_privileges view rather than dspobjaut or edtobjaut

One of the new SQL Views added with the latest Technical Refreshes for IBM i, 7.3 TR2 and 7.2 TR6, is going to be very useful to me. Once a year, at audit time, I am asked to produce a list of all objects within the production library list that are not adequately secured. With the new Object Privileges SQL view I can generate all of the information I need in minutes, using a few SQL statements. This is going to save me a lot of time and effort.

The Object Privileges view, OBJECT_PRIVILEGES, like many of the new Views is to be found in the library QSYS2. The information it contains is the same as the information I can generate using in the Display Object Authority command, DSPOBJAUT. Rather than list all of the columns I will list them when I use them in various SQL statements. If you want to see a full list of all the columns visit the link I have provided a link to the IBM documentation for this View at the bottom of this post.

Tuesday, May 2, 2017

Second parameter added to SQL's LTRIM and RTRIM

2nd parameter added to sql trim built in functions rtrim and ltrim

As part of the goodies that came with the recent Technical Refreshes, TR, was the addition of a second parameter to SQL's Left trim, LTRIM, and Right trim, RTRIM, built in functions. The second parameter has the same functionality as it does in CL and RPG's trim built in functions, when used trimming particular character from either the left or right of the string.

The syntax of these SQL functions is remarkably similar to that of the CL built in function:

Wednesday, April 26, 2017

Using SQL to aggregate columns into one returned result

aggregating multiple values into one result set using sql

There are times when I have want an easy way to put values from a file, or table, "horizontally" into one column rather than vertically with more than one row.

"Vertical" "Horizontal"

Wednesday, April 19, 2017

Min and Max built in functions added to RPG

using the rpg min and max built in functions

A pair of introductions to RPG as part of IBM i 7.3 TR2, and 7.2 TR6, are the minimum and maximum built in functions, %MIN and %MAX. The maximum built in function returns the greatest value in a list of variables and/or values. The minimum returns the lowest value from a list of variables and/or values. Otherwise the way they are defined and the rules governing them are the same.

To use these functions there must be at least two values, and they must be of the same type. What I mean is that all of the values must be some type of numeric, character, date, time, or timestamp. I cannot mix types in the values. Pointers cannot be used.

They are so simple I can go straight to examples of using them. Let me start with a variety of numeric data types:

Thursday, April 13, 2017

Change to SQLRPGLE compile listings

type of rpg used in sqlrpgle compile listing changed to free format

Something I have noticed since the installation of IBM i 7.3 TR2 and 7.2 TR6 on the partitions I use, is a change to the compile listings of SQLRPGLE programs.

The process to create an object from a SQLRPGLE source member is a multi-step process:

  1. The syntax of the SQL within the source member is checked to ensure it is valid.

Wednesday, April 12, 2017

Nested data structures in RPG

nested data structures with other data structures

I am grateful to the folks at RZKH for applying the PTFs for the latest TR for IBM i 7.3, TR2. I started looking at the new features and functions added to RPG as part of this TR and decided to write about those first (Note: Same functions are included in 7.2 TR6 too). There were three additions, the one that first caught my eye was the ability to nest data structures.

Nested data structures are only available when defining data structures in free format RPG. If you are still using fixed format definitions this is another sign from IBM that it is time to move to free format RPG.

Inserting a nested data structure is pretty much as I would have expected, see below.

Tuesday, April 11, 2017

End of support for IBM i 7.1 announced

Steve Will, chief architect for the IBM i operating system, announced in a tweet today that the end of support, EOS, for release 7.1 will be on April 30, 2018.

Tweet from Steve Will: One of the FAQs I receive now has an official answer: IBMi 7.1 EOS 30 April 2018

Wednesday, April 5, 2017

How to write data to a multiple member file

handle multiple member files in rpg

Often I get the same question asked by several people, this time I have been asked the same question five times just this week, and I have been told that this is a question people have been asked in interviews:

How do you write data to different members in the same file using RPGLE?
The file has ten members, and you need to write data to the fifth and sixth members in one RPGLE program.

Tuesday, April 4, 2017

Saint Isidore of Seville day

Happy St Isidore of Seville day!

Saint Isidore is the Catholic patron saint for computers, programmers, and the internet. When things go wrong, very badly wrong, and everything else is failing it is always nice to know there is someone we can call on for divine intervention.

I am sure other denominations and faiths of saints, deities, etc we can ask for help from? If you know of any please post their details in the comment section below.

Monday, April 3, 2017

IBM i performance FAQ updated

ibmi performance faq updated april 3 2017

An updated version IBM's IBM I on Power – Performance FAQ was published today, called a "Spring refresh" by Steve Will the IBM i chief architect.

The What's new in the latest version?, section 1.3 on page 11, lists eight new questions added to the document.

If you are just a programmer type, not dealing with the finer points of tuning a Power server, there are sections to do with optimizing Cobol and RPG too:

  • Section 8.14 p57: How do I tune RPG/COBOL application performance and native I/O file access?
  • Section 9 p62-68: Database performance
  • Section 10 p69-70: RPG/Cobol native I/O

Good stuff to read, learn, and apply to your work.

The document is a PDF file and can be found on IBM's web site here.

Wednesday, March 29, 2017

Command syntax validation API

using qcmdchk and qmhrcvpm api

Like many posts in this blog, this started off as a simple explanation of one API, and quickly became a more complex scenario giving more useful information to you, the readers of this blog. It all started when I wrote about using the QCMDEXC SQL procedure. I received a message asking me why I had not used the QCMDCHK, Check Command Syntax, API before using QCMDEXC. This is a good point: How can I check if a CL string being received is valid before I pass it to either the QCMDEXC SQL function or API? And if it is not valid how can I return a useful error message?

When writing the example RPG code for this post I found that the most interesting information QCMDCHK generated about any error was written to the program message queue. This is information I want, so to get these diagnostic messages from the program message queue I would need to use another API, the Receive Program Message API, QMHRCVPM.

Wednesday, March 22, 2017

Determine the end of month using CL

get end of month date only using cl

The idea for this post came from several people who asked me the same question: "How to find the date of the end of the month? only using CL". When I quizzed them they explained that this was asked during an interview. If anyone asks me for help I always ask them how they would do it, and they all had pretty much the same solution, but struggled with how to determine if February should have 28 or 29 days. They asked how I would have done it. I came up with two solutions that I will share here.

The first version is very similar to what they can come up with. In this example the date is in *DMY format.

Tuesday, March 21, 2017

System, Partition, and Processor pool information all in one place

qlzarcapi for system, partition, server pool information

I am the first admit I do not know everything about IBM i as, in my opinion, there is just too much to know. Therefore, I am always grateful when people send me things they have found as there are many times they are new to me too. I have Laurent Gomes to thank for bringing to my attention the API QLZARCAPI.

I find it interesting that I cannot find any mention of it IBM's KnowledgeCenter, and only a passing reference in the documentation for the QMGTOOLS. What this API does is to display the system, partition, and processor pool information for the current IBM i partition.

Friday, March 17, 2017

TR2 and TR6 released today

ibmi 7.3 tr2 and 7.2 tr6 out now

Today is the day that the new Technology Refreshes, TRs, become available for IBM i 7.3 and 7.2. You can see what is included in them in an earlier post I wrote about 7.3 TR2 and 7.2 TR6.

TRs are available as PTFs that are downloaded from IBM's Support site:

New PowerSystems S812 out today

power8 s812 out now

The latest of the PowerSystems server is out today. The S812 is the lowest end Power8 server, for up to 25 users. Alas, it is an either IBM i or AIX scenario as the server can only host one partition. It is in P05 tier and will be marketed to new small size customers looking for a robust server, and as a replacement for the smaller Power6 and Power7 servers.

You will find more information about this new PowerSystems server:

Thursday, March 16, 2017

Interview with IBM Systems Magazine

ibm system magazine interview with author simon hutchinson

About a month ago I was contacted by Paul Tuohy and asked if I would consider being interviewed about this blog, RPGPGM.COM, for his iTalk with Tuohy podcast for the IBM Systems Magazine. I was very flattered that he considered me worthy, as he has interviewed members of IBM's IBM i and PowerSystems teams, and many of the IBM Champions.

Yesterday the interview was published on IBM Systems Magazine's website, with the title Simon Hutchinson on RPG Code and, in both text and audio. You have all read how I write, now you can hear what I sound like.

I just want to thank Paul for making the interview so comfortable, I feel I could have continued talking for a lot longer than the allotted time.

Wednesday, March 15, 2017

Difference between UNION and JOIN

difference between selection with join and select with union

After last week's post about using a Select statement with an UNION several people have asked me to clarify the differences between an Union and a Join.

I think using some simple graphics makes it a whole lot easier to explain. I have two files:

File 1
File 2

Monday, March 13, 2017

New in DB2 for i in forthcoming TRs from COMMON

Last Tuesday (March 7, 2017) the COMMON user group posted a video called What new in DB2 for i, presented by IBM's Scott Forstie, to their website.

In this 40 minute video Scott discuss the enhancements to DB2 for i (SQL) that are being delivered at part of IBM i 7.2 TR6 and 7.3 TR2, at the end of this month.

Fortunately this video is available to the general public on COMMON's website here.

Wednesday, March 8, 2017

Using Union to combine data from two files into one View

sql union clause to combine data from more than one file

When, in the ERP application my employer uses, an order is closed and posted to General Ledger the order's data is copied from the live file to the history file, and then deleted from the live file. This becomes an inconvenience when I am asked to produce a list of orders for a customer over a date range; the eligible data could be in both files. I have always wanted a way to link the live and history files together so that I can get the data from one place in the order I want.

Like most IBM i developers I want to use SQL to get data rather than use RPG's data access operation codes. I want to link these files together using SQL as I can get large chunks of data in one SQL operation faster than I can in RPG. Fortunately there is something in SQL to give me what I need, the UNION clause, that will allow me to join two or more selects statements together.

Wednesday, March 1, 2017

Using Get Diagnostic for SQL errors

get diagnostics for errors

I have already written about using DB2 for i's (SQL) GET DIAGNOSTICS to get the number of rows changed by a SQL statement. I can use the GET DIAGNOSTICS statement to get a lot more information about the SQL statement that has just occurred. In fact there are approximately 100 different types of information that can be retrieved using this statement, which is too much for one post. Therefore, I have decided to concentrate upon those keywords I think would be useful for diagnosing errors.

I have identified seven of keywords that I would find useful when trying to determine the cause of an error:

Thursday, February 23, 2017

PTF cover letters for 7.3 TR2 and 7.2 TR6

ptf cover letter for ibmi 7.3 tr2 7.2 tr6

The cover letters for the PTFs for IBM i 7.3 TR2 and 7.2 TR6 were released onto IBM's Support site yesterday. You can find them at:

The PTFs will be available for download on March 17, 2017.

For the details about what will be in these Technical Refreshes see IBM i 7.3 TR2 and 7.2 TR6 announced, which gives you an overview and has links to all the information from IBM.

Wednesday, February 22, 2017

Why I should be using the QCMDEXC SQL procedure

using sql procedure qcmdexc rather than api

My favorite API has to be QCMDEXC. I think it was the first API I used when I when I was making the transition from RPGII to RPGIII many, many years ago. I use it so often I think half the programs I write have it in them. QCMDEXC allows me to execute any CL command that does not return any values.

For several releases there has been a SQL procedure QCMDEXC, which does the same as the API. Then in IBM i 7.1 TR7 there was an update to the SQL procedure. You will find that I have used it in several examples in prior posts, but I have not given it its own post until now. I have to confess that I am so accustomed to using the API program version of QCMDEXC, I keep forgetting to use the SQL procedure.

Wednesday, February 15, 2017

Using API to test Help Panels

quhdsph api to diplay uim help module text

After publishing the post about how to create help text for display files using UIM several people messaged me that there is an IBM i API that can be used to test the help, without needing to add code to a display file. I have to admit I was unaware of the API, and am grateful to those who messaged me.

I decided to create a program to call the Display Help, QUHDSPH, API that way I can have a simple user interface to use, I only need to enter the variables I want and leave the rest with there defaults. I am going to show a very simple example here to illustrate how QUHDSPH can be used. As this is a simple example program a lot of the code I would add for myself, for validation etc. is not present.

For simplicity I am using a display file to enter the variables for the help module I want to see. There are other ways I could have done this, perhaps using a command instead, but I want this to be K.I.S.S compliant.

Tuesday, February 14, 2017

IBM i 7.3 TR2 and 7.2 TR6 announced

ibmi 7.3 tr2 and 7.2 tr6

Steve Will, chief architect of IBM i, made the "spring" announcement of new Technical Refreshes for 7.3 and 7.2 in his You and i blog earlier today.

Included in the announcement:

  • A new lower end PowerSystems server, S812.
  • DB2 (SQL) enhancements
    • New

Monday, February 13, 2017

Video: 7.3 TR1 and 7.2 TR3 latest and greatest

A video by IBM's Tim Rowe going into many of the cool things that came out with IBM i 7.3 TR1 and 7.2 TR3, that was released last October.

Friday, February 10, 2017

WMCPA spring conference is in March

The Wisconsin Midrange Computer Professional Association, WMCPA, will be holding their spring technical conference next month. This three day event, Tuesday March 14 – Thursday March 16, promises to be the largest IBM i conference in the Midwest this year, with over 50 presentations from many well-known IBM i experts and 2017 IBM Champions. Tuesday starts off with the WMCPA sponsored "Women in IT", geared towards high school girls and women already working in or interested in Information Technology.

This year's event is at the Lake Lawn Resort in Delavin, WI. The resort is less than an hour from Milwaukee, and 1 hour 45 minutes from Chicago.

Wednesday, February 8, 2017

Adding UIM help to the Display file

add code to dds for help to be displayed

Having created a UIM help panel group I need to add the code to my Display file's DDS so that the help will be displayed. This post is going to use the UIM panel group that was created in my previous post to demonstrate, in simple terms, how to do this. I have designed a very simple display file to only include what I think is the minimum needed for it to work.

Most people have no idea where to find the Help key in their 5250 emulation client, therefore, as per SAA CUA standards I always define F1 as an alternate help key. I define the alternate help key in the file level keywords, top before the first record format definition, of the display file.

Rather than use SDA I find it easier to enter this source code using a source code editor, for example SEU. Therefore, I will be describing how to add this code as if I am using a source code editor.

Monday, February 6, 2017

IBM video: Introductory DB2 for i and SQL

While this video was recorded a few years ago it is still a good introduction for those wanting to learn SQL. The video is called IBM DB2 for i and SQL overview, and it is presented by IBM’s Eric Jackson.

Since the video was recorded IBM has revamped their website, so the link to the IBM DB2 for i page, shown in the sixth minute, has been replaced by:

Wednesday, February 1, 2017

Creating help text using UIM

uim panel group help

Thanks to UIM, User Interface Manager, designing help for your DDS display files is very simple. UIM has been used by IBM for their screens, menus, and help panels since the launch of the AS400, it was not until V2R1, 1991, that it was made available to programmers to create their own help panels. The "language" itself is easy to become familiar with and its compile listings are some of the easiest to decipher on IBM i.

In this post I am going to show how to create UIM help panels that can be used in DDS display files, and in a second post how to add the various DDS keywords to your Display file source code to use the UIM help panels.

Monday, January 30, 2017

Steve Will: What is coming in 2017

This is a short video that Steve Will, IBM i's chief architect, made for OCEAN user group of Southern California about what is coming in 2017.

This compliments his earlier blog post about the future of IBM i.

Wednesday, January 25, 2017

Recovering source for a Physical file

recover source for physical file

I am sure this has happened to us all: I have an object and the source it was compiled from is missing. In an earlier posts I showed how I could retrieve the source code from a program, but what about other object types?

Many, many years ago I wrote a command and programs to retrieve the source code for various types of files. When I recently found a physical file with its source missing I thought "I will just use my retrieve command". To my horror I found the command source and object in my personal tools library, but the programs and their source were missing. I may only use this command once in a very long time, but in the past it had proved invaluable in recovering the source for physical, display and printer files. I decided to go ahead and recreate the programs, in several stages. First I want to be able to recover the source for a physical file, then I would move onto the more complicated arrangements found within display and printer files. This post will describe the first stage of this journey, recovering the source for a physical file. The later stages will be covered in future posts.

Monday, January 23, 2017

Steve Will: IBM i future 2017 update

2017 update on future of ibmi

I get many people asking me about the future of IBM i, therefore, when Steve Will, IBM i's chief architect, publishes a blog with an update about our beloved platform I feel compelled to share it.

His blog starts off with this same question:

One of the most common questions people ask me is "What's the future for IBM i?"

He continues and layouts IBM's vision of the future, reiterating the strides that have been made in the capabilities of this operating system.

Wednesday, January 18, 2017

Easy way to merge data using SQL

merge statement in sql

I had a project where the first step was to merge data from three files into one. I have used the word "merge" deliberately to differentiate it from "copy". With a "merge" I needed to update specific fields if a record is present in the output file with a matching key. If there is no matching key record I add a new record. I have done this many times in RPG, this time I wanted to try something different, like SQL.

After searching IBM's KnowledgeCenter I discovered the MERGE SQL statement. I do not know when it was released, but searching in the different versions of the KnowledgeCenter I can find it in the versions for IBM i 7.1, 7.2, and 7.3, but I cannot find it in the earlier releases. The MERGE does exactly what I want it to, it will update on a match and insert when there is no match.

Monday, January 16, 2017

IBM video: Introduction to database modernization

Even though this is now three years old I thought it was worth sharing: an overview of the "Introduction to Database Modernization Course OD20" course, presented by IBM's Dan Cruikshank. He goes into how to convert from a DDS files model to a SQL DDL model.

Wednesday, January 11, 2017

Using auditing columns to audit data

generated audit columns

The germ for this post came from a comment made by a work colleague. I always insist that all master files have a "Last update user” and "Last update timestamp" field, making it possible to know when a particular record was last changed and who did it. "Wouldn't it be nice if the system updated the audit user and time for us," my colleague said looking at an old master file without these fields. This sent me to IBM's KnowledgeCenter to see if this was possible, and after a while of poking around I found the way to do it, auditing columns.

If a file or table has auditing columns, every time data is added to or changed the auditing columns will be automatically updated by the IBM i database manager, no extra coding needed in my RPG or SQL. I cannot move values to these columns, thereby, ensuring that the information contained within is sacrosanct. I cannot define auditing columns in DDS, if I need to add them to DDS file I would use the SQL ALTER TABLE statement. There are 21 types auditing columns covering all kinds of information, but in my scenario I just want to add auditing columns that will allow me to see:

Monday, January 9, 2017

IBM document: DB2 for i FAQ

I have to thank an IBM-er for bringing this document to my attention. It is seven page document titled: DB2 for i Frequently Asked Questions, and was released November 1, 2016.

The questions it answers are:

  1. What is DB2 for i?
  2. Do I need to buy DB2 for i?
  3. What level of IBM i supports DB2 for i?
  4. How does DB2 for I compare to DB2 for Linux/Unix/Windows?
  5. Is DB2 for i "optimized" for SAP?
  6. Do I need DB2 to connect my Linux, Unix, or Windows clients to DB2 for i?
  7. Does DB2 for i support data warehousing?

Wednesday, January 4, 2017

Using SEU with totally free RPG

use seu with modern totally free rpg

I have been asked the same question many times recently: "Why can't I use SEU with the new RPG?" Before I directed people to various posts in this blog, which I think has confused some. Therefore, I have decided to put it all in one place, this post.

Let me start by saying that it is possible to use SEU to edit totally free RPG. The issue is that IBM has not considered SEU the editor of choice for IBM i since release 6.1, not making any changes to it since that release. Rational Developer for i, RDi, is now IBM's preferred editor. Unlike SEU, RDi is not included with the standard package of IBM i based development tools. RDi is not free, its licenses are purchased separately. I would recommend that if you can get a license of RDi you should start learning how to use it, rather than using the out of date SEU. For the sake of this post I am going to assume that you do not have an RDi license, and you have to continue to use SEU.

Sunday, January 1, 2017

Happy 2017

Another new year is upon us and I am filled with excitement with what it will bring us all, and gratitude for what 2016 brought us too.

It is a truly exciting time to be involved with IBM's PowerSystems servers and the IBM i operation system. In my opinion the highlights of 2016 were: