In order to try to get around a UDF problem reported on earlier, I upgraded my DB2 UDB V7.2 on windows 2000 system from fp8 to fp11. After i did that, I dropped the trigger and functions I had created under FP8 and when I tried to recreate the function with the same create function statement, I now get this error:

SQL0104N An unexpected token "name" was found following "external ".

Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

The Create statement is:
create function testudf (varchar(10))
returns integer
language java
parameter style java
not fenced not null call
no sql
external action
external name 'javaUDFs.testudf'

Db2diag.log shows the following:

2004-02-12-14.48.59.688000 InstanceB2 Node:000
PID:2340(db2syscs.exe) TID:2564 Appid:*LOCAL.DB2.040212194753
access_plan_manager sqlra_compile_var Probe:20 Database:MEH
DIA1033C Error using TRANSLATE function with "" arguments when attempting to
TRANSLATE "" to "" in string "".

ZRC=0xFFFF8A21

My thoughts are that the problem is caused by the known problem:

2.10.7 Data corruption after moving from DB2 Version 7 FixPak 8 or earlier
This corruption problem affects data moved from DB2 Version 7 FixPak 8 or
earlier to any of the following:
Version 7 FixPak 9 or later
Version 8
Version 8 FixPak 1 or later
DB2 UDB Version 7 FixPak 9 and higher corrects a data corruption error. The
corruption was originally caused in previous versions of DB2 by a double
conversion of data sent from a client to a DB2 UDB Unicode database via a JTA
connection. Any data other than ASCII invariant characters (hexadecimal value
between 00 and 7F) that existed prior to Version 7 FixPak 9 is affected. After
applying Version 7 FixPak 9 or later , the data corruption becomes visible upon
retrieval of the CHAR, VARCHAR, and CLOB data.
To fix the previously corrupted data:
Export data using the same code page that the client used to put the data into
the database.
Migrate the database to the later version of DB2.
Import the data from Step 1 using the Unicode code page 1208.

Problem is that, if I am correct in assuming that this is the problem, I don't understand the workaround instructions. Any assistence would be greatly appreciated.

Thanks much.

MarvL

Hi MarvL,

I don't see a link between your problem and the error description for the db2 bug.

I would try and rearrange the udf definition according to the docs. I know that db2 is picky about the sequence of clauses in triggers and udfs. The external name clause for example should come right between the returns clause and the language clause, if I am not completely wrong for some reason - like being off topic :-)

Johann

Thank you for responding Johann. I too could see no relation between the errors I was receiving and the function definition. The create function statement was working OK with fp8. I did try moving the 'external name' to a couple of different places in the create function, but it didn't like the statement no matter where I put. My SQL reference doesn't seem to care where the 'external name' is in the CREATE FUNCTION as it is all over the place in their examples. I am very confused and do not know what to try next.

create function command gave errors reported from command center. When entered from a command line, worked fine. ????