It has been a long time since the release of version 6.1 of IBM i. In fact, that version was released in January of 2008, and there are still some companies that have yet to upgrade. We’re also working with several companies that are just now upgrading to 6.1, but most of them are only doing so because IBM will soon no longer support prior versions. However, supportability is not the only reason you should keep an eye on updates. There have been several since the release of 6.1, and this post will focus on the critical components of the latest version, 7.3.
IBM released version 7.3 for IBM i on April 12, 2016. As with any IBM i update, there are so many changes and improvements across so many disciplines that reading the update documents would be cumbersome, to say the least. With that in mind, this post will focus on a few select items that most of our clients will find useful and, in many cases, necessary. These areas of focus are RPG development, auditing capabilities and data analytics.
RPG IV Upgrades
We all want easily readable, supportable programs. If you have complete control over what you’re creating, from the database to the applications, you can create such programs. More often than not, however, you need to write programs based on existing databases that could be built using different standards. Fortunately, 7.3 contains a couple of enhancements that can help you develop clean structures and more readable programs.
Fully Free Form RPG:
RPG source with the special directive **FREE in the first line contains only free-form code. There is no limit on the length of the source code line, and you can structure your program as you see fit. Ultimately, this capability allows you to get back to creating RPG programs that flow as they should.
Still, there is an important consideration for those times when you need to bring in COPY routines. The column-limited source code in your existing COPY routines can be brought into the free-form programs. There is now no need to recreate those source members to be included in your new standard.
Aliases:
One of the items that can help make your programs more readable is the use of aliases. The field name size restrictions you encounter when you’re defining your database files is problematic in generating meaningful names. Now, you can specify the ALIAS keyword on any externally described file, and the RPG compiler will recognize that name in your programs.
See the following example:
A R ITEMMST
A ITEMNBR 15A ALIAS(CUSTOMER_NAME)
A ITMDESC 50A
A ITSLLDT 3 0 ALIAS(SALES_LEAD_TIME)
You can specify ALIAS in your file specification:
Fitemmst if e disk ALIAS
From that point forward in your program you can refer to SALES_LEAD_TIME for the field ITSLLDT, making your processing that much more supportable for yourself and for other programmers that may need to support your work in the future.
%SCANR:
Although this function won’t directly make your applications more supportable, it can save you some coding time and energy. This new opcode helps you to find the last occurrence of a string, as opposed to the first occurrence of a string.
The following code pulled from the IBM documentation is a good example of how much easier it is to isolate something at the end of a string:
path = '/home/locations/manchester/a.txt';
lastSlash = %SCANR('/' : path); 1
if lastSlash = 0;
filename = path;
else;
filename = %SUBST(path : lastSlash + 1); 2
endif;
Data Tracking
Auditing Columns:
We often need to know which user or application affected the last update to a table. With Generated Auditing Columns, we can track important characteristics about the function that performed the last update. There are three types of data that can be tracked in a generated auditing column, and you can have multiple columns in the table that track this information. The three types of data are data change type; special register; and built-in global variable.
- A column defined to contain a generated data change operation will be updated with an I or U to indicate whether the last modification was an insert or update.
- A column defined to contain a generated special register value will be assigned the current value of the special register when the data change operation occurs.
- A column defined to contain a generated built-in global variable value will be assigned the current value of the global variable when the data change operation occurs.
Any time the row is inserted or updated, the database manager automatically loads these columns. At the time you create your table, you can define the characteristics that you would like to track in the auditing columns. See the example below, where the data change type, user and job name are all logged in the auditing columns.
CREATE TABLE ITEMMST
(ITMNBR CHAR(15),
ITMDSC CHAR(50),
ITSLLDT DECIMAL(3),
ITBUYR CHAR(10),
IT_CHANGE_TYPE CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION),
IT_CHANGE_USER VARCHAR(128) GENERATED ALWAYS AS (SESSION_USER),
IT_CHANGE_JOBNAME VARCHAR(28) GENERATED ALWAYS AS (QSYS2.JOB_NAME))
This capability becomes even more useful when you implement Temporal Tables, which allow you to not only see the auditing data for the last update, but also through your entire update history. Over time, you can do some very good data analysis with the values you care about in the auditing columns. With that ability in mind, let’s talk about temporal tables.
System Period Temporal Tables:
It can sometimes be difficult to track updates over a period of time. New regulations and auditing requirements only add to the complexity. Journals were once an option, but reporting against their data is not easy. Thus, in many cases we the users tried to design our applications to deal with these changes.
The new system period temporal tables allow you to create a history table of a current active table, and within that history table there will be “before” images of all of the changes to your current table. The period in which the change happened will be tracked, as well, and if you implement the auditing columns outlined above, you can also track who and what made the changes to the tables. Best of all, this data is stored in a table that’s easy to report against.
You now have the ability to create some analytics for changes to data over time. In a simple example, if you have a table carrying the price of a product, you can now create reports that show you price changes throughout your company’s history – without doing any application development to acquire that data.
What’s more, setting up the system period temporal tables is quite easy. The table is created similarly to most other tables, but it has three additional time stamp columns and a system period column. Refer to the example below, which was taken from IBM’s documentation:
CREATE OR REPLACE TABLE DEPARTMENT
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT CHAR(3) NOT NULL,
LOCATION CHAR(16),
START_TS TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
END_TS TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
TS_ID TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID,
PERIOD SYSTEM_TIME (START_TS, END_TS),
PRIMARY KEY (DEPTNO))
You would define the corresponding history table as follows:
CREATE TABLE DEPARTMENT_HIST LIKE DEPARTMENT
Then you would tie them together in a versioning relationship like this:
ALTER TABLE DEPARTMENT ADD VERSIONING USE HISTORY TABLE DEPARTMENT_HIST
Once the table is set up and versioning is turned on, any changes or deletes to the table will cause the values from the row prior to the update to be written to the history table.
Analytics Improvements
Have you been taking advantage of the OLAP functions that are now in DB2 for i? So many companies are moving copies of data to data warehouses in order to accomplish some of the big data analytics, even though the capabilities are right there on IBM i. Furthermore, version 7.3 contains new OLAP functions that will allow you to perform more detailed data analytics than you could before.
First, let’s quickly recap OLAP. OLAP (online analytics processing) allows you to bring back data ranking, row numbering and other aggregate information into your query. For example, if you had a table with the standardized test scores for students throughout your state, you could rank those students in your reports. Or, you could also include grouping and ranking students within a specific school district.
There were already a host of OLAP functions available, and the release of 7.3 brought even more. Review the OLAP specification at the following link for detailed information on the capabilities available to you:
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzolapexp.htm
Adopting v7.3
All in all, the IBM i 7.3 update contains a host of upgrades, fixes and efficiencies you won’t want to miss, especially if you’re still behind on upgrading to 6.1. We’ve covered RPG IV upgrades, data tracking enhancements and analytics improvements, but there a host of other important changes. To incorporate all of these changes effectively, with minimal downtime, resources and hassle, you’ll need a strategic partner – a managed services provider who can help to implement new technologies, as well as the improved processes those technologies allow. To learn how we can help, call or email us today.
