-----------------------------------------------------------------------------
-- (c) Copyright IBM Corp. 2007 All rights reserved.
--
-- The following sample of source code (<93>Sample<94>) is owned by International
-- Business Machines Corporation or one of its subsidiaries ("IBM") and is
-- copyrighted and licensed, not sold. You may use, copy, modify, and
-- distribute the Sample in any form without payment to IBM, for the purpose
-- of assisting you in the development of your applications.
--
-- The Sample code is provided to you on an "AS IS" basis, without warranty
-- of any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS
-- OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
-- MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions
-- do not allow for the exclusion or limitation of implied warranties, so the
-- above limitations or exclusions may not apply to you. IBM shall not be
-- liable for any damages you suffer as a result of using, copying, modifying
-- or distributing the Sample, even if IBM has been advised of the
-- possibility of such damages.
--
-----------------------------------------------------------------------------
--
-- SOURCE FILE NAME: UDFCreate.db2
--    
-- SAMPLE: Creates the external UDFs implemented in UDFsrv.vb
--
-- To run this script by itself, from the CLP perform the following steps:
-- 1. Connect to the database
-- 2. Issue the command: 
--            
--               db2 -td@ -vf <script-name>
--
--    where <script-name> represents the name of this script, UDFCreate.db2
------------------------------------------------------------------------------

CREATE FUNCTION ScalarUDF(CHAR(5), DOUBLE)
RETURNS DOUBLE
SPECIFIC ScalarUDF
EXTERNAL NAME 'UDFsrv.dll:UDFsrv!ScalarUDF'
LANGUAGE CLR
PARAMETER STYLE SQL
FENCED
CALLED ON NULL INPUT
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
EXECUTION CONTROL SAFE@

CREATE FUNCTION ScratchpadScUDF()
RETURNS INTEGER
SPECIFIC ScratchpadScUDF
EXTERNAL NAME 'UDFsrv.dll:UDFsrv!ScratchpadScUDF'
LANGUAGE CLR
PARAMETER STYLE SQL
FENCED
SCRATCHPAD 10
FINAL CALL
NOT DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
EXECUTION CONTROL SAFE@

CREATE FUNCTION ScUDFReturningErr(DOUBLE, DOUBLE)
RETURNS DOUBLE
SPECIFIC ScUDFReturningErr
EXTERNAL NAME 'UDFsrv.dll:UDFsrv!ScUDFReturningErr'
PARAMETER STYLE SQL
LANGUAGE CLR
FENCED
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
EXECUTION CONTROL SAFE@


CREATE FUNCTION TableUDF(DOUBLE)
RETURNS TABLE(name VARCHAR(20),
              job VARCHAR(20),
              salary DOUBLE)
SPECIFIC TableUDF              
EXTERNAL NAME 'UDFsrv.dll:UDFsrv!TableUDF'
LANGUAGE CLR
PARAMETER STYLE SQL
NOT DETERMINISTIC
FENCED
NO SQL
NO EXTERNAL ACTION
SCRATCHPAD 10
FINAL CALL 
DISALLOW PARALLEL 
NO DBINFO
EXECUTION CONTROL SAFE@