I always use long and short names for tables/view/indexes/columns. I follow the naming method for long names but not for short names. ALL names in my source are upper case and all SQL syntax is lower case. When the source creates an object it makes no difference case the system creates these as upper case entries. No mixed as is allowed unless you double-quote the name.
Never double-quote a table or column name unless you hate yourself and others. One caveat, is for work tables that get turned into CSVs. You can used the quoted column names as cell headings with spaces in them. Users like those. One additional problem with double-quoted names, is the catalogs look funny as the names are not listed with quotes but they are listed with spaces. If you use the catalogs to generate a list of column names for whatever, you have to manage for the fact a name might have spaces in it, which means always checking the name text value for space in between other words. A real pain.
If your column name turns blue in ACS RSS edit session, use another name, just in case. It's a possible conflict with a reserved keyword.
Create a master base data type table or data dictionary that defines sizes for things that you want consistent. This way if you use a cross reference tool you can quickly identity tables you need to investigate for changes. Its simple to achieve with a "create TABLE (LONG_COL1 for column COL1...) as select GEN_COL1 from MASTER_TABLE_REF...) with no data rcdfmt ..." . One down side is you have to move identity column definitions to alter table statements.
OR get a SQL DDL tool to create SQL DB objects source for you.
We just mix DDS and DDL (PF and SQL member types). All database definition objects go into QDDSSRC. All other SQL source go in other source members. We use LM(i) to manage these objects.
We don't implement constraints other than unique indexes.
Other things to consider are the short names (system names); you have to have them. If you don't specify a short name the system will make them up for you. We use an old pattern of APPITMTY, where APP is the app code, ITM is the particular object name and TY is the object type. So a table could be ACPMASTB for Accounts Payable Master Table. We then replace the TB in the name for a pattern for index and views like APCMASI0 or ACPMASV0. We also are starting to implement temporal tables and these need a companion table to keep the changes. We name them like the master table but with HS appended to the end of the name. So our base name size is eight characters and with history tables we can use up the last two for the history designator.
-Matt
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Friday, August 6, 2021 4:04 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: DB2 for i SQL naming conventions
UPPERCASE_WITH_UNDERSCORES
Is really the only option..
You can't use Mixed case in Db2 for i _UNLESS_ you double quote the names
so
create "My_Table"
means you always need
select * from "My_Table"
whereas
create MY_TABLE
or create my_table
means you can use
select * from my_table
select * from MY_Table
select * from My_Table
ect...
Charles
On Fri, Aug 6, 2021 at 3:54 PM <lee_paul@xxxxxxxxxxxxxxxx> wrote:
When creating Tables, Indexes, Views, Functions, or other SQL-based
objects on the IBM i, what do you use for your naming conventions?
Object naming for multiple word separation: Do you use UpperCamelCase
or UPPERCASE_WITH_UNDERSCORES or Upper_Camel_Case_With_Underscores?
Constraint Naming: What do you name your different constraints?
Where do you store your SQL Source? QSQLSRC?
What other standards and conventions should I be thinking about when
creating SQL objects on the IBM i?
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit:
https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinf
o/midrange-l__;!!O6xM9Yim9Yk!vX4LrsF750JLDCzcQ9xM9ACfDcLQkaDYS4jHWTt22
0EMsk6VgMaOr8VduR3YcrJUkETJ$ or email:
MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.com/v3/__https://archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!vX4LrsF750JLDCzcQ9xM9ACfDcLQkaDYS4jHWTt220EMsk6VgMaOr8VduR3Ycr2gijxS$ .
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://urldefense.com/v3/__https://amazon.midrange.com__;!!O6xM9Yim9Y
k!vX4LrsF750JLDCzcQ9xM9ACfDcLQkaDYS4jHWTt220EMsk6VgMaOr8VduR3YcgzGRbcF
$
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!vX4LrsF750JLDCzcQ9xM9ACfDcLQkaDYS4jHWTt220EMsk6VgMaOr8VduR3YcrJUkETJ$
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.com/v3/__https://archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!vX4LrsF750JLDCzcQ9xM9ACfDcLQkaDYS4jHWTt220EMsk6VgMaOr8VduR3Ycr2gijxS$ .
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://urldefense.com/v3/__https://amazon.midrange.com__;!!O6xM9Yim9Yk!vX4LrsF750JLDCzcQ9xM9ACfDcLQkaDYS4jHWTt220EMsk6VgMaOr8VduR3YcgzGRbcF$
midrange.com
