• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Continue A Sequence When Inserting

    January 15, 2018 Ted Holt

    Carlos writes, “Hey, Ted! I have a question regarding inserting rows into a file that is keyed on a sequence number. I need to insert more rows, and I need the new rows to have the next available sequence numbers. Can I perform this task with an SQL INSERT statement, or do I have to use record-level access?”

    If the sequence number were defined as an identity column, Carlos would have no problem. Unfortunately, the sequence number column is a simple numeric field. Fortunately, I was able to give Carlos two solutions. It would not surprise me if you can suggest others.

    Let’s set up the problem. The table (physical file) into which Carlos wants to insert the new rows — let’s call it FILEA — looks something like this:

    Sequence DataA
    1 A
    2 B
    3 C
    4 D
    5 E

    The table with the new data — FILEB — looks like this:

    DataB
    M
    N
    O

    Carlos wanted to insert three new rows beginning with sequence number 6. The easy way to do this is to use a function I wrote a long time ago — the INCR function. It’s a simple RPG routine that returns an integer value that is one greater than the previous value.

    insert into filea
       select incr(5), field3
         from fileb
        order by datab
    

    Now FILEA looks like this:

    Sequence DataA
    1 A
    2 B
    3 C
    4 D
    5 E
    6 M
    7 N
    8 O

    If you don’t want to use my function, you can use IBM’s code instead. Create a sequence. I’ll unimaginatively call it SEQ1.

    create or replace sequence mylib.seq1
        as dec (3) start with 6
    

    Use the NEXT VALUE expression to retrieve the next sequence number from SEQ1.

    insert into filea
       select next value for seq1, datab
         from fileb
        order by datab
    

    If it were not for one little-bitty limitation of CREATE SEQUENCE and ALTER SEQUENCE, I would not have mentioned my INCR function. That limitation is this: the START WITH value must be a numeric literal. I tried to retrieve the largest sequence number from FILEA into a variable and set the sequence to the next integer value, but no soap. However, I was able to make it work with INCR. Here’s a dynamic compound statement that worked properly.

    begin
    
       declare  NextSeq   integer;
    
       values (select max(field1) from filea) into NextSeq;
    
       insert into filea
          select incr(NextSeq), datab
            from fileb
           order by datab;
    end
    

    I’m very grateful for the functionality that IBM packs into the world’s best relational database management system. I’m also grateful that I can write my own code when necessary. We have the best of both worlds.

    RELATED STORIES

    Numbering Rows in an SQL Result Set

    CREATE SEQUENCE

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    FOCAL POINT SOLUTIONS GROUP

    IntellaFLASH™

    FPSG is the only hosting provider that offers IntellaFLASH™.  IntellaFLASH was created by FPSG and is an exclusive FPSG solution that provides the following:

    • No User downtime for production backups
    • Supports BRMS and Tivoli Storage Manager
    • Provides near Continuous Data Protection (CDP)
    • Create point-in-time copies of your entire environment within minutes
    • Easy and quickly repeatable
    • Processes are tied into Job Schedulers
    • No user downtime for planned outages
    • No disruption to the send and receive process production, and DR stays in sync during the Switch test
    • Supports heterogeneous environments
    • Create test/development environments on the fly
      ⇒ Simplify operating system/application upgrade testing efforts
      ⇒ Improve quality assurance testing

     

    Watch our IntellaFLASH™ Video to learn more

    Let’s Discuss Your Custom Solution Needs

    ContactUs@FocalPointSg.com

    Follow us on LinkedIn

    focalpointsg.com | 813.513.7402

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    As I See It: Resolutions, Or Let’s Make A Deal Modernization Slides Down IBM i Priority List

    6 thoughts on “Guru: Continue A Sequence When Inserting”

    • Barry Thrash says:
      January 15, 2018 at 9:54 am

      –Build Temp Tables
      Declare Global Temporary Table FileA (
      Sequence Numeric(9, 0) ,
      DataA Char(10)
      ) With Replace;

      Declare Global Temporary Table FileB (
      DataB Char(10)
      ) With Replace;

      –Load Tables
      Insert Into QTemp.FileA Values
      (1, ‘A’),
      (2, ‘B’),
      (3, ‘C’),
      (4, ‘D’),
      (5, ‘E’);

      Insert Into QTemp.FileB Values
      (‘M’),
      (‘N’),
      (‘O’);

      –Check your data
      Select * From QTemp.FileA;

      –Run the insert with Row_Number
      Insert Into QTemp.FileA
      (Select Row_Number ()
      Over (Order by (DataB)) + (Select Count(*)
      From QTemp.FileA),
      a.*
      From QTemp.FileB a);

      –Validate Result
      Select * From QTemp.FileA;

      Reply
    • glenngundy says:
      January 15, 2018 at 10:20 am

      Hey Ted. Great topic. Assuming FILEA has a unique key of SEQUENCE, I’m wondering what would happen if two or more of these insert jobs were running at the same time (each using a different input file of course) and they tripped over each other when writing to FILEA. Is it possible to modify the compound statement to use the next available number? Would you have to change the one INSERT statement to use a cursor?

      Reply
    • glenngundy says:
      January 15, 2018 at 10:36 am

      Would a CTE help to do this?

      Reply
    • Scott Sims says:
      January 15, 2018 at 2:48 pm

      Hey Ted,

      You could also use the row_number function along with a little subquery.

      Insert into filea
      select row_number( ) over () + (select max(Sequence) from filea), datab
      from fileb
      order by datab;

      This “seeds” the row number with the highest Sequence number in filea giving you the incremented numbers you are wanting from the row_number function for the insert.

      Of course no other insert operation could run at the same time.

      Reply
      • Scott Sims says:
        January 15, 2018 at 2:54 pm

        Same idea as Barry’s just a little different in little different in the subquery.

        Reply
    • Mark Murphy says:
      January 16, 2018 at 2:46 pm

      Glenn, when using a sequence, each job get’s it’s own set of numbers. You can adjust how big that set is when you create the sequence. The result is that sequence numbers may not be inserted sequentially if multiple jobs are pounding on it. By default each job caches 20 values on first access, so Job1 gets sequence numbers 1-20, job2 get 21-40, and job3 gets 41-60. If these three jobs then start inserting records rapid fire you could get sequence numbers added like 1, 41, 21, 22, 42, 43, 2, 23, 3, 44, etc. The jobs don’t step on each other, and they don’t contend for data area locks unless they all run through their cache at the same time, but the sequence numbers are not sequential, and they could have gaps of up to 19 numbers if a job grabs a set of numbers and only uses one of them before it ends. You can also set the cache to 1 value, but then you would have contention for the data area. This wouldn’t be an issue if the file contention you would get by grabbing the largest number in the file each time wasn’t an issue.

      Reply

    Leave a Reply Cancel reply

TFH Volume: 28 Issue: 3

This Issue Sponsored By

  • UCG TECHNOLOGIES
  • WorksRight Software
  • RPG & DB2 Summit
  • Computer Keyes
  • Manta Technologies

Table of Contents

  • Update On The Spectre And Meltdown Patches For Power
  • Modernization Slides Down IBM i Priority List
  • Guru: Continue A Sequence When Inserting
  • As I See It: Resolutions, Or Let’s Make A Deal
  • Rimini Street Finally Finds Its Way to Wall Street

Content archive

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

Recent Posts

  • COMMON Set for First Annual Conference in Three Years
  • API Operations Management for Safe, Powerful, and High Performance APIs
  • What’s New in IBM i Services and Networking
  • Four Hundred Monitor, May 18
  • IBM i PTF Guide, Volume 24, Number 20
  • IBM i 7.3 TR12: The Non-TR Tech Refresh
  • IBM i Integration Elevates Operational Query and Analytics
  • Simplified IBM i Stack Bundling Ahead Of Subscription Pricing
  • More Price Hikes From IBM, Now For High End Storage
  • Big Blue Readies Power10 And IBM i 7.5 Training for Partners

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.