• 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: Preamble Comments

  April 15, 2019 Chris Ringer

  Do you remember what you ate for lunch two days ago? If you’re like me, you had to think about it for a minute before answering. Now imagine trying to recall the detailed requirements of a few programs you wrote a year ago so you can modify them for a new project. Or better yet, what if someone else like a retired employee or a traveling contractor coded these programs? At this point you may become very dependent on any comments they left in the code.

  It’s been said that any time spent commenting on your code now might save …

  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: 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

Previous Articles Next Articles