• 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

• Guru: Speed Up Command-Line PHP

  January 21, 2019 Alan Seiden

  While PHP runs quickly via the Apache web server, command-line PHP scripts (run from PASE directly or via CL or RPG) have a lag on start-up. In this article, I show how to speed up PHP when called from the command line (known as PHP-CLI).

  Why does PHP-CLI (command line PHP) have a slow start-up? While several reasons are often given, I’ve found the culprit to be the loading of PHP extensions that are enabled by php.ini and other configuration files of Zend Server. Examples of extensions are ibm_db2, simplexml, and Zend’s proprietary components.

  Within a normal web server environment, …

  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

• 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

Previous Articles Next Articles