Good Bye to IDMS!
SQL in Practice : How to do Corner
A Peep into DB2 Universal Database
Crossfire Section 7.2
Back to Main Page
Located at http://www.reocities.com/SiliconValley/Vista/4905/idms72.htm
We have information that Per Mogensen, Chairman of Danish IDMS Users Group is leaving the IDMS Community in Denmark and Europe. Per is changing job as of August 1998. Per will be responsible for Web servers/browser technology, Lotus Notes, Firewall and security in the distributed environment etc etc at his new job. Per is already a veteran in the IDMS and mainframe area. Per has been involved with IDMS in Denmark since 1985 and has played a key role in Release 12.0 implementations on Siemens BS2K and IBM MVS/ESA.
Per Mogensen has been involved in co-operation with all IDMS Clients in the whole of Scandinavia. With the shortage of mainframe and IDMS knowhow in the market, such departures will be strongly felt!
IDMS/SQL News wish Per Mogensen all the best in his new career.
Newsgroup - IDMS-LIDMS-L - The IDMS mailing List CA-IDMS Discussion Forum based at the University of Georgia. At the moment besides technical discussion, there is very interesting discussion about IDMS History going on. Those interested, this is the right time to go back to the nostalgic years of 70s and 80s!
No Warning on 'Bad' APARS!- From Helsinki Dispatches
We have read about the database crisis in Finland in the last issue. What caused the corruption, was never conclusively determined. There has been some intense speculation that the absence of APAR #: LO00012 (Unpredictable errors may occur if the IDMSCOMP or IDMSDCOM database procedures are called at improper times) might have been the cause. But this is highly unlikely, because the client did not have any schema with database procedure being called at improper times. Also the symptoms do not match.
IDMS/SQL News has noted recently that many PTFS had errors and they are not marked as bad PTFs in CA System, even though corrections are already published. For example, HYPER APAR GO80610 published on 2 NOV 1995, has errors and can cause serious problems during recovery. The correction appeared as GO87631 in February 1996. But even today, the old PTF is not marked 'bad' in TCC system. Similarly LO00174 published on 12 JUL 1996, for SR8 problems and 1142 errors contains serious errors and can cause database corruption. This was corrected by HYPER APAR LO03809 in 19 SEP 1996, but there is no 'warning' on the old one.
Also not all the HYPER apars are hit on the keyword search. So it is quite possible for someone to download the bad apar any time, even now, without knowing there is a correction for it!
Clients are recommended to stay reasonably current on tapes, even if you don't apply each and every APAR on a daily basis.
Total Number of Transactions - Jan 1998
CPU system transactions A-CPU IMS-DC 3.660.000 D-CPU IDMS-DC 800.000 IMS-DC 740.000 CICS 1.760.000 total D-CPU 3.300.000 grand total 6.960.000
Motivation for 14.0
Planned Work for Application
Check Application for Adaption source changes 1 Culprit 1 Assembler program recompiles 1 PL/I program 5 Assembler programs
Reasons for Adaption
changes in control blocks or DSECTs recompile of edit routines for mapping change of record layout for task statistics in logfile remove circumvention from release 12.01 for option TO (subschema-control) in BIND RUN-UNIT-command
DC-statistics memory-function external version of subschema control block map edit modules display record description from IDD check for signon of user
Conversion Timetable Spanned over a period of 6 months from base install CA-IDMS release 14.0 in June 1997 to Conversion of production systems in December 1997.
Release 12.01 14.0 concept, produced by DBA staff 600 h 180 h realization of migration 1.560 h 425 h testing thru end-users 120 h 80 h efforts of system-programming staff 300 h 100 h tuning 225 h 0 h corrections 40 h 200 h total 2.845 h 985 h
Problems Solved before Migration (I)
Problems Solved before Migration (II)
Problems Solved after Migration
Page sizes specified for 3390 devices in the CA-IDMS Database Design Guide
are partially incorrect. Any value that is not divisible by 4 should be
reduced until it is divisible by 4.
VM/ESA and Year 2000 Testing
This apar is applied to the Operating system dependent moudle, RHDCOCMS. After application of this apar you will need to relink the startup modules for the systems you wish to test on. However, you can apply the apar directly to the startup module by replacing the module name RHDCOCMS with the startup module name.
With this Apar applied each time the CA-IDMS system is started it will be started with an internal date of 12/31/99 and an internal time of 23:45:00.
If the operating system dependent module is included in the concatenation for a Local mode or batch to cv job, each time these jobs are run they will be started with an internal date of 12/31/99 and an internal time of 23:45.
For further details contact the latest notes on APAR : LS33092.
Deadlocks in OLTP Systems
-- compiled by Per Chr. Hansen from client experiences in Sweden and Norway
Locks have been classified as a necessary evil. When two tasks are waiting each other for resources and no one can proceed until one of them relinquishes control, we have deadlock situation. This is different from the normal "wait" situation, where a task wait for resources kept by another (longterm) task. An online task can "wait" and time out if a CV job is keeping resources for a long period.
What is a heavy task? The terminology is relative. That is, what is considered "heavy" in an online OLTP environment might be a trivial job for the batch! Indeed, we want to focus on this type of tasks here.
An online transaction is very fat. Usually the response time is sub-second. Even in cases, where there is a 1-3 seconds response time, the actual CPU time involved will be sub-second. Such tasks can still contribute to deadlocks if they are accessing and locking the same or related records. Very often, clients want to run some CV batch jobs during daytime. Some of these are very small as per batch criteria. But even such "small" jobs taking 5-10 seconds CPU are extremely heavy if it has to run at the same time as online users! The result will be that many online users will be locked out (wait abend) or get deadlocks
How can you avoid this? The solution is very much application dependent. One easy solution is to restrict the running of batch-CV jobs to some narrow windows and lock out online users during this time. This is a like traffic light solution. Some people are allowed and some are just stopped. How about restricting the batch-CV jobs to between 12 and 12.30 (lunch time for online users?)? This may or may not be feasible depending upon the end user requirements. The rest of the batch-CV jobs should be run after 17.00 hrs.
Frequent COMMITs in the batch-CV jobs can certainly reduce the deadlocks with online users. But then we have to take care of the abnormal situation where the batch job abends! How to rollback part of the data, how to account for the data which is already committed? How to process the rest of the transactions in an automatic way?
If the batch-CV jobs are doing some kind of order processing, one should investigate the possibility of using some flags on the order record which says, the order as incomplete. They should be flagged as "ready" only after all the orders have been put in. Again, the online tasks which use these orders should be modified to take care of the "flag". In the event of a batch-CV abend, another task can be started to delete all the incomplete orders and re-start the batch_CV job again to insert all the orders. Again this may or may not be possible depending upon how the orders are processed. If the order is updating some warehouse records, then deleting incomplete orders may not help.
Another way is to keep track of the orders which are COMMITTED in the batch-CV job. Then in the case of an abend, the job can be re-run and the committed orders can be skipped.
We have noted that many network databases have sets with link to many other records. This increases the probability of deadlocks. We are just wondering using SQL option with unlinked constraints is a solution in these situations?
Wanted : IDMS/LU6.2 Knowhow
In IDMS/SQL News 6.3 we had a discussion on some of the problems of LU6.2 when the conventions used by partner systems differ. In this short writeup, we see a mounting problem on a Worldwide basis - Shortage of LU6.2 people!
There have been many requests from IDMS client base to give more importance to the LU6.2 part of IDMSDC. But the vendor was downplaying the product . In the early 90s, CA gave the impression that CA90s had the necessary components to give the functionalities of LU6.2 support in IDMSDC, so that no more development was needed for the current LU6.2 line driver. But alas! Which CA90s component can support program-program communication across CICS-IDMSDC, IDMSDC-OS/2, IDMSDC/Tandem, in Assembler or ADS? None! Since a coherent approach was missing from the vendor, LU6.2 in IDMS have been supported by isolated consultants at various clients, all these years.
Who wants to go through VTAM traces in this "Mickey Mouse" Era of Computing? No one! Who wants to read PTERM (IDMS) traces? No one!! Who wants to go through that meticulous Assembler code of SEND/RECEIVE? No one!!! But then we have a problem! ie there is no one to support a very powerful functionality which has always been a part of IDMSDC all these years!
In spite of all the connectivity noise from various vendors, nothing has replaced the conventional LU6.2 protocol. Even in IBM's DRDA implementation, LU6.2 is made use of internally! IBM is quite serious about the importance of APPC even in this Internet Era, proof of which is seen in publications (May 1998) like Multiplatform APPC Configuration Guide(GG24-4485-00).
In Scandinavia the following companies have production level IDMSDC/LU6.2 knowledge (among other things). System Tjenester Services Oslo, Vegasoft Oy Helsinki. There are also many independent consultants who are well versed in LU6.2.
Change is the essence of life. Since IBM reluctantly introduced DB2 in the early 80s, it has come a long way. Subsequent releases were far behind the existing databases. But the relational twist in the database world and the 'backbone-less attitude' of the competing vendors were favourable to IBM. Even at DB2 V 4.3 level, on the mainframe , DB2 was still catching up with many database features of IDMS Release 12.0. But things are changing now with Version 5.0.
There is not much change in the mainframe area. The change is of a much global nature. DB2 Universal database Version 5.0 was simultaneously on Mainframe, UNIX, NT, OS/2 and Windows/95. This is a bold step forward. What is different in Version 5 is the connectivity and compatibility offered across the products.
On a source level and at implementation level, the mainframe product stands distinct and even different from other platforms. This is understandable, since one cannot expect portability across a 370 machine and a PC! But on all other platforms (UNIX,OS/2,NT and W95) IBM claims that the internal source of DB2 UDB 5.0 is the same!
We have installed the product on our PC and is in the process of doing some testing. Installation is excellent. Documentation is good. Simple things are working. The only negative point we have seen so far, is that you need a BIG PC indeed. Even with a pentium 133 with 24 M memory things were slow and Windows resorted to a SWAP file of 50M when we tried some queries and Access Plans. It looks like one needs a Pentium 200 and with some 80+M RAM!!
Since there is no IDMS/SQL on PC, we intend to use DB2/Windows 95 for simple testing in future. More articles will follow on DB2 in future issues.
How Many Indexes for a Table?
How many indexes should be there for a table, at most? 5,10,15.... ? It all depends upon the size of the table, processing requirements etc. Nevertheless, there should be some guidelines which can be used to arrive at a meaninful desgin.
In the CODASYL world the same issue was there when some key records participated in too many sets. Later some clients added indexes too, when the integrated indexing was introduced with IDMS 10.0. Since linked sets are not there in SQL (or can be avoided), it may appear that we do have more freedom here. But since the owner-member links are there in a pure SQL database, this necessitates the usages of more foreign keys, which in turn makes more indexes a requirement.
An old IBM DB2 Manual (V 1.2) says that there should not be more than 5 indexes for an OLTP SQL table. Since this was written in mid 80s and there have been advances in technology (12.0 features, ESA, Syplex for example) we can be a little more flexible now. Still, we believe that there is nothing wrong in setting an upper limit of 10 indexes for an SQL Table, with a strong recommendation to keep this at 5, if the table is going to be updated a lot in online.
Recently one among us attended an SQL Course in London (not on IDMS). A UNIX database was used as an example, and the instructor's recommendation was to use not more than 2 indexes for a table to be used in online!!
RCMs and Access Modules
Access Mdoule is a very important component of SQL access. It is vital because, it can be dynamically created at run time, depending upon the options on each access module.
If 'AUTO RECREATE' is on, IDMS will recreate the AM at run time, if needed using RCMs involved in the AM.
RCMs will be loaded into reentrant program pool for this reason. If for any reasons, the AM has to be recerated more than once, this can give rise to certain anomaloies, again very much dependent on the various options used. One of those issues were discussed in Serious SQL Issues Item 6 (IDMS/SQL News 7.1). Here is an issue which is related.
CREATE ACCESS MODULE uses the RCM in the reentrant (XA) program pool, if available, rather than a possibly newer one in the load area. The user can force
the load area version to be used in any case, by issueing a DCMT VARY PRO NCI for the RCM.
Decimal Datatype in SQL
--- an ex-Cullinet technician contributed to this note
Everyone knows that datatype INTEGER is in fact signed binary equivalent to PIC S9(8) COMP (= Fullword in Assembler, FIXED BIN (31,0) in PL/1). What about DECIMAL datatype? Though not obvious, in the following definition, column IQ-VAL will support negative values.
CREATE TABLE IQ (NAME CHAR (20) NOT NULL, IQ-VAL DECIMAL (4) NOT NULL ); SELECT * FROM IQ; NAME IQ-VAL ---- ---- HUMAN BEING 100 ORANGUTAN 70 MONKEY 57 JOKER MADMAN -10 MIDNIGHT OWL -20The reason is that decimal is in fact = signed packed decimal. This becomes clear from the following display in OCF IDMS Release 14.0.
OCF 14.0 Online DIS TABLE IQ LIKE REC AS SYN; ADD RECORD NAME IS IQ COMMENT 'Record built from SQL TABLE IDMSSQL.IQ'. 03 NAME PIC X(20). 03 IQ1 PIC S9(4) USAGE COMP-3.
The Utility Manual says " Using UNLOAD on an SQL-defined database:
You can use the UNLOAD utility statement to unload and reload an SQL-defined database to make these changes:
- Change area page ranges
- Change page size
- Change the maximum number of records per page
The page changes are specified in the new DMCL definition.
Note: The modified DMCL must be available during the unload operation as well as during the reload operation." The syntax for SQL unload/reload becomes
UNLOAD SEGMENT segment-name (AREA area-name) RELOAD INTO same-segment-name DMCL dmcl-nameThe named DMCL above is the new DMCL which contains the same named SQL segment, optionally with the limited changed described above. This DMCL will be used for the RELOAD operation. The DMCL used for UNLOAD is the one given under //SYSIDMS card. This minute difference is not clear in the manual.
For SQL databases, unload and reload should use the same segment name. So if one has to resize the areas, this can be done only by using a new DMCL. You cannot make changes to table definitions in an SQL-defined database between the unload and reload steps.
For SQL-defined segments, area and table stamps are unloaded during the UNLOAD steps of an SQL-defined database and are reloaded during RELOAD processing. This becomes a problem if one wants to unload an SQL data area from production to test IDMS. Some clients do want to do this to use the same data in testing (the actual database size in pages will be smaller in test). Such an operation is common in the case of network databases.
More and more, CA-IDMS sites are also DB2, IMS, MQSeries and/or VSAM users. Moreover, new systems need to maintain integrity across all those repositories. Until now, this has been a non-issue for pure blue sites because CICS supports two-phase commit through its SYNCPOINT command. By requesting a SYNCPOINT, all repositories in use by the task either commit or none of them does. CICS acts as coordinator in the two-phase commit process. CA-IDMS cannot participate in the two-phase commit process because it doesn't offer a DML called PREPARE-TO-COMMIT.
Aquisoft will soon offer OCA-COMMIT. This product gives CICS-IDMS transactions the ability to write a PREPARE-TO-COMMIT journal record. When the CICS transaction requests a SYNCPOINT, a TRUE (task related user exit) is invoked for each repository and a PREPARE-TO-COMMIT request is sent to all. OCA-COMMIT provides a TRUE that sends the request to all CVs that have an active run unit. Note that you can have multiple CA-IDMS run units against different backends. Once all the PREPARE-TO-COMMITs are successful, the COMMITs or their equivalents are requested.
OCA-COMMIT provides all the components needed for synchronization with CICS at startup. In the event of a CA-IDMS crash, upon startup CA-IDMS will synchronize any INDOUBT run unit with CICS. Console messages are issued if CICS is not present. Roll forward and Rollbacks utilities are fully supported.
In the future, we will be looking at writing a coordinator within CA-IDMS. This would allow us to commit run units across multiple backends and/or MQSeries (using our OCA-MQSeries Interface).
The product availability date is Fall 98.
Visit the home Page at http://www.aquisoft.com
In this Issue : Back to Top (Contents)
Back to Main Page
IDMS/SQL is published on behalf of IDMS WatchDog Group, Helsinki-Hřrsholm-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.