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.