• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Bypass Locked Records in SQL Queries

    January 14, 2009 Dear Intelligent Colleague

    This tip is a follow-up to both of the tips I published last week. Like Bypassing a Locked Record, Take Two, it has to do with locked records. Like Be Specific When Updating With SQL Cursors, it deals with SQL updates.

    You may remember that there are two ways to update in SQL. The positioned update uses a cursor to update one row at a time, as we talked about last week. Updating through a cursor is similar to updating with native I/O.

    The searched update, the easier and more powerful of the two, does not work through a declared cursor, but updates a set of records at once. I have written about these types of updates before.

    If you attempt a searched update, and one of the selected records is locked, the system sets the SQL state variable to a non-zero value, and one or more of the updates will not have taken place. If you need an all-or-nothing update, you can roll back the update when it fails. But if you want to skip locked records, V6R1 has a new option you need to know about. Take a look at following SQL update, paying special attention to the last line.

    UPDATE CustMaster SET chgcod = 7
       WHERE state = 'TX'
          WITH CS
             SKIP LOCKED DATA
    

    SKIP LOCKED DATA tells the system to ignore rows (records) that are locked by other transactions.

    The SKIP LOCKED DATA clause is available only in the cursor stability and read stability isolation levels, hence WITH CS in this example. You can use SKIP LOCKED DATA with SELECT, UPDATE, and DELETE commands.

    –Ted

    RELATED STORIES

    Bypassing a Locked Record, Take Two

    Be Specific When Updating With SQL Cursors

    Updating through an SQL Cursor



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Eradani

    API Enable Your IBM i Today

    Create & Manage Secure, High Speed, APIs for IBM i with Eradani Connect!

    o Automatically schedule and track shipments

    o Communicate with IoT devices

    o Connect to Amazon, Shopify and others

    o Automate order processing

    o Create mobile and web user interfaces

    o Generate Open API Swagger Documentation

    Eradani Mentors will ensure your success!

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    SkyView Partners:  Security software with a measurable return on investment
    ARCAD Software:  FREE Webinar, ITIL Best Practices with Philippe Magne, January 28
    COMMON:  Join us at the 2009 annual meeting and expo, April 26-30, Reno, Nevada

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    Getting Started with PHP for i5/OS: List Price, $59.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    IBM Increases Price on Tape Cartridges, Printers AS/400 LUG: Friends in High Places

    Leave a Reply Cancel reply

Volume 9, Number 2 -- January 14, 2009
THIS ISSUE SPONSORED BY:

ProData Computer Services
Vision Solutions
Guild Companies

Table of Contents

  • Bypass Locked Records in SQL Queries
  • Data Queues vs. MQSeries
  • Admin Alert: Looking for i5/OS Trouble, Part II

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • IBM i 7.3 TR12: The Non-TR Tech Refresh
  • IBM i Integration Elevates Operational Query and Analytics
  • Simplified IBM i Stack Bundling Ahead Of Subscription Pricing
  • More Price Hikes From IBM, Now For High End Storage
  • Big Blue Readies Power10 And IBM i 7.5 Training for Partners
  • IBM Delivers More Out-of-the-Box Security with IBM i 7.5
  • Groundhog Day For Malware
  • IBM i Community Reacts to IBM i 7.5
  • Four Hundred Monitor, May 11
  • IBM i PTF Guide, Volume 24, Number 19

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.