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

Dave,

Most of the replies mentioned the system creating short name. I'm with Alan, they are just too painful, especially if you try to use the tables in RPG. Always specify the system name and SQL name.

We mostly use the system names because we write RPG code. More and more tools though are picking up the SQL names which I like a lot. We also have another group that retrieves data from our system through an SQL interface. SQL names help them identify what they need and we can communicate much easier. Same for our .NET group.

This is the shell we use to create our tables. It is very similar to Alan's example. Some of the differences are that we are still on 5.4 and our change management system wants things done a certain way.

-- -----------------------------------------------------------------------------
-- Table . . . : Table_Name Author . . :
-- Date . . . . : mm/dd/yyyy
-- Project . . :
-- Purpose . . :
-- Compile Notes: RUNSQLSTM SRCFILE(library/QSQL) SRCMBR(table_name)
-- COMMIT(*NONE) ERRLVL(20)
-- -----------------------------------------------------------------------------
-- Modifications:
--
-- Project Date Developer Description
-- -----------------------------------------------------------------------------
-- xxxxxxxxx xx/xx/xxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-- -----------------------------------------------------------------------------

-- Drop existing table
-- Drop table &LIB/table_name;

-- Create table
Create table &LIB/Format_Name (
LongName for shortname Numeric(19,0) not null,
Type_Status for STSTATUS Char(1) not null with default 'P'
Constraint Table_Name_Record_Status
Check(Type_Status in ('A', 'I', 'P')),
LongDate for shortdate Date,
LongTimestamp for shorttimestamp Timestamp,
Add_User for AddUser Char(18) not null with default USER,
Add_Timestamp for AddTmsp Timestamp not null
with default CURRENT_TIMESTAMP,
Add_Proc for AddProc Char(128) not null,
Chg_User for ChgUser Char(18),
Chg_Timestamp for ChgTmsp Timestamp,
Chg_Proc for ChgProc Char(128),
Dlt_User for DltUser Char(18),
Dlt_Timestamp for DltTmsp Timestamp,
Dlt_Proc for DltProc Char(128),

-- Create primary key(s)
Constraint Table_Name_Key Primary Key (Primary_Key_1, Primary_Key_2),

-- Referential constraint
Constraint Valid_Table_Name_Field
Foreign key (Table_Field_1, Table_Field_2)
references Ref_Table (Ref_Field_1, Ref_Field_2)
On delete cascade
On update RESTRICT);

-- Assign long name and system name
Rename Format_Name to Long_Table_Name for system name Table_Name;

-- Add text description to the file
Label on table table_Name
is 'Table description goes here---------------------->';

-- Add column headings to the fields
Label on column table_name (
longname is 'Heading 1---------->Heading 2---------->Heading 3---------->');
Add_User is 'Add User',
Add_Timestamp is 'Add Timestamp',
Add_Proc is 'Add Procedure',
Chg_User is 'Change User',
Chg_Timestamp is 'Change Timestamp',
Chg_Proc is 'Change Procedure',
Dlt_User is 'Delete User',
Dlt_Timestamp is 'Delete Timestamp',
Dlt_Proc is 'Delete Procedure');

-- Add column text to the fields
Label on column table_name (
longname text is 'Description-------------------------------------->');
Add_User text is 'Add user',
Add_Timestamp text is 'Add timestamp',
Add_Proc text is 'Add procedure',
Chg_User text is 'Change user',
Chg_Timestamp text is 'Change timestamp',
Chg_Proc text is 'Change procedure',
Dlt_User text is 'Delete user',
Dlt_Timestamp text is 'Delete timestamp',
Dlt_Proc text is 'Delete procedure');

Rick

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dave Murvin
Sent: Friday, October 29, 2010 11:27 AM
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

Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.