SQL in Practice : How to do CornerDBNAME / SEGMENT Info
SQL Can Do It!
Limits for Long Integer
Ready Clause and Access Modules
Fatal Errors in IDMS SQL
Back to Main Page Located at http://www.reocities.com/idmssql
Homage to Astor FoldalIDMS Wellwishers in Oslo inform us that Astor Foldal, one of the pioneers of IDMS Usage in Norway, has passed away last week after sudden illness. After working for the IDMS distributor Scand Systems in the early eighties, Mr Foldal was long time DCA at Norwegian Telecom and played an important role in the early stages of IDMS and ADS in Norway. With his smiling face and good humour, Astor was liked by all at Cullinet Userweeks and other special course gatherings.
IDMS/SQL joins other friends and colleagues at
Telenor, Norsk Hydro, Statoil, BBS, State Computer Center (SDS), PMSC and several consultants in Norway and other Scandinavian countries in paying homage
to Astor in his early journey from here to
Bad PTFS since 9711 (14.0 Tape)Here is a list of PTFS which were corrected by other APARS later. Note that not all of them are bad ptfs, some are just being modified by a later PTF. Contact the vendor for an exhaustive list and final word on these.
Bad PTFS Corrected /Modified by LO30846 LO51067 LO31077 LO35606/08 LO31350 LO53390 LO31900 LO37443 LO35616 LO39282 LO37323 LO44984 LO37442 LO49501 LO37664 LO42505 LO37919 LO38258 LO38990 LO51408 LO40724 LO48796 LO40921 LO49768, LO49866 LO40934 LO46070 LO41353 LO44430 LO49938 LO50119 LO54525 LO57212 TB96255 New version TB96255 (=LO51054)
Real Year 2000 Problems!Embedded Chip problem is the most serious issue now, not the software programs doing billing or salary or interest calculation (programs have been increasingly handling year 2000 already since 1995, in such scenarios). Many MVS/IDMS systems also have been IPL'ed with Y2K, so system software should pass. But there is now ay to test Y2K if it is embedded within the chip! Read about hardware error in a: Time Special on Embedded Chip Problems
Vapourware Harmony DisappearsReports in IDMS-L and other forums indicate that the much touted "Harmony" from CA has evaporated by the last CA World. There was subdued coverage on the subject. Gateway which was an important link in the Harmony has been largely rejected, especially by the IDMS Client base. CA's attempt to sell OpenIngres to IDMS Client base also failed miserably. One doesn't have to be an EDP pundit to predict that the next in the line for waste basket are Jasmine and Opal. How many are going to attend the Jasmine section of the CA World. Well, you will never find out - CA no longer will hold individual conferences by name.
This treatise first appeared in IDMS-L in June 1998 and Aug 1998. Several 'old timers' contributed thru IDMS-L. Later Chris Hoelscher published an edited version in 1999.
IDS, Integrated Data Store, was designed by Charlie Bachman for General Electric to run on their GE-635 Mainframe under their GECOS operating system. IDS for the GE-200 was a primitive data base manager. It allowed a single file only for the database and had no tools for table generation so they had to be hand coded in assembler. Mistakes in coding the tables combined with bugs in the IDS code itself were a rich source of fatal errors that caused the system to crash, several times a day. BFGCC was not able to bring up a production system with GE's version of IDS due to its unreliability.
Eventually, BFGCC offered to help debug the software if GE would have no objection. B.F. Goodrich bought the rights and converted it to run on the IBM family of mainframes and renamed it IDMS in the process.IDS for the GE-200 was written in the language of the General Assembly Program (GAP). This assembler had no DSECTS, no macros and no cross-reference. In addition, there were no EXTRN type addresses or a linkage editor to resolve them. The GE-200 had only a loader. IDS and its tables had to be assembled together to create the load module, which meant a big re-assembly taking about 30 minutes, each time you changed either IDS or the tables. Programs communicated with each other through fixed memory locations, assigned in advance and hard coded into the programs that were to use them. Jim Gilliam (of the IS staff) and his team cut the IDS assembly listing apart and studied it subroutine by subroutine until they gained an understanding of the logic used. They also realized that using second generation assembler for implementation of complex software was a loser's game. A new tool was needed.
GE had a software implementation language called WIZOR. The GE FORTRAN II compiler was written in WIZOR as well as the WIZOR compiler itself. Which made WIZOR an easily modifiable language and a new enhanced dialect, G-WIZ, was soon born. Gilliam and others re-wrote IDS into G-WIZ and so created a dependable version. A copy of the new software was returned to GE as part of the agreement. BFGCC continued to work with its version of IDS and in 1969 it was modified to handle two database files. By this time, BFGCC was now running a (sort of) on-line (more correctly remote batch) order processing system in operation on a GE-265 configuration (GE235 CPU with Datanet-30 network controller) using their modified version of IDS for the GE-200 series machines.
At this point, BFG instituted a corporate policy dictating that all BFG data centers will select IBM equipment (or else) when upgrading to third generation computers. Since there were no IDS type DBMSs available on IBM mainframes, BFGCC management, after a feasibility study, authorized the development of IDMS. BFGCC was a member of CODASYL and as such, had an advance copy of the CODASYL Data Base Task Group's (DBTG) April 1971 specifications. Also, Schubert, who represented BFGCC in CODASYL, was an active member of the DBTG and thoroughly knowledgeable regarding the proposed specifications. IDMS was developed using a subset of the DBTG's specifications.
IDMS was designed and written at the Cleveland data center of The BFGCC. Most of the work took place from late 1969 to mid 1971. The project team consisted of five programmers under the management of Richard F. (Dick) Schubert. The programmers and their contributions were as follows: Vaughn Austin, IDMSCALC & IDMSINIT; Ken Cunningham, IDMSDMLC; Jim Gilliam, IDMSDBMS, IDMSIDMS, and the overall design of the IDMS system; Pete Karasz, IDMSDUMP, IDMSPFIX, IDMSRBCK, IDMSREST, IDMSRFWD, IDMSUBSC, and ISL language compiler; and Ron Phillips, IDMSCHEM.
It is without doubt that the work with IDS gave us the confidence to tackle development of IDMS. It is important at this juncture to stop and point out the facts that the only IDS source ever seen by us was the earliest version for the GE-200 and that no code from IDS has survived into IDMS. We did, however, retain the concepts of file navigation based on tables and the owner-member record relationship. Multi-file databases (DMCL), areas, schemas (DDL) and subschemas are all CODASYL concepts implemented for the first time in IDMS. The IDD was our original contribution and it was not part of the DBTG's specifications until years later. IDS for the GE-200 had none of these features and for that reason alone it could not serve as the basis for IDMS. IDMS was all new design, all new code. Later versions of IDS for the GE-400 and GE-600 series machines were CODASYL compliant implementations but they came years after IDMS.
Just what WAS ISL? Although the IBM assembler was much more sophisticated than GAP, our aversion to assembler for large-scale software implementation has remained with us. A language like WIZOR was needed for the IBM. Recall that this was 1969. "C" is not yet a gleam in the eyes of Kernighan and Ritchie. IBM is already using PL/S for writing parts of MVT and other software, but they are keeping it proprietary. We had to roll our own. The implementation language was developed with the required IBM/360 features using GE-200 WIZOR 5 as the starting point which gave the programmers a familiar syntax to work with. Coding of IDMS has begun months before there was a compiler to grind the code. The code generator part of the compiler was chopped away and new code written to produce 360-assembler source code. We ran test compiles of small routines and captured the output on 7-track tape, which the GE-200 and the IBM/360 could both read. The code was submitted to the IBM assembler and if errors were found, we adjusted the compiler and tried again. Eventually we got error free assemblies. At this point we could compile the compiler and assemble it on the 360. Intermediate Systems Language (ISL) was ready for business. For the IDMS implementation we had to make do with a primitive one-pass version, as there was no time for unessential improvements. It did the job. Later, a multi pass optimizing compiler for ISL was written to produce code for, and take advantage of all addressing modes of, the PDP-11/45. A highly optimizing version for the IBM-370 followed in 1975. IDMS and all its utilities were originally written in ISL except IDMSDMLC, IDMSCALC and IDMSCHEM.
At this point, IDMS was ready for production. The initial set of data base tables was hand coded, first in plain assembler, later using macros. The macros were also utilized in creating the first IDD in early 1971 so the schema and subschema compilers had a dictionary to work from. Once those processors were developed, the hand coding of subschema tables came to a welcome end. While the development of the IDMS software was in progress, application programmers were busy converting the components of the order entry system from GECOM to COBOL. The first production on the IBM 370/155 using IDMS was PRESTO, an accounts receivable system. TOPSY, the order entry system followed shortly and many others later. IDMS at this point = was a local mode only system.
How did IDMS come to exist outside of BFGCC? BFGCC realized that IDMS was marketable software that would find a ready audience among those who, like BFGCC, were early users of IDS but were now confronted with the need to go to an IBM mainframe. A proposal was submitted to BFG management to establish an independent business unit, the Information Systems Division, which would market IDMS and other software yet to be developed and also support BF Goodrich with its corporate data processing needs. Coding pads with the ISD logo were printed and the first edition of the IDMS User's Reference manual bore the ISD inscription as well. Promotional materials like IDMS ashtrays with miniature Lifesaver Radial tires around them were given away to prospective customers. BFGCC began selling copies of IDMS, first to a local company, then to others. The first five customers of IDMS were: ACME Cleveland Co., Abbott Laboratories, General Motors, RCA, and Sperry Rand (UNIVAC). Two more prospective customers, Boeing Computer Services and Western Electric were trying out IDMS but we never got to sign them because the roof fell in on our hopes of becoming an independent division within BF Goodrich.
Since then, in 1986 BFG began its transformation from a rubber and chemicals company to one primarily involved in aircraft support systems and maintenance. The Tire Division was combined with Uniroyal's as the Uniroyal-Goodrich Tire Company. Within a year or so BFG sold its interest in the company and was out of the business it was best known for. Next came BFGCC. The Company was busted into three divisions. One sold outright, one kept as the Specialty Chemicals Division, and one spun off as an independent entity called the Geon Company. Specialty Chemicals switched to an AS400 and left IDMS around 1995. Geon went to a SAP based client server system on Digital (excuse me, Compaq) Alpha processors and completed the move of all production from IDMS in February of 1997. The IBM mainframe was removed in April of 1998. And the original development team? Vaughn Austin left BFG in 1995 and is in Technical Systems Support at Alltel's Twinsburg, OH Data Center. Ken Cunningham left BFG in the 80's, worked at various companies in the Cleveland area and is now retired. Jim Gilliam, the "father of IDMS" still works at Geon, has been an Oracle DBA these last three years and was expected to retire at the end of 1998. Pete Karasz served out his time at Geon and took an early retirement offer, have been out since January of 1998. Ron Phillips remained with the Uniroyal-Goodrich Tire Company until Michelin bought it and moved everyone to Greer, SC. Ron passed away when he suffered a heart attack while clearing his drive after a freak snowstorm. Dick Schubert retired from Geon in 1995.
The story supplied by the original Developer Peter Karasz ends here. We continue the story:
Cullinane Database Systems developed the system further. The main enhancement was the addition of an integrated online component - IDMSDC. In addition to its usage as a database server to be used with IBM's CICS, now IDMS could support a complete DB/DC environment of its own. Rick Nini (R) and Don Heitzman (H) led the DC development team , who also contributed to the 'RH' in RHDC prefix of IDMS modules. With the addition of ADS/OnLine 4GL IDMS was the leader in the database market. The dictionary - IDD - became a complete online dictionary - the only active dictionary in the market that time. A datamation report dated 1984 said that 4 out of 5 new IBM customers opted for IDMS.
But Cullinet did a marketing blunder with the Release 10.0 of the product. That was advertising the product as relational! Recall the big IDMS/R advertisements? ASF component was the 'relational' part! ASF was built using LRF which itself was not bad as long as one restricted the usage to retrieval only (like VIEWs in relational systems). One could make a table and a builtin map and dialog automatic. But there it stopped. Database is more than a simple standalone table. Using ASF one could join two tables. But there was no way to connect a group of tables to a dailog or a COBOL program. Besides the all important 'SQL' was missing. In short, there was no real R in IDMS/R. Eventhough the other components in the group, ICMS and Goldengate were good for 1984 (Goldengate/ICMS provided the first integrated PC access to mainframe data!), the R failure overshadowed evreything! Good or bad is a different thing - one cannot claim one's product to be relational without having a relational product and without endorsing the model itslef! This eventually dragged IDMS into the discussion with Dr Codd (the infamous 12 Rules). 12 rules were not the Bible of Database Theory, but it served its purpose - broadcasting and proving IDMS/R as conforming to none of the 12 Rules! [In reality, there was no point in including IDMS into the 12 Rules discussion, beacuse IDMS was not a relational system, then. If Cullinet hadn't claimed IDMS to be relational, then Codd could not have included the product in his comparison (eg: IMS was not included in the comparison)]
1987 Userweek had a blueprint for IDMS release 11.0 with full SQL support. SQL Option was supposed to be based on the SQL Engine of VAX-DB with its optimizer written in C. This was the right direction to go for IDMS. But Cullinet management headed by John Landry & Co had other ideas. They wanted to focus on Expert Systems. IDMS was sidelined and the next Userweek in 1988, there was no talk of Release 11! What a blunder. Many who worked hard to get Release 11 left the firm. VAX Magic did not bring enough revenue. Then John Cullinane came back for a brief period and talked about Enterprise DB for MVS! That is SQL Option was to be a separate database and not part of IDMS! Well, the drama did not go further. In the summer of 1989 Cullinet ran out of money and fallen in the hands of CA. By October 1989 the takeover was complete. CA got a quality database with its 3000 customer base. With Cullinet acquisition, CA crossed the 1 billion dollar mark in revenue.
Though CA released Release 12.0 with full SQL in 1992-93, there was no attempt to popularize SQL. Either CA did not know what they had (in relation to other relational systems) or IDMS's own faithful CODASYL people did not trust SQL. Whatever be the reasons net result is the same: SQL option of IDMS is scarcely used and many industry analysts do not consider (quite wrongly in fact) IDMS as a relational database.
When DB2 V 4.x was released, the product had just come of age! Here is one of the features 'proudly' introduced in V 4: the ability to use 'as' clause in SELECT! Version 4 This feature was in IDMS R12.0 and even in ADS+/PC SQL! "As" caluse was also supported in OLQ/SQL of 10.2. It's hard to believe IDMS gave up against such an incomplete product so early and so easily!
If DB2 had been the 'IDMS' killer, then by now DB2 should have monopolized the database market, which is not the case today. In fact there were no 'IDMS killers' other than Cullinet's foolish decisions and later CA's 'lack of any decisions'! There was a vacuum in the db market which was filled by products like Oracle, Sybase and Informix. Even here (UNIX market) the better product was Ingres which failed. Oracle with its agressive marketing forged ahead. But is it marketing alone? Not at all. Oracle put their technicians at the site as consultants. It served two purposes: One it made more revenue than the product sale on the mini machines. Secondly, Oracle was able to support the sub-standard database with their own people at the site and mask the errors and shortcomings from the real users. Later years proved that this strategy worked very well in an ever changing "Client/Server/Unix World"!
Anybody remember where Rini and Heitzman went after leaving Cullinane/Cullinet? Well, they formed BST (Business System Technology) which first came out with RTE ...which was bought by Cullinet and ultimately became PERFMON for IDMS. Later BST built a product called Endevor which was eventually sold to (guess who?) CA, and is now back in IDMS world. How about Flip ... who left Cullinet, started DBMS (DMLO, etc).. Flip's original partner, Ray Nawara, acquired controlling interest in DBMS with the help of Shamrock Holdings (Roy Disney, Walt's brother) so effectively DBMS became a Mickey Mouse organization. DBMS sold the education and consulting to Keane Inc. in 1989 and the products were sold to CA in 1990. Flip then started Platinum Technology, which became a successful DB2 vendor and now in the CA basket!
Now that relational databases are accepted as standard on all platforms it will be interesting to look back at the origin of SQL and relational Systems. Since Codd published the first paper in 1970 on A Relational Model of Data for Large Shared Data Banks" CACM 13, 6 (June 1970), there has been resarch going on at IBM Lab at San Jose, which was known as System-R Group. One of the pioneers was Don Chamberlin who in fact coined the word SEQUEL (Structred English Query language). Incidentally Don is still wih IBM has even come out with book on DB2 Universal Database.
There were several others working at System-R Group. Some of them worked on a prototype which was known as System_d for a while. S. Andler, I. Ding, K. Eswaran, C. Hauser, W. Kim, J. Mehl and R. Williams. "System D: A Distributed System for Availability" Eighth International Conference on Very Large Data Bases, Mexico City (September 8-10, 1982).Some time in early eighties a group left IBM and formed a company called Esvel. Later some of the employees left Esvel and joined HP, Oracle, Tandem etc. Esvel was eventually bought by Cullinet in 1986 and beacme the IDMS/VAX. This later became the basis for IDMS/SQL on mainframe.
The first commercial product on SQL was not SQL/DS as many people think. It was in fact Oracle! Orcale was influenced by System-R, but not based on System-R. As one of them original devlopers, Roger Bamford put it "In terms of System R's influence on Oracle: some ideas came from Esvel, and some of those came from System R. But the original code they'd written was really like somebody had a paper that described the language, and they had a computer and nothing else. " Oracle shipped the first product in 1979 on VM. For marketing reasons, it was called Version 2. And there was no version 1 at all.
Back to IDMS/SQL (VAX) and Cullinet. The technology from Esvel was indeed a superb buy for Cullinet. This is reflected in the news which appeared in the German issue of Computerworld in 1986. The translation reads as "Cullinet strives higher IBM compatibility:WESTWOOD - Cullinet Software Inc. wants to develop their program products further, in order to achieve a larger compatibility with the large computer often commodity of IBM. The programs are to be modified so that they can operate with the Structured Query language (SQL). Cullinet reacts IBM's (SQL) challenge ever more strongly by competiting with Big Blue. In the opinion of industry analysts Cullinet underestimated so far the effects of the IBM products on own growth. Therefore the conversion to a larger compatibility is highest priority. A first step was the transfer of Esvel Inc., San Jose, in July. The product range of this provider covers also a program, which uses the Structured Query Language, which is intended for minicomputers of Digital Equipment.
What happened later did not follow this direction. Generator emphasis and going back from the announced IDMS R11 are discussed elsewhere in this newsletter.
We stop the System-R story here. It will be continued in the next Issue. References: System R Bibliography
++VER (Z038) FMID(CGJE000) PRE (GJ09711,GJ09810) SUP (TB96255). ++ZAP (IDMSSERV) DISTLIB(DISTLOAD). NAME IDMSSERV IDRDATA LO51054 BASE 000000 VER 0010C2 9202,8004,4110,8000 VER 00117A 92E7,8003,5810,8008 . . . ++ZAP (RHDCCKUR) DISTLIB(DISTLOAD). NAME RHDCCKUR IDRDATA LO51054 BASE 000000 VER 00013A 5870,6008 REP 00013A 45E0,C188 Part of SMP Execution JCL //ALO51054 EXEC SMPPROC //SYSIN DD * SET BDY(GLOBAL). RECEIVE SELECT(LO51054). /* //BLO51054 EXEC SMPPROC,COND=(4,LT,ALO51054.SMPGO) //SYSIN DD * SET BDY(IDMSTGT). APPLY SELECT(LO51054) CHECK. /* //APPLY EXEC SMPPROC,COND=((4,LT,ALO51054.SMPGO), // (4,LT,BLO51054.SMPGO)) //SYSIN DD * SET BDY(IDMSTGT). APPLY SELECT(LO51054). /*Once you run the Job comes the surprise! APPLY fails because the ZAPS won't verify. What about the SUP parameter? It does not do anything. In fact, it is only documentational! It serves no purpose whatsoever other than misleading you! And on top of everything, APPLY CHECK does not identify the error! It only gives a cc=04 (missing PREREQ apars). It should have given CC=8.
APPLY SELECT(LO51054) CHECK. GIM38201W THERE IS A MODID ERROR FOR ZAP ENTRY IDMSSERV IN SYSMOD LO51054. GIM31902I SYSMOD LO51054 DOES NOT SPECIFY LO30854 ON THE PRE OR SUP OPERAND. IS CURRENTLY INSTALLED. . . . GIM20501I APPLY PROCESSING IS COMPLETE. THE HIGHEST RETURN CODE WAS 04. APPLY SELECT(LO51054). GIM38201W THERE IS A MODID ERROR FOR ZAP ENTRY IDMSSERV IN SYSMOD LO51054. GIM31902I SYSMOD LO51054 DOES NOT SPECIFY LO30854 ON THE PRE OR SUP OPERAND. . . . IS CURRENTLY INSTALLED. GIM23701E ** ZAP VER PROCESSING FOR SYSMOD LO51054 FAILED FOR MODULE RHDCCKUR IN THE RETURN CODE (08) EXCEEDED THE ALLOWABLE VALUE. DATE 99.214 - TI GIM23701E ** ZAP VER PROCESSING FOR SYSMOD LO51054 FAILED FOR MODULE IDMSSERV IN THE RETURN CODE (08) EXCEEDED THE ALLOWABLE VALUE. DATE 99.214 - TI GIM30216I APPLY PROCESSING FAILED FOR SYSMOD LO51054. SYSTEM UTILITY PROCESSI GIM20501I APPLY PROCESSING IS COMPLETE. THE HIGHEST RETURN CODE WAS 08. NAME RHDCCKUR RHDCCKUR BASE 000000 VER 00013A 5870,6008 AMA104I VERIFY REJECT - SET NO GO SWITCHThere is no way to replace a USERMOD like this. 'SUP' and the 'USERMOD' is a joke. What about RESTORE? Well, RESTORE requires that you restore all the apars (which are not ACCEPTed) applied on a particular module. In the IDMS scenario, once we start RESTORE process it will trigger a chain reaction. You may end up restoring everything. But SMP works very nice, when it works? Isn't it? Yes, it does; so do your own homemade methods, when it works!
DCMT D DBNAME gives .... *** DBTSBLE DBTBGLBL COMPILED 99-09-10 AT 14.16.08 DBNAME DS20DM MATCH ON SUBSCHEMA IS OPTIONAL SEGMENT TESTDICT SEGMENT G001MKB SEGMENT GS20MDB DBNAME DS20TS MATCH ON SUBSCHEMA IS OPTIONAL SEGMENT GS20TS10 SEGMENT GS20TS12 SEGMENT GS20TS30 SEGMENT GS20TS31 SEGMENT GS20TS32 . . . DBNAME DS20TB MATCH ON SUBSCHEMA IS OPTIONAL SEGMENT GS20TS12 SEGMENT GS20TS30 SEGMENT GS20TS31 SEGMENT GS20TS32 PAGE 053 - NEXT PAGE: But one cannot browse 50-100 pages of display to find the contents of just one dbname. There is no command like DCMT D DBNAME DS20TS. The following QFiles written in IDMS Mode against System Catalog will accomplish this task for you. [Full source at the end]. OLQ DBNAME DS20TS DBNAME NAME DS20TS GS20TS10 DS20TS GS20TS12 DS20TS GS20TS30 DS20TS GS20TS31 DS20TS GS20TS32 DS20TS GS20TS33 DS20TS GS20TS34 DS20TS GS20TS36 DS20TS GS20TS41 DS20TS GS20TS60 END OF REPORT And if you want to know what each segment consists of, you could use DCMT D SEGMENT GS20TS10 Here DCMT gives more information D SEGMENT GS20TS10 ---------- Area ------ Lock Lo-Page Hi-Page ’Ret ’Upd ’Tret ’Ntfy GS20TS10.AR20MB54 Upd 1130001 1130160 0 0 0 0 STSmp: 1998-08-13-188.8.131.522226 Pgp:10S20 STSt: 0 Pnd Lock: GS20TS10.AR20MB56 Upd 1135001 1135100 0 0 0 0 Here you have page numbers and STATUS. If you want areaname, datasset name or page group still these are missing. The following QFILE will display the missing information. OLQ SEGINFO GS20TS10 FILENAME DSNAME AREANAME PGRP ZA1110-PERSON TS20C.DBIDMS.ZA11TS10 AR20MB10 20 ZA1111-SUB TS20C.DBIDMS.ZA11TS11 AR20MB11 20 ZA1112-EQUIP TS20C.DBIDMS.ZA11TS12 AR20MB12 20 ZA1113-CONTROL TS20C.DBIDMS.ZA11TS13 AR20MB13 20 ZA1114-ORDER TS20C.DBIDMS.ZA11TS14 AR20MB14 20 ZA1115-USER TS20C.DBIDMS.ZA11TS15 AR20MB15 20 ZA1116-LAND TS20C.DBIDMS.ZA11TS16 AR20MB16 20 ZA1118-TARIFF TS20C.DBIDMS.ZA11TS18 AR20MB18 20 ZA1117-INDEX TS20C.DBIDMS.ZA11TS17 AR20MB17 20 ZA1119-PROVISION TS20C.DBIDMS.ZA11TS19 AR20MB19 20
The source follows:DIS QFILE DBNAME WITH MOD SOU ONLY AS SYN &DBN=SYSIDCT SET ACCESS IDMS SET DATE DD-MONTH-YYY SET CATALOG SYSTEM SET NULL CHARACTER '.' OPTIONS ALL HEADER ECHO NOFILLER FULL WHOLE INTERRUPT NOOLQHEADER - NOPATHSTST NOSTST COMMENT VERBOSE NODBKEY NOPICTURE NOCODETSB NOSYN SELECT DBNAME, NAME - FROM SYSTEM.DBSEGMENT - WHERE DBTSBLE='DBTBGLBL' AND DBNAME='&DBN' PAGE HEADER - LINE 1 '* DBNAME AND SEGMENT INFO FROM CATALOG *' CENTER PAGE FOOTER - LINE 1 '* DBNAME &DBN CONSISTS OF ABOVE SEGMENTS *' CENTER DISPLAY SPREAD EVEN DIS QFILE SEGINFO WITH MOD SOU ONLY AS SYN &SEG=EMPDEMO SET ACCESS IDMS SET DATE DD-MONTH-YYYY SET CATALOG SYSTEM SET NULL CHARACTER '.' OPTIONS ALL HEADER ECHO NOFILLER FULL WHOLE INTERRUPT NOOLQHEADER - NOPATHSTST NOSTST COMMENT VERBOSE NODBKEY NOPICTURE NOCODETSB NOSYN SELECT F.DDNAME AS DDNAME, - F.NAME AS FILENAME, - SUBSTR(DSNAME,1,32) AS DSNAME, - A.NAME AS AREANAME, - A.PAGEGROUP AS PGRP - FROM SYSTEM.FILE F, SYSTEM.AREA A, SYSTEM.FILEMAP FM - WHERE F.SEGMENT = '&SEG' AND F.SEGMENT=A.SEGMENT - AND FM.SEGMENT=A.SEGMENT AND FM.SEGMENT=F.SEGMENT - AND FM.AREA=A.NAME AND FM.FILE = F.NAME EDIT FILENAME - ALIGN LEFT - PICTURE 'X(8)' EDIT DSNAME - ALIGN LEFT - PICTURE 'X(32)' EDIT AREANAME - ALIGN LEFT SPARSE - PICTURE 'X(10)' EDIT PGRP PICTURE '9(4)' PAGE HEADER - LINE 1 '* SEGMENT, FILE AND AREA INFORMATION FROM SYSTEM CATSLOG *' CENTER PAGE FOOTER - LINE 1 '* FOR SEGMENT = &SEG - DATE ¸DATE LINE ¸LINE' CENTER DISPLAY LEFT COLUMNS = FILENAME DSNAME(32) AREANAME(16) PGRP
SELECT CONCAT (START-YEAR-0415, START-MONTH-0415, START-DAY-0415) AS START-DATE FROM EMPLOYEE OLQ 098006 00 56 whole lines and 0 partial lines in report. OLQ 098007 00 56 records read. 56 records selected. EMPLOYEE REPORT 05/31/99 START-DATE 78 5 4 <-- but we want it in 780504 format 79 616 82 526 77 3 4 811111 81 919 SELECT START-YEAR-0415, START-MONTH-0415, START-DAY-0415, CONCAT (START-YEAR-0415, START-MONTH-0415, START-DAY-0415) FROM EMPLOYEE OLQ 098006 00 56 whole lines and 0 partial lines in report. OLQ 098007 00 56 records read. 56 records selected. 05/31/99 START-YEAR-0415 START-MONTH-0415 START-DAY-0415 --------------- ---------------- -------------- ------ 78 5 4 78 5 4 79 6 16 79 616 82 5 26 82 526 77 3 4 77 3 4 81 11 11 811111 81 9 19 81 919 79 9 29 79 929 76 8 8 76 8 8 80 12 21 801221 There is no way in olq to remove these spaces and get zeroes, because OLQ does not support "REPLACE" BIF. If we try to change the picture in menu mode: CA-OLQ Release 14.0 *** Report Format - Picture *** -> Page 1 of 1 137000 Specify pictures and press the ENTER key. Disp Select Options or Alter Seq ¸ , 0 Picture EMPLOYEE X START-YEAR-0415 1 _ _ _ 99 X START-MONTH-0415 2 _ _ _ 99 X START-DAY-0415 3 _ _ _ 99 ** COMPUTED COLUMNS ** X SQLCOL00004 4 X(6) DIS EMPLOYEE REPORT 05/31/99 START-YEAR-0415 START-MONTH-0415 START-DAY-0415 --------------- ---------------- -------------- ------ 78 05 04 78 5 4 79 06 16 79 616 82 05 26 82 526 77 03 04 77 3 4 81 11 11 811111 81 09 19 81 919 79 09 29 79 929 COMPUTE NEW-COL = CONCAT (START-YEAR-0415, START-MONTH-0415, START-DAY-0415) DIS COL NEW-COL EMPLOYEE REPORT 05/31/99 NEW-COL ------- 78 5 4 <-- still no use 79 616 82 526
SQL OPTION can do it for YOU!SELECT START_YEAR_0415 !! CAST(START_MONTH_0415 AS CHAR(2)) !! CAST (START_DAY_0415 AS CHAR(2)) AS "START DATE" FROM EMPNET.EMPLOYEE *+ Status = -4 SQLSTATE = 42604 Messages follow: *+ DB005000 T8810 C0M324: Message for module IDMSOCF, SQL statement number : 1. *+ DB005157 T8810 C-4M324: Operator CONCAT invalid for data type DECIMAL OCF 14.0 ONLINE IDMS SELECT SUBSTR(HEX(START_YEAR_0415),1,2) || SUBSTR(HEX(START_MONTH_0415),1,2) || SUBSTR(HEX(START_DAY_0415),1,2) AS "START DATE" FROM EMPNET.EMPLOYEE ; *+ *+ START DATE *+ ---------- *+ F7F0F0 <-- not ok *+ F7F0F1 SELECT CAST(START_YEAR_0415 AS CHAR(2))|| SUBSTR(HEX(START_MONTH_0415),1,2) || SUBSTR(HEX(START_DAY_0415),1,2) AS "START DATE" FROM EMPNET.EMPLOYEE *+ *+ START DATE *+ ---------- *+ 78F0F0 <-- not yet! SELECT CAST(START_YEAR_0415 AS CHAR(2))|| SUBSTR(HEX(START_MONTH_0415),2,1) || SUBSTR(HEX(START_MONTH_0415),4,1) || SUBSTR(HEX(START_DAY_0415),2,1) || SUBSTR(HEX(START_DAY_0415),4,1) AS "START DATE" FROM EMPNET.EMPLOYEE *+ *+ START DATE *+ ---------- *+ 780504 <-- this is what we wanted!! *+ 790616 *+ 820526 *+ 770304
connect to applcat1; *+ Status = 0 SQLSTATE = 0000 select * from ylesch1.binary; *+ *+ TWOSTAR VALUE *+ ------- ----- *+ 0 1 *+ 1 2 *+ 2 4 *+ 3 8 *+ 4 16 *+ 5 32 *+ 6 64 *+ 7 128 *+ 8 256 *+ 9 512 *+ 10 1024 *+ 11 2048 *+ 12 4096 *+ 13 8192 *+ 14 16384 *+ 15 32768 *+ 16 65536 *+ 17 131072 *+ 18 262144 *+ 19 524288 *+ 20 1048576 *+ 21 2097152 *+ 22 4194304 *+ 23 8388608 *+ 24 16777216 *+ 25 33554432 *+ 26 67108864 *+ 27 134217728 *+ 28 268435456 *+ 29 536870912 *+ 30 1073741824 *+ 31 2147483648 *+ 32 4294967296 *+ 33 8589934592 *+ 34 17179869184 *+ 35 34359738368 *+ 36 68719476736 *+ 37 137438953472 *+ 38 274877906944 *+ 39 549755813888 *+ 40 1099511627776 *+ 41 2199023255552 *+ 42 4398046511104 *+ 43 8796093022208 *+ 44 17592186044416 *+ 45 35184372088832 *+ 46 70368744177664 *+ 47 140737488355328 *+ 48 281474976710656 *+ 49 562949953421312 *+ 50 1125899906842624 *+ 51 2251799813685248 *+ 52 4503599627370496 *+ 53 9007199254740992 *+ 54 18014398509481984 *+ 55 36028797018963968 *+ 56 72057594037927936 *+ 57 144115188075855872 *+ 58 288230376151711744 *+ 59 576460752303423488 *+ 60 1152921504606846976 *+ 61 2305843009213693952 *+ 62 4611686018427387904 *+ *+ 63 rows processedThese are binary expansions of 2. In fact, we have reached 2**62. One cannot store 2**63 but can manage 2**63 -1. So the maximum value will be 9223372036854775807.
Data type integer can only take 2**31 - 1 = 2147473647. In order to support higher values one must use LONGINT (8 bytes binary).
SELECT E.* FROM EMPNET.DEPARTMENT, EMPNET.EMPLOYEE E WHERE DEPT_ID_0410=2000 AND FIRST "DEPT-EMPLOYEE" *+ *+ EMP_ID_0415 EMP_FIRST_NAME_0415 EMP_LAST_NAME_0415 EMP_STREET_0415 *+ ----------- ------------------- ------------------ --------------- *+ 69 JUNE BLOOMER 14 ZITHER TERR *+ *+ EMP_CITY_0415 EMP_STATE_0415 EMP_ZIP_FIRST_FIVE_0415 *+ ------------- -------------- ----------------------- *+ LEXINGTON MA 01675 *+ . . . . *+ *+ 1 row processed If you omit 'FIRST' you get all SELECT E.* FROM EMPNET.DEPARTMENT, EMPNET.EMPLOYEE E WHERE DEPT_ID_0410=2000 AND "DEPT-EMPLOYEE" *+ *+ EMP_ID_0415 EMP_FIRST_NAME_0415 EMP_LAST_NAME_0415 EMP_STREET_0415 *+ ----------- ------------------- ------------------ --------------- *+ 69 JUNE BLOOMER 14 ZITHER TERR *+ 100 EDWARD HUTTON 781 CROSS ST *+ 11 RUPERT JENSON 999 HARVEY ST *+ 67 MARIANNE KIMBALL 561 LEXINGTON AVE *+ 106 DORIS KING 716 MORRIS ST *+ 101 BRIAN NICEMAN 60 FLORENCE AVE *+ . . .
Ready mode in Access Modules
- Ready Modes <-- ON CREATE AM - Isolation Levels <-- ON AM and in PGM - Transaction Modes <-- ON AM and in PGM
Case 1: READY MODE - Shared RetrievalDefault is shared update incremental. We override this in AM as follows:
CREATE ACCESS MODULE IDMSSQL.DSQL001 FROM SQLSAVE.DSQL001 AUTO RECREATE OFF READY ALL SHARED RETRIEVAL; <-- Note that this does not appear in the following display see PTF LO44970/972 for correction But after this a display shows DIS ACCESS MODULE IDMSSQL.DSQL001 *+ Status = 0 SQLSTATE = 00000 *+ CREATE ACCESS MODULE IDMSSQL.DSQL001 VERSION 1 *+ DATE COMPILED 1999-06-15-184.108.40.2061203 *+ FROM SQLSAVE.DSQL001 *+ AUTO RECREATE OFF *+ VALIDATE ALL *+ READ WRITE <-- transaction mode *+ DEFAULT ISOLATION CURSOR STABILITY <-- isolation level *+ ACCESS MODULE SIZE 4432 *+ CONTAINS *+ TABLE IDMSSQL.POSTTAB *+ ; INSERT attempt failed: WS-SQLERRMC: -DB001031 T3541 C-4M321: DB error 1228; Tbl:?, Area:?- E ErrSet:?, ErrTbl:POSTTAB, ErrArea:SQLAREA1 Update failed WS-SQLERRMC: DB001031 T3546 C-4M321: DBerror 0828; Tbl:POSTTAB, Area:SQLAREA1- ErrSet:INDEX1, ErrTbl:POSTTAB, ErrArea:SQLAREA1 '28' is a RE-READY problem. Attempt to ready in UPDATE mode failed, since it is already readied in RET mode.
Case 2. READ ONLY Transaction Mode but with default ready mode.
DIS ACCESS MODULE IDMSSQL.DSQL001 *+ Status = 0 SQLSTATE = 00000 *+ CREATE ACCESS MODULE IDMSSQL.DSQL001 VERSION 1 *+ DATE COMPILED 1999-06-15-13.07.29.197961 *+ FROM SQLSAVE.DSQL001 *+ AUTO RECREATE OFF *+ VALIDATE ALL *+ READ ONLY *+ DEFAULT ISOLATION CURSOR STABILITY *+ ACCESS MODULE SIZE 4432 *+ CONTAINS *+ TABLE IDMSSQL.POSTTAB *+ ; Now we get while updating WS-SQLERRMC: -DB001005 T3608 C-4M321: Invalid transaction state
Case 3. Overriding Transaction Mode at run timeProcess code as below can override the READ ONLY transaction mode specification of AM.
EXEC SQL SET TRANSACTION READ WRITE END-EXEC.This will work, provided READY MODE is Access Module is shared update.
Fatal Errors in CA-IDMS SQL!When the first SQL application in IDMS was put in production in Norway in 1994, it was a tremendous achievement for the ESP product and the client. Performance was good, though there were some serious issues which were not resolved. But at this stage of the product it was accepted. Then the expectation was that these issues would be addressed later. One of them was the ability to manipulate timestamps and TDNs across the catalogs/database. This was needed so that a database could be backed up without having to back up all of the catalog. If a catalog contains multiple SQL applications or if you have to restore a database from production to test, such a utility is needed. The vendor promised, and promised, now after 5 years the utility is not there!
Then there were some serious errors in the SQL when one used complex SQL. There were issues when one used SQL to update network databases. Here we take a simple example of NOT EXISTS sub clause againt pure SQL tables where IDMS/SQL gave totally wrong results. Here are the tables:
create table IDMSSQL.A ( ID integer, NIMI char (10), POSTNR char (4)); create table IDMSSQL.B ( A_ID integer, NIMI char (10), POSTNR char (4)); insert into idmssql.a values (1111, 'Jon', 1282); insert into idmssql.b values (1111, 'Jon', 1282); insert into idmssql.b values (4444, 'Diff', 1282); We tried the following in IDMS SQL 14.0 SELECT * FROM B B1 WHERE NOT EXISTS (SELECT * FROM A, B B2 WHERE A.ID = B2.A_ID) ; Row not Found! This is flatly wrong! Look at the following 'IN' subquery which gave the correct result. SELECT * FROM B B1 WHERE B1.A_ID NOT IN (SELECT A.ID FROM A ) ; A_ID NIMI POSTNR 4444 Diff 1282We again tried the same with ADS+/PC SQL Database. Here are the results:
ADS+/PC gave the correct results where IDMS 14.0 was totally wrong. How can one trust such a database which does not understand one of the most elementary SQL clauses? This is worse than getting an error code.
Just One More!When WWW access to Internet became prevalent in the early nineties, we were told that domain names followed some standards. According to this,
.COM - Commercial Organization .GOV - Government .EDU - Educational .ORG - Other organizationsBesides, it was assumed that all these suffixes meant they are in the US. Other countries should use for example,
uk - for United Kingdom fi - Finland no - Norway and so onBut soon it became obvious that Cyberworld had no national boundaries and companies did not want to associate themselves with some country code. In fact, only the United Kingdom faithfully followed this protocol. So a person trying for BBC got Boston Business Computing if we he tried 'http://www.bbc.com' and if he tried 'http://www.bbc.com.uk' he got nothing! BBC home page was at 'www.bbc.co.uk' . Most British sites used 'co.uk' instead of 'COM'. It was simply difficult to remember the British Sites! Just like VHS standard obliterated Betamax, soon most countries followed the simple method, ie .COM without country code. Finally in 1999, BBC purchased the ''COM' domain name from Boston Business Computing and from 1 September, 1999 www.bbc.com is owned by the British Broadcasting Corporation. The original owner, Boston Business Computing is located at www.bosbc.com today!
Latest is that , well http://www.bbc.com does indeed take you to BBC now. But it is only a mapping to the old address http://www.bbc.co.uk/home/today/! The base is not changed!!
IDMS/SQL is published on behalf of IDMS WatchDog Group, Palo Alto-Helsinki-Oslo for free circulation among IDMS Users Worldwide. IDMS/SQL News is not a CA publication. CA-IDMS/DB, CA-IDMS/DC and CA-ADS are registered trademarks of Computer Associates International Inc. CICS, IMS-DB/DC and DB2 are registered trademarks of IBM Corporation.
Technical examples are only guidelines and modification might be required in certain situations and operating systems. Opinion expressed are those of the authors and do not represent the views of IDMS clients or related vendors. © Permission is hereby granted to use or reproduce the information, only to IDMS Customers and Consultants, provided the material is distributed free of charge.