IDMS/SQL News              6.3

Vol 6.3        Technical Information for CA-IDMS Users                       September 1997

In this Issue

Headlines

Release 14.0 or 12.01 9707?
12.01 9707 APAR Tape
VSE tape news for 14.0
VM tape news for 14.0
Server 4.0 (NT Support) is GA

Potpourri

Discrash Real Recovery Scenario
R14.0 and CICS Interface
Data Complete not correctly set for LU6.2 - Rel. 14.0
Read Ahead Processing in LU6.2 - A Report

SQL in Practice : How to do Corner

SQL Tips against AREA Sweep
COMMIT, COMMIT RELEASE in linked SQL Dialogs
ODBC CCI-User Errors with Release 14.0
Tracing ODBC Problems
Webserver and Single Threading in CCI/PC


TCP/IP Socket Interface for IDMS
Swimming without Water
MQSeries Interface to IDMS
Courses in IDMS DB/DC

Located at http://www.reocities.com/SiliconValley/Vista/4905/idms63.htm

IDMS Release 14.0 has been in production.

Finnish client struggles with unsolved SQL errors and wonders why some of the elementary system errors were not discovered earlier in beta testing!

— Client Response

Newsletter from CA says 120 people from CA Scandinavia attended CA World.

One wonders why an IDMS Technician from CA was not among the 120, though from client side 30 in 330 were IDMS technicians!

More Swimming.. !

Release 14.0 or Release 12.01 9707?

While the clients were installing IDMS Release 14.0 at MVS sites, there was a general impression that 9607 was the last 12.01 tape. But now a new 12.01 APAR tape 9707 has been released!

This will definitely help those clients who want to wait for Release 14.0, at the same time want to be on new APAR level with the release. But at the same time this strategy is confusing! This reminds one of the time when IDMS 10.2 had two series of tapes - S1020x and S1021x series- at the time time!




Release 12.01 9707 Apar Tape for MVS!

CA-IDMS MVS 12.01 maintenance tape at genlevel 9707 has been released. The tape is C09707DOAM0 and this is only maintenance tape. There will not be any base tape at this level.

NEW on 9707

Release 14.0 For VSE

As of this date there is no target date for VSE/ESA14.0. It is currently in the works, according to sources in CA. Clients migrating to VSE/ESA 2.2, please note :
  1. VSE ESA 2.2 and IDMS 12.0x is compatible.
  2. Cobol For VSE is not supported until 14.0.
  3. LE will be supported in 14.0.

Release 14.0 For VM/ESA

A Danish client got the information that it is under development. Clients should go for 9607 VM/ESA tape which is available now. VM/ESA client is in production in Stavanger with strategic 24 hours OLTP application.

Server 4.0 GA for NT

Available now. Also Visual Express 1.1 is available (CD Rom Version) with OpenIngres Desktop as the local db.

Finnish IDMS Meeting

Spring Seminar was held on 26-28 May, on board Silja Line m/s Serenade between Helsinki and Stockholm. About 90+ participants attended the meeting. IDMS Users formally bid farewell to former IUA Finland Chairman Rauno Kokko who has joined ICL to start a new career. Timo Kotilainen was elected as the new Chairman of FIUA.

Diskcrash Real Scenario

- Arne HÝstad,System Tjenester, Oslo

Quite recently we had a disk crash in one of the production systems. IDMS came down immediately. And there was no way to recover the files from the damaged disk. We had to employ full recovery procedures Database Administration - section Backup and Recovery talks about recovery after a warmstart and recovery from i/o-errors, but not both at the same time. We used the following method to recover all the database files using backup and the journals.

  1. ARCHIVE JOURNAL READ (Database Admin. says ARCHIVE JOURNAL ALL but this is illegal after a CV-crash, you get DB005156 message)
  2. Restored affected areas (those on the disk with i/o-errors)
  3. Concatenated journals archived since the backup was taken
  4. ROLLFORWARD ALL against affected areas only
  5. ROLLBACK DMCL ACTIVE (here I used the concatenated journal as input, but maybe I could have used the last archived journal since the utility has the read the complete file backwards?).
  6. FORMAT JOURNAL ALL
  7. UNLOCK all areas in the DMCL (here I run the IDMSLOOK-utility against the DMCL to get all the area-names. Release 14 would be a little bit better since you can use the segment-names, but still you have to key in everything)
  8. Started IDMS with the initialized journals
Everything seems ok. But the manual should be corrected so that the information will be correct. It is very important to describe these procedures correctly.

Note that :

R14.0 and CICS Interface

The 14.0 CICS interface is not downwardly compatible to a release 12 backend. There are modules shared between CV and the mini-cv and this processing has been changed in release 14.

A circumvention:

Client can asm all UCF interfaces (IDMSINTC, IDMSINTL, IDMSCINT, IDMSCINL, and ∆UCFCICS) with R14.0 library. STARTUP CICS with R14.0 LIB. If there are more than one set of IDMSINTC and UCFCICS (all asm with R14.0 lib), this one CICS region can access both R12.01 and R14.0 at the same time. The same rule applies to IDMSINTL.

Data Complete not correctly set for LU6.2 - Rel. 14.0

- Arne HÝstad, System Tjenester, Oslo

Once again CA has violated the LU6.2-protocol with the setting of the Data Complete-bit when you receive several datablocks which has been sent with several SEND_DATA-commands. This is also mentioned in the homepage of IDMSSQL News at Release 14.0 :

"For LU6.2 APPC, data-complete flag PTF is required for 14.0. This ptf has been on and off ever since 10.2, 12.0 and 12.01 and classified as optional. But most, if not all, clients need this. Details, please contact local CA.".

For rel. 12.0 we got the apar GO27971 which implement what CA calls 'Read-Ahead-processing'. This was a permanent ptf and made the LU6.2 driver (RHDCD0EV) works correctly as documented in the SNA-documentation. For rel. 14.0 they have come up with an usermod TC82113. The problem is that this is made optional and is not implemented permanently in source. I will try to describe how the problem is experienced:

Partner A:		Partner B:

ALLOCATE		Task starts
SEND_DATA		
			RECEIVE_DATA
			(DataComplete is set)
			SEND_DATA
(DataComplete is not set)
			SEND_DATA
(DataComplete is not set)
			SEND_DATA w/DEALLOCATE

RECEIVE_DATA (DataComplete and Deallocate is set)
RECEIVE_DATA (no more data to receive)
RECEIVE_DATA (no more data to receive)
On the first RECEIVE_DATA (Partner A) you get all three blocks concatenated into one block and the second and third RECEIVE_DATA has no more data to receive.

How it should be handled:

The following sequence shows how it should be handled (and how it is handled by GO27971 and TC82113):

Partner A:		Partner B:

ALLOCATE		Task starts
SEND_DATA			
			RECEIVE_DATA 
			(DataComplete is set)
			SEND_DATA
RECEIVE_DATA (DataComplete is set)
			SEND_DATA
RECEIVE_DATA (DataComplete is set)
			SEND_DATA w/DEALLOCATE
RECEIVE_DATA (DataComplete and Deallocate is set)
Here we receive the data as it was sent; in three separate blocks.

This problem is important to all those who runs LU6.2 and try to send more than one block at a time (for instance a file-transfer).

IDMS/SQL thanks Arne HÝstad. In Finland, APPC expert Onni Kukkonen, Vegasoft Oy experiened similar problems with several customers with 12.01 and 14.0. Based on various discussions in IWDG through internet, the following report is published.

LU6.2 Read-Ahead Processing

- IDMS/SQL Special report

What is the issue:

APPC means the advanced program to program (peer to peer) communication using SNA LU6.2 protocol defined by IBM. Typically two programs residing in two different environments communicate each other as in a telephone conversation. So when one talks the other one listens. Environments can be IDMSDC - CICS, IDMSDC - OS/2, IDMSDC - VAX etc.

In the case of programs, talk-listen situation gets translated into SEND/RECEIVE. So when one program issues a SEND, there should be a corresponding RECEIVE in the other program. Synchronization is very important and is achieved by coding techniques and testing of some flags.

For example:

The scenario is as follows

Server LU		Client LU 
(IDMSDC or CICS:..)	(IDMSDC, CICS...)

SEND_DATA Buffer	 (expect data_complete, not set)
SEND_DATA Buffer           (expect data_complete, not set)
SEND_DATA Buffer	RECEIVE_and_wait buffer 
			(last buffer, data_complete set)
			RECEIVE_and_wait buffer 
			RECEIVE_and_wait buffer 
LU6.2 Client (IDMS 12.0) claims that 'DATA COMPLETE' is not set, when they are sending multiple block on an LU6.2 line. The program expect a 'DATA COMPLETE' are set after each block of data, but the 'DATA COMPLETE', are only set after the last block are confirmed. This means that the first RECEIVE-AND-WAIT will get all the data sent by the 3 SEND-DATA command. As such, the program will not be able to handle it, unless you have further logic on WHAT-RECEIVED etc.

Client had a similar problem on their 10.21 system when ptf 92-02-1002 were applied. When they backed off the ptf the problem disappeared. It seems the changes in Release 12.0 LU6.2 driver re-introduced the problem.

We are looking at what is known as READ-AHEAD processing in LU6.2/VTAM communication. there is no "error" here, but a decision on consistency in protocols across systems. Let's go back to 10.21 level.

History from 10.21

At this point, it is obvious that there has not been a decision on read-ahead code. Finally it was decided that the read-ahead code was bypassed in driver since there seem to be fewer problems without it. Those who seem to need it was given an optional APAR to put the read-ahead code back in.

Optional APAR GS87952 dated 26 FEB 1996, enables the Read-Ahead processing feature of the VTAM LU SNA driver. Some time later TC82064 was given to back out the above apar (ie removes the read-ahead processing).(Several APARs appeared doing the same thing - activating and de-activating the READ AHEAD processing).

Release 14.0 - again read-ahead was removed from the driver. For R14.0 clients they need optional APAR TC82113 or its equivalent published version to enable Read-Ahead processing in VTAMLU driver.

Bottom Line

Confusing? Need not be. The bottom line is not changed. For 12.01 and 14.0, by default, READ AHEAD processing is not in the LU6.2 driver. Most clients do not need this. But if you need this functionality, then optional APARS are available which will activate the READ AHEAD processing. Having said that, IDMS Client (Norwegian Telecom) tells us that the information they have from CA says that TC82113 will be sourced in a future 14.0 tape. That is the default will be that the READ AHEAD processing will be in the driver of 14.0!

SQL in Practice


SQL Tips against AREA Sweep

When you create a table in a new area and try to do a
SELECT * from schema.table_name;
by default, IDMS 12.0 will use area sweep. And it can be very slow or fast depending upon the size of the area. For example, a client had a large sql area defined, 700,000 pages, sample table has just 100 rows. A SELECT * on this table, with no criteria, took 50 minutes wall clock time (in IDMS 12.0). This will be very frustrating when one has just started making an IDMS/SQL database. (And disastrous during an online LIVE demo!)

What is wrong here ? How can you avoid this? In 12.0, you can do either

1) ALTER TABLE schema.table_name ESTIMATED rows 100; or
2) do an UPDATE STATISTICS for TABLE schema.table_name; Then instead of area sweep, system will use the default index to access the table and response will be only a fraction of a second.

Release 14.0 Update

We have information that in the above situation IDMS Release 14.0 will always avoid an area sweep and use the default index even without giving either of the above statements!
                    OCF 14.0 ONLINE IDMS NO ERRORS                       
EXPLAIN STATEMENT 'SELECT * FROM IDMSSQL.A1'
INTO TABLE IDMSSQL.ACCESS_PLAN; 
where IDMSSQL.A1 is the test table.


SET OPTIONS COMPRESS ON;
Status = 0        SQLSTATE = 00000
   SELECT TSCHEMA, TABLE, SECTION, COMMAND, ACMODE, ACNAME
                  FROM
   IDMSSQL.ACCESS_PLAN;

TSCHEMA		TABLE	SECTION	COMMAND	 ACMODE	 ACNAME

 IDMSSQL	 A1	0	25	S	ROWID01047
where ROWID010 is the default index automatically defined by IDMS system at table creation time. This is sorted on DBKEY.

COMMIT, COMMIT RELEASE in linked SQL Dialogs

We have the following situation where a non-SQL dialog links to an SQL dialog repeatedly to get some information. The question is whether we should have a COMMIT in the second dialog or not! The second dialog is retrieval only. Let's monitor these dialogs with ADS/ALIVE.
   ___________
  !           !
  !           !
  !  DLG29    !  Native DML only
  !           !
  !___________!
	!
	! LINK
   _____!______
  !           !
  !           !
  ! DLG30     ! SQL Only
  !           !
  !___________!

Source of First dialog

      ADD
     PROCESS NAME IS PDLG29-RP1 VERSION IS 1
         MODULE SOURCE FOLLOWS
READY ALL USAGE-MODE IS RETRIEVAL.
MOVE 5300 TO DEPT-ID-0410.
OBTAIN CALC DEPARTMENT.
MOVE 1  TO I1.
WHILE I1 < 20
REPEAT.
 LINK TO 'DDLG30'.   -----> source below
ADD 1 TO I1.
 END.  §REPEAT  END
DISPLAY TEXT 'HERE WE ARE'.
         MSEND.

Access Module DDLG30

LOOK AM=DDLG30 (Partial output follows)
Access Module=DDLG30   Schema=IDMSSQL
      Version=1      Compiled=1996-10-15-06.11.34
              2,504 Bytes in Access Module DDLG30
                         AUTO RECREATE OFF
                         VALIDATE BY ALL
                         READ ONLY
                         DEFAULT ISOLATION TRANSIENT READ
 
          RCM=DDLG30   Dictionary=APPLDICT
      Version=0          Compiled=1996-10-15-06.11.11
            CRC Section=2      Compiled DML command (run tree)
                   Command=25 SELECT
 
      SUBSCHEMA=IDMSCATY             DMCL=R120DMCL
  Compiled=96-10-15  06.11.34    Date Last Critical Change=96-04-04  09.42.47
 
           Subschema Structure is Relational and Bound
             ......      

Case 1 : No COMMIT in the second dialog

After every link to the SQL dialog we see that a new SQL run unit is created. Also RETURN command does not result in the release of the run unit.
 000006     MOVE 'APPLDICT' TO WS-DBNAME.
 000007     EXEC SQL
 000008     CONNECT TO :WS-DBNAME
 000009     END-EXEC.
 000010     IF SQLCODE NE 0 THEN DO.
 000011     MOVE SQLCODE TO WS-EMPID.
 000012     RETURN.
 000013     END.
 000014     EXEC SQL
 000015     SELECT EMPID, EMPNAME
 000016     INTO :WS-EMPID, :WS-EMPNAME
 000017     FROM
 000018     IDMSSQL.YLEMP
 000019     WHERE EMPID= 999
 000020     END-EXEC.
 000021     IF    SQLCODE NE 0
 000022     MOVE  SQLCERC TO WS-EMPID.
 000023
 ==MSG> ..............NEXT LINE TO EXECUTE FOLLOWS..............
 000025     RETURN.
 ****** *** BOTTOM OF DATA ***************** CA-IDMS/ADSALIVE ***
After about 10 links and return wee see that
 LTERM Id  User Id   Task Code Task Id  Stg   Space  RUs  S/NS   Notify  RCEs
  LV12001   KESA01   ADS2       806     369k  344k   12   19      6     188
  LV12002   DLG      OPER       807     13k   20k     0    0      0       9
  USNWYKP1           +Idle+       0      0k    0k     0    0      0       0
   
 TASK ID ORIG IDMSPROG SUBSCHEM PRI STA V∆ PAGEREAD PAGEWRIT CALLIDMS LOCK-REQ
00000806 DBDC DDLG30   IDMSCATY 100   A 10 00000000 00000000 00000004 00000001
00000806 DBDC DDLG30   IDMSCATY 100   A 10 00000000 00000000 00000004 00000001
00000806 DBDC DDLG30   IDMSCATY 100   A 10 00000000 00000000 00000004 00000001
00000806 DBDC DDLG30   IDMSCATY 100   A 10 00000000 00000000 00000004 00000001
00000806 DBDC DDLG30   IDMSCATY 100   A 10 00000000 00000000 00000004 00000001
00000806 DBDC DDLG30   IDMSCATY 100   A 10 00000000 00000000 00000004 00000001
00000806 DBDC DDLG30   IDMSCATY 100   A 10 00000000 00000000 00000004 00000001
00000806 DBDC DDLG30   IDMSCATY 100   A 10 00000000 00000000 00000004 00000001
00000806 DBDC DDLG30   IDMSCATY 100   A 10 00000000 00000000 00000004 00000001
00000806 DBDC DDLG30   IDMSCATY 100   A 10 00000000 00000000 00000004 00000001
00000806 DBDC DDLG29   EMPSS01  100   H 54 00000000 00000000 00000005 00000008
00000010 DBDC RHDCLGSD IDMSNWK9 253   A 56 00000000 00000000 00000003 00000001
00000009 DBDC RHDCLGSD IDMSNWK9 253   A 56 00000000 00000000 00000003 00000001
00000008 DBDC RHDCLGSD IDMSNWK9 253   A 56 00000000 00000000 00000003 00000001

CASE 2: The same with COMMIT before return.

Noticeable redcution in storage and RCEs.
 PROCESS: PDLG30-PM
    ... 
    first lines same as above
    ....
     EXEC SQL
     COMMIT -- JUST COMMIT, NO RELEASE
    END-EXEC.
 ..............NEXT LINE TO EXECUTE FOLLOWS..............
     RETURN.
* *** BOTTOM OF DATA ******************************** CA-IDMS/ADSALIVE ***
  LTERM Id  User Id   Task Code Task Id  Stg   Space  RUs  S/NS   Notify  RCEs
  LV12001   KESA01   ADS2       887     248k  147k   2    9      6       138
  LV12002   DLG      OPER       826     13k   20k    0    0      0         9
  PROSLOQ1            +Idle+    0       0k    0k     0    0      0         0
  PROSLOP1            +Idle+    0       0k    0k     0    0      0         0
   
After COMMIT but before RETURN. This was about 5 iteration of DDLG30.

CASE 3: Explicit COMMIT Release

Now the storage and RCEs are even less.
    EXEC SQL
     COMMIT RELEASE  -- EXPLICIT RELEASE
    END-EXEC.
    ..............
    RETURN.
                                               Pgm   DB     DB Locks
  LTERM Id  User Id   Task Code Task Id  Stg   Space  RUs  S/NS   Notify  RCEs
  LV12001   KESA01   ADS2       921     158k  100k   2    9      6       107
  LV12002   DLG      OPER       826     13k   20k    0    0      0         9
  PROSLOQ1           +Idle+       0     0k    0k     0    0      0         0
  PROSLOP1           +Idle+       0     0k    0k     0    0      0         0
  
The display is at about 5 iteration.

In case2 and case3, there is only one SQL run unit.

TASK ID ORIG IDMSPROG SUBSCHEM PRI STA V∆ PAGEREAD PAGEWRIT CALLIDMS LOCK-REQ
00000921 DBDC DDLG30   IDMSCATY 100   A 10 00000000 00000000 00000004 00000001
00000921 DBDC DDLG29   EMPSS01  100   H 54 00000000 00000000 00000005 00000008
00000010 DBDC RHDCLGSD IDMSNWK9 253   A 56 00000000 00000000 00000003 00000001

Conclusion:

In the above case the second dialog is a read-only dialog. If it had been an update dialog, it would have deadlocked in the "case 1" above.

Using COMMIT, COMMIT RELEASE all depends upon what you want to do in the second dialog.

ODBC Access and CCI-User Error

-Tarmo Kumpulainen, Finnish Broadcasting Co, Helsinki

After 14.0 install ODBC access started giving crazy errors! Everything has been working in 12.01 tape.
While connecting VISUAL EXPRESS (or any other ODBC product, we got the message
DC_SYSTEM CCI-USER NOT DEFINED TO SECURITY.
CCIDCH follows. This message comes in the IDMS log.

Finally it was found out that the module RHDCD0LV has been linked incomplete by the Release 14.0 Install! There is a missing CSECT in the link of RHDCD0LV. The missing CSECT RHDCD0LB is not delivered with the IDMS installation. It is delivered only with the CA-Server installation. When you link RHDCD0LV, you have to make sure that you pick up D0LB from the SERVER DISTLIB.

Obtain RHDCD0LB from CASERVER install and include it in RHDCD0LV link. This will resolve the CCI - USER not defined error.

IDMS/SQL adds: Some clients had got this error in 12.01 also. It is mysterious why the missing module is not linked part of the IDMS Install. Afterall, the whole load module is only 22K in size!

Tracing ODBC Problems

- Docup
To turn on the CA-IDMS Server 3.0 trace file, follow these 6 steps:

 1.  Enter the ODBC Administrator.
 2.  Select the IDMS Data Source, click CONFIGURE.
 3.  Select OPTIONS, Set Log Options, click SETUP.
 4.  Enter a log file name, typically CAIDDSI.LOG, CAID0916.LOG, etc.
 5.  Select APPEND and click the CLIENT TRACE options:
          ODBC, SQL, DTS and DTS-CCI.
 6.  Click OK, OK and EXIT...run the PC Client product.
 
When you created this file, and run your Visual Express test.

Single Threading in CCIPC

Several Clients here have been successfully using ODBC access against IDMS. The flow of logic from PC side is roughly as follows:

Windows 95 -->IDMS/ODBC Driver -->CCIPC .... CCIDriver/MVS -->IDMSDBMS -->IDMS Data
  

It has been reported that the CCIPC module which provides the transport between the PC and the mainframe server is single-threaded, although it supports multi-user environment. Concurrent requests are queued up while one executes. In effect, this may slow down the response in a multi-user environment.

Note 1