problem with UDF

[Jagdip Singh]

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 = p_number);
return SalesAmount*s_quant;
end @
create unique index employee_idx
on employee(emp_num) @

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_num)
ON DELETE RESTRICT
add foreign key (c_num)
REFERENCES clients(c_num)
ON DELETE RESTRICT
add CONSTRAINT sales_constraint check(s_amount =
db2inst1.salesamount(s_quantity, 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 "SALESAMOUNT"
was found in the function path. SQLSTATE=42884

can any one help me

regards,
Jagdip

[Knut Stolze]

Jagdip Singh <jx*****@cs.rit.edu> wrote:

Hi all,
I am getting this problem. Script and error are below
connect to jagdip user db2inst1 using ibmdb2 @ [...] set current sqlid='db2inst1' @
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.
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_quant;
end @
A suggestion to simplify the function:

CREATE FUNCTION salesamount(s_quant 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.salesamount ...

Also for all the tables, it might be advisable to use an explicit schema
name.
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_num)
ON DELETE RESTRICT
add foreign key (c_num)
REFERENCES clients(c_num)
ON DELETE RESTRICT
add CONSTRAINT sales_constraint check(s_amount =
db2inst1.salesamount(s_quantity, p_num)) @

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_constraint ( CHECK ( s_amount =
"db2inst1".salesamount(s_quantity, 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]

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]

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]

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" <jx*****@cs.rit.edu> wrote in message
news:3f********@buckaroo.cs.rit.edu...

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 = p_number); return SalesAmount*s_quant;
end @
create unique index employee_idx
on employee(emp_num) @

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_num)
ON DELETE RESTRICT
add foreign key (c_num)
REFERENCES clients(c_num)
ON DELETE RESTRICT
add CONSTRAINT sales_constraint check(s_amount =
db2inst1.salesamount(s_quantity, 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 "SALESAMOUNT"
was found in the function path. SQLSTATE=42884

can any one help me

regards,
Jagdip