• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Faking Create Or Replace Table

    January 20, 2015 Ted Holt

    The new CREATE OR REPLACE feature of SQL has been most helpful to me. It works for aliases, functions, masks, permissions, procedures, sequences, triggers, variables and views. It would be nice if it worked for tables, especially when I’m developing a new application. Here’s a workaround.

    The trick is to use a dynamic compound statement. If you’re not familiar with these, I recommend you read Michael Sansoterra’s excellent article on that subject.

    One of the things that dynamic compound statements let you do is include conditional logic, which is what we need. We need a way to drop a table if it exists, and not error out if the table does not exist.

    Here’s a typical script of the sort that I run under RUNSQLSTM:

    /* Delete the tables if they exist */
    begin
       declare continue handler for sqlexception
          begin end;
       drop table customers;
       drop table shipments;
       drop table invoices;
    end;
    
    create table customers 
    . . . code omitted . . .
    
    create table shipments
    . . . code omitted . . .
                          
    create table invoices 
    . . . code omitted . . .
    

    The dynamic compound statement is the begin-end block that spans the second through the eighth lines. I’ve defined a continue handler to run an empty begin-end block when an exception occurs. The continue handler takes control when a drop table ends abnormally.

    This technique is not exactly like Create or Replace. There is one big difference. When you delete an object, the system discards all object authority for that object. Create or Replace retains object authority.

    RELATED STORY

    Dynamic Compound Statements In DB2 For i

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Maxava

    Migrating to a new IBM Power System?

    Whether it be Power8, Power9 or Power10 – Maxava has you covered

    Our migration service moves data from the old to the new server without disruption while the business continues to operate without impacting performance. Our service avoids long periods of downtime and means businesses can reduce the risk of moving to new hardware.

    To learn more about Maxava’s migration service, call us on 888 400 1541 or VISIT maxava.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Profound Logic Software:  Reach Your Modernization Goals. Register for the February 25 Webinar now!
    New Generation Software:  Ask us about Query, Reporting, and Analytics. Order a FREE Trial of NGS-IQ.
    System i Developer:  Upgrade your skills at the RPG & DB2 Summit in Dallas, March 17-19

    Strengthening Dollar Curtails Global IT Spending Growth IBM i Predictions For 2015 From Around The Community

    Leave a Reply Cancel reply

Volume 15, Number 01 -- January 20, 2015
THIS ISSUE SPONSORED BY:

ProData Computer Services
SEQUEL Software
WorksRight Software

Table of Contents

  • Faking Create Or Replace Table
  • Everybody Likes Shortcuts! Part 1, Navigation
  • Regurgitating Data With DB2 For i Data Change Table References

Content archive

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

Recent Posts

  • IBM Mulls Using DataMigrator as Cloud Warehouse Pipeline
  • PowerTech AV Automatically Detects Ransomware Activity
  • Infor Puts CM3 Project On Hold
  • Four Hundred Monitor, June 29
  • IBM i PTF Guide, Volume 24, Number 26
  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050

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.