Introduction › SQL Collection

SQL Collection

CA 2E lets you use SQL in place of or with DDS in data definition statements. You use SQL to define tables, views, rows, and columns, rather than IBM i 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.
• Implement SQL 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 applications that use SQL, you must have:

• IBM’s SQL/400 program products installed.
• The QSQL library that is loaded on your IBM i.

To use SQL 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 SQL.

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 Implementation.
• IBM’s Structured Query Language, see your IBM documentation.

DDL Collection

CA 2E lets you use DDL in place of or 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.
• Implement DDL 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.
• The QSQL library that is 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.
• IBM’s Structured Query Language, see your IBM documentation.

Note: In 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 tasks:

• 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, when a compile is submitted to create the SQL/DDL objects, the objects are created into the library/SQL collection that is specified for the YSQLLIB model value.