Creating and Managing Your Model › Creating a CA 2E Design Model › Implementing SQL

Implementing SQL

If you implement SQL, each model library list and associated job description must reference an SQL collection. Parameter SQLLIB creates and sets the name of the SQL collection.

If you accept the default option *DBFGEN, CA 2E looks at the value for the DBFGEN parameter and, if it is set to *SQL, creates the collection and builds a name from the model library name according to the following rules:

• If the model library name contains the characters "MDL," CA 2E drops these characters and replaces them with the "SQL." For example, if you name the model library "INVMDLR40,"CA 2E creates the name "INVSQLR40" for the SQL collection.
• If the model library name does not contain "MDL," CA 2E appends the characters "SQL" to the model name. For example, if the name of the model library is "INVNTRY," CA 2E creates the name "INVNTRYSQL" for the SQL collection.
• If the model name is seven to nine characters in length,CA 2E truncates the suffix. If the model name is ten characters or more in length,CA 2E sends an error message.

Implementing DDL

If you implement DDL, each model library list and associated job description must reference an SQL collection. Parameter SQLLIB creates and sets the name of the SQL collection.

If you accept the default option *DBFGEN, CA 2E looks at the value for the DBFGEN parameter and, if it is set to *DDL, creates the collection and builds a name from the model library name according to the following rules:

• If the model library name contains the characters "MDL," CA 2E drops these characters and replaces them with the "SQL." For example, if you name the model library "INVMDLR40,"CA 2E creates the name "INVSQLR40" for the SQL collection.
• If the model library name does not contain "MDL," CA 2E appends the characters "SQL" to the model name. For example, if the name of the model library is "INVNTRY," CA 2E creates the name "INVNTRYSQL" for the SQL collection.
• If the model name is seven to nine characters in length,CA 2E truncates the suffix. If the model name is ten characters or more in length,CA 2E sends an error message.

Note: Irrespective of the value of the YSQLFMT model value and if the generation mode is *DDL, the RCDFMT keyword is generated.

Limitations:

• The current implementation of the DDL generation mode is not valid for the following cases:
  • Access paths that have virtual fields
  • SPN access path
  • QRY access path
  • Multi-member files

  Workaround for Virtual Fields, SPN, and QRY Access Paths: If the earlier generation mode is *DDS, revert to it and regenerate the access path. You need not regenerate the functions that use this access path. If you want to have an SQL type database, regenerate the access path using *SQL generation mode. The functions using this access path must be regenerated.

  Workaround for Multi-Member Files: If you want to have more than one member for the access paths, revert to *DDS generation mode.

Note: If you want to change an access path, which is previously defined as *DDS with a MAXMBR compiler override, to *DDL, you must revert to *DDS generation mode and must remove the compiler override, and then change back to *DDL generation mode.