• Guru: Concerning The Stepping-On Of Feet

  October 19, 2020 Ted Holt

  It has come to my attention that once again I did not tell the truth, the whole truth, and nothing but the truth. In Three Suboptimal I/O Practices, I said that a simple SELECT INTO was preferable to a cursor that fetches one row. It turns out that there is at least one situation in which SELECT INTO will not serve the purpose, and one has no choice but to use a cursor that fetches one row.

  The situation is this: the program must lock the fetched row for update. Despite its power and simplicity, SELECT INTO cannot lock …

  Read more

• Guru: Dynamic Arrays Come To RPG – Limitations, Circumventions, And More

  October 12, 2020 Jon Paris

  In my first two tips in this series I covered the basics of automatic sizing arrays and variable sized arrays. In this final part am going to discuss some of the current limitations in this support and the facilities IBM has put in place to help circumvent them.

  IBM publishes an extensive list of the limitations, but in normal usage only a couple present “real” restrictions in practical terms. If you are curious though, you can find the full list here.

  Limitations

  The first limitation to bear in mind is that, currently, only top-level variables can be defined …

  Read more

• Guru: Overloading Subprocedures

  October 5, 2020 Paul Tuohy

  The ability to overload subprocedures in RPG is something I had been waiting for a long, long time. IBM finally made it available through a Technology Refresh (7.4, TR1 or 7.3 TR7). If you are not familiar with the term, overloading (in RPG) is the ability to create multiple subprocedures of the same name with different implementations.

  Let’s have a look at how overloading might benefit us when it comes to writing programs and subprocedures. This is a portion of a prototype copy member that, amongst others, contains the prototypes of these three subprocedures:

  dcl-pr format_from_Date varChar(10) extProc(*dclCase);
    dateIn date(*ISO) 

  …

  Read more

• Guru: DISTINCT Can Hide A Performance Problem

  September 28, 2020 Ted Holt

  When I see the word DISTINCT in an SQL query, a little red flag goes up inside my head. Not literally, of course. But it does make me pause and scrutinize the query more closely. I have found that poorly designed queries sometimes include the word DISTINCT as a final act of redemption to forcibly return the proper result set.

  The purpose of DISTINCT is to remove duplicate rows from a result set. As the DB2 for i SQL reference puts it:

  The keyword DISTINCT is not considered an argument of the function, but rather a specification of an operation …

  Read more

• Guru: SQL Can Read Program-described Data

  September 21, 2020 Ted Holt

  I thought I left program-described database files behind me in 1988. That’s when I left my last S/36 shop to begin working on the S/38. Well, I did, but not completely. From time to time I work on a system with program-described files, and even some externally described files have program-described fields. Fortunately — and I owe this to Scott Forstie — I have learned that SQL can read program-described data. Will wonders never cease?

  You may be thinking, “This article doesn’t apply to me. Our files are externally described.” You may be right. Then again, you may be wrong. …

  Read more

• Guru: Qualified Files – Underused and Unappreciated

  September 14, 2020 Ted Holt

  When IBM adds a new feature to the RPG compiler, they do so for a reason. That’s why I try to learn new techniques. I hope they’ll improve the quality of the source code I write. One relatively new feature that I do not see widely used is the qualified file. In the following paragraphs, I’d like to tell you why I like qualified files and how to use them.

  To understand the need for qualified files, it may be good to begin with a brief lesson on the history of the RPG language. When RPG was originally developed, the …

  Read more

• Guru: Reading Nested XML Using SQL

  August 31, 2020 Jonathan M. Heinz

  XML is a data-interchange format, not a relational database management system. For this reason, using SQL to query XML data can be challenging, as what would be stored in two relational tables are placed in one element of XML. To put it another way, detail data is nested under the header data.

  I would like to share a way of using SQL to extract nested data from an XML file. I found this method useful when testing a change to a process that creates XML to be sent to customers. I can use this SQL to quickly check that the …

  Read more

• Guru: Scalar Use of LISTAGG

  August 24, 2020 Ted Holt

  The SQL LISTAGG function is as handy as a pocket. Only recently I used it to build a string of comma-separated values (CSV) to populate a drop-down box. It sure beats a cursor and a loop. I’ve noticed in my reading that LISTAGG can be used for both aggregate and scalar purposes under Oracle. Db2 doesn’t support the scalar use, but I found another way to do the same thing.

  To see what I’m talking about, check out example 7 in the article Oracle LISTAGG Function Usage, Tips & Examples, by Ben Brumm. I recommend studying the entire article; …

  Read more

• Guru: Dynamic Arrays Come To RPG – The Next Part Of The Story

  August 17, 2020 Jon Paris

  In my first tip on this topic I covered the automatic sizing option (*AUTO) for the new dynamic arrays. In this tip I am going to look at the second option (*VAR), which allows the programmer to directly control the capacity of the array, growing and shrinking it as required. In addition I will also briefly cover the third option (*CTDTA) which, as you may have guessed, relates to compile time arrays.

  Using A Varying Length Array

  Let’s start with a brief example of using a variable sized array in conjunction with SQL. This approach answers the classic question of …

  Read more

• Guru: Use The Administration Runtime Expert To Validate Your Application Attributes

  August 10, 2020 Dawn May

  This third article in my series on using the Administration Runtime Expert (ARE) reviews how ARE can validate the attributes of your applications. This article assumes you have already learned the basics of how to use ARE from articles one and two.

  There are an unlimited number of ways you can use ARE to validate application attributes. A few examples are:

  • Verify application attributes on a single partition
  • Verify application attributes are consistent across multiple partitions
  • Verify application attributes in the test environment are the same as production
  • Verify application authorities

  When you are creating a template to validate …

  Read more

Previous Articles Next Articles