Components and Installation › Development Environment › SQL Collection

SQL Collection

CA 2E lets you use SQL in place of or along with DDS in data definition statements. Using SQL, you define tables, views, rows, and columns, rather than IBM i server physical files, logical files, records, and fields.

You can implement SQL at both the system level and the model level. If you implement SQL at the system level, all new models default to SQL. When you implement at the model level, all access paths and functions default to SQL. Within a model, you can also specify SQL for individual access paths and functions.

The i OS operating system contains the execution objects necessary to execute applications you generate with SQL. However, if you want to generate and compile CA 2E applications that use SQL, you must have IBM’s SQL/400 program products installed and the QSQL library loaded on your IBM i.

To use SQL in a model environment, SQL collections, equivalent to IBM i libraries, must be associated with the model library. The YCRTMDLLIB command can automatically create the SQL collection. This command includes the SQLLIB parameter that lets you create the collection for a particular model.

You can create SQL collections for an existing model with the Create SQL Library (YCRTSQLLIB) command. This command updates the YSQLLIB model value for the associated model. You then use the Change Model Value (YCHGMDLVAL) command to update the YDBFGEN model value to SQL.

See the following references for more information:

• For more information on the YCRTSQLLIB and YCHGMDLVAL commands, see the Command Reference Guide.
• For more information on the differences between earlier versions of CA 2E SQL implementations and current implementations, see the SQL Implementation appendix in the Administrator Guide.

For more information about IBM’s Structured Query Language, see your IBM documentation.

DDL Collection

CA 2E lets you use DDL in place of or along with DDS in data definition statements. You use DDL to define tables, indexes, rows, and columns, rather than IBM i physical files, logical files, records, and fields. There is no view generation when DDL is used as generation method.

You can implement DDL at both the system level and the model level. If you implement DDL at the system level, all new models default to DDL. When you implement at the model level, all access paths and functions default to DDL. Within a model, you can also specify DDL for individual access paths and functions.

The i OS operating system contains the execution objects necessary to execute applications you generate with DDL. However, if you want to generate and compile applications that use DDL, you must have IBM’s SQL/400 program products installed and the QSQL library loaded on your IBM i.

To use DDL in a model environment, an SQL collection, equivalent to an IBM i library, must be associated with the model library. The YCRTMDLLIB command can automatically create the SQL collection. This command includes the SQLLIB parameter which lets you create the collection for a particular model.

You can create an SQL collection for an existing model with the Create SQL Library (YCRTSQLLIB) command. This command updates the YSQLLIB model value for the associated model. You then use the Change Model Value (YCHGMDLVAL) command to update the YDBFGEN model value to DDL.

For more information about:

• The YCRTSQLLIB and YCHGMDLVAL commands, see the CA 2E Command Reference Guide.
• For differences between earlier versions of CA 2E SQL implementations and current implementations, see the appendix SQL/DDL Implementation in the Administration Guide.
• IBM’s Structured Query Language, see IBM documentation.

Note: In the earlier releases of CA 2E, it was only possible to create SQL/DDL related tables, views and indexes into SQL Collections. However, the access path generation process has now been modified to enable creation of SQL/DDL tables, views and indexes into normal libraries, in addition to SQL Collections. The SQL/DDL generation has now been modified to do the following

• The YSQLLIB model value can also hold a normal library (non-SQL collection).
• The user is given the option to decide whether to generate the hard coded value present in the YSQLLIB model value into the generated source, through the YSQLCOL model value.
• If the library/SQL collection is not generated into the source during generation (by virtue of YSQLCOL set to *NO), when a compile is submitted to create the SQL/DDL objects, the objects are created into the library/SQL collection specified for the YSQLLIB model value.