• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Ease the Burden of Overloading

    March 29, 2016 Ted Holt

    A while back I said that function overloading is marvelous. I haven’t changed my mind. The same is true of overloaded stored procedures. The only negatives to overloading are a couple of annoyances that the system requires to maintain order. Fortunately, those annoyances are easily circumvented. Here’s what and how.

    Assume a table of items. Each item is identified by a five-digit ID code for internal use only. Customers use an alternate catalog ID to refer to items.

    create table Items
       (    ID                    dec (5,0),
            Catalog_ID            char(6),
            MaterialThisLevel     dec (5,2),
            LaborThisLevel        dec (5,2),
            OverheadThisLevel     dec (5,2),
            MaterialLowerLevels   dec (5,2),
            LaborLowerLevels      dec (5,2),
            OverheadLowerLevels   dec (5,2),
         primary key (ID)
       )
    

    Notice that there are six cost columns (fields): material, labor and overhead (also called burden) costs for the item itself and those same costs for the items that make up this item. The total cost of an item is the sum of the six costs.

    Let’s write a function that retrieves the cost of an item, given an ID. In fact, while we’re at it, let’s write a second function that retrieves the cost of an item, given a catalog number.

    create function RetrieveCost
       ( p_ID dec(5))  returns dec(7,2)
    returns null on null input
    begin
       declare v_Cost   dec(7,2);
       select (MaterialThisLevel + LaborThisLevel +
               OverheadThisLevel + MaterialLowerLevels +
               LaborLowerLevels  + OverheadLowerLevels)
         into v_Cost
         from Items
        where ID = p_ID;
        return v_Cost;
    end
    
    create function RetrieveCost
       ( p_Catalog_ID char(6))  returns dec(7,2)
    returns null on null input
    begin
       declare v_Cost   dec(7,2);
       select (MaterialThisLevel + LaborThisLevel +
               OverheadThisLevel + MaterialLowerLevels +
               LaborLowerLevels  + OverheadLowerLevels)
         into v_Cost
         from Items
        where Catalog_ID = p_Catalog_ID;
        return v_Cost;
    end
    

    Now we have two functions of the same name, and we can call them to retrieve costs by ID or catalog ID from any query.

    select . . . RetrieveCost (x.ItemID) . . . 
      from Sales as x
    
    select . . . RetrieveCost (x.Catalog_ID) . . .
      from Orders as x
    

    So far so good. Now let’s look at the system catalog to see what’s going on behind the scenes.

    select routine_name, specific_name, external_name
      from sysfuncs where routine_name = 'RETRIEVECOST'
    

    ROUTINE_NAME

    SPECIFIC_NAME

    EXTERNAL_NAME

    RETRIEVECOST

    RETRI00001

    MYLIB/RETRI00002(RETRIEVECOST_1)

    RETRIEVECOST

    RETRIEVECOST

    MYLIB/RETRI00001(RETRIEVECOST_1)

    Ignore the specific name for now. I want to point out that the system associates the function name RETRIEVECOST with subprocedures in two service programs. That is, the service programs, both of which are in library MYLIB, are named RETRI00001 and RETRI00002. There’s annoyance number 1. These names are not the most desirable, but at least you don’t have to refer to them in your SQL queries.

    Each service program has a subprocedure named RETRIEVECOST_1. These subprocedures carry out the work.

    Suppose you wish to delete one of the RetrieveCost functions. You could do this:

    drop function retrievecost
    

    If you did, the system would heartlessly respond with error SQL0476 (Routine RETRIEVECOST in *LIBL not unique.)

    Add the parameter type to tell the system which function to drop.

    drop function retrievecost (dec(5))
    

    This is no big deal in this case, but listing the parameter types gets messy when you have a lot of parameters. This is annoyance number 2.

    To avoid these annoyances, give each function a specific name.

    create function RetrieveCost
       ( p_ID dec(5))  returns dec(7,2)
       specific RtvCostID
       returns null on null input
    begin
       declare v_Cost   dec(7,2);
       select (MaterialThisLevel + LaborThisLevel +
               OverheadThisLevel + MaterialLowerLevels +
               LaborLowerLevels  + OverheadLowerLevels)
         into v_Cost
         from Items
        where ID = p_ID;
        return v_Cost;
    end
    
    create function RetrieveCost
       ( p_Catalog_ID char(6))  returns dec(7,2)
       specific RtvCostCat
       returns null on null input
    begin
       declare v_Cost   dec(7,2);
       select (MaterialThisLevel + LaborThisLevel +
               OverheadThisLevel + MaterialLowerLevels +
               LaborLowerLevels  + OverheadLowerLevels)
         into v_Cost
         from Items
        where Catalog_ID = p_Catalog_ID;
        return v_Cost;
    end
    

    Now look at the system catalog.

    ROUTINE_NAME

    SPECIFIC_NAME

    EXTERNAL_NAME

    RETRIEVECOST

    RTVCOSTID

    MYLIB/RTVCOSTID(RETRIEVECOST_1)

    RETRIEVECOST

    RTVCOSTCAT

    MYLIB/RTVCOSTCAT(RETRIEVECOST_1)

    The service programs have decent names, but even better, look at the specific names. These descriptive names provide a much better way to manage the functions.

    To refer to a function, you no longer have to add the parameter list. Instead, you can use the specific name.

    comment on specific function rtvcostid
       is 'Retrieve item cost by ID'
    
    alter specific function rtvcostid
       called on null input          
    
    drop specific function rtvcostid  
    

    It’s not the end of the world if you don’t use specific names when creating overloaded stored procedures and functions, but it’s a nice habit to get into. You already have plenty of annoyances in your life without these two, don’t you?

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    New Generation Software

    On Demand Video

    Data Visualization and Analytics
    with NGS-IQ and Microsoft Power BI

    Power BI is a popular visualization and analytics solution, but it’s only as good as the data your analysts can draw from your IBM i database.

    NGS-IQ simplifies and automates the cleansing, transformation, and delivery of IBM i data to Power BI reports and insights. Watch our new video to learn and see how NGS-IQ supports Power BI users while enabling you to maintain control and security.

    www.ngsi.com – 800-824-1220

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    COMMON:  2016 Annual Meeting & Expo, May 15 - 18, in New Orleans! Great Power Systems event!
    iTech Solutions:  Get your copy of Pete Massiello's The IBM i State of the Union report now!
    NGS:  Webinar: Getting from ? to ! with NGS-IQ - April 5. RSVP Now!

    Healthcare Tech Trends Shape Industry And IBM i X Marks The Spot

    Leave a Reply Cancel reply

Volume 16, Number 07 -- March 29, 2016
THIS ISSUE SPONSORED BY:

WorksRight Software
T.L. Ashford
Bug Busters Software Engineering

Table of Contents

  • Installing PHP on Your Laptop
  • Ease the Burden of Overloading
  • Finders, Keepers: Long Lost RSE Keyboard Shortcuts

Content archive

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

Recent Posts

  • Power10 Boosts NVM-Express Flash Performance
  • Fortra Completes Postmortem Of GoAnywhere Vulnerability
  • Guru: Binding Directory Entries
  • How Does Your Infrastructure Spending Stack Up To The World?
  • IBM i PTF Guide, Volume 25, Number 22
  • Fortra Issues 20th State of IBM i Security Report
  • FNTS Launches Managed Services for Power Servers in IBM Cloud
  • Total LTO Shipped Capacity Up Slightly in 2022
  • Four Hundred Monitor, May 24
  • Update On Critical Security Vulnerability In PowerVM

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