RE: User Defined Function Problem

Debbie, 

Looks like database is complaining that 2nd call to your UDF timed out.
You'll need to debug what happens on the 2nd call.  
First of all, make sure your service program is compiled with debug.
Once you connect with OpsNav you'll get your job number at the top of the
Message screen.  On green screen do STRSRVJOB JOB(.....) followed by STRDBG
SRVPGM(......).  Set a breakpoint at the first line of your GETBODATE
function.
Run the statement the second time and see what's passed in.  Step through
your function to see what happens (i.e. if and why it takes so long to
execute 2nd time around).

If you don't like green screen debugger, you can use graphical System
Debugger by clicking Run->Debugger in OpsNav.  There is a slight learning
curve so green screen is probably a better option if speed is imperative.

Elvis

-----Original Message-----
Subject: User Defined Function Problem

I have a problem that I'm not quite sure how to even begin to describe but
here goes.

Here is the statement that I am running:

SELECT A.ASCOMP AS COMPANY,
       TRIM(A.ASBECD) AS ITEM,
       '   ' AS SKU,
       CWMPINH.GETBODATE(A.ASCOMP, CAST(A.ASBECD AS CHARACTER(12)), 
                         CAST(' ' AS CHARACTER(14)))
       FROM CWMPDTA.INIOFRL1 A
       WHERE A.ASCOMP =001
       AND TRIM(A.ASCACD) IN ('6W4')

My function GETBODATE is a service program comprised of a module which
prepares some parameters and simply calls an under lying RPGLE program which
passes back a date.

My function works great the first time it is executed from Ops Nav.  It may
even work a couple more times but if the user closes their session and opens
a fresh one.  The next time the statement is executed, they get an error.

Here's the error:  SQL State: 38501
Vendor Code: -443
Message: [SQL0443] *N

The under lying joblog says basically - 


     User-defined function error on member INIOFRL1.


 


                         Additional Message Information


 


 Message ID . . . . . . :   CPF503E       Severity . . . . . . . :   30


 Message type . . . . . :   Sender copy


 Date sent  . . . . . . :   04/13/06      Time sent  . . . . . . :
13:43:09  

 


 Message . . . . :   User-defined function error on member INIOFRL1.


 Cause . . . . . :   An error occurred while invoking user-defined function


   GETBODATE in library CWMPINH. The error occurred while invoking the


   associated external program or service program WEB010R1 in library
CWMPINH, 

   program entry point or external name WEB010R1, specific name GETBODATE.
The 

   error occurred on member INIOFRL1 file INIOFRL1 in library CWMPDTA. The


   error code is 3. The error codes and their meanings follow:


     1 -- The external program or service program returned SQLSTATE 00000.
The 

   text message returned from the program is:  .


     2 -- The external program failed before it completed.


     3 -- The database timed out waiting for the program to return. The
timeout

   value used by the database was 0 minutes and 30 seconds.


 



I can run this a million times from green screen SQL without a problem.

Any ideas??????

Debbie Kelemen
Sr. Programmer / Analyst
Chefs Catalog
(719) 272-2617
dkelemen@xxxxxxxxxxxxxxxx