DB2 UDF Problem
in [DB2]
|
Prev: Backup DB
Next: UDF returning three scalars
From: Gilroy Gonsalves on 15 Jun 2010 11:59 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
From: Lennart Jonsson on 16 Jun 2010 00:27 On 2010-06-15 17:59, Gilroy Gonsalves wrote: > 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 The main purpose of a function is to return something, but it may under restricted circumstances have side effects. Not the other way around. > 2) Do i need to modify any thing in the above mentioned UDF. > 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
From: Gilroy Gonsalves on 16 Jun 2010 06:27 On Jun 16, 5:27 am, Lennart Jonsson <erik.lennart.jons...(a)gmail.com> wrote: > On 2010-06-15 17:59, Gilroy Gonsalves wrote: > > > > > 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 > > The main purpose of a function is to return something, but it may under > restricted circumstances have side effects. Not the other way around. > > > 2) Do i need to modify any thing in the above mentioned UDF. > > 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 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
From: Lennart Jonsson on 16 Jun 2010 14:34 On 2010-06-16 12:27, Gilroy Gonsalves wrote: > On Jun 16, 5:27 am, Lennart Jonsson <erik.lennart.jons...(a)gmail.com> > wrote: >> On 2010-06-15 17:59, Gilroy Gonsalves wrote: >> >> >> >> > 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 >> >> The main purpose of a function is to return something, but it may under >> restricted circumstances have side effects. Not the other way around. >> >> > 2) Do i need to modify any thing in the above mentioned UDF. >> >> 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 > > > > 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. > > 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
From: Gilroy Gonsalves on 17 Jun 2010 04:14 On Jun 16, 7:34 pm, Lennart Jonsson <erik.lennart.jons...(a)gmail.com> wrote: > On 2010-06-16 12:27, Gilroy Gonsalves wrote: > > > > > On Jun 16, 5:27 am, Lennart Jonsson <erik.lennart.jons...(a)gmail.com> > > wrote: > >> On 2010-06-15 17:59, Gilroy Gonsalves wrote: > > >> > 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 > > >> The main purpose of a function is to return something, but it may under > >> restricted circumstances have side effects. Not the other way around. > > >> > 2) Do i need to modify any thing in the above mentioned UDF. > > >> 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 > > > 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. > > 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 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
|
Pages: 1 Prev: Backup DB Next: UDF returning three scalars |