Vol 1.4 Technical Information for IDMSâ Users December 1992
DB/DC GA Tape (9208) is released for MVS. With this, more
and more customers are in a position to move to Release
12.0. The VSE version is at ESP . With 90% and above code
common to all operating
systems, VSE/VM/BS2K/Fujitzu versions share the same GA code with MVS. R12.0 is installed at above 500 sites worldwide now.
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.
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.
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.
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
|The next is very special. There is
a table called SYSTEM.SYNTAX where the syntax of all
views, and tables defined with CHECK option are available.
SELECT SYNTAX FROM SYSTEM.SYNTAX WHERE
SCHEMA = 'DEFAULT' AND TABLE = 'TESTVIEW'
The output follows:
CREATE VIEW TESTVIEW (ID,NAME)AS SELECT DEPT_ID,DEPT_NAME
ORDER BY 1, 2
The next query lists the tables defined under schema = 'DEFAULT'
SELECT NAME FROM SYSTEM.TABLE WHERE SCHEMA = 'DEFAULT'
*+ 2 rows processed
Now we go and browse the ACCESS_PLAN
SELECT * FROM DEFAULT.ACCESS_PLAN
The listing is too large to show here. An edited version follows:
Note that ACCESS_PLAN is a special table with pre-defined columns and is defined by user or automatically defined by the system. It is not part of the Catalog.
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
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.
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".
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.
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
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.
1. C.J.Date: Some Relational Myths Exploded Parts I
and II 1984
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).
Back to the Main Page