Serge Rielau wrote:
This function was introduced in DB2 V8.2 (FP7).
Try running db2updv8
Thanks. Unfortenate that doesnt help. I still get the error:
[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2 "CALL
SYSPROC.ALTOBJ( 'VALIDATE','CRE ATE TABLE DB2INST1.T(C1 BIGINT, C3 DOUBLE
NOT NULL)',-1,?)"
SQL0443N Routine "SYSPROC.ALTOBJ " (specific name "ALTOBJ") has
returned an
error SQLSTATE with diagnostic text "SQL0204 Reason code or token:
DB2INST1.T(C1". SQLSTATE=38553
Anything else that needs to be done?
Below are steps taken to reproduce error:
[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ uname -a
Linux wb-03 2.4.21-27.ELsmp #1 SMP Wed Dec 1 21:59:02 EST 2004 i686
i686 i386 GNU/Linux
[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL08022"
with level identifier "03030106".
Informational tokens are "DB2 v8.1.0.89", "OD_14086", "MI00105_14086" ,
and
FixPak "9".
Product is installed at "/opt/IBM/db2/V8.1".
[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2updv8 -d tmp
DB2UPDV8 complete successfully for database 'tmp'.
[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2stop
2006-08-18 22.19.40 0 0 SQL1064N DB2STOP processing was
successful.
SQL1064N DB2STOP processing was successful.
[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2start
2006-08-18 22.19.45 0 0 SQL1063N DB2START processing was
successful.
SQL1063N DB2START processing was successful.
[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2 connect to tmp
Database Connection Information
Database server = DB2/LINUX 8.2.2
SQL authorization ID = DB2INST1
Local database alias = TMP
[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ cat def.ddl
DROP FUNCTION FOO;
DROP TABLE T;
CREATE TABLE T (
c1 INT NOT NULL GENERATED ALWAYS AS IDENTITY,
c2 FLOAT
);
INSERT INTO T (c2) VALUES 10, 20, 30, 40, 50, 60, 70;
DROP VIEW V;
CREATE VIEW V AS SELECT c1, c2 FROM T;
DROP TRIGGER Trg1;
CREATE TRIGGER Trg1
BEFORE INSERT ON T
REFERENCING NEW AS N
FOR EACH ROW
SET n.c2 = COALESCE(n.c2, 7);
CREATE FUNCTION FOO ()
RETURNS FLOAT
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
NOT DETERMINISTIC
RETURN
select c2 from (
select c2, rownumber() over () as x from T
) Y where x = 1 ;
[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2 -tf def.ddl
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
[db2inst1@wb-03 ~/nobackup/ltjn/test_altobj]$ db2 "CALL
SYSPROC.ALTOBJ( 'VALIDATE','CRE ATE TABLE DB2INST1.T(C1 BIGINT, C3 DOUBLE
NOT NULL)',-1,?)"
SQL0443N Routine "SYSPROC.ALTOBJ " (specific name "ALTOBJ") has
returned an
error SQLSTATE with diagnostic text "SQL0204 Reason code or token:
DB2INST1.T(C1". SQLSTATE=38553
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
