• 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

• Guru: Addressing A Legitimate Question

  December 10, 2018 Ted Holt

  This is the last Monday issue of The Four Hundred for 2018. My, how time flies! I like to do something different at year end. In previous years I have solved Sudoku puzzles, found my way through mazes, solved the peg game, and more. This year I wish to honor a request that has come from various people and to address what they consider to be a legitimate question.

  As I wrote recently, the question I hear occasionally goes something like this: “Why bother with service programs? Why not use dynamic calls?” Rather than insult the …

  Read more

• Guru: Right-Size Your PHP

  December 3, 2018 Alan Seiden

  In my experience doing performance audits, I often point out that while PHP for IBM i comes perfectly configured for small applications, it must often be tuned to handle large workloads. When applications must accommodate hundreds or thousands of users, or use AJAX technology, two easy changes should be considered for PHP and Apache configurations.

  1. PHP_FCGI_CHILDREN Setting In fastcgi.conf

  On IBM i, Zend Server installs a configuration file called fastcgi.conf. Among other settings, it includes PHP_FCGI_CHILDREN, which determines how many PHP jobs run when its Apache server is started. The file — /www/zendphp7/conf/fastcgi.conf (Zend Server 9 or higher) or …

  Read more

• Guru: When Is An Error Not An Error?

  November 26, 2018 Jon Paris

  When is an error not an error? When it is expected! In this article I want to discuss the use of RPG’s MONITOR op-code and discuss ways in which it might change the way you code RPG. I was prompted to write up my thoughts on this subject as a result of being quizzed by students at a recent RPG & DB2 Summit as to why I was using Monitor blocks rather than more conventional RPG techniques in my examples.

  So what do I mean by expected? Basically I mean those errors that you know are going to happen …

  Read more

• Guru: Configure Collection Services

  November 12, 2018 Dawn May

  Collection Services collects valuable information about your partition and the workloads you are running. While many think of Collection Services as performance data, it is really systems management data and at some point you will need that data. Collection Services helps you understand the overall workload running on your system and trends over time. It provides the ability to look backwards in time to understand “How did I get here?” and has the data to answer a wide variety of other questions.

  Collection Services is on by default, so whether you know it or not, you are running Collection Services. …

  Read more

• Guru: Ready Or Not! Part 5 Of Big Changes In RDi V9.6, Edit/Browse Toggle, PDM Perspective, More

  October 22, 2018 Susan Gantner

  In this tip I’m going to cover a few of the “smaller” features implemented in V9.6.0.3 and …0.4 that I think you should know about. I’ll cover my favorite — the Edit/Browse toggle feature — as well as some enhancements to Block Nesting. I’ll also follow-on to my last tip about the PDM affinity features in the Object Table — the PDM perspective — plus a few more little goodies.

  A quick hint for you on the subject of exactly what new features became available within RDi and when: If you ever want to see a complete list of new …

  Read more

• Guru: A More Efficient Way To Merge With SQL

  October 15, 2018 Mike Larsen

  Lately, I’ve been using the merge statement in my programs to insert or update rows in a table. I recently came across a situation where a program using merge was running every few minutes and performing updates to thousands of rows each time it ran. Since this table was journaled, it was obvious some re-design was necessary.

  For those unfamiliar with the merge statement, it is sometimes referred to as an “upsert.” That means it will either perform an update or an insert. In RPG terms, I like to compare it to a chain operation. With a chain, you check …

  Read more

• Guru: Learn %PARMS! Solve Two CL Problems!

  October 8, 2018 Ted Holt

  Those wonderful people at IBM have done it yet again! They have gladdened my existence with new CL functionality that solves two problems, and I will never have to face those problems again. Let me tell you about the new %PARMS built-in function.

  The %PARMS function returns the number of parameters that are passed into a CL procedure (i.e. a CL program or a CL module). In the past, I have monitored for message MCH3601. That works in some situations, but not in all. The %PARMS function gives me an unambiguous way to know whether a parameter was passed or …

  Read more

• Guru: Phonetic Functions In SQL, Part 1

  September 17, 2018 Paul Tuohy

  In my next two articles I am going to discuss the use of phonetic functions in SQL. You can use phonetic functions to select or order rows based on the phonetic sound of a string as opposed to the actual characters in the string. The obvious use of phonetic functions is with names, but they can be used with any string columns.

  I must admit that this touches on one of my pet peeves — the spelling of my surname. I have lost count of the number of times I have had to spell my name two, three, or four …

  Read more

Previous Articles Next Articles