Relational DBMS Optimizers

- Based on InfoDB Magazine
Nothing can help more in estimating the performance of a Relational Database Management System (RDBMS) than a thorough understanding of its optimizer.

This was the conclusion of a study conducted by David McGoveran, associate editor of InfoDB the leading journal for database users.
(InfoDB - Fall 1990).

Is the optimizer syntax driven or sensitive ? Does it use cost functions
and global/local statistics ?
Does it do a global optimization based on the whole database environment ?

In brief, the features were classified into:

General Features like interpreted or compiled, EXPLAIN facilities.
Access Method Support like order of operations, multiple sort algorithm
Index Support like use of partial indices, multiple indices, multi-table indices.
Statistics like table and index statistics, disk space, update statistics. Efficiency Features like saving of dynamic plans, dynamic
optimization. Artificial Intelligence Features like heuristics, self-learning, parallel and distributed processing support.

CA-DB had its origin in 'Mars' an original relational DBMS by ESVEL Inc. the company founded by alumni of System-R prototype (Gartner Group report SMS R-106-105 - Feb 14, 1990). IDMS-DB mainframe optimizer is an
enhanced version of CA-DB optimizer a nd is written in 'C'. 20 years of DBMS production expertise has been put into the Access Module Compiler (IDMSAMC) to realize high volume OLTP as well as adaptive query management.

In the words of Doug Inkster, CA Ottawa, " The SQL option of IDMS is composed of an extremely complex and sophisticated architecture. The operator tree structures used throughout the compilation and execution phases of IDMS are a powerful and flexible representation which are an integral part of the SQL implementation. Their use has allowed the
development of simple, yet very powerful algorithms for the compilation and execution of SQL DML statements in IDMS today."

System-ID in Sysgen

In the IDMS/DC Sysgen, there is a new parameter coded as 'system id is
SYSTnnnn'. This id is important in R12, and will be used by IDMS
internal or external security, CCI/DDS etc. System-id is used later in
'CREATE RESOURCE 'statement in Security Ca talog. Users must have signon
privilege to system-id..

Transaction Statistics more extensive in 12.0

PIB Nr: CI72603 (Target: COBOL/PL1 DB/DC)

User-written programs which request transaction statistics via an "ACCEPT TRANSACTION STATISTICS" command in DC COBOL or its equivalent in PL1 or assembler must be recompiled for IDMS release 12.0. The transaction statistics provided for release 12.0
are considerably more extensive than those available under 10.2, and as a result the tran- saction statistics block is larger.

There is no mechanism provided in 12.0 to request that only the 10.2-format transaction statistics be supplied in response to the command. As a result, the entire 12.0 statistics area gets transferred to user working storage. This will cause an overlay of anything in the 44 bytes immediately followin g a release 10.2-format transaction statistics area, with results varying according to what got overlaid. Recompiling affected programs using a release
12.0 precompiler and dictionary MACLIB will cause the release 12.0 version of the transa ction statistics area to be generated, thus preventing the problem.

Secondly, if you try to read the dictionary database, using IDMSNWK? subschema, then database defaults to DBNAME= SYSDICT. ie we always read the SYSDICT DML area. In the same way, for the user database EMPSCHM, if no dbname is specified, subschema EMPSS01 will always default to DBNAME= EMPDEMO. This is equivalent to setting 'DCUF SET DBNAME=EMPDEMO' or 'MOVE 'EMPDEMO' to DB-NAME' in ADS. The following discussion is based on 12.0 OLQ access against 1) employee database using EMPSS01 2) dictionary database using IDMSNWKA

DCUF SET DICTNAME ' ' DBNAME ' ' (or nothing is set)
OLQ SIGN ss empss01
OLQ 100021 00 Ready to retrieve data from subschema EMPSS01
OLQ 100022 00 Schema: EMPSCHM Version: 100

select * from department ! display

DEPARTMENT REPORT 05/05/92

DEPT-ID-0410 DEPT-NAME-0410

5300	 BLUE SKIES
5100 	BRAINSTORMING
2000 	ACCOUNTING AND PAYROLL
1000 	PERSONNEL
3100 	INTERNAL SOFTWARE
3200 	COMPUTER OPERATIONS
4000 	PUBLIC RELATIONS
1000 	EXECUTIVE ADMINISTRATION
5200 	THERMOREGULATION
END OF REPORT

Case 2.
OLQ SIGN ss empss01 dictname sysdict dbname empdemo
OLQ 100021 Ready to retrieve data from subschema EMPSS01
100022 Schema: EMPSCHM Version: 100
100023 Database name: EMPDEMO
100025 Dictionary name: SYSDICT

Note that signon message explicitly name the DB and DICT, but the result is exactly same as CASE 1.

select * from department ! display

Case 3.
OLQ SIGN ss empss01 dictname ' ' dbname empdemo

Here dictname is default dictionary, dbname is specified.

Case 4.
DCUF SET dictname sysdict dbname empdemo

OLQ SIGN ss empss01

Similar to Case 2, but 'DCUF' is used to set dict/dbnames. Cases 3 and 4 give exactly the same result as Case1 (=Case 2).

Case 5.1
sign ss idmsnwka
SIGNON will be rejected since IDMSNWKA subschema cannot be found in the
DEFAULT dictionary(SYSDICT).

Case 5.2
sign ss idmsnwka dictname sysdirl

100021 00 Ready to retrieve data from subschema IDMSNWKA
100022 00 Schema: IDMSNTWK
100025 00 Dictionary: SYSDIRL

GET FIRST 5 SEQ USER-047
OQ 098006 00 5 whole lines.....
(Users from SYSDICT DML area)

Case 5.3
sign ss idmsnwka dictname sysdirl dbname sysdict
100021 00 Ready to retrieve data from subschema IDMSNWKA
100022 00 Schema: IDMSNTWK
100023 00 Database name: SYSDICT
100025 00 Dictionary name: SYSDIRL
The result is exactly same as Case 5.2

Case 5.4
sign ss idmsnwka dictname sysdirl dbname system

Database name: SYSTEM
Dictionary name: SYSDIRL
GET FIRST 5 SEQ USER-047
Here the 'SYSTEM' DML area is read.

Case 5.5
sign ss idmsnwka dictname sysdirl dbname sysdirl

Database name: SYSDIRL
Dictionary name: SYSDIRL

The last one reads the SYSDIRL DML area. To summarize, Cases 5.3, 5.4 and 5.5 read different DML databases , or dictionaries, as we know them in an IDMS environment.

The sample DBTABLE DISPLAY in Release 12.0:

DBNAME *DEFAULT MATCH ON SUBSCHEMA IS OPTIONAL

SUBSCHEMA IDMSNWK? MAPS TO IDMSNWK? USING DBNAME SYSDICT

SUBSCHEMA IDMSCAT? MAPS TO IDMSCAT? USING DBNAME SYSDICT

SUBSCHEMA EMPSS01 MAPS TO EMPSS01 USING DBNAME EMPDEMO


DBNAME SYSDICT MATCH ON SUBSCHEMA IS OPTIONAL

SEGMENT APPLDICT
SEGMENT SYSMSG
SEGMENT SYSSQL

DBNAME SYSDIRL MATCH ON SUBSCHEMA IS OPTIONAL

SEGMENT SYSDIRL
SEGMENT SYSMSG

DBNAME SYSTEM MATCH ON SUBSCHEMA IS OPTIONAL
SEGMENT CATSYS
SEGMENT SYSMSG
SEGMENT SYSTEM
DBNAME EMPDEMO MATCH ON SUBSCHEMA IS OPTIONAL

SEGMENT EMPDEMO
(DCMT D DBN display 

IDD is enhanced to include SQL Catalog

Integrated Data Dictionary is enhanced to include SQL Catalog and Security Catalog. It is fair to say:
R12 IDD = 10.2 IDD + SQL Catalog (+ Security Catalog)

SQL Catalog can only be accessed using SQL statements (apart from the System Compilers). DML area can be accessed by IDMSNWKA as well as by IDMS/SQL.

SQL catalog is accessed from OCF, our interactive SQL component. SQL catalog can be System Catalog or User SQL Catalog. System Catalog contains DMCL, Segment and DBTABLE definitions. It is recommended that System Catalog be not used for creating use r database tables. User SQL Catalog should be used for defining SQL Schemas, tables and Views. Also if you are using SQL against network databases, the SQL mapping schema is defined here.

System SQL Catalog is necessary for all users - with or without SQL.

SQL Benefit for Network Databases

Release 12.0 support for SQL access against existing network databases provide some unexpected side benefits. One of them is the ability to access multiple network databases in the same program - ADS, COBOL or PL1.

SQL mapping is done with a single statement in OCF by creating an SQL schema for each network schema. Note that this is just a mapping and used only by SQL and has no effect on existing programs.

CREATE SCHEMA EMP for NONSQL SCHEMA EMPSCHM
VERSION 1 SEGMENT EMPSEG;

That's all. There is no need for any extra global schema or subschema. For example, if you want to access EMPLOYEE from EMPSCHM schema and POST_OFFICE from POSTSCHM, this can be done in a single statement.

SELECT * from EMP.EMPLOYEE E,
POST.POST_OFFICE P
where EMP_ID = 1023 and
E.POSTNR = P.POSTNR;

In OLQ 10.2, this was possible after signing on to the two subschemas. Major difference in R12.0 is that you can do this in a program, online and batch, not just in query. Also it can be incorporated into a DECLARE CURSOR SQL statement, for later processing using FETCH.

Above all, no subschemas are required. SQL access is done at a very low level by the DBMS. Optimizer will automatically create access module to be used at run time. So subschemas will not be used even against network databases, in SQL mode.

You may note that existing programs and schemas continue to function as they did in 10.2 with no change at all.

For a detailed study of SQL access ... see IDMS Expanded Page Vol 2.1.

Self-Training on SQL

A sample SQL database with 12 tables is installed as part of R12 base tape. One can use SQL Self Training Guide to test and learn SQL statements against this database.

Your Own Database:

Many users want to make their own test databases. Well, this is easy in IDMS implementation. One does not have to wait for system programmer to put entries into all kinds of tables and TSO etc. to start making a simple database. And above all 100% ac tivities are done in DC.

Allocate a test SEGMENT large enough to hold all test tables. (1000 pages good enough for 100s of tables). This should be in DMCL and formatted. This is a one time DBA job. Later users can make schemas and tables using this test space. No more DBA i ntervention is required. Once the initial work is done making a test database is only one command away from ENTER NEXT CODE! ie OCF - Online Command Facility. OCF takes you to the most powerful SQL facility in the industry today. We may point out that part of this power comes from the integration of OCF with IDD.

Everything you type in OCF can also be stored in IDD and executed directly from OCF or BCF..

Example:

CREATE SCHEMA CJD
DEFAULT AREA
TEST_AREA;
CREATE TABLE CJD.S ...
CREATE TABLE CJD.P ... etc.

INSERT INTO CJD.S VALUES
('S1','Parts','London'); 

Another person can do:

CREATE SCHEMA DBXY
DEFAULT AREA
TEST_AREA;

SET SESSION QUALIFIER DBXY;
CREATE TABLE DEPT.....
CREATE TABLE EMPL...

or in IDD or OCF

ADD MODULE CJD_TABLES
LANGUAGE IS OCF
MOD SOURCE FOLLOWS
CREATE SCHEMA ...
CREATE TABLE ...
CREATE INDEX...
ETC. ETC.
MSEND; 

Basically an AREA (=tablespace) is shared by all the schemas. In the initial stages, this will be extremely useful for individual programmers who want to try their SQL expertise. DBA need not create individual AREAS or FILES etc. And the programmer s need not spend time waiting for DMCL definitions and file allocation.