Hi,

Lets say I have the following table: (Table Name: SERVERTYPE)

DATABASE_NAME SEQUENCE_BASE

------------------------------------------------

PROD_server 0

DR_server 1000000000


I have the following query:

SELECT database_name, sequence_base
FROM SERVERNAME
WHERE database_name = 'PROD_server'

How can use this query in a DB2 UDF? Will the following syntax work?

DECLARE v_dbname varhcar(10);
DECLARE v_seqbase int;

SET (v_dbname, v_seqbase) = (SELECT database_name, sequence_base
FROM SERVERNAME
WHERE database_name = 'PROD_server');

Please suggest if you have any other approach.

Thanks,
Subra

Which DB2 version and OS are you using? What do you want the UDF to do?

Andy

DB2 Version - 9.1.6

In the UDF, I want to retrieve the values for database_name, sequence_base and assign them in a local variable - v_dbname, v_seqbase respectively. Then based on these values, I will execute a sequence which will return an integer value. Below is the sample code that I am still writing. But no luck so far.

CREATE FUNCTION GFOR.RETRIEVE_SEQUENCE(pi_table_nm VARCHAR(64))
RETURNS INTEGER
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
SPECIFIC GFOR.RETRIEVE_SEQUENCE
NULL CALL
INHERIT SPECIAL REGISTERS


BEGIN ATOMIC

-- Declarations and Initilizations
DECLARE v_seqmask INT default 0; -- Will be updated to 1,000,000,000 in DR data center
DECLARE v_seqvalue INT;
DECLARE v_servername VARCHAR(20);
DECLARE v_servertype VARCHAR(10);
DECLARE v_seqname VARCHAR(10);
DECLARE v_seqbase INT;

SET v_servername = CURRENT SERVER;

/*
************************************************** **************
** Get the appropriate sequence name based on the table name **
** passed to this function **
************************************************** **************
*/

SET v_seqname = (CASE pi_table_nm
WHEN 'CTRL_APPROVAL' THEN 'GFOR.SQ00CTAP'
WHEN 'CTRL_EVENT' THEN 'GFOR.SQ00CTEV'
WHEN 'CTRL_RESULT_RECORD' THEN 'GFOR.SQ00CTRERE'
WHEN 'DATA_MOVEMENT_CONTROL' THEN 'GFOR.SQ00DAMOCO'
WHEN 'DATA_MOVEMENT_ERROR' THEN 'GFOR.SQ00DAMOER'
WHEN 'EXP_CONFIGURATION' THEN 'GFOR.SQ00EXCO'
WHEN 'EXP_EVENT' THEN 'GFOR.SQ00EXEV'
WHEN 'EXP_EVENT_ERROR' THEN 'GFOR.SQ00EXEVER'
WHEN 'FO_EVENT_LOG' THEN 'GFOR.SQ00FOEVLO'
WHEN 'FO_USER_COMMENT' THEN 'GFOR.SQ00FOUSCO'
WHEN 'IMP_CONFIGURATION' THEN 'GFOR.SQ00IMCO'
WHEN 'IMP_EVENT' THEN 'GFOR.SQ00IMEV'
WHEN 'IMP_EVENT_ERROR' THEN 'GFOR.SQ00IMEVER'
WHEN 'PR_CORRECTION_EVENT' THEN 'GFOR.SQ00PRCOEV'
WHEN 'PR_FUND_DIVIDEND_CORRECTION' THEN 'GFOR.SQ00PRFUDICO'
WHEN 'PR_FUND_DIVIDEND_STAGE' THEN 'GFOR.SQ00PRFUDIST'
WHEN 'PR_FUND_VALUATION_CORRECTION' THEN 'GFOR.SQ00PRFUVACO'
WHEN 'PR_FUND_VAL_DIV_STG_APPROVAL' THEN 'GFOR.SQ00PRFUVADISTAP'
WHEN 'PR_FUND_VALUATION_ESTIMATE' THEN 'GFOR.SQ00PRFUVAES'
WHEN 'PR_FUND_VALUATION_EST_DETAIL' THEN 'GFOR.SQ00PRFUVAESDE'
WHEN 'PR_FUND_VALUATION_STAGE' THEN 'GFOR.SQ00PRFUVAST'
END);


/*
************************************************** **********************
** Need code section to retrieve Production or DR Servername, **
** and sequence_base based on value in v_servername **
** SET (v_dbname, v_seqbase) = (SELECT database_name, sequence_base **
** FROM SERVERNAME **
** WHERE database_name = 'PROD_server'); **
************************************************** **********************
*/

SET v_servertype = 'PROD_server';
SET v_seqbase = 0;

/*
************************************************** ************************
** Code section to call appropriate sequence based on table name passed **
** and the server type (PROD_server [OR] DR_server' **
************************************************** ************************
*/
IF v_servertype = 'PROD_server' THEN
SET v_seqvalue = (nextval for v_seqname) + v_seqbase;
END IF;



RETURN v_seqvalue;

END;

So what is your problem? The SET you listed should work.

Andy

You can use dynamic SQL. Construct the SQL statement in a string, then execute it using PREPARE and EXECUTE statements and then process the result set being returned.