• 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

• Guru: Dynamic Arrays Come To RPG

  July 20, 2020 Jon Paris

  Some 12 months ago, when the 7.4 release was announced, I wrote the Guru Tip “7.4 Brings New RPG Goodies” describing the features of 7.4 that were also available on 7.3. I said at the time that I would return later to discuss the 7.4-only features. So now that a significant number of shops have access to 7.4 that time has arrived.

  Dynamic arrays are the answer to the perennial programmer question: “Just how big do I need to make this array?” In my experience it doesn’t actually matter how big you make it, at some point down the road …

  Read more

• Guru: The Case for Mixed-Case Procedure Names

  July 14, 2020 Paul Tuohy

  Using mixed case makes source code easier to read and easier to debug. I programmed in all upper case for many, many years, so I don’t recoil in horror when I see something in all uppercase. However, it’s a bit more difficult for developers who aren’t quite as long in the tooth as I am. To them, all uppercase is pretty abhorrent and bad (and I don’t mean bad as in good).

  There is a potential case issue with subprocedure names: there are at least three places on the system where the system shows subprocedure names in uppercase, regardless of …

  Read more

• Guru: RDi V9.6, Part 9 – RDi Helps with Refactoring Code into Procedures

  July 6, 2020 Susan Gantner

  I’m guessing that you have some RPG code in your shop that’s desperately in need of some refactoring. I have yet to visit an RPG one that wasn’t in that position. If by chance you don’t know what I mean by “refactoring” or why you may need it, I highly recommend that you read Ted Holt’s excellent series of Guru tips on refactoring RPG. I’ve included links to those tips in the Related Stories section below.

  I was inspired to go back and review the entire series after I attended Ted’s session Refactoring RPG: What, Why & How at an …

  Read more

• Guru: Use The Administration Runtime Expert To Validate Your System Configuration

  June 22, 2020 Dawn May

  Using the Administration Runtime Expert (ARE) to validate system configuration settings is an easy way to confirm configuration values are set the way you want them to be. You can use ARE to ensure these system configuration settings are consistent across multiple IBM i partitions or to verify settings have not changed on an individual partition. In this second in a series of articles on ARE, we’ll walk through the steps to set up a basic template to validate system configuration settings.

  The first step is to identify what system configuration attributes you want to make sure are consistent or …

  Read more

Previous Articles Next Articles