JR38047: CREATE NICKNAME FAILS IF REMOTE TERADATA DATABASE HAS UDF CREATED WITH NAME ISNUMERIC.

DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• If you have created an UDF named "IsNumeric" on remote Teradata
  datasource, when create nickname for a remote table located on
  same database, the operation fails and return SQL1822N error,
  the error message is as follow:
  SQL1822N  Unexpected error code "5589" received from data source
  "SERV1".
  Associated text and tokens are "Function 'IsNumeric' does not
  exist.".
  SQLSTATE=560BD
  
  You can reproduce this error by following steps:
  1. Create UDF "IsNumeric" on target Teradata data source, for
  example, issuing following commands by Teradata tool BTEQ:
    CREATE FUNCTION IsNumeric(param1 INTEGER, param2 INTEGER)
    RETURNS CHAR
    LANGUAGE C
    NO SQL
    PARAMETER STYLE TD_GENERAL
    EXTERNAL NAME 'CS!udfsrc!/<udfpath>/udfsrc.c!F!IsNumeric';
  
  2. On Federated database, create Federated objects (Wrapper,
  Server, User Mapping) then, create nickname for exiting remote
  table.
  
    CREATE WRAPPER <wrapper name> LIBRARY '<Teradata Wrapper
  Library>' OPTIONS (DB2_FENCED 'Y');
  
    CREATE SERVER  <server name>  TYPE teradata  VERSION <version
  number>  WRAPPER <wrapper name>  OPTIONS(NODE '<node name>');
  
    CREATE USER MAPPING FOR <user id> SERVER <server name> OPTIONS
  (REMOTE_AUTHID '<auth id>', REMOTE_PASSWORD '<password>')
  
    CREATE NICKNAME <nickname> for <server name>.<schema
  name>.<table name>;
  
  DB21034E  The command was processed as an SQL statement
  because it was not a valid Command Line Processor command.
  During SQL processing it returned:
  SQL1822N  Unexpected error code "5589" received from data
  source "<server name>".
  Associated text and tokens are "Function 'IsNumeric' does
  not exist.". SQLSTATE=560BD
  

Local fix

• A workaround for this issue is, remove Teradata UDF "isNumeric"
  or alter its name to any else.
  

Problem summary

• User affected:
   Users of Federation Server who use Teradata wrapper
  Problem Descripion and Summary:
   See error description.
  

Problem conclusion

• Problem was first fixed in V97fp4. This fix should be applied
  Federation Server.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  JR38044

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  TERADATA WRAPPE

• Fixed component ID

  5724N9709

Applicable component levels

• R970 PSY

     UP