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.
midrange.com
