Configure pureQuery to use multiple DB2 package collections

Set up a pureQuery Java Persistence API (JPA) application to use multiple DB2 package collections.

It is possible for multiple copies of a database schema to exist. This situation might happen in a partitioned database schema where there is one database for east coast employee data and another database for west coast employee data. In this case, the two databases have the same schema. There might be two databases with two database catalogs. Or there might be only one database, in which case, the high-level qualifier of the table names (the schema name) must be different. Since the schemas are the same, there can be a single set of JPA entities used to access both sets of data. There are several ways to configure JPA to handle these situations.

Important: When there are multiple persistence units, either with separate databases or a single database, you must run the wsdbgen command once for each persistence unit.

The following three scenarios exist that require the use of multiple DB2 package collections. If we need more information, read about configuring an application to use IBM Optim™ PureQuery Runtime.

1. When there are two persistence units with different data source names, using static SQL, two sets of DB2 packages exist: one DB2 package in each database. Since two persistence units exist, two persistence_unit_name.pdqxml files for the JPA runtime environment exist.

2. If the tables are in a single database, then two persistence units can also be used. In this case, the data source is the same in both persistence units. However, the schema name property, wsjpa.jdbc.Schema must be different. There are two sets of DB2 packages. Each DB2 package must have a different package name or a different package collection name. Both the wsdb2gen and the DB2 bind command have options to specify the package collection and package names.

3. We can create a single persistence unit, which will eliminate the need to maintain two persistence unit configurations and run the wsdbgen command multiple times. This configuration requires a common package name. Thus the package collection names must be different. Use the createEntityManager(Map map) method, where the map contains the values for the wsjpa.jdbc.Schema and wsjpa.jdbc.CollectionId properties to specify the package collection name and schema name.

Related tasks

Configure JDBC providers to use pureQuery to access DB2

wsdbgen command