Migrating applications to a new environment with different schemas without modifying the artifacts using IBM WebSphere Adapter for JDBC

This article describes how you can dynamically connect a new schema when moving from one environment to another by enabling IBM® WebSphere® Adapter for JDBC to pick up the database schema name for tables at runtime.

Abhishek Rohira (arohira1@in.ibm.com), Software Engineer, IBM

Photo of Abhishek RohiraAbhishek Rohira is a Software Engineer working on the development and support of WebSphere Adapter at the IBM India Software Lab. He has more than 3 years of experience working with various Java technologies, including JCA. He has a Bachelor's degree in Computer Science and Engineering from the Vellore Institute of Technology, India.



25 July 2012

Also available in Russian

Introduction

One of the major issues that customers face while moving their applications built from one environment to another is to regenerate artifacts specific to the new environment. As the adapter artifacts are bound to the schema name, any change in the schema name, as required by the environment, requires regenerating artifacts using Enterprise Metadata Discovery (EMD), or manually editing the artifacts. This is not recommended and can affect how the adapter works. To reduce the pain of regenerating artifacts each time, a solution is provided in IBM WebSphere Adapter for JDBC V7.5.0.2 and later. This feature uses the environment specific SchemaName value at runtime. It means that you can use the same old artifacts to run in the new environment with the new SchemaName.

Prerequisites

This article uses the following products:

  • IBM Integration Designer V8.0
  • IBM WebSphere Adapter for JDBC Software V7.5.0.2
  • IBM Business Process Manager V7.5.1 or above

Note: The solution is valid for WebSphere Adapter for JDBC V7.5.0.2 and later and enabled on IBM DB2® and Oracle® databases only.


Possible scenarios

You can apply the solution to either of the two listed scenarios:

  • Generating artifacts with EMD and setting the environment variable name (TableNameEnv) property.
  • Enabling old artifacts to run in the new environment.

Generating new artifacts with EMD and setting the environment variable name

This section describes how to generate new artifacts using WebSphere Adapter V7.5.0.2 and later. A new property named TableNameEnv (Environment Variable Name) was added under the JDBC adapter, which sets the environment variable for the new schema. This environment variable holds the value for the new schema that has to be set in the admin console. You must specify the environment variable name during EMD. The possible scenarios are:

  • Single schema having single or multiple tables
  • Multiple schemas having single or multiple tables

Single schema having single or multiple tables

The steps for a single schema having single table and a single schema having multiple tables are the same, so we will use multiple tables as an example. The same can be applied for a single table as well. See Figure 1.

Figure 1. Schema structure: Single schema (SAMPLE_SCHEMA1) having multiple tables
Schema structure: Single schema (SAMPLE_SCHEMA1) having multiple tables

In order to create environment variable at EMD, do the following steps:

  1. Run EMD and navigate to Find Objects in the Enterprise System. Select the Edit query button while creating the query under the "Find Objects in Enterprise System" window, as shown in Figure 2.
    Figure 2. Select the Edit Query option
    Select the Edit Query option
  2. Check the text box for Prompt for additional configuration settings when adding business object, as shown in Figure 3.
    Figure 3. Click the checkbox
    Click the checkbox
  3. Run the query. This shows the list of schemas present, as shown in Figure 4.
    Figure 4. Tables under SAMPLE_SCHEMA1
    Tables under SAMPLE_SCHEMA1
  4. As you select the multiple tables to add, a pop-up windows appears stating to specify the Configuration properties. Select the checkbox to Overwrite the Schema Name at runtime using WAS Environment variable and provide a value for the Environment Variable Name, as shown in Figure 5.
    Figure 5. Environment variable name
    Environment variable name

    You will create a new environment variable later under the WebSphere Application Server admin console, which has the same name shown in Figure 5. The value of that environment variable will be the new schema shown in Figure 6.

  5. The environment variable name specified for Table1_Schema1 will be reflected in Table2_Schema1 as well.
    Figure 6. Environment variable name
    Environment variable name

    Note: It is not mandatory for Table2_Schema1 to have the same value. You can edit the Environment Variable Name value based on your choice. Specifying different value indicates that you want to move Table2_Schema1 to a different schema other than the one you chose for Table1_Schema1. You need to create one more environment variable in the admin console. After finishing EMD, the artifacts will look like those shown Listing 1 and Listing 2.

    Listing 1. Code in <schemaName>_Table1_Schema1.xsd
    <jdbcasi:JDBCBusinessObjectTypeMetadata xmlns:jdbcasi="http://www.ibm.com/xmlns/prod
    /websphere/j2ca/jdbc/metadata">
    <jdbcasi:TableName>"SAMPLE_SCHEMA1"."TABLE1_SCHEMA1"</jdbcasi:TableName>
    <jdbcasi:TableNameEnv>New_Schema1</jdbcasi:TableNameEnv>
    </jdbcasi:JDBCBusinessObjectTypeMetadata>
    Listing 2. Code in <schemaName>_Table2_Schema1.xsd
    <jdbcasi:JDBCBusinessObjectTypeMetadata xmlns:jdbcasi="http://www.ibm.com/xmlns/prod
    /websphere/j2ca/jdbc/metadata">
    <jdbcasi:TableName>"SAMPLE_SCHEMA1"."TABLE2_SCHEMA1"</jdbcasi:TableName>
    <jdbcasi:TableNameEnv>New_Schema1</jdbcasi:TableNameEnv>
    </jdbcasi:JDBCBusinessObjectTypeMetadata>

    After generating the artifacts, you need to create these environment variables in the admin console, as shown in Figure 7. Refer to Creating and deploying the environment variable.

    Figure 7. Schema structure: Multiple schemas having multiple tables
    Schema structure: Multiple schemas having multiple tables

In order to create environment variable at EMD, do the following steps:

  1. Run EMD and navigate to Find Objects in the Enterprise System. Select the Edit query button while creating the query under the "Find Objects in Enterprise System" window, as shown in Figure 8.
    Figure 8. Select the Edit Query option
    Select the Edit Query option
  2. Check the checkbox for Prompt for additional configuration settings when adding business object, as shown in Figure 9.
    Figure 9. Click the checkbox
    Click the checkbox
  3. Run the query. Figure 10 shows the list of schemas present.
    Figure 10. Multiple schemas having multiple tables
    Multiple schemas having multiple tables
  4. As you select the multiple tables to add, a pop-up windows appears stating to specify the Configuration properties. Select the checkbox to Overwrite the Schema Name at runtime using WAS Environment variable and provide a value for Environment Variable Name, as shown in Figure 11.
    Figure 11. Environment variable name
    Environment variable name

    Note: You will create a new environment variable later in the admin console, which has the same name shown in Figure 11. The value of that environment variable will be the new schema.

  5. The environment variable name specified for Table1_Schema1 will not be reflected in Table1_Schema2, as shown in Figure 12.
    Figure 12. Environment variable name
    Environment variable name

    Note: It is not mandatory for Table2_Schema1 to have the same value. You can edit the environment variable name value based on your choice. Specifying a different value indicates that you want to move Table2_Schema1 to a different schema other than the one you chose for Table1_Schema1. You need to create one more environment variable in the admin console.

  6. After finishing EMD, the artifacts look similar to Listing 3 and Listing 4.
    Listing 3. Code under <schemaName>_Table1_Schema1.xsd
    <jdbcasi:JDBCBusinessObjectTypeMetadata xmlns:jdbcasi="http://www.ibm.com/xmlns/prod
    /websphere/j2ca/jdbc/metadata">
    <jdbcasi:TableName>"SAMPLE_SCHEMA1"."TABLE1_SCHEMA1"</jdbcasi:TableName>
    <jdbcasi:TableNameEnv>EnvironVar_Schema1</jdbcasi:TableNameEnv>
    </jdbcasi:JDBCBusinessObjectTypeMetadata>
    Listing 4. Code under <schemaName>_Table1_Schema2.xsd
    <jdbcasi:JDBCBusinessObjectTypeMetadata xmlns:jdbcasi="http://www.ibm.com/xmlns/prod
    /websphere/j2ca/jdbc/metadata">
    <jdbcasi:TableName>"SAMPLE_SCHEMA2"."TABLE1_SCHEMA2"</jdbcasi:TableName>
    <jdbcasi:TableNameEnv>EnvironVar_Schema2</jdbcasi:TableNameEnv>
    </jdbcasi:JDBCBusinessObjectTypeMetadata>

Enabling old artifacts to run in the new environment

In order to use the old artifacts, you need to add a few lines of code under the top level business object (<BO>.xsd) files. Add a new property under the <jdbcasi:JDBCBusinessObjectTypeMetadata> tab of each BO. For example:

<jdbcasi:JDBCBusinessObjectTypeMetadata 
xmlns:jdbcasi="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
<jdbcasi:TableName>"SAMPLE_SCHEMA1"."TABLE1_SCHEMA1"</jdbcasi:TableName>
<jdbcasi:TableNameEnv>EnvironVar_Schema1</jdbcasi:TableNameEnv>
</jdbcasi:JDBCBusinessObjectTypeMetadata>

Add a new property named TableNameEnv under the XSD of each top level BO file. After updating the XSD, create a new application environment variable in the admin console. Refer to the next section, "Creating and deploying the environment variable".


Creating and deploying the environment variable

As the artifacts for the appropriate scenarios have been generated, now it is time to create the environment variable, with the same name as declared in EMD, in the admin console. Remember, the value of these environment variables will be the new schemas that you want the adapter to pick up at runtime.

To create the environment variable in the admin console:

  1. Open the admin console. Navigate to the Environment tab, as shown in Figure 13.
    Figure 13. Environment tab
    Environment tab
  2. After selecting appropriate scope, click New to create a new environment variable, as shown in Figure 14.
    Figure 14. New environment variable
    New environment variable
  3. Give the same name that was specified at EMD.

    Note: You need to create as many environment variables as possible in the admin console as specified at EMD. For example, under the single schema having single or multiple tables, you have specified New_Schema1, so you need to create an environment variable name of "New_Schema1". For a single schema having single or multiple tables, see Figure 15.

    Figure 15. CustomerSchema is the name of the new schema for the JDBC adapter to pick up at runtime
    CustomerSchema is the name of the new schema for the JDBC adapter to pick up at runtime

    However, under multiple schemas having single or multiple tables, you have given two environment variable values, such as "EnvironmentVariable_1" and "EnvironmentVariable_2". For a multiple schema having single or multiple tables, see Figure 16 and Figure 17.

    Figure 16. SampleSchema is a new schema where tables are moved at runtime
    SampleSchema is a new schema where tables are moved at runtime
    Figure 17. SampleSchema is a new schema where tables are moved at runtime
    SampleSchema is a new schema where tables are moved under at runtime

    Therefore, you need to create two environment variable with the same name (see Figure 18). Save it to a master configuration.

    Figure 18. SampleSchema is the new schema where some of the tables are moved at runtime
    SampleSchema is a new schema where tables are moved under at runtime
  4. After creating the environment variable, restart the server.
  5. Deploy the module to the server. The adapter will now take the new schema value specified at the admin console.

Points to remember while performing the solution

  • The solution is available from Version 7502 and later.
  • After creating the environment variable, you must restart the server.

Conclusion

This article demonstrated a solution that dynamically connects to a new schema when moving from one environment to another, either by generating new artifacts or by reusing older artifacts.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=827307
ArticleTitle=Migrating applications to a new environment with different schemas without modifying the artifacts using IBM WebSphere Adapter for JDBC
publish-date=07252012