Bob Knows Db2 for i

 This week marks the GA of the IBM Bob Premium Package for i. This tailored package extends IBM Bob’s AI coding assistant capabilities to support IBM i application development. The IBM Bob Premium Package for i includes both an IBM i Developer mode and an IBM i Database mode. With Bob’s focus on enabling programmers to more efficiently traverse the entire application development lifecycle, this customized package delivering an IBM i Developer mode is not a surprise. The IBM i Database mode which provides Bob with a deeper knowledge of Db2 for i is a surprising and welcome addition.

 IBM Bob’s ability to provide both an IBM i Developer and IBM i Database perspective to end users reminds me a little bit of the Bo Knows marketing campaign. Nike launched this campaign in 1989 to highlight Bo Jackson’s success in multiple United States professional sports leagues – baseball and American football. The excellence that he was able to demonstrate in two professional sports with different skills was unprecedented in recent US sports history. 

 

The IBM i Database mode has enriched IBM Bob with tools and skills. These tools and  skills  are automatically used by Bob as you request his assistance with your Db2 for i databases. These requests may require Bob to run SQL queries on your server, retrieve the SQL source for your Db2 object, or share information on database security best practices. 

 

The IBM i Database mode includes slash commands which are pre-defined prompts to enable you to direct Bob to perform a specific database-related task. For more repetitive tasks, these commands can be a time saver because they allow you to get Bob working right away on a database task as opposed to multiple interactions with Bob before he understands exactly what operation you want him to perform.

 

One example is the /erd command.  This command instructs Bob to generate a Entity Relationship Diagram (ERD) for a set of related database tables on the IBM i server that your session is connected to.  The following figure shows a portion of an ERD generated by Bob for the Db2 for i sample database.

The IBM Database for i mode also includes an SQL Index Strategy Advisor workflow. Workflows are complex, multi-step processes that use agentic and/or non-agentic steps to accomplish a particular task. For many releases, Db2 for i has provided multiple SQL performance tools and services to capture performance data which you can analyze to come up with index recommendations to improve SQL performance. This index strategy workflow makes that process more efficient by guiding you through each step of the process and interacting with the Db2 tools and services without you remembering which command to run or Db2 performance data object to access. 

If it  is a new SQL statement that you’ve created, then the workflow will automatically capture a new  set of database monitor data which is needed to perform its indexing strategy review. For existing SQL statements run by your application, the workflow can find the required performance data to review in a plan cache snapshot instead of wasting time re-collecting performance data. 

 

I’ve highlighted just some of the Db2 capabilities available in the IBM Bob Premium Package for i and these capabilities will grow over time. With these limited highlights, I’m hoping you’re excited to kick the tires yourself on Bob’s IBM i Database mode.  The free trial support is available to anyone on the IBM Bob website and includes access to the IBM i premium package. Enterprise customers can work with their business partner or IBM sales representative to arrange a free trial.

Why do SQL Procedures have a Program Type Setting?

 If your application uses a lot of SQL stored procedures, then you may have noticed there’s a PROGRAM TYPE setting. A stored procedure is really SQL’s version of a program object. There’s nothing in the SQL standards that connects a stored procedure to a program object.  However, many relational databases, Db2 for i included, utilize a program object in their stored procedure implementation to improve performance.

When you execute the CREATE PROCEDURE statement on Db2 for i, you may or may not know that the Db2 engine generates a C program object to implement the procedural logic  that was coded with SQL.  This enables Db2 for i to interpret the SQL-based logic a single time and store the implementation in a program object. The alternative is dynamically interpreting the SQL logic every time that the SQL stored procedure is invoked – it doesn’t take a rocket scientist to understand that performance would be unacceptable if the SQL procedure had to be interpreted on every execution.

 

The fact there’s a program object associated with your SQL stored procedure is hidden from the application. The application just needs to know the name of the SQL procedure to invoke and Db2 is responsible for finding and using the corresponding C program object. 

 

Now that you understand the association between an SQL procedure and IBM i program, the PROGRAM TYPE setting for an SQL stored procedure should make a little more sense. Many moons ago when the SQL procedure  support was first delivered with the V4R2 release in 1998, there was only a single PROGRAM TYPE setting MAIN.  Not sure where the values of the PROGRAM TYPE setting came from, but regardless of the origin MAIN meant that a C program was generated every time that an SQL stored procedure was created. 

 

When I was working on the IBM PartnerWorld team (now known as IBM Parter Plus) in the late 1990’s & early 2000’s, one of my responsibilities was helping software vendors port their applications based on other databases to the IBM i (then known as the iSeries). The Db2 for i SQL stored procedures made it much easier for vendors to port their applications.

 

A couple of years after the SQL stored procedure support was delivered, our porting team helped an Oracle-based vendor port their application to run natively on the IBM i. This software vendor’s application relied almost exclusively on stored procedures – if I recall correctly that application used over 8,000 different stored procedures. Every business function supported by the applications resulted in a significant number of stored procedure calls. As this porting project moved into the performance testing phase, the cost of the high number of stored procedure invocations was consuming a nontrivial amount of  the overall application runtime.

 

That’s when the second PROGRAM TYPE value of SUB entered the picture to give Db2 for i the option of generating a C service program object. With a service program object, IBM i only performs the activation processing once within a job (or database connection). In contrast, activation processing is performed every time that a program object is called within a job. While the performance difference is relatively small – my testing showed about almost a 10% performance improvement on a stored procedure called repeatedly within a job - the multiplicative impact it had on the vendor’s stored procedure heavy solution was significant. 

 

Another bonus is the simplicity of the coding change as shown in the following example. All that you need to do is add the PROGRAM TYPE clause to your CREATE PROCEDURE statement.

CREATE PROCEDURE simpleproc (IN i1 INT, OUT o1 INT)
  LANGUAGE SQL
  PROGRAM TYPE SUB 
BEGIN
  SET o1 = i1;
END

Your application doesn’t need hundreds or thousands of stored procedures to benefit from the performance benefits provided by the PROGRAM TYPE SUB option. If you have a single SQL stored procedure that’s called multiple times within a job or connections which is often the case with connection pools, then your application performance will increase from the multiplicative effect as well. 

Use the Force - NOT!

In a pivotal scene towards the end of the original Star Wars film, Obi-Wan Kenobi issues the iconic line: Use the Force, Luke. The ghost of Obi-Wan is urging to Luke turn off his targeting computer on his attack of the Death Star and instead rely on his feelings (i.e., the Jedi Force) to guide his proton torpedoes.

I’m sure  you’re probably thinking how in the world does the Jedi Force relates to Db2 objects on IBM i…   Db2 tables and indexes, which includes physical files and logical files, actually have a legacy force setting. That force setting is the Force Write Ratio (FRCRATIO) parameter on Db2 for i tables and indexes have the Force Access Path (FRCACCPTH) parameter.  If you’re using non-default values for these parameters, then effectively you’re turning off the targeting computer like Luke decides to do.

While turning off the computer works for Luke (I figured a spoiler alert wasn’t required for a movie that I saw as a kid) – hopefully, the “NOT!” in this entry title makes it clear that’s not a recommended practice on IBM i. These force settings were introduced at a time (in the 1980’s) when computer hardware was less reliable. Thus, there may have been situations in the old days where applications needed the ability to ensure critical data changes were forced from memory to permanent storage. While this may sound appealing for some applications, be aware that there is a performance cost to using these force settings.

If a non-default value of 1 is specified for the FRCRATIO parameter on a Db2 table, then every new row or changed row will result in the associated data being forced to disk while the application waits. The default behavior enables the program to continue processing while the row data is saved to memory and depends on the IBM i operating system to decide when the best time is to write the changed data to disk. The non-default behavior will also prevent other jobs from performing blocked reads on the Db2 table with the force setting which can also negatively impact system performance.

The FRCACCPTH setting available for Db2 indexes can have a similar negative impact on performance. A setting of *YES can degrade performance since it causes the application program to wait while Db2 forces every change to the access path (i.e., index tree) to disk. In contrast, the default setting of *NO allows the program to continue and on the IBM i operating system to decide when the best time is to write the changed index data from memory to disk.

If you’re really worried about losing critical database changes made by your application, a better alternative is to rely on the IBM i journaling support. With journaling enabled, data changes are first written to the attached journal receiver, and the journal receiver entries are written to storage before the changes are written to the memory copy of the Db2 object. Journaling provides protection against unexpected system outages and depends on the IBM i operating system to efficiently write change data to disk.

Despite the Db2 force parameters primarily being historical artifacts, our IBM Expert Labs team regularly finds Db2 objects with non-default settings for the FRCRATIO and FRCACCPTH parameters. on client systems when performing a Db2 & SQL Assessment for customers.  The SYSFILES IBM i service in QSYS2 makes it easy to find Db2 objects using the non-default settings as the following query demonstrates:

SELECT table_name, table_schema, 
       Force_Write_Ratio, Force_Keyed_Access_Path FROM qsys2.sysfiles
 WHERE system_table_schema  = 'DBSCHEMAX' AND 
       (Force_Write_Ratio IS NOT NULL OR 
         Force_Keyed_Access_Path = 'YES')

Since SYSFILES is an IBM i service based on a user-defined table function, make sure you scope the search request as much as possible to minimize the performance impact of this query.

Hopefully, I’ve been successful in convincing you not to use the Db2 force parameters and save your intuition and feelings for other aspects of application development and design.  Your applications and system will perform best when you rely on your IBM i computer to manage when changed data in memory gets written to permanent storage.

Db2 system names for SQL objects - do I feel lucky?

 In the past, I’ve written about the performance considerations of using longer, more descriptive names for you SQL object names on Db2 for i. SQL object names can be up to 128 characters long. In contract, the names for DDS-created Db2 objects are limited to 10 characters. There’s also a usability consideration when utilizing SQL object or column names greater than 10 characters in length.

 The reason that there’s a usability concern is that IBM i commands (e.g., Save Object) and interfaces support object names up to 10 characters in length. Does that mean you’re out of luck when it comes to using these legacy interfaces with SQL objects which have long object names?  No - Db2 for i “helps” you out by generating a system name which can be used with these non-SQL interfaces when your SQL object has a long name greater than 10 characters in length.

 The good news is that Db2 automatically generates these system names for you  … and the bad news is that Db2 automatically generates these system names for you.  This good news and bad news conundrum is why this entry title asks: Do I feel lucky?

 When an SQL table name is greater than 10 characters in length (e.g., EMPLOYEE_MASTER), Db2 for i will generate a 10 character system name. The Db2 system name generation algorithm takes the first 5 characters of the table (i.e., EMPLO) and then uses a 5 digit number for the suffix. The 5 digit number is incremented for each SQL object that starts with the same 5 characters to guarantee that the system name will be unique within the target schema (or library) that will house the SQL table.

 Assuming that the EMPLOYEE_MASTER is the first table created within the database, the generated system name will be: EMPLO00001. This generated name can be used on IBM i commands and will be used on interfaces or commands that return a list of objects such as the Display Library (DSPLIB) command. Obviously, this generated system name is not user friendly. In addition, the generated system name can vary from system to system depending on the order your SQL objects get created. 

 Let’s say the employee database has a second table named EMPLOYEE_EMAIL in addition to EMPLOYEE_MASTER. On the development system, EMPLOYEE_MASTER was created first and EMPLOYEE_EMAIL table created second. The system names for these two tables respectively would be EMPLO00001 and EMPLO00002.  Let’s assume that on the test system, the table creation process is reversed, and the EMPLOYEE_EMAIL table is created first. As a result, the EMPLOYEE_EMAIL table will have a system name of EMPLO00001 on the test system and EMPLO00002 on the development system.

 This potential difference in system names is not ideal, so it’s strongly recommended to provide a system name for Db2 to use instead of having Db2 for i generate a random name. A system name can be easily provided using the FOR SYSTEM NAME clause on your SQL CREATE statements as shown in the following example.

 CREATE TABLE employee_master FOR SYSTEM NAME empmast ( 
  emp_id CHAR(6),
  emp_fname VARCHAR(20),
  emp_lname VARCHAR(30), 
  emp_dept_number CHAR(3))

 Now, the EMPLOYEE_MASTER table will always have a system name of EMPMAST. Luck is no longer involved in terms of the table creation order determining if the system name is EMPLO00001 or EMPLO00002. Just as important, the user now has a system name which is user friendly. Let’s say you’re looking at output from the DSPLIB command would you rather see EMPMAST or EMPLO00001?

 The same usability issue exists with column names longer than 10 characters in length - the emp_dept_number column is an example of this in the earlier example. The generated system name would be EMP_D00001.  By the way, this ugly generated column name will be what you get by default if you have the compiler generate an externally described data structure based on the table definition using DCL-DS in RPG or the COBOL COPY statement. 

 The good news is that the FOR COLUMN clause can be used to override the generated name as the following example demonstrates. The system name for EMP_DEPT_NUMBER is now EDEPTNUM instead of the ugly and hard to comprehend name of EMP_D00001. 

 CREATE TABLE employee_master FOR SYSTEM NAME empmast ( 
  emp_id CHAR(6),
  emp_fname VARCHAR(20),
  emp_lname VARCHAR(30), 
  emp_dept_number FOR COLUMN edeptnum CHAR(3))

 It should now be clear that the lucky developer is the one using their own system names (EMPMAST & EDEPTNUM) instead of relying on the names automatically generated by Db2 (EMPLO00001 & EMP_D00001).

Finding Inline Candidates - Part II

 Last month, I reviewed how to identify user-defined functions that are a candidate for inlining from the perspective of finding functions that are small and simple with the usage of a system catalog object. Another method is to identify the user-defined functions that are being used most frequently on your system. With this approach, you maximize the performance impact of any user-defined function (UDF) that can be made inline eligible because the UDF is run so frequently. 

This methodology requires a Plan Cache Snapshot. A Plan Cache Snapshot is a point in time copy of the data from the “live” Plan Cache. When a snapshot is created, the result is a Db2 table. The rows in this table contain information about the query implementation along with query execution statistics. The query execution statistics include a count of the number of UDF executions that were not inlined for each SQL statement. Given that this execution information is stored in a Db2 table, we’ll see that it is relatively simple to write a query to find these candidate UDFs.

 

The first piece of information you’ll need for your query is the name of the snapshot table. If the IBM i Access Client Solution (ACS) SQL Performance Center was used to create a snapshot, then you’ll need to figure out the name it generated for the snapshot object. This task is easily accomplished by bringing up the ACS SQL Performance Center and navigating to the Plan Cache Snapshot tab as shown in the figure below.

Now that we have name of the table for the query, let’s look at the following query it will be plugged into. The QQSMINT8 column is the key column for our inline UDF candidate analysis – this is the column that contains the number of times that an SQL statement invoked a UDF that is currently not being inlined by the Db2 engine. This query uses this column on the WHERE clause to only return queries which used non-inlined UDFs that in addition to returning the count of UDFs that were not inlined in the final result set.

SELECT qqsmint8 AS SQL_UDFs_Not_Inined, qvp15f AS Times_Run,
       CASE
         WHEN LENGTH(RTRIM(qqc104)) = 0 THEN 'No Program'
         ELSE RTRIM(qqc104) CONCAT '/' CONCAT qqc103 END AS Pgm, 
        qq1000 AS sql_stmt
  FROM snaplib.snapshot_table
  WHERE qqrid = 1000 AND qqsmint8 > 0
  ORDER BY qvp15f DESC

 

The other QQRID column referenced on the WHERE clause is also critical because the 1000 record type is the row in the snapshot table which contains the query execution statistics. Most of the other record types contain data regarding the query implementation. The SELECT list also includes the QVP15F column which contains the number of times the query was run while the query plan resided in the Plan Cache. This count helps you understand how many times the non-lined UDFs are being invoked. The more times that these UDFs are run, the bigger the performance impact will be on your system if you’re able to make the UDF inline eligible.

 

Here's an example of the output this query can return using a snapshot on my system.  It only found one query that’s dependent on non-lined UDFs.  However, that query has been run over 500 times to this point. 

The QQ1000 column contains the query text, so that you can review and find the name of the non-inlined UDFs for further analysis. Hopefully, this method used along with the previously discussed method will help you identify user-defined functions that can be made inline eligible in order to improve the performance of your applications and servers.

Finding Inline Candidates

 A couple of months ago, I highlighted the performance benefits of Db2’s ability to inline calls to a user-defined function (UDF). While I discussed how to determine whether or not a UDF is inline eligible, I didn’t cover methods to identify UDFs that are candidates to make inline eligible.

Db2’s main requirement for inlining UDFs is that the function logic must be simple. Inlining is not an option for UDFs with complex logic which requires many SQL statements. Therefore, it would make sense to find the UDFs with a small number of SQL statements to determine if they can be made inline eligible. The good news is that the catalogs and services provided by Db2 for i make this candidate UDF search quite easy.

 

The following query uses the SYSFUNCS catalog view which contains a row for every UDF to find the functions that are currently not being inlined. This data set is then joined to the SYSPROGRAMSTAT catalog view which view returns a row for every program object which contains an embedded SQL statement. One key thing to note is that this view uses an underlying service to dynamically generate this list of program objects with calls to a UDF – the SYSPROGRAMSTAT view is not just reading rows from a table like other Db2 catalog views in QSYS2. Thus, it’s critical for performance to limit the scope of the program objects by searching for SQL program objects in a specific library (i.e., system_program_schema='KMEXAMPLE')  instead of Db2 generating a row for every SQL program object on the systems.  A couple years ago, I highlighted the importance of limiting scope for all IBM i services.

SELECT routine_schema, routine_name, number_statements
FROM qsys2.sysfuncs f INNER JOIN
       qsys2.sysprogramstat p
     ON p.system_program_name = specific_name AND
        p.program_schema = routine_schema
  WHERE f.INLINE='NO' AND
        p.system_program_schema = 'KMEXAMPLE' AND 
        p.number_statements <= 5 AND 
        p.routine_type ='FUNCTION' AND 
        p.routine_body  =  'SQL'
  ORDER BY number_statements 

 

Also notice that the SYSPROGRAMSTAT view returns a count of the number of SQL statements embedded in the program object in the NUMBER_STATEMENT column. This column makes it easy to find those UDFs whose logic is composed of a small number of SQL statements. On my system, this query returns the following result set which can now be used to determine if any of these simpler UDFs can be modified to make them inline eligible.

The first step would be reviewing the source of the UDF to determine the feasibility of making the function inline eligible.  The following source code for the CALC1 UDF1 reveals that its logic contains one of the key inhibitors to inline eligibility – an intermediate result variable (i.e., OUT1).

CREATE FUNCTION  CALC1 ( P1 CHAR(4) , P2 CHAR(2) , P3 CHAR(2) ) 
RETURNS CHAR(10)   
LANGUAGE SQL 
DETERMINISTIC 
NOT FENCED
BEGIN 
  DECLARE OUT1 CHAR (10) ; 
  SET OUT1 = CHAR(SUBSTR(P1, 1, 2), 2) CONCAT P2 CONCAT P3; 
  SET OUT1 = REPLACE(OUT1, 'A', 'X') ; 
  RETURN OUT1 ; 
END 

 

An intermediate result variable requires an SQL statement to assign a value to it.  UDFs can be only inlined if they contain a single RETURN statement, so the assignment statement for the intermediate result is a showstopper.  That’s the bad news – the good news is that frequently these intermediate result statements aren’t needed.  Often programmers just utilize these intermediate results to simplify the coding logic without understanding the performance impacts. For this UDF, the intermediate result variable and assignments aren’t needed if the UDF logic is updated to the following:

 

CREATE FUNCTION  CALC1 ( P1 CHAR(4) , P2 CHAR(2) , P3 CHAR(2) ) 
RETURNS CHAR(10)   
LANGUAGE SQL 
DETERMINISTIC 
NOT FENCED
BEGIN 
  RETURN REPLACE( 

           (CHAR(SUBSTR(P1, 1, 2), 2) CONCAT P2 CONCAT P3), 
           'A', 'X') ; 
END 

 

An alternative route to identify which UDFs to target for inlining is to find the UDFs that are being called most frequently on your system that are not being inlined by Db2. Plan Cache Snapshots can be leveraged for this type of analysis, but you’ll have to come back to the blog next month to learn about that approach,  In the interim, you have a method for identifying the UDFs with simple logic on your system that are the easiest candidates for coding changes to make them eligible for inlining by Db2.

SQL FOR Statement Delivers

 Christmas is near which means it is gift giving season, so I thought I’d highlight a powerful capability that the SQL procedural language delivers to programmers – the FOR statement.

 The SQL procedural language (SQL PL) itself was a powerful addition to the SQL Standard because it enables developers to code both their data access and application logic entirely in SQL.  No need to learn the syntax for embedding SQL in  a high-level programming language such as RPG or COBOL, all the coding can be done with SQL.

 

For the most part, the SQL PL support mirrors other programming languages with support for variables, assignment statements, conditional statements, and looping constructs. While other languages support a for loop, the SQL FOR statement is unique in the fact that it streamlines the use of a cursor to process the rows returned by a SELECT statement.

 

In a high-level programming language, the use of a cursor requires a specific logic flow that looks like the following:

DECLARE pgm1cur CURSOR FOR
  SELECT colPK, col2 FROM table1 WHERE col3>100;

OPEN pgm1cur;

FETCH pgm1cur INTO :host1, :host2;

 
while SQLCODE = 0
   /* application logic using colPK and col2 values 

      stored in host1 & host2 variables*/
   FETCH pgm1cur INTO :host1, :host2;
end;

CLOSE pgm1cur;

The cursor needs to be declared and opened. Next, the FETCH statement is used with a looping construct to process each row returned by the cursor and copy the values needed for the application logic into local variables.

The coding for cursor processing is nothing complex, but it is somewhat tedious. The SQL FOR statement stands out because it reduces the amount of code that needs to be created and maintained. As the following example shows, the coding load is reduced because the FOR statement eliminates the need for opening, advancing, and closing the cursor along with the need to copy column values into host variables.

FOR myloop AS pgm1cur CURSOR FOR
  SELECT colPK, col2 FROM table1 WHERE col3>100
DO
     /* application logic using 

        colPK and col2 values */ 

END FOR;

The FOR statement makes cursor usage so simple that one can easily overlook that they’re even using a cursor – let’s look at customer example from a recent IBM Expert Labs engagement which reinforces this. 

A client needed help optimizing the performance of their SQL stored procedures and one of their stored procedures contained the following FOR statement:

FOR myloop AS proc1Cur CURSOR FOR
  SELECT colPK, col1, col2 FROM table1 WHERE rowProcessed ='N'
DO
    SET currentPK = colPK;

     /* application logic using col1 and col2 values */

     UPDATE table1 SET rowProcessed='Y' WHERE colPK = currentPK;
END FOR;

The client’s developer coded a separate UPDATE statement to update the row which the loop was currently processing because they lost track of the fact the FOR statement provides a cursor that can be used for a positioned update.  While the client’s FOR statement functionally worked, performance was sub-optimal because the separate UPDATE statement must search and find the row that needs to be updated. 

 

The positioned update used in this optimized version of the FOR statement eliminates the search and find overhead by just updating the row that the cursor is  currently pointing to.

FOR myloop AS proc1Cur CURSOR FOR
  SELECT colPK, col1, col2 FROM table1 WHERE rowProcessed ='N'
   FOR UPDATE OF rowProcessed
DO
    SET currentPK = colPK;

     /* application logic using col1 and col2 values */

    UPDATE table1 SET rowProcessed='Y' WHERE CURRENT OF proc1Cur;

END FOR;

One performance test that I ran showed the positioned update to be two times faster. I’m guessing that a 2x performance increase would be pretty high on the Christmas wish list for most programmers!

 

That's a wrap for 2025 -  Merry Christmas and I look forward to interacting with all of you in the new year!