DB2 SQL for IBM i TR6 Enhancement

Posted by: Bob Cozzi

Rogue Programmer

Cozzi Productions, Inc.

www.cozTools.com/qryfile

Chicagoland

DB2 SQL for IBM i TR6 Enhancement
has no ratings.

Published: 12 Apr 2013
Revised: 03 May 2013 - 4010 days ago
Last viewed on: 25 Apr 2024 (5798 views)

Main URL:

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) );

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: 10 years 11 months 25 days 2 hours 29 minutes ago
Edited: Thu, 02 May, 2013 at 15:38:43 (4011 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: 10 years 11 months 25 days 35 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: 10 years 11 months 24 days 2 hours 55 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.