• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Be Specific When Updating With SQL Cursors

    January 7, 2009 Ted Holt

    To update a table (physical file) through an SQL cursor, use the FOR UPDATE OF clause in your UPDATE command. (I have written about FOR UPDATE OF before.) When you update through an SQL cursor, avoid two mistakes that can cause a performance problem.

    First, don’t forget to include FOR UPDATE OF. The SQL preprocessor does not require you to include this clause, and will not generate a message to warn you of its omission. If you do not specify FOR UPDATE OF, SQL assumes that you want to update all columns (fields).

    Second, list only the columns that you will update. If you list columns that you don’t update, SQL will not notice the inconsistency.

    The possible performance problem occurs when SQL thinks you might update a column that is used in the WHERE clause. SQL will consider using access paths to speed up the update, but it will not use an access path over an updated column. When you tell SQL which columns will be updated, SQL knows that it can use access paths over other columns.

    Here’s an example of a good cursor declaration. Notice the FOR UPDATE OF clause in the cursor declaration.

    D CustData      e ds                  extname(CustMaster)
    D ChangeCode      s              1p 0
    
    /free                              
         exec sql                       
            declare Customer cursor for 
               select * from CustMaster
                where state = 'TX'      
                  for update of chgcod;
    
         exec sql                       
            open Customer;              
            // insert code to check for open error
                                        
         dow '1';                       
            exec sql                    
               fetch Customer into :CustData;
            if sqlstt = '02000';        
               leave;                   
            endif;                      
            // insert code to check for fetch error
    
            // insert calcs to calculate new Change Code here
            // eval whatever ...
            // call whatever ...
            // etc.
    
            // ChangeCode now has a new value for the customer.
            
         exec sql                       
               update CustMaster set chgcod = :ChangeCode
                  where current of Customer;
         enddo;                         
         exec sql                       
            close Customer;             
            // insert code to check for close error
         return;                        
    

    SQL knows that the program will not update STATE, so it can use an index built over STATE in order to select the rows (records) for Texas customers. Without FOR UPDATE OF, it will ignore indexes and read the entire table.

    RELATED STORY

    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
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    New Generation Software:  Run IBM Query/400 from your Windows desktop!
    Bug Busters Software Engineering:  High availability software that won't break the bank
    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

    Infor Begins North American VAR Recruitment Drive There’s No i in Barack Obama, But There Is One in Bailout

    Leave a Reply Cancel reply

Volume 9, Number 1 -- January 7, 2009
THIS ISSUE SPONSORED BY:

Help/Systems
WorksRight Software
MKS

Table of Contents

  • Bypassing a Locked Record, Take Two
  • Be Specific When Updating With SQL Cursors
  • Admin Alert: Looking for i5/OS Trouble, Part I

Content archive

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

Recent Posts

  • IBM Tweaks Some Power Systems Prices Down, Others Up
  • Disaster Recovery: From OS/400 V5R3 To IBM i 7.4 In 36 Hours
  • The Disconnect In Modernization Planning And Execution
  • Superior Support: One Of The Reasons You Pay The Power Systems Premium
  • IBM i PTF Guide, Volume 25, Number 13
  • IBM i Has a Future ‘If Kept Up To Date,’ IDC Says
  • When You Need Us, We Are Ready To Do Grunt Work
  • Generative AI: Coming to an ERP Near You
  • Four Hundred Monitor, March 22
  • IBM i PTF Guide, Volume 25, Number 12

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 © 2023 IT Jungle