Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Question Unanswered: db2 hung while inserting record

    Hi,

    My java application which inserts records to db2 will hang after uncertain amount of insertions, several hundreds or thousands. It just died at the INSERT statement and never exit. Is there any timeout settings of SQL execution?

    Here is an error in db2diag.log, I m not sure whether it's relevant to the problem:

    2004-03-10-22.13.13.484001 InstanceB2 Node:000
    PID:2048(javaw.exe) TID:2336 Appid:none
    oper system services sqloSSemClose Probe:20

    Unexpected system error 0x6 has occurred.
    This has been mapped to ZRC 0x83000006.

    PID:2048 TID:2336 Node:000 Title: SYSTEM ERROR DESCRIPTION
    invalid handle.

    db2 version: 8.1.2, client: jdbc java application

    Thanks in advance!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't know what the error message means, but do you perform any commits?. If not, do a commit every 1000 inserts and see if that helps.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2004
    Posts
    4
    thank you, Marcus_A, I think I do commit my work: in fact it's a complicated insertion, here is the source code:

    private static final String INSERT_DOCUMENT_SQL =
    "INSERT INTO Document VALUES " + "(DEFAULT, ?, ?, ?, ?, ?, " +
    "?, ?, ?, ?, ?, DEFAULT, ?, ?, ?)";

    public void addDocument(Document doc) throws DocumentException
    {
    // add the Document object to DB2
    s_logger.debug("executing addDocument...");

    checkConnection();

    PreparedStatement stmt = null;
    PreparedStatement insertDocument = null;
    PreparedStatement selectMaxDocID = null;
    PreparedStatement insertRelated = null;
    PreparedStatement selectRelatedDocID = null;
    PreparedStatement updateRelatedDocID = null;

    boolean autoCommit = true;

    ResultSet rs = null;
    try
    {
    // 1. get SourceID
    int sourceID = getSourceID(doc);
    s_logger.debug("Get sourceID: " + sourceID);

    // 2. get LanguageID
    int languageID = getLangID(doc);
    s_logger.debug("Get languageID: " + languageID);

    // 3. insert into Document
    // set auto-commit false to execute a transaction
    m_conn.setAutoCommit(false);
    autoCommit = false;

    insertDocument = m_conn.prepareStatement(INSERT_DOCUMENT_SQL);
    // languageID
    insertDocument.setInt(1, languageID);
    // sourceID
    insertDocument.setInt(2, sourceID);
    // uri
    insertDocument.setString(3, uri);

    ...

    // hits
    Integer hits = (Integer)doc.getProperty(HITS_PROP);
    if (hits == null) hits = new Integer(0);
    insertDocument.setInt(13, hits.intValue());

    s_logger.debug("Data set to statement.");

    // DEAD HERE
    insertDocument.executeUpdate();
    s_logger.debug("Insert executed.");

    // get the pid(PK) of the inserted document
    long pid = -1;
    selectMaxDocID = m_conn.prepareStatement(SELECT_MAXDOCID_SQL);
    rs = selectMaxDocID.executeQuery();
    if (rs.next())
    {
    pid = rs.getInt(MAX_DOCID);
    doc.setPIDString(String.valueOf(pid));
    s_logger.debug("MaxDocID found: " + pid);
    }
    else
    {
    m_conn.rollback();
    throw new DocumentException("Retrieve maxDocID failed!");
    }
    rs.close();

    ...

    m_conn.commit();
    }
    catch (SQLException e)
    {
    try
    {
    if (!autoCommit)
    {
    s_logger.warn("Insert Document Transaction is aborted!");
    m_conn.rollback();
    }
    }
    catch(SQLException se)
    {
    s_logger.warn("SQLException thrown while rollback of addDocument: ", se);
    }
    s_logger.warn("SQLException thrown while addDocument: ", e);
    throw new DocumentException(e);
    }
    catch (IOException e)
    {
    s_logger.warn("IOException thrown while addDocument: ", e);
    throw new DocumentException(e);
    }
    catch(Exception rte)
    {
    s_logger.warn("Exception thrown while addDocument: ", rte);
    throw new DocumentException(rte);
    }
    finally
    {
    try
    {
    m_conn.setAutoCommit(true);
    if (rs != null) rs.close();
    if (stmt != null) stmt.close();
    if (insertDocument != null) insertDocument.close();
    if (selectMaxDocID != null) selectMaxDocID.close();
    if (insertRelated != null) insertRelated.close();
    if (selectRelatedDocID != null) selectRelatedDocID.close();
    if (updateRelatedDocID != null) updateRelatedDocID.close();
    }
    catch (SQLException e1)
    {
    s_logger.warn("SQLException thrown while close statements in addDocument...", e1);
    }
    s_logger.debug("execute addDocument over!");
    }

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    CLI Trace and/or Event Monitoring may help

    Cheers
    sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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