Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    10

    Unanswered: db7.2 fp8 to fp11

    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

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: db7.2 fp8 to fp11

    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

    Originally posted by marvl
    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

  3. #3
    Join Date
    Feb 2004
    Posts
    10

    fp8 to fp11 upgrade

    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.

  4. #4
    Join Date
    Feb 2004
    Posts
    10

    fp8 to fp11 problem solved

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •