• 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: Why A Function Was There But Could Not Be Found

  March 25, 2019 Ted Holt

  Hey, Ted:

  I am stuck on trying to create a function in RPG to use in SQL. I based it on your FMTDATE function, which I successfully installed and is working great! I have been trying to get this function working for five hours and I am at my wits’ end. Hopefully, you will notice something right away.

  –Andrew

  The message that Andrew was receiving was SQL0204 (HISFUNCT in *LIBL type *N not found). (I have replaced the name of Andrew’s function with HISFUNC.) Yet the function existed and the service program existed. There was nothing wrong with Andrew’s RPG …

  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

• Guru: Open Access To The Rescue

  February 11, 2019 Jon Paris

  Recently I came upon a couple of questions on internet lists asking for an easy method to create flat files in the IFS. Not CSV files, but rather simple text files where individual data items are in fixed character positions. Various somewhat convoluted solutions were offered to the requestors, but to me this problem shouted “Open Access!”

  So, I set about quickly building an Open Access (OA) handler to perform the task. I find OA really useful for this kind of job because, once the handler is written, creating these kinds of files in the future become a trivial task, …

  Read more

• Guru: Create A UDTF Wrapper For A Stored Procedure Result Set

  February 4, 2019 Michael Sansoterra

  In the tip, Arranging Query Logic in DB2 for i Routines, I addressed a reader’s question about how to create a user-defined table function (UDTF) in DB2 for i that would return the same result set as an existing stored procedure. The purpose of having the UDTF would be to do additional processing on a result set, such as joining the result set with another query or dumping the result set to a temporary table for analysis. To accomplish this, I suggested moving the stored procedure query logic into a UDTF and then replace the query within the stored …

  Read more

• Guru: Health Indicators

  January 28, 2019 Dawn May

  In Configure Collection Services, I reviewed how you can improve the configuration of Collection Services. In this tip, I’m going to demonstrate how to easily review your Collection Services data and understand your partition’s performance characteristics using the Health Indicators.

  Navigator for i has a rich set of performance tasks, one of which is Investigate Data, also referred to as the Performance Data Investigator (PDI). The base operating system allows you to manage and visualize Collection Services data. The Performance tasks (and PDI) also support other types of performance data, but those graphical features require installing the Performance Tools …

  Read more

Previous Articles Next Articles