IDMS/SQL News 1.2

Vol 1.2   Technical Information for IDMS Users    May 1992


Optimizer Technology of CA-DB/SQL scores very high points in an Industry Analysis. 42 features were considered by a study carried out by InfoDB Journal . CA-IDMS/SQL Optimizer is not only based on CA/DB Optimizer but also incorporates existing CA-IDM S technology to ensure OLTP performance on 370 platforms.


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.

In all, 9 databases - CA-DB, DB2, Informix, Ingres, Oracle, RDB, Sharebase, Sybase and Tandem - were evaluated against 42 features. CA-DB scored 25 Yes, 9 Partial and 8 No.
Ingres scored very well too. DB2 scored 16 Y, 9 P and 17 N. All others were far behind.The results are not surprising at all.

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.


DICTNAME, DBNAME, SEGMENT and
Default Dictionaries in R 12.0


DBTABLE consists of DBNAMES and is defined in OCF. Each DBNAME consists of one or more database SEGMENTS. Segment in turn contains one or more physical files/areas.

DBNAME was an 'optional' feature in 10.2, used only if the user had multiple databases or secondary dictionaries. In 12.0, DBNAME is always required and is closely associated with database SEGMENTs. Then there is the question of all 10.2 applications where DBNAMEs were not used. For this reason, 10.2 compatibility is provided at the top of DBTABLE by supporting subschema mapping. This will be helpful in the sense that no application program re-writing is required if no dbnames were used in 10.2.

(refer to DBTABLE display box below)

The first subschema mapping with IDMSNWK? is a special case which achieves two things in one shot. First it establishes the default dictionary as SYSDICT. ie if no dictname is given or DICTNAME ='', system will always use SYSDICT. Note that in R12, sys gen dictionary is always SYSTEM, which as you see is NOT the default dictionary.

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

Case 1.

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.

 

Helsinki News

European IDMS User Association Executive meeting was held in Helsinki on 24/04/92. CA was represented by Lars Ahlberg and Scott Corrigan, CA France (IDMS European Product Champion) among others. Wishlist for Europe, 91 was presented and out of 105 r equests from IDMS Customers 70% were favourably considered by CA. They are either already implemented in the new release or accepted for a future release.

CA-IDMS Release 12.0 GA

Release 12.0 GA tape will be available in September 92 timeframe. Sincework on IDMS 12.1 is already on the way, some technicians had mistakenly mixed up 12.1 beta tape with the GA availability of R12.0. In fact, some customers have plans to go into production with ESP tape in June-July period and these operations will allow CA to monitor IDMS in a production environment.

IUA-92 Dallas

IDMS User Association World Meeting was held in Dallas, April 5-9. The event was a great success with real life presentations from users, product demos and future directions from CA, speaches by industry gurus and analysts.

Future Database Events:

Oct 92 - Gothenburg, Sweden, Scandinavian IDMS Users Meeting (IUA)

10-20/1' 93 - San Diego

Information Management Conference (IMC). Users can choose among 3 groupings- IDMS, Tools, and Datacom .


Back to the Main Page

This page at http://www.reocities.com/idmssql/idms12.htm


IDMS/SQL is published on behalf of IDMS WatchDog Group, -Helsinki-Oslo for free circulation among IDMS Users Worldwide. IDMS/SQL News is not a CA publication. CA-IDMS/DB, CA-IDMS/DC and CA-ADS are registered trademarks of Computer Associates International Inc. CICS, IMS-DB/DC and DB2 are registered trademarks of IBM Corporation. Technical examples are only guidelines and modification might be required in certain situations and operating systems. Opinion expressed are those of the authors and do not represent the views of IDMS clients or related vendors. Permission is hereby granted to use or reproduce the information, only to IDMS Customers and Consultants, provided the material is distributed free of charge.