The Zen 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 Zen 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 178 for a discussion of the XML control file. For a discussion of data types, see Zen 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 177 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.
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
Zen includes sample XML templates that you use to define the layout of data as required by your COBOL application. See Table 178. 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
Zen 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 Zen 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 Zen prior to the current version.
1 Access a command prompt at the operating system.
2 Execute Schema Executor at the command line (see Table 178 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.
The name of the Zen database accessed by your application. Required option. If the database specified does not exist, the utility prompts for a path and file name.
The name or IP address of the server running the Zen 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 Zen Security in Advanced Operations Guide for a discussion of the Zen security models.
-p password
The password required to access a secure database. See Zen Security in Advanced Operations Guide for a discussion of the Zen 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 Zen 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 Zen database engine), you can specify a log file for the client machine.
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.
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:
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 180 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 table name
CB008
FieldName - Duplicate field name
CB009
IndexName - Duplicate index name
CB010
TableName parameter is not specified.
CB012
Identifier contains invalid characters.
CB013
Offset has to be a nonnegative 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 than precision for FieldName.
CB019
Log and XML file names must be different.
CB022
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 nonnegative integer.
CB052
Length of Btrievefilename cannot exceed 64 characters.
CB057
No index specified for ParentTableName
CB099
Parser error
CB100
Schemaexec finished 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 Zen 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\Zen\samples\cobolschemaexec. For default locations of Zen files, see Where are the files installed? in Getting Started with Zen.
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 Zen\bin\ directory.
3 Execute the following command at a DOS prompt:
cobolschemaexec file_path\Zen\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 ZenCC.
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
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)