![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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.
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.
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
In order to create environment variable at EMD, do the following steps:
-
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
-
Check the text box for Prompt for additional
configuration settings when adding business object, as shown
in Figure 3.
Figure 3. Click the checkbox
-
Run the query. This shows the list of schemas
present, as shown in Figure 4.
Figure 4. Tables under SAMPLE_SCHEMA1
-
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
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.
- The environment variable name specified for
Table1_Schema1 will be reflected in Table2_Schema1 as well.
Figure 6. 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
In order to create environment variable at EMD, do the following steps:
-
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
-
Check the checkbox for Prompt for additional
configuration settings when adding business object, as shown
in Figure 9.
Figure 9. Click the checkbox
- Run the query. Figure 10 shows the list of schemas
present.
Figure 10. Multiple schemas having multiple tables
-
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
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.
- 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
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.
-
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:
-
Open the admin console. Navigate to the Environment tab,
as shown in Figure 13.
Figure 13. Environment tab
- After selecting appropriate scope, click New to create a
new environment variable, as shown in Figure 14.
Figure 14. New environment variable
- 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
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
Figure 17. SampleSchema is a new schema where tables are moved 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
- After creating the environment variable, restart the server.
- 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.
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.
- WebSphere Adapters product
page: Sample applications that show you how to use various
WebSphere Adapters.
-
WebSphere Adapters Information Center: Product benefits, product
descriptions, product news, case studies, training information, support
information, and more.
-
WebSphere Adapters product
library:
Product demos, Redbooks, white papers, and more.
-
IBM Redbook: WebSphere Business
Integration Adapters: This IBM Redbook takes you through the full
life cycle of an adapter development project - design, build, test,
deployment, and implementation on multiple broker types, using both
out-of-the-box and custom adapters.
-
WebSphere Adapters support: A
searchable database of support problems and their solutions, plus
downloads, fixes, problem tracking, and more.
Abhishek Rohira is a Software Engineer working on the development and support of WebSphere Adapter with IBM - ISL. He has 2.5 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.
