Wednesday, December 13, 2017

Using SQL to get information about ASP capacity

sql view asp_info get data about asp

Someone asked me if there was an easy way to monitor the available storage space in their Auxiliary Storage Pools (ASP)? I have to admit this is something I have not been asked before, and not something I worried about. I knew we were getting close to critical storage when I would receive the message from the system operator asking what could be deleted from the system before that was reached.

Fortunately in the latest round of Technology Refreshes, IBM i 7.3 TR3 and 7.2 TR7, a new Db2 for i View was added to return information about the ASP.

The new View, ASP_INFO, is one of a pair of additions to Db2 for for ASP monitoring. Like all the recent additions to Db2 for i this View is found in the library QSYS2. I am not going to list all the columns here, as I will only using a few in this example. If you want a list of them all you can go to IBM's KnowledgeCenter here.

Wednesday, December 6, 2017

Resolving "array X not defined or not usable"

overcome the array not defined or not usable compile error

I have shared my example of using a multiple row fetch as an efficient way to get multiple records/rows at once, and place the fetched data into a data structure array. To save myself time, and effort, I don't enter all the fields from the file/table as subfields, I use the file/table to externally define the data structure. Many of my colleagues where I work, and many of you, use this method as it makes things like programming subfiles so easy.

Every once in a while someone comes to me with, what I can only describe as a cryptic, message in the SQL precompile listing:

Wednesday, November 29, 2017

Getting data about jobs using SQL

JOB_INFO table function in place of WRKSBMJOB, WRKUSRJOB, and WRKSBSJOB

This UDTF, User Defined Table Function, slipped under my radar when it was introduced last year as part of Technology Refreshes for IBM i 7.3, TR1, and 7.2, TR5. It is as if the commands WRKUSRJOB (work with user jobs), WRKSBMJOB (work with submitted jobs), and WRKSBSJOB (work with subsystem jobs) were all rolled into one to create the UDTF JOB_INFO.

Wednesday, November 22, 2017

SQL View for information about Job Queues

using sql db2 for i view to get job queue information

Another of the new Views added to the Db2 for i as part of the new Technology Refreshes, 7.3 TR3 and 7.2 TR7, is one that lists all of the job queues on your IBM i.

The JOB_QUEUE_INFO View will return a row for each job queue. It is essentially the equivalent of the Work with Job Queue command, WRKJOBQ, and the Retrieve Job Queue Information API, QSPRJOBQ. There are over 60 columns of information I can get in my results, don't worry I am not going to list them all here. IBM does a great job of describing them all here, I will only describe the columns I use in my examples.

Friday, November 17, 2017

Last of 7.3 TR3 and 7.2 TR7 PTFs now available

last of ptfs for ibm i 7.3 tr3 and 7.2 tr 2 out now

I have been remiss in letting you know when the PTFs for parts of the latest Technology Refreshes, that were not released in October, became available.

The PTFs for the new RPG features were released November 6:

  • IBM i 7.2 TR7: SI66149
  • IBM i 7.3 TR3: Target release current SI66150
  • IBM i 7.3 TR3: Target release previous SI66153

More information about these PTFs can be found here.

A trial version of the new version of Rational Developer for i, RDi version 9.6, became available yesterday, Thursday November 15.

The full version will be available on November 21.

This page provides more information about the new version, including where to download it from.

That is it. All of the features and functions promised in the new TRs are now available to us.

Wednesday, November 15, 2017

Limit-ing the number of rows affected by SQL statement

using limit to give the number of results to return using sql

Included in the latest Technology Refresh PTFs, IBM i 7.3 TR3 and 7.2 TR7, is the Limit clause for SQL statements. At the time of my writing this post I cannot find a description of this clause within IBM's KnowledgeCenter, therefore, I have come up with my own:

The Limit clause will affect the number of results given by it. If I use Limit in a Select statement, I will only return the number of rows in the Limit clause. Limit does not replace the FETCH NEXT x ROWS, it does the same.

Below I will give examples of using Limit in Delete and Update statements, and also show how it can be used when defining a cursor in a RPG program.

Wednesday, November 8, 2017

Inserting more than one row at a time

sql insert to add more than 1 record at a time

Most of us who have been programming in RPG for many years are familiar with doing things at a record level. We read, write, update, and delete one record at a time. Moving to SQL allows us to do things a set of data at a time, more than one row/record. We can fetch multiple rows from a table/file, delete more than one row with a single statement, and we can add multiple rows/records to a table/file at once. You can still do row/record level access using SQL, but until you start thinking in sets you do not get to experience the speed of file I/O that you can with using multiple row/record statements.

In this post I am going to give two examples of performing multiple row inserts.

  1. Inserting more than one row/record at a time with just one statement with hard coded values
  2. Inserting just a few rows/records from one table to another, as you would do to create testing data

Wednesday, November 1, 2017

Program stack information via SQL

stack_info to get call stock information using sql

The guys at RZKH applied the latest Technology Refresh PTFs over the weekend, which means I can start playing with the new things. I am starting with the new table function STACK_INFO.

STACK_INFO allows me to use a Select statement to retrieve a row for each entry in the call stack for a specific thread in a job. It supports all types of call stacks: OPM, ILE, Java, PASE, and even the LIC stack. It gives me that same information I could get using the Display Job command, DSPJOB, or the Retrieve Call Stack API, QWVRCSTK. To be able to use this table function my profile must have *JOBCTL special authority, and *SERVICE to get the LIC stack entries.

This table function has two "parameters":

Friday, October 27, 2017

Technology Refresh PTFs out today

ibm i 7.3 tr3 and 7.2 tr7 out now

Today is the day the PTFs for IBM i 7.3 TR3 and 7.2 TR7 are released.

IBM i 7.3 TR3 IBM i 7.2 TR7
Base TR SF99727 level 3 SF99717 level 7
Db2 for i SF99703 level 7 SF99702 level 19

Yes, the Db2 for i is not included in the Base TR PTF.

IBM were a bit cheeky with the some of the other products they announced.

IBM i 7.3 TR3 IBM i 7.2 TR7
RDi November 21,2017
RPG "Available with PTFs delivered later in 2017"

When I finished publishing this post I am going to be asking the IBM i hosting service I use, RZKH, to download these PTFs.

Wednesday, October 25, 2017

Fetching more rows when using multiple row Fetch

getting more rows using a multiple row fetch

In an earlier post I gave an example of how to fill a "load all" subfile using a SQL multiple row Fetch and a data structure. A "load all" subfile can have a maximum of 9,999 records, therefore, what happens if I have more than 9,999 records/rows in my file/table?

I would need to load the next 9,999 records from the point where the first multiple row Fetch finished. Fortunately this is simple using OFFSET in the declaration of the cursor.

Just as a quick reminder: Why would I use a multiple row Fetch to get the data for a "load all" subfile. By using the multiple rows Fetch I would fetch 9,999 records in one file I/O. If I used the RPG read operation code I would need to perform 9,999 I/O operations.

Wednesday, October 18, 2017

Replacing parts of strings using regexp

regexp replace to replace parts of strings and fields

The fifth installment of my examples of the using regular expressions added to Db2 for i as part of IBM i 7.3 TR2 and 7.2 TR6 is going to be about REGEXP_REPLACE. I can already replace parts of strings using SQL's REPLACE and RPG's %SCANRPL, but REGEXP_REPLACE allows me to use regular expressions to find the pattern I wish to replace.

The syntax for the REGEXP_REPLACE is:

  REGEXP_REPLACE(source-string, search-pattern, replacement-string,
                 start-position, occurrence, flags)

Wednesday, October 11, 2017

Using Regexp to substring

regular expression, regexe, to substring

In this third installment of the regular expression functions added to Db2 for i is going to be about REGEXP_SUBSTR. The SQL SUBSTRING and RPG %SUBST built in function need a fixed start and number of positions. The REGEXP_SUBST allows me to use regular expressions patterns to find and substring the pattern I desire.

All of these Regular Expressions functions were added to Db2 for i as part of the 7.3 TR2 and 7.2 TR6 updates. Rather than repeat what I have in the earlier posts, I will refer you to them for more details.

The format for this function is:

  REGEXP_SUBSTR(source-string, search-pattern, starting-position,
                occurrence, flags, group)

Wednesday, October 4, 2017

More information about the new TRs

more links about ibmi 7.3 tr3 7.2 tr7

I saw this on Twitter this morning. Quote from Tim Rowe, business architect for application development on IBM i:

"We will keep updating RPG as long as we keep updating IBM i and we have a long road map"

A day after the announcement of the latest Technology Refreshes for IBM i 7.3 and 7.2 a few more links are coming out from various IBM sources which I consider worthy of sharing:

Tuesday, October 3, 2017

IBM i 7.3 TR3 and 7.2 TR7 announced

ibmi 7.3 tr3 and ibmi 7.2 tr 7 announced

In a blog post published this morning Steve Will, chief architect of IBM i, announced the new Technology Refreshes for IBM i 7.3, TR3, and 7.2, TR7. I am not going to give a long list of links to all the new and enhanced features and functions, the Enhancements pages, listed below, do that. What I will mention are the things that caught my eye.

As with earlier TRs there are many additions and changes to Db2 for i (SQL). These will keep me busy over the next few months writing about them for this blog.

Monday, October 2, 2017

New Technology Refreshes information found

IBMi 7.3 TR3 and 7.2 TR7 announced by IBM

Ahead of the announcement of the new Technology Refreshes I found the following pages listing some of the information of what is included within them.

I assume that the official announcement will be made tomorrow at the COMMON Fall conference. When it is made I will write another post including all the relevant information and links to the various articles and pages.

Thursday, September 28, 2017

Expecting new Technology Refreshes next week

With the COMMON user group Fall conference happening next week I am sure that I am not the only person expecting an announcement about new Technology Refreshes for IBM i releases 7.3 and 7.2.

Alas, I do not have any "inside information" on what will be included in these TRs. But I have found "place holder" pages, which at the time of publication of this post are empty.

I have to admit I am geeking out waiting to see what new features are going to be released, and then come up with reasons how I can use them.

If you are interested in what will be in these TRs keeping checking this blog, because as soon as I find any information about them I will share it here, on RPGPGM.COM.

Wednesday, September 27, 2017

Index Advisor helps performance

using index advisor build sql indexes to speed up your sql

As we all start using more SQL to get data from our tables and files, there are things we can do to ensure that our programs are not that "monster" that takes down a system. One of them is to ensure we use the best access path to get that data. Rather than hunt for the "best" logical file/index to match our key, we should always build our Selects, etc, over the physical file/table and allow the Db2 for i engine to find the best access path for us. Personally, I think that is remarkable that the Db2 for i engine will find the best logical file/index to use. If there is not an index that matches what I need, it builds a temporary access path. This temporary access will be reused, within the same job, whenever I run that SQL statement. You may have noticed if you execute a complex SQL statement many time it will get faster the more times it is run, within the same job.

Building temporary access paths can still result in a "monster" being created. The best way to improve the performance of the "monster" is to make sure that there are indexes that match what I need. Rather than having to identify all the indexes myself, I can use Index Advisor and have Db2 for i recommend the index I should build.

Wednesday, September 20, 2017

REGEXP for searching in strings

regexe instr to find instances of character in search string

Continuing my posts about the regular expressions introduced as part of IBM i Technical Refreshes to 7.2, TR6, and 7.3, TR2, I have struggled to come up with a good brief description that would suffice for this post's title. IBM's documentation describes this function as:

returns the starting position or the position after the end of the matched substring

After playing with this function for awhile I would describe it as: "returns the position where the searched pattern occurs, whether first or nth occurrence, in the source string". And I would have to admit that description is better than IBM's, but it not a good description either. It is probably best I start explaining how to use this function and you will be able to decide how you would describe it.

The format for this function is as follows:

Monday, September 18, 2017

Db2 for i poster

Scott Forstie, Db2 of i Team Leader, posted on twitter:

Are you bummed out that you don't have #Db2fori posters? Cheer up, you can have the pdf

The link to download the poster is here.

I have already printed mine and stuck it on the wall of my office.

Thursday, September 14, 2017

Writing to a User Space, update

get data from user space using pointer

After publishing my previous post several people contacted me regarding the method I had used to write data to and retrieve data from a User Space. In my example code I have used the QUSCHGUS API to write, and the QUSRTVUS API to retrieve. The messages asked why I had taken that approach versus using a pointer?

Either approach is valid, as they both perform the same function. But in the age of modern RPG code using pointers can be considered the better way to go. As I was remiss in my previous post I intend to make amends with this one.

One thing to be aware of if you are using your own User Spaces, rather than ones created by a list API, is your own does not contain the header information for the User Space. I will need to determine the equivalent data myself.

Wednesday, September 13, 2017

Writing to a User Space

using api to write to user space

I have written in the past about using User Spaces with List APIs. These earlier examples showed how I can retrieve data from a User Space, but how would I write or update my own User Space?

A User Space is like a data area, only bigger. While the maximum size of a data area is 2,000 bytes, a User Space can be up to 16,776,704 bytes. And I must use APIs to put and retrieve data in and out of a User Space.

Why would I use a User Space? They can be anything I want them to be.

  • Passing large number of parameters between programs
  • Share data between more than more than one program, I could use it to share data between more than one procedure
  • Snapshot of a file record (or table row)
  • I know of one software vendor that stores SQL statements in User Spaces that are later executed

Wednesday, September 6, 2017

REGEXP for count

regexe expression count used for counting characters in a string

With the latest IBM i Technical Refreshes to 7.2, TR6, and 7.3, TR2, a number regular expressions functions were added to Db2 for i (SQL). In this post I am going to describe the REGEXP_COUNT function.

Regular readers of this blog will know that I have already written about the REGEXP_LIKE. Rather than repeat a lot of the things I wrote about in the post I am just going to refer you to it at various time in this one.

The format for this function is as follows:

  REGEXP_COUNT(source-string, search-pattern, start-position, 
               regexp-flags)

Tuesday, September 5, 2017

Glimpses to the future releases of IBM i

timeline of current and future releases of ibmi

Whenever I have seen this image in IBM's presentations I have wanted to get a copy to post in this blog. Why? It shows IBM's plans for the life of the current releases and the timeline for future releases of the IBM i operating system.

I am not so naive to think these dates are fixed, we all realize that the release dates of future releases, and the end of support for current releases, can change. The exception is IBM i 7.1, its end of support is set for April 30, 2018.

I still expect Technology Refreshes to be released twice a year. I do expect a new TR to be announced at the COMMON Fall conference, next month.

Wednesday, August 30, 2017

Using derived columns to sort files

using sql views and derived columns to sort the data in the results

My employer uses "descriptive" part numbers for the parts they manufacturer. Without going into too much information, "descriptive" part numbers contain information about the item within the part number, it could contain characters that describe the material used to make it and/or characters to denote its end use. I am often asked to create a report for certain collections of parts. These "collections" are identified by a range of characters in certain positions of the part number, and the sort order desired may not be in a simple sort order, I will explain what I mean by that later.

The "descriptive" part numbers I will be using in these example consists of three parts:

Wednesday, August 23, 2017

REGEXP like added to SQL

regular expression regex like

I can remember many years ago having a conversation with a non-AS400 (yes, it was that long ago) person about the programming environment we worked with. He scoffed at the AS400 stating he could never work on it as it did not support Regular Expressions. I am not going to describe in too much detail what Regular Expressions, sometimes called regex or regexp, are as others have done a good job doing that. To give a very simple explanation: it is a sequence of characters that define a search pattern, which is then used to find or find and replace parts of strings. If you are interested in learning more check the Wikipedia page about regular expressions here.

Within the Db2 for i PTFs that accompanied IBM i TR2 and 7.2 TR6 a number of regular expressions functions were introduced to SQL. I am not going to describe them all in one post, as it would be too long, as I feel they merit their own posts. To start this post is about the REGEXP_LIKE. I am just going to give a simple overview of how to use this, if you want more details or want to learn how to make complex statements I suggest you visit the page on IBM's KnowledgeCenter, link at the bottom of this post, or use your favorite search engine to search for "REGEXP_LIKE". The posts about REGEXP_LIKE for Oracle's PL/SQL I found helpful.

Tuesday, August 22, 2017

Finding the minimum value across several columns

minimum value of more than one column

Sometimes I find something that I think is cool, it may be something small, like this, but I feel it is worth sharing.

I was asked about how to find the minimum value from multiple columns. SQL offers us the MIN function for returning the smallest value from a column. I can use that to get the smallest value from multiple columns, and the smallest value from all of those columns.

I have a file with three fields containing the following numbers:

Wednesday, August 16, 2017

Sometimes a List API is better than the SQL View

use list api and user space to get data about spool files

Sometimes it is just "better" to use a list API than it is to use the SQL View that "replaced" it. This week has given me an example of this. I was asked to create a program that would allow users to email themselves reports. I knew I would have to present the users with a list of spool files from which they could select the one they would want to email.

"No problem", I thought, "I will just use the Output Queue Entries SQL View."

I created a View over the OUTPUT_QUEUE_ENTIRES View, and then created a program using a multiple row fetch to get more than one row from the View. In the "test" IBM i partition the program ran a bit slow the first time I ran it, then ran faster each time after as the DB2 query engine created a temporary access path for me. Being a "test" partition there are not many output queues or spool files when compared to the "live" partition. I moved the objects I created to "live" and called the program there. It took more than 20 minutes to load the subfile with the list of user's spool files. With several hundred users and a very large number of spool files (many thousands, does anyone ever delete spool files?) in the "live" partition it was searching through all of them to find the few spool files belonging to me. The time taken made this method unacceptable. Back to the "drawing board".

Tuesday, August 15, 2017

We are not impostors

value of self taught programmers

Last week I found a wonderful article by Roger Pence in IBM Systems magazine.

It started with the title "Your are not an impostor", and continued with the subheading "Self-taught RPG programmers bring valuable skills to enterprise programming teams". As one of those self-taught programmers it caught my eye, and he made some good points about what would make me feel like an impostor, and what I can do to overcome it. It confirmed my opinion that there is a place in every organization for the self-taught and motivated programmers, alongside the formally educated graduates.

You can read the article on IBM Systems online magazine here.

If, like me, that site does not always load, I have placed a copy here (PDF file).

Alas, when I tried to go to the URL given for "The Impostor's Handbook: A primer for self-taught programmers", bit.ly/2ivK5lt, it went to a 404 page not found error page. If you have the correct URL for this page I would be grateful if you would post it in the comments section below.

Wednesday, August 9, 2017

Resetting the value of a variable

using reset operation code to set value back to initialization value

One of my colleagues was reading the post about initializing variables when defining them and asked a question that I had not considered when writing that post.

"What if I change the value in the variable," he asked, "can I change it back to its original value?"

Fortunately there is in RPG an operation code that will do this. The RESET operation resets the value of any variable or record format back to the value it was at the initialization phase of the program. It is not new operation, I have used it for as long as I have programmed on the AS400 - IBM i.

Thursday, August 3, 2017

Types of saves

types of saves

I have Steve Pitcher to thank for sharing this image so that I could post it here. It is from IBM's KnowledgeCenter showing the different types of saves that can be performed using the various save commands, on the right side, and the options on the SAVE menu, on the left.

list of the types of saves and what they save

Wednesday, August 2, 2017

Checking Software License expiration dates using SQL

checking softwatre license expiration dates using sql

Included in the latest Technical Refreshes for IBM i 7.3 and 7.2 is a SQL View and Procedure to help us monitor the licenses of the software on our IBM i.

IBM uses Software License Keys with certain IBM i products to ensure that I have paid the appropriate license fees to use these products on an IBM i. I know of several third party IBM i software vendors who also use this method to stop the unauthorized copying of their software to unlicensed servers. It is all standard stuff to prevent the distribution of unauthorized software.

Thursday, July 27, 2017

IBM i is more than an AS400

why ibm i  is not as400

I frequently get messages asking why does IBM call it IBM i when it is just an AS/400, or even System/38, by another name. Talking to other authors and IBM-ers I find that I am not alone to receive communications like this.

Steve Will, chief architect for IBM i, has felt compelled to dispel this fallacy. Earlier this month he made an online presentation, sponsored by HelpSystems, explaining how IBM i differs and is a lot more than AS/400. The video can be watched on the sponsor's website here.

The video lasts just over an hour, and, in my opinion, is worth watching to learn the evolution of the operating system to become the thoroughly modern IBM i.

Wednesday, July 26, 2017

Position cursor into field on screens

position cursor on a display file record format

The germ for this post's subject came from a two part question I was asked:

  1. How can I know which field the cursor is in when Enter is pressed?
  2. How can I position a cursor on some field without using indicators? No indicators are available in that very old screen.

Fortunately there are ways to do this by adding a few keywords and fields to the display file's DDS. In these examples I am going to use one display file with three screens, each showing a way to do what has been asked, and the RPG code needed too (and I am going to cheat on the second part).

Monday, July 24, 2017

DB2 name change

db2 family renamed

Last week at OCEAN User Group's Technical Conference I attended a couple of sessions by Scott Forstie, IBM's Business Architect for DB2 for i. I noticed in his presentation he had used "Db2" rather than "DB2".

When I asked him about this he explained that last week (Monday July 17, 2017) the DB2 family had been officially renamed "Db2". The Db2 family is:

  • Db2 for i
  • Db2 for z/OS (mainframe)
  • Db2 for Linux, Unix and Windows

I found that that Db2 page on IBM's website has already been changed, see here.

Now I need to remember to use the new name when writing about it in this blog.

Wednesday, July 19, 2017

Finding the number of times a character is in a field using SQL

using sql to count the number of times a character is in a field

I needed to determine, first, if a certain character was present in a file's field, and then how many times it occurs. Using RPG's file input I would not be able to do this without reading and checking every record's field. Could this be done easier in SQL? Yes.

I am going to give examples of various ways of being able to tell if a certain character is in a field in a file, using increasing complex methods I found. At first I will just give the SQL Select statement for each, and at the end the SQLRPGLE code I created to do this in a program.

Wednesday, July 12, 2017

Updating one row at a time using SQL

update current row/record using sql

I have been asked to write some more about using SQL embedded in RPG programs. One question I have been asked by several people is how to update a row from a file that had been previously Fetched, just like READ and UPDATE using RPG's native database access. I thought it would be useful to show examples using both RPG native database access and SQL.

First I need a file, as this is for example purposes it very simple, just two fields and one key field:

Wednesday, July 5, 2017

SQL null byte map as a 2-dimensional array

using a 2 dimensional array for sql null byte map in rpg

Nulls are something we are all going to have to get use to handling as we move from DDS files to SQL tables, or start using data from DB2 of i or other non-IBM i databases. Recently I was working on a project with a SQL Table with more than 300 columns. If there was no data in a particular field it was null. If you think about it null is a better alternative than blank or zero as those values could be valid values for the contents of that column.

I have written about handling null in SQL in an earlier post, but in this scenario the method I described to define the null byte map was not practical. I am not going to list 300+ subfields in a data structure array for the null indicators for the matching data structure array I would be moving the SQL Table's data into.

Thursday, June 29, 2017

RPGPGM.COM four years old

Wow! I find it hard to believe that I have been writing this blog for four years.

We have been fortunate to share this journey in an exciting time for the IBM i operating system. In all my years of working with this operating system, and is predecessors, there seems to be so much more functionality added every year than there was in the past. During these four years I have written almost 400 posts and pages, trying to share my enthusiasm for IBM i and what it can do.

Wednesday, June 28, 2017

Using relative record number in SQL

sql rrn function to retrieve relative record number from file

I have written about using the Relative Record Number, RRN, in RPG and said that I could not think of a reason why I would ever need to use RRN in one of my programs. Well, I have to take that statement back as I recently found a really good reason for using it. I needed a quick way to retrieve the last record from several different "flat files", and check if it started with the characters "END:". I could do it in RPG, but what is the fun with doing that when I could do the same using SQL?

What is my definition of what a "flat file" is? See here.

I am not describe these "flat files" as it is not really relevant to what I am going to show. I will say that I had a variety of these files from several sources, the layout of the contents is different, and the maximum record lengths are different to. They all have the following in common:

Wednesday, June 21, 2017

Happy birthday AS400

happy 29th birthday as400

Today is the 29th anniversary of the launch of the AS400. It was June 21, 1988, when IBM announced their latest midsize server. You can see a video recording of the announcement here.

At its launch the AS400 server and its OS400 operating were ground breaking.

Tuesday, June 20, 2017

IBM VP reaffirms commitment to RPG and IBM i

ibm vp reaffirms future of rpg

In my opinion it is always good when IBM publishes something about their future commitment to PowerSystems servers, IBM i operating system, and the RPG programming language. Steve Pitcher sent me a copy of a letter, from June 9 2017, by Steve Sibley, the Vice President of IBM Cognitive Systems, that hits this trifecta.

At the start of his letter Steve leaves us in no doubt IBM's regard for RPG:

Wednesday, June 14, 2017

SQL Views for Authorization Lists

views for authorization lists to view user authorities and objects belonging to them

Many companies that use IBM i secure their objects using Authorization Lists. The Authorization List is its own object type, *AUTL. If you have not used or heard of them before I found this good description of them in IBM's KnowledgeCenter.

Like a group profile, an authorization list allows you to group objects with similar security requirements and associate the group with a list of users and user authorities.

Authorization lists provide an efficient way to manage the authority to similar objects on the system and aid in the recovery of security information.

I am not going to discuss in this post how to create Authorization Lists, how to add objects to the lists, or anything similar. I am just going to describe two new SQL Views that were added in the latest IBM i Technical Refreshes, 7.3 TR2 and 7.2 TR6:

Tuesday, June 13, 2017

Using SQL to load data into a test instance

using 3 part sql path to update test files

Today's post is written by David Taylor.

From time to time, we need to load data from a production system to a development system. To make the process more complex, the data often resides in more than one file. Using SQL, we finally found a way to load the data into multiple files from the same cursor. We include in the SELECT clause any fields from any of the files in the data set to refine the cursor. Once we have the right data set, we can use the final version for the multiple file loads. Keep in mind, we need the data from all three files for the process test to work correctly. We need not just any set of records; we need the data that matches the relationships and limits defined in the cursor. In the samples each file ends with a letter. That same letter is the first character for the field names. For example, FILEA has fields like AKEYFLD1, AFIELD1, and so forth.

Wednesday, June 7, 2017

Repositioning file pointer after EOF in CL

stop end of file from happening in a cl program

I am always grateful for the feedback I receive from you, the readers of this web site. There are many times you mention ways of doing things that either did not think of or was unaware of. Today is a good example. I have Andrew Norton to thank for bringing to my attention a command that is a better solution for the scenario I described in the previous version of this post.

Those of us who have programmed in CL have found that when read a file when the end of file has been encountered I thought there was no way I could use the file again in the same CL program. In RPG I could use the Set Lower Limits operation, SETLL operation to reposition the file pointer. But in CL I was stuck.

Tuesday, June 6, 2017

Display files in CL

how to code dspf in clp or clle

I could give this a subtitle: "How to put a bad date in a date field", but this is not the reason of this post, just an accidental discovery. Having previously written about how to use database files in a CL program I have to mention using display files in CL.

You cannot use CL for anything as complex as a subfile. But you can for what I call "report submission screens", you know the type: a screen is presented to the user, they enter the selection criteria they desire and press Enter, and a program is submitted to batch to generate either a paper report or an email attachment. My example display file has two record formats, and is very simple:

Wednesday, May 31, 2017

Using OPNQRYF for file I/O in CL

opnqryf and cpyfrmqry examples

In my last post about the various way I could perform file I/O in CL code I deliberately omitted mention of the Open Query file command, OPNQRYF, as I wanted to cover it in its own post. I am sure everyone who has been programming on the IBM i or its predecessors have encountered and used this command.

I used it too, but not anymore. In the days before embedded SQL become efficient and fast within RPG programs I would often use OPNQRYF. This command allowed me to select fields and records, sort the data in a different order to the files' access paths, etc. thereby reducing the amount of checking and processing the following RPG program would need to perform. But it was always slow as it used the Classic Query Engine, CQE, to access the IBM i's database. In version 7.2 OPNQRYF was moved from using CQE to the faster and more efficient, SQL Query Engine, SQE. While this did improve its speed and performance it is still more efficient, and in my opinion easier and clearer, to do the equivalent using SQL embedded in RPG.

I am going to be giving examples of how to use OPNQRYF to do some of the things I talked about in yesterday's post.

Tuesday, May 30, 2017

Read, write, and update a file in CL

equivalent of rpg read, write, update, setll, chain in cl

Someone messaged me telling me that there were not, in their opinion, many good articles about CL programming, and asked if I would write some. Personally I think the majority of IBM i developers under utilize this language, keeping it just as a simple control language. I use CL programs and procedures a lot, and wrote about my thoughts on the need to modernize your CL in a post last year.

Where to start? Coincidentally I received an email from another asking about reading, writing, and updating a file just using CL. Let's start with data base access. In this post I will use the RPG file operation's name and then describe how to do the equivalent in CL.

Wednesday, May 24, 2017

Determining the screen size

determine screen size if it is *ds3 24x80 or *ds4 27x132

Most of us are aware that TN5250 sessions (displays) on the IBM i can be configured in two sizes:

  • 24 x 80 commonly known as *DS3
  • 27 x 132 commonly known as *DS4

Most of the time IBM i operating system handles this for us, only displaying "wide" screens when my session is *DS4 and the information to be displayed is wider than 80 columns.

Tuesday, May 23, 2017

My favorite CL shortcuts

using cl shortcuts rather than long hand code

If I am going to write and give examples of programming in CL I need to start by explaining some of the CL language shortcuts I use. Perhaps "shortcuts" are not the right word, maybe "shorthand". Oh well, I am sure you will work out what I mean as you read this.

I use these shortcuts extensively in my CL code as for a couple of reasons:

  1. I think it is easier for someone else to understand the code I have written.
  2. It is just quicker to write, the faster I can write code the more I can get done.

I am going to start with my favorite three.