Almost hidden among the many useful tools IBM made available in the SYSTOOLS library, we find the LPRINTF procedure. This procedure performs a very simple task: when invoked, it writes a message in the job log; it has only one VARCHAR parameter in which you set a character string to be displayed in the log. Actually, LPRINTF interfaces the API Qp0zLprintf, so that you can easily invoke it even with SQL.
What is the use of LPRINTF? There are several situations in which it may be useful writing additional information in the joblog, other than those provided by the system. A significant example is a program in which SQL statements are dynamically built into a character field, also with variables. After building the instruction into a character variable, with LPRINTF you can output it into the job log, so that you can check what is wrong in the event of an error or undesired results.
To invoke LPRINTF from an RPG program, simply perform this SQL statement:
Exec SQL CALL SYSTOOLS.LPRINTF(:message) ;
Let’s see an example of a program that dynamically builds an SQL statement for generating a table with data, also using variables. This is the instruction for building the SQL statament:
sqlStmt = 'CREATE TABLE mduca1.Faq40013T AS (' + 'SELECT CodCliente, RagioneSociale, NazCl, ' + 'd.Articolo, Descrizione, SUM(OrdQty) ' + 'FROM AnCli00f ' + 'JOIN OrdDt00f d ON CodCliente = OrdCli ' + 'JOIN AnaArt00f a ON a.Articolo = d.Articolo ' + 'WHERE NazCl = ' + pNation + ' ' + 'GROUP BY CodCliente, RagioneSociale, NazCl, ' + 'd.Articolo, Descrizione ' + 'ORDER BY CodCliente)' + ' WITH DATA' ;
This program fetches from customer master file all the customers for the nation received as a parameter, together with other data from sales orders and items master, and writes them in the FAQ40013T table.
We launch the program and check the results.
call faq40013 parm('IT')
The program did not create the table … normally we should activate the debug, relaunch the program and see the contents of the sql variable to understand the problem … but with LPRINTF, just have a view to the job log and we have the SQL statement ready to be checked:
Here’s the mistake: a column was defined with the SUM scalar function, but it was not given a name. The CREATE TABLE statement requires that each column of the table being created has its own unique name.
Let’s correct, compile and see if it works:
There are still problems… let’s check.
This time it’s the quotes missing around the country code … let’s correct again and see if this is the good one!
Now everything is OK ! By having the SQL instruction directly displayed in the joblog, we were able to solve problems much faster. Obviously this is just a simple example, but with much more complex SQL statements, the help of LPRINTF becomes more valuable.
Of course, nothing prevents from using LPRINTF to write in the joblog any other information you consider useful to better monitor your applications.
LPRINTF procedure is available in DB2 for i product from versions 7.3 (level 16) and 7.4 (level 4)
The sample program’s source is available on GitHub: https://github.com/MD2706GIT/FAQ400/blob/master/FAQ40013.sqlrpgle
LPRINTF Procedure: https://www.ibm.com/support/pages/systoolslprintf-procedure