• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Surmounting Identity Column Challenges

    June 11, 2014 Ted Holt

    Using an identity column is a wonderful way to ensure that each row of a table (record of a physical file) has a unique identifier. It sure beats storing the next batch (invoice, transaction, etc.) number in a data area or a one-record database file. Here are a few things to keep in mind when inserting rows (writing records) into a table that has an identity column.

    1. Let the system assign the value to the identity column. You do this by specifying GENERATED ALWAYS or omitting the GENERATED option when you create the table.

    create table Sales
      ( ID                integer
                          generated always as identity,
        Invoice           dec  (9,0),
        Line              dec  (3,0),
        InvoiceDate       date,
        Customer          dec  (7,0),
        Item              char (12),
        Quantity          dec  (3,0),
        Price             dec  (5,2),
      primary key (ID))
    

    Or:

    create table Sales
      ( ID                integer as identity,
    . . . etc . . .
    

    The other option, GENERATED BY DEFAULT, allows you to assign a value of your choosing to the identity column. There are probably situations that call for this option, but I can’t think of any at the moment.

    2. One way to insert a row is to list the columns (fields), omitting the identity column.

    insert into Sales
       (Invoice, Line, InvoiceDate, Customer, Item, Quantity, Price)
    values (1001, 002, '2014-06-04', 6004, 'AB-221',  6,    5 )
    

    3. If you have a lot of columns in a table (think dozens or even hundreds of columns), you may not want to list them. If you don’t list the columns, assign the value DEFAULT to the identity column.

    insert into Sales values
    (default, 1001, 002, '2014-06-04', 6004, 'AB-221',  6,    5 )
    

    The system assigns the next value to the ID column.

    4. Combining two tables can be a challenge. What if you want to add all the rows in the sales table to the sales history table, and both tables have some of the same values in their identity columns? Don’t panic. Just add OVERRIDING USER VALUE to the statement.

    insert into SalesHistory
       overriding user value
       select * from Sales
    

    The system will ignore the values in the ID column of the Sales table and assign new values to the ID column when it inserts the Sales rows into SalesHistory.

    I have written plenty of code that retrieves the next sequential number from a data area or physical file, and I wouldn’t think of changing any of it that is still in production. But I don’t plan to use that technique again.

    RELATED STORIES

    Reader Feedback and Insights: Identity Columns and Performance

    Guarantee Unique Key Values



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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ProData

    Simple.  Reliable.  Powerful. 

    IBM i software to help you navigate your universe!

    Use these tools to get where you are going:

    DBU to easily, yet securely, access the data on your IBM i or remote system.
    RDB Connect for powerful record level access to remote data using RPG, CL or COBOL.

    SQL/Pro and Audit offer reliable SQL data processing, tracking and reporting.

    RDR retrieves those records which were deleted in error.

    And more!

    Join our DBUniverse of loyal, happy customers and experience your destination…

    Do IT with DBU!

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    inFORM Decisions:  Register for our white paper on AP Automation.
    CloudFax400:  Moving to an IBM PureSystems™? You need our cloud-based enterprise FAX service.
    Remain Software:  IBM i Application Lifecycle Management freedom and flexibility

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ EnterpriseTech: High Performance Computing Industry News From ITJ EIC Timothy Prickett Morgan

    Watson Tapped For Mobile Apps Four-Core Power8 Box For Entry IBM i Shops Ships Early

    Leave a Reply Cancel reply

Volume 14, Number 13 -- June 11, 2014
THIS ISSUE SPONSORED BY:

ProData Computer Services
HelpSystems
WorksRight Software

Table of Contents

  • Custom Perspectives In RSE
  • Surmounting Identity Column Challenges
  • Admin Alert: Reorganizing IBM i Files To Improve Disk Performance, Part 1

Content archive

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

Recent Posts

  • IBM i Development and Modernization is Getting A Fresche Start with Some Ground-Breaking Subscriptions
  • CloudSAFE And Focal Point Solutions Group Combine Services, Unify Brands
  • Guru: Partitioning Result Sets Using SQL
  • As I See It: Elusive Connections
  • IBM i PTF Guide, Volume 25, Number 47
  • AWS Inks Deal With Connectria To Have a Power Play
  • IBM i Shops Have Alternatives to Db2 Web Query
  • Eradani Lays Waste to API Payload Restrictions
  • Four Hundred Monitor, November 15
  • Old PHP and Other PASE Apps Break on IBM i 7.5

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