Thursday, October 10, 2019

Additions to RPG in new TRs

rpg enhancements to ibm i 7.4 tr1 and 7.3 tr7

On Tuesday I wrote about the new additions to Db2 for i in the latest Technology Refreshes, IBM i 7.2 TR1 and 7.3 TR7. Today I bring you the additions to the RPG programming language.

Barbara Morris, lead developer for the RPG compilers at IBM, published a blog in the RPG Cafe part of the IBM Community website. In this blog she introduces us to the three additions coming with these TRs:

Wednesday, October 9, 2019

Putting multiple SQL Selects together to make one row

multiple sql select statements within 1 select statement to make a single row

I was working with one of the Tech Support guys at work putting together bits of information into a one row of data that would allow him to display information on any IBM i partition. This is not something I had done before, and after a little while "playing" I had a statement that put bits of information from different sources together into one row.

I am sure we are familiar with using a Select to get information from a table/file, or perhaps join tables/files together, to get the results. But they are always related, with columns/fields that can be used to link the tables/files together. In this scenario I am running several independent Select statements, each producing one row of results, I want to combine into one. The information want is:

  1. Partition name
  2. Date and time
  3. Default CCSID, from system value
  4. Number of spool files in QEZJOBLOG output queue
  5. Model number
  6. Release of IBM i

Tuesday, October 8, 2019

Db2 enhancements in IBM i 7.4 TR1 and 7.3 TR7

ibmi 7.4 tr1 and 7.3 tr7

It is a good job I checked the pages for the new Technology Refreshes today as they now contain data, and I can find announcement documents too. But there has been no fanfare on platforms like Twitter that I would have expected.

It would appear that the two TRs contain the same Db2 for i enhancements. Just having a quick look I can see several I am very interested in trying:

  • Views to see which modules and service programs and bound to programs
  • Db2 services for data queues
  • Db2 services for the IFS
  • A way to use SQL to write directly to the job log

In other words lots of good stuff to play with and write about in the future.

Thursday, October 3, 2019

Waiting for the new TRs

waiting for new trs

It is now October, and I am impatiently waiting for the new Technology Refreshes for IBM i.

In previous years the announcement had been made in August or September for the second round of the biannual TRs. This year we are still waiting.

Pages were added to the IBM Community wiki on September 29, which makes me think it cannot be too long before the announcement is made.

You can find the pages here...

As soon as the announcements are made you will be able to read about them here.

Wednesday, October 2, 2019

Getting database relationships using SQL

get list of dependent objects for a file using sql

I was asked if it was possible to get the same information that is shown using the Display Database Relations command, DSPDBR, via SQL. You can generate an output file using the DSPDBR command, but that was not what was wanted.

There is a Db2 for i view SYSVIEWDEP which returns the dependencies of a view to its "parent". But it just contains only SQL views and indexes, no information for logical files. As I work in an environment where there are thousands of logical files in the ERP application this view does not give the dependency information I needed.

If the information is available via a command it must be contained somewhere within the IBM i operating system. In this case the dependency information is in the file QADBFDEP in QSYS.

Wednesday, September 25, 2019

Creating many different dates, days, months, and years in just one SQL statement

calculating different dates using sql

The germ for this post came from a program I found that was used in a job I was making changes to. The program made a data structure of information about various dates, you'll see exactly what below, that was passed to various programs. The program was several thousand lines of unnecessarily complicated RPG code. Looking at the results it was passing back to the calling program I knew it could be done a lot simpler using SQL, rather than the complex calculations this program contained.

Never one to turn down a challenge over the weekend I wrote a SQL RPG program that generates the same information in the data structure just using one SQL Select statement. I thought this would be an interesting thing to share due to some of the SQL functions I used, some I have written before others I have not, and you might find useful if you have to do something similar.

When called this program returned a data structure that contained the following:

Wednesday, September 18, 2019

Using SQL to rank results

ranking results using sql rank()

When I wrote about using a SQL Sequence to place sequential values in a column I used the example of ranking students based upon on their test results. Looking back this may not have been the best example, as if two students had the same score they would have been given a different sequence number.

Fortunately SQL does have a function I can use to properly rank these students, based on their test scores.

Let me start with the students and their test scores:

---------  ----------  -----------  -----
FUESTER    MAX                      25.69
ECKERT     TRISTAN          M       49.86
APUZZO     CAROL            C       14.89
TORRES     JOSE                     94.96
PENA       JUANITA          M       74.09
HONG       XIA                      18.54
REED       STEVE            J       65.83
OKEEFE     VINCENT                   6.02
KING       CATHERINA        K       18.76
WILEY      CHRISTIAN        J       60.77
MORALES    MARIA            A       65.83

Steve Reed and Maria Morales have the same test score, therefore, they need to have the same ranking. Let me show the SQL statement I would use to rank these eleven students by their score.

Thursday, September 12, 2019

End of support for IBM i 7.2 is announced

end of life for ibmi release 7.2 in 2021

On September 10 IBM announced the end of marketing of IBM i 7.2 on April 30, 2020, and the end of support for this release effective April 30, 2021.

IBM i 7.2 was released in May 2014, and by 2021 it will have had a life of 7 years.

If you have partitions running IBM i 7.2 it is time to determine if your Power server can support the newer releases 7.3 and 7.4, and if it does to start making plans upgrade to one of those newer releases.

You can read the announcement document from IBM here.

Wednesday, September 11, 2019

Using SQL to determine which subsystems are active or not

list all subsystems and whether they are active

When I wrote about how it is possible to check if a subsystem is active or not someone asked me if there is a way to list all the subsystems and whether they are active or not.

Having had some time to "play" I have developed a way of doing this by combining data from the following two SQL table functions:

I can get a list of all subsystem description objects by using the OBJECT_STATISTICS table function:

Wednesday, September 4, 2019

Variable length arrays in RPG

arrays with varying number of elements

One of the new additions to RPG in IBM i 7.4, and not to the equivalent TR of 7.3, was the ability to have variable length arrays.

Having read the documentation, and having had a chance to "play", my findings mean I can divide this post three sections:

  1. Setting the number of elements in the array
  2. Expanding number of elements
  3. Compile time array

Before I get started I want to show what the maximum size of an array, this number has not changed since at least IBM I 7.2 . It is not the number of elements in the array that is the limit, it is the total size of the array. The array must not exceed 16,773,104 bytes. For example, this is valid as an array of 16,773,104 elements of one character is valid.