• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Where’s the Other MBROPT Option?

    April 19, 2006 Hey, Ted

    I suppose everybody knows that the Copy File (CPYF) command can copy data from one database file to another. And I suppose everybody knows that, depending on the MBROPT parameter, the data in the source file (the “from” file) can either replace or be added to the data in the target file (the “to” file). Furthermore, I suppose that some people know that the MBROPT(*UPDADD) option causes the system to replace the records that have matching key values and add records with no matching key values. But does anybody know how to add the records that don’t have matching key values without updating the records that do?

    –Bewildered

    You have two methods–one that uses CPYF and one that uses SQL. But first, let’s put the problem into context for the readers. Let’s start with a file ORDDTL of sales order details. Each line represents an item on the order and is uniquely keyed on order number and line number.

    Order     Line  Item  
    Number  Number  Number  Quantity
    ======  ======  ======  ========
        1        1    I01          5  
        1        2    I08          3  
        2        1    I01          6  
        2        2    I09          6  
        3        1    I01          3  
        3        2    I07          3  
        3        3    I09          6  
        3        4    I02          6  
        4        1    I02          5  
        4        2    I08          5  
        4        3    I22          6  
        6        1    I01          8  
    

    Assume a second file ORDDTLCHGS of the same format with additional order lines and/or changes to existing order lines.

    Order     Line  Item  
    Number  Number  Number  Quantity
    ======  ======  ======  ========
        5        1    I18          4
        6        1    I01         12
        7        1    I05          6
    

    Notice that two lines, for orders 5 and 7, do not exist in the ORDDTL file. The record for order 6, line 1, contains a new quantity for that order line.

    To apply the updates to ORDDTL, use the *UPDADD option, like this:

    CPYF FROMFILE(ORDDTLCHGS) TOFILE(ORDDTL) MBROPT(*UPDADD)
    

    Here’s the resulting dataset.

    Order     Line  Item  
    Number  Number  Number  Quantity
    ======  ======  ======  ========
        1        1    I01          5 
        1        2    I08          3 
        2        1    I01          6 
        2        2    I09          6 
        3        1    I01          3 
        3        2    I07          3 
        3        3    I09          6 
        3        4    I02          6 
        4        1    I02          5 
        4        2    I08          5 
        4        3    I22          6 
        6        1    I01         12 
        5        1    I18          4 
        7        1    I05          6 
    

    Notice that the quantity has changed for order 6, line 1, and the lines for orders 5 and 1 have been added.

    Bewildered wants to add 5 and 7 without updating 6. Obviously he needs to add records to the file.

    CPYF FROMFILE(ORDDTLCHGS) TOFILE(ORDDTL) MBROPT(*ADD)
    

    However, this CPYF command chokes when it tries to add the 6-1 record to the file. When I tested this example, the system copied the 5-1 record, then canceled, leaving the ORDDTL file only partially updated.

    The solution is not in the MBROPT parameter, but in the ERRLVL parameter, which tells CPYF to ignore errors.

    CPYF FROMFILE(ORDDTLCHGS) TOFILE(ORDDTL) MBROPT(*ADD) ERRLVL(*NOMAX)
    

    Now the database looks like this:

    Order     Line  Item  
    Number  Number  Number  Quantity
    ======  ======  ======  ========
        1        1    I01          5 
        1        2    I08          3 
        2        1    I01          6 
        2        2    I09          6 
        3        1    I01          3 
        3        2    I07          3 
        3        3    I09          6 
        3        4    I02          6 
        4        1    I02          5 
        4        2    I08          5 
        4        3    I22          6 
        6        1    I01          8 
        5        1    I18          4 
        7        1    I05          6 
    

    If you have access to any version of SQL, you may find it to be a better solution.

    insert into orddtl                
    select *                          
      from orddtlchgs as C            
     where not exists                 
        (select *                     
           from orddtl as OD          
          where C.orderno = OD.orderno
            and C.lineno = OD.lineno)
    

    The parenthesized select looks for at least one record that has the same order number and line number as a record in the ORDDTLCHGS file. If no matching record is found, the command inserts the ORDDTLCHGS record into ORDDTL.

    –Ted

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Rocket Software

    Introducing Rocket Software’s Solutions for IBM®i: Ignite Your Business Potential!

    Unlock the true power of your IBM® i platform with Rocket Software’s cutting-edge solutions. Embrace innovation and streamline operations with our comprehensive tools to propel your business forward.

    Explore Rocket Software Application Modernization solutions to learn how you can optimize your IBM i infrastructure to deliver enhanced user and developer experiences from a modern, agile environment:

    • Rocket DevOps: Simplify compliance reporting and user access to critical systems.
    • Rocket Process Insights: Build a data-informed, smart modernization plan with bottom-line results.
    • Rocket API: Unlock critical data and deliver fast ROI with robotic process Automation (RPA) for IBM i.
    • Rocket Modern Experience: Modernize UI/UX application for productive outcomes.
    • Rocket iCluster: Ensure uninterrupted operation for your IBM i applications.
    • Rocket Terminal Emulator: Access host-based systems from browsers or mobile devices.
    • Rocket MFA: Build a layered defense against password vulnerabilities.

    Modernization. Without Disruption.

    Rocket Software

    Learn more

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Maximum Availability:  Secure, cost-effective, real-time iSeries replication software solutions
    COMMON:  Join us at the Fall 2006 conference, September 17-21, in Miami Beach, Florida
    nuBridges:  Leading provider of secure FTP on the iSeries

    Job Posting Areas Created on the IT Jungle Forums ISVs React (Or Not) to PHP on the System i

    Leave a Reply Cancel reply

Volume 6, Number 16 -- April 19, 2006
THIS ISSUE SPONSORED BY:

T.L. Ashford
Advanced Systems Concepts
Bug Busters Software Engineering

Table of Contents

  • Where’s the Other MBROPT Option?
  • CL’s Null Value
  • Admin Alert: An Experimental Technique for Automatically Deleting User Profiles
  • Job Posting Areas Created on the IT Jungle Forums

Content archive

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

Recent Posts

  • 40 Years Of DB2, But Even More For That No-Name Database Embedded In The System/38
  • API Dev Tool Delivers For Trucking Outfit
  • Guru: TryIT – You’ll Like It
  • It’s Time To Tell Us How It Is And What You’re Doing
  • IBM i PTF Guide, Volume 25, Number 40
  • Shield Builds on Success with Nagios for IBM i
  • Why You Should Be Concerned About the MGM ‘Vishing’ Attack
  • IBM Bolsters Database Security with Guardium 12.0
  • Four Hundred Monitor, September 27
  • The IBM i Marketplace Survey Needs Your Input

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