Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    US
    Posts
    10

    Unanswered: Problems with Java SP on DB2 v8.1

    Can anybody help with the following two problems.

    1. Build java/SQLJ SP in "DB2 Development Center" using wizard.
    Compilation is fine.
    SP jar file is in a right place (in my case C:\IBM\SQLLIB\FUNCTION\jar\DB2ADM)
    Granted execute permission to all users and all groups
    Still get:

    A database manager error occurred.[IBM][CLI Driver][DB2/NT] SQL4304N Java
    stored procedure or user-defined function "DB2ADM.GET_MONTHS1", specific
    name "GET_MONTHS1" could not load Java class "GET_MONTHS1", reason code "1".
    SQLSTATE=42724

    Never had these problems on DB2 7.2 (acctually I recreated the same SP that works fine on 7.2)

    2. What is the right way to migrate java SP from v7.2 to v8.1
    Batch file generated on 7.2 partialy failed on v8.2 because db2 java classes have been repackaged.
    Sure I can adjust the classpaths manually but it does not look right.
    Any other suggestion?

    Thanks for any help

    Regards
    Vlad

  2. #2
    Join Date
    Oct 2003
    Location
    Chicago
    Posts
    5

    Re: Problems with Java SP on DB2 v8.1

    1. Build java/SQLJ SP in "DB2 Development Center" using wizard.
    Compilation is fine.
    SP jar file is in a right place (in my case C:\IBM\SQLLIB\FUNCTION\jar\DB2ADM)
    Granted execute permission to all users and all groups
    Still get:

    A database manager error occurred.[IBM][CLI Driver][DB2/NT] SQL4304N Java
    stored procedure or user-defined function "DB2ADM.GET_MONTHS1", specific
    name "GET_MONTHS1" could not load Java class "GET_MONTHS1", reason code "1".
    SQLSTATE=42724

    I have the same problem - have you managed to figure this out? I was able to create a couple of procs, but then it was like it just spontaneously broke.

  3. #3
    Join Date
    Sep 2003
    Location
    US
    Posts
    10
    No I still have the same problem on two machines:
    W2K Pro / DB2 8.1 FP3 and
    XP Pro / DB2 8.1 FP3

    JDK_PATH set to C:\j2sdk1.3.1_08
    (also tried C:\IBM\SQLLIB\java\jdk)

    1.
    Running SP throwgh DC get:

    DB2ADM.GET_MONTHS - Run started.
    Data returned in result sets is limited to the first 100 rows.
    Data returned in result set columns is limited to the first 20 bytes or characters.
    DB2ADM.GET_MONTHS - Exception occurred while running:
    A database manager error occurred.[IBM][CLI Driver][DB2/NT] SQL4304N Java stored procedure or user-defined function "DB2ADM.GET_MONTHS", specific name "GET_MONTHS" could not load Java class "GET_MONTHS", reason code "1". SQLSTATE=42724

    DB2ADM.GET_MONTHS - Roll back completed successfully.
    DB2ADM.GET_MONTHS - Run failed.

    2.
    DB2DIAG.log shows:

    java.lang.ClassNotFoundException: GET_MONTHS
    at COM.ibm.db2.app.classloader.DynamicClassLoader.loa dNonSystemClass(DynamicClassLoader.java:155)
    at COM.ibm.db2.app.classloader.ClassLoaderTemplate.lo adClass(ClassLoaderTemplate.java:93)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:2 50)
    at COM.ibm.db2.app.classloader.PowerClassLoader.loadN onSystemClass(PowerClassLoader.java:91)
    at COM.ibm.db2.app.classloader.ClassLoaderTemplate.lo adClass(ClassLoaderTemplate.java:93)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:2 50)

    3.
    CLASSPATH set to:

    .;C:\IBM\SQLLIB\java\db2java.zip;C:\IBM\SQLLIB\jav a\db2jcc.jar;C:\IBM\SQLLIB\java\sqlj.zip;C:\IBM\SQ LLIB\bin;C:\IBM\SQLLIB\tools\db2XTrigger.jar;C:\IB M\SQLLIB\java\common.jar;C:\IBM\SQLLIB\java\jdk\bi n;C:\IBM\SQLLIB\FUNCTION;C:\IBM\SQLLIB\java\db2jcc _license_cisuz.jar;C:\IBM\SQLLIB\java\db2jcc_licen se_cu.jar

    so C:\IBM\SQLLIB\FUNCTION is definitly there

    4.
    GET_MONTHS.jar is in C:\IBM\SQLLIB\FUNCTION\jar\DB2ADM directory

    5.
    SP source code:

    /**
    * SQLJ Stored Procedure GET_MONTHS
    */

    //package com.scs.dbsp.jcxp;

    import java.sql.*; // JDBC classes
    import sqlj.runtime.*;
    import sqlj.runtime.ref.*;

    #sql iterator GET_MONTHS_Cursor1 ( int, String );

    public class GET_MONTHS
    {
    public static void gET_MONTHS ( int[] RETVAL,
    int[] SQLCODE,
    String[] SQLSTATE,
    String[] SQLMessage,
    ResultSet[] rs ) throws Exception
    {
    RETVAL[0] = 0;
    SQLCODE[0] = 0; // Good SQLCODE
    SQLSTATE[0] = "00000"; // Good SQLSTATE
    SQLMessage[0] = " "; // Good SQLMessage
    GET_MONTHS_Cursor1 cursor1 = null;
    try{
    #sql cursor1 =
    {
    SELECT
    SCS_MONTH.MONTH_CODE AS CODE,
    SCS_MONTH.MONTH_NAME AS NAME
    FROM
    SCS_MONTH
    };
    rs[0] = cursor1.getResultSet();

    } catch (SQLException e)
    {
    // Set return parameters
    SQLSTATE[0] = e.getSQLState();
    SQLCODE[0] = e.getErrorCode();
    SQLMessage[0] = e.getMessage();
    RETVAL[0] = -100;

    // Close open resources
    try {
    if (rs[0] != null) rs[0].close();
    } catch (SQLException e2) { /* ignore */ };
    }

    }
    }


    Again never had this problem with DB2 7.2 on W2K and AIX.

    Does anybody have it working ?

    Thanks for any help

    Regards
    Vlad

  4. #4
    Join Date
    Oct 2003
    Location
    Chicago
    Posts
    5

    possible interim answer

    My DBA and I worked on trying to debug this for about 3 hours yesterday. I had built a simple java proc with the DC named itsbroke, accepting the defaul code and sql statement it generates. It ran through the DC GUI, but when I built it and tried running it through the CLP client it would give the aforementioned error. However, after working on it for a while it worked very rarely almost at random in a particular CLP session. Eventually, the DBA bounced (restarted?) the DB instance, and all seemed to be working just fine. Unfortunately, other responsibilities did not allow me to test further today, but this might be worth a try for you. Granted, it's not very satisfying, treating a IBM Regatta AIX/DB2 box like it's Windoze, but...

  5. #5
    Join Date
    Sep 2003
    Location
    US
    Posts
    10

    Re: possible interim answer

    Well I made it work with the same trick: restarted the instance couple of times. Suddenly it finds the SP classes.
    Thanks for the advice though it does not explain what is going on :-)

    Regards
    Vlad

  6. #6
    Join Date
    Sep 2003
    Location
    US
    Posts
    10

    Re: possible interim answer

    Finally I think I figured out the root of this problem (as well as some other troubles that I had while rebuilding and reruning SP):

    at least on W2k platform in the development environment KEEPFENCED parameter has to be set to NO.

    Regards
    Vlad

Posting Permissions

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