Contributed by Mike Patrick
There are essentially three flavors of DB2:
Each flavor comes with its own peculiarities. There are driver differences:
db2jcc.jar). On System z, a license file is required.
jt400.jar). The native driver is supported, but a license file is required.
And system differences:
Each database adapter must implement two queries: one to return column names, and one to return procedure parameters. The system catalog varies from platform to platform:
Two of the functions required for the Acceptance Tests need their syntax modified for DB2. DB2 does not support
CONCAT taking three arguments. They are:
CREATE TABLE USERS(NAME VARCHAR(50) UNIQUE, USERNAME VARCHAR(50), USERID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1))
CREATE FUNCTION ConcatenateF (firststring varchar(100), secondstring varchar(100)) RETURNS varchar(200) RETURN CONCAT(firststring, concat(' ', secondstring))
Which JDBC driver should I use?
DB2Environmentuses the native driver, and
DB2iEnvironmentuses the Toolbox driver. Using the native driver on System i is possible, but at present requires a recompile of
DB2iEnvironment. Consider the following:
If the default database (often the tables of interest to our testing) must be journalled, acceptance tests with INSERT and UPDATE operations may fail with:
java.sql.SQLException: [SQL7008] TEST_DBFIT in TESTLIB not valid for operation. Cause . . . . . : The reason code is 3. Reason codes are: 1 -- TEST_DBFIT has no members. 2 -- TEST_DBFIT has been saved with storage free. 3 -- TEST_DBFIT not journaled, no authority to the journal, or the journal state is *STANDBY. Files with an RI constraint action of CASCADE, SET NULL, or SET DEFAULT must be journaled to the same journal.
There are two ways around this:
transaction isolation=noneto the connection string.