• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: How To Cancel A Bad SQL Update

    May 15, 2017 Ted Holt

    In Three Ways To Manage Unmatched Data I wrote about the use of the RAISE_ERROR function to force a SELECT statement to cancel when unmatched data is considered a fatal error. Another good use of RAISE_ERROR is to force an UPDATE statement to cancel when an invalid condition occurs.

    To illustrate, imagine that you and I work in a factory. All factories have inventory. The people we serve purchase some inventory items and manufacture others. Our job is to write a program that will allow certain people to zero out the inventory balance for certain types of purchased items.

    The users will enter a series of item numbers into a database table (physical file) named ItemBal3. Our program is to set the quantity on hand to zero, but only for type 3 and type 4 items. Our program contains this UPDATE statement:

    update items as i
       set i.QtyOnHand = 0
     where i.type in ('3','4')
       and i.itemnumber in (select item from ItemBal3);
    
    
    If the users enter the ID numbers of items that are of other types, the program ignores those items.

    Let’s take it a step further. Suppose that the presence of some other type of item in ItemBal3 is an error that cannot be overlooked. In such a case, we can make the UPDATE cancel itself, like this:

    update items as i                                            
       set i.QtyOnHand =                                                  
              case when i.type in ('3','4') then 0                      
                   else raise_error ('97905',                           
                             'Non-purchased items cannot be zeroed') end
     where i.itemnumber in (select item from ItemBal3)

    Notice that the item type is no longer tested in the WHERE clause, but in the SET. If the database manager attempts to modify a type-3 or type-4 item, the case expression returns zero, which is assigned to the QtyOnHand column.

    But if the database manager attempts to modify an item of another type, the system calls the RAISE_ERROR function, which cancels the UPDATE and returns SQL state 97905 to the caller. If the program is running under commitment control, the database manager rolls back any items that were changed before the invalid item was encountered. I can illustrate with an example.

    Here is the item master table:

    Item number Description Type Quantity on hand
    A-1 3-inch Doodle 1 20
    A-3 5cm Spinkler 2 20
    A-7 #7 Hoozit 3 20
    B-1 Widget, size 8 4 20
    B-2 #12 Skyhook 5 20
    F-3 4-inch Floozle 6 20

    Here is ITEMBAL3.

    Item
    A-7
    B-1
    F-3

    If the system updates the items in the order in which they are listed, the quantity on hand for A-7 and B-1 changes from 20 to zero. But when it tries to update F-3, RAISE_ERROR cancels the UPDATE statement. Under commitment control, the quantity on hand reverts to 20. Without commitment control, A-7 and B-1 have a zero balance after the canceled UPDATE.

    Under commitment control, database integrity is preserved. You can find the problem, fix it, and restart the program.

    Without commitment control, you don’t know what you have. Good luck.

    RELATED STORY

    Three Ways To Manage Unmatched Data

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: FHG, Four Hundred Guru, SQL

    Sponsored by
    PERFSCAN

    Revolutionary Performance Management Software

    At Greymine, we recognize there is a void in the IT world for a dedicated performance management company and also for a performance management tool that’s modern, easy to use, and doesn’t cost an arm and a leg. That’s why we created PERFSCAN.

    PERFSCAN is designed to make your job easier. With revolutionary technology, an easy-to-read report and graphics engine, and real time monitoring, tasks that used to take days can now take minutes. This means you will know your system better and will be able to provide better service to your customers.

    OUR FEATURES

    PERFSCAN is full of robust features that don’t require you to take a three-day class in order to use the product effectively.

    Customizable Performance Reporting

    Whether you are troubleshooting a major system problem or simply creating a monthly report, PERFSCAN lets you select any combination of desired performance metrics (CPU, Disk, and Memory).

    User Defined Performance Guidelines

    No matter if you are a managed service provider managing complex systems in the cloud or a customer analyzing your on-premises solution, PERFSCAN gives you the flexibility to define all mission critical guidelines how they need to be.

    Understanding The Impact Of Change

    Tired of all the finger pointing when performance is suffering? PERFSCAN’s innovative What’s Changed and Period vs. Period analysis creates a culture of proof by correlating known environmental changes with system performance metrics.

    Comprehensive Executive Summary

    Creating performance graphs is easy. Understanding what they mean is another thing. With one mouse click, PERFSCAN includes an easy-to-understand executive summary for each core metric analyzed.

    Combined Real-Time Monitor And Performance Analysis Tool

    With PERFSCAN’s combined built in enterprise real-time monitor and historical performance analysis capability, you will always know how your mission-critical systems are performing.

    Cloud Performance Reporting Is Easy

    Managing performance for production systems in the cloud can be a black hole to many system administrators. The good news is PERFSCAN analyzes all core metrics regardless of the location. That’s why MSPs and customers love PERFSCAN.

    Detailed Job Analysis

    PERFSCAN shows detailed top job analysis for any desired period. All metrics are displayed in two ways: Traditional Report and Percentage Breakdown Pie Chart. This toggle capability instantly shows the jobs using the most system resources.

    Save Report Capability

    Your boss lost the report you gave to him on Friday. Now what do you do? With PERFSCAN’s save report capability, any report can be retrieved in a matter of seconds.

    Professional PDF Reporting With Branding

    Creating professional looking reports for your customers has never been easier with PERFSCAN. Branding for our partners and service provider customers is easy with PERFSCAN.

    Check it out at perfscan.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    COMMON Looking Youthful In 2017 HelpSystems Tackles IBM i Password Woes

    Leave a Reply Cancel reply

TFH Volume: 27 Issue: 33

This Issue Sponsored By

  • Profound Logic Software
  • Remain Software
  • ASNA
  • Linoma Software
  • Manta Technologies

Table of Contents

  • Open Source On IBM i: Let It Grow
  • HelpSystems Tackles IBM i Password Woes
  • Guru: How To Cancel A Bad SQL Update
  • COMMON Looking Youthful In 2017
  • The Five Things Clouds Need To Deliver For IBM i

Content archive

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

Recent Posts

  • IBM i Delivers Sizable Benefits, Forrester Consulting Reports
  • SBOMs Will Come to IBM i, Eventually
  • IBM i Backup Provider Storagepipe Snapped Up By Thrive
  • Four Hundred Monitor, June 7
  • IBM i PTF Guide, Volume 25, Number 23
  • Power10 Boosts NVM-Express Flash Performance
  • Fortra Completes Postmortem Of GoAnywhere Vulnerability
  • Guru: Binding Directory Entries
  • How Does Your Infrastructure Spending Stack Up To The World?
  • IBM i PTF Guide, Volume 25, Number 22

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