Skip to content

12. IBM DB2 Express-C 10.5

We will now address the porting to DB2 of what was done with MySQL. Both DBMSs indeed use the same strategy for generating primary keys.

12.1. Setting up the working environment

12.1.1. Eclipse environment

We will be working with the following Eclipse environment:

  

The DB2 projects listed above can be found in the [<examples>/spring-database-config\db2\eclipse] folder.

Note: Press [Alt-F5] to regenerate all Maven projects.

12.1.2. Generating the databases

As we did with Oracle, we will need to install the DB2 JDBC driver in the local Maven repository.

  

The [install.bat] file contains the following code:

"%M2_HOME%\bin\mvn.bat" install:install-file -Dfile=db2jcc4.jar -Dpackaging=jar -DgroupId=com.ibm.jdbc -DartifactId=db2jcc4 -Dversion=1.0

where [%M2-HOME%] is the Maven installation directory (see section 23.2). After this installation, the DB2 JDBC driver can be referenced in the [pom.xml] files using the following dependency:


        <dependency>
            <groupId>com.ibm.jdbc</groupId>
            <artifactId>db2jcc4</artifactId>
            <version>1.0</version>
</dependency>

Throughout the rest of this guide, connections to DB2 databases are made using the credentials [db2admin / db2admin]. Start DB2 and its client [Db2Manager] (see section 23.8).

  

The database [DBPROD] is the [dbproduits] database from the previous DBMS. However, [DB2Manager] did not allow me to use that name (perhaps because it was too long for it). Now we create the [PRODUITS] table using the following Eclipse run configuration [generic-create-dbproduits-jpa]:

The execution creates the [PRODUITS] table in the [DBPROD] database:

  • in [1] above, the sequence was not generated by [OpenJpa] but by DB2 itself, which uses it internally to generate primary keys;

Now, run the configurations:

  • [spring-jdbc-generic-01.IntroJdbc01];
  • [spring-jdbc-generic-01.IntroJdbc02];
  • [spring-jdbc-generic-03.JUnitTestDao1];
  • [spring-jdbc-generic-03.JUnitTestDao2];

They should all pass.

Now let’s generate the [dbproduitscategories] database. It will be called [DBCAT] here for the reasons already mentioned regarding database name length restrictions. Repeat the procedure used to create [DBPROD] for [DBCAT].

  

We will now create the tables for the [DBCAT] database from Eclipse using the [generic-create-dbproduitscategories-openjpa] configuration:

This execution yields the following result:

 

You must modify the [VERSIONING] column of the five tables so that they have 1 as the default value:

 
 

This step must be performed for all five tables.

Now, run the configurations:

  • [spring-jdbc-generic-04.JUnitTestDao];
  • [spring-jpa-generic-JUnitTestDao-openjpa];

Both should pass.

12.2. Configuring the JDBC layer

 

The [db2-config-jdbc] project configures the [JDBC] layer of the following test architecture:

The project is analogous to the [mysql-config-jdbc] configuration project for the JDBC layer of the MySQL DBMS (see Section 3.3). We present only the changes:

The [pom.xml] file imports the DB2 JDBC driver:


<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>dvp.spring.database</groupId>
    <artifactId>generic-config-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>generic jdbc configuration</name>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.2.3.RELEASE</version>
    </parent>

    <dependencies>
        <!-- variable dependencies ********************************************** -->
        <!-- JDBC driver for the DBMS -->
        <dependency>
            <groupId>com.ibm.jdbc</groupId>
            <artifactId>db2jcc4</artifactId>
            <version>1.0</version>
        </dependency>
        <!-- constant dependencies ********************************************** -->
....
    </dependencies>

...
</project>
  • lines 18-22: the DB2 JDBC driver;

The second change is in the [ConfigJdbc] class, which defines the database access credentials:


    // connection parameters
    public final static String DRIVER_CLASSNAME = "com.ibm.db2.jcc.DB2Driver";
    public final static String URL_DBPRODUCTS = "jdbc:db2://localhost:50000/dbprod";
    public final static String USER_DBPRODUITS = "db2admin";
    public final static String PASSWD_DBPRODUCTS = "db2admin";
    public final static String PRODUCT_CATEGORIES_DB_URL = "jdbc:db2://localhost:50000/dbcat";
    public final static String USER_PRODUCT_CATEGORIES_DB = "db2admin";
    public final static String PASSWD_DBPRODUCTSCATEGORIES = "db2admin";

The third modification that can be made is to the maximum number of parameters that a [PreparedStatement] can support:


    // maximum number of parameters for a [PreparedStatement]
    public final static int MAX_PREPAREDSTATEMENT_PARAMETERS = 10000;

The [JUnitTestPushTheLimits] test generates SQL statements for 5,000 products, which will generate [PreparedStatement] objects with 5,000 parameters. MySQL supported this value. DB2 did as well.

The fourth change is to the name of the [ROLES] table. This name is reserved in the DB2 DBMS. We therefore renamed it [ROLES_]:


public final static String TAB_ROLES = "ROLES_";
public static final String SELECT_ROLES_BYUSERID = "SELECT DISTINCT r.ID as r_ID, r.VERSIONING as r_VERSIONING, r.NAME as r_NAME FROM ROLES_ r, users u, USERS_ROLES ur"
+ " WHERE u.ID=:id AND ur.USER_ID=u.ID AND ur.ROLE_ID=r.ID";

12.3. Configuring the OpenJPA JPA Layer

 

The [db2-config-jpa-openjpa] project configures the [JPA] layer of the test architecture:

The project is analogous to the [mysql-config-jpa-openjpa] configuration project for the OpenJpa JPA layer of the MySQL DBMS (see Section 8.3). In fact, both DBMSs use the [@GeneratedValue(strategy = GenerationType.IDENTITY)] annotation to generate primary keys. There is only one change to make. It is in the definition of the [jpaVendorAdapter] bean in the [ConfigJpa] class:


    // the JPA provider
    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
        OpenJpaVendorAdapter openJpaVendorAdapter = new OpenJpaVendorAdapter();
        openJpaVendorAdapter.setShowSql(false);
        openJpaVendorAdapter.setDatabase(Database.DB2);
        openJpaVendorAdapter.setGenerateDdl(true);
        return openJpaVendorAdapter;
}
  • Line 6: We tell the JPA implementation that it will be working with a DB2 database. The JPA implementation will then adopt both the proprietary data types and the proprietary SQL of this DBMS.

With these changes made, running the [spring-jpa-generic-JUnitTestDao-openjpa] configuration should succeed.

12.4. Configuring the Hibernate JPA layer

 

Note: Press [Alt-F5] to regenerate all Maven projects.

The [db2-config-jpa-hibernate] project is analogous to the [mysql-config-jpa-hibernate] project (Section 6.3) with the same modifications that were used to port the [mysql-config-jpa-openjpa] project to the [db2-config-jpa-openjpa] project (Section 12.3).

Once these changes have been made, running the [spring-jpa-generic-JUnitTestDao-hibernate-eclipselink] configuration should succeed.

 

Note: Press [Alt-F5] to regenerate all Maven projects.

The [db2-config-jpa-eclipselink] project is analogous to the [mysql-config-jpa-eclipselink] (Section 7.3) with the same modifications that were used to port the [mysql-config-jpa-openjpa] project to the [db2-config-jpa-openjpa] project (Section 12.3).

With these modifications in place, the execution of the [spring-jpa-generic-JUnitTestDao-hibernate-eclipselink] configuration should succeed.

https://tahe.developpez.com
This tutorial written by Serge Tahé is made available to the public under the terms of the Creative Commons Attribution – Non-Commercial – ShareAlike 3.0 Unported.