Thursday, September 20, 2018

Closing all the files with one operation in RPG

closing multiple files with one rpg close operation code

There are times I stumble across things in the IBM manuals I felt I should have known. One of these is the ability to close all the files in a program with just one close statement. I have no idea how old this ability is, but it is relevant if you are programming your RPG in a modern manner.

Before main procedures were introduced to the RPG world, our programs were simple. We defined our files, and knew that they would close when the program ended.

01  **free
02  ctl-opt option(*nodebugio:*srcstmt:*nounref) ;

03  dcl-f PFILE1 ;
04  dcl-f DSPFILE workstn ;
05  dcl-f PRTFILE printer ;

06  *inlr = *on ;

Wednesday, September 19, 2018

Getting data for my auditors is simple using SQL

easy way to get user info using sql

It is that time of the year when the auditors start asking me for system information about the IBM i I am responsible for. The reports they want from me today are:

  1. Users who have not signed onto the IBM i for over a year.
  2. Users who have a user profile, but have never signed onto the IBM i.

It is also a good opportunity to show how some of the things I have written about in the past come together to make my job a lot easier.

A colleague, who is responsible for a different IBM i partition, asked me if there was an easy way to get this information?

Tuesday, September 18, 2018

Get a specific number row using SQL

getting the 98th row from a file using sql

This is going to be a quickie. I was asked:

How can I get the 98th record from a file using SQL?

Fortunately this very simple, but first let me go through setting up my example.

I created a Db2 for i table to use in this example. I could have used a DDS file just as well. But this gives me an excuse to use one of what I consider one of the cool features of Db2 for i tables, identity columns.

If I am using a table with an identity column I don't have to increment some value in a field to keep a unique field in a file or table. The value for an identity columns are auto generated by Db2 for i, so all I have to do is just define them in my table. My example table just contains an identity column.

Friday, September 14, 2018

IBM i 7.3 TR5 and 7.2 TR9 now released

ibm i 7.3 tr5 7.2 tr9 out now

Today is an exciting day as the latest Technical Refreshes for the currently supported releases of the IBM i are available to download as PTFs:

  • IBM i 7.3 TR5
  • IBM i 7.2 TR9

I am not going to repeat what these TRs include, I am going to give you a]the link to the post I wrote when they were announced in August, see IBM i 7.3 TR5 and 7.2 TR9 announced.

The PTFs you will need to download and apply to install the new TR on your IBM i are:

Wednesday, September 12, 2018

Generic program to use SQL count for any file

program to get count of records from any file using sql

The germ for this post came from a question I found in an IBM i Facebook group. How could this person write a program where he would pass the name of any file to SQL and get a count of records in that file. I gave my answer to that question, but the more I pondered I came up with what I consider to be a better solution. Which is what I am going to describe in this post.

I have written about creating and executing SQL statements contained in program variables before, and this just builds upon that.

Fortunately the SQL syntax to count the number of records is the same no matter what file or table I use:

SELECT COUNT(*) FROM some_table
 WHERE some_column(s) = some_value(s)

Wednesday, September 5, 2018

Using SQL to determine how many records end with...

determine what alpha fields end with

I was asked an intriguing question that I thought it would make a good post for this blog:

Using SQL, how would I get a count of all records in a file where the value in an alphanumeric field ends with 18?

Rather than show just the finished SQL statement I am going to show how, and why, I built each part of the select statement.

Let me start with my test file. It will come as no surprise to regular readers of this blog that it is called TESTFILE, and is in the library QTEMP. The file contains one alphanumeric/character field, CHARFIELD, its length is irrelevant. Its contents looks like:

Wednesday, August 29, 2018

Getting the System Name using SQL

get as400 system name using only sql

What appeared at first look to be a simple challenge given on Twitter:

How would you get the system name of a partition via a SQL statement?

In a CL program this is simple as I can retrieve the system name using the Retrieve Network Attributes command, RTVNETA.

03  RTVNETA SYSNAME(&SYSNAME)

So how to get the same information using SQL?

Wednesday, August 22, 2018

Using Environmental Variables

environment values are more than just widening the strdbg window

This all started with what was going to be a very short post about using an Environment Variable to set the width of your debug screen. But I became curious and by the time I finished playing with them this grew into how to create your own Environment Variables, and use them.

Let me start with the question: What is an Environment Variable?

Environment Variables come in two types:

  1. System – these Environment Variables are stored in the global environment space and are available to all jobs running on this IBM i system, and are persistent even when the system is IPL-ed.
  2. Job – the variables are outside the program's space, and are job specific, when the job ends they are automatically deleted.

Wednesday, August 15, 2018

Getting data from XML file directly into data structure array

xml straight into file

I have written before about retrieving data from a XML file in the IFS. In that example I took that data from the file into a XML column in a DDL (SQL) table and then processed it from there. Since I published the example I have received two messages, one from Birgitta Hauser and another from Jan Koefoed-Nielsen, giving me examples of how to retrieve the data from the file and format it into columns, not using a DDL table as an interim step.

In this example I will not be directly outputting the information from the IFS XML file directly into an output file or table in IBM i. My experience of receiving XML files makes me want to validate what I am sent, before I start updating production files. A comma in a number, currency symbols, characters in what should be numeric value, and untranslatable characters should all be handled before updating any file. To this end I will be retrieving the data from the XML file and putting it into a data structure array. I can then "read" the array and perform any validations I want.

Monday, August 13, 2018

AS400 to IBM i: And we're just getting started

Video featuring Steve Will, chief architect of IBM i, and Alison Butterill, product offering manager for IBM i, showing ways IBM i clients have innovated over the past 30 years.

Runs just over 1 hour.

Hosted by HelpSystems, recorded July 18, 2018.