Vol 1.4   Technical Information for IDMSâ Users    December 1992

Nordic Character Support in IDMS/SQL

As every programmer in Finland and Scandinavia knows, our national characters ØÆÅ(ÖÄÅ) have been a major 'headache' whenever an application is handling names, addresses and similar info. IBM EBCDIC code doesn't recognize national characters and so Ø, Æ and Å are represented by at symbol (X'7C'), pound sign (X'7B') and dollar sign (X'5B'). Small øæå have their own hexa equivalents. The problem is that the selected hexa values don't sort naturally in Nordic Alphabets.

During past decades the problem has been solved using various methods; for instance in IDMS-DB, database procedures have been used to transform national characters to achieve desired collating sequence so that the indexes are sorted correctly. OLQ u sed CODETABLEs for sorting. Upper/Lower translate issue in ADS BUILTIN functions and OLM is another problem handled separately.

IDMS/SQL Option by default will collate national characters in the wrong order both in indexes and in SQL ORDER BY clause. SQL Option does not support database procedures.

We have been in contact with Doug Inkster in SQL Development and a solution has been proposed to support SQL Alternate Character Set. That is to have new translate tables in RHDCCODE for European Languages. The SQL Alternate Character Support is initiated by

'ALTER CATALOG
DEFAULT CHARACTER SET
<LANGUAGE>' command.

This must be done as the first command in an SQL Catalog, immediately after running TABLEDDDL, but before any user schemas are defined. All CHAR/ VARCHAR fields will be using the new character set, indexes will be built correctly , ORDER BY clause will honour 'ØÆÅ' (ÖÄÅ in S/SF).

This only works for SQL-defined tables. When SQL is used to access a network database, no translation is done. (Existing DB procedures will be honoured). Likewise, when a command joins an SQL table with network record, the SQL columns are decoded before being compared with corresponding network elements.

At the moment the solution doesn't cover SQL Catalog entities (table names and column names).

The SQL Alternate Character Set Support will be available after the IDMS Maintenance tape Genlevel 9212, that is the 1st quarter of '93. Future releases of IDMS / SQL may include SQL Alternate Character Set Support implemented at individual table columns. Eventually IDMS SQL
Option may support fieldproc, but the cost of fieldproc seems to be very heavy.

A Taste of SQL Catalog

OCF (Online Command Facility) is the interactive SQL in IDMS. All SQL DDL/DML can be executed under OCF. Since the Catalog itself is an SQL database, it is logical to try SQL against the Catalog.

A variety of queries are possible. The following is a fraction of what is possible under OCF.

Real output is edited to reduce the size. Catalog Tables are always defined in schema SYSTEM.

SELECT NAME FROM SYSTEM.SCHEMA

*+ NAME
*+ ----
*+ BOB
*+ CHWSCHM
*+ DB2TEST
*+ DEFAULT
*+ DEMOEMPL
*+ DEMOPROJ
*+ DEMOPROJ
*+ EMPSQL
. .. . .
*+ SYSCA
*+ SYSTEM
24 rows processed

The following SELECT lists the Schemas mapping to segment 'SQLTEMP'.

SELECT NAME, TYPE,AREA
FROM SYSTEM.SCHEMA
WHERE SEGMENT='SQLTEMP';

NAME TYPE AREA
DEFAULT R TEMPWORK
LENRO01 R TEMPWORK
KOHGE R TEMPWORK
DB2TEST R TEMPWORK
4 rows processed 

SCHEMA  PROG.    TABLE ACMODE
------  ------   --- ------ 
GKWEMP  GKWDSQL  EMPL   C 
GKWEMP  GKWDSQL  EMPL   C 
GKW     GKWDSQL1 EMPL   I

ACMODE gives the access strategy used for a particular SQL. (C=CALC,I=Index, A=Area Scan).

Tribute to PDP-8

"The AP (Associated Press) replaced distributed PDP-8s with centrally
located VAXclusters...)". (Computerworld,Sept 92 page 77). What makes this replacement a remarkable event is the fact that the "old" PDP-8s have been in production for a solid 25 years.

PDP-8 and PDP-11 were born in an era when the computers were big (really big) and the market was occupied by mainframes from IBM, CDC, UNIVAC and Burroughs. The word "PC" did not exist. With PDP series, Digital marked the beginning of minicomputer era in commercial dataprocessing. PDPs were great machines and it is quite amazing that a PDP-8 is still in production in the nineties, at a well known news bureau such as Associated Press.

UNIX and C fans may note that the first UNIX was written in C on a PDP-11 machine at Bell Lab.

In today's volatile hardware and software market, very often one sees that the lifetime of many fancy products can be counted by just one ¦hand. Many get replaced even before undergoing any production run at ¦all. An old software or hardware savin g millions may not make any ¦headlines, but they are the backbone of the industry.

SQL -based Engine?

SQL Implementation of Release 12.0 has been widely accepted by the IDMS customer base and the industry gurus as a true relational database on 370 platform. The old questions such as 'is it really relational' or 'is it a front-end to network' etc. are not heard anymore.

Having said that, one cannot ignore the fact that some 'myths' still exist on SQL as well as IDMS/DB. Pioneers like C.J.Date have been in the forefront to remove the SQL myths through various forums (1).

Here we look at a relational myth which has appeared recently in a Gartner Group report on "Relational Databases and CA-IDMS". The report has an entry of "N" for IDMS against a property which they call "SQL-based Engine"(2). Before trying to see if IDMS is eligible for Y/N, we need to make sure what exactly is an "SQL-based Engine".

What does anyone mean by an 'SQL-based engine'? If by that you mean there is, in addition to a physical engine which deals with block and record level I/O, there is a logical layer which deals with the selection criteria, the column selection e tc. (ie the Relational Data Management bit of DB2) - then IDMS conforms exactly to that architecture. In fact the HLDB layer was precisely what was missing in 10.2 to deal with SQL - the physical structures, by and
large, already existed .

Relational Database

Part of the confusion comes from the myth that in a relational database data is physically stored as tables. C.J.Date clarifies the whole point:
"The true state of affairs is as follows: A relational database is a database in which data is logically perceived as tables. Tables are logical data structure in a relational system, not the physical data structure. At the physical level,the system is free to use any or all of the traditional techniques - physical ordering, indexing, hashes, pointer chains... and so on- provided that it can map those structures only into tables at logical level."(1).

Origin of SQL

Structured Query Language originated as Structured English Query Language and was called SEQUEL in the beginning(3). SQL was not proposed by Dr Codd or not part of the Relational Model. SQL was originally developed as an end-user "query" tool agains t relational databases. Later SQL was enhanced to include complete DDL, DML and DCL.

In Date's own words "The SQL language consists of a set of facilities for defining, manipulating and controlling data in a relational database"(4). That is to say SQL has to be based on the relational database, not the other way! In other words, su ch an SQL which is sitting on top cannot be the basis for any low-level engines.

Several criticisms have appeared in the journals on the shortcomings of SQL not only by the critics but by the topmost advocates of relational databases (5,6). Talking about 'Fatal Flaws in SQL' Dr E.F.Codd says 'The criticisms of SQL in this articl e... are certainly not intended to be interpreted as criticisms of the relational approach to database management. SQL departs significantly from the relational model, and, where it does, it is SQL that falls short'. (6)

IDMS Implementation

At runtime an SQL request is processed by IDMSSQL,then the logical engine IDMSHLDB and then the physical engine consisting of DBMS, DBIO, LMGR. IDMSSQL and IDMSHLDB are used only by SQL implementation. Corresponding modules exist for CA-DB and DB2 (the names may not be same, Date has used Runtime Supervisor and Stored Data Manager)(7).What Gartner Group calls SQL engine is probably this HLDB module?

If we go further lower level, by the time a database request gets to the DBMS engine, there is no SQL or network DML. Each DBMS has its own way of storing the data. B+ tree indexing is one of the common methods employed by almost all relational DBMS s. IDMS, CA-DB etc also use hashing.

At the lowest level there is no "SQL", but only a physical engine. And it is fair to say the physical engine (IDMSDBMS) in Release 12.0 is shared by both network and relational implementations. Engine itself is neither SQL based nor CODASYL based.

Conclusion

We have seen that SQL and relational databases are not synonymous terms. SQL is operating at the conceptual level, whereas the DBMS engine is operating at the internal level. The simple truth is that the entity called SQL-based engine is non-existent and so cannot be the basis for IDMS or any other database.

References:

1. C.J.Date: Some Relational Myths Exploded Parts I and II 1984
2. Gartner Group: Relational Databases and CA-IDMS - Presentation at IUA Gothenburg Oct 92
3. D.D.Chamberlin and R.F.Boyce: "SEQUEL: A structured English Query Language" Proc ACM SIGMOD May 74
4. C.J.Date: A Guide to SQL Sandard 1988
5. C.J.Date: A critique of SQL Language - ACM SIGMOD Nov 74
6. E.F.Codd "Fatal Flaws in SQL" Datamation Aug 15, Sept 1, 1988
7. C.J.Date: Introduction to Database Systems (4th Ed) 1986

SQL OCF Correction

In the article on 'Self-training on SQL' (IDMS/SQL Vol 1.2 Page 3) it was given that in an OCF session, the DEFAULT schema can be set as 'SET SESSION QUALIFIER XYZ' This is not correct anymore. The correct syntax is

SET SESSION CURRENT SCHEMA XYZ;

A note on ÆØÅ (on IBM 370)

When referring to IBM 370 EBCDIC characters the letters ÆØÅ are really referring to IBM #@$ respectively. So ÅACFØTAT looks like $ACF@TAT in IDMS manuals and on US keyboards.

In Finland EBCDIC # = Ä (=Æ in N).

In hexadecimal,
X '7B' = Æ (Ä) X'C0' = æ (ä)
X '7C' = Ø (Ö) X'6A' = ø (ö)
X '5B' = Å X'D0' = å