Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: Problem migrating DB2 database from 32-bit DB2 8.1FP7 to 64-bit

    I am migrating a DB2 database called WAREHOUS from a 32-bit DB2 8.1FP7 sytem to a 64-bit DB2 9.5 system I am getting errors when cloning the database using db2move and db2look. The sql is captured ing a file called db2look_warehous.sql. When I run the sql using the command "db2 -tvf WAREHOUS db2look_warehous.sql" I get a small number of errors but one in particular is as follows:

    Here is the SQL in the db2look_warehous.sql file:

    CREATE FUNCTION "ITMUSER"."ADDXUNITS"( NDate VARCHAR(16), NVal INTEGER,
    NUnit VARCHAR(10) ) RETURNS VARCHAR(20) SPECIFIC ADDXUNITS LANGUAGE SQL
    CONTAINS SQL BEGIN ATOMIC DECLARE dateRet VARCHAR(20); DECLARE fYear
    CHAR(4); DECLARE vYear CHAR(2); DECLARE vMonth CHAR(2); DECLARE vDay
    CHAR(2); DECLARE vDate TIMESTAMP; DECLARE vDate2 TIMESTAMP; DECLARE
    vDateVC VARCHAR(30); IF NDate IS NULL OR NVal IS NULL OR NUnit IS NULL
    THEN SET dateRet = 'Null Input'; RETURN dateRet; END IF; IF length(NDate)
    < 16 OR NUnit NOT IN ('M','D') THEN SET dateRet = 'Invalid Input'; RETURN
    dateRet; END IF; SET vYear = substr(NDate,2,2); SET vMonth = substr(NDate,4,2);
    SET vDay = substr(NDate,6,2); IF INT(vYear) < 50 THEN SET fYear = '20'
    || vYear; ELSE SET fYear = '19' || vYear; END IF; SET vDate = timestamp(fYear
    || '-' || vMonth || '-' || vDay || '-00.00.00' ); IF NUnit = 'M' THEN SET
    vDate2 = vDate + NVal months; ELSE SET vDate2 = vDate + NVal days; END
    IF; SET vDateVC = VARCHAR(vDate2); SET dateRet = '1' || substr(vDateVC,3,2)
    || substr(vDateVC,6,2) || substr(vDateVC,9,2) || '000000000'; RETURN dateRet;
    END

    and here is the error when runnig the sql:

    CREATE FUNCTION "ITMUSER"."ADDXUNITS"( NDate VARCHAR(16), NVal INTEGER,
    NUnit VARCHAR(10) ) RETURNS VARCHAR(20) SPECIFIC ADDXUNITS LANGUAGE SQL
    CONTAINS SQL BEGIN ATOMIC DECLARE dateRet VARCHAR(20); DECLARE fYear
    CHAR(4); DECLARE vYear CHAR(2); DECLARE vMonth CHAR(2); DECLARE vDay
    CHAR(2); DECLARE vDate TIMESTAMP; DECLARE vDate2 TIMESTAMP; DECLARE
    vDateVC VARCHAR(30); IF NDate IS NULL OR NVal IS NULL OR NUnit IS NULL
    THEN SET dateRet = 'Null Input'; RETURN dateRet; END IF; IF length(NDate)
    < 16 OR NUnit NOT IN ('M','D') THEN SET dateRet = 'Invalid Input'; RETURN
    dateRet; END IF; SET vYear = substr(NDate,2,2); SET vMonth = substr(NDate,4,2)
    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 "END-OF-STATEMENT" was found following "=
    substr(NDate,4,2)". Expected tokens may include: "<delim_semicolon>". LINE
    NUMBER=9. SQLSTATE=42601

    SET vDay = substr(NDate,6,2); IF INT(vYear) < 50 THEN SET fYear = '20' || vYear; ELSE SET fYear = '19' || vYear; END IF; SET vDate = timestamp(fYear || '-' || vMonth || '-' || vDay || '-00.00.00' ); IF NUnit = 'M' THEN SET vDate2 = vDate + NVal months; ELSE SET vDate2 = vDate + NVal days; END IF; SET vDateVC = VARCHAR(vDate2); SET dateRet = '1' || substr(vDateVC,3,2) || substr(vDateVC,6,2) || substr(vDateVC,9,2) || '000000000'; RETURN dateRet
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0206N "VDAY" is not valid in the context where it is used. SQLSTATE=42703

    END
    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 "END-OF-STATEMENT" was found following "END".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    Can anyone help?

    Thanks,
    JHC

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I know it's a pain in the neck to search in this forum; this question has been answered, like, 15 million times already...

    In short: use db2look with the "-td@" option, then execute the DDL script with the same option. If you must know why, read this thread: http://www.dbforums.com/db2/1634932-...-question.html
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2009
    Posts
    2

    thanks!

    Nick,

    Sorry for the duplication. I noticed this before in my search but didn't quite understand it at first til you explained it. Thank you!

    jhc

Posting Permissions

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