Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    11

    Unanswered: SQL Queries in DB2 UDF

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Which DB2 version and OS are you using? What do you want the UDF to do?

    Andy

  3. #3
    Join Date
    Jun 2010
    Posts
    11
    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;

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    So what is your problem? The SET you listed should work.

    Andy

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •