SQL Access for COBOL Applications
Providing SQL Access for COBOL Applications
This appendix includes the following sections:
Overview of PSQL Support for COBOL
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.
Restrictions
The following restrictions currently apply to providing SQL access for COBOL applications.
•OCCURS cannot be nested within OCCURS.
•OCCURS cannot be nested within partial REDEFINES.
•Partial REDEFINES cannot be nested within partial REDEFINES.
•Partial REDEFINES cannot be nested within OCCURS.
•Only one index can be defined for an OCCURS. Additional indexes cannot be defined for the items within the OCCURS.
•The only data types supported are those defined for the MicroKernel and Relational engines. The data types are described in the XML control file by using only the transactional data types. See
cobolschemaexec.xsd in Table
180 for a discussion of the XML control file. For a discussion of data types, see
PSQL Supported Data Types.
SQL Statements
The following table lists the use of SQL statements with data tables created from OCCURS, partial REDEFINES, or variable record layouts.
Table 179 Use of SQL Statements With COBOL Data Constructs
Statement | Use with OCCURs and partial REDEFINES | Use with Variable Record Layouts | Notes |
ALTER TABLE | No | No | |
CREATE INDEX | No | No | |
CREATE INDEX IN DICTIONARY | No | Yes | |
CREATE TRIGGER | No | No | |
DELETE | No | Yes | |
DROP TABLE | Yes | Yes | A DROP TABLE statement removes all of the entries from the system tables. The data file itself is not deleted or modified. Also, when you drop a main table, a message informs you to drop any dependent tables if any are detected. A dependent table depends on a main table and results from conditions such as an OCCURS that contains an index or from partial REDEFINES. Once you drop the dependent tables, you can drop the main table. |
INSERT INTO | No | No | |
UPDATE | Yes | Yes | An UPDATE statement cannot update a column on which a table filter has been defined. A table filter is a logical expression associated with a table. Table filters are defined as part of your metadata in the XML files. |
all other SQL statements listed in SQL Engine Reference. | Yes | Yes | |
Components
PSQL installs the following components to provide SQL access for COBOL applications.
Table 180 Components Installed to Provide SQL Access for COBOL Applications
Component | Purpose | Location1 |
w3cobolschemaexec100.dll | 32-bit library of routines used by
Schema Executor | Windows server: file_path\PSQL\bin\ |
w64cobolschemaexec.dll | 64-bit library of routines used by
Schema Executor | Windows server: file_path\PSQL\bin\ |
Linux: libpsqlcobolschemaexec100.so macOS: libpsqlcobolschemaexec100.dylib | 32-bit and 64-bit library of routines used by
Schema Executor | Linux server: /usr/local/psql/lib |
cobolschemexecmsgrb.dll | Message resource bundle used by 32-bit library of routines | Windows server: file_path\PSQL\bin\ |
w64cobolschemaexecmsgrb.dll | Message resource bundle used by 64-bit library of routines | Windows server: file_path\PSQL\bin\ |
Linux: libpsqlcobolschemaexecmsgrb.so macOS: libpsqlcobolschemaexecmsgrb.dylib | Message resource bundle used by 32-bit and 64-bit library of routines | Linux server: /usr/local/psql/lib |
cobolschemaexec.xsd | Control file (document type definition) used by
Schema Executor when processing XML files | Windows server: file_path\PSQL\schemas Linux or macOS server: /user/local/psql/schemas/ |
cobolschemaexec.log | Default logging file for messages produced by
Schema Executor when processing of XML files | Window server: file_path\PSQL\logs Linux or macOS server: /usr/local/psql/logs/ |
cobolschemaexec.exe | Utility that populates the system tables used by the Relational Engine to interpret the ISAM data as normalized SQL tables. Also referred to as Schema Executor. | file_path\PSQL\bin\ |
cobolschemaexec | Utility that populates the system tables used by the Relational Engine to interpret the ISAM data as normalized SQL tables. Also referred to as Schema Executor. | Linux or macOS installation: /usr/local/psql/bin/ |
SampleMainTable.xml | Sample XML template that defines data for a simple table. | Windows server and client: file_path\PSQL\samples\cobolschemaexec Linux or macOS installation: /usr/local/psql/samples/cobolschemaexec |
SampleMainWithOccurs.xml | Sample XML template used to define data that contains OCCURS constructs. | Windows server and client: file_path\PSQL\samples\cobolschemaexec Linux or macOS installation: /usr/local/psql/samples/cobolschemaexec |
SampleMainWithRedefines.xml | Sample XML template used to define data that contains REDEFINES constructs. | Windows server and client: file_path\PSQL\samples\cobolschemaexec Linux or macOS installation: /usr/local/psql/samples/cobolschemaexec |
SampleVariantRecord.xml | XML template used to define data that contains variable record layouts. | Windows server and client: file_path\PSQL\samples\cobolschemaexec Linux or macOS installation: /usr/local/psql/samples/cobolschemaexec |
A log file on a Windows client or a Linux client is optional and may be specified when Schema Executor is run | | Same as current directory if no path is specified. Otherwise, location depends on user-supplied path. |
1For default locations of PSQL files, see
Where are the PSQL files installed? in Getting Started with PSQL. |
Using SQL Access
Complete the following tasks to take advantage of SQL access:
1 Manually edit the appropriate XML template to describe the data layout.
2 Copy the data files specified in the XML templates to the database folder.
3 Execute the utility to populate the system tables used by the Relational Engine (use the XML to create normalized data).
4 Optionally, if you are a COBOL applications developer, ensure that you deploy any new system tables created by Schema Executor.
Step 1: Modify the Sample XML Templates
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.
►To Modify an XML Template
1 Open the XML template in a text editor.
2 Modify the XML as described in the file comments.
3 Save the modified template with a path and file name of your choosing.
Step 2: Copy the Data File Specified in the XML Template
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.
Step 3: Run the Schema Executor Utility
PSQL includes a command line utility called the Schema Executor, also referred to as SchemaExec.
Schema Executor performs the following actions:
•Parses the XML files that you manually edited.
•Populates existing system tables that the Relational Engine uses to interpret the data as normalized SQL tables (a database created with PSQL contains all of the required system tables to support SQL access)
•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.
►To Process an XML File with Schema Executor
1 Access a command prompt at the operating system.
2 Execute Schema Executor at the command line (see Table
180 for where this executable is installed by default).
Provide the required options, XMLfilename and databasename, and any desired optional options. See
Schema Executor Command Format.
If errors occur during the processing of the XML content, review the errors reported in the Schema Executor log file. See
Log Messages. Execute the utility with the corrected XML until no errors result from the processing.
Schema Executor Command Format
cobolschemaexec XMLfilename databasename [-s servername] [-u login_id] [-p password] [-i svr_loginid] [-c svr_password] [-l log_file] [ -h | -? ]
Table 181 Options for Schema Executor Utility
Option | Meaning |
XMLfilename | The file name of the XML schema that defines the layout of the data. Required option. See
Step 1: Modify the Sample XML Templates. |
databasename | The name of the PSQL database accessed by your application. Required option. If the database specified does not exist, the utility prompts for a path and file name. |
-s servername | The name or IP address of the server running the PSQL database engine. You may use “localhost” as the name if running SchemaExec on the same machine as the database engine. If servername is not specified, the local machine is assumed to be the server. |
-u login_id | The user name required to access a secure database. See
PSQL Security in Advanced Operations Guide for a discussion of the PSQL security models. |
-p password | The password required to access a secure database. See
PSQL Security in Advanced Operations Guide for a discussion of the PSQL security models. |
-i svr_loginid | The login name required to access the operating system on a remote machine. This option is required if SchemaExec is processing an XML file located on a remote server. |
-c svr_password | The password required to access the operating system on a remote machine. This option is required if SchemaExec is processing an XML file located on a remote server. |
-l log_file | Log file to use for messages produced during processing of the XML file. If you execute SchemaExec on a machine running the PSQL database engine, a default log is created automatically. You do not need to use the -l log_file option. The default log is named cobolschemaexec.log. If you execute SchemaExec on a client machine (a machine not running the PSQL database engine), you can specify a log file for the client machine. |
-h or -? | Display command usage. Ignore all other options. |
Note The required options XMLfilename and databasename are positional and must appear in that order.
Example Usage
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.
•Database already exists (with server running on local host):
cobolschemaexec file_path\PSQL\samples\cobolschemaexec\test.xml demodata\
•Database does not exist (with server running on local host):
cobolschemaexec file_path\PSQL\samples\cobolschemaexec\test.xml mytest
The utility prompts as follows:
CB103 : Could not connect to mytest
Do you want to create database (y/n) ?
If you press y, the utility prompts for a database path:
Please enter the 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.
•Database exists on a remote server:
cobolschemaexec file_path\PSQL\samples\cobolschemaexec\test.xml demodata -s TestMachine -i testuser -c testuser
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.
•Database does not exist on a remote server:
cobolschemaexec file_path\PSQL\samples\cobolschemaexec\test.xml mytest -s RemoteMachine -i testuser -c testuser
This example assumes that a user testuser (with password "testuser") exists on the remote machine (TestMachine) with administrative privileges.
The utility prompts as follows:
CB103 : Could not connect to mytest
Do you want to create database (y/n) ?
If you press y, the utility prompts for a database path:
Please enter the 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.
Creating a New Database with Schema Executor
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.
Log Messages
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.
Table 182 Possible Error Codes in a CobolSchemaExec Log File
Error Code | Description |
CB001 | Unknown error. |
CB002 | Property name attribute missing |
CB003 | Both MAINTABLE and VARIANTRECORDTABLES not supported |
CB004 | Occurs Table Name specified is Invalid |
CB005 | Occurs Count specified is Invalid |
CB006 | Occurs Mapping Index specified is Invalid |
CB007 | TableName - Duplicate TableName |
CB008 | FieldName - Duplicate FieldName |
CB009 | IndexName - Duplicate IndexName |
CB010 | Table name parameter is not specified |
CB012 | Identifier - contains invalid characters |
CB013 | Offset has to be a non-negative integer |
CB014 | Identifier length cannot exceed 20 characters |
CB016 | Precision has to be greater than zero |
CB017 | Invalid Precision specified for - FieldName |
CB018 | Scale cannot be greater Precision for - FieldName |
CB019 | Log and XML file names must be different |
CB022 | TableFilter - TableFilter cannot have more than 255 characters |
CB023 | FieldName is not a field of TableName |
CB024 | TableFilters should be defined for all REDEFINES Table or for NONE |
CB025 | Incorrect Parent Element |
CB028 | Identifier name identifiername should start with an alphabetic character |
CB029 | Identifier name identifiername cannot be a keyword |
CB050 | DataFile - doesn't exist at DatabasePath |
CB051 | OCCURS/REDEFINES Length must be a non-negative integer |
CB052 | Length of Btrievefilename cannot exceed 64 characters |
CB057 | No index specified for Parent Table - ParentTableName |
CB099 | Parser error |
CB100 | Schemaexec completed successfully |
CB101 | Invalid value for command line argument argument |
CB102 | Value for Password cannot be specified without Login |
CB103 | Could not connect to DatabaseName |
CB105 | Could not create database DatabaseName in the dictionary path Databasepath |
CB106 | Could not create the specified DSN |
CB108 | Could not close the database databasename |
CB109 | Could not read data from XML file |
CB110 | Could not drop the database |
Step 4: Optionally, Deploy the System Tables
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.
Example of How to Execute a Sample XML File
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.
1 Copy maintbl.mkd to the data file folder of the database to which you wish to connect.
For example, suppose that a database named test exists with a data file location of c:\data\test. Copy maintbl.mkd to c:\data\test.
2 Open a command prompt at the PSQL\bin\ directory.
3 Execute the following command at a DOS prompt:
cobolschemaexec file_path\PSQL\samples\cobolschemaexec\samplemaintable.xml test
4 On successful execution of Schema Executor, the table maintbl (as specified in the XML file) is created in the test database.
5 You man now perform SQL operations on table maintbl using PCC.
Additional Notes
This section provides notes pertaining to SELECT statements and table filters.
SELECT Statements
A SELECT query on an OCCURS table returns the following:
•Columns of the OCCURS table
•Column of the main table that comprises the mapping index
•OCCURS counter that indicates the number of occurrences of the OCCURS clause
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).
Table Filters
A table filter is a filter condition for a particular table. In the sample XML files it is referred to as TABLEFILTER.
•A TABLEFILTER may have an expression with left and right operands, both being column names. For example, Cust_Num = My_Cust_Num, where both Cust_Num and My_Cust_Num are column names.
Insert a space between the operands and the operator.
•If a constant value is used in the expression for a TABLEFILTER, the value must be specified within single quotes.
Example: Cust_Num = '100' (where Cust_Num is the column name)
•Use the following XML entities when specifying a TABLEFILTER in an XML file.
XML Entity | Used For |
< | less than (<) |
> | greater than (>) |
& | ampersand (&) (AND) |
" | double quotes (") |
' | single quotes (') |
Examples of Valid TABLEFILTER Usage
Cust_Num = '100' (equivalent to Cust_Num = 100)
Cust_Num < '100' (equivalent to Cust_Num < 100)
Cust_Num > '100' (equivalent to Cust_Num > 100)
Cust_Num <> '100' (equivalent to Cust_Num <> 100)
'a' = Category | Account_Num <= 'a123' (equivalent to 'a'=category OR account_num <= 'a123')
'a' = Category & Account_Num = 'a123' (equivalent to 'a' = category AND account_num = 'a123')
Cust_Num = My_cust_Num (where both the operands are column names)