DB2 for i 7.1 TR10 and i 7.2 TR2 Features, Part 2

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:

1. Reset statistics indicator–accepts YES or NO; controls whether or not the job statistics are reset.
2. Subsystem Filter–comma delimited list of subsystems
3. Job Name Filter–Special values include:
   *–Only information for the current job is returned.
   *ALL–Information for all jobs is returned
   *CURRENT–Information for all jobs with a job name that is the same as the current job is returned
   *SBS–Information for all active subsystem monitors is returned
   *SYS–Information for all active system jobs is returned
4. Current User Filter–comma delimited list of users to include in the results. As the name implies, the current user, not the job user, is used for the filter

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:

-- 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

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

CALL QSYS2.SET_JVM('734443/QUSER/QZDASOINIT','GENERATE_SYSTEM_DUMP') ;

/home/mike/javacore.20150915.225544.286.0001.txt

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