Transitioning to isCOBOL : Transitioning from the IBM DB2 Preprocessor : Issues you should be aware of
Issues you should be aware of
The isCOBOL SQL support based on JDBC has some differences with the IBM DB2 preprocessor. Below you find a list of the changes required to the configuration in order to obtain the same behavior between the IBM DB2 preprocessor and isCOBOL on JDBC.
Enabling the DB2 compatibility mode
For the best compatibility with the IBM DB2 rules and behaviors, you should enable the DB2 compatibility mode in the Compiler before compiling your programs with ESQL. In order to enable the DB2 compatibility mode, add the following option to your compile command:
-csdb2
When this option is used, the Compiler generates specific code to return the result sets in the same format that would be produced when using the IBM DB2 preprocessor. In particular, it supports the SQLDA structure and the use of date, time and timestamp as function parameters, for example DAYOFWEEK or WEEKS_BETWEEN. It also allows you to intercept the result of a function or a special register with a SET statement.
JDBC Connection
isCOBOL creates a JDBC connection. There is no need of native DB2 client libraries; you just need the IBM DB2 JDBC driver library (e.g. db2jcc4.jar.jar). You can find JDBC driver libraries at https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads.
These setup steps are required:
1. Add the IBM DB2 JDBC driver to the Classpath.
Example for Windows:
set CLASSPATH=%CLASSPATH%;\path\to\db2jcc4.jar
Example for Linux/Unix:
export CLASSPATH=$CLASSPATH:/path/to/db2jcc4.jar
2. Define the JDBC driver class in the runtime configuration:
iscobol.jdbc.driver=com.ibm.db2.jcc.DB2Driver
3. Define the connection URL in the runtime configuration. Example:
iscobol.jdbc.url=jdbc:db2://localhost:50000/SAMPLE
Autocommit mode
Programs generated by the IBM DB2 preprocessor don’t work in autocommit mode by default, while isCOBOL works in autocommit mode by default. So, unless autocommit was enabled in your DB2 client environment, add the following entry to the isCOBOL configuration:
iscobol.jdbc.autocommit=false
Different format string from DATE, TIME and TIMESTAMP
The format string used in JDBC to represent the value of DATE, TIME and TIMESTAMP columns may be different than the format string used when working with the IBM DB2 preprocessor. In order to make isCOBOL use the same format strings as the IBM DB2 preprocessor, you can set the following runtime configuration properties:
Error for NULL value fetched
When a NULL value is fetched and no indicator variable was used, you expect the error code -305.
By default isCOBOL doesn’t return errors in this case, it just sets the host variable to zero or spaces depending on the picture. In order to have the error number -305 also with isCOBOL
1. compile the program with the -csqn option,
2. add iscobol.esql.value_sqlcode_on_null=-305 to the runtime configuration.
Handling of CHAR and VARCHAR fields that store binary data
The DB2 Preprocessor allows you to store any character in CHAR and VARCHAR fields, same as you can do with a COBOL item with picture X. Some COBOL applications take advantage of this possibility to store the dump of COBOL structures with COMP fields into CHAR and VARCHAR fields.
When moving to Java JDBC, instead, CHAR and VARCHAR fields are managed as strings and a conversion error may occur if an unknown character is found in the string. The database encoding affects the management of the strings.
You can configure the isCOBOL runtime to manage CHAR and VARCHAR fields as byte arrays, so any character can be stored into these fields.
To have all CHAR and VARCHAR fields managed as byte arrays, set
iscobol.esql.db2.row_data_as_bytes_threshold=1
To have only some CHAR and VARCHAR fields managed as byte arrays, set the property to a value greater than 1. For example, by setting:
iscobol.esql.db2.row_data_as_bytes_threshold=100
CHAR and VARCHAR fields whose size is not less than 100 bytes will be managed as byte arrays, while smaller CHAR and VARCHAR fields will be managed as strings.
Error for no data during DELETE, INSERT and UPDATE
Programs generated by the IBM DB2 preprocessor return a NOT_FOUND condition (e.g. SQLCODE=100) when an UPDATE statement or a DELETE statement doesn’t affect any record. The same NOT_FOUND condition is returned for INSERT INTO SELECT statements, when the inner SELECT doesn’t find any record.
isCOBOL doesn’t do the same, by default.
In order to have the same behavior of the IBM DB2 preprocessor with isCOBOL, set iscobol.esql.value_sqlcode_on_no_data in the configuration.
How to obtain SQLCODE=100 instead of -4470
The type 4 JDBC driver of DB2 automatically closes the cursor when the last record has been fetched, so if you fetch again after a NOT_FOUND condition (e.g. SQLCODE=100), you get the error -4470 (Resultset closed).
With DB2 Prep, instead, if you fetch again after a NOT_FOUND condition, you get another NOT_FOUND condition, SQLCODE is always 100.
To have the same behavior as DB2 Prep, add the allowNextOnExhaustedResultSet=1 setting to the connection URL, for example:
iscobol.jdbc.url=jdbc:db2://10.10.82.136:50000/DATABASE1:user=db2user;password=secret;allowNextOnExhaustedResultSet=1;
Alternatively you can pass this setting via the iscobol.jdbc.options configuration property.