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.

  1. Compile a list of column names that are needed for the lookup and the table name in the DB2 database.

  2. Verify there are no data type mismatches between databases.

  3. Draft a Create View statement similar to below.

CREATE VIEW <NAME-VIEW>

AS

SELECT

<column_names>, <column_names> , <column_names>

FROM <TABLENAME>

  1. 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>

  1. 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>

  1. 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.

  2. Occasionally, fields cannot be mapped due to a datatype difference or mismatch in tables. To resolve this issue, perform casting 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>