How to Use SQL Web Connect › Create an SQL Schema to Access Network CA IDMS-Data

Create an SQL Schema to Access Network CA IDMS-Data

To enable SQL access to a network database it is necessary to create an SQL Schema. The SQL Schema is defined in the SQL Catalog and identifies:

• The network schema
• The database instance (optional)

No definitions other than the schema are stored in the SQL Catalog.

Note: Even if you do not wish to access your IDMS network data using SQL, we nevertheless recommend that you complete this step, as the example schema you create here is used further on in this scenario when connecting via JDBC or ODBC.

Follow these steps:

1. Bring up OCF, the CA IDMS online command facility.
2. Verify that you have the necessary rights to create the SQL Schema:
   • The CREATE privilege on the schema named in the statement.
   • The USE privilege on the non-SQL schema.
   • If you specify DBNAME, you must have USE privilege on the database; if you do not specify DBNAME or specify a value of NULL, you must have DBADMIN privilege on DBNAME SYSTEM.
3. Type an SQL CREATE SCHEMA statement that identifies the network schema and database instances as follows:

    CREATE SCHEMA schema-name
       FOR NONSQL SCHEMA
       nonsql-schema specification 
       DBNAME nonsql-database-name;
   

   Set the following parameters to appropriate values:

   • schema-name—Specifies the name of the schema being created. schema-name must be a 1- through 18-character name that follows the conventions for SQL identifiers. schema-name must be unique within the dictionary.
   • nonsql-schema specification—Identifies the non-SQL-defined schema to associate with the SQL schema.
   • nonsql-database-name—Identifies the database containing the data described by the non-SQL-defined schema. nonsql-database-name must be a segment name or a database name that is defined in the database name table. Omit this line if you want to access multiple physical instances with the same logical definition.

   For example, to create an SQL Schema against the network version of the Employee Demo Database, submit the following statement:

   CREATE SCHEMA EMPSQL
     	  FOR NONSQL SCHEMA
         APPLDICT.EMPSCHM V 100
     	  DBNAME EMPDEMO;
   

4. Submit the CREATE SCHEMA statement.

   All records in the schema are now automatically accessible as tables, and their elements are visible as columns.

5. Issue a SELECT statement using the newly created SCHEMA to test that SQL access is functioning correctly. For example, to retrieve all employees using the EMPSQL Schema created above, use the following code:

   SELECT * FROM EMPSQL.EMPLOYEE;
   

Note: For more information about SQL schemas and accessing non-SQL-defined databases, see:

• The description of the 'Create Schema' statement and the information about the FOR NONSQL SCHEMA clause contained within that description in the CA IDMS SQL Reference Guide.
• The 'Accessing Non-SQL Defined Databases' section in the CA IDMS SQL Programming Guide.