When developing with the Altibase Precompiler, it describes a few things to be considered in advance. In this chapter, we will refer to the development of the program by first mentioning performance, threads, and so on.

Considerations for Performance

Developers should consider at least two things regard to performance.

1. Minimization of Prepare/Execute or Direct-Execute method

   Form of performance	Simple Insert Performance (2.4GHz / 8EA)
   Prepare/Execute each time	0.0008.43sec (Processing time per case)
   Prepare once/Execute each time	0.0005.55sec (Processing time per case)

   Since the above performance comparison is for a very simple insert, it should be premised that the effect of improving performance may vary depending on the query complexity, load, and equipment at the time of application. However, if the communication cost due to each DBMS and Prepare is reduced, the application server can be very effective in a structure that is separated from the DBMS server. In other words, it is expected to reduce the cost of 1 prepare communication from the communication cost of prepare 1 and execute 1.

2. Check the SQL statement execution plan (PLAN)

   Altibase can check the execution plan with the following command.

   The prompt "iSQL" refers to executed in the SQL statement execution utility provided by Altibase. This utility is provided in $ALTIBASE_HOME/bin/isql. (In addition to this, "Orange for Altibase" of Wear Valley, which is provided by ALTIBASE, can be used in the Windows environment.) When the query is executed in the query processor after executing the above command, the execution plan of the query is displayed.

   The developer needs to tune the SQL statement in a form that reduces the cost of the number of accesses by checking at least what index (INDEX) the table accesses and how many times the data is fetched from the corresponding index. Since ALTIBASE's memory DB hardly performs disk I/O, it can perform a certain portion of fast performance even if a full scan is performed, but on the contrary, unnecessarily frequent access to memory causes frequent system calls, resulting in relatively high CPU load. In other words, it is necessary to cover the CPU cost as frequently as the process of loading the memory space into the cache, which is the space that the CPU can compute, occurs frequently. Therefore, it should be considered that reducing these costs with the appropriate index scans can maximize performance and minimize CPU load.

Considerations for Threads

ALTIBASE does not guarantee inter-thread protection for connection objects. In other words, if a query is executed by accessing it simultaneously with one connection, an unknown error occurs. Therefore, when developing in the form of taking a connection object for each individual thread, or when multiple threads need to access one connection, the user must perform concurrency control on the connection object. If such concurrency control is not performed, the following errors may occur.

Considerations for Host Variables

When specifying the size of a char type host variable when declaring a host variable in ALTIBASE, "+1byte" in consideration of null-padding must be added. In the precompiled option, +1 byte can be omitted with the "-n" option, but in this case, the user must control the clear length of the char type host variable.

The indicator-related part that occurs when a NULL value is returned is also a case of a problem that occurs frequently. This is explained in the last part of the document, "Frequently Occurred Error Messages". "?" is used for parameter markers of host variables with all the ALTIBASE precompilers.

Also, a colon (:) must be used before the host variable in the source.

Checking Errors

The SQLCA object of ALTIBASE can be used without a separate declaration. It is recommended for Developers to insert code that performs error checking at all stages of using EXEC SQL statements. In the case of ALTIBASE, when using a cursor, if an error is not checked in the Cursor Prepare/Declare step, only an error indicating that the cursor has not been defined is checked at the time of opening the cursor. Please make sure to check for errors in the Cursor Prepare/Declare step that is omitted. The following error may occur during the open or fetch phase.

The variables provided by ALTIBASe for error checking are as follows. The error check does not matter which variable is selected, but it is recommended to use sqlca.sqlcode to check errors, and to output SQLCODE when printing the debugging log to check errors.

• Example of application

Since SQLCODE is all defined in $ALTIBASE_HOME/msg/manual.txt, refer to the relevant file, and some predefined values as below can also be used. If there is a case of conversion in another DBMS or a case that the user has separately redefined, it must be converted appropriately.

Commit Mode

Starting from ALTIBASE version 5, it is not necessary to perform explicit Commit/Rollback for the SELECT statement. However, when handling LOBs, NonAuto-Commit mode must be accessed, and explicitly Commit/Rollback must be performed. When accessing through Auto-Commit, the following error occurs.

• Error in change transaction (INSERT, UPDATE, DELETE)

• Error in query transaction (SELECT)

  Additionally, it should be considered that if an explicit Commit/Rollback is not performed after accessing the LOB, the lock is maintained by the corresponding transaction, which may cause a resource failure.

System Signal Handling

Altibase client library is not safe when a system signal occurs. For example, if the network connection is terminated due to an external cause, an application program in progress may be forcibly terminated by receiving a SIGPIPE signal. To prevent this forced termination, the SIGPIPE signal must be handled by the user application. However, if a function in the Alitbase client library is called while processing the SIGPIPE signal, this should not be called because the program may be stopped.

However, after signal processing is completed, it is possible to call a function in the Altibase client library.

• Example of signal handling

Other unsupported items

1. Dynamic Method 4 method is not supported
2. Context syntax is not supported (refer to "Considerations for threads)
3. Procedure typeset, Ref-Cursor, and result set in the form of array cannot be retrieved with the precompiler
4. It is not safe for the system signal occurrence. System signal processing should be handled by the user code