PROBLEM IN CALLING JAVA UDF's 
Author Message
 PROBLEM IN CALLING JAVA UDF's

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.


Tue, 10 Aug 2004 05:22:17 GMT
 PROBLEM IN CALLING JAVA UDF's

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



Tue, 10 Aug 2004 05:52:20 GMT
 PROBLEM IN CALLING JAVA UDF's
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


Wed, 11 Aug 2004 00:47:21 GMT
 PROBLEM IN CALLING JAVA UDF's


Quote:
> Hi Dirk,
>                      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.

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



Wed, 11 Aug 2004 01:39:53 GMT
 PROBLEM IN CALLING JAVA UDF's
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



Wed, 11 Aug 2004 06:47:05 GMT
 PROBLEM IN CALLING JAVA UDF's
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



Sat, 14 Aug 2004 02:23:50 GMT
 PROBLEM IN CALLING JAVA UDF's

Quote:

> 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.


Tue, 17 Aug 2004 07:15:04 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. CAN WE CALL MQSeries OBJECTS FROM JAVA UDF's

2. UDF: Java VM can't find java.lang.System

3. Calling a UDF in informix from a Java App

4. Resultset problem when using JAVA to call MS SQL Server's stored procedure

5. calling udf's from a calculated member

6. HELP: Can't call UDF as sub-query

7. calling UDP or UDF's with created functions

8. About java UDF's

9. problem calling an Interbase UDF

10. Problem with Trigger calling UDF.

11. Java UDF newbie questions/problem

12. Java UDF problem


 
Powered by phpBB® Forum Software