Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    16

    Unanswered: declaring cursor in Function

    Briefly said, I want to know whether "declaring cursor in Function" is supported or not. (There is a thread concluding "declaring cursor in trigger" is not supported. )

    I am working on porting a cache system from Oracle to DB2, in oracle, we have created triggers that:
    1. get a whole list of active nodes from a node management table
    2. for each node,
    2.1 send an message noticing the change of data.
    2.2 wait for acknowledge message returned by the application running on the node.
    2.3 if failed, signal error so that change is prevented.

    For implementation, we
    1. created a Function F1 written in Java for 2.1 and 2.2
    2. created a Stored Procedure SP2 which get the node list via a select cursor, then in a loop, calling F1 for each node.
    3. create trigger that call SP2.

    Since for DB2, trigger can not call store procedure. I will use Function instead. A trigger call Function F2, which get the node list and in turn call the Java Function F1 to send messages.

    My question arise when I code F2, I need to use cursor.

    If using cursor is supported within a Function, that will be fine. Or discussing workaround will also be very helpful.

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: declaring cursor in Function

    I've used FOR as a way of processing a row at a time from a select, below is the template (but also refer to DB2 manuals on-line):

    FOR c1 AS
    SELECT ......
    DO
    call function
    some other processing
    END FOR;


    Originally posted by Xun_Zhang
    Briefly said, I want to know whether "declaring cursor in Function" is supported or not. (There is a thread concluding "declaring cursor in trigger" is not supported. )

    I am working on porting a cache system from Oracle to DB2, in oracle, we have created triggers that:
    1. get a whole list of active nodes from a node management table
    2. for each node,
    2.1 send an message noticing the change of data.
    2.2 wait for acknowledge message returned by the application running on the node.
    2.3 if failed, signal error so that change is prevented.

    For implementation, we
    1. created a Function F1 written in Java for 2.1 and 2.2
    2. created a Stored Procedure SP2 which get the node list via a select cursor, then in a loop, calling F1 for each node.
    3. create trigger that call SP2.

    Since for DB2, trigger can not call store procedure. I will use Function instead. A trigger call Function F2, which get the node list and in turn call the Java Function F1 to send messages.

    My question arise when I code F2, I need to use cursor.

    If using cursor is supported within a Function, that will be fine. Or discussing workaround will also be very helpful.

Posting Permissions

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