Recommendations for multiple schemas within a database

If you support multiple concurrent development or test environments in the same system, your applications might be written with unqualified table names. When unqualified table names are used, multiple developers can execute concurrently against multiple copies of the same database schema with different qualifier names. Using unqualified table names allows multiple test and development environment schemas to reside on the same subsystem. A structured approach is required to maintain such an environment and to keep multiple instances of an application running against the database and its associated packages.

In environments that use DB2® for Linux, UNIX, and Windows databases, an entire database can be devoted to one development or test environment. Alternatively, if multiple application instances share the same database in a DB2 for Linux, UNIX, and Windows environment, or multiple development or test environments exist in a single DB2 for z/OS® subsystem, a naming convention can support independent environments in the same system.

The following list describes recommendations when using multiple schemas within a database:
  • To ensure independence of the environments, the table or other database objects, schema, and the packages must be isolated. To ensure table isolation, create identical table schemas with different sets of qualifiers.
  • When a package is bound, the QUALIFER option can be used to set the default schema. A development environment, might have multiple packages with the same package name but different qualifier settings. In addition to the qualifier option, other conventions must be used to support multiple packages with the same package name. You can use two options, collection IDs or package versioning.
    Collection IDs
    Package collections represent a grouping of packages. Collections allow multiple packages with the same name to exist in the same system. Collections can be considered as a container where uniqueness is maintained.

    If packages with the same name are in different collections, the packages can exist on the same system. The use of collections can allow many application developers to work on different versions of an application, can support test and production on the same server, and can allow a staged, fully available rollout of an application version.

    In the case of separate collections, applications will need to set the variable CURRENT PACKAGESET (or CURRENT PACKAGE PATH in DB2 for Linux, UNIX, and Windows, or in DB2 for z/OS). Setting the variable can be done with a WebSphere® DataSource setting.

    You use the pureQuery Configure utility with the -collection option to set the collection ID.

    Tip: When you use the Configure utility on a pureQueryXML file, a collection ID is added to the file. To change the collection ID in a configured file with the Configure utility -collection option, you must also use either the Configure option -setPreStatusOfAllPackages with the value set to REQUIRED, or the Configure option -cleanConfigure with the value set to TRUE.
    Package versions
    Package versioning can also achieve the same results. As with separate collections, packages with different versions can coexist, even if they are in the same collection.

    Package versions have the benefit of not requiring a special setting in the WebSphere data source or application logic. For package versioning, a consistency token in the application pureQueryXML file identifies the correct package version.

    You use the pureQuery Configure utility with the -pkgVersion option to set the package version.


Feedback