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

    June 25, 2014 Ted Holt

    In the April 16, 2014, issue of this august publication, I shared how encoded vector indexes can help with the task of summarizing data from large tables. Tom McKinley of IBM graciously emailed me with more information about that technique, and it is my pleasure to pass Tom’s wisdom along to you.

    Assume two tables–one of items, one of sales.

    create table Items
      ( Item        char (12) not null,
        Description char (25),
        Cost        dec  (7,2),
        Price       dec  (7,2),
      primary key (Item))
    
    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))
    

    Here’s what Tom had to say:

    “If a query summarizes by a column in another table, you can exploit this include technology by creating the EVI over the join column in the table that has the measures you want to summarize. The query below groups by a column in the Items table but sums the sale amount in the Sales table. The implementation ends up joining the Items file to the SalesByItem symbol table, not to the big Sales table.”

    create encoded vector index SalesByItem
     on sales (item)
     include (count(*), sum(quantity * price))
    
    select i.item, sum(s.quantity * s.price)
      from Items as i
      join Sales as s
        on i.item = s.item
     group by i.item
     order by 2 desc
    

    Tom adds, “I think that this capability has huge potential that is not being fully exploited.”

    I have to agree, Tom.

    Certainly I–and probably a lot of other IBM i professionals as well–need to become experts in the use of encoded vector indexes.

    RELATED STORY

    Here’s Help For A Huge Hardship



                         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
    OCEAN User Group

    OCEAN TechCon22 – July 21-23, 2022

    Three Days of Inspiration & Innovation!
    In-Person & Online

    Join your IBM i Community for hands-on learning from technical presentations and workshops, plus a Vendor Solutions Expo. Technically, We’re passionate about continuing education on the IBM Power Systems platform!

    Register Now!

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    CloudFax400:  Moving to an IBM PureSystems™? You need our cloud-based enterprise FAX service.
    System i Developer:  Upgrade your skills at the RPG & DB2 Summit in Minneapolis, Sept 30 - Oct 2.
    COMMON:  Join us at the COMMON 2014 Fall Conference & Expo in Indianapolis, Oct 27-29

    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

    Quadrant and BCD Re-Align Partner Programs DB2 for i 7.2 Features and Fun, Part 1

    Leave a Reply Cancel reply

Volume 14, Number 14 -- June 25, 2014
THIS ISSUE SPONSORED BY:

SEQUEL Software
WorksRight Software
Bug Busters Software Engineering

Table of Contents

  • DB2 for i 7.2 Features and Fun, Part 1
  • Here’s More Help For A Huge Hardship
  • Admin Alert: Reorganizing IBM i Files To Improve Disk Performance, Part 2

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.