Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
DB2 SQL for IBM i TR6 Enhancement
has no ratings.
Published: 12 Apr 2013
Revised: 03 May 2013 - 1422 days ago
Last viewed on: 25 Mar 2017 (2942 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Using the SQL FOR SYSTEM NAME Clause Published by: Bob Cozzi on 12 Apr 2013 view comments(3)

System Name Support in SQL

With Technology Refresh 6 (TR6) DB2 SQL for IBM i now includes the ability to use long SQL names along side traditional IBM "object names". This means you can specify lengthy SQL names in your SQL statements, and also specify the corresponding name generated for the system's use. This avoids having names like CVT00001, CVT00002 generated when you create a table named (for example) CVTCurrency.

The feature uses the "FOR SYSTEM NAME" clause to identify the IBM i name being associated with the SQL name specified elsewhere on the SQL statement. This does not associate a separate object with the SQL name, it simply allows you to specify what name to use when creating a TABLE, VIEW, INDEX or temp Table. Here's an example that creates a table named "Currency_Conversion" and tells the SQL statement to use "CVTCUR" as the system name (i.e., the IBM i 10-position object name)

 

CREATE TABLE Currency_Conversion FOR SYSTEM NAME CVTCUR
( cid integer GENERATE ALWAYS AS IDENTITY,
  CURCODE CHAR(3) NOT NULL DEFAULT "",
  DOLLAR  DECIMAL(15,9),
  CURRENCY DECIMAL(15,9),
  PRIMARY KEY (CID) );

 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: DB2 SQL for IBM i TR6 Enhancement
Posted: 3 years 10 months 23 days 6 hours 55 minutes ago
Edited: Thu, 02 May, 2013 at 15:38:43 (1423 days ago)

I'm confused on what is new here.  We've been using long object names for tables and columns for years.  For example:

CREATE TABLE        TB_WIP_SKUWHS_ADJ                    
   (ID_COMPANY          CHAR(3)                 NOT NULL 
   ,ID_LOB              CHAR(2)                 NOT NULL 
   ,ID_LOCATION         CHAR(5)                 NOT NULL 
   ,CD_WHS_ADJ_TYP      CHAR(2)                 NOT NULL 
   ,ID_ITM_SKU          CHAR(8)                 NOT NULL 
   ,NU_SEQUENCE         SMALLINT                NOT NULL 
   ,CD_WHS_SUB_TYP      CHAR(2)                          
   ,QU_ITM_ADJ          INTEGER                          
   ,AM_ITM_ADJ_CST      DECIMAL(7,2) ...........                     

etc. was created years ago on our iSeries.  We would then follow up the CREATE statement with:

LABEL ON TABLE TB_WIP_SKUWHS_ADJ IS 'SGR33X2N';  

I guess it's just the FOR SYSTEM NAME clause that is new?

 

Posted by: BrianR
Premium member *
Green Bay, WI
Comment on: DB2 SQL for IBM i TR6 Enhancement
Posted: 3 years 10 months 23 days 5 hours 1 minutes ago

Steve,

In the example you posted, the system object name would be TB_WIxxxxx with xxxxx being a numeric value (probably 00001).  The LABEL ON statement merely changed the object text (not the object name).  Previously, if you wanted a more de!--script--ive object name, such as TBWPSKWHAD, you would have to RNMOBJ (actually, I'm not sure if that would fully work).  Now you can use the FOR SYSTEM NAME clause to do it all in the same SQL statement.

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: DB2 SQL for IBM i TR6 Enhancement
Posted: 3 years 10 months 22 days 7 hours 22 minutes ago

What I gave was actually incomplete out of my clipboard.  We follow the CREATE with:

LABEL ON TABLE TB_WIP_SKUWHS_ADJ IS 'SGR33X2N';               
RENAME   TABLE TB_WIP_SKUWHS_ADJ TO SYSTEM NAME SGR33X2N ;    

The RENAME is what took care of the short name for us.  The RENAME would now  be unnecessary with the new clause.