RE: DB2 for i SQL naming conventions -- MIDRANGE-L

Regarding "Never double-quote a table or column name unless you hate
yourself and others."

For readability purposes and use within other SQL-based tools, we would like
to use UpperCamelCase. That requires double quoting the file and field
names. I certainly agree that having to put double quotes everywhere to
reference the files and fields is very frustrating. Are there other reasons
we will hate ourselves for going that path?

Thank you,

-Lee

-----Original Message-----

From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx
<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx> > On Behalf Of Tyler, Matt

Sent: Friday, August 6, 2021 6:33 PM

To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx
<mailto:midrange-l@xxxxxxxxxxxxxxxxxx> >

Subject: RE: DB2 for i SQL naming conventions

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know the
content is safe.

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

date: Fri, 6 Aug 2021 17:48:34 -0400

from: <lee_paul@xxxxxxxxxxxxxxxx <mailto:lee_paul@xxxxxxxxxxxxxxxx> >

subject: DB2 for i SQL naming conventions

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?