Hi all,

I am trying to implement UDF which will truncate 4 tables as part of
UDF. The reason to choose DB2 UDF as apposed to DB2 Procedure is one
of our Data Stage job uses this function as part of SELECT query.

For example, if the datastage job wants to truncate any tables, it
uses following query
{code}SELECT TRUNCATE_TABLE_PARTITION('table-name','search-string')
from sysibm.sysdummy1;{code}

UDF Function that is created is

{code}
CREATE FUNCTION TRUNCATE_TABLE_PARTITION(p_table_name VARCHAR(100),
p_suffix VARCHAR(100) )
RETURNS integer
SPECIFIC TRUNCATE_TABLES
LANGUAGE SQL
MODIFIES SQL DATA
NO EXTERNAL ACTION


------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
RETURN
CASE (P_TABLE_NAME)
WHEN 'TB01' THEN DELETE FROM TB01 WHERE col1=p_suffix
WHEN 'TB02' THEN DELETE FROM TB02 WHERE col1=INTEGER(p_suffix)
WHEN 'TB03' THEN DELETE FROM TB03 WHERE col1=INTEGER(p_suffix)
WHEN 'TB04' THEN DELETE FROM TB04 WHERE col1=p_suffix
END
{code}


When i try to implement this user defined function, i get SQLCODE=-104
and SQLSTATE=42601 error.

My question is
1) If my UDF is simply truncating the tables what must it *RETURN*
type
2) Do i need to modify any thing in the above mentioned UDF.

Thanks Gilroy

On 2010-06-15 17:59, Gilroy Gonsalves wrote:
The main purpose of a function is to return something, but it may under
restricted circumstances have side effects. Not the other way around.

What you describe does not really belong in a sql function, but you can
squeeze it in. First, MODIFIES SQL DATA requires that the function
returns a table not a scalar (and since you modifies sql data, you have
to specify that). Something like:

create function myfun (
p_table_name VARCHAR(100),
p_suffix VARCHAR(100)
)
returns table( x int )
LANGUAGE SQL
MODIFIES SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN ATOMIC

if p_table_name = 'TB01' then
delete from tb01 where col1 = p_suffix;
elseif p_table_name = 'TB02' then
delete from tb02 where col1 = int(p_suffix);
end if;

return values (1);
END
@

You call a table function like:

select * from table(myfun('TB01', '3'));


I don't think it is a good idea to use functions this way, but if it is
absolutely necessary you may succeed with something like the above.


/Lennart


> Thanks Gilroy

On Jun 16, 5:27*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:


Hi Lennart,

Much appreciated for your help. I followed the method of using SQL
Procedure. But since we are migrating our environment from Oracle to
DB2, some of the data stage Jobs uses Oracle function which requires
the table to be truncated as SELECT query. As per current project
timeline, we are simply migrating it to DB2 maintaining the same
Oracle function/Procedure in DB2 without changing data stage jobs.

Do you have any suggestion, of achieving the same case with different
alternative. Again, DELETE is something which i am unhappy off. 50
millions rows each day populate this table and delete this 50 million
each day as part of Nightly batch. So the LOGGING which the DELETE
stmt will do is what concerns me a lot.

The tables is currently MDC, PARTITION and distributed, and i believe
DETACH...followed by ADD partition.......then DROP DETATACHED
partition is something which is ticking my head. Truncate is part of
NIGHTLY BATCH only.

Any suggestions.

Thanks
Gilroy

On 2010-06-16 12:27, Gilroy Gonsalves wrote:
If you have range partitioning and can detach partitions I believe that
this will outperform the alternatives (cave eat, I haven't used r.p.
other than to play with, so there might be something lurking around).

The obvious question is how to do that from a function? Perhaps it is
possible to write an external function in say c, that can use some db2
api and solve the problem that way. If that is not possible I would look
into the possibility to do a call to a procedure from the function.

Sorry for not being of more help

/Lennart

On Jun 16, 7:34*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:


Calling SQL procedure from UDF is restricted in DPF env. This is a
serious limitation that DB2 enforced.

What i will do if i am told to do is
1) Schedule TASK for Nightly batch before the data stage job is
executed
2) Detach the partition so the parition become independent partition
3) Add new partition
4) Will maintain window of 7 day to drop the detached partition
5) Data stage job execute
6) Run REORG and RUNSTATS on the table

I believe this is one thing which i am thinking off....


Thanks
Gilroy

Simple scenario: Pass two parameters ID & TYPE based on TYPE select ID from 1 of the 5 Tables Table_A............Table_E -----> Return ID

I have tried it doing both ways using If ....elseif but keep getting th errors.

CREATE FUNCTION TEST.GET_CHILD_ID(
IN_ID BIGINT,
IN_TYPE BIGINT
)
RETURNS BIGINT
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
RETURNS NULL ON NULL INPUT
BEGIN ATOMIC
DECLARE CHILD_ID BIGINT;
CASE
WHEN IN_TYPE=1
THEN SELECT TABLE_A.ID INTO CHILD_ID FROM TEST.TABLE_A AS TABLE_A WHERE TABLE_A.TABLE_A_ID = IN_ID;
WHEN IN_TYPE=4
THEN SELECT TABLE_B.ID INTO CHILD_ID FROM TEST.TABLE_B AS TABLE_B WHERE TABLE_B.TABLE_B_ID = IN_ID;
WHEN IN_TYPE=2
THEN SELECT TABLE_C.ID INTO CHILD_ID FROM TEST.TABLE_C AS TABLE_C WHERE TABLE_C.TABLE_C = IN_ID;
WHEN IN_TYPE=0
THEN SELECT TABLE_D.ID FROM INTO CHILD_ID TEST.TABLE_D AS TABLE_D WHERE TABLE_D.TABLE_D_ID = IN_ID;
WHEN IN_TYPE=9
THEN SELECT TABLE_E.ID INTO CHILD_ID FROM TEST.TABLE_E AS TABLE_E WHERE TABLE_E.TABLE_E_ID = IN_ID;
END CASE;
RETURN CHILD_ID;
END;