Workshop 2002 14 17 October 2002 CAIDMS DML

Workshop 2002 14 -17 October 2002 CA-IDMS DML SQL vs CODASYL Kay Sussmann Consultant IUA Board of Directors 1

TOPICS § SQL Against Non-SQL (Network) Defined Databases § SQL vs CODASYL Terminology § SQL PROGRAMMING § CODASYL (Non-SQL) PROGRAMMING § SQL Component Display 2

SQL Against Non-SQL Defined Databases Access to non-SQL, or Network, defined databases using SQL DML or dynamic SQL statements – SQL Option required 3

SQL vs CODASYL Terminology SQL Term non-SQL (CODASYL) Term SCHEMA TABLE COLUMN ROW RECORD ELEMENT or FIELD RECORD OCCURRENCE VIEW REFERENTIAL CONSTRAINT SET – chained or user-owned index INDEX SET – system-owned index CALC Record LOCATION MODE IS CALC 4

SQL SCHEMA For Non-SQL SCHEMA § Define a SQL SCHEMA in the Catalog to point to non-SQL SCHEMA in Dictionary CREATE SCHEMA schema-name FOR NONSQL SCHEMA nonsql-schema-name dictname. VERSION vers-nr DBNAME dbname EXAMPLE: CREATE SCHEMA EMPSQL FOR NONSQL SCHEMA APPLDICT. EMPDEMO VERSION 100; 5

SQL TABLE for CODASYL RECORD § SQL TABLE name is same as CODASYL RECORD name • If RECORD name includes hyphens, ie EMPLOYEE-REC, enclose in quotes • Record ELEMENTs are converted to Table COLUMNs • Record Element Name transformed to Column Name by converting hyphens (-) to underscores (_) 6

SQL COLUMNs For CODASYL ELEMENTs § Record Elements occurring a fixed number of times are represented by multiple columns RECORD ELEMENT: TABLE COLUMNS: MONTH_01 MONTH_02 MONTH_03. . MONTH_11 MONTH_12 PIC XX OCCURS 12 TIMES. CHAR(2); CHAR(2); 7

SQL COLUMNs For CODASYL ELEMENTs § PICTURE and USAGE converted to Data Type PICTURE and USAGE SQL DATA TYPE X(n) A(n) G(n) S 9(n)V(n) S 9(4) S 9(8) S 9(n) n>8 CHAR(n) GRAPHIC(n) NUMERIC(p, s) DECIMAL(p, s) SMALLINT INTEGER LONGINT DISPLAY GRAPHIC DISPLAY COMP-3 COMP 8

SQL COLUMNs For CODASYL ELEMENTs § SQL does not support: • • Group Elements FILLERs Condition Names (Level 88) REDEFINES and elements subordinate to a REDEFINES • Variably occurring elements and subordinate elements (OCCURS DEPENDING ON) • Some USAGE BIT definitions 9

SQL Support in Non-SQL SCHEMA SET statement: OWNer is record-name <dbkey-specification(s)> PRImary KEY is system-owned-index-name CALc NULl MEMber is record-name <dbkey-specification(s)> FOReign KEY is element-name NULlable ( element-name ) NULlable NULl 10

UPDATE STATISTICS § Examines and updates definitions in a non -SQL defined SCHEMA with statistical information in dictionary • SCHEMA AREA – actual number of pages • SCHEMA RECORD – actual number of record occurrences § Used to determine optimal access strategy for processing SQL statements 11

TABLE PROCEDURES § Allow processing of non-SQL defined data in a relational way even if the data does not conform to the rules of SQL • Process complex structures such as bill -of-materials • Access non-SQL data which is not SQL compliant 12

SQL vs CODASYL SQL DML VERB CODASYL DML VERB SELECT OBTAIN UPDATE DELETE INSERT MODIFY ERASE STORE PURPOSE Retrieve data and move into program Change data Remove data Add data 13

SQL vs CODASYL SQL RELATIONAL COMMAND MODULE (RCM) ACCESS MODULE (AM) CODASYL SUBSCHEMA 14

SQL DECLARE SECTION WORKING-STORAGE SECTION. . EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 <host-variable(s)> EXEC SQL END DECLARE SECTION END-EXEC. . EXEC SQL BEGIN DECLARE SECTION END-EXEC SQL INCLUDE TABLE <table-name> END-EXEC SQL END DECLARE SECTION END-EXEC. 15

SQL DECLARE CURSOR. . . *** DECLARE CURSORS EXEC SQL DECLARE CURSOR cursor-name CURSOR FOR SELECT col-name(s) FROM table-name(s) WHERE selection-criteria ORDER BY sort-specification END-EXEC. . 16

SQL COMMUNICATIONS BLOCK SQLCA – SQL Communications Block § Returns information regarding the success or failure of an SQL request § SQLCODE is tested for return code type values 17

SQL PROGRAMMING PROCEDURE DIVISION. . EXEC SQL WHENEVER SQLERROR GO TO SQL-ERROR END-EXEC SQL WHENEVER NOT FOUND GO TO NOT-FOUND END-EXEC. 18

SQL PROGRAMMING § § § Implicit or explicit CONNECT TO OPEN CURSOR FETCH CURSOR CLOSE CURSOR COMMIT (CONTINUE) RELEASE 19

SQL PROGRAMMING PROCEDURE DIVISION. . EXEC SQL WHENEVER SQLERROR GO TO SQL-ERROR END-EXEC SQL CONNECT TO : host-variable END-EXEC. 20

SQL PROGRAMMING. . . EXEC SQL OPEN CURSOR cursor-name END-EXEC. . 21

SQL PROGRAMMING. . . EXEC SQL FETCH cursor-name INTO options END-EXEC. IF SQLCODE = 100 EXEC SQL CLOSE cursor-name END-EXEC GO TO label. . 22

SQL PROGRAMMING. . . 999 -END-OF-JOB. EXEC SQL COMMIT RELEASE END-EXEC. GOBACK. 23

Non-SQL PROTOCOL ENVIRONMENT DIVISION. . IDMS-CONTROL SECTION. PROTOCOL. MODE IS mode IDMS-RECORDS option. . 24

Non-SQL SCHEMA SECTION DATA DIVISION. SCHEMA SECTION. DB subschema-name WITHIN schema-name VERSION version-nr. . 25

Non-SQL COPY IDMS WORKING-STORAGE SECTION. . COPY IDMS RECORD record-name options. . 26

Non-SQL COMMUNICATIONS BLOCK SUBSCHEMA-CTRL – non-SQL Communications Block § Returns information regarding the success or failure of a DML request § ERROR-STATUS is tested for return code type values 27

Non-SQL PROGRAMMING § § BIND READY OBTAIN FINISH 28

Non-SQL PROGRAMMING PROCEDURE DIVISION. . 000 -HOUSEKEEPING. BIND RUN-UNIT. BIND record-name. READY. . 29

Non-SQL PROGRAMMING. . . OBTAIN NEXT record-name WITHIN area-name. IF ERROR-STATUS = ‘ 0307’ GO TO label. . 30

Non-SQL PROGRAMMING. . . 999 -END-OF-JOB. FINISH. GOBACK. 31

DISPLAY Support for SQL Components § DISPLAY and/or PUNCH ACCESS MODULE, CALC KEY, CONSTRAINT, INDEX, Table Procedure KEY, SCHEMA, TABLE PROCEDURE, VIEW § DISPLAY ALL 32

DISPLAY SQL SCHEMA DISPLAY SCHEMA schema-name FULl ALL FULL, or ALL, option reports the definition for the named SQL SCHEMA and all definitions (TABLEs, CALC KEYs, INDEXes, etc. ) related to the SCHEMA. 33

DISPLAY SQL TABLE DISPLAY TABLE table-identifier FULl FULL option reports the syntax for the named SQL TABLE and all definitions (CALC KEYs, INDEXes, and CONSTRAINTs) associated with the TABLE. 34

DISPLAY SQL TABLE DISPLAY TABLE table-identifier LIKe RECord LIKE RECORD option reports the syntax for the named SQL TABLE in IDD format with COBOL elements. 35

DISPLAY SQL TABLE DISPLAY TABLE DEMOPROJ. CONSULTANT AS SYNTAX; *+ Status = 0 SQLSTATE = 00000 CREATE TABLE DEMOPROJ. CONSULTANT *+ DEFINITION TIMESTAMP 2000 -03 -14 -08. 45. 394012 *+ DATE CREATED 2000 -03 -14 -08. 45. 33. 650233 BY EXG *+ DATE LAST UPDATED 2000 -03 -14 -08. 45. 39. 383384 BY EXG ( CON_ID UNSIGNED NUMERIC(4) NOT NULL, CON_FNAME CHARACTER(20) NOT NULL, CON_LNAME CHARACTER(20) NOT NULL, PROJ_ID CHARACTER(10), START_DATE NOT NULL, SS_NUMBER UNSIGNED NUMERIC(9) NOT NULL, RATE UNSIGNED DECIMAL(7, 2), ) IN PROJSEG. PROJAREA *+ DEFAULT INDEX ON DBKEY ; 36

DISPLAY SQL TABLE DISPLAY TABLE DEMOPROJ. CONSULTANT LIKE RECORD AS SYNTAX; *+ Status = 0 SQLSTATE = 00000 ADD RECORD NAME IS CONSULTANT RECORD NAME SYNONYM IS CONSULTANT LANGUAGE IS SQL COMMENT 'Record built from SQL TABLE DEMOPROJ. CONSULTANT'. 03 CON-ID PIC 9(4). 03 CON-FNAME PIC X(20). 03 CON-LNAME PIC X(20). 03 PROJ-ID PIC X(10). 03 PROJ-ID-I PIC S 9(8) USAGE COMP. 03 START-DATE PIC X(10). 03 SS-NUMBER PIC 9(9). 03 RATE PIC 9(5)V 9(2) USAGE COMP-3. 03 RATE-I PIC S 9(8) USAGE COMP. 37

DISPLAY SQL VIEW DISPLAY VIEW view-identifier LIKe RECord LIKE RECORD option reports the syntax for the named SQL VIEW in IDD format with COBOL elements. 38

WAY NOITANIMIRCSID PLOT _____ PASS 0 MD BA PHD Final Exam GETTING IT AL L WHEATHER ME _________ IT IT ALL/WORLD LU CKY 39

The Plot Thickens Highway Reverse Answers Overpass Discriminatio n 3 Degrees below Zero Getting away from it all A Bad Spell of Weather It’s Beneath Me It’s a Small World After All A Lucky Break 40

CA-IDMS DML SQL vs CODASYL Kay. Sussmann@att. net 41