It is't in the manual

IDMS/SQL News

There is growing concern among Release 12.01/ 14.0 clients about the technical support in general and commitment to SQL in particular! Several outstanding SQL problems are still unsolved. Recently a Finnish client was forced to ask the vendor: "Who is your SQL person in Europe?" "Kuka han on?" There was no answer!

Many clients here in Helsinki, Turku, Tornio, Oslo, Bergen, Copenhagen, Sweden ... are seriously running / developing production applications using modern front-end tools, ODBC, TCP/IP and the IDMS database at the backend. Instead of getting practical help on existing products and issues, clients are lectured on 'future' solutions with untested and unproven sub-products!

Send your contributions to : idmssql@reocities.com

Scandinavian Corner

We are starting a Scandinavian Corner to discuss special issues facing Scandinavian clients in Norway, Finland, Denmark and Sweden.

The discussion will be carried out in a mixed environment, even mixed languages - Finnish, Norwegian, Swedish, Danish and English. We will try to translate the main points to English whenever it is relevant and possible.

So all clients, consultants and readers are requested to send the material in Finnish, Swedish, Norwegian, Danish or English to this new column. Some of these may be later published in SQL News itself.

Skandinaavisen keskustelukulma

Aloitamme skandinaavisen keskustelukulman, käsittelemään Norjan, Suomen, Tanskan ja Ruotsin asiakkaiden erityiskysymyksiä.

Keskustelu käydään sekalaisessa ympäristössä, myös kielellisesti - suomi, norja, ruotsi, tanska ja englanti. Pyrimme kääntämään määrätyt kohdat englanniksi kun on aihetta ja mohdollista.

Joten kaikki Suomen asiakkaat ja lukijat pyydetään lähettämään aineistoa - suomeksi, ruotsiksi tai englanniksi tähän uuteen kolumniin. Jotkut näistä tullaan ehkä julkaisemaan SQL News lehdessäkin myöhemmin.

Skandinavisk Hjørne

Vi skal starte ett Skandinavisk Hjørne for å diskutere spesielle saker (problemer) angående Skandinaviske kunder i Norge, Finland, Danmark og Sverige.

Diskusjonen vil bli gjort i mikset miljø, til og med i mikset språk - finsk, norsk, svensk, dansk og engelsk. Vi vil prøve å oversette de viktigste poengene til engelsk når det er meningsfullt og mulig.

Så, alle norske kunder og konsulenter er ønsket til å sende materiale på norsk, svensk eller engelsk til denne nye spalte. Noen av disse vil kanskje komme på trykk i selve "SQL News".

Current General Issues

Readers are warned that this is not an exhaustive list of IDMS problems, but something our Scandinavian clients have come across in recent times.

Broken Chains after Batch-CV Job

A client in Oslo had a long standing problem with a mix of CV batch and local jobs getting broken chains in very special situations. Re-running the job with backup usually worked ok. The problem has been associated with the dynamic change in the buffer pool value after CV startup. Contact CA for the solution for 12.01 and 14.0. [Solved Sept 97]
Product: IDMS Problem #1915
Reported Rel: 12.0

When the number of pages in a buffer pool is varied to a higher number the bit tables associated with that buffer may ignore some pages near the end of the pool. As a result any action against an area that uses that buffer pool and causes a purge of pages may omit some pages from the operation. An example of this would be varying an area from retrieval to update. All pages for the area should be purged from the buffer pool but this problem can result in some of the pages remaining active in the pool at the completion of the DMCT VARY command. This could result in incorrect application results and potential broken chains if these pages are involved in an update operation.

SQL Issues

where more documentation is needed
  1. Unload and Reload of an SQL database

  2. How to refer to host variables in an ADS dialog typically when the SQL tables are included through the ADSC Record /Table Definition Screen (This will be discussed later)
1. IDMS/SQL Preliminary Report on Unload and Reload of SQL databases

First of all SQL database areas can be UNLOAD/RELOAD-ed using the same utility used for network databases. There are some differences and restrictions.

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-name
Note that the 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.

  • Unload/Reload 'operation' itself may work if we use two DMCLs
  • But runtime will fail because of timestamp mismatch between the catalog and the database. The catalog will have the 'old timestamp' and the database timestamp will be 'new' coming from the production database.
  • There is no way to unload/reload the 'partial catalog' portion of our database in question.
  • Some people have suggested a crude 'circumvention' of pfixing the database timestamp with that of the target catalog.
  • This will work only if the tables also have the same table definition number (TDN) and in the right order. In practice and in large production systems this cannot be guaranteed!
[We will try to get an example of timestamps, if time permits]

Year 2000 Conversion with Builtin Functions in ADS

The following is a problem faced by a client in Finland in recent days:

We are using substring and concatenate built-in-functions in ads to manipulate date fields. When changing 9(6) date fields using builtin function substring and concat, leading zeroes are converted to blanks producing erraneous results. For example, yymmdd to ddmmyy change from '000909' gives '09 9 ', when result fields is x(6). When result field is 9(6) then result is '000009'. This happens even with cobol moves=yes option.

 ads statement :
 move
  con(sub(w-date,5,2),
  sub(w-date,3,2),sub(w-date,1,2)) 
 to w-date2
 where w-date Pic 9(6).
 
We found that ads performs leading zero suppression before giving the field to substring function, because it moves w-date to another location without following cobol moves=yes rules Basic need is to convert date handling in ads dialogs to year 2000 compatible with minimum effort, without changing date field formats, only changing cobol moves option in ADSC to yes.

[Continued on the next column on the right]

case 1:
 MOVE '000909' TO W-DATE.
 MOVE 
 CON(SUBS(W-DATE,5,2),
        SUBS(W-DATE,3,2),
        SUBS(W-DATE,1,2))
 TO W-DATE2.
 
 On gets '09 9  '
 --------------------------------
Case 2:
 MOVE '000909' TO W-DATE1.
 MOVE
 CON(SUBS(W-DATE1,5,2),
        SUBS(W-DATE1,3,2),
        SUBS(W-DATE1,1,2))
 TO W-DATE.

One gets '000009' 
 
The fields used are 
     02 W-DATE
          PICTURE IS  9(6)
     02 W-DATE1
          PICTURE IS  X(6)
      02 W-DATE2
          PICTURE IS  X(6)
One Technician proposed the following solutions:
1. Use translate function
 MOVE '000909' TO W-DATE.     <----the field is 9(6)
 MOVE
  TRANS(CON(SUBS(W-DATE,5,2),
   SUBS(W-DATE,3,2),SUBS(W-DATE,1,2)),'0',' ')
 TO W-DATE2. MOVE W-DATE2  TO WS-X6.

The above works allright and will give the result as '090900'.
2. another might be to use the input x(6), after moving the date in 9(6) field to x(6) field. Again you might want to do the things with least possible coding and errors.
3. Third option which is not yet investigated is the usage of DATECHG bifs.
Has anyone any other solution?

20/Oct/97 Update A senior technician informs us that DATECHG bif also can be used. We' ll have the details in a few days.

COBOL/370 Online?

A client from Oslo reports that COBOL/370 subroutine usage seems to be extremely slow in online environments. The exact reasons are unknown at the moment, though we believe that it is not due to the execution time of the COBOL code itself. The complete scenario follows:
  _________
 !         !
 !  ADS    !   Main dialog
 !_________!
      !
      !
  ____v____             _________
 !         ! pgm1      !         !pgm2
 !  COBOL  !  ------>  ! COBOL   !
 !_________!           !_________!
COBOL/370 pgm with sql access also calls another COBOL subroutine with no database calls. The ADS dialog has no SQL calls. It is mainly a driver dialog for display purpose. The SQL calls are in COBOL pgm1, which is called repeatedly by the dialog to fill the map. The whole thing was slow.

Just for testing, the client replaced the COBOL code with an ADS dialog and the response was very fast. Also in OCF the corresponding SQL query was even faster. Obviosuly the problem points to COBOL. But we know that the COBOL code is fast. The culprit seems to be

The environment setup for LE/370

When COBOL II was introduced it was found that COBOL II environment setup was too heavy for online systems. Many IGZ.. modules were loaded 100s and 1000s of times repeatedly. This was finally circumvented by IDMDC by intercepting those calls for IGZ... modules. Here in COBOL/370 there are new modules starts with CEE... The client has checked up every one of them, they are loaded onlu once (at least according to IDMSDC DCMT program statistics). But at least one of them - IBM LE/370 module CEEPLKA - seems to be the biggest module in the IDMS system - 689K! This has been called for every link to the COBOL program. A problem has been reported to CA as investigations continue.

latest 1/11/97 Various problems are reported with LE/370 runtime support. See guidelines in LO06374 and LO06375. Performance issue has also been addressed by a new PTF. Contact CA.

If any of the readers know more about COBOL/370 in general or COBOL/370 in IDMSDC or CICS environment, please let us know. What is the purpose of CEELPKA module?

IDMS/SQL Final report will appear here.

Suomalainen keskustelukulma

Note: The following comments are in Finnish. We might translate the relevant ones to English as time permits.

Referential Integrity Violation

Release 12.0:ssa oli virhe IDMS/1688 Linkkaamaton rajoite, jota käytti yksi tai useampi tauluun viittava NULLABLE-sarake, saattoi sallia poistoja, jotka rikkoivat rajoitetta. Paras tapa kiertää tämä oli tehdä avainsarakkeet NOT NULL. Tämän piti olla korjattu 14.0:ssa, mutta se esiintyy yhä.
Details of this error was discussed in IDMS/SQL News 5.3 Our Response will appear here

Mixed Index

Toinen virhe on ongelma IDMS/1355 Indeksit, joilla on sekalainen(mixed) tai laskeva lajittelujärjestys aiheuttavat arvaamattomia tuloksia SQL:ssä, esim. tietokannan rivejä ei löydy. Tämä voidaan tilapäisesti kiertää lajittelemalla indeksit nousevaksi. - eli lyhyesti: ei käytetä sekalaista (mixed) lajittelujärjestystä. Tämäkin virhe on vielä 14.0:ssakin.

Nämä virheet ovat SQL-käytön kannalta oleellisia. Ensimmäinen näyttää olevan looginen virhe DBMS-ytimessä koskien viite-eheyttä. Toinen on vakava optimoija-häiriö, joka antaa aivan vääriä tuloksia (ei virheilmoituksia, joten käyttäjä ei ole tietoinen vakavasta virheestä SQL-SELECT tuloksessa).

IDMS/SQL Response will appear here

Kokemuksia IDMS Release 14.0:sta

Martin Floman, Senior DBA, Oy Yleisradio AB, Helsinki 1997-10-28

Miksi halusimme kiirehtiä installointia?

  • Language Environment
  • Vuosi 2000

SQL-kokemuksia

  • Installoinnin yhteydessä ajettiin sisään n.10 CA:n ilmoittamaa korjausta
  • Katalogien konvertointi (CONVERT CATALOG: APPLDICT ja SYSTEM) oli nopea (pienillä katalogeilla)
  • Muutama vanha rel. 12.0:ssa toimiva ohjelma ei toiminut
  • Access Modulin (AM) luonnissa ongelmia mm. kursoreiden kanssa (CA tutkii)
  • Dynaaminen SQL aiheuttaa tietyissä tilanteissa (bulk option) suoritusaikaisia virheitä (CA tutkii)
  • ADS-dialogit abendoivat link-komennolla (korjattu TF24058:lla)
  • Load kaatuu DUPLICATES VIOLATION virheeseen, jos UNIQUE INDEX:n avaimen arvona on 0 (CA tutkii)
  • DISPLAY-komento hyvä näyttäen esim. taulumäärittelyn syntaksin, indeksit, constraint:t jne - tosin ongelmia suojausten kanssa (sallittu vain SYSADMIN-oikeudella? - CA tutkii)

Muita havaintoja SQL:n käytöstä (myös ennen rel. 14.0)

  • Skandinaaviset kirjaimet eivät aina toimi esim. tauluproseduureissa, konkatenoinnissa tai muissa ’built in’-funktioissa (CA tutkii)
  • AM:n automaattinen regenerointi ei välttämättä tallenna tulosta tehtävän loputtua – riippuu mm. käytetystä COMMIT-komennosta. (dokumentointi manuaaleissa epäselvä – ks. IDMS/SQL NEWS)
  • Utiliteettien puute ongelmallinen SQL-käytön lisääntyessä. Esim. varmistusten/palautusten tai unload/reload:n teko täysin ’omalla vastuulla’ perinteisin välinein ja tarkemmat ohjeetkin puuttuvat. Aikaleimojen ja taulujen määrittelynumeroiden, jotka ovat fyysisesti sekä katalogissa että tietokanta-alueella, mennessä ’sekaisin’ saattaa DBA joutua korjaamaan niitä ’FIX PAGE’:lla (CA:n pitäisi olla tietoinen puutteista)
  • RCM:n (Relational Command Module) lataus saattaa epäonnistua OCF:ssä (CA tutkii, shutdown auttaa).
  • Indeksiavainten järjestyksen ollessa sekä DESC että ASC saattaa SELECT:n tulos olla väärä varsinkin jos käytetään päivämääriä (CA tutkii)!
  • OLQ toimii myös relaatiotauluilla (SET ACCESS IDMS), suojaukset samat kuin OCF:ssä. OLQ:lla voi myös päivittää tauluja!
  • Isot päivittävät eräajot (kuten esim. load-ajot) kannattaa ajaa localina tk-alueiden ollessa CV:ssä OFFLINE/RETRIEVAL-moodissa, päivitysten jälkeen tehdään UPDATE STATISTICS ( BCF:llä CV:n kautta alueiden ollessa RETRIEVAL-/UPDATE-moodissa). CA:n mukaan ainakin rel. 14.0:sta lähtien UPDATE STATISTICS ei tarvitse (eikä voida) tehdä alueelle, joka sisältää vain indeksejä, koska indeksit käsitellään automaattisesti vastaavien taulujen yhteydessä. Alueiden varioinnit OFF/RET/UPD voi tehdä UCF-stepissä.
IDMS/SQL has asked an SQL expert to make comments on these issues. Our report will appear here


This page is located at http://www.reocities.com/idmssql/scand1.htm
Back to Main Page 1