Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2013
    Location
    India
    Posts
    246

    Unanswered: java sp in db2 procedures - error

    Hello all,

    i am using DB2 v10.1.0.3 on rhel 5

    I have a db2 jar containing a java sp with only a main function with out any arguments. while deploying that in db2 inside a db2 procedure it is giving error.

    i am not able to get enough info on web to find the reason behinds.

    Steps i followed are as follows:

    1. db2 "CALL "SQLJ"."DB2_INSTALL_JAR"("file://datahome/db2inst1/procedures/dsc_expiry_update.jar", "test_dsc",0)"
    Return Status = 0

    2. CREATE PROCEDURE EPROC.TEST_DSC_PROC()
    SPECIFIC TEST_DSC_PROC
    DYNAMIC RESULT SETS 0
    DETERMINISTIC
    LANGUAGE JAVA
    PARAMETER STYLE JAVA
    NO DBINFO
    THREADSAFE
    MODIFIES SQL DATA
    PROGRAM TYPE SUB
    EXTERNAL NAME 'dsc_expiry_update!main ()'
    ;


    After this error is returned as follows:


    Lookup Error - DB2 Database Error: ERROR [42724] [IBM][DB2/LINUX] SQL4306N Java stored procedure or user-defined function "EPROC.TEST_DSC_PROC", specific name "TEST_DSC_PROC" could not call Java method "main", signature "()V".

    I not able find out the reason or any error i done while defining the definition of java sp is given below:

    ---------------------------------------------------------------------------------
    import java.io.*;
    import java.net.*;
    import java.util.*;
    import java.sql.*;


    //import java.text.NumberFormat;


    public class dsc_expiry_update {

    public static void main(String args[]){

    Connection newCon = null;
    Statement Statmt,stmt0;
    PreparedStatement st1;
    //PreparedStatement pstmt=null,pstmt1=null,pstmt2=null;
    ResultSet Res=null,res1=null,res2=null,res3=null,rs0=null;

    java.util.Date cur_date= new java.util.Date();

    try
    {

    //Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
    //newCon = DriverManager.getConnection("jdbc:db2:dbmstc", "db2inst1", "db2inst1");
    Class.forName("com.ibm.db2.jcc.DB2Driver");
    newCon = DriverManager.getConnection("jdbc:db2://localhost:50001/DBMSTC","db2instn", "mstc@123");
    java.sql.Statement stmt=newCon.createStatement();
    stmt.execute("set schema eproc");
    stmt.close();
    System.out.println("Connected successfully to eproc schema "+cur_date);



    }catch(Exception E){
    System.out.println( "[jdbcServlet.service1():" + E + "]" );
    //return;
    }// end try


    try{
    Statmt = newCon.createStatement();

    try{

    stmt0 = newCon.createStatement();
    //rs0 = stmt0.executeQuery("select USER_REF_ID,REF_ID,USER_TYPE from EP_DIGITAL_CERT_INFO where STATUS='a' and (CERT_EXPIRY_TIME<current timestamp or DE_EXPIRY_TIME<current timestamp)");
    rs0 = stmt0.executeQuery("select USER_REF_ID,REF_ID,USER_TYPE from EP_DIGITAL_CERT_INFO where STATUS='a' and CERT_EXPIRY_TIME<current timestamp");
    while(rs0.next())
    {
    String s8 = rs0.getString(1);
    String dscchrno = rs0.getString(2);
    String usertp = rs0.getString(3);
    if(usertp==null) usertp="";

    st1= newCon.prepareStatement("update EP_DIGITAL_CERT_INFO set STATUS='e' where REF_ID="+dscchrno);
    st1.executeUpdate();
    st1=null;

    if(usertp.equals("v"))
    {
    st1= newCon.prepareStatement("update EP_VENDOR_MASTER set DSC_STATUS='n' where REF_ID="+s8);
    st1.executeUpdate();
    }
    else
    {
    st1= newCon.prepareStatement("update EP_PM_USERS set DSC_STATUS='n' where USER_REF_ID="+s8);
    st1.executeUpdate();
    }

    System.out.println("Expired DSC Updated Done on -->"+cur_date+" for User_REF_ID= "+s8+" User Type= "+usertp+" DSC Chrono No-->"+dscchrno);
    } //for end
    rs0.close();
    //System.out.println("Expired DSC Updated Done on -->"+cur_date);
    }catch(SQLException sqe){
    System.out.println("Expired DSC Updation failed :"+sqe+" on "+cur_date);
    }
    newCon.commit();
    stmt0=null;

    /************************************************** ******************************/

    try{

    stmt0 = newCon.createStatement();
    rs0 = stmt0.executeQuery("select USER_REF_ID,REF_ID,DE_SERIAL_NO from EP_DIGITAL_CERT_INFO where STATUS='a' and DE_EXPIRY_TIME<current timestamp");
    //rs0 = stmt0.executeQuery("select USER_REF_ID,SL_ID from EP_ENCRYPTION_CERT_STORE where DE_STATUS='a' and DE_EXPIRY_TIME<current timestamp");
    while(rs0.next())
    {
    String s8 = rs0.getString(1);
    String dscchrno = rs0.getString(2);
    String desrlno = rs0.getString(3);
    if(desrlno==null) desrlno="";

    st1= newCon.prepareStatement("update EP_DIGITAL_CERT_INFO set STATUS='e' where REF_ID="+dscchrno);
    st1.executeUpdate();
    st1=null;


    //st1= newCon.prepareStatement("update EP_ENCRYPTION_CERT_STORE set DE_STATUS='e' where SL_ID="+dscchrno);
    st1= newCon.prepareStatement("update EP_ENCRYPTION_CERT_STORE set DE_STATUS='e' where USER_REF_ID="+s8+" and DE_SERIAL_NO='"+desrlno+"' and DE_STATUS='a' and DE_EXPIRY_TIME<current timestamp");
    st1.executeUpdate();
    st1=null;

    System.out.println("Expired ENCRYPTION Updated Done on -->"+cur_date+" for User_REF_ID= "+s8+" ENCRYPTION SL ID-->"+dscchrno);
    } //for end
    rs0.close();
    }catch(SQLException sqe){
    System.out.println("Expired ENCRYPTION Updation failed :"+sqe+" on "+cur_date);
    }
    newCon.commit();
    /************************************************** *******************************/


    }catch(Exception es){
    System.out.println("ss"+es);

    }finally{
    try{
    newCon.close();
    }catch(Exception nn){}


    }//finally

    }
    }
    --------------------------------------------------------------

    please help me to resolve the same

    thanks in advance

    ssumit
    ssumit

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Seems that the catalog info does not match what you've built...

    Follow more carefully the IBM-supplied sample code in files spserver.java and spcat in the samples/java/jdbc tree (also available in the knowledge centre).

    As an aside: I can't see why such a simple sproc should use java instead of plain SQL PL (if skills were available).

  3. #3
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Thanks db2mor,

    I got your point and gone through the info from path /opt/ibm/db2/V10.1/samples/java/jdbc files..

    I came to the below conclusion:

    1. My jar is not getting cataloged as in db2 procedure because: it contain only main method in a class:

    public class dsc_expiry_update {

    public static void main(String args[]){
    ....

    in examples, main method cases are not there moreover parameters in ,out or inout are mandatory.

    So it means Java without any method (only main) cant be cataloged and run.

    2. Due to lack of PL/SQL resource all work is done in java, and inturn called from AIX crontab.


    Being a DBA i am trying to do all these task from ATS and db2 stored procedures.

    Please confirm what i understood is correct or not ?

    Please give your comments on above approach.


    thanks in advance.

    ssumit
    ssumit

  4. #4
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    any response on this post please ?
    ssumit

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi ssumit,

    too many notes...

    if you use a stored procedure defined as PARAMETER STYLE JAVA, then:
    • you have to define your method as

    public static void mymethod(...)
    and define you procedure as
    ...
    EXTERNAL NAME 'my_full_class_name!mymethod'
    ...
    • if your SP has no parameters

    then your method has to be defined as:
    public static void mymethod()
    • if you have an output parameter

    for example, for the following definition:
    CREATE PROCEDURE EPROC.TEST_DSC_PROC(..., out myoutpar varchar(10), ...)
    ...
    then (for varchar data type, for example) your method has to be defined as:
    public static void mymethod(..., String[] my_out_par, ...)
    you can set it as my_out_par[0] = "my_value";
    • it's not a good idea to establish a new connection from your SP

    you should use your current connection:
    newConn = DriverManager.getConnection("jdbc:default:connecti on");
    newConn.setAutoCommit(false);
    ...
    finally close your connection by
    newConn.close();
    • it's not a good idea to issue a commit inside SP unless it's really necessarily.
    You should manage your transaction from a calling application.
    • Don't try to print to System.out
    Print to a file if you need logging or notifications.
    Regards,
    Mark.

  6. #6
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Thanks Mark , Your suggestions worked..

    For every bode else:

    method name "main()" is not supported in EXTERNAL NAME clause of CREATE PROCEDURE .. command of DB2.
    replace main with my_method (any other name) and use as metioned below.

    EXTERNAL NAME 'my_full_class_name!mymethod'

    Thanks to all contributing in thread

    regards
    ssumit
    ssumit

  7. #7
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by ssumit View Post
    For every bode else:

    method name "main()" is not supported in EXTERNAL NAME clause of CREATE PROCEDURE .. command of DB2.
    replace main with my_method (any other name) and use as metioned below.
    That's not true.
    If you want to use the following java function:
    Code:
    public static void main(String[] arg) {...}
    then you must use:
    Code:
    create procedure myproc (
    --out v varchar(...)
    inout v varchar(...)
    )
    language java
    parameter style java
    external name 'my_full_class_name!main'
    ...
    and it will work.
    Note that you have to declare the corresponding parameter as OUT or INOUT.
    IN will not work.
    Regards,
    Mark.

  8. #8
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    thanks mark,

    but i am not able to do it practically what you have stated ...

    1. created proc:

    CREATE PROCEDURE EPROC.TEST_DSC_PROC(INOUT V varchar(2))
    SPECIFIC TEST_DSC_PROC
    DYNAMIC RESULT SETS 0
    DETERMINISTIC
    LANGUAGE JAVA
    PARAMETER STYLE JAVA
    NO DBINFO
    THREADSAFE
    MODIFIES SQL DATA
    PROGRAM TYPE SUB
    EXTERNAL NAME 'dsc_expiry_update_new!main';

    successful.


    2. my java prog. is as


    public class dsc_expiry_update_new {

    public static void main(String[] args){...}

    }



    3. db2

    call EPROC.TEST_DSC_PROC('ab')


    ERROR ENCOUNTERED:

    SQL4306N Java stored procedure or user-defined function "EPROC.TEST_DSC_PROC",
    specific name "TEST_DSC_PROC" could not call Java method "main", signature
    "([Ljava/lang/StringV". SQLSTATE=42724


    Please suggest a way-out... or any alternative

    thanks in advance

    ssumit
    ssumit

  9. #9
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    db2 10.5.3 on linux, 9.7.6 on windows

    dsc_expiry_update_new.java file:
    Code:
    public class dsc_expiry_update_new {
      public static void main(String[] arg) {
        arg[0] = "**";
      }
    }
    Code:
    CREATE PROCEDURE TEST_DSC_PROC(INOUT V varchar(2))
    SPECIFIC TEST_DSC_PROC
    DYNAMIC RESULT SETS 0
    DETERMINISTIC
    LANGUAGE JAVA
    PARAMETER STYLE JAVA
    NO DBINFO
    THREADSAFE
    MODIFIES SQL DATA
    PROGRAM TYPE SUB
    EXTERNAL NAME 'dsc_expiry_update_new!main'
    makeit.sh
    Code:
    ${DB2_HOME}/java/jdk64/bin/javac ${1?"java source file must be specified"}
    classname=$(echo $(basename $1) | sed 's/\.[^\.]*$//').class
    cp $classname ${DB2_HOME}/function
    To compile and deploy:
    ./makeit.sh dsc_expiry_update_new.java

    Test.
    Code:
    call  "TEST_DSC_PROC('ab')"
    
      Value of output parameters
      --------------------------
      Parameter Name  : V
      Parameter Value : **
    
      Return Status = 0
    If this doesn't work - you definitely do something wrong.
    If you at unix - check the permissions on file ~/sqllib/function/dsc_expiry_update_new.class
    Fenced user has to have a read permission on this file.
    Regards,
    Mark.

  10. #10
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Hello mark,

    Thanks for correcting me.

    I am using 10.1 on RHEL with java version
    java version "1.7.0_09-icedtea"
    OpenJDK Runtime Environment (rhel-2.3.4.1.el6_3-i386)
    OpenJDK Server VM (build 23.2-b09, mixed mode)

    I doubt that some changes might have been incorporated in 10.1 / or some compatibility issues with java version, because of which my code is not working.

    Below is what i tried at the end (exactly same as said by you but now luck. error messages are attached.

    1.

    CREATE PROCEDURE EPROC.TEST_DSC_PROC(INOUT V varchar(2))
    SPECIFIC TEST_DSC_PROC
    DYNAMIC RESULT SETS 0
    DETERMINISTIC
    LANGUAGE JAVA
    PARAMETER STYLE JAVA
    NO DBINFO
    THREADSAFE
    MODIFIES SQL DATA
    PROGRAM TYPE SUB
    EXTERNAL NAME 'dsc_expiry_update_new!main'


    sucessfull.


    2.

    [db2insts@mstcho ~]$ db2 "CALL "EPROC"."TEST_DSC_PROC"('ab')"


    SQL4306N Java stored procedure or user-defined function "EPROC.TEST_DSC_PROC",
    specific name "TEST_DSC_PROC" could not call Java method "main", signature
    "([Ljava/lang/StringV". SQLSTATE=42724


    3. called through GUI


    CALL "EPROC"."TEST_DSC_PROC"(@V)


    Lookup Error - DB2 Database Error: ERROR [07001] [IBM] CLI0100E Wrong number of parameters. SQLSTATE=07001


    I will be grateful if someone point out the root cause.

    thanks in advance
    ssumit

  11. #11
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hello ssumit,

    It works on my 10.1.4 on Windows 64-bit.
    Show the messages appeared in the ~/sqllib/db2dump/db2diag.log after an unsuccessful sp call.

    I don't have RHEL 10.1 32-bit so I can't try it.
    Try to use IBM JDK for compilation which is supplied with DB2.
    Regards,
    Mark.

  12. #12
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Thanks mark,

    Ok, i will try with DB2s JDK.

    Diag is recording as follows:


    2014-08-07-13.17.47.323319+330 I137295971G463 LEVEL: Warning
    PID : 11924 TID : 61442928 PROC : db2fmp (
    INSTANCE: db2insts NODE : 000 DB : DBMSTC
    APPID : *LOCAL.db2insts.140806073500
    HOSTNAME: mstcho
    FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll, probe:140
    MESSAGE : JNI GetMethodID failed. method:
    DATA #1 : Hexdump, 4 bytes
    0xB6834576 : 6D61 696E main

    2014-08-07-13.17.47.323580+330 I137296435G542 LEVEL: Warning
    PID : 11924 TID : 61442928 PROC : db2fmp (
    INSTANCE: db2insts NODE : 000 DB : DBMSTC
    APPID : *LOCAL.db2insts.140806073500
    HOSTNAME: mstcho
    FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll, probe:150
    MESSAGE : JNI GetMethodID failed. signature:
    DATA #1 : Hexdump, 22 bytes
    0xB68345C0 : 285B 4C6A 6176 612F 6C61 6E67 2F53 7472 ([Ljava/lang/Str
    0xB68345D0 : 696E 673B 2956 ingV

    2014-08-07-13.17.47.323973+330 E137296978G458 LEVEL: Warning
    PID : 11924 TID : 61442928 PROC : db2fmp (
    INSTANCE: db2insts NODE : 000 DB : DBMSTC
    APPID : *LOCAL.db2insts.140806073500
    HOSTNAME: mstcho
    FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10
    MESSAGE : ADM10000W A Java exception has been caught. The Java stack
    traceback has been written to the db2diag log file.

    2014-08-07-13.17.47.324745+330 I137297437G500 LEVEL: Warning
    PID : 11924 TID : 61442928 PROC : db2fmp (
    INSTANCE: db2insts NODE : 000 DB : DBMSTC
    APPID : *LOCAL.db2insts.140806073500
    HOSTNAME: mstcho
    FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10
    MESSAGE : java.lang.NoSuchMethodError: dsc_expiry_update_new.main([Ljava/lang/StringV
    DATA #1 : Hexdump, 4 bytes
    0x03A97384 : 0000 0000

    Any findings ?

    Thanks

    ssumit
    ssumit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •