I had an issue earlier today. I logged into SQL and set my environment to use *SQL naming. The old library.file syntax vs the IBM i syntax of library/file

I then wanted to test one of the SQL Functions I include in the new Query File tool. This one happen to be the CVTDATE routine. It converts zoned, packed and text based fields that contain a MDY, YMD, or DMY date value into a true DATE data-type.

So I entered:

RUNSQLF 'SELECT CVTDATE(orddte,'ymd') as Order_Date, CustNo, Company' OUTPUT(*PRINT)

What do I get back? CVTDATE in *LIBL type *N not found. 

What the heck? I walked away and after a while when I came back to continue, I searched for SQL FUNCTION LIBARY LIST on BING. That led me to this page on the IBM website that said:

The initial value of the CURRENT PATH special register is *LIBL if system naming was used for the first SQL statement run in the activation group. The initial value is "QSYS","QSYS2", "X" (where X is the value of the USER special register) if SQL naming was used for the first SQL statement.

Sure enough, I had my NAMING set to *SQL when I started that session, so it ONLY looks into QSYS and QSYS2 for SQL function. In addition, it looks in the library of the same name as the User, if that library exists. So my functions, which are on the library list and exist in both COZQRYF and COZTOOLS libraries, were missing in action.

Of course the fastest way to fix this is to change the naming setting to *SYS, signoff/signon and go!  And I did that and it worked.

But then on that same IBM webpage, I read that using the SET PATH or the more verbose SET CURRENT FUNCTION PATH command may be used to change the "library list" (if you will) that SQL uses to locate functions.  So I ran this statement:

SET CURRENT FUNCTION PATH=*LIBL

And now I was getting the results I expected.

The moral of this story is that when you can't locate your SQL functions, try using the SET PATH or SET CURRENT FUNCTION PATH statement to set the path to *LIBL and try your SQL statement again.