RE: trigger, UDF and SQL path

Tough to say what the culprit is without more info.

I assume there is a PGMC somewhere in the picture, one that runs PGMA, then
PGMB. Is it possible PGMC is compiled with *SQL naming? Or PGMB?

If as you say SQL trigger is causing it because of *SQL naming, perhaps you
can change it so it uses *SYS naming. You can do that by putting SET OPTION
NAMING =*SQL just before the BEGIN statement in the trigger source.

All that said, UDFs are searched by SQL path. You can set the SQL path
explicitly, using SET OPTION statement, i.e.:

SET OPTION SQLPATH='QSYS,QSYS2,QGPL,MYLIBRARY,YOURLIBRARY'

That way you can be sure no matter what naming is used, your UDF is always
located by the PGMB. SQL path is different from schema and libl path as it
pertains to UDFs, SPs and like.

Lots of speculation I'm afraid. Perhaps if you share the message ids you're
getting we'll be able to narrow down on it.

Well, let us know what it turns out to be.

Elvis


Mike Cain - DB2 for i5/OS Temporary Indexes - The Good, The Bad, The Ugly
October 16
2007 System i Fall Technical Conference | Orlando | November 4-7
Celebrating 10-Years of SQL Performance Excellence on IBM System i, eServer
iSeries and the server affectionately known as the AS/400

-----Original Message-----
Subject: trigger, UDF and SQL path

I have a UDF created in a lib that is on the system portion of my libl, so
that it is always available to all users. The UDF is unqualified in the
SQL SELECT stmt in my pgm and is always found. I have added a SQL trigger
to a file to monitor changes to a specific field and write an audit rec.
My issue: PGMA to change the field value is run and the trigger is fired
and the pgm ends. PGMB is called that uses the UDF and it cannot find the
UDF (Function UDF1 not found in library *N. ) Additional error info
states: A library name of *N indicates that the current SQL path was
used. So, somehow the firing of this trigger has caused my SQL stmts in
PGMB to no longer use the libl. It appears to be an issue with naming
(*SYS v. *SQL) but I am not sure where to look. Any help is appreciated.