• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • No Truncate Table? No Problem!

    March 23, 2011 Hey, Ted

    I am working on a project using DB2 for i, but my experience is with other database management systems. I can’t find the SQL TRUNCATE TABLE statement. Does DB2 cover this functionality some other way?

    –Brad

    Even in 7.1, the latest release, IBM does not implement the TRUNCATE TABLE statement. However, since this statement is included in other DB2 products, as well as in Informix, I expect we’ll see it eventually as part of our world.

    For the benefit of readers who are not familiar with TRUNCATE TABLE, this statement removes all rows of a table (records of a physical file) without dropping the table (deleting the physical file). It is the SQL equivalent of the Clear Physical File (CLRPFM) CL command.

    In the meantime, you have nothing to worry about, because this functionality is indeed covered by a feature of the DELETE statement.

    When you issue a DELETE without a WHERE clause, you are telling the system to remove all records from the table. If the table is small, the database engine will probably delete the rows individually. However, if the table is large, the system may use either a clear operation (when commitment control is not active) or a change file operation (when commitment control is active.)

    I did a quick experiment with two sequential physical files (not SQL tables) that illustrates this point. Commitment control was not active.

    One file had 12 records. After I ran a DELETE without a WHERE, Display File Description (DSPFD) showed zero active records and 12 deleted ones.

    The second file had about 255,000 records. After a DELETE with no WHERE, DSPFD showed zero active records and zero deleted ones.

    Of course, you can always fall back on CLRPFM. It works on all types of physical files, including SQL tables, even when those tables are journaled.

    –Ted



                         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
    Focal Point Solutions Group

    CLOUD SOLUTIONS

    From Production, Test and Development, Disaster and Backup environments, as well as hosting customer-owned servers, we offer a variety of Cloud Solutions to accommodate all sizes of business and industry. FPSG is in a unique position to provide services for multinational corporations and SMBs with data centers located throughout North America and Europe.

    Does your IBM midrange, AIX, iSeries, Intel, or Linux environment need an improved Cloud strategy?

    EXPLORE OUR CUSTOM SOLUTIONS

    • Production Solutions
    • Disaster Recovery
    • Backup and Recovery Services
    • Data Centers
    • Security & Compliance Services
    • Application Hosting

    MANAGED SERVICES

    Focal Point offers a variety of custom-managed services from daily operations management and monitoring to high availability, backup, and disaster recovery support services. If your enterprise requirements reside in IBM midrange, AIX, iSeries, Intel, or Linux, FPSG can help support your team with our managed services experts. No User downtime for production backups.

    • System Monitoring
    • Server & SAN
    • High Availability/Disaster Recovery Monitoring
    • Managed Backup Services
    • Security Administration & Monitoring
    • Cloud Environment Monitoring

    Our experts combine decades of experience with industry-leading innovation to customize effective solutions for your organization, budget, and infrastructure.

    It’s not uncommon for organizations to miss important requirements when implementing and planning for new technologies. Our Managed Technical Services ensure total compliance, with no compromise or loss of data. Our skilled specialists will design and execute a non-disruptive and comprehensive solution, allowing your IT team to concentrate on the day-to-day activities – resulting in a more agile and cost-effective infrastructure.

    Watch our IntellaFLASH™ Video to learn more

    Let’s Discuss Your Custom Solution Needs

    Follow us on LinkedIn

    focalpointsg.com | 813.513.7402

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    SEQUEL Software:  FREE Webinar: Overcoming query limits with SEQUEL. March 23
    Northeast User Groups Conference:  21th Annual Conference, April 11 - 13, Framingham, MA
    looksoftware:  Integrate IBM i apps with web services. FREE on-demand webinar and white paper!

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    Infor Touts License Fee Growth, Expansion Plans Security of SecurID In Question Following Hack of RSA

    Leave a Reply Cancel reply

Volume 11, Number 11 -- March 23, 2011
THIS ISSUE SPONSORED BY:

Bytware
ProData Computer Services
Twin Data Corporation

Table of Contents

  • Taking RSE to Task
  • Today’s Horoscope
  • Admin Alert: Must Your Rack Be IBM Black?
  • Duplicating an Entire Table or a Subset of a Table Using SQL
  • No Truncate Table? No Problem!
  • Automatically Deleting Spooled Files through Expiration Dates

Content archive

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

Recent Posts

  • DRV Brings More Automation to IBM i Message Monitoring
  • Managed Cloud Saves Money By Cutting System And People Overprovisioning
  • Multiple Security Vulnerabilities Patched on IBM i
  • Four Hundred Monitor, June 22
  • IBM i PTF Guide, Volume 24, Number 25
  • Plotting A Middle Age Career Change To IBM i
  • What Is Code Transformation Even?
  • Guru: The CALL I’ve Been Waiting For
  • A Frank Solstice
  • The Inevitable Wave Of Power9 Withdrawals Begins

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.