Using SQL Access

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 file(s) specified in the XML template(s) 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 v12 includes sample XML templates that you use to define the layout of data as required by your COBOL application. See Table 181. In developer’s 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’s 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 file(s) 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 v12 includes a command line utility called the Schema Executor, which is also referred to as SchemaExec.

Schema Executor performs the following actions:

•

parses the XML file(s) 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 v12 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 PSQL v12.

►

To Process an XML File with Schema Executor

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

1

Access a command prompt at the operating system.

2

Execute Schema Executor at the command line (see Table 181 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. Re-execute the utility with 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 182

Options for Schema Executor Utility

The file name of the XML schema that defines the layout of the data. Required option. See Step 1: Modify the Sample XML Templates.

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.

See also Creating a New Database with Schema Executor.

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.

See Log Messages.

Display command usage. Ignore all other options.

Note: The required options XMLfilename and databasename are positional and must appear in that order.

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.

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 183

Possible Error Codes in a CobolSchemaExec Log File

Property name attribute missing

Both MAINTABLE and VARIANTRECORDTABLES not supported

Occurs Table Name specified is Invalid

Occurs Count specified is Invalid

Occurs Mapping Index specified is Invalid

TableName - Duplicate TableName

FieldName - Duplicate FieldName

IndexName - Duplicate IndexName

Table name parameter is not specified

Identifier - contains invalid characters

Offset has to be a non-negative integer

Identifier length cannot exceed 20 characters

Precision has to be greater than zero

Invalid Precision specified for - FieldName

Scale cannot be greater Precision for - FieldName

Log and XML file names must be different

TableFilter - TableFilter cannot have more than 255 characters

FieldName is not a field of TableName

TableFilters should be defined for all REDEFINES Table or for NONE

Incorrect Parent Element

Identifier name identifiername should start with an alphabetic character

Identifier name identifiername cannot be a keyword

DataFile - doesn't exist at DatabasePath

OCCURS/REDEFINES Length must be a non-negative integer

Length of Btrievefilename cannot exceed 64 characters

No index specified for Parent Table - ParentTableName

Schemaexec completed successfully

Invalid value for command line argument argument

Value for Password cannot be specified without Login

Could not connect to DatabaseName

Could not create database DatabaseName in the dictionary path Databasepath

Could not create the specified DSN

Could not close the database databasename

Could not read data from XML file

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 PSQL v12. Therefore, you may have a few additional system tables (DDF files) to deploy.