Hi all,
        I have two system on both running db2 v 7.2 on win 2000.One
system is local to me and another is remote.On my local system
triggers r calling java UDF's and passing data.But in my remote system
triggers r working but they r not  calling java UDF's.In the remote
system if i open the db2diag.log i am getting following message,
      ****************************************
       The number of processors on this machine exceeds the defined
entitlement of "1" for the product "DB2 Enterprise Edition".  The
number of
processors on this machine is "4".  You should purchase additional
processor
entitlements from your IBM representative or authorized dealer and
update your
license .
**************************************************
                   I am not sure because of this problem my java UDF's
r not called by triggers.In this if any one can help me out it will be
a great help for me.I want to know is there any way of checking any
log why my trigger is not calling java UDF.
Thanks
Kiran.

No, this message is not the reason for the Java UDFs not being called on
your trigger. A common mistake for this is that the user forgets to declare
the UDF with "external action" and the optimizer thinks its not necessary to
execute it.

OTOH, it would be good if you buy more licenses so that we IBMers have some
food on the table for tomorrow. ;-)

Dirk

Hi Dirk,
          Thanks for your reply.There was some classpath problem,the
java UDF's are working now.I have one more question for u,
                    I have table in DB2 with all columns with
character data type.For this table the trigger is calling java UDF and
passing the data.There is another table with both character and
Timestamp data types,for this the trigger is not calling the java
UDF.The following is the list of columns with data types in the table,
            Table name----ACC
            COLUMNS-----1.ACC_NUM_MST CHARACTER(10)
                        2.CRE_ID      CHARACTER(32)
                        3.CRE_TMS     TIMESTAMP
                        4.UPD_ID      CHARACTER(32)  
                        5.UPD_TMS      TIMESTAMP
Here is the sql i am using for registering the java UDF for above
table
    CREATE FUNCTION TestUDF(CHARACTER(10),CHARACTER(32),TIMESTAMP,CHARACTER(32),TIMESTAMP)"
+" RETURNS int" +" EXTERNAL NAME 'TestDB!TestUDF'" +" LANGUAGE java "
+" PARAMETER STYLE db2general " +" DETERMINISTIC " +" FENCED " +" NOT
NULL CALL " +" NO SQL " +" EXTERNAL ACTION " +" NO SCRATCHPAD " +" NO
FINAL CALL " +" ALLOW PARALLEL " +" NO DBINFO");
WHAT MY JAVA UDF HAS TO DO
--------------------------
         When ever any application inserts , updates or deletes any
record from the table ACC,then the trigger will fire and that trigger
passes the inserted or updated or deleted record to the java UDF.
                         When i am inserting data into table ,trigger
is not passing data but the data is inserted into the table.I
think,the problem is with the TIMESTAMP.
                     Pl let me know,how we have to deal with
TIMESTAMP, DATE,CLOB,DECIMAL and BLOB  data types when we are
registring java UDF.Pl look into it and reply back when have time its
very much needed to me, to go ahead with my work.
Thanks
sashi

have you tried calling the UDF as it it is called in your trigger? E.g. if
you do and insert with a UDF call try calling the UDF with the some example
values in the insert.

It could be that you use the wrong types in the UDF for TIMESTAMP. If you
set the diaglevel to 4 it will say in the db2diag.log which signature DB2
expects.

Dirk

Hi Dirk,
         I have set the diaglevel to 4 and tried but, NO ERROR'S in
the db2diag.log.Any how i am sending u the java UDF code ,trigger code
and the registering SQL.I hope u will get a answer after looking into
my code,
TRIGGER SQL
----------
CREATE TRIGGER GOSDDL.DEL_ACC AFTER  DELETE  ON GOSDDL.ACC
REFERENCING  OLD AS RE  FOR EACH ROW  MODE DB2SQL BEGIN ATOMIC
VALUES(TimestampUDF(RE.ACC_NUM_MST,RE.CRE_ID,RE.CRE_TMS,RE.UPD_ID,RE.UPD_TMS));
END

SQL FOR REGISTRING JAVA UDF
--------------------------
CREATE FUNCTION TimestampUDF(CHARACTER(10),CHARACTER(32),TIMESTAMP,CHARACTER(32),TIMESTAMP)"
+
                                               " RETURNS int" +
                                               " EXTERNAL NAME 'TimestampDB!TimestampUDF'"
+
                                               " LANGUAGE java " +
                                               " PARAMETER STYLE db2general " +
                                               " DETERMINISTIC " +
                                               " FENCED " +
                                               " NOT NULL CALL " +
                                               " NO SQL " +
                                               " EXTERNAL ACTION " +
                                               " NO SCRATCHPAD " +
                                               " NO FINAL CALL " +
                                               " ALLOW PARALLEL " +
                      " NO DBINFO");

JAVA UDF CODE
-------------
import COM.ibm.db2.app.*;
import COM.ibm.db2.jdbc.app.*;
import java.sql.*;
import java.io.*;
import java.net.*;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;

class TimestampDB extends UDF
{

  public void TimestampUDF(String ACC_NUM_MST,String CRE_ID,Timestamp
      CRE_TMS,String UPD_ID,Timestamp UPD_TMS,int outvar) throws
Exception
  {                     String temp1 =new String();
                        String s = new String();
                         try
                                {
                         File out1 = new File("out.xml");
                        FileWriter lWriter = new FileWriter(out1);
                        lWriter.write("into udf");
                        temp1 =(ACC_NUM_MST+CRE_ID+""+CRE_TMS.toString()
+UPD_ID+""+UPD_TMS.toString());
                        lWriter.write(temp1);
                        lWriter.close();
                        set(6,0);
                                }
                                                catch(IOException e)
                                                {
                                                        System.out.println("Some IO Error :"+e.getMessage());
                                                }
                                                //String udfserverurl ="rmi://10.2.7.193/UdfTest";
                                            //UdfServerIntf udfserverintf =
(UdfServerIntf)Naming.lookup(udfserverurl);
                        //udfserverintf.add(temp1);
                                }
                        }      

   Thanks for your reply,
Thanks
sashi

sashi,

did you try to call your UDF without the trigger as I suggested? Did it
work?

You chose a DB2GENERAL style UDF and these pass TIMESTAMPs as Strings into
Java (see App dev guide appendix C).

Change the signature of your UDF to take Strings parameters in the position
where you pass TIMESTAMPs and test it without the trigger (no need to
complicate things).

Dirk

Thanks very much Dirk, UDF's r working.By changing the datatype in
javaUDF to string for timestamp............it works.