• Guru: Emulate Control Break Reporting With DB2 For i SQL

  May 13, 2019 Michael Sansoterra

  When generating ad hoc user reports, it’s often the case that developers don’t have the time or luxury to write a high-level language program or to use a reporting tool such as DB2 Web Query or Crystal Reports to format the data appropriately. But when a user is given a raw dump of a SELECT statement’s result set in Excel, it can be quite ugly to wade through and format.

  The user may have to do things like add total rows or remove duplicate values, which can be quite time consuming. Thankfully, DB2 for i SQL offers a way to …

  Read more

• Guru: Open Access To The Rescue – The Next Step

  May 6, 2019 Jon Paris

  I recently wrote a tip on using an Open Access handler to enable the creation of flat files in the IFS. In that version of the handler I generated a generic file name and used hard-coded record delimiters and code page settings. I mentioned at the time that I would normally supply these values at run time. In this follow-on tip I describe how the handler can be modified to use the Open Access User Parameter to facilitate this.

  The User Parameter is simply specified as a second parameter to the HANDLER keyword. It can be a simple variable or, …

  Read more

• Guru: Creating Excel Spreadsheets With Python

  April 29, 2019 Mike Larsen

  Over the past several years, I’ve seen a growing demand to provide business data in Excel. Some shops are content to receive their data in .csv format and then manually apply formatting to make it presentable. Wouldn’t it be better if we could provide a spreadsheet that is already formatted?

  In my opinion, the answer is yes. Most recently, I’ve been using PHP to accomplish this task. PHP works great for me, but I always like to have different options from which to choose. This is important to me because some shops may not have PHP installed. Based on that …

  Read more

• Guru: Dealing With RPG Errors and Embedded SQL

  April 8, 2019 Ted Holt

  Hey, Ted! I’m having trouble using some of the new techniques I learned at the RPG and DB2 Summit. Below is a screen shot of a program I am writing. I cannot figure out why the compiler doesn’t like it. Can you see anything that would be causing the declarations to fail?

  — Mike

  I glanced over Mike’s code and noticed that he used a correlation name in the SELECT and WHERE clauses, but did not define that correlation name for any of the tables, like this:

  SELECT x.onefield, x.twofield, x.redfield, x.bluefield
  FROM MYTABLE
  WHERE x.onefield = :TestValue;
  

  He …

  Read more

• Guru: IBM i Save File Compression Options

  April 1, 2019 Michael Sansoterra

  As I finished populating some test tables with a large volume of data on a small and transient IBM i partition in the cloud, I thought life was good. But my countenance fell as I realized the tables plus OS hogged over 70 percent of the disk space. I wondered how to get all the data into a single save file for safe keeping.

  The buzzer in my mind was loud and clear: it ain’t gonna work, you don’t have enough room. As I loathed the thought of using multiple save files to save my test data, I remembered that …

  Read more

• Guru: When Playing With SQL

  March 18, 2019 Paul Tuohy

  One of the questions I have been asked a lot at conferences is “How do you figure out x in SQL?” In this article, I will discuss four things I use a lot when playing with SQL in Run SQL Scripts: VALUES, SYSIBM.SYSDUMMY1, global variables, and the system catalog.

  VALUES

  When I am trying to figure out how an SQL function works, my first port of call is the VALUES statement. VALUES derives a result directly from an expression. For example, the following statement:

  values upper('paul');
  

  Would generate the following result set:

  

  You can specify more than one value in …

  Read more

• Guru: Manage Filters in RDi

  March 11, 2019 Paul Tuohy

  The longer you have been using Rational Developer for i (RDi), the longer the list of filters you are trying to manage. Maybe you have started to get clever with the naming of filters and/or you spend a lot of time using drag and drop to try and keep your filters in some kind of order.

  The good news is that RDi does provide a means of grouping filters — Filter Pools.

  This is a (very) cut down example of filters in my Remote Systems view. Let’s see how we can use Filter Pools to make this list more manageable. …

  Read more

• Guru: Why And How Not to Use The Aretha Franklin I/O Method

  March 4, 2019 Ted Holt

  The Aretha Franklin I/O Method is still used heavily in RPG shops even though a better method has existed for decades. In the following paragraphs, I explain the Aretha Franklin I/O Method, tell you why you should not use it, and show you the superior method.

  First, let me give credit where credit is due. Although I had been using the Aretha Franklin I/O Method since my System/34 days, I never knew it by that name. Then Dan Cruikshank (now retired) of IBM informed me of this terminology. Here’s how it works:

  Assume an RPG program that needs data from …

  Read more

• Guru: Find The Needle In The Haystack With Watches

  February 25, 2019 Dawn May

  IBM i does a great job of logging information using messages. Messages are sent to message queues, the history log, as well as the job log. IBM i job logs can be very useful for identifying and resolving application issues. No other system has such a great logging facility for reviewing what happened in a job.

  What if you need to debug an intermittent application problem? What if the symptom is a message logged to the job log? And what if there are hundreds of jobs where this message could be sent? There are a variety of ways you can …

  Read more

• Guru: Change XML Elements in SQL

  February 18, 2019 Paul Tuohy

  Over the last few years, it has become more common to store XML or JSON in a column in a table. Whereas SQL provides all the necessary functions to construct/deconstruct XML or JSON from/to relational data, it does not provide an easy means to change the contents of an element. In this article, I am going to demonstrate a technique for changing the contents of an XML element using an SQL stored procedure.

  Just to provide some background, I was recently working on a project where DB2 XML Extender functionality was being replaced with the standard XML functions. The project …

  Read more

Previous Articles Next Articles