7.1 7.1 |
|
Please Cast Your Vote: Now!— it can make a difference!Computer Associates is planning an employee recognition award, SELECTED SOLELY by the clients. It specifically states that development and support are eligible. Clients and Consultants in Scandinavia, Europe and the World are requested to use this opportunity to cast their votes. The form is available at Employee Recognition Form Scandinavian Clients are going through a turmoil period, in terms of activity and technical support (or lack of it!). IDMS/SQL News makes a special request to all IDMS Technicians and readers to use the above forum to make their choice known, preferably before the last date :15th March' 98. IWDG stay with the technicians who solidly stood behind the product, in difficult times, in spite of the bureaucracy and opposition from the most unexpected quarters! We hope technicians and well wishers at the Clients do the same! |
Database Crisis in Finland!- Helsinki DispatchesAccording to the largest Finnish Daily "Helsingin Sanomat" (Also Internet Edition 18/2 and 24/2) and "Tietoviiko" (20/2 and 27/2) the Helsinki Corporation offices have come to a standstill after serious database corruption at the Service Bureau Novo Group Oy. Client's IDMS database of about 10Giga had gone wild. While more than 20 experts have been involved for 10+ days in fixing the problem, there has been serious questions about whether there are broken chains in the Scandinavian IDMS Support Management! |
In 12.0 ADSOBCOM gave trouble in the VM/CMS environments. First of all one needs to run ADSOBSYS utility to create the ADSOOPTI module for the system. Then you must have access to 'C' CA90s runtime libraries (In VM some of these 'C' modules may not be in LOADLIBS, but available as MODULEs.] Else, like in MVS, you get the following message:
COMPILE FROM LOAD DIALOG IS ( DIDR1205 ).
*** DIALOG DIDR1205 VERSION 1 NOT FOUND
Note that this is true even in online if you don't have access to 'C' runtime modules. When these were fixed client started getting S0C1 abend.
SYSIDMS parms --> CVMACH=IDMSCV
SYSIDMS parms --> USERCAT=OFF
SYSIDMS parms --> IDMSDBUG=ON
DMSABE148T System abend 0C1 called from
000010E2 reason code 00000000
The magic solution came in the form of G097161 and LO25638. The last one is a VM only required PTF for IDMSVMCF. Then the compile worked!
SIGNON USER=zyz PASSWORD=? DICTNAME=appldict COMPILE FROM LOAD DIALOG IS (DIBJART). *** COMPILER MESSAGES FOLLOW: DC497031 DIALOG PROCESSING BEGINNING DC497129 PROCESSING DIALOG DIBJART 1 DC497005 DIALOG SUCCESSFULLY COMPILED DC497032 DIALOG PROCESSING COMPLETED *** END OF COMPILER MESSAGESBut when the client repeated the run it abended with
* MSG FROM IDMSMNT : IDMS BEING TERMINATED ABEND CODE 3903 REASON CODE 24 -> DDNAME DCMSG CMS DISKID ERRORThen it was found that the 'JCL' (EXEC) given in the manual is not good enough. One has to give (like many other utilities) the ddcard for the message area.
Once the user added FILEDEF for the message area ADSOBCOM worked, and it worked always!
While testing IDMSRFWD on VM/ESA Client was getting:
DB002255 C-4M352: Function OPEN DDname SYS001 - Open error 103IDMS Batch Command Facility Ended with Errors Subsequent test done at CA showed that RFWD works, if we use libraries from 9607 Tape onwards. It was found out that Client was running on 9601 VM Tape (+9605 Special RHDCOCMS module).
APAR #: GO92691 DATE: 22 APR 1996
Problem Description: DB002252 open error 103 on sys001 when running rollback under VM/CMS.
&TRACE ALL FI SYS001 DISK TEMP JRNL T(RECFM VB LRECL 4096 BLKSIZE 4096 FI SYSDBOUT DISK RFWD SYSDBOUT A FI SYSLST DISK RFWD SYSLST A FI SYSIDMS DISK RFWD SYSIDMS * FI SYSIPT DISK RFWD SYSIPT * FI SYSPCH DISK RFWD SYSPCH A FI SORTWK01 DISK SORTWK01 WORK A FI CDMSLIB DISK DBALIB LOADLIB A (RECFM U CONCAT FI CDMSLIB DISK CAICCI11 LOADLIB * (RECFM U CONCAT FI CDMSLIB DISK APAR9607 LOADLIB C (RECFM U CONCAT FI CDMSLIB DISK APAR9601 LOADLIB C (RECFM U CONCAT FI CDMSLIB DISK IDMSLIB LOADLIB C (RECFM U CONCAT GLOBAL LOADLIB DBALIB CAICCI11 APAR9607 APAR9601 IDMSLIB GLOBAL TXTLIB CASORT$C EXECOS OSRUN IDMSBCF ROLLFORWARD AREA EMPDEMO.EMP-DEMO-REGION COMPLETED VERIFY; <-- sysipt file ECHO=ON DMCL=R120DMCL < -- sysidms file RECORDS RESTORED TO AREA EMPDEMO.EMP-DEMO-REGION 0 TOTAL RECORDS RESTORED 0 JOURNAL INPUT COUNTS: BLOCK COUNT 242 BACKWARD 0 RECORD COUNT 9178 BACKWARD 0 Status = 0 Command Facility ended with no errors or warningsNB: In this example our primary attempt was to get the job run without errors. In a real case on VM, RFWD must be run in SORTED mode, since VM does not read tape backwards (an IBM deficiency)!
Most of the serious problems are solved but some minor ones are still being reported like "using CEDF produces a loop in CICS which is caused by IDMSINTC." Keep an eye on the following PTFS. Contact the local vendor for the latest ones. As always, this list is by no means an exhaustive one, please contact Web-TCC for the final word.
GO82724 - ASRA in CICS
GO82725 - SOS on CICS front-end
GO89172 - ASRA in INTC XA
GO95331 - INTC timeout related
GO95352 - CICS abend U409 - see below
GO95357 - AEY9 abend in special situations
GO99998 - DBNAME not honoured by INTC - see below
LO04355 - CICS 3.3 or high w/Storage Protect on
LO12825 - Corrects an error in GO95352
LO12827 - Corrects an error in GO99998
LO22200 - CICS 4.1 APCW abend
LO25652 - INTC abend crashes CICS
LO30590 - VSAM/T and CICS Syncpoint Support
Make a note that the last PTF on 9601 - GO84023,
on 9607 - GO99391
on 9707 - LO19748
For Release 14.0 clients, the last APAR on 9711 is LO25803.
IDMSCOMP BEFORE STORE
BEFORE MODIFY
BEFORE FINISH
BEFORE ROLLBACK
IDMSDCOM AFTER GET
BEFORE FINISH
BEFORE ROLLBACK
IDMS/SQL News Comments: The reason this problem is appearing is that in 10.2 people got correct results even after what is considered improper coding now! There were minute differences at which point the control is given to the procedures in 10.2. These differences do matter in 12.0 and one may end up getting really wrong results now!
ADD PROCESS NAME IS PDLG1-RP1 VERSION IS 1
*+ USED BY PROGRAM DDLG1 VERSION 1
MODULE SOURCE FOLLOWS
MOVE 'SYSTEM' TO DB-NAME.
MOVE 'DDLG1' TO PROG-NAME-051.
OBTAIN CALC PROG-051.
MOVE DESCR-051 TO WS-DESC1.
LINK TO 'DDLG3'.
MOVE 'SYSDIRL' TO DB-NAME.
MOVE 'DDLG1' TO PROG-NAME-051.
OBTAIN CALC PROG-051.
MOVE DESCR-051 TO WS-DESC2.
DISPLAY TEXT 'HERE WE ARE'.
ADD PROCESS NAME IS PDLG3-PM VERSION IS 1
*+ USED BY PROGRAM DDLG3 VERSION 1
MODULE SOURCE FOLLOWS
RETRUN.
The dialog DDLG1 used the subschema IDMSNWKA. The dialog DDLG3 doesn't do any database access, but it must have a different subschema connected just to avoid the run unit being extended.
IDMS/SQL thinks that using SQL straight is a much cleaner and easier way. The above method becomes complicated the moment one has update! Using SQL access network database in the above case, one doesn't need the second dummy dialog. The coding will be in the direction of the following SELECT.
SELECT * from systemnw."prog-051" where prog_name_051 = 'DDLG1'; SELECT * from sysdirnw."prog-051" where prog_name_051 = 'DDLG1';where systemnw and sysdirnw are appropriate SQL schemas defined for each of the dictionary databases.
One of the last things any client wants is unpredictable results! We have used the word "results" instead of "errors", because even errors are preferable than wrong results, in certain situations!
We have reports indicating that clients are getting totally crazy results in certain situations. Here is a brief survey of some of them.
1. Complex UNION gives wrong and right results!
Mathematically, UNION operation is commutative. ie UNION (A,B) = UNION (B,A). But in the following case it violates this rule and gave different results! Example: (From a real case greatly abridged here...)
SELECT col, col2..... from 3 tables WHERE complex_where_clause with a subSELECT UNION SELECT col1, col2.. WHERE a less complex_ where_ clause ;Gave the correct results. But when they interchanged the two sides of the UNION they got nothing!
SELECT col1, col2..
WHERE a less complex_where_clause
UNION
SELECT col1, col2..
WHERE complex_where_clause with a subSELECT;
*+ No qualifying rows found
We have information from news group reports that similar errors have been reported by clients elsewhere.
Status: Not yet solved.
2. DB005037 Error on CREATE Access Module
Source Code:
DECLARE CURSOR KERTAK2 CURSOR FOR
SELECT TILINO
FROM SQO201.TILI
WHERE KERTUN1_1 =:KERTUN-1 AND..
3. Load Utility fails on zero valueLoad Utility gives pseudo message In OCF,
INSERT INTO TB_RESKNO VALUES (0000, 'ALL IS WELL HERE'); SQLCODE 0 SQLSTATE 00000But LOAD Utility FAILS in batch execution of BCF with LOAD into table... statement. It also gives a misleading message of DUPLICATES Violation!
4. Create Access Module fails with DB005156
Similar to case 2 above, but a different message.
- Normally comes when the WHERE clause refers to a field which is not in the SELECT and host variables are used
- Circumvention is to make sure that all columns in the where clause are also in SELECT
5. DESC or Mixed Index Usage in 12.0 / 14.0
Using DESC in indexes, especially mixing of ASC and DESC in the same index can give totally wrong results, without giving any error message at all!
CREATE TABLE IDMSSQL.MIXED2
( COL1 INTEGER,
COL2 DATE,
NAVN CHARACTER(20) ) ;
CREATE UNIQUE INDEX INDX1 ON
IDMSSQL.MIXED2 ( COL1, COL2 DESC ) ;
SELECT * FROM MIXED2;
*+ COL1 COL2 NAVN
*+ 111 1997-10-16 TODAY
But
SELECT * FROM MIXED2 WHERE COL1 = 111;
*+ No qualifying rows found < ---- Wrong!
SELECT * FROM MIXED2 WHERE COL1 = 111
AND COL2 > = '1997-10-16';
*+ No qualifying rows found < ---- Wrong!
Both of them are wrong and should have returned the same
result as below!
SELECT * FROM MIXED2 WHERE (COL1 = 111 OR NAVN='TODAY')
AND COL2 > = '1997-10-16';
*+ COL1 COL2 NAVN
*+ 111 1997-10-16 TODAY
*+ 1 row processed
The following also works OK.
SELECT * FROM MIXED2 WHERE COL1 = 111
AND COL2≪ = '1997-10-16';
The Final Recommendation from CA: In SQL do NOT use DSECENDING index at all!
6. RCM Not FOUND Message
Sometimes one can get a message while creating Access Modules
DROP ACCESS MODULE .; (Status=0)
COMMIT; (Status=0)
CREATE ACCESS MODULE . FROM etc...
Status=-4 SQLSTATE=42607 Messages follow:
DB005514 T68286 C1M324: Load of RCM failed with return code 20.
DB005533 T68286 C-4M324: Requested RCMs not found.
The RCM named does exist, actually in two copies, which one can see
with 'DCMT D PRO FROM ':
*** Program Definition Table - Dictionary Modules Indicated by "D" ***
Program Typ D DDname/Version DictName Node
RCM D Version 1
RCM D Version 1 APPLDICT
...
The only wayout is to recycle CV, according to the client!
Technical Explanation from CA was as follows:
But this is basically resulting from IDMS making two PDEs for the same module (here RCM) in certain situations. One with dictname and one with spaces (in practice the space will deafult to the same dictionary as a result of dbname mapping) .
This situation can change only if allow dictname on program statements in sysgen (a long standing issue). This would have allowed us to sysgen programs from any dictionary. Or if the IDMS Loader today can make out that dictionaries 'APPLDICT' and spaces and one and the same thing, and load such modules just once!
What you see is a bye-product of the deficiency. PDE does not carry the dictname at all. It is kept in some sort of table elsewhere. Till now, it didn't affect the end users and no one noticed the small glitch of having the same dialog available in two copies! But with SQL access in very special situations, this can completely stop loading of an RCM!
Solved Issues in the last 2 months:
Recently a user got the following: Loading an SQL table by BCF local job number of rows elapsed time i/O 10000 1 min 4000 100000 30 min 200000 the next 100000 2 hours 600000The table has just one index. DEFAULT index is dropped . No constraints on the table. DMCL has 100 page sin the buffer. Obviously this is not the way to go!
Two important points here:
1. PAGE RESERVE is obviously wrong! PAGE RESERVE
+ unusable space on a page must never exceed 30% of the PAGE SIZE. If it does, then SMP for these pages will never be updated and will show as 'EMPTY' and DBMS will search all these pages unnecessarily while inserting a new row or even SR8 record. Taking the above example, it looks like while inserting the 100,001 st record, DBMS is going through 10,000+ pages (though they are full).
2. The buffer is too low. Recommendation from experts is to use a buffer of 1000 pages or higher for load. Also remember to turn PREFECTH=OFF in SYSIDMS.
Improvements:
After the changes as follows: Buffer = 1000 pages for index 500 for data, PAGE RESERVE 20%, record size was 107 bytes
Client was able to loading 4.6 million records in 70 minutes. Investigations continue to improve the response time further. The client has to load 70 million rows in a table.
Indexing for SQL databases
We have not discussed how indexing parameters like the size of SR8, DISPLACEMENT, IBC value, index levels etc. The issues discussed in the old manual "Use of Indexing with IDMS/R Rel 10.2 Sept 1987" is still valid for 12.0 and 14.0.
For SQL databases, user can specify some of these values. Otherwise, SQL will take its own default. Usage of ESTIMATED ROWS on CREATE TABLE statement has an impact in the calculation of IBC. The size of an SR8 is vital when we are talking about 70 million rows! An SR8 size should never exceed 30% of the page size. With this much data, IBC should be reasonably high to avoid too many levels.
Why SELECT is faster than INSERT?
This will come as a surprise to many! Even if it has to walk through many levels of SR8, a SELECT in such a situation will be always faster than an INSERT. In the case of INSERT SMP pages has to be read to find free space. This happens also for index update. Then there is question of SR8 splitting which will involve updating of further index pages.
Because of all these, a wrong IBC value can result in 100s and even 1000s of unnecessary I/Os! And when loading millions of rows, elapsed time can simply fly out of the window!
Last year our management asked me to investigate a way to bring the mainframe data, more specifically the IDMS data, to the desktop. The solution was to be fast, reliable, secure and equally usable in 2 and 3-tier client/server, as well as from within a web browser. As if it wasn't difficult enough yet, there shouldn't be any limitations due to the nature of our existing database structure (network, not relational).
In the past we already implemented a 3-tier solution using Sequelink software from Intersolv and APPC. This wasn't considered a feasible solution anymore, especially in an internet/intranet environment. Considering our database structure I also eliminated SQL and ODBC as a candidate. Being a full CA-IDMS/DC shop, I kept coming back to solutions based on APPC in order to communicate with our database. Well, until I came across two very interesting software called TCP/IP Sockets interface to CA-IDMS and MQSeries Interface to CA-IDMS from Aquisoft, Inc (a Canadian based software company) and distributed in Europe through Formula OpenSoft Benelux B.V. These two software mirror theTCP/IP socket and the MQI API's from IBM in their CICS environment.
As my company just implemented TCP/IP on all platforms, it was a logical choice for me to investigate the TCP/IP socket interface. Installation of the TCP/IP Socket interface for CA-IDMS was a breeze.
Soon after that I could communicate directly between a Windows PC and IDMS using Visual Basic or Powerbuilder software. Communication from within a browser environment wasn't that simple. I tried several possibilities before coming up with a nice solution (at least to me). In order to be equally useful in a client/server and web environment I stumbled upon the COM interface from Microsoft. This component based solution seemed useful in our case, so I tried it and with success. Using TCP/IP as a transport mechanism and COM objects to encapsulate data and/or business logic, we had a solution usable from within Visual Basic, Powerbuilder and the web all using the same components.
In the near future we will evaluate the COM-way against JAVA based solutions in order to communicate with CA-IDMS. On the CA-IDMS side one can use ADS dialogs or IDMS/DC COBOL routines. All these promising new possibilities to open up access to CA-IDMS where made possible just by implementing a TCP/IP listener.
You can write to Marc.Cobbaert@sd.be for more information or contact Formula OpenSoft Benelux B.V Tel: 31-78-6736341 Fax: 31-78-6736529 email: formulabls@compuserve.com Website: http://www.formulabls.com
IBM MQSeries is the leading messaging software of today. It provides asynchronous messaging between a variety of platforms and operating systems.
VEGA-90's MQSeries interface allows IDMS/DC/UCF applications to use IBM MQSeries functions. The MQSeries interface is an optional component of the VG-IDMS Gateway with the features below:
VEGA-90's MQSeries interface is implemented in co-operation with IBM Finland. It will be generally available at the beginning of March 1998. For details on these and other Vegasoft Products and Manuals see Vegasoft Oy
Schema Help from Burst Technology, creates a Windows help file from any IDMS schema. With virtually no preparation a complete cross-referenced list of areas, sets and records is created in easy-to-use Windows help file format. Schema Help provides hot key access to record, set and area information.
An optional no-cost feature puts the database diagram online with links to the RECORD, SET and AREA information. Links are created to one or many database diagrams, so the links go in both directions (e.g. from record to diagram as well as from diagram to record).
The above is a sample from the help on the System Catalog Record 'Table' (SYSTEM.TABLE for SQL Catalog).
The product is extremely useful and simple at the same time. We classify this as a brilliant product and recommend it to all IDMS Clients. The company has been generous enough to allow a free download of IDMSNTWK and EMPSCHM Help. See http://www.burstek.com/SH.html
The failed Ingres Gateway against IDMS Project, solely managed by CA Norway and technical bosses in CA Scandinavia has been at last declared a flop!
In spite of the fact that the underlying database was IDMS/MVS, no IDMS technician was ever seriously involved from the vendor in the implementation. A project of this nature can never be implemented by marketing /sales gimmicks alone!
The client a State Organization, has finally dropped the Gateway Approach and instead, decided to make the whole thing in clean IDMS/SQL mainframe with ADS and COBOL, Table Procedures and PC/ODBC Link from Windows/95/NT.
The client is making slow but steady progress in correcting SQL errors in 14.0. Also there are some programming issues related to deadlock between SQL and DML access to the same database in an application thread. Client is also at the moment trying to find out what exactly is an acceptable load time for his 70 million rows SQL Table!
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.
Get your own Free Home Page