This appendix includes the following sections:

The PSQL Relational Engine includes support for COBOL OCCURS clauses, partial REDEFINES, and variable record layouts.

A partial REDEFINES clause identifies a portion of the data within a record (such as a 05 level within a 01 level). A variable record layout is also referred to as a REDEFINES because the entire record is being redefined. To avoid confusion with terminology, this topic refers to partial REDEFINES and to variable record layouts.

You do not need to change your COBOL application to take advantage of the SQL access.

You enable SQL access by describing the handling of data in your application to the PSQL Relational Engine. In developer terms, you define the metadata to the Relational Engine.

Note that this topic applies only to COBOL applications that include OCCURS, partial REDEFINES, or variable record layouts.

The following restrictions currently apply to providing SQL access for COBOL applications.

The following table lists the use of SQL statements with data tables created from OCCURS, partial REDEFINES, or variable record layouts.

PSQL installs the following components to provide SQL access for COBOL applications.

Complete the following tasks to take advantage of SQL access:

PSQL includes sample XML templates that you use to define the layout of data as required by your COBOL application. See Table 180. In developer terms, you describe your metadata in the XML files.

Copy the data files specified in the XML file to the data file location of the database before you run Schema Executor. The database is the one to which you need to add the tables.

For example, you want to add a table (specified in the XML as mytable.mkd) to a database test that has its data files under c:\data\test. Copy the data file mytable.mkd to c:\data\test before you run Schema Executor.

PSQL includes a command line utility called the Schema Executor, also referred to as SchemaExec.

Schema Executor performs the following actions:

See also Example of How to Execute a Sample XML File.

cobolschemaexec XMLfilename databasename [-s servername] [-u login_id] [-p password] [-i svr_loginid] [-c svr_password] [-l log_file] [ -h | -? ]

The following examples illustrate usage of Schema Executor.

For default locations of PSQL files, see Where are the PSQL files installed? in Getting Started with PSQL.

The utility prompts as follows:

If you press y, the utility prompts for a database path:

Provide an existing path or the utility returns an error. Ensure that the database file (for example, a .MKD file) being used in the XML file is available in the path.

This example assumes that a user testuser (with password "testuser") exists on the remote machine (TestMachine) with administrative privileges, and that the database file being used in the XML file is available in the data file directory of the database on the remote machine.

This example assumes that a user testuser (with password "testuser") exists on the remote machine (TestMachine) with administrative privileges.

The utility prompts as follows:

If you press y, the utility prompts for a database path:

Provide an existing path or the utility returns an error. Ensure that the database file (for example, a .MKD file) being used in the XML file is available in the path.

If the utility option databasename specifies a database that does not exist, Schema Executor prompts you whether to create a new database. If you specify “yes,” the utility prompts for a location of the new database. The location (path and folder name) must already exist for Schema Executor to create the database.

Note that Schema Executor also expects to find the data files for databasename in the default folder. The utility informs you if it finds no data files. Manually copy the data files to the default folder and run Schema Executor again to process the XML.

This section lists the codes that may appear in a log file after processing an XML file with Schema Executor.

The success code is CB100 : Schemaexec completed successfully.

The following table lists the error codes.

If you are a COBOL applications developer, ensure that you deploy all of the system tables with your application. Such deployment is nothing new and is mentioned only because you may have additional system tables. For example, Schema Executor creates additional system tables and populates them if you run the utility against a database created with a version of PSQL prior to the current version. Therefore, you may have a few additional system tables (DDF files) to deploy.

The sample XML and data files are provided under file_path\PSQL\samples\cobolschemaexec. For default locations of PSQL files, see Where are the PSQL files installed? in Getting Started with PSQL.

To execute the XML file SampleMainTable.xml using Schema Executor perform the following steps.

This section provides notes pertaining to SELECT statements and table filters.

A SELECT query on an OCCURS table returns the following:

For example, if you perform the query SELECT * FROM FIELD for the tables created by the execution of Schema Executor on the sample XML file

file_path\PSQL\samples\cobolschemaexec\ SampleMainWithOccurs.xml

then the utility returns columns Id, OccursCounter, Field_1, Field_2, and Field_3.

A SELECT query on a REDEFINES table returns all of the columns of the parent table and the columns of the REDEFINES table.

For example, if you perform the query SELECT * FROM Redefined_group for the tables created by the execution of Schema Executor on the sample XML file

file_path\PSQL\samples\ cobolschemaexec\SampleMainWithRedefines.xml

then the utility returns columns Id, Account_Num, Category, Redef_Struct_Num (all columns of the parent table), and Redefined_field_1 (column of the REDEFINES table).

A table filter is a filter condition for a particular table. In the sample XML files it is referred to as TABLEFILTER.