• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: I’m A Number, You’re A Number, Everybody’s A Number

    September 9, 2019 Ted Holt

    Do you, like Bob Seger, sometimes feel that you are nothing more than a number? Me too. That’s because to many people, that’s exactly what we are. And if there’s one thing that computers are good at, it’s assigning numbers — to orders, to accounts, to invoices, to transactions, and of course, to people. Since we have to make the computer assign numbers, we may as well learn the modern way to do it.

    In my earliest days of programming, I would store the last assigned of a series of numbers in a data file. (The S/34 and S/36 had data files, not a database.) When I began working with the S/38, I learned to store the last assigned number in a data area. My programs would retrieve the last assigned number, add 1 to it, and update the file or data area.

    There’s nothing wrong with that method, in my opinion, but I think it’s wiser to let the database do as much work as possible so that my applications have to do less. DB2 for i has two mechanisms for assigning serial numbers — identity columns and sequence objects. Comparing and contrasting the two would take me off the subject, so let me just say that I prefer to use an identity column when possible, and that’s the approach I take in the following example.

    Let’s assume a typical header-detail table configuration:

    create or replace table bvvhdr
     ( OrderID   dec (9)  primary key
                          generated always as identity,
       EntryDate date     not null with default,
       DueDate   date     not null with default,
       CustomerID dec(7)  not null with default
     );
    
    create or replace table bvvdtl
     ( OrderID   dec (9),
       Line      dec (3),
       Item      char(6) not null with default,
       Quantity  dec (3) not null with default,
    
       primary key (OrderID, Line),
    
       constraint bvvdtl_fk_orderID
          foreign key (OrderID)
          references bvvhdr (OrderID)
          on delete cascade
     );
    

    The ORDERID column is the generated key value in the header. That is, the database manager generates a new order number every time we insert a new row into the BVVHDR table.

    insert into bvvhdr (entrydate, duedate, customerid)
       values ('2019-09-09', '2019-10-01', 1)
    

    But that’s not the way I usually use INSERT in production jobs. I usually write RPG programs that insert entire data structures, so let’s see some code like that.

    ctl-opt nomain
            option(*srcstmt: *nodebugio);
    
    /copy copybooks,bvv
    
    dcl-s  Nx   int(10);
    
    dcl-proc  CreateSale   export;
    
       dcl-pi CreateSale;
          ioHeaderData             likeds(Header_t);
          ioDetailData             likeds(Detail_t)   dim(10);
          inDetailCount            int(10)            const;
       end-pi;
    
       dcl-ds  DetailData          likeds(Detail_t) based(pDD);
    
       exec sql
          insert into bvvhdr
             overriding user value
             values(:ioHeaderData);
    
       // retrieve the generated key
       exec sql
          values Identity_val_local()
            into :ioHeaderData.OrderID;
    
       for Nx = 1 to inDetailCount;
          pDD = %addr(ioDetailData (Nx));
          DetailData . OrderID = ioHeaderData.OrderID;
          DetailData . Line    = Nx;
          exec sql
             insert into bvvdtl
                values (:DetailData);
       endfor;
       return;
    
    end-proc  CreateSale;
    

    For completeness, here’s the copybook to which it refers.

    **free                                                                  
    dcl-ds  Header_t ext extname('BVVHDR') alias qualified template end-ds; 
    dcl-ds  Detail_t ext extname('BVVDTL') alias qualified template end-ds; 
                                                                            
       dcl-pr CreateSale;                                                   
          ioHeaderData             likeds(Header_t);                        
          ioDetailData             likeds(Detail_t)   dim(10);              
          inDetailCount            int(10)            const;                
       end-pr;                                                              
    

    Let’s break it down.

       exec sql
          insert into bvvhdr
             overriding user value
             values(:ioHeaderData);
    

    The insert refers to a data structure, ioHeaderData, which came into the subprocedure as a parameter. This data structure includes all the fields in the header record, including the order ID. I had to include OVERRIDING USER VALUE to tell the system to ignore the ORDERID field in the data structure.

    The system assigns an order number to the new order. How can I retrieve the generated order ID so that I can assign it to the details? There are two methods—use the IDENTITY_VAL_LOCAL function or use a table reference (SELECT FROM INSERT). I call the former “Old Reliable”, but the SQL reference manual says this:

    Alternative to IDENTITY_VAL_LOCAL:

    It is recommended that a SELECT FROM INSERT be used to obtain the assigned value for an identity column. See table-reference for more information.

    These are weasel words. For one thing, the sentence is in passive voice, which is something which is avoided by me. Who recommends it? They don’t say.

    Second, there’s no explanation. Why does this unknown person (or these unknown persons) recommend it?

    Maybe I’ll write about that method one of these days, but for this example, I’ll stick with Old Reliable.

       // retrieve the generated key
       exec sql
          values Identity_val_local()
            into :ioHeaderData.OrderID;
    

    Now the header data structure has been updated with the newly assigned order number, which we can copy into the ORDERID column of the detail data structure.

       dcl-ds  DetailData          likeds(Detail_t) based(pDD);
    
       for Nx = 1 to inDetailCount;
          pDD = %addr(ioDetailData (Nx));
          DetailData . OrderID = ioHeaderData.OrderID;
          DetailData . Line    = Nx;
          exec sql
             insert into bvvdtl
                values (:DetailData);
       endfor;
    

    The header and the details are tied together nicely and neatly.

    Updating presents a slight challenge. If you try to change the order ID of a header record, the system will respond with nasty message SQL0798 (Value cannot be specified for GENERATED ALWAYS column ORDERID). However, that does not mean that the generated column value cannot be changed. This is confusing, so first let me show you how to make it work correctly, then I’ll add a little more information.

    Here’s a short program that retrieves a row, changes a column, and then writes the data structure back to the database.

    **free
    ctl-opt bnddir('THOLT')
            option(*srcstmt: *nodebugio);
    
    /copy copybooks,bvv
    
     dcl-ds HeaderData   likeds(Header_t)  inz;
     dcl-s  OID          int(10);
    
       exec sql set option commit=*none;
    
       OID = 1;
    
       exec sql
          select * into :HeaderData
            from bvvhdr
           where orderID = :OID;
    
       HeaderData.CustomerID = 2;
    
       exec sql
          update bvvhdr
             overriding system value
             set row = :HeaderData
           where OrderID = :OID;
       return;
    

    Notice the UPDATE, specifically the part that says OVERRIDING SYSTEM VALUE. This tells the system not to increment the ORDERID. “Wow!” I hear you say. “You mean you have to tell the system that!?” Believe it or not, you do. If I specify OVERRIDING USER VALUE, as I did with the INSERT, the system will update the order number with the next available number. And if you omit the OVERRIDING option, the program won’t compile.

    There you have the basics: how to define a serial number in the database, how to retrieve it so it can be written into other tables, and how not to increment it when you shouldn’t. For more information, see the links below.

    RELATED RESOURCES

    IDENTITY_VAL_LOCAL

    Identity columns

    INSERT

    UPDATE

    Comparison of identity columns and sequences

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, DB2 for i, FHG, Four Hundred Guru, IBM i, S/38

    Sponsored by
    TL Ashford

    TL Ashford writes software to generate Barcode Labels and Forms for the IBM i.

    Our software products are the most feature-rich, cost-effective solutions available!

    TLAForms converts traditional IBM i spool files into high quality, professional forms that are output as PDF documents. A form is associated with a spool file and the form is designed. TLAForms then ‘watches’ the IBM i output queue for a new spool file to be generated. When a new spool file is generated, TLAForms reads the data from the spool file, places the data on the form, and outputs a PDF document. The PDF documents can be archived to the IFS, automatically emailed, and printed.

    Features:

    • Select Data directly from an IBM i Spool File
    • Burst IBM i Spool Files based on page or Spool File data
    • Add spool file data to form as Text using a wide variety of fonts and colors (the MICR font for printing checks is included in the software)
    • Add spool file data to form as bar code – including 2-dimensional bar codes PDF-417 and QRCode
    • Configure SQL statements to retrieve and use IBM i File data on forms
    • Utilize Actions to show or hide objects on the form based on data within the spool file
    • Import Color Graphics
    • Use Color Overlays
    • Create Tables
    • Forms can be archived to the IFS
    • Forms can be emailed automatically
    • Forms can be printed to any IBM i, Network or Windows printer
    • Forms are automatically generated when a new spool file is generated in the IBM i output queue
    • NO PROGRAMMING required
    • On-Line Video Training Library

    Learn more about TLAForms at www.tlashford.com/TLA2/pages/tlaforms/overview.html

    Barcode400 is native IBM i software to design and print labels directly from your IBM i in minutes! Compliance and RFID labeling is easy using Barcode400’s tools and templates.

    Features:

    • Software resides on the IBM i
    • IBM i security and Backup
    • Labels are centrally located on the IBM i
    • Label formats are available to all users the instant they are saved – even in remote facilities
    • GUI designer (Unlimited Users)
    • Generate Labels as PDF documents!
    • Print to 100’s of thermal transfer printers
    • Print to HP and compatible printers
    • Print labels interactively – No Programming Necessary!

    OR Integrate into existing application programs to automatically print labels – Barcode400 has been integrated with nearly every ERP and WMS software available on the IBM i, including thousands of in-house written applications.

    • On-Line Video Training Library
    • Free Compliance Labels
    • Generate Checks using the MICR font
    • RFID Support (optional)
    • History / Reprint Utility
    • Integration Assistant
    • Low Cost (no tiered pricing)

    Learn more about Barcode400 at www.tlashford.com/TLA2/pages/bc400labels/overview.html

     

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    IBM And Inspur Power Systems Buck The Server Decline Trends Software Change Management Has To Change With The DevOps Times

    One thought on “Guru: I’m A Number, You’re A Number, Everybody’s A Number”

    • Jose Walker says:
      September 9, 2019 at 1:27 pm

      1) You can get a Array of IDs in one line:
      select * into :AHdr from final table(
      insert into bvvhdr
      overriding user value
      values (1, ‘2019-09-01’, ‘2019-09-11’, 4),
      (1, ‘2019-09-02’, ‘2019-09-12’, 5),
      (1, ‘2019-09-03’, ‘2019-09-13’, 6) )

      2) If last ID is 5 and You do this:
      Update bvvhdr
      overriding system value
      set OrderID = 40
      where OrderID = 5
      You will get a error in the future.

      Best Regards,
      José

      Reply

    Leave a Reply Cancel reply

TFH Volume: 29 Issue: 49

This Issue Sponsored By

  • Maxava
  • COMMON
  • RPG & DB2 Summit
  • WorksRight Software
  • Manta Technologies

Table of Contents

  • Talking Digital Transformation With The New And Prior CEO
  • Software Change Management Has To Change With The DevOps Times
  • Guru: I’m A Number, You’re A Number, Everybody’s A Number
  • IBM And Inspur Power Systems Buck The Server Decline Trends
  • IBM i PTF Guide, Volume 21, Number 35

Content archive

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

Recent Posts

  • Doing The Texas Two Step From Power9 To Power10
  • PHP’s Legacy Problem
  • Guru: For IBM i Newcomers, An Access Client Solutions Primer
  • IBM i 7.1 Extended Out To 2024 And Up To The IBM Cloud
  • Some Practical Advice On That HMC-Power9 Impedance Mismatch
  • IBM Extends Dynamic Capacity Pricing Scheme To Its Cloud
  • Here’s What You Should Do About The IBM i Skills Shortage
  • Matillion Founder Recounts Midrange Roots
  • Four Hundred Monitor, February 24
  • IBM i PTF Guide, Volume 23, Number 8

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.