AW: Controlling Library List in User Defined Functions

Hi,

That's the problem with SQL Naming and you are using SQL-Naming!
With SYS-Naming the library list is searched to find all objects (tables,
views, indexes, stored procedures and user defined functions) you want to
access.

With SQL-Naming the path is only searched to find "programs", that means to
find stored procedures and user defined functions, but not for to find
tables or views.

If no current schema is specified, the tables, views and indexes are
searched in a library with the same name as the user is signed on.
If you want to read tables, views and indexes from an other library, this
library must be set with "SET CURRENT SCHEMA".
With SQL-Naming you only unqualified access of tables or views is only
possible for a single schema.
If you want to access database objects located in different schemas, you
either have to qualify the objects or use aliases that point to the objects
in the other library.

We often have discussions about SQL and SYS-Naming and there are a lot who
advocate SQL-Naming.
This is ok, if you plan to access not only DB2 on System i but also other
databases such as ORACLE or MYSQL.
But IMHO as long as we work solely on the System i, I prefer and use
SYS-Naming.

I don't know which environment you use to create your function, but in
either way you need to set then naming:
1. If you are using STRSQL, just press F13, Option 1 and change the naming
to *SYS
2. If you are using Run an SQL Statement, you can change the naming under
JDBC-Setup
3. If you are using i Navigator wizards to create your function, you need to
change the properties.
(Under i Navigator 6.1 the naming can be changed as follows: Position on
Databases, Right click and Preferences
Before 6.1 the naming could be changed somewhere in the properties as
far as I remember it was under Connections)

Contrary to embedded SQL, in SQL programming the naming cannot be set in the
SET OPTION-Statement.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Coyle, Stephen F.
Gesendet: Tuesday, 07. October 2008 15:33
An: midrange-l@xxxxxxxxxxxx
Betreff: Controlling Library List in User Defined Functions

Hi All,

I have a user defined function that I'm trying to use within an
SQL statement. Ideally I would want the function to use the library list
of the environment it is called from. I'm not having any luck there and
thought someone might point me in the right direction. Below is the
function definition and the error I get when attempting to run it.
From what I've read it looks like it is using SQL naming
convention where the library used is equal to the user profile. I want
to use system naming convention. My defaults within STRSQL (where I'm
testing) is set to *SYS naming convention.

Thanks in advance...
- Steve

FUNCTION DEFINITION:

SET PATH "QSYS","QSYS2","SCOYLE" ;
CREATE FUNCTION GBCMODS.RTVNRACCTCLASS (
INOBJ CHAR(6) ,
INSUB CHAR(8) )
RETURNS CHAR(3)
LANGUAGE SQL
SPECIFIC GBCMODS.RTVNRACCTCLASS
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
NO EXTERNAL ACTION
NOT FENCED
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN

DECLARE RECCOUNT INT ;
DECLARE CLASS CHAR ( 3 ) NOT NULL DEFAULT ' ' ;
SET PATH = GBCMODS , GBCCOM ;

IF SUBSTR ( INSUB , 4 , 5 ) = '93720' OR
INSUB >= '92000000' AND INSUB <= '92099999' AND INOBJ = '4215 '
OR
INSUB >= '99000000' AND INSUB <= '99099999' AND INOBJ = '4215 '

THEN
SET CLASS = 'BON' ;
ELSE
SELECT COUNT ( * ) INTO RECCOUNT FROM F0005 WHERE DRSY = '56 ' AND DRRT
= 'JS' AND TRIM ( DRKY ) = SUBSTR ( INSUB , 4 , 5 ) ;
IF RECCOUNT > 0 THEN
SET CLASS = 'MIS' ;
ELSE
SELECT COUNT ( * ) INTO RECCOUNT FROM F0005 WHERE DRSY = '56 ' AND DRRT
= 'JO' AND TRIM ( DRKY ) = TRIM ( INOBJ ) ;
IF RECCOUNT > 0 THEN
SET CLASS = 'PAY' ;
ELSE
SET CLASS = 'OTH' ;
END IF ;
END IF ;
END IF ;

RETURN CLASS ;

END ;


ERROR IN JOBLOG:

F0005 in SCOYLE type *FILE not found.