problem with UDF

**Knut Stolze** · Nov 12 '05, 05:52 AM

Re: problem with UDF

Jagdip Singh <jxs1878@cs.rit .edu> wrote:
[color=blue]
> Hi all,
> I am getting this problem. Script and error are below
>
>
> connect to jagdip user db2inst1 using ibmdb2 @[/color]
[...][color=blue]
> set current sqlid='db2inst1 ' @[/color]

Here you set the value for the SQLID special register to the string
'db2inst1'. Because of the quotes, this is treated as a case-sensitive
schema name for your statements below.
[color=blue]
> create function salesamount ( s_quant integer, p_number integer ) returns
> real
> begin atomic
> declare SalesAmount real;
> SET (SalesAmount) = (SELECT p_price from products where p_num =
> p_number); return SalesAmount*s_q uant;
> end @[/color]

A suggestion to simplify the function:

CREATE FUNCTION salesamount(s_q uant INTEGER, p_number INTEGER)
RETURNS REAL
RETURN ( SELECT p_price * s_quant
FROM products
WHERE p_num = p_number )

Also, I recommend to explicitly specify the schema name in your statement to
avoid the problem you have here:

CREATE FUNCTION db2inst1.salesa mount ...

Also for all the tables, it might be advisable to use an explicit schema
name.
[color=blue]
> alter table sales
> add CONSTRAINT sales_idx primary key(s_num)
> add foreign key (p_num)
> REFERENCES products(p_num)
> ON DELETE RESTRICT
> add foreign key (emp_num)
> REFERENCES employee(emp_nu m)
> ON DELETE RESTRICT
> add foreign key (c_num)
> REFERENCES clients(c_num)
> ON DELETE RESTRICT
> add CONSTRAINT sales_constrain t check(s_amount =
> db2inst1.salesa mount(s_quantit y, p_num)) @[/color]

First, you still can't do this as I said in the other post. A constraint
cannot refer the data in another table.

To solve your problem, you have two options:

ALTER TABLE sales
ADD CONSTRAINT sales_constrain t ( CHECK ( s_amount =
"db2inst1".sale samount(s_quant ity, p_num)) @

This works because the double-quotes tell DB2 to take the schema name as
case-sensitive, so it matches what you specified above.

The other option is to use a "SET CURRENT SQLID = db2inst1" (without the
double or single quotes and let DB2 convert the names to upper-case as is
the default.

Once you solved the syntactical issues, you will get this error:

SQL0546N The check constraint "X" is invalid. SQLSTATE=42621

There are several reasons:
(1) your UDF has a subquery, which is not allowed
(2) your UDF is defined as EXTERNAL ACTION, which is not allowed
(3) your UDF is defined as NOT DETERMINISTIC (aka VARIANT), which is not
allowed

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

**Serge Rielau** · Nov 12 '05, 05:52 AM

Re: problem with UDF

take away the qotes on sqlid. I think what you have.
You function lives in "db2inst1", but you are trying to find it in
"DB2INST1"

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

**Serge Rielau** · Nov 12 '05, 05:52 AM

Re: problem with UDF

BTW, get rid of the unnecessary SQL. RETURN (SELECT ....) will be enough.
DB2 can do A LOT of optimization for functions with only a RERTURN
statement.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

**Jagdip Singh** · Nov 12 '05, 05:52 AM

Re: problem with UDF

Thanks everyone for replying to my problem
I also was playing around and figured out the same thing as check-constraint
is very limited

Warm regards,
Jagdip Singh

"Jagdip Singh" <jxs1878@cs.rit .edu> wrote in message
news:3fb91a79$1 @buckaroo.cs.ri t.edu...[color=blue]
> Hi all,
> I am getting this problem. Script and error are below
>
>
> connect to jagdip user db2inst1 using ibmdb2 @
>
> set current sqlid='db2inst1 ' @
>
> drop function salesamount @
> drop table employee @
> drop table clients @
> drop table products @
> drop table sales @
>
> create table employee
> (emp_num integer not null,
> emp_name varchar(20),
> emp_commision real,
> emp_salary real
> ) @
>
>
> create table clients
> (c_num integer not null,
> c_name varchar(20),
> c_addr varchar(20),
> c_city varchar(15),
> c_state varchar(2),
> c_zip integer,
> c_phone integer
> ) @
>
>
> create table products
> (p_num integer not null,
> p_quantity integer,
> p_price real
> ) @
>
>
> create table sales
> (s_num integer not null,
> p_num integer not null,
> s_quantity integer not null,
> s_amount real not null,
> emp_num integer not null,
> c_num integer not null
> ) @
>
> create function salesamount ( s_quant integer, p_number integer ) returns
> real
> begin atomic
> declare SalesAmount real;
> SET (SalesAmount) = (SELECT p_price from products where p_num =[/color]
p_number);[color=blue]
> return SalesAmount*s_q uant;
> end @
>
>
> create unique index employee_idx
> on employee(emp_nu m) @
>
> create unique index clients_idx
> on clients(c_num) @
>
> create unique index products_idx
> on products(p_num) @
>
> create unique index sales_idx
> on sales(s_num) @
>
> alter table employee
> add CONSTRAINT employee_idx primary key(emp_num) @
>
> alter table clients
> add CONSTRAINT clients_idx primary key(c_num) @
>
> alter table products
> add CONSTRAINT products_idx primary key(p_num) @
>
> alter table sales
> add CONSTRAINT sales_idx primary key(s_num)
> add foreign key (p_num)
> REFERENCES products(p_num)
> ON DELETE RESTRICT
> add foreign key (emp_num)
> REFERENCES employee(emp_nu m)
> ON DELETE RESTRICT
> add foreign key (c_num)
> REFERENCES clients(c_num)
> ON DELETE RESTRICT
> add CONSTRAINT sales_constrain t check(s_amount =
> db2inst1.salesa mount(s_quantit y, p_num)) @
>
>
> this script is giving me problem
>
> DB21034E The command was processed as an SQL st
> valid Command Line Processor command. During SQ
> SQL0440N No function by the name "SALESAMOUN T"
> was found in the function path. SQLSTATE=42884
>
> can any one help me
>
> regards,
> Jagdip
>
>
>
>
>
>
>
>
>
>
>[/color]

problem with UDF

problem with UDF

Comment

Comment

Comment

Comment