Insert data into a column with user defined datatype in DB2

311 Views Asked by At

I created a user defined datatype using the following query

CREATE TYPE test3.data_type1 AS
(xmldata clob(204800) logged NOT compact)
INSTANTIABLE
inline LENGTH 195
MODE DB2SQL;

I then created a table using the following query:

CREATE TABLE test3.test_table
(col1 varchar(256),
col2 test3.data_type1,
col3 varchar(254),
col4 varchar(128),
col5 varchar(128) WITH DEFAULT USER,
col6 timestamp WITH DEFAULT,
col7 varchar(128),
col8 timestamp)
;

I am now trying to insert data into this table, test_table using:

INSERT INTO test3.test_table (col2)
VALUES
(data_type1('aaaa'));

The error I get is as follows:

SQL Error [42884]: No authorized routine named "DATA_TYPE1" of type "FUNCTION" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.26.14

I also tried multiple options of this insert statement but with no luck. I also tried creating a function as mentioned in this post INSERT into DB2 with a user defined type column But, I get an error while creating this function as well. Here is the function as per this post :

CREATE FUNCTION data_type1 (xmldata clob(204800)) 
RETURNS data_type1
BEGIN ATOMIC 
    DECLARE t data_type1;
    SET t = data_type1();
    SET t..xmldata = xmldata;
    RETURN t;
END

The error I get with this is as follows:

SQL Error [42704]: "DATA_TYPE1" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=4.26.14

DB2 version - 11.5 LUW Any pointers in the right direction would help.

0

There are 0 best solutions below