• 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

• Guru: What Do People Have Against Timestamps?

  August 3, 2020 Ted Holt

  In my work I often see database tables and physical files with related date and time fields (columns). By related, I mean that the two fields together indicate a certain time on a certain date for a certain event. I have found these date/time pairs to be difficult to work with at times, so much so that I have come to prefer timestamps.

  I wonder why database architects (I use the term loosely) specify separate date and time fields rather than a timestamp. Having done no scientific survey, I can only guess. I suspect that many tables date back …

  Read more

• Guru: Creating PDF Documents With Python

  July 27, 2020 Mike Larsen

  I’m often asked to create reports for my business partners. The format of the report depends on who made the request. If the financial team made the request, I may produce an Excel file. The CEO may want to report quarterly sales to their shareholders. In that case, I may produce a PDF using the company letterhead.

  In a prior article, I showed how to create Excel files using Db2 tables. Now I’m going to show how to create PDF documents using Db2 tables.

  This story contains code, which you can download here.

  I always start by making …

  Read more

Previous Articles Next Articles