DB2 for i 7.1 TR10 and i 7.2 TR2 Features, Part 2
October 20, 2015 Michael Sansoterra
Continuing on from Part 1, this tip covers some new features recently made available in DB2 for i.
SQL Function Parameter Limit
User-defined functions capabilities in DB2 for i have received a huge boost in that the parameter list limit for a scalar function has jumped from 90 parameters to a whopping 1024 parameters. Likewise, the combined input parameter and return columns limit for a table function has increased from 125 to 1025.
Off the top of my head I can’t think of an example when I would want to pass more than 90 parameters into a scalar function. However, for table functions I can think of a few examples where this increased parameter and return column limit would be useful. For instance, say you are building a table function to parse a comma delimited text file (CSV) with the potential for a large number of columns. The increased limit can definitely come in handy if the table function needs to return more than 125 columns.
RUNSQLSTM Code Format Improvement
A great benefit of the DB2 for i catalog views is that they store the code used to create various SQL objects such as stored procedures, triggers, views, and functions (unless the code has been obfuscated.) Tools such as System i Navigator give developers the ability to easily retrieve the SQL source for these objects from the database’s catalog.
In the past, when creating code-based SQL objects using the RUNSQLSTM command, the subsequent retrieval of the source code from the system catalogs may have been difficult to read because the line formatting wasn’t done correctly. This has been corrected so that the source code line formatting is preserved as expected.
DB2 for i Services
The services views and functions continue to pile up! These services offer admins and developers a great way to peek into OS-related functions without having to undertake the complexity of API programming. Who wants to bother with user spaces any more to get a list of system objects?! Here are a few of the newly available services:
ACTIVE_JOB_INFO Table Function
This user-defined table function (UDTF) resides in the QSYS2 schema and allows you to query the active jobs on the system, analogous to the WRKACTJOB IBM i OS command. The function accepts four parameters:
This sample query returns job information about all active jobs for users MIKE and JOHN with an elapsed disk I/O count > 10000 (since the last time the statistics were reset).
SELECT JOB_NAME,SUBSYSTEM,AUTHORIZATION_NAME,JOB_STATUS,MEMORY_POOL, CPU_TIME,ELAPSED_TOTAL_DISK_IO_COUNT FROM TABLE(ACTIVE_JOB_INFO('NO','','','MIKE,JOHN')) ACT_JOBS WHERE ELAPSED_TOTAL_DISK_IO_COUNT>10000
The result will look something like this:
The ability to easily query active jobs and test for excessive CPU or disk usage with this UDTF can help identify jobs that are sucking an inordinate amount of resources.
Here is one more example:
-- Example: retrieve information for all jobs in subsystems -- QUSRWRK, QBATCH and QINTER for user MIKE -- and do not reset the statistics -- SELECT * FROM TABLE( ACTIVE_JOB_INFO('NO','QUSRWRK,QBATCH,QINTER','*','MIKE') ) ACT_JOBS
This function returns many useful columns, so be sure to review this DeveloperWorks page that explains all of its intricacies.
This services view gives SQL personnel a clear view into the state of the IBM i job scheduler (similar to using WRKJOBSCDE).
SELECT SCHEDULED_JOB_NAME,SCHEDULED_TIME, COALESCE(VARCHAR(SCHEDULED_DATE), SCHEDULED_DAYS, SCHEDULED_DATE_VALUE) AS SCHEDULE, NEXT_SUBMISSION_DATE,STATUS, DESCRIPTION,COMMAND_STRING, LAST_ATTEMPTED_SUBMISSION_TIMESTAMP FROM QSYS2.SCHEDULED_JOB_INFO
The results will look something like this:
This view allow easy programmatic access to the job scheduler and can be used to search for programs or libraries within the command string, for jobs that were submitted but failed, jobs that are on hold, etc. This type of functionality is an administrator’s best friend because you can query precisely what you need without having to sift through everything.
QSYS2.JVM_INFO View and SetJVM Stored Procedure
Working with Java can be challenging when there are problems with the code. Whereas in a traditional 5250 batch or interactive job, there are a number of tools that give insight into what is happening within the job (call stack, activation groups, open files, relative record numbers, job definition attributes, etc.) It has been much harder to peer into the internals of a Java job. IBM i 6.1 introduced the Work with JVM Jobs (WRKJVMJOB) command to make peeking into a Java job easier. The new SET_JVM stored procedure can help as well.
For instance, if you encounter a job with failing Java code, you can use procedure SET_JVM to issue a Java dump:
CALL QSYS2.SET_JVM('734443/QUSER/QZDASOINIT','GENERATE_SYSTEM_DUMP') ;
The Java dump will be placed on the IFS in the user’s home folder. The name starts with “javacore” followed by a date and timestamp. Here is an example output file:
Although too large to show here, the dump has quite a few useful tidbits including OS information, Java environment information including version, starting parameters, environment variables, memory usage, threads, garbage collector info, and the default loaded libraries (including jar file path) and classes.
The procedure accepts two parameters: the qualified job name (with the special value of * being the current job), and the action to perform. The supported actions are shown below (along with the descriptions I filched from this this DeveloperWorks page:
GC_ENABLE_VERBOSE–Enable verbose garbage collection detail.
GC_DISABLE_VERBOSE–Disable verbose garbage collection detail.
GENERATE_HEAP_DUMP–Generates information about the JVM’s heap. Generates a dump of all the heap space allocations that have not yet been freed.
GENERATE_SYSTEM_DUMP–Generates system detail for the JVM. Generates a binary format raw memory image of the job that was running when the dump was initiated.
GENERATE_JAVA_DUMP–Generates Java detail for the JVM. Generates multiple files that contain diagnostic information for the JVM and the Java applications running within the JVM.
Along the lines of reviewing Java jobs and troubleshooting, the JVM_INFO view can be used to get an overview of the JVM jobs active on your IBM i. Say you just changed the JDK default version on your partition to Java 8. An IPL was done and you want to know what JVMs are still on the system running a version other than 8. The following query will answer this question by showing all jobs running a JVM other than 8:
SELECT JOB_NAME,START_TIME,JAVA_THREAD_COUNT, USER_DIRECTORY,CURRENT_HEAP_SIZE, MALLOC_MEMORY_SIZE,BIT_MODE,JAVA_HOME FROM QSYS2.JVM_INFO WHERE JAVA_HOME NOT LIKE '%jdk80% -- inspect the JDK path
The results looks something like this (and I changed the heading names and omitted the JAVA_HOME column):
As you examine the columns returned by the view, it’s clear there are plenty of worthwhile things to be gleaned from the results including jobs that are taking a large amount of memory, or jobs running the 32-bit vs 64-bit architecture.
Because Java jobs are often hosted by a job other than the calling interactive or batch job, this view will come in handy as it’s often hard to know where Java code is running on IBM i. For example, when I started a Java program from an interactive job using QSHELL (or using RUNJVA command), a job named qp0zspwp ran the Java code rather than the interactive job itself.
In contrast, when an RPG program invokes Java code using the Java Native Interface (JNI), a JVM will be started within the current job. DB2 Java routines will also run the Java code in the same job. You can verify this by querying the services view SYSTOOLS.GROUP_PTF_CURRENCY (which uses the Java based HTTP functionality) and then querying JVM_INFO. You will see that the job that ran the query now has a JVM associated with it.
With IBM implementing Java code in standard DB2 functionality and RPG programmers crossing over into the Java world with JNI, not to mention the number of popular Java based applications on IBM i, it is important to know about the resources the JVMs on your IBM i are consuming. JVM_INFO is a great tool to help collect the information.