Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    7

    Unanswered: need to lock records with cursor with return

    I need a procedure to
    DECLARE c_ReonnRecs CURSOR WITH RETURN FOR
    SELECT ....
    OPEN c_ReonnRecs;
    ... and then update the status of the records

    I've tried:
    FOR UPDATE;
    and
    FOR UPDATE WITH RS USE AND KEEP UPDATE LOCKS;
    when I attempt to update the status of these records and I get an error: "an update or delete is not allowed against the specified cursor"

    I need the locks because we will have multiple servers polling for data and I don't want the records processed more than once.

    Any suggestions would be greatly appreciated.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Maybe if you supply the DB2 version and OS, the complete code you are trying, and the complete error you are getting, you will get a better response.

    Andy

  3. #3
    Join Date
    Feb 2012
    Posts
    7
    thanks Andy...
    ZOS and db2 version 9.1

    CREATE PROCEDURE T14TOPS.GetReconnectJobs( )
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1

    BEGIN

    Re: BEGIN

    DECLARE c_ReonnRecs CURSOR WITH RETURN FOR
    SELECT ... FROM T14TOPS.CI006v a
    left outer join T14TOPS.CI031v d
    on a.MTR_NES_ID = d.MTR_NES_ID,
    T14TOPS.CI001v b,
    T14TOPS.CI002v c
    WHERE FO_TYPE_CDE = 5
    AND a.ORD_STAT_ID in ('ACTV','FELD')
    AND a.RP_FLDTP_TYPE_CDE in ('E', 'Y')
    and d.AMI_RD_FLG = 'Y'
    AND FO_COMPL_DAT = 0
    AND a.CUST_NBR = b.CUST_NBR
    AND a.PREM_NBR = c.PREM_NBR
    and a.ORDPEN_EFF_DAT = DECIMAL(REPLACE(CHAR(CURRENT DATE, ISO),'-',''),8,0)
    FOR UPDATE; -- WITH RS USE AND KEEP UPDATE LOCKS;

    OPEN c_ReonnRecs;

    END Re;

    UPDATE T14TOPS.CI006v a
    SET RP_FLDTP_TYPE_CDE = 'P'
    where current of c_ReonnRecs;

    commit;

    END;

  4. #4
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Cursor is readonly - it selects from more than one table/view. SQL0510N.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Besides, I believe until you FETCH from a cursor there is no "current position".
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Feb 2012
    Posts
    7
    Let me re-phrase the objective. I need select and lock records, update the status of those selected records and return the results back out to the requestor.
    I read that you cannot lock records in a query that has more than one table in the FROM clause.
    Any ideas or suggestions are much appreciated - thanks!

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I would recommend you to have a look at the doc to understand sp and use of cursors
    this code does not seem logic at all.
    if rows need to be locked try using isol rr or select .. for update..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

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
  •