• 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 – Vault400

    Do the Math When Looking at IBM i Hosting for Cost Savings

    COVID-19 has accelerated certain business trends that were already gaining strength prior to the start of the pandemic. E-commerce, telehealth, and video conferencing are some of the most obvious examples. One example that may not be as obvious to the general public but has a profound impact on business is the shift in strategy of IBM i infrastructure from traditional, on-premises environments to some form of remote configuration. These remote configurations and all of their variations are broadly referred to in the community as IBM i hosting.

    “Hosting” in this context can mean different things to different people, and in general, hosting refers to one of two scenarios. In the first scenario, hosting can refer to a client owned machine that is housed in a co-location facility (commonly called a co-lo for short) where the data center provides traditional system administrator services, relieving the client of administrative and operational responsibilities. In the second scenario, hosting can refer to an MSP owned machine in which partition resources are provided to the client in an on-demand capacity. This scenario allows the client to completely outsource all aspects of Power Systems hardware and the IBM i operating system and database.

    The scenario that is best for each business depends on a number of factors and is largely up for debate. In most cases, pursuing hosting purely as a cost saving strategy is a dead end. Furthermore, when you consider all of the costs associated with maintaining and IBM i environment, it is typically not a cost-effective option for the small to midsize market. The most cost-effective approach for these organizations is often a combination of a client owned and maintained system (either on-prem or in a co-lo) with cloud backup and disaster-recovery-as-a-service. Only in some cases of larger enterprise companies can a hosting strategy start to become a potentially cost-effective option.

    However, cost savings is just one part of the story. As IBM i expertise becomes scarce and IT resources run tight, the only option for some firms may be to pursue hosting in some capacity. Whatever the driving force for pursing hosting may be, the key point is that it is not just simply an option for running your workload in a different location. There are many details to consider and it is to the best interest of the client to work with an experienced MSP in weighing the benefits and drawbacks of each option. As COVID-19 rolls on, time will tell if IBM i hosting strategies will follow the other strong business trends of the pandemic.

    When we say do the math in the title above, it literally means that you need to do the math for your particular scenario. It is not about us doing the math for you, making a case for either staying on premises or for moving to the cloud. There is not one answer, but just different levels of cost to be reckoned which yield different answers. Most IBM i shops have fairly static workloads, at least measured against the larger mix of stuff on the public clouds of the world. How do you measure the value of controlling your own IT fate? That will only be fully recognized at the moment when it is sorely missed the most.

    CONTINUE READING ARTICLE

    Please visit ucgtechnologies.com/IBM-POWER9-systems for more information.

    800.211.8798 | info@ucgtechnologies.com

    Article featured in IT Jungle on April 5, 2021

    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

  • Query Supervisor Gives Database Engineers New Power
  • IBM Unveils New and Improved IBM i Services
  • 3 Takeaways from the 2021 PowerTech Security Report
  • Four Hundred Monitor, April 14
  • IBM i PTF Guide, Volume 23, Number 15
  • Big Blue Unveils Spring 2021 IBM i Technology Refreshes
  • Thoroughly Modern: Innovative And Realistic Approaches To IBM i Modernization
  • Guru: Web Services, DATA-INTO and DATA-GEN, Part 2
  • Back To The Future With A New IBM i Logo
  • IBM i PTF Guide, Volume 23, Number 14

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.