DB2 Naming Standards

You may also like...

2 Responses

  1. Raul Baron says:

    Hi Ember and congrats for your great blog.
    We agree to use the exact same standards as you mention, i.e. FKs for Foreign Keys, IX for indexes, TB for tables, UQ for Unique constraints, VW for views, etc. As you say it is an excellent practice to know which entity is responsible for a certain problem.
    As for database names our imagination declines since we use suffixes such as db2pro for production or pre for pre-production or test for test environments. We waste 3 positions (db2) since we only have DB2 in this customer, so there’s no need to say it’s a DB2 database πŸ™‚
    And as the instance name concerns, our creativity goes even worse. We use one server-one instance-one database for each environment but test and development (one instance and two databases). Again, no actual need to name instances other than the factory name. On the other hand we only have five databases to maintain for now.

    As to the old application (cobol + z/Linux DB2) it only has tables and indexes and the naming standard is very curious: each table name has a prefix of 4 chars indicating the business area plus 4 more chars that try to summarize what it is about. e.g. PARMUSUA means parametric table of users. Indexes are named just like the table but the 4th char is a number indicating only the index number. So you never know how many indexes a table has nor what is that index about. e.g. PAR3USUA. This is because we evolved from DB2/VM (that is, DB2 natively on z/VM) which had a table and/or index limitation of 8 chars. When we migrated into DB2 LUW we lazily kept that standard inherited from the z/VM period.

    I hope not to have written much too long πŸ™‚

    Best regards from Madrid

  2. Pavan Kristipati says:

    Ember — This is great. Co-incidentally I am used to following very similar naming standards. I use PK_ as a prefix for all Primary Key Index and U_ for Unique key index.

    Sometimes, if the application allows, I also would like to enforce environment name within the schema name. For example, a stage table in a DW database could be EDWSTGPR.. This naming convention tells me that this is a EDW database, stage schema and Production environment. The last two letters differ from environment to environment.. viz., QA, DV, IN etc.
    Best Regards.
    Pavan Kristipati.

Leave a Reply

Your email address will not be published. Required fields are marked *