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

    Unanswered: UDF Error: How to resolve

    Hi,

    I am vnew DB2 SQL Programming. I am in the process of writing an UDF. Below is the code snippet. When I try to compile I get the following error. How can I resolve this?

    Error Message: Error:6/10/2010 11:40:41 AM 0:00:00.031: DB2 Database Error: ERROR [42704] [IBM][DB2/LINUXX8664] SQL0204N "GFOR.V_SEQNAME" is an undefined name. LINE NUMBER=79. SQLSTATE=42704


    Code Snippet:

    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(25);
    DECLARE v_servertype VARCHAR(10);
    DECLARE v_seqname VARCHAR(10);
    DECLARE v_seqbase INT;
    DECLARE v_table_nm VARCHAR(64);

    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    As explained in your other thread, you are attempting to access an object that does not exist on your machine. the line
    Code:
    SET v_seqvalue = nextval for v_seqname + v_seqbase;
    is attempting to call the object
    Code:
    GFOR.V_SEQNAME
    which does not exist.
    Dave

  3. #3
    Join Date
    Jun 2010
    Posts
    11
    Dave,

    I do understand that v_seqname is not the actual sequence name. As you can see in the code v_seqname is local variable within this function. This variable will be assigned a value of a sequence name based on input pramater p_table_nm. This section is coded as part of the CASE statement. Are you telling me that v_seqname is not evaluating to a value of sequence name? If so, how what would be the best approach?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may want to use dynamic SQL.

Posting Permissions

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