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:

Friday, December 30, 2016

Leap second coming January 1 2017

I have just learned that there will be a leap second before January 1, 2017. A leap second is an extra second added to a day to keep UTC (Coordinated Universal Time) synchronized with "mean solar time". The leap second is added to December 31, 2016, after 23:59:59 UTC, and before 00:00:00 UTC on January 1 2017.

December 31, 2016 23:59:59 UTC
23:59:60 UTC
January 1, 2017 00:00:00 UTC

The question is how does this effect IBM i?

Wednesday, December 28, 2016

Calling a program in totally free RPG

call program in free format rog

Someone said to me that the reason that they will not use totally free RPG is they cannot call programs using it. In the earlier flavors of free format RPG they could just switch from free format to fixed to do the call, and return to free format afterwards.

  Var1 = 'Hello' ;
  Var2 = 1 ;
  Var3 = '' ;
C                   call      'OTHERPGM1'
C                   parm                    Var1
C                   parm                    Var2
C                   parm                    Var3
  if (Var3 = 'Y') ;

With totally free RPG once the **FREE compiler directive is placed in the code it is not possible to go back to fixed format after that. Therefore, the approach used above is not possible.

Wednesday, December 21, 2016

Easy way to convert date to words using SQL

convert date to an alphanumeric string

The idea for this post came from a meeting of the programming managers at work. My programming nemesis, the "I can do everything you do in RPGIV just as well using RPGIII" guy, was bragging that no-one could write a better way of changing a date into words than his old RPG38 program.

"I can do what your program does in just a few lines of code," I explained. He disputed that I could, and the challenge was on.

His old program is passed a date, as a number, and returns:

  1. Day number of the week, where 1 = Sunday, 2 = Monday, etc.
  2. Day of the week
  3. Name of the month
  4. The date in words, for example "THURSDAY JANUARY 1, 2017"

Thursday, December 15, 2016

Redpaper: Tools and Solutions for Modernizing, updated

Yesterday an updated version of the Tools and Solutions for Modernizing Your IBM i Applications Redpaper was released. This Redpaper, which was first published in 2014, describes the tools that are available from Independent Software Vendors, ISV, and Business Partners, BP, to modernize IBM i applications and databases.

It has sections about:

  1. Mobile, web, and client solutions
  2. Database modernization tools
  3. Security
  4. Tools for understanding and modernizing RPG and COBOL

While I usually avoid mentioning ISV and BP software in this blog, sometimes it is interesting to see what is "out there".

You can download the latest version of this Redpaper, as a PDF, here.