• 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

• Five Acquisitions You May Have Missed

  March 13, 2019 Alex Woodie

  The New Year has started off with some wheeling and dealing, as some software company owners look to bulk up while others look to hand off responsibility to somebody else. Those operating in the IBM i marketplace aren’t alone in making acquisitions. Here are five under-the-radar deals in the midrange that you may have missed.

  Attunity‘s line of real-time data integration software will now be sold through Qlik, which acquired the publicly traded company in a $560-million in late February. It was a natural enough move for Qlik, the well-regarded BI vendor that was acquired by private equity …

  Read more

• Guru Classic: What’s That Name, Again?

  March 6, 2019 Paul Tuohy

  Author’s Note: This article was originally published in February 2013. I recently had to hunt out this article when visiting a client. The content is even more relevant as the use of SQL stored procedures continues to grow.

  One of the great things about writing articles and tips is that people will drop you a note to ask a question or tell you how much they liked (or disliked) what you wrote. But even better is when someone drops you a line to let you know they have taken what you wrote and extended it.

  Such was the case with …

  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

• Four Hundred Monitor, March 4

  March 4, 2019 Jenny Thomas

  “Dear March, come in!” Readers of Emily Dickinson might recognize the first line of one of her many poems. Although it is unlikely she was thinking of the computing industry as she welcomed the new month, the sentiment is fitting as we already enter the last month of the first quarter of the year. 2019 has been a busy one for IBM as it continues to make headlines throughout the computing world, which will hopefully result in good numbers coming out of Q1. You can count on IT Jungle to be watching for that news, and in the meantime, you …

  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

• IBM i Has Been Getting With The Program For Years

  February 4, 2019 Timothy Prickett Morgan

  There are many things that one could constructively criticize IBM about when it comes to the Power Systems platform running the IBM i operating system. But, in recent years at least, one of those things would not be – and could not be – that the company has not done enough to embrace the most important elements of the modern programming toolbox.

  In fact, the company has done and increasingly good job of embracing and extending the compilers, interpreters, frameworks, and models of the programming languages that have gone mainstream since Java first took the stage at the beginning of …

  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: Easy Date Difference

  January 14, 2019 Ted Holt

  Hey, Ted:

  The dates in our database are stored as seven-digit packed-decimal values in the common CYYMMDD format. In 2018 I wrote an SQL query that reported the number of days between two dates, but it quit calculating properly as soon as it started using 2019 dates. Can you tell me the proper way to find the difference between two dates in days?

  –Becki

  I don’t know if “the” proper way exists or not, Becki, but I can show you how to do the required calculation. SQL has some handy built-in functions that address your problem.

  The DAYS function returns …

  Read more

• Guru: Arranging Query Logic in DB2 for i Routines

  January 7, 2019 Michael Sansoterra

  Hey, Mike:

  We use a stored procedure to return the result set to Java and display the results to a screen. I would like to make this stored procedure put the result set into a temporary table and then do some processing on the temporary table. Is this possible and if so how can I achieve this? Thanks.

  Your dilemma is understandable: stored procedure result sets are great when data needs to be returned to a client. However, once generated, the result set cannot be joined, sorted, or stored in a temporary table. Thankfully, a user-defined table function (UDTF) allows …

  Read more

Previous Articles