Create a Virtual Table in DB2 to Configure a Lookup for a Multi-Column Table
Use this article to create a view in DB2 to configure a lookup for a table with numerous columns.
-
Compile a list of column names that are needed for the lookup and the table name in the DB2 database.
-
Verify there are no data type mismatches between databases.
-
Draft a Create View statement similar to below.
CREATE VIEW <NAME-VIEW>
AS
SELECT
<column_names>, <column_names> , <column_names>
FROM <TABLENAME>
-
Execute the Select query to verify it returns data successfully. This can also be performed in SQL Execute.
SELECT
<column_names>, <column_names> , <column_names>
FROM <TABLENAME>
-
If the query has errors, modify the query until it returns data successfully.
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>
-
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.
-
Occasionally, fields cannot be mapped due to a datatype difference or mismatch in tables. To resolve this issue, perform casting for the field.
-
Determine the data type in the both tables for the fields experiencing the issue and field length.
-
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>