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

    Unanswered: Sequence cannot be invoked through a variable

    Hi,

    Please refer to the following code. An external application will call this UDF - RETRIEVE_SEQUENCE where it will pass sequence name as an input parameter to this UDF. The UDF will INVOKE the appropriate sequence based on some logic. When I try INVOKE this sequence within the UDF, I am getting the following error. Please note that the actual sequence that is being passed exists in the database. When the sequence name are passed as parameters (p_sequence_nm), the UDF isn't working. But if I INVOKE the sequence with the actual SEQUENCE NAME, instead of a variable, then the UDF is working fine. How can we resolved this problem?

    /* DO GET ERROR */
    IF v_hostname1 = v_hostname2 THEN
    SET v_seqvalue = NEXTVAL FOR GFOR.pi_sequence_nm + v_seqbase;
    END IF;

    /* WORKS FINE */
    IF v_hostname1 = v_hostname2 THEN
    SET v_seqvalue = NEXTVAL FOR GFOR.SQ00CTAP + v_seqbase;
    END IF;

    Error Message:
    Error:6/14/2010 10:47:23 AM 0:00:00.031: DB2 Database Error: ERROR [42704] [IBM][DB2/LINUXX8664] SQL0204N "GFOR.PI_SEQUENCE_NM" is an undefined name. LINE NUMBER=31. SQLSTATE=42704

    --CODE SNIPPET OF THE UDF

    CREATE FUNCTION GFOR.RETRIEVE_SEQUENCE(pi_sequence_nm VARCHAR(64))
    RETURNS INTEGER
    LANGUAGE SQL
    SPECIFIC GFOR.RETRIEVE_SEQUENCE
    NOT DETERMINISTIC
    READS SQL DATA
    INHERIT SPECIAL REGISTERS


    BEGIN ATOMIC

    -- Local Variable Declaration
    DECLARE v_seqvalue INT;
    DECLARE v_hostname1 VARCHAR(20);
    DECLARE v_hostname2 VARCHAR(20);
    DECLARE v_seqbase INT;
    DECLARE v_sequence VARCHAR(15);


    SET v_hostname1 = (SELECT HOST_NAME FROM TABLE(SYSPROC.ENV_GET_SYS_INFO()) AS SYSTEMINFO);

    --Get the HOSTNAME and sequence_base from the lookup table based on the given host_name
    SET (v_hostname2,v_seqbase) = (SELECT server_cd, sequence_base_nbr
    FROM GFOR.XREF_SERVER_SEQUENCE_BASE
    WHERE server_cd = v_hostname1);



    -- Invoke the SEQUENCE passed and Generate the Keys
    IF v_hostname1 = v_hostname2 THEN
    SET v_seqvalue = NEXTVAL FOR GFOR.pi_sequence_nm + v_seqbase;
    END IF;


    RETURN v_seqvalue;

    END;

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    This is like a broken record. This question has been answered at least three times. Please look back at your prior posts for the answers on how to resolve running dynamic SQL.
    Dave

  3. #3
    Join Date
    Jun 2010
    Posts
    11
    I am not sure what you mean by broken record. Just give an example of how to resolve this.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Use dynamic SQL.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jun 2010
    Posts
    11
    Can someone show me how to use dynamic sql on this example as I am very new to Db2. I spent enough time and still unable to find a solution.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by subra729 View Post
    I spent enough time
    May be not...

    Let me google that for you

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Please take a look at one of your first threads on this subject and Stolze told you how to do it at the end of thread.
    http://www.dbforums.com/db2/1657347-...s-db2-udf.html
    Dave

Posting Permissions

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