- Choosing DBMS connection type
- Thread program, connection pool management
- Checkpoints when disconnecting an application
- Session Timeout
- Considerations when using SELECT Cursor
- Checking SQL Error
- Considerations for LOB type
- Using Prepared Statement
- SQL Tuning
- Cautions during the bulk change operation
This chapter describes considerations in the development stage are summarized and explained.
Choosing DBMS connection type
① Choosing ALTIBASE connection type
TCP/IP connection type, normal connection method
UNIX Domain Socket connection type, only available in the local server
IPC connection type, only available in the local server
The CONNTYPE=n means the connection type, and when the connection string is specified in the program, the user can specify it explicitly. If DBMS and application are located on separate servers, only CONNTYPE=1 can be used. However, if DBMS and application run on the same server, it is recommended to select CONNTYPE=2, 3 to reduce the cost of communication.
② Auto-commit mode is the default setting for ALTIBASE
JDBC connects in auto-commit mode according to the standard specification. Other applications are determined by the "AUTO_COMMIT" property in the $ALTIBASE_HOME/conf/altibase.properties file by default unless the user changes. If this property value is "1", it operates in auto-commit mode. (Auto-commit means that the commit is automatically performed after the change transaction is completed normally)
Therefore, if the user wants to change this property value to Non auto-commit, ALTIBASe must be restarted after changing the property value to "0" in the configuration file. If the user is controlling by session, the user can execute the following SQL after connecting to the DBMS.
(For the ALTER SYSTEM command, it can be changed in real time to reflect the entire system, but it will be restored after restarting, so it is recommended to modify and restart the property that affects the entire property through the property file.)
Thread program, connection pool management
In a thread environment program, if multiple threads share one connection, the developer must perform concurrency control for the connection. ALTIBASE exchanges the promised protocols during processing session-server communication. Generally, it goes through the process of PAREBINDEXECUTEFETCH, etc. In this situation, an error occurs in the process that the other protocol is interrupted and the session is incorrectly processed.
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.
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 state after an error
|Option to set the timeout in the blocked state between transmission/reception on the network while connected to the DB|
|Occurs when the time to try to connect to the DB exceeds this setting|
Occurs when the total time to execute a query while connected to the DB exceeds this setting
Occurs when the time interval during which the FETCH protocol occurs exceeds this value after executing the SELECT statement while connected to the DB
Occurs when idle time without executing any query while connected to DB exceeds this setting
Occurs when this setting value is exceeded until commit/rollback is executed after executing the query while connected to the DB (execution query is rolled back)
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)
If the error by TIMEOUT occurs, the following error can be checked in the application.
Connection time out
The session has been closed by the server
The session has been closed by the server
Client's query exceeded in the execution time limitation
Client unable to establish connection
The session has been closed by the server
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.
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.
Considerations for LOB type
When querying for LOB type in ALTIBASE, it must be operated in non-auto-commit mode. The following error may occur in auto-commit mode.
It does not matter if the query is executed with the LOB in the column of the table, but the above error occurs when the LOB type is included in the SELECT target clause. Therefore, when querying the LOB type, it must be executed after changing the properties of the session to non-auto-commit mode.
However, in the case of using ALTIBASE HDB 6.3.1 or later JDBC driver, LOB type data can be queried in auto-commit mode by setting the transaction commit mode to be controlled on the client-side. How to set the transaction commit mode on the client-side of the JDBC is as follows.
- Example of altibase_cli.ini
Using Prepared Statement
ALTIBASE stores the execution plan of internally executed queries to speed up the performance of query processing. However, if a query of PREPARE/EXECUTE is executed every time, the execution plan that has already been PREPARE is used internally, but because the network cost to execute PREPARE is queried every time, it is recommended to write the following code to prevent the downgrade of performance.
( O )
( X )
There is a DB development library called OTL among open sources (Oracle, odbc template library). This library processes all queries in the form of prepare/execute every time. However, once the streaming option is activated, once prepared queries are only processed in the form of executing. The developer can query with the use of these tuning elements (reducing the network transmission cost). In the process, the dynamic should be minimized as much as possible and make sure that it is not preparing the same query every time.
The above code creates an SQL statement to be executed each time, prepares it, and executes it. In this case, the following would be reasonable.
When developing operations related to DB operation, there are cases where query processing is slow. Or, there are cases where the query was processed quickly in the test environment, but the query process is very slow in the integration test or operation stage. This can be seen as a case where the developer or DBA neglects the verification or testing process of the execution plan for all queries executed.
ALTIBASE can check the query processing process, that is, the execution plan as follows.
iSQL> ALTER SESSION SET EXPLAIN PLAN = ON;
We can see that the above execution plan FULL SCAN table T1 and accessed 262,145 records. If the index exists and the condition to use the index is specified, the following might be displayed.
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 4 )
SCAN ( TABLE: T1, INDEX: IDX_T1, ACCESS: 2, SELF_ID: 2 )
For more information on SQL tuning, please refer to the “ALTIBASE SQL Tuning Guide” document.
Cautions during the bulk change operation
Since general bulk change operation has a high load not only in ALTIBASE but also in other DBMS, there are precautions required for each vendor. ALTIBASE requires the following cautions.
① Possibility of memory increase in case of mass change of memory table
ALTIBASE supports the MVCC (Multi-Version Concurrency Control) technique. In the MVCC technique, when a change occurs, the same record is duplicated in the empty space of the table to make the change. If 10 million changes are made, memory increases due to 10 million copies will inevitably occur. (Of course, the increased area is reused as data or replication area, but the memory is not released in real time.)
② Continuous increase of transaction log files by performing bulk change operation
A large number of changes require a redo log for each change target record to be recorded in the transaction log file. Therefore, it is necessary to record the redo log of all the records that are changed in one transaction, but if the scope of this target is large, as many transaction log files are created as well. (ALTIBASE deletes the transaction log file at the time of the checkpoint. If there is information on which the transaction is ongoing in the log file, it cannot be deleted, and there is a possibility of a disk shortage failure due to the log file increase.)
③ Possibility of dead-lock occurrence and delay in the reflection in a replication environment
In a replication environment, as described above, the change information of all records changed by one transaction is transmitted to the other server.
In this process, a lock is held for the corresponding record of the other server. If the other server changes the record in the same range, a deadlock may occur between the transaction caused by the redundancy log and the transaction occurring in the other server itself.