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

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: bonehead question

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