Wednesday, February 25, 2015

Changing Case using SQL

select upper and lower case conversion translation

Last week I discussed how to change the case of alphabetic characters using the new %UPPER and %LOWER BIFs, built in functions, added to CL in IBM i 7.2, you can read it here. I mentioned the limitations of using the %XLATE BIF in RPG to accomplish the same. But there is an easier way using embedded SQL in the RPG to emulate the functionality of CL's %UPPER and %LOWER do.

RPG's %XLATE requires that you need to give all of the characters that should be replaced with what you need them to be replaced by. This is useful for so many situations. But when converting case most programs I have seen many that just have the following:

Thursday, February 19, 2015

IBM i facts to surprise you CIO

There are times I come across an article on another website I think is worth sharing with you. Alex Woodie's "Five IBM i facts that will surprise your CIO" is one.

Trying to keep the IBM i relevant in your organization? It probably seems like an uphill battle at times, especially if you have a CIO who knows next to nothing about the platform. Here are five fun facts that may help save the platform at your organization, or at least get the CIO to give it a second look before he kicks it to the curb.

You can read it here, and feel free to share it with the "AS400 is dead" naysayers.

Wednesday, February 18, 2015

CL Built in Functions to Change Case

built in function to change upper to lower case %bif

IBM i 7.2 brought us two new CL built in functions (BIFs), %UPPER and %LOWER, that are used translate the contents of a variable or string to upper or lower case. I have always used the RPG BIF %XLATE to achieve the same result, I just wish IBM had added the same BIFs to RPG.

The syntax for both of these CL BIFs is similar to each other with just two parameters:

  1. Variable or string to convert - mandatory
  2. CCSID - optional

For example:

Wednesday, February 11, 2015

Reading a SQL table in SQL

sql fetch update insert to red update write to sql table

In a previous post I explained how to handle a SQL table using RPG operation codes to perform the file access. The post was just to illustrate that it was possible. I received messages expressing surprise that I had chosen to use RPG and not SQL. I think those who sent me those messages missed the point of the post. Anyway in this post I am going to show the same example, but using SQL for accessing the SQL table.

If I was doing this myself I would probably replace the entire DO loop in the RPG program with the following SQL statement:

Wednesday, February 4, 2015

Finding triggers using SYSTRIGGER

trigger information from systriggers

Triggers allow business logic to be placed at the file/table level, independent of the software. When you access the file/table with a program, SQL, DFU, or DBU the trigger will execute. Before making changes to objects or creating test data it is important to find what triggers are present on which files and tables to stop them from executing in unexpected manners.

Fortunately there is a SQL view, SYSTRIGGER in QSYS2, that contains all the information we need to know about all the triggers on the IBM i. I am not going to list all the columns in SYSTRIGGER as IBM's documentation job does a good job of doing it here.

Wednesday, January 28, 2015

Reading a SQL table in RPG

reading sql table in rpg

This idea for this post comes from a Comment posted on Creating a SQL table on the fly. Anonymous posed the question "Now how do you use that table in your RPG program?".

The post I describe how it is possible to use the SQL CREATE TABLE to build a SQL table extracting data from another file. I have mainly used this in processes that extract data and then send the extracted data to the requester via email. But there is no reason why I cannot read the table created in a RPG program.

Wednesday, January 21, 2015

How to replace Key Lists

replace klist with %kds key data structure or key field list

As we now have all free RPG we need to find ways to move from familiar fixed format definitions to free format equivalents. Key lists, KLIST, are a good example. I still see new programs written in RPGLE with free form calculations, but with fixed format Key lists.

  C     KeyTestfile   klist
  C                   kfld                    wkFld1
  C                   kfld                    wkFld2
  C                   kfld                    wkFld3

The solutions was provided back in V5R2:

Wednesday, January 14, 2015

Trim in CL and its Second Parameter

trim (%TRIM, %TRIMR,%TRIML) is now in CL. And did you know that there is a second parameter to state which characters you want trimmed?

I am sure I am not the only person who has been using the Trim built in functions (BIFs) in their RPG code to remove leading and trailing blanks from strings. Trim BIFs were introduced into CL with IBM i release 7.1, and function in the same way as their RPG brethren.

Were you aware that the Trim BIFs have a second parameter that can be used for removing characters other than blanks? I have Miguel Cordova to thank for bringing this to my attention. Using the second parameter I can remove any character using the Trim. Below I will show examples in RPG and in CL, and discuss what I found in using them in both languages.

Friday, January 9, 2015

Je suis Charlie

Je suis Charlie

I have been following with horror the news from France regarding the Charlie Hebdo massacre, and I have been reflecting upon how this effects me. I am fortunate that I am not in the danger those heroic people were when I express my rights.

I believe that the freedom of expression is essential to the creative process. As developers, programmers, system administrators, operators, etc. we need to be able to express opinions freely without fear of retribution. This blog is an example of my right of expression, as everything I say is my opinion.

While I do not know any of the people murdered or injured in this attack, I do feel that this is an assault on all of us as it attempts to silence us. The freedom of speech is a delicate balance, as one person's freedom of speech can be another’s hate speech. No matter how badly someone felt nothing justifies the actions that took place.

I feel I must join the chorus and say: Je suis CHARLIE.

Wednesday, January 7, 2015

Which Control options/H-specs do you use?

rpg control specification

In my many years of experience working for various companies I have found that just a few programmers define Control specifications in their RPG programs. The Control specification is commonly known as the Header specification, or H-spec, as in fixed format RPG the specification character, in the sixth position of the line, is 'H'. In all free RPG the Control specification was replaced by the Control options, 'CTL-OPT'.

In my opinion this was the most cryptic of the specifications in RPG III as you had to know what each column stood for, as it would only reveal its meaning when F4 was pressed to prompt the line.