Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
Locating SQL FUNCTIONS on the Library List
has no ratings.
Published: 27 Aug 2014
Revised: 29 Aug 2014 - 970 days ago
Last viewed on: 25 Apr 2017 (1980 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Locating SQL FUNCTIONS on the Library List Published by: Bob Cozzi on 27 Aug 2014 view comments(6)

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.

 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: Ringer
Premium member *
Comment on: Locating SQL FUNCTIONS on the Library List
Posted: 2 years 7 months 29 days 8 hours 45 minutes ago

Are you saying you are using *SQL naming but found a way to override the default *LIBL to find functions? If so, nice, I didn't know that was possible. I thought *SQL naming was limited to the 3 Libraries or an explicit library name (MYLIB.MYFUNC). 

Sorry, still on my 1st cup of coffee.

Chris Ringer

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Locating SQL FUNCTIONS on the Library List
Posted: 2 years 7 months 29 days 8 hours 18 minutes ago

Yep, that's what I'm saying, Chris. And my coffee isn't working today.

Posted by: Ringer
Premium member *
Comment on: Locating SQL FUNCTIONS on the Library List
Posted: 2 years 7 months 29 days 7 hours 20 minutes ago

So I have to ask, why not use *SYS naming? Not being critical, just wondering if there's a lesson there to be learned too. Thanks. 

Chris Ringer

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Locating SQL FUNCTIONS on the Library List
Posted: 2 years 7 months 29 days 7 hours 4 minutes ago
Edited: Thu, 28 Aug, 2014 at 10:21:44 (971 days ago)

I sometimes use *SQL naming when I connect to a remote system. You can use the 3-tier naming and avoid doing a Connect To. For example:

SELECT a,b,c FROM rmt.lib.file order by b

But this requires *SQL naming.

Posted by: Ringer
Premium member *
Comment on: Locating SQL FUNCTIONS on the Library List
Posted: 2 years 7 months 29 days 6 hours 31 minutes ago

Oustanding info. Thanks. 

Posted by: Ringer
Premium member *
Comment on: Locating SQL FUNCTIONS on the Library List
Posted: 2 years 7 months 29 days 4 hours 55 minutes ago

SELECT * FROM OTHERSYS.RINGERLIB.MYTABLE 

I try the 3 tier naming and get SQL0114 "Relational database OTHERSYS not the same as current server THISSYSNAM". 

On v6.1. How do I get this syntax to work? I changed my naming to *SQL in STRSQL. Thanks.

Chris Ringer