• 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
    ARCAD Software

    Embrace VS Code for IBM i Development

    The IBM i development landscape is evolving with modern tools that enhance efficiency and collaboration. Ready to make the move to VS Code for IBM i?

    Join us for this webinar where we’ll showcase how VS Code can serve as a powerful editor for native IBM i code and explore the essential extensions that make it possible.

    In this session, you’ll discover:

    • How ARCAD’s integration with VS Code provides deep metadata insights, allowing developers to assess the impact of their changes upfront.
    • The role of Git in enabling seamless collaboration between developers using tools like SEU, RDi, and VS Code.
    • Powerful extensions for code quality, security, impact analysis, smart build, and automated RPG conversion to Free Form.
    • How non-IBM i developers can now contribute to IBM i projects without prior knowledge of its specifics, while ensuring full control over their changes.

    The future of IBM i development is here. Let ARCAD be your guide!

    Watch Now

    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

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

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