| Return to Previous Page |
IDMS is
still not listed among SQL databases, in spite of the
fact that :
- SQL
support came in 1992 with Release 12.0
- A
117 SQL Table application with ADS/O and SQL was
in production as early as 1993
- SQL
features in IDMS 12.0 was far better than the
same available in DB2 2.3 in 1994
- Optimization
in IDMS/SQL was coming from DB/VAX of Cullinet
which was rated very high by one of the DBMS
Magazines which compared databases like Ingres,
Oracle, Sybase and CA-DB/VAX
- Hashing
(CALC) was supported in IDMS/SQL but not in any
other relational databases
- All
through the 1980s and 90s most VAX/Unix based
databses had a limitation of 2 K page size.
- From
a 'modern' Web front end or Java front-end an
IDMS database can give far better performance and
scalability than any of the Unix based systems
available today!
The
main problem is the marketing (rather lack of it) and the
perception created in the industry. Over the years, the
battle was given up by the vendor, even though there was
no reason to. Even Cullinet's own old timers played
not-so-good role here, because of their obsession with
the performance of the network database. Advances in the
hardware and the way people use systems today, make most
of these concerns obsolete.
Here
once again we run through an SQL database creation using
IDMS/SQL 15.0. We do everything online using OCF - the
Online Command Facility in IDMS (~ISQL).
Here we
go:
| Step |
OCF 15.0 ONLINE IDMS NO ERRORS DICT=TESTDICTedit
|
| 1 |
-- THIS LINE IS A COMMENT
-- YOU CAN DE-COMMENT IT BY REMOVING '--''
-- DROP SCHEMA YOURID;
CREATE SCHEMA YOURID
DEFAULT AREA SQLDB-TEST.AREA ;
*+ Status = 0 SQLSTATE = 00000
|
| 2 |
CREATE TABLE YOURID.EUROPE
( LAND CHAR(15) NOT NULL,
CAPITAL CHAR(20) NOT NULL);
*+ Status = 0 SQLSTATE = 00000
CREATE UNIQUE INDEX IX1 ON YOURID.EUROPE (LAND);
*+ Status = 0 SQLSTATE = 00000
|
| 3 |
SET SESSION CURRENT SCHEMA YOURID;
INSERT INTO EUROPE VALUES('NORWAY','OSLO');
INSERT INTO EUROPE VALUES('SWEDEN','STOCKHOLM');
INSERT INTO EUROPE VALUES('FINALND','HELSINKI');
INSERT INTO EUROPE VALUES('DENMARK','KØBENHAVN');
INSERT INTO EUROPE VALUES('FRANCE','PARIS');
INSERT INTO EUROPE VALUES('ESTONIA','TALLIN');
|
| 4 |
SELECT * FROM EUROPE;
*+
*+ LAND CAPITAL
*+ ---- -------
*+ NORWAY OSLO
*+ SWEDEN STOCKHOLM
*+ FINALND HELSINKI
*+ DENMARK KØBENHAVN
*+ FRANCE PARIS
*+ ESTONIA TALLIN
*+
*+ 6 rows processed
|
| 5 |
-- SET SESSION CURRENT SCHEMA YOURID;
UPDATE EUROPE SET CAPITAL='COPENHAGEN'
WHERE LAND='DENMARK';
*+ Status = 0 SQLSTATE = 00000
*+ 1 row processed
SELECT * FROM EUROPE;
*+
*+ LAND CAPITAL
*+ ---- -------
*+ NORWAY OSLO
*+ SWEDEN STOCKHOLM
*+ FINALND HELSINKI
*+ DENMARK COPENHAGEN
*+ FRANCE PARIS
*+ ESTONIA TALLIN
*+
*+ 6 rows processed
|
| 6 |
-- SET SESSION CURRENT SCHEMA YOURID;
DELETE FROM EUROPE;
*+ Status = 0 SQLSTATE = 00000
*+ 6 rows processed
SELECT * FROM EUROPE;
*+
*+ No qualifying rows found
|
| 7 |
Add again:
-- THIS IS ALMOST A REPEAT OF MODULE DBA03
-- SET SESSION CURRENT SCHEMA YOURID;
INSERT INTO EUROPE VALUES('NORWAY','OSLO');
INSERT INTO EUROPE VALUES('SWEDEN','STOCKHOLM');
INSERT INTO EUROPE VALUES('FINALND','HELSINKI');
INSERT INTO EUROPE VALUES('DENMARK','COPENHAGEN');
INSERT INTO EUROPE VALUES('FRANCE','PARIS');
INSERT INTO EUROPE VALUES('ESTONIA','TALLIN');
|
| 8 |
-- NEXT CREATE A VIEW WITH CHECK OPTION
CREATE VIEW EU
AS SELECT * FROM EUROPE
WHERE LAND NOT IN ('NORWAY', 'BRAZIL', 'USA')
WITH CHECK OPTION;
*+ Status = 0 SQLSTATE = 00000
-- NOW SELECT (SEE IF NORWAY WHICH IS IN THHE ORIGINAL TABLE, LISTED?
SELECT * FROM EU;
*+
*+ LAND CAPITAL
*+ ---- -------
*+ SWEDEN STOCKHOLM
*+ FINALND HELSINKI
*+ DENMARK COPENHAGEN
*+ FRANCE PARIS
*+ ESTONIA TALLIN
*+
*+ 5 rows processed
|
| 9 |
-- NOW TRY TO INSERT BRAZIL USING THIS SPECIAL VIEW
INSERT INTO EU VALUES ('BRAZIL','BRASILIA');
*+ Status = -4 SQLSTATE = 23000 Messages follow:
*+ DB001023 T7910 C-4M321: Check constraint violation
|
| 10 |
-- WILL IT GO THRU ? WHY NOT
DROP TABLE EUROPE;
*+ Status = -4 SQLSTATE = 42607 Messages follow:
*+ DB002016 T7980 C-4M6003: References to table exist, Table:YOURID.EUROPE
|
| 11 |
-- SET SESSION CURRENT SCHEMA YOURID;
DROP TABLE EUROPE CASCADE; -- CASCADE WILL FORCE A DELETE NOW
*+ Status = 0 SQLSTATE = 00000
-- OPTIONALLY YOU CAN ALSO DROP THE SCHEMA
-- BY DECOMMENTING THE FOLLOWING LINE
DROP SCHEMA YOURID;
*+ Status = 0 SQLSTATE = 00000
|
Creating an IDMS/SQL Databse
online is just as simple as (if not simpler than) doing
the same on any of the popular Unix products. In fact OCF
allows you to list the table source 100% which I was not
able to do on many 'modern' systems. Schema can be
displayed with full syntax of all the tables. Extremely
easy to transport the source across environments.
| 1 Simple Table Display
|
DIS TABLE YOURID.EUROPE AS SYN
*+ Status = 0 SQLSTATE = 00000
CREATE TABLE YOURID.EUROPE
*+ DEFINITION TIMESTAMP 2005-09-20-10.26.59.791578
*+ DATE CREATED 2005-09-20-10.26.59.700271 BY TVG
*+ DATE LAST UPDATED 2005-09-20-10.26.59.700271 BY T51464
( LAND CHARACTER(15) NOT NULL,
CAPITAL CHARACTER(20) NOT NULL
)
IN SQLDB-TEST.AREA
*+ DEFAULT INDEX ON DBKEY
*+ IX1 UNIQUE INDEX ON ( LAND )
;
|
| 2 Table with all the details
|
DIS TABLE YOURID.EUROPE FULL AS SYN;
*+ Status = 0 SQLSTATE = 00000
CREATE TABLE YOURID.EUROPE
*+ DEFINITION TIMESTAMP 2005-09-20-10.26.59.791578
*+ DATE CREATED 2005-09-20-10.26.59.700271 BY TVG
*+ DATE LAST UPDATED 2005-09-20-10.26.59.700271 BY TVG
( LAND CHARACTER(15) NOT NULL,
CAPITAL CHARACTER(20) NOT NULL
)
IN SQLDB-TEST.AREA
*+ DEFAULT INDEX ON DBKEY
*+ IX1 UNIQUE INDEX ON ( LAND )
;
CREATE UNIQUE INDEX IX1
ON YOURID.EUROPE
*+ DATE CREATED 2005-09-20-10.26.59.791578
( LAND ASC )
UNCOMPRESSED
IN SQLDB-TEST.AREA
INDEX BLOCK CONTAINS 10 KEYS DISPLACEMENT IS 2 PAGES
;
|
| 3 Let's create one more table
|
CREATE TABLE YOURID.TEST (ID INT, NAME CHAR(10));
*+ Status = 0 SQLSTATE = 00000
|
| 4 The whole Schema can be
displayed with full syntax
|
DIS SCHEMA YOURID FULL AS SYN;
*+ Status = 0 SQLSTATE = 00000
CREATE SCHEMA YOURID
*+ DATE CREATED 2005-09-20-10.26.52.625510 BY TVG
*+ FOR SQL
DEFAULT AREA SQLDB-TEST.AREA
;
CREATE TABLE YOURID.EUROPE
*+ DEFINITION TIMESTAMP 2005-09-20-10.26.59.791578
*+ DATE CREATED 2005-09-20-10.26.59.700271 BY TVG
*+ DATE LAST UPDATED 2005-09-20-10.26.59.700271 BY TVG
( LAND CHARACTER(15) NOT NULL,
CAPITAL CHARACTER(20) NOT NULL
)
IN SQLDB-TEST.AREA
*+ DEFAULT INDEX ON DBKEY
*+ IX1 UNIQUE INDEX ON ( LAND )
;
CREATE TABLE YOURID.TEST
*+ DEFINITION TIMESTAMP 2005-09-20-10.30.47.484962
*+ DATE CREATED 2005-09-20-10.30.47.484962 BY TVG
*+ DATE LAST UPDATED 2005-09-20-10.30.47.484962 BY TVG
( ID INTEGER,
NAME CHARACTER(10)
)
IN SQLDB-TEST.AREA
*+ DEFAULT INDEX ON DBKEY
;
CREATE UNIQUE INDEX IX1
ON YOURID.EUROPE
*+ DATE CREATED 2005-09-20-10.26.59.791578
( LAND ASC )
UNCOMPRESSED
IN SQLDB-TEST.AREA
INDEX BLOCK CONTAINS 10 KEYS DISPLACEMENT IS 2 PAGES
;
|
|