• 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
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    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

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

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