Re: SQL database object names longer than 10 characters and mixed case?

[Alan Campin <alan0307d@xxxxxxxxx>]

We define 100% of our new generation databases as SQL tables with long
names. All names are stored in the system definition tables. 10 and long.

If you define a table with a long name you can do a DSPFD on the short name
and see the long name.

A DSPFFD will give you the long names for a given table. I use DBU and it
shows the long field names and I prefer to use Ops Nav, Database as it gives
me the long and the short on the screen in a column.

Here is a script to create a table. We always give explicit long names and
don't let the system assign them. Trying to figure out what perfr00001 and
perfr00002, etc is is just too painful.

You can also use the ALIAS keyword in DDS but I recommend DDL.

DROP TABLE PERFORMANCE_SCORE ;

CREATE TABLE PERFORMANCE_SCORE (
  ID FOR COLUMN PSID       BIGINT
    GENERATED ALWAYS AS IDENTITY (
     START WITH 1 INCREMENT BY 1
     MINVALUE 1 MAXVALUE 9223372036854775807
     NO CYCLE NO ORDER
     CACHE 20 )    ,
  DRIVER_ID FOR COLUMN PSDRIVERID BIGINT DEFAULT NULL ,
  SCORE FOR COLUMN PSSCORE    BIGINT NOT NULL ,
  SCORE_RECORDED FOR COLUMN PSRECORDTS TIMESTAMP NOT NULL ,
  ADDED_TIMESTAMP FOR COLUMN PSADDTS    TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP ,
  ADDED_USER_ID FOR COLUMN PSADDUSER  BIGINT DEFAULT NULL ,
  UPDATED_TIMESTAMP FOR COLUMN PSUPDTS    TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP ,
  UPDATED_USER_ID FOR COLUMN PSUPDUSER  BIGINT DEFAULT NULL ,
  OBJECT_VERSION FOR COLUMN PSOBJVER   BIGINT NOT NULL DEFAULT 0 ,
    CONSTRAINT PRI_PERFSCO_00001 PRIMARY KEY( ID ) )
RCDFMT RPERFSCO   ;

RENAME TABLE PERFORMANCE_SCORE TO SYSTEM NAME PERFSCO ;

ALTER TABLE PERFORMANCE_SCORE
  ADD CONSTRAINT FOR_PERFSCO_PSDRIVERID_00001
    FOREIGN KEY( DRIVER_ID )
    REFERENCES DRIVER ( ID )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION ;

ALTER TABLE PERFORMANCE_SCORE
  ADD CONSTRAINT FOR_PERFSCO_PSADDUSER_00001
    FOREIGN KEY( ADDED_USER_ID )
    REFERENCES USER_PROFILE ( ID )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION ;

ALTER TABLE PERFORMANCE_SCORE
  ADD CONSTRAINT FOR_PERFSCO_PSUPDUSER_0001
    FOREIGN KEY( UPDATED_USER_ID )
    REFERENCES USER_PROFILE ( ID )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION ;

LABEL ON TABLE PERFORMANCE_SCORE
  IS 'Performance Score.' ;

LABEL ON COLUMN PERFORMANCE_SCORE (
  ID IS 'Id' ,
  DRIVER_ID IS 'Driver              Id' ,
  SCORE IS 'Score' ,
  SCORE_RECORDED IS 'Score          Timestamp' ,
  ADDED_TIMESTAMP IS 'When                Added               Timestamp' ,
  ADDED_USER_ID IS 'Who                 Added               Id' ,
  UPDATED_TIMESTAMP IS 'When                Updated' ,
  UPDATED_USER_ID IS 'Who                 UpdateD             Id' ,
  OBJECT_VERSION IS 'Object              Version' ) ;

LABEL ON COLUMN PERFORMANCE_SCORE(
  ID TEXT IS 'Id' ,
  DRIVER_ID TEXT IS 'Driver Id' ,
  SCORE TEXT IS 'Score' ,
  SCORE_RECORDED TEXT IS 'Score Timestamp' ,
  ADDED_TIMESTAMP TEXT IS 'When Added Timestamp' ,
  ADDED_USER_ID TEXT IS 'Who Added User Id' ,
  UPDATED_TIMESTAMP TEXT IS 'When Updated Timestamp' ,
  UPDATED_USER_ID TEXT IS 'Who Updated User Id' ,
  OBJECT_VERSION TEXT IS 'Object Version' ) ;

On Fri, Oct 29, 2010 at 11:11 AM, Morgan, Paul <Paul.Morgan@xxxxxxxxxxx>wrote:

> Dave,
>
> It generates an iSeries object or field name with the first six characters
> of the name followed by a four digit number.  The long name against the
> short name is linked up in the SQL definition tables.
>
> Paul
>
> Principal Programmer Analyst
> IS Supply Chain/Replenishment
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
> midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dave Murvin
> Sent: Friday, October 29, 2010 12:27 PM
> To: Midrange Systems Technical Discussion
> Subject: SQL database object names longer than 10 characters and mixed
> case?
>
> I gather that is is possible to have SQL database object names on the
> System i that are longer than 10 characters and mixed case.  This could
> be SQL tables, views, triggers, etc.  I just can't see where they would
> be used or stored.  Perhaps in the IFS?
>
> Has anyone actually used these long names, and if so, could you give me
> an example?  (Not the actual SQL code, but just a sample name, where you
> would store in on the system, and what type of application would use it.)
>
> Thanks.
>
> --
> Dave Murvin
>
> www.drmetools.com
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.