db2look generates incorrect function mapping DDL causes users
need to refine their db2look output before running it to
recreate their federated objects.
-- --------------------------------
-- DDL Statements for FUNCTION MAPPING
-- --------------------------------
CREATE FUNCTION MAPPING "FM_TO_CHAR"
FOR "SYSIBM".TO_CHAR(SYSIBM.TIMESTMP,SYSIBM.VARCHAR)
SERVER "SERV1"
OPTIONS
(REMOTE_NAME 'TO_CHAR'
);
In above DDL, TIMESTMP should be TIMESTAMP, VARCHAR should
be
VARCHAR().
Reproduce:
create wrapper net8;
create server serv1 type oracle version '11.1' wrapper net8
options(node '****');
create user mapping for <USER NAME> server serv1
options(remote_authid '<USER ID>',remote_password '<PASSWORD>');
CREATE FUNCTION MAPPING FM_TO_CHAR FOR
SYSIBM.TO_CHAR(SYSIBM.TIMESTAMP,SYSIBM.VARCHAR()) SERVER serv1
OPTIONS (REMOTE_NAME'TO_CHAR') ;
db2look -d <db name> -e -fedonly -o db2look.sql
-- --------------------------------
-- DDL Statements for FUNCTION MAPPING
-- --------------------------------
CREATE FUNCTION MAPPING "FM_TO_CHAR"
FOR "SYSIBM".TO_CHAR(SYSIBM.TIMESTMP,SYSIBM.VARCHAR)
SERVER "SERV1"
OPTIONS
(REMOTE_NAME 'TO_CHAR'
);
=> db2 "SELECT substr(funcschema,1,14),substr(funcname,1,40)
from SYSIBM.SYSFUNCMAPPINGS"
1 2
-------------- ----------------------------------------
SYSIBM TO_CHAR(SYSIBM.TIMESTMP,SYSIBM.VARCHAR)
1 record(s) selected.
=> db2 "CREATE FUNCTION MAPPING "FM_TO_CHAR" FOR
"SYSIBM".TO_CHAR(SYSIBM.TIMESTMP,SYSIBM.VARCHAR) SERVER "SERV1"
OPTIONS (REMOTE_NAME'TO_CHAR') "
DB21034E The command was processed as an SQL statement because
it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "VARCHAR" was found following
"IBM.TIMESTMP,
SYSIBM.". Expected tokens may include: "LONG". LINE NUMBER=1.
SQLSTATE=42601
=> db2 "CREATE FUNCTION MAPPING "FM_TO_CHAR" FOR
"SYSIBM".TO_CHAR(SYSIBM.TIMESTMP,SYSIBM.VARCHAR()) SERVER
"SERV1" OPTIONS (REMOTE_NAME'TO_CHAR') "
DB21034E The command was processed as an SQL statement because
it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0204N "SYSIBM.TIMESTMP" is an undefined name. LINE
NUMBER=2.
SQLSTATE=42704
=> db2 "CREATE FUNCTION MAPPING "FM_TO_CHAR" FOR
"SYSIBM".TO_CHAR(SYSIBM.TIMESTAMP,SYSIBM.VARCHAR()) SERVER
"SERV1" OPTIONS (REMOTE_NAME'TO_CHAR') "
DB20000I The SQL command completed successfully. |
