Enhancements to Existing Features › Meaningful Names for SQL/DDL

Meaningful Names for SQL/DDL

CA 2E now lets you create SQL or DDL database with meaningful names, instead of the implementation or truncated model names. This feature makes it easier to work with the external interfaces such as DB2 and Web Query.

The YSQLVNM model value controls the transition to meaningful names for SQL or DDL database. For more information about the YSQLVNM model value, see the YSQLVNM topic in the CA 2E Administration Guide.

Note: For more information about how to move to meaningful names for SQL or DDL database, see the article How to Move to Meaningful Names for SQL or DDL Database.

Limitations:

• There is a limitation across the copy process in CA 2E. When a table is regenerated/recompiled with different naming convention (*SQL, *DDS, *LNG, *LNF, and *LNT), then copying process might fail in a few cases.

  The following table shows the areas where copy process works:

Note:

• Consider a table and its view that are created with YSQLVNM as *DDS or *SQL. When YSQLVNM is changed to *LNG and the table and view are regenerated/recompiled, the table and its view that were created with either of *DDS or *SQL naming convention would be deleted, and new table and its view would be created.
• To generate or regenerate a function with RLA code for DDL database, set the YSQLVNM model value to *DDS or *LNG or *LNT, or *LNF and set the YDDLDBA model value to *RLA.

The following documentation deliverables were updated for this feature:

• Administration Guide
• Building Access Paths Guide
• Building Applications Guide
• Command Reference Guide

Meaningful Name for Model Files with Valid System Name

For a table that has a valid system name (less than or equal to 10 bytes), the long name implementation fails. This is due to an IBM i limitation which does not allow the table name and the system name to be different from each other, if both the names are valid system names.

This limitation has been addressed by introducing a file level option, "Enhance SQL Naming" in the Edit File Details panel.

If you meet the following three conditions:

• Change the Enhance SQL Naming option on the Edit File Details panel to Y
• Set the model value YSQLVNM to *LNG or *LNT
• Model file name is a valid system name

Then, it generates the table with (underscores) "_"s and "TABLE" as suffix, so that the name of the table becomes more than 10 char long.

Examples:

• CUSTOMER is generated as CUSTOMER_TABLE along with its 2E implementation name.
• CUST is generated as CUST__TABLE along with its 2E implementation name.
• C is generated as C_____TABLE along with its 2E implementation name.

However, if a table that has a valid system name (less than or equal to 10 bytes in length), is generated with YSQLVNM model value set as *LNG or *LNT, and when you set the Enhance SQL Naming option on the Edit File Details panel to N, then the following error message is generated in the code:

YSQLVNM as *LNG or *LNT is not allowed for this table

Refer to the section "Long Name Scenarios" in the "Administration Guide"

The following documentation deliverables were updated for this feature:

• Building Access Paths Guide
• Building Applications Guide
• Defining a Data Model Guide
• Generating and Implementing Applications
• Tutorial Guide

Controlling Meaningful Names for Table and/or Fields

CA 2E now lets you generate meaningful names for Table and/or Fields, independently of each other, so you have the flexibility to choose one or both or neither, depending on your environment requirements.

The two new values for the YSQLVNM model value, which are introduced for this feature are as follows:

*LNF

Generates meaningful names for fields only.

*LNT

Generates meaningful names for Table only.

The following documentation deliverables were updated for this feature:

• Building Access Paths Guide
• Building Applications Guide
• Defining a Data Model Guide
• Generating and Implementing Applications
• Tutorial Guide