• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Use SQL To Update A Sequence Number

    November 14, 2012 Hey, Ted

    Is it possible to use a single SQL statement to assign an ascending sequence number to a column in a table? I’d like the sequence number to start at 10 and increment by 10 as every row is updated so that the number column in the updated rows would be 10, 20, 30, etc.

    –Doug

    I know a way, Doug. However, let me say up front that I’ve only played with this. That is, I’ve never used it in a production environment. I can’t speak to how practical it might be or what you might need to watch out for.

    DB2 for i provides a way to create an object that generates a sequence of numbers. You can use this object to update your database table (physical file). Here’s an example.

    First, create a table to play with.

    create table TestSeq
    (SerialNbr dec(5,0), Name char(24))
    

    Next, put some data into the table.

    insert into testseq (Name) values 
    ('Bob White-Quayle'),
    ('Billy Doo'),
    ('Jack O''Napes')
    

    Here’s what the data looks like.




    SERIALNBR

    SERIALNBR

    NAME

    Null

    Bob White-Quayle

    Null

    Billy Doo

    Null

    Jack O’Napes

    Create the sequence generator.

    create sequence renumber
    start with 10
    increment by 10
    no maxvalue
    no cycle
    

    Update your table.

    update testseq
       set SerialNbr = next value for renumber
    

    Take another look at the data.




    SERIALNBR

    SERIALNBR

    NAME

    10

    Bob White-Quayle

    20

    Billy Doo

    30

    Jack O’Napes

    If you’re finished with the sequence generator, you can get rid of it.

    drop sequence renumber
    

    If not, you can leave it around for next time. The sequence will start where it left off.

    Maybe that’s an answer to your question.

    You can also use a sequence generator when adding data to a table.

    insert into testseq (SerialNbr, Name) values
    (next value for renumber, 'Bob White-Quayle'),
    (next value for renumber, 'Billy Doo'),
    (next value for renumber, 'Jack O''Napes')
    

    This looks like a good way to generate unique key values. Maybe I’ll get a chance to use it for real someday.

    –Ted

    RELATED STORY

    V5R3 Advances DB2 UDB for iSeries



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Share on Reddit (Opens in new window) Reddit
    • Share on Facebook (Opens in new window) Facebook
    • Share on LinkedIn (Opens in new window) LinkedIn
    • Share on X (Opens in new window) X
    • Email a link to a friend (Opens in new window) Email

    Tags:

    Sponsored by
    ARCAD Software

    AI is opening new possibilities for IBM i modernization, but for business-critical applications, performance alone is not enough: managing risks remains essential.

    How do BOB, Claude, and ARCAD Transformer RPG compare in converting RPG code to free-form RPG?

    Join Alan Ashley, Sr. Solutions Architect, as he presents a hands-on benchmark comparing these three solutions.

    Beyond conversion performance, discover how to minimize modernization risks through automated regression testing, validating application behavior and detecting unintended changes before they impact business operations.

    During this session, you’ll discover:

    • Compare code conversion performance: BOB vs. Claude vs. ARCAD Transformer RPG
    • Understand the role of deterministic automation alongside AI in a comprehensive IBM i modernization strategy
    • Accelerate RPG code transformation with predictable, rule-based processes
    • Reduce business risks through automated regression testing with ARCAD Verifier
    • Build a controlled, auditable modernization process that protects business-critical applications

    Register now for this webinar to discover how ARCAD Software’s deterministic modernization solutions complement AI initiatives, providing a structured, repeatable, and controlled approach to application transformation.

    Share this:

    • Share on Reddit (Opens in new window) Reddit
    • Share on Facebook (Opens in new window) Facebook
    • Share on LinkedIn (Opens in new window) LinkedIn
    • Share on X (Opens in new window) X
    • Email a link to a friend (Opens in new window) Email

    Sponsored Links

    HiT Software:  Download FREE paper "Change Data Capture for Business Intelligence and Analytics"
    looksoftware:  Achieving the impossible with RPG Open Access. Live webcast Dec 4 & 5.
    ITJ Bookstore:  Bookstore BLOWOUT!! Up to 50% off all titles! Everything must go! Shop NOW

    IT Jungle Store Top Book Picks

    Bookstore Blowout! Up to 50% off all titles!

    The iSeries Express Web Implementer's Guide: Save 50%, Sale Price $29.50
    The iSeries Pocket Database Guide: Save 50%, Sale Price $29.50
    Easy Steps to Internet Programming for the System i: Save 50%, Sale Price $24.97
    The iSeries Pocket WebFacing Primer: Save 50%, Sale Price $19.50
    Migrating to WebSphere Express for iSeries: Save 50%, Sale Price $24.50
    Getting Started with WebSphere Express for iSeries: Save 50%, Sale Price $24.50
    The All-Everything Operating System: Save 50%, Sale Price $17.50
    The Best Joomla! Tutorial Ever!: Save 50%, Sale Price $9.98

    Converting CASE in CL Admin Alert: A Checklist For Performing IBM i Planned Maintenance

    Leave a ReplyCancel reply

Volume 12, Number 27 -- November 14, 2012
THIS ISSUE SPONSORED BY:

ProData Computer Services
WorksRight Software
PowerTech

Table of Contents

  • Converting CASE in CL
  • Use SQL To Update A Sequence Number
  • Admin Alert: A Checklist For Performing IBM i Planned Maintenance

Content archive

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

Recent Posts

  • IBM i Delivers New Development Tooling With 2026 Fall Tech Refreshes
  • Big Blue Says AI Is Not Behind The Big Uptick In IBM i Security Alerts
  • Guru: Web Service With Pagination
  • The GenAI Revolution And Product Shortages Send IT Budgets Skyward
  • IBM i PTF Guide, Volume 28, Number 40
  • IBM i PTF Guide, Volume 28, Number 41
  • Bob Code Assistant Gets Better Understanding Of IBM i Applications
  • IBM i In The Cloud And The Case For Reversing Replication
  • Guru: From Hiking Boots To Long-Term Memory
  • Inside IBM’s New PowerHA Cloud Offering, Full System Manager

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