• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Here’s Help For A Huge Hardship

    April 16, 2014 Ted Holt

    Multimillion-row tables are more and more common in IBM i shops these days. Querying those monsters can be a strain on the system. Fortunately, IBM gave us some help in DB2 for i 7.1.

    The help comes in the form of a new wrinkle in indexing. The smart people who produce the world’s greatest relational database management system found a way to store aggregate (summary) information in encoded vector indexes. Here’s an example.

    Assume a table (physical file) of sales history, such as this one:

    create table Sales
      ( ID                integer as identity,
        Invoice           dec  (9,0),
        Line              dec  (3,0),
        InvoiceDate       date,
        Customer          dec  (7,0),
        Item              char (12),
        Quantity          dec  (3,0),
        Price             dec  (5,2),
      primary key (ID));
    

    Suppose that users commonly want to know how many of each item was sold and how much money each item placed into the company bank account. You might use a query like this one:

    select item,
           sum(quantity) as Qty,
           sum(quantity * price) as Extended
      from sales
     group by item
     order by 1
    

    Each time the query runs, the system has to rebuild the summary figures.

    You might improve performance by creating the following encoded vector index:

    create encoded vector index SalesByItem
     on sales (item)
     include (sum(quantity), sum(quantity * price))
    

    This index includes two summary figures for each item: the sum of the quantity and the sum of the quantity multiplied by the price. As rows are added to, deleted from, or changed in the table, the system adjusts the summary figures. When a query needs these figures, it can read them from the index instead of rebuilding them.

    My thanks to Tom McKinley, of IBM, who reminded me of this new feature in his informative presentations at the recent RPG & DB2 Summit in Texas. I had forgotten that this feature existed.

    RELATED STORY

    Enter the Vector, Victor!



                         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
    UCG Technologies

    CYBER-ATTACKS ON THE RISE. PROTECT WITH THE TRIPLE PLAY.

    COVID-19 has not only caused a global pandemic, but has sparked a “cyber pandemic” as well.

    “Cybersecurity experts predict that in 2021, there will be a cyber-attack incident every 11 seconds. This is nearly twice what it was in 2019 (every 19 seconds), and four times the rate five years ago (every 40 seconds in 2016). It is expected that cybercrime will cost the global economy $6.1 trillion annually, making it the third-largest economy in the world, right behind those of the United States and China.”1

    Protecting an organization’s data is not a single-faceted approach, and companies need to do everything they can to both proactively prevent an attempted attack and reactively respond to a successful attack.

    UCG Technologies’ VAULT400 subscription defends IBM i and Intel systems against cyber-attacks through comprehensive protection with the Triple Play Protection – Cloud Backup, DRaaS, & Enterprise Cybersecurity Training.

    Cyber-attacks become more sophisticated every day. The dramatic rise of the remote workforce has accelerated this trend as cyber criminals aggressively target company employees with online social engineering attacks. It is crucial that employees have proper training on what NOT to click on. Cyber threats and social engineering are constantly evolving and UCG’s Enterprise Cybersecurity Training (powered by KnowBe4) is designed to educate employees on the current cutting-edge cyber-attacks and how to reduce and eliminate them.

    A company is only as strong as its weakest link and prevention is just part of the story. Organizations need to have a quick response and actionable plan to implement should their data become compromised. This is the role of cloud backup and disaster-recovery-as-a-service (DRaaS).

    Data is a company’s most valuable asset. UCG’s VAULT400 Cloud Backup provides 256-bit encrypted backups to two (2) remote locations for safe retrieval should a cyber-attack occur. This is a necessary component of any protection strategy. Whether a single click on a malicious link brings down the Windows environment or an infected SQL server feeds the IBM i, once the data is compromised, there is no going back unless you have your data readily available.

    Recovery is not a trivial task, especially when you factor in the time sensitive nature of restoring from an active attack. This leads to the third play of the Triple Play Protection – DRaaS.  Companies have myriad concerns once an attack is realized and a managed service disaster recovery allows employees to keep focus on running the business in a crisis state.

    The combination of training employees with secure backup and disaster recovery offers companies the best chance at avoiding financial disruption in an age of stronger, more frequent cyber-attacks.

    Reach out to UCG Technologies to discuss your company’s security needs and develop a data protection plan that fits you best.

    ucgtechnologies.com/triple-play

     800.211.8798 | info@ucgtechnologies.com

     

    1. https://theconversation.com/cyberattacks-are-on-the-rise-amid-work-from-home-how-to-protect-your-business-151268

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Essextec:  Linux on Power. Lunch on us. A winning combination.
    LANSA:  Webinar: Mobile and the IBM i: Why Should You Care? May 21, 9 am PT/11 am CT/Noon ET
    COMMON:  Join us at the COMMON 2014 Annual Meeting & Exposition, May 4 - 7 in Orlando, Florida

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ EnterpriseTech: High Performance Computing Industry News From ITJ EIC Timothy Prickett Morgan

    Electronic Storage Taps Japanese Reseller to Carry LaserVault UBD IBM i TR8, Database Driven

    Leave a Reply Cancel reply

Volume 14, Number 9 -- April 16, 2014
THIS ISSUE SPONSORED BY:

Help/Systems
WorksRight Software
Bug Busters Software Engineering

Table of Contents

  • The Geezer’s Guide to Free-Form RPG, Part 2: Data Structures and More
  • Here’s Help For A Huge Hardship
  • Admin Alert: Elements Of An IBM i Incident Management Plan, Part 2

Content archive

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

Recent Posts

  • Why Open Source Is Critical for Digital Transformation
  • mrc Refreshes IBM i Low-Code Dev Tool
  • Unit Testing Automation Hits Shift Left Instead of Ctrl-Alt-Delete Cash
  • Four Hundred Monitor, March 3
  • IBM i PTF Guide, Volume 23, Number 9
  • Doing The Texas Two Step From Power9 To Power10
  • PHP’s Legacy Problem
  • Guru: For IBM i Newcomers, An Access Client Solutions Primer
  • IBM i 7.1 Extended Out To 2024 And Up To The IBM Cloud
  • Some Practical Advice On That HMC-Power9 Impedance Mismatch

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 © 2021 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.