• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Counterintuitive Table Creation

    October 13, 2010 Ted Holt

    To be able to create one table that is described exactly like an existing table or view is good. But how you create that table determines whether the new table is exactly like the old one or not. Believe it or not, something as trifling as a pair of parentheses can make a difference.

    Small Things Matter

    The CREATE TABLE statement has several forms. Here are the two I want to talk about today.

    CREATE TABLE xxx LIKE zzz
    
    CREATE TABLE xxx (LIKE zzz)
    

    There’s not much difference–one pair of parentheses. But as amazing as it sounds, the inclusion or omission of parentheses matters. Let me demonstrate.

    First, create a table.

    create table someschema/customers
      (Account     dec(7)   not null,
       Name        char(20),
       Class       char(1) default 'A',
       primary key (Account))
    

    Add column headings.

    label on column someschema/customers
    (Account is
     '  Customer             Account              Number    ',
     Name is
     '                    Customer            Name      ',
     Class is
     '                    Customer            Class     ')
    

    Put some data into the table. Notice two inserts. The first one supplies values for all three columns. The second one loads the account and name columns only.

    insert into someschema/customers values
    (1001, 'Camus, Albert',       'B'),
    (1002, 'Maupassant, Guy de',  'A'),
    (1003, 'Sartre, Jean-Paul',   'B'),
    (1004, 'Vern, Jules',         'C')
    
    insert into someschema/customers (Account, Name)
       values (1005, 'Gide, Andre')
    

    Here’s what the database looks like when I query it with green-screen SQL.

    select * from customers
    
      Customer
       Account   Customer              Customer
        Number   Name                  Class
         1,001   Camus, Albert            B
         1,002   Maupassant, Guy de       A
         1,003   Sartre, Jean-Paul        B
         1,004   Vern, Jules              C
         1,005   Gide, Andre              A
    

    Do you see the nice column headings? Do you see that Gide’s classification code defaulted to A?

    Now create two more tables like the first one.

    create table someschema/custparen (like someschema/customers)
    
    create table someschema/custnoparen like someschema/customers
    

    Run the same two inserts against the two new tables. Take a look at the table that was created without parentheses.

    select * from custnoparen
    
      Customer
       Account   Customer              Customer
        Number   Name                  Class
         1,001   Camus, Albert            B
         1,002   Maupassant, Guy de       A
         1,003   Sartre, Jean-Paul        B
         1,004   Vern, Jules              C
         1,005   Gide, Andre              A
    

    It looks just like the first table. Now look at the table that was created with parentheses.

    select * from custparen
      ACCOUNT   NAME                  CLASS
        1,001   Camus, Albert           B
        1,002   Maupassant, Guy de      A
        1,003   Sartre, Jean-Paul       B
        1,004   Vern, Jules             C
        1,005   Gide, Andre             -
    

    Do you see the differences? The nice column headings are not there, and Gide’s classification code is null (represented by a hyphen.) What happened?

    According to the IBM Info Center, when you include parentheses around the LIKE clause, the system does not retain certain attributes when making the copy. I copied the following list from the 7.1 Info Center:

    • Default value, if a table name is specified (view name is not specified)
    • Nullability
    • Hidden attributes
    • Identity attributes
    • Column heading and text

    Copy Options

    You may use copy options to further control which attributes are copied. Here’s another CREATE TABLE command.

    create table someschema/custdft 
     (like someschema/customers
         including column defaults)
    

    After the same two inserts as before, what does this new table look like?

    ACCOUNT   NAME                  CLASS
      1,001   Camus, Albert           B
      1,002   Maupassant, Guy de      A
      1,003   Sartre, Jean-Paul       B
      1,004   Vern, Jules             C
      1,005   Gide, Andre             A
    

    Notice that column defaults were copied, but the column headings were not.

    Here is the list of copy options from the 7.1 documentation.

    INCLUDING IDENTITY COLUMN ATTRIBUTES
    EXCLUDING IDENTITY COLUMN ATTRIBUTES

    EXCLUDING COLUMN DEFAULTS
    INCLUDING COLUMN DEFAULTS
    USING TYPE DEFAULTS

    INCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES
    EXCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES

    INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES
    EXCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES

    It Is What It Is

    This business of including or omitting parentheses is hardly intuitive, but nobody called to ask my opinion when this decision was made. As a rule, I omit the parentheses.



                         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
    Raz-Lee Security

    Start your Road to Zero Trust!

    Firewall Network security, controlling Exit Points, Open DB’s and SSH. Rule Wizards and graphical BI.

    Request Demo

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Help/Systems:  FREE Webinar: Manage your temporary storage and IFS. Oct. 21, 9 a.m. CST
    PowerTech:  FREE Webinar! Securing & Controlling Your Powerful Users with Authority Broker. Oct. 20
    IT Jungle Bookstore:  BACK IN STOCK: Internet Programming for AS/400, iSeries & System i

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    VAI Inks Partnership with Continental Resources The Hundred Thousand Plus on the Four Hundred

    Leave a Reply Cancel reply

Volume 10, Number 31 -- October 13, 2010
THIS ISSUE SPONSORED BY:

WorksRight Software
ProData Computer Services
inFORM Decisions

Table of Contents

  • Don’t Let Your RPG Just Drift, Grab an OAR!
  • Counterintuitive Table Creation
  • Admin Alert: Will i5/OS V5R4M

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