Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Question Unanswered: Stored Procedure using Java

    Environment: DB2 Connect Edition v8.1 fixpak 10 on Solaris 8

    Hi all,

    I am trying to get a stored procedure working, info from db2diag.log to pin point the error is not useful..

    The Java Code:

    import java.sql.*;

    public class SPUTIL
    {

    public static void SPTRUNCATE(String databaseName, String tableName)
    throws SQLException
    {
    int errorCode = 0;
    String errorLabel = "";
    boolean bFlag;
    String sql;
    PreparedStatement stmt = null;


    try
    {
    Connection con = DriverManager.getConnection("jdbc:default:connecti on");



    sql = "IMPORT FROM /dev/null OF DEL REPLACE INTO " + "'" + databaseName + "'.'"
    + tableName + "'";

    stmt = con.prepareStatement( sql );

    bFlag = stmt.execute();
    }
    catch ( SQLException sqle)
    {
    errorCode = sqle.getErrorCode();
    throw new SQLException ( errorCode + " : " + errorLabel + " FAILED" );
    }
    }
    }


    I Compiled the java code to class file and put it in <instance>/sqllib/function folder..

    I ran the following script

    CREATE PROCEDURE DPS.SPTRUNCATE
    (IN DBNAME VARCHAR(8) , IN TBLNAME VARCHAR(8))
    SPECIFIC DPS.SPTRUNCATE
    DYNAMIC RESULT SETS 0
    NOT DETERMINISTIC
    LANGUAGE JAVA
    PARAMETER STYLE JAVA
    EXTERNAL NAME 'SPUTIL.SPTRUNCATE'
    FENCED
    THREADSAFE

    The script ran successfully, but when i call the procedure

    CALL SPTRUNCATE('TSTDB','TAB1')
    SQL4302N Procedure or user-defined function "DPS.SPTRUNCATE", specific name "SPTRUNCATE" aborted with an exception "-104 : FAILED". SQLSTATE=38501

    SQL4302N Procedure or user-defined function "DPS.SPTRUNCATE", specific name "SPTRUNCATE" aborted with an exception "-104 : FAILED ".

    Explanation:

    The procedure or user-defined function aborted with an exception.
    The administration notification log contains a stack traceback
    for the aborted routine.

    User Response:

    Debug the routine to eliminate the exception.

    sqlcode : -4302

    sqlstate : 38501



    Any pointers?

    Thanks in Advance,
    Newbie

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    IMPORT is not an SQL statement, so you can't prepare or execute it. It's a CLP command.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Question

    Thanks n_j

    could you tell me how to make a CLP connection instead of a JDBC connection in Java?

    Connection con = DriverManager.getConnection("jdbc:default:connecti on");

    Thanks!
    Newbie

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You can't do that, of course.

    What you could do is to try invoking CLP via Runtime.getRuntime().exec() - I'm not sure if this will work thought. Even if it does, it might have certain performance and security implications.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at this article: http://tinyurl.com/9gnlo

    It describes how to use a C stored procedure to invoke the respective API.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Question

    Thanks n_j! the following code works but, i dont want to create a temporary file to get this working.... any ideas?

    import java.util.*;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.io.*;

    public class SPUTIL
    {
    public static void main(String argv[]) throws Exception
    {
    try
    {
    Class.forName("com.ibm.db2.jcc.DB2Driver");

    Properties props = new Properties(); // Create Properties object
    props.put("user", "db2inst"); // Set user ID for connection
    props.put("password", "passwd"); // Set password for connection
    props.put("currentSchema","schema");
    String url = "jdbc:db2://23.23.23.23:55555/TSTDB"; // Set URL for data source
    java.sql.Connection conn = DriverManager.getConnection(url, props); // Create connection

    tbTrunc(conn);

    conn.close();
    }
    catch(SQLException sqle) {
    while(sqle != null) { // Check whether there are more
    System.out.println("Message: " + sqle.getMessage());
    System.out.println("SQLSTATE: " + sqle.getSQLState());
    System.out.println("SQL error code: " + sqle.getErrorCode());
    sqle=sqle.getNextException(); // Retrieve next SQLException
    }
    }
    catch (ClassNotFoundException cnfe)
    {
    cnfe.printStackTrace();
    }
    }

    //
    static void tbTrunc(Connection conn) throws Exception
    {

    try
    {
    // store the CLP commands in a file and execute the file
    File outputFile = new File("storImport.db2");
    FileWriter out = new FileWriter(outputFile);

    String cmd = "IMPORT FROM /dev/null OF DEL REPLACE INTO '" + tableName + "'";

    out.write("CONNECT TO TSTDB user blah using passwd;\n");
    out.write(cmd + ";\n");
    out.write("CONNECT RESET;\n");

    out.close();

    Process p = Runtime.getRuntime().exec("db2 -vtf storImport.db2");

    // open streams for the process's input and error
    BufferedReader stdInput = new BufferedReader(new InputStreamReader(p.getInputStream()));
    BufferedReader stdError = new BufferedReader(new InputStreamReader(p.getErrorStream()));
    String s;

    // read the output from the command and set the output variable with
    // the value
    while ((s = stdInput.readLine()) != null)
    {
    System.out.println(s);
    }

    // read any errors from the attempted command and set the error
    // variable with the value
    while ((s = stdError.readLine()) != null)
    {
    System.out.println(s);
    }

    // destroy the process created
    p.destroy();

    // delete the temporary file created
    outputFile.deleteOnExit();
    }
    catch (IOException e)
    {
    e.printStackTrace();
    System.exit(-1);
    }
    }
    }
    Thanks stolze!
    Have a look at this article: http://tinyurl.com/9gnlo

    It describes how to use a C stored procedure to invoke the respective API.
    That piece of information was a goldmine! however i think i dont have a C compiler installed on my Solaris box and i could not do the "bldrtn" part... any other suggestions? Do i have to download gcc and install it or is there a C compiler that exists on my machine that i dont know of?

    Just curious... What kind of connection does this C code use?

    Thanks!
    Newbie

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Don't use a temp file for the import operation. On Unix, you have "/dev/null" to read from and on Windows it is the file named "NUL".

    I don't know about your compiler options on your Solaris box. You may want to ask this question your system administrator.

    As for the connection: the stored procedure is executed by a DB2 process itself and, thus, it has the an implicit connection context. Therefore, the procedure doesn't have to worry about connections at all.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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