Specifying wrapper information in the federated.sql file

When you use the db2look command to change the wrapper that you use to access a data source, you need to edit the SQL statements in the federated.sql file.

Procedure

  1. Ensure that the SQL statements in the federated.sql file are in the following order:
    1. Create the wrapper.
    2. Create the server definitions.
    3. Create the user mappings.
    4. Create the nicknames, including nicknames for remote tables that were created using transparent DDL.
    5. Alter the nicknames to override the default mappings.
  2. Change the wrapper name.
    The wrapper name is on both the CREATE WRAPPER and CREATE SERVER statement.
    • Use the default wrapper name so that you do not have to specify the library file name.
    • If you do not use the default wrapper name, ensure that the wrapper library name is correct. The wrapper libraries are in the file system. On UNIX systems, the wrapper libraries are in the instancehome/sqllib/lib directory where instancehome is the home directory of the instance owner. On Windows systems, the wrapper libraries are in the %DB2PATH%\SQLLIB\BIN directory where %DB2PATH% is the directory where Db2® is installed, for example, C:\Program Files\IBM\sqllib. The CREATE WRAPPER statement must specify the base library of the wrapper, which is the library name. The base library name does not include, 'F' (fenced) or 'U' (unfenced).
  3. If the AUTHORIZATION and PASSWORD parameters are used with the CREATE SERVER statement, the values that are associated with these parameters are empty in the SQL statements generated by the db2look command. You must add the correct values for these parameters.
  4. Manually enclose the remote data types in the CREATE TYPE MAPPING statements in double quotation marks (") if you want the characters to be case sensitive.
    The remote data types in the generated CREATE TYPE MAPPING statements are not enclosed with double quotation marks.
  5. When you run db2look on an upgraded database, empty parentheses are added not only against parameterized data types that do not have length but also against non-parameterized data types. If you have non-parameterized data types, remove the empty parentheses.
  6. Insert the correct passwords in the CREATE USER MAPPING statements.
  7. Check the SQL statements for any local views that are created by using the WITH CHECK OPTION clause. This clause is no longer supported. Remove the SQL statements that create these local views from the federated.sql file.