IDMS/SQL - A Sample Session

OCF is the online tool for executing SQL Statements within IDMSDC. This is much more powerful than the TSO based SPUFI menus of DB2. One thing is that it is running under IDMSDC like all IDMS compilers - IDD, ADSO, MAPC, Sysgen etc . Secondly you have 100% access to the most powerful dictionary in the industry even today - IDD. So here we go:

  
One Last Time! V15 Enter Next Task Code:

OCF

OCF 15.0 ONLINE IDMS NO ERRORS DICT=TSTDICT

 
Next -- THIS LINE IS A COMMENT
-- YOU CAN DE-COMMENT IT BY REMOVING '--'
-- DROP SCHEMA YOURID;
CREATE SCHEMA YOURID
DEFAULT AREA G807SQL.TEMP ;
*+ Status = 0 SQLSTATE = 00000

Note: This is the only thing you need to depend on the system DBA. ie the AREA (=Tablespace) must be already defined in the IDMS control table known as DMCL and a physical file allocated and connected to this area.

 
  OCF 15.0 ONLINE IDMS NO ERRORS
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
 
  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');
 
  
-- SET SESSION CURRENT SCHEMA YOUR-ID; 
SELECT * FROM EUROPE;                  
*+                                     
*+ LAND             CAPITAL            
*+ ----             -------            
*+ NORWAY           OSLO               
*+ SWEDEN           STOCKHOLM          
*+ FINALND          HELSINKI           
*+ DENMARK          KØBENHAVN          
*+ FRANCE           PARIS              
*+ ESTONIA          TALLIN             
*+                                     
*+ 6 rows processed
 
  
-- SET SESSION CURRENT SCHEMA YOUR-ID; 
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
 
  DELETE FROM EUROPE;
*+ Status = 0 SQLSTATE = 00000
*+ 6 rows processed
SELECT * FROM EUROPE;
*+
*+ No qualifying rows found


 
  We populate again

-- 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');

 
  -- 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
 
  *+ Status = 0 SQLSTATE = 00000
-- NOW SELECT (SEE IF NORWAY WHICH IS IN THE ORIGINAL TABLE, LISTED?
SELECT * FROM EU;
*+
*+ No qualifying rows found
-- NOW TRY TO INSERT BRAZIL USING THIS SPECIAL VIEW
INSERT INTO EU VALUES ('BRAZIL','BRASILIA');
*+ Status = -4 SQLSTATE = 23000 Messages follow:
*+ DB001023 T8692 C-4M321: Check constraint violation

This is a way to implement domain integrity using views. Here the base table has no restrictions on the values. But the view forces a domian and if we are using the view to update the table, those constraints are applied by the DBMS on the table operations.

  
  -- WILL IT GO THRU ? WHY NOT
DROP TABLE EUROPE;
*+ Status = -4 SQLSTATE = 42607 Messages follow:
*+ DB002016 T8728 C-4M6003: References to table exist, Table:YOURID.EUROPE

 
  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
 
 

Date Time Arithmatic

Let's create a table first with a column as datatype TIMESTAMP

CREATE TABLE IDMSSQL.ORDER_HEAD 
( ORDER_ID 	      NUMERIC(12) NOT NULL, 
  ORDER_TYPE_PRIORITY     SMALLINT NOT NULL, 
  ACTION_TYPE_ID 	CHARACTER(3), 
  ORDER_DATE 	         TIMESTAMP, 
  STATUS 		SMALLINT NOT NULL, 
  INFO_CHG_DATE 	  TIMESTAMP 
) ; 
CREATE UNIQUE INDEX IDX_STATUS_ID 
ON IDMSSQL.ORDER_HEAD 
( STATUS ASC, 
ORDER_ID ASC, 
ORDER_TYPE_PRIORITY ASC );

Populate the table with one row:

INSERT INTO ORDER_HEAD VALUES (360,6,'EAB','2006-09-08-16.43.17.123456',
20, '2007-03-26-16.43.17.123456');

Datetime arithmatic is built into SQL. A casual look shows that the date functions in IDMS/SQL are much more intuitive than what is available in more popular Unix based databases. (Try these or similar commands in your Sybase or Oracle and see what you get)

SELECT ORDER_DATE, ORDER_DATE + 10 SECONDS FROM ORDER_HEAD
WHERE ORDER_ID IN (361 )
*+
*+ ORDER_DATE (EXPR)
*+ ---------- ------
*+ 2007-08-08-16.43.17.123456 2007-08-08-16.43.27.123456
*+
*+ 1 row processed

Current Timestamp

SELECT current timestamp FROM IDMSSQL.ORDER_HEAD WHERE ORDER_ID IN (361 );

CURRENT TIMESTAMP(FUNCTION)

2007-10-26-17.04.02.446921

Note: one needs a dummy table with one row or so, to get the current timestamp...this is due to the SQL syntax requiring a from table.

Some more examples:

UPDATE ORDER_HEAD SET ORDER_DATE =ORDER_DATE + 11 MONTHS         
  WHERE ORDER_ID IN (361 );                                      
*+ Status = 0        SQLSTATE = 00000                            
*+ 1 row processed                                               
 SELECT ORDER_DATE, ORDER_DATE + 10 SECONDS   FROM ORDER_HEAD    
  WHERE ORDER_ID IN (361 );                                      
*+                                                               
*+ ORDER_DATE                  (EXPR)                            
*+ ----------                  ------                            
*+ 2007-08-08-16.43.17.123456  2007-08-08-16.43.27.123456        
*+                                                               
*+ 1 row processed                                               
SELECT ORDER_DATE, ORDER_DATE + 10 MINUTES   FROM ORDER_HEAD     
 WHERE ORDER_ID IN (361 );                                       
*+                                                               
*+ ORDER_DATE                  (EXPR)                            
*+ ----------                  ------                            
*+ 2007-08-08-16.43.17.123456  2007-08-08-16.53.17.123456        
*+                                                               
*+ 1 row processed                                               
SELECT ORDER_DATE, ORDER_DATE + 10 DAYS      FROM ORDER_HEAD     
 WHERE ORDER_ID IN (361 );                                       
*+                                                               
*+ ORDER_DATE                  (EXPR)                            
*+ ----------                  ------                            
*+ 2007-08-08-16.43.17.123456  2007-08-18-16.43.17.123456        
*+                                                               
*+ 1 row processed
 
  BETWEEN - Be aware of the difference!

Make a note that in SQL, the clauses between 100 and 120 and between 120 and 100 do not mean the same. And they do give different results.

SELECT * FROM IDMSSQL.KOMMUNE                 
WHERE KOMMUNE_NR BETWEEN 100 AND 120          
*+                                            
*+ KOMMUNE_NR  KOMMUNE_NAVN                   
*+ ----------  ------------                   
*+        101  Halden                         
*+        103  Fredrikstad                    
*+        104  Moss                           
*+        105  Sarpsborg                      
*+        106  Fredrikstad                    
*+        111  Hvaler                         
*+        113  Borge                          
*+        118  Aremark                        
*+        119  Marker                         
*+                                            
*+ 9 rows processed                      
SELECT * FROM IDMSSQL.KOMMUNE           
WHERE KOMMUNE_NR BETWEEN 120 AND 100    
*+                                      
*+ No qualifying rows found