Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Posts
    15

    Unanswered: Dynamic SQL in a Cursor's FOR LOOP (DB2)

    Hi Guys,

    I have a quick question! After hours of searching, I still can't find what I am looking for.

    Code:
      CREATE PROCEDURE Tmp()
      LANGUAGE SQL
      BEGIN ATOMIC
        DECLARE fname CHAR(40);
       
        FOR v AS cur1 CURSOR FOR 
                    SELECT firstnme, midinit, lastname FROM employee
        DO 
          SET fname = v.lastname || ',' || v.firstnme;
          ..
          ..
          ..
        END FOR;
      END
    The above code bloc from DB2 uses FOR Loop and a STATIC SQL! Is there a way to use Dynamic SQL within the same FOR Loop? I know we can do a OPEN..FETCH..LOOP block with a Dynamic SQL based cursor, but my requirement is using a FOR loop based Cursor

    Cheers,
    Faisal.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    No, your are not able to use dynamic statement in a FOR loop.
    What's the strange requirement and what do you want to achieve?
    Regards,
    Mark.

  3. #3
    Join Date
    Sep 2004
    Posts
    15
    Thanks a lot Mark for your reply. It was to the point and served my purpose
    Actually I have a code written by someone, it uses a OPEN / Fetch / Close construct, everything looks great but some times the fetch loop gets stuck in the middle of the cursor and infinitely fetches the same record again and again until the process is killed. This is a very strange behavior that I have never seen in Oracle / SQL server! I am new to DB2 and wanted to try a different approach for cursor based processing. That is why I asked if it was possible with a FOR loop

    We have been debugging the code (very simple procedure) and everything is normal except for this occasional stuck at the same row issue!

    Cheers.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    If you seek help for your symptom (instead of rewriting the sproc) then post the DB2-version +DB2-fixpack +operating-system details + DB2-edition, and the cursor details, dbm cfg/db cfg.

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by faisalee View Post
    Actually I have a code written by someone, it uses a OPEN / Fetch / Close construct, everything looks great but some times the fetch loop gets stuck in the middle of the cursor and infinitely fetches the same record again and again until the process is killed.
    It looks like some error in the logic of leaving the fetch loop.
    Can you show this code?
    Regards,
    Mark.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by faisalee View Post

    I have a quick question! After hours of searching
    I don't think this qualifies for a quick question.
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

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