|
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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.