DbFit



DB2

Contributed by Mike Patrick

Why are there multiple DB2 adapters?

There are essentially three flavors of DB2:

Each flavor comes with its own peculiarities. There are driver differences:

And system differences:

Which DB2 adapter should I use?

Setup Script to run Acceptance Tests

Two of the functions required for the Acceptance Tests need their syntax modified for DB2. DB2 does not support AUTO_INCREMENT or 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))

and

CREATE FUNCTION ConcatenateF
  (firststring varchar(100),
   secondstring varchar(100))
RETURNS varchar(200)
  RETURN CONCAT(firststring, concat(' ', secondstring))

DB2 for System i Considerations

Choosing a driver

Which JDBC driver should I use?

Using the default database

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:


Microsoft SQL Server

Deploying the JDBC Driver

  1. Download MS SQL Server JDBC driver from their site (It is not open source and cannot be distributed with DbFit.)
  2. Deploy sqljdbc4.jar in DbFit’s lib folder - the same folder as dbfit-XXX.jar.
  3. Deploy auth\x86\sqljdbc_auth.dll in %programfiles(x86)%\Java\jre7\bin folder to allow integrated windows authentication

Database Engine configuration

SQL Express Specific

Network and firewall settings

Connection string to MS SQL Server database

Examples

!|Connect|myhost:1433|myuser|mypassword|mydbname|

!|Connect|myhost|myuser|mypassword|mydbname|

!|Connect|myhost\myinstance|myuser|mypassword|mydbname|

!|Connect|myhost;instanceName=myinstance|myuser|mypassword|mydbname|

!|Connect|jdbc:sqlserver://myhost\myinstance;user=myuser;password=mypassword;databaseName=mydbname|

!|Connect|jdbc:sqlserver://myhost;integratedSecurity=true|


Teradata

Deploying the JDBC Driver

  1. Download the Teradata JDBC driver from their site (It is not open source and cannot be distributed with DbFit.)
  2. Deploy terajdbc4.jar and tdgssconfig.jar in DbFit’s lib folder - the same folder as dbfit-XXX.jar.

Connection string to a Teradata database

Examples

!|Connect|myhost|myuser|mypassword|mydbname|

!|Connect|myhost|myuser|mypassword|

!|Connect|myhost/LOGMECH=LDAP|myuser|mypassword|

!|Connect|myhost/TMODE=TERA|myuser|mypassword|

!|Connect|myhost/TMODE=ANSI|myuser|mypassword|

!|Connect|jdbc:teradata://myhost/user=myuser,password=mypassword,database=mydbname,tmode=TERA,logmech=td2|


Netezza

Deploying the JDBC Driver

  1. Download the Netezza JDBC driver from their site (It is not open source and cannot be distributed with DbFit.)
  2. Deploy nzjdbc.jar in DbFit’s lib folder - the same folder as dbfit-XXX.jar.

Connection string to a Netezza database

Examples

!|Connect|192.168.85.128:5480|admin|password|NETEZZATEST|


Informix

  1. [Download the Informix driver from the IBM site] (http://www-03.ibm.com/software/products/en/infjdbc).
  2. Deploy ifxjdbc.<major>-<minor>-<tag>.jar (e.g. ifxjdbc-4.10.JC4W1.jar) in DbFit’s lib folder - the same folder as dbfit-XXX.jar.

Connection string to an Informix database

Examples

!|Connect|<myHost>:<myTcpPortNumber>|<myUserName>|<myPassword>|<myDbName>|

!|Connect|jdbc:informix-sqli://<myHost>:<myTcpPortNumber>/<myDbName>:INFORMIXSERVER=<myServer>;user=<myUserName>;password=<myPassword>|


blog comments powered by Disqus