Page History

Communication link failure (EXEC->INVL)

Communication link failure (PREP->EXEC)

Invalid request to process the SQL statement

The above errors occur when one connection is executing another SQL in the absence of concurrency control while executing any SQL while using the thread program or connection pool. Therefore, when the above error occurs, first, it is necessary to check whether concurrency control or connection processing in the program written by the developer does not cause an error to interrupt.

(Occasionally, if the timer is set in the user program, it may occur even if it is not a concurrency control problem as described above. For example, another SQL statement is executed while the FETCH protocol is not yet completed because the timer operates during the SELECT~FETCH process. if it is set, the above error may occur.)

Checkpoints when disconnecting an application

The SQL statements executed in the program are prepared in individual programs and servers. It is recommended to develop code to release resources when these SQL statements are used. In particular, in the case of Java, memory increase may occur, so the following code is important.

Connection con = pool.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery();
 
try
{
   ….
} exception ()
{
   …..
}
finally
{
    // Release all resource used within the program.
    rs.close();
    stmt.close();
    conn.close();
}

In the case of development such as CLI/ODBC, SQL_DROP must be executed with the SQLFreeStmt function at the end of use after the memory is allocated with SQLAllocStmt. If it is reused, use the SQL_CLOSE option. Otherwise, if it is completely disabled, use the SQL_DROP option to explicitly disable it.

Session Timeout

If the user has a 5-second timeout for a connection attempt and a blocking phenomenon on the network occurs, and there is no response with 30 seconds if the user wants to disconnect the connection, add the option to the connection string as follows.

Other TIMEOUT settings operate in the unit set in $ALTIBASE_HOME/conf/altibase.properties, and can be controlled by executing the following query for each session. (When it set to "0", it operates as infinite)

ALTER SESSION SET QUERY_TIMEOUT = 30 (Unit: second)
ALTER SESSION SET FETCH_TIMEOUT = 30

If the error by TIMEOUT occurs, the following error can be checked in the application.

TIMEOUT-related errors are also recorded in $ALTIBASE_HOME/trc/altibase_boot.log.

If the above log is recorded in altibase_boot.log, the developer must check and take action to determine why the Timeout error occurred.

Each cause is the same as described above, so if it occurs as a bottleneck of query processing, the corresponding query should be tuned. If UTrans_Timeout occurs as in the above example, commit/rollback is not performed after a change transaction occurs. The transaction control part of the program must be corrected/corrected.

If it is difficult for the user to solve it with the above instruction and need to force the processing, the TIMEOUT setting value for the DB session of the program should be processed rather than the property value of the entire DB.

Considerations when using SELECT Cursor

When implementing a SELECT statement using CURSOR, the user must perform an error check on the FETCH statement and close the CURSOR after an error occurs or after reading all data. If the user tries to FETCH a CURSOR that has already been read or try to work on a CURSOR that is not closed yet, an unintended error may occur. After using CURSOR, you must CLOSE normally.

Another consideration is that in the case of ALTIBASE, if COMMIT/ROLLBACK occurs during FETCH, the open CURSOR is automatically closed. Therefore, in order to create another transaction while during FETCH, a separate DB session needs to be created and a separate transaction needs to be processed with the session or perform a COMMIT/ROLLBACK after all the FETCH is completed.

However, if there are a large number of records to be processed, it is recommended to implement a structure in which separate sessions are established and processed rather than bulk changes. If the number of records to be changed must be bound in one transaction, it is recommended to develop a program so that conditions can be separated and processed.

Checking SQL Error

It is recommended that developers of the ALTIBASE environment to check the SQL error after all queries are processed. In particular, the following case must be checked in order to find errors.

EXEC SQL PREPARE ….
EXEC SQL EXECUTE ….
If (SQLCODE != SQL_SUCCESS)
   Error_log ();
 
Or,
EXEC SQL DECLARE CURSOR…
EXEC SQL OPEN ….
If (SQLCODE != SQL_SUCCESS)
   Error_log ();

In the above code, there is no error checking after performing PREPARE or DECLARE CURSOR. In this case, if the PREPARE (DECLARE CURSOR) step causes an error, the SQL error in the EXECUTE (OPEN) step would cause an error such as "Not defined (XX)", and despite the fact that it was actually caused by the PREPARE (DECLARE CURSOR) error, but the error cannot be accurately confirmed.

ALTIBASE also sends a query to the server for the execution of PREPARE (DECLARE CURSOR), so it performs query processing up to the step before executes it.

It is important to note that SQL error checking must be performed when all SQL processing statements are entered.

[ERR-91101 : Connection is in autocommit mode. One can not operate on LOB datas with autocommit mode on.]

sprintf (sql,  “INSERT INTO t1 VALUES (%d, %d)”, szNum1, szNum2);
EXEC SQL Prepare S for :sql;
EXECUTE S;

EXEC SQL INSERT INTO t1 VALUES (:szNum1, :szNum2);

ALTER SESSION SET EXPLAIN PLAN = ON;