Finding Table functions with SYSFUNCS

In every IBM i recent release and Technology Refresh a few new Audit Journal Table extract functions are added. I wanted to see which one were on the IBM i partition I was using. I was tempted to use the OBJECT_STATISTICS Table function to get a list of them:

01 SELECT OBJNAME,OBJLONGNAME 02 FROM TABLE(QSYS2.OBJECT_STATISTICS('SYSTOOLS','*ALL','AUD*'))

Line 1: I am only interested in the system object name, OBJNAME, and the long (SQL) object name, OBJLONGNAME.

Line 2: The Audit Journal Table functions are in the SYSTOOLS library. I am using the wild card for the object name, in the third parameter, to find all the objects that start with 'AUD'.

The results were:

OBJNAME OBJLONGNAME -------- ------------- AUDIT_AD AUDIT_JOURNAL_AD AUDIT_AF <NULL> AUDIT_AP AUDIT_JOURNAL_AP AUDIT_AU AUDIT_JOURNAL_AU AUDIT_AX AUDIT_JOURNAL_AX AUDIT_CA <NULL>

I was fortunate that the first few results is what I am looking for, as any object that starts 'AUD' will be included in the results. I was running this in IBM i 7.5 and I am surprised that some of the long names are null. I did check the same statement in a partition running IBM i 7.6 and all of the results had long names:

OBJNAME OBJLONGNAME -------- ------------- AUDIT_AD AUDIT_JOURNAL_AD AUDIT_AF AUDIT_JOURNAL_AF AUDIT_AP AUDIT_JOURNAL_AP AUDIT_AU AUDIT_JOURNAL_AU AUDIT_AX AUDIT_JOURNAL_AX AUDIT_CA AUDIT_JOURNAL_CA

I really wanted a better way to just a list of the Table functions. Fortunately, there is a SQL View just for this, SYSFUNCS.

SYSFUNCS lists all functions:

• Scalar functions
• Column functions
• Table functions

If I wanted to see all functions I would use the following:

01 SELECT * FROM QSYS2.SYSFUNCS

I am not going to list the results here. I do recommend that you do execute this statement to see what information is available.

If I want to list only Table functions the column FUNCTION_TYPE would be 'T'. For Scalar functions I would use 'S', and 'C' for Column functions.

If I only want to list the Audit Journal Table functions I would use the following:

01 SELECT ROUTINE_NAME 02 FROM QSYS2.SYSFUNCS 03 WHERE FUNCTION_TYPE = 'T' 04 AND ROUTINE_SCHEMA = 'SYSTOOLS' 05 AND ROUTINE_NAME LIKE 'AUDIT_JOURNAL_%'

Line 1: I only want the ROUTINE_NAME, which is the long name of the Table function.

Line 3: I only want to list Table functions.

Line 4: The Audit Journal Table functions are all in the SYSTOOLS library.

Line 5: And they all start with...

The first few results look like:

ROUTINE_NAME ---------------- AUDIT_JOURNAL_AD AUDIT_JOURNAL_AF AUDIT_JOURNAL_AP AUDIT_JOURNAL_AU AUDIT_JOURNAL_AX AUDIT_JOURNAL_CA

If I wanted to check if the two new ones added as part of IBM i 7.6 TR1 and 7.5 TR7 I can use the following:

01 SELECT ROUTINE_NAME 02 FROM QSYS2.SYSFUNCS 03 WHERE FUNCTION_TYPE = 'T' 04 AND ROUTINE_SCHEMA = 'SYSTOOLS', 05 AND ROUTINE_NAME IN ('AUDIT_JOURNAL_RP', 06 'AUDIT_JOURNAL_VO')

The results show I have both:

ROUTINE_NAME ---------------- AUDIT_JOURNAL_RP AUDIT_JOURNAL_VO

I can also use SYSFUNCS to show that one of my favorite Scalar functions, QCMDEXC, is present:

01 SELECT ROUTINE_NAME,FUNCTION_TYPE 02 FROM QSYS2.SYSFUNCS 03 WHERE ROUTINE_NAME = 'QCMDEXC'

Line 1: I want the Scalar function name and Function Type in the results.

Line 3: Where all functions have the routine name 'QCMDEXC'.

One result is returned:

ROUTINE_NAME FUNCTION_TYPE ------------ ------------- QCMDEXC S

'S' identifies this as a Scalar function.

 

You can learn more about the SYSFUNCS SQL View from the IBM website here.

 

This article was written for IBM i 7.6, and should work for some earlier releases too.