Wednesday, May 23, 2018

How to SQL table's long names in RPG program

using long column names from sql table

The seed for this post came from a conversation I had with one of my work colleagues. He complained that even though he could give the columns (fields) in a DDL (SQL) tables names longer than ten characters he found that it was hard to use these names in programs, especially those using printer or display files.

He is not the first person who had made this observation to me. Over the time I have written this web site others have made the same observation.

Let me show what I shared with him so that he could easily overcome this perceived shortcoming.

Monday, May 21, 2018

New Technical Refreshes coming in September?

While poking around in IBM's developerWorks wiki I noticed that pages for the next Technical Refreshes for IBM i 7.3 and 7.2 have already been added.

Wednesday, May 16, 2018

Deleting the current row when using cursor

delete current sql cursor row

In a previous post I wrote about how to perform an update of the current row/record when using a SQL cursor in a RPG program. I have been asked what could be considered the obvious follow on question: How I delete the current row/record when using a SQL cursor?

Fortunately it is as simple as the Update process. All I need to do is...

  • Define the cursor
  • Open the cursor
  • Fetch from the cursor
  • Delete
  • Close the cursor

In its simplest form a program could just be like this:

Wednesday, May 9, 2018

Creating a XML file

create xml file using sql and rpg

In an earlier post I described how to retrieve information from a XML file. This left me thinking how do I create a XML file just using native IBM i functionality?

I wanted to keep my program simple, K.I.S.S. After a good deal of research I have an example program of 28 lines, which takes data from a DDS file or DDL table, converts the data to XML, and outputs the data to a file in the IFS. I need acknowledge Birgitta Hauser here. She is someone whose work I study, and articles I always read. Some of the code in this example is based upon a series of example procedures and programs she posted on Github. I will post a link to her Github at the bottom of this post along with the other reference links.

Before I continue I want to describe the parts of a XML document so you understand when I call an element something what it is. Below is an example of a XML document:

Wednesday, May 2, 2018

Update the current row when using cursor

updating current SQL cursor

I was asked how to update the current row if I am "reading" a file/table using a SQL cursor. At first this question took me aback, it was not that I did not know how to do it, but more a question of why?

What I consider to be one of the biggest benefits of using SQL to process (fetch, delete, and update) data from your database in sets, "chunks" of data. If I need to process a hundred rows/records of data why get each one individually? as that would be a hundred I/O operations. I can get all hundred rows in one I/O operation, thereby, reducing the amount of time and system resources needed to get the data.

I spent some time thinking of a scenario where I might want to fetch rows/records one at a time. I came up with the scenario of an interface. One program would add data to a file, and another would retrieve the data from the file and process the transaction. My example will be that second program.

Wednesday, April 25, 2018

Easy to create shortcut commands

creating your own shortcut commands

After my post about how to create your own commands I received messages from readers giving me examples of how they have created their own shortcut commands, to do the same as the standard IBM i command but shorter. In one example the command SP did the same as WRKSPLF.

I don't think many people know of proxy commands. These allow me to create shortcut commands of the regular IBM i commands without any programming. You can even make proxies of proxy commands, although I cannot think of a reason why I would ever think to do so.

In this example I am going to create a proxy command, SP, for the Work with Spooled Files command, WRKSPLF.

Wednesday, April 18, 2018

Sending your own program created messages to the error subfile

filling the error subfile, errsfl, with program generated message

I have previously written about using the error subfile to display error messages, rather than the message subfile. Personally I like using the error subfile as it is another case of allowing the operating system to do the work for me.

Since publishing that article I have received messages asking how can people create their own error message text, in the RPG program, that is displayed in the error subfile.

Fortunately there is a simple way to do this that will work in either RPG in CL.

No matter which language I use the display file can be the same. Here is the display file I will be using in this example.

Wednesday, April 11, 2018

Built in function to return name of procedure

%proc built in function to return procedure name

Until the last round of Technical Refreshes, IBM i 7.2 TR7 and 7.3 TR3, I could not find a way to retrieve the name of the procedure that I was in. The program data structure enticed me with the *PROC keyword. This was not the procedure's name, but rather either the program's or module's name.

01  DPgmDs           SDS                  qualified
02  D ProcName          *PROC

01  dcl-ds PgmDs psds qualified ;
02    ProcName *proc ;
03  end-ds ;

Fortunately these two Technical Refreshes introduced a new built in function, BiF, to returns the name of the current procedure: %PROC

This example is of what I call a closed subprocedure and how this new BiF works.

Wednesday, April 4, 2018

Handling nulls in CL

in a cl program how to handle a null value from a file

I have written previously about how to handle nulls values in RPG and in SQL, but until this week I had never had a problem with null in a CL program.

The CL program was "reading" a SQL table, where some columns/fields were null.

    ORDNBR      ORDSTS       ORDVALUE
01 PO18087       P            145.67
02 PO18556       C             15.36
03 PO18223       -                  -
04 PO18777       D                  -

The CL program is simple.

Happy patron saint's day!

Today is the saint's day of Saint Isidore of Seville.

In 1997 Pope John Paul II, of the Catholic Church, decided that computers and the internet needed a patron saint. His choice was St Isidore of Seville, "the last of the great scholars of the ancient world".

I hope you all have a good St Isidore Seville day. But if you need divine intervention for your computer problem this is the day could be the right day to ask for divine inspiration.

To learn more about St Isidore of Seville see here.

I need to say "St Isidore of Seville" rather than just "St Isidore" as there is another saint of the same name, "St Isidore the farmer", who is the patron saint of farm workers and the communities they live in.

Wednesday, March 28, 2018

Processing simple XML using XML-INTO

xml using rpg xml-into

I had received notification from a supplier that they were changing the order file they send my employer. Previously they sent a Microsoft Excel spreadsheet, starting the end of this month it would now be a XML file. In the past if I need to convert a XML into data in an IBM i file I would use the EDI application to do the conversion. As the XML is so simple I decided to process its contents in my own RPG program.

The operation code XML-INTO takes information from elements in the XML document and, in my example, places them into data structure subfields. I believe that this operation code has been around since V5R4, I am just a late comer to using it.

The XML document I will be using in these (very) simple examples contains a (very short) list of names and the city and state the person is in. The proposed format of the XML file the supplier sending me is very simple too, no need for anything complicated.

Wednesday, March 21, 2018

Handling divided by zero in SQL

coping with divide by 0 in sql update

One of the messages that annoys me the most is "Attempt to divide by zero". I am sure every programmer, no matter, what language they use, has encountered this at least once. It frustrates me is there is not much we can do to fix the error once it has happened. It annoys me that this can be handled very well within the program to prevent the error.

Before I get started I know there are a couple of different ways the division calculation can be described, I did check. I will be using the following:

   Result = Dividend / Divisor 

It is the Divisor that must be zero for this error to occur, as:

Monday, March 19, 2018

Creating a list of journaled files, improved

finding which files are journaled using sql object-statistics

In my previous post about making a list of files in a library that are journaled I stated that I could not find this information in any Db2 for i view or table function.

Krister Karlsson brought to my attention that the information is available in the OBJECT_STATISTICS table function. Looking at the documentation from IBM it would appear to have been added in the previous round of Technology Refreshes.

Rather than using the Display Object Description command, DSPOBJD, I can get the information I desire from a simple Select statement.

Friday, March 16, 2018

New Technical Refreshes released today

ibm i 7.3 tr4 7.2 tr8 made available

The latest IBM i Technical Refreshes for version 7.2 and 7.3, that were announced in February, were released earlier today.

The information about the PTFs can be found here:

One thing to be aware of is that these probably do not contain the PTFs for the latest changes to RPG, these will be released on Monday March 19. These PTFs are listed here.

Wednesday, March 14, 2018

Creating a list of journaled files

make list of files being journaled

"How do we know what files are journaled in library x?" one of the programmers asked me. A straight forward question, but where to find the information?

I could not find reference to journals in either the Db2 for i Views SYSTABLES or SYSTABLESTAT.

I could find a fields for journal information using the Display File Description command, DSPFD, but only in the types of information that I can display or print, not in those that create an outfile.

I might have used an API, QUSLOBJ with format type OBJD0500 or QUSROBJD format OBJD0400, if I only wanted to know about one file, but I want a "list" of files.

Wednesday, March 7, 2018

Creating your own commands, part 2

creating command validation program, help for the commands, and return value to calling program

In the last post, Creating your own commands, part 1, I wrote about a lot of the basics in creating your own IBM i commands. In this post I am going to show how to write a validation program for the command, how to add help, and return a value from a command.

I am going to create a validation program for the command with the file and library I showed in the previous post.

                   File & library (TESTCMD)

Type choices, press Enter.                                  

File . . . . . . . . . . . . . .                Name
  Library  . . . . . . . . . . .     *LIBL      Name, *LIBL

My validation program will check if the file entered exists. Like the program the command calls the validation program is passed one parameter for each parameter the command has. This command will pass a parameter, 20 characters long, that contains the file and library name.

Wednesday, February 28, 2018

Creating your own commands, part 1

creating your own ibm i commands part 1

We have been able to create our own commands, I believe, since the AS400 was first launched. Why would I want to create my own commands?

I create them just for ease of use. In my opinion it is easier to remember:

ITTOOLS/ITTOOLS

Than this:

CALL ITTOOLS/ITT001

Saturday, February 24, 2018

Searching RPGPGM.COM

I was surprised this week to have received communications asking how to search this website for various words and phrases. I am writing this post so that I can refer people to it, if I am asked this question again.

I can think of three easy ways to find information on this site:

 

Wednesday, February 21, 2018

Having the source for suggested indexes created for you

using sql to created advised indexes for you

Last year I wrote about using the Db2 for i index advisor to suggest indexes that would improve the performance of your SQL statements. I showed how you could copy data from its results to use to create for SQL statements to create the suggested Indexes. Recently I found a Db2 for i Procedure that makes this so much easier as it will create source members for the suggested Indexes.

I can use the Index Advisor View to see what Indexes are suggested for the FILE1 in the library MYLIB.

01  SELECT SYS_DNAME AS LIBRARY,
02           SYS_TNAME AS TABLE,
03           FIRSTADV AS FIRST_ADVISED,
04           TIMESADV AS NBR_TIMES,
05           KEYSADV AS ADVISED_KEYS
06     FROM QSYS2.SYSIXADV
07    WHERE SYS_DNAME = 'MYLIB'
08      AND SYS_TNAME = 'FILE1'

Wednesday, February 14, 2018

Making IP connections easier between IBM i partitions

ddm server authority entries

I have mentioned in previous posts regarding using DDM files and configuring remote database connections that there is a difference in using SNA and IP for the connection protocol.

When you use SNA to connect everything performed on the remote IBM i is performed as the user profile QUSER. It is impossible to discriminate between valid requests for the files/tables and others trying to get at data they should not be authorized to.

CRTDDMF FILE(QTEMP/DDMFILE1)
          RMTFILE(OTHERLIB/RMTFILE)
          RMTLOCNAME(SYSTEM2)

Tuesday, February 13, 2018

IBM i 7.3 TR4 and 7.2 TR8 announced

ibm i 7.3 tr4 and ibm i 7.2 tr 8 announced

I find it interesting that the announcements of new Technical Refreshes are always made on a Tuesday. After finding empty pages for the new TRs last week it was obvious that the announcement would happen soon. What does the latest announcement contain:

The biggest announcement are the new Power9 chips are coming.

Wednesday, February 7, 2018

Configuring database connection between two IBM i

configuring database connection to another ibm i

In my day job I work in an environment where there are multiple PowerSystems servers, and many of them have multiple IBM i partitions. I was tasked to gather information from most of the partitions into one location. I decided to use SQL to fetch the information from the various sources.

I used the following two methods to connect to the other partitions using the partition name:

CONNECT TO SYSTEM2
  or  
SELECT * FROM SYSTEM2.OTHERLIB.RMTFILE

When debugging my program I found that when I tried to connect to some of the partitions I received a SQLCOD value of -950 and the following message:

Tuesday, February 6, 2018

New Technology Refreshes are coming

are ibm i 7.3 tr4 and 7.2 tr8 coming soon?

In the past the twice yearly Technology Refreshes updates for the current releases of IBM i were announced in the middle of February and the beginning of October, to coincide with Common USA events.

While Common does not appear to have a Spring conference this year (at the time I am writing this post their website is unavailable), the announcement of IBM i 7.3 TR4 and 7.2 TR8 could still be on track for this month as I have found empty pages for them in IBM developerWork's website.

Wednesday, January 31, 2018

Getting the library list from a Job Description

using api to get library list from jobd

The germ for this post came from this question:

How can get the Library List from a Job Description?

I can use the DSPJOBD command to see the Job Description's library list, alas I can only display or print it. This does not make it easy for me to use in a program.

DSPJOBD JOBD(MYLIB/MYJOBD)


Display Job Description
  Job description:  MYJOBD         Library:  MYLIB

  Initial library list:
    Sequence
     number   Library
        10    QTEMP
        20    MYLIB
        30    ANOTHERLIB
        40    QGPL

Wednesday, January 24, 2018

SQL Views to list SQL programs and the statements within them

use sql views to find out about programs containing sql

I found these two Views when going through the list of Db2 for i Views on Scott Forstie's Db2 for i poster (if you have not printed this out and stuck it to your wall do so now!). Both contain information about programs containing SQL statements:

  1. SYSPROGRAMSTAT:  One row for each program that contains a SQL statement
  2. SYSPROGRAMSTMTSTAT:  One for each SQL statement in a program

There are a couple for gotchas I found working with these Views, which I was disappointed by, I will mention these shortcomings when I discuss the View in detail below.

Wednesday, January 17, 2018

Using RPG data structures with SQL insert and update

rpg data structures with sql inserts and updates

As I can fetch rows using SQL from a table, or file, into a data structure. I was asked:

Can I insert or update rows using a RPG data structure?

The answer is a definite "Yes".

In these examples I am going to be inserting and updating the following table:

01  CREATE TABLE QTEMP.TABLE1 (
02    COLUMN1 CHAR(10) NOT NULL,
03    COLUMN2 DECIMAL(11,2),
04    COLUMN3 DATE,
05    COLUMN4 TIMESTAMP
06  ) ;

Wednesday, January 10, 2018

Displaying more than one subfile at a time

more than 1 subfile on a screen

I decided to write the post after being asked if I had an example of having two subfiles on the same screen. I did a quick search using Google and was unable to find what I considered a good example I felt comfortable sharing.

In this example I am going to show two subfiles horizontally (i.e. one on top of the other). I could have shown them vertically (next to one another), or even had more than two. The principals I show here can easily be adapted to fit either of those other scenarios. The most important thing to remember is that each set of subfile and subfile control record format cannot overlap another.

To reduce the size of the display file source code, shown here, I have removed all unnecessary color and display attributes codes that I use in the screens I build. I am going to show my display source in multiple parts to make it easier to understand what each record format is for. Let me start with the file level keywords.

Wednesday, January 3, 2018

Determining length of a string using SQL

calculating length of data within a column

This is just a quickie in response to a question I was asked: How can I determine the length of string within a column using SQL?

This is very simple as there is Db2 for i scalar function to do this LENGTH. But before I do that let me explain why using this scalar function is simpler than doing the same using RPG.

I have a simple DDL table of just one column. There is no "NOT NULL", therefore, this column can be null.

CREATE OR REPLACE TABLE QTEMP.TESTFILE (
  FIELD1 VARCHAR(80)
) ;

And it contains the following data:

Monday, January 1, 2018

Welcome to 2018

At the start of a new year I am always excited about what it will bring, and grateful what the old year brought.

The past few years have been a really exciting time to be involved with the IBM i world. In my opinion the twice yearly Technical Refreshes has allowed IBM to bring us new things without us having to go the through upgrading to a new release. My favorites of last year's, 2017, introductions were additions to Db2 for i:

What were your favorites of the 2017 introductions?

If you need a reminder check these links: