PURPOSE
This article describes how to create a View in DB2. This maybe useful when configuring a lookup for a table with numerous columns.
STEPS
1. Compile a list of column names that are needed for the lookup and the table name in the DB2 database. Also verify there are no data type mismatches between databases.
2. Draft a Create View statement similar to below.
CREATE VIEW <NAME-VIEW>
AS
SELECT
<column_names>, <column_names> , <column_names>
FROM <TABLENAME>
3. Prior to creating the View using Control Center execute the Select query to verify it returns data successfully. This can also be performed in SQL Execute. If the query is successful proceed to step 4. If there are errors modify the query until it returns data successfully.
SELECT
<column_names>, <column_names> , <column_names>
FROM <TABLENAME>
4. Using Control Center or SQL Execute run the Create View statement.
CREATE VIEW <NAME-VIEW>
AS
SELECT
<column_names>, <column_names> , <column_names>
FROM <TABLENAME>
5. Configure the respective application to use the View for data retrieval. When performing configuration the View name will need to be used in place of the original table.
Cast Due to Data Type Mismatch
Occasionally fields cannot be mapped due to a datatype difference in tables. To resolve this issue casting will have be performed for the field.
1. Determine the data type in the both tables for the fields experiencing the issue and field length.
2. As part of the View cast the field to the data type used in the Synergize table. This is illustrated below.
TMW Table Synergize Table
DB2 Data type Microsoft SQL Data type
<Field Name> INTEGER <Field Name> VARCHAR
CREATE VIEW <NAME-VIEW>
AS
SELECT
<column_names>, CAST (<column_name> AS VARCHAR(numeric_value)) AS <column_name1> FROM <TABLENAME>