IDMS/SQL News  
Vol 11.1     Technical Information for Mainframe and IDMSâ Users          January 2002

 

IDMS Release 15.0 is Available!

IDMS Manuals available in Adobe PDF format

IDMS Release 15.0 Manuals are available in Adobe PDF format now. This is at Computer Associates site. Contact your site's technical representative to get access to the manuals.

   
   
Finally here is the first 2002 issue of a much delayed IDMS/SQL News!  With sparse resources and contributions, we don't know how long we can continue!

Here is a well made Introduction to SQL for the DBAs. Click IDMS/SQL for the DBA for the slides.


 
Topics of The Week's Summary - Details at 2001-tow02.htm Not there yet
   
A layman's look at IDMS Utility Manual

ARCHIVE JOURNAL

The ARCHIVE JOURNAL utility offloads, to one or more archive
journal files, the entries in one or more disk journal files.
There are many options available. In this example we use the
READ option which directs the ARCHIVE JOURNAL utility to offload a single disk journal file without condensing it or marking it as empty afterwards.

This is the most harmless option here!

Note that this utility is normally run by System Programmer or Operations Staff Only. DBAs may also run it in conjuntion with recovery operations. Normal programmers with no experience should never submit this job!

Sample JCL
//S010         EXEC PGM=IDMSBCF,TIME=1,REGION=4096K            
//STEPLIB  DD  DSN=IDMS11.DBA.LOADLIB,DISP=SHR                 
//         DD  DSN=SYSTEM11.IDMS.LOADLIB,DISP=SHR             
//DCMSG    DD  DSN=IDMS11.IDMS.SYSMSG.DDLDCMSG,DISP=SHR        
//J1JRNL  DD DSN=IDMS11.J1JRNL,DISP=SHR,DCB=BUFNO=100         
//J2JRNL  DD DSN=IDMS11.J2JRNL,DISP=SHR,DCB=BUFNO=100         
//J3JRNL  DD DSN=IDMS11.J3JRNL,DISP=SHR,DCB=BUFNO=100         
//*
//ARCHIVE  DD DSN=IDMS11.TEST.JRNL.ARCHIVE,                   
//            DISP=(NEW,CATLG,CATLG),                          
//            DCB=(DSORG=PS,RECFM=F,LRECL=15476,BLKSIZE=15476), 
//            SPACE=(15476,(5000,1000),RLSE)                    
//SYSLST   DD SYSOUT=*                                          
//SYSJRNL  DD  DUMMY                                           
//SYSIDMS  DD  *                                              
DMCL=GLBLDMCL                                              
IDMSQSAM=ON                                                  
QSAMAREA=ARCHIVE.JOURNAL                                      
//SYSIPT   DD *                                               
CONNECT TO SYSTEM ;                                            
ARCHIVE JOURNAL READ;*
//
       

 

What happens if the Journals are not emptied?

If the journals are not emptied, a stage comes when no journal is available for the system. No database related tasks can be dispatched.
You will see messages like :

DC205013 V15 T1745860 All Disk Journals are UNAVAILABLE; Reasons below.
DC205014 V15 T1745860 ARCHIVE JOURNAL NOT yet RUN against full Journal J1JRNL
DC205014 V15 T1745860 ARCHIVE JOURNAL NOT yet RUN against full Journal J2JRNL
DC205014 V15 T1745860 ARCHIVE JOURNAL NOT yet RUN against full Journal J3JRNL
DC205011 V15 T1745860 NO Journals are AVAILABLE. IDMS Waiting
DC001003 V15 T1764106 TASK:OCF PROG:IDMSOCF STALLED WAITING FOR
JBCHECB AT 10B
DC027007 V15 T1764106 TASK:OCF PROG:IDMSOCF ABENDED WITH CODE D002

Here the OCF has abended waiting on JBCHECB. Typically IDMS will wait and will not abend in such a situation, but is stalled doing practically nothing. At this stage DBA can still submit the archive journal jobs and clear the situation. But if the journal wait is due to a long running batch CV job, archive journal will not work after a while. The only way-out here will be to cancel the CV job and allow rollback to take place. Because of this it is advised that long CV jobs must have periodic COMMITs to release the database as well as the 'lock' on the journal files.

 

GET QUEUE - 4404 and 4405

GET QUEUE ID 'Q123A100' KEEP INTO WD-DUMMY1
MAX LENGTH 100 .
DISPLAY TEXT ERROR-STATUS.

If there is no queue at all (QUEUE does not exist) then we get 4404.

If we change the code to

PUT QUEUE ID 'Q123A100' FROM WD-DUMMY1
LENGTH 100.
GET QUEUE ID 'Q123A100' KEEP INTO WD-DUMMY1
MAX LENGTH 100.

DISPLAY TEXT ERROR-STATUS.

Now we get 4405!

This will be puzzling to many. We just wrote a QUEUE and try to read it, and we don't find it? Here there is a queue record.
But the GET QUEUE will try GET NEXT by default. There is a current QUEUE record but there is no NEXT record.

If we change the code again to

PUT QUEUE ID 'Q123A100' FROM WD-DUMMY1
LENGTH 100.
GET QUEUE ID 'Q123A100' KEEP INTO WD-DUMMY1
MAX LENGTH 100 FIRST.

Then we find the queue record. Incidentally there is no GET QUEUE CURRENT though there is GET SCRATCH CURRENT!

 

Abend CODE 1117 in a Local Mode JOB

Abend 1117 happens when trying to delete a record fragment of a variable-length record, the DBMS was unable to adjust the space on a database page. The possible causes are a broken chain, corrupt page, or
running in retrieval CV with retrieval nolock specified in the sysgen.

But the following was from a LOcal Mode CV Job doing no updates.

09.42.18 JOB30275 +IDMS DC208001 IDMS job abending with abend code 1117
How can one get this in a local mode job doing no updates? In our case, the user ran the job again and then it ended 'peacefully'!


STRING / DISPLAY COBOL Statements in IDMSDC - Forbidden or NOT?

Olden days one was not supposed to use verbs like INSPECT, STRING/UNSTRING and DISPLAY in a DC-COBOL program.

This is relfected in the Manual DML Reference — COBOL Chapter 1.3.2 Executing programs

The Report Writer and Segmentation features, as well as features invoked by the
SORT, EXHIBIT, TRACE, ACCEPT, STRING, UNSTRING, and INSPECT
commands. The DISPLAY verb is generally not supported, although it will work
with the COBOL-II and LE-compliant COBOL compilers with restrictions as
mentioned in Appendix H, “VS COBOL II Support” on page H-1 and
Appendix I, “Considerations for IBM Language Environment” on page I-1. The
EXAMINE and TRANSFORM verbs, though valid under VS/COBOL, will not
compile under VS/COBOL-II since the logic of these verbs has been incorporated
into the inspect verb and are not valid verbs in COBOL-II.

But later in Appendix H on COBOL II

- STRING/UNSTRING/INSPECT — COBOL II verbs that require GETMAIN
services are supported; this includes STRING, UNSTRING, and INSPECT.
Note: Exercise caution with STRING, UNSTRING, and INSPECT, using them
may increase SRB time.
- DISPLAY — Output is sent to the IDMS Log. Although the verb is supported, it
is not recommended. The proper CA-IDMS replacement verbs should be used
instead.

In the case of INSPECT and STRING/UNSTRING, SVC Screening will intercept these
verbs and convert the GETMAIN to DC GET STORAGE. DISPLAY statement also will
be intercepted and the result which should have gone to SYSOUT will be routed to IDMS
Log with a message ID DC057005.

Eg: IDMS DC057005 V3 T14512024 SYSOUT ERROR-STATUS=0000

DCMT D MESS DC057005

<ddname> <message text>
A COBOL II, PL/I, or LE370 program has attempted to
put out a line of message text to DDNAME <ddname>.
THE CA-IDMS/DC/UCF system has intercepted the message and written it to the DC log file.
Module(s) = RHDCOSCR


SQL and INSERT into a Network Database

INSERT INTO POSTNET.POSTSTED VALUES (4034,'STAVANGER','STAVANGER',
11,3,'STAVANGER', ' ' , 2001, 10,10,0,0,0)
*+ Status = -4 SQLSTATE = 42606 Messages follow:
*+ DB005000 T63646 C0M324: Message for module IDMSOCF, SQL statement number :
*+ -1.
*+ DB005502 T63646 C-4M324: Table POSTSTED member of AUTOMATIC set. INSERT not
*+ permitted.

DB005502 C<sqlcode>M<module-number>: Table <table-name>
member of AUTOMATIC set. INSERT not permitted.
An attempt was made to INSERT a row into a non-SQL defined
record which is an AUTOMATIC member of a set with no
referential key definition. Review the rules for performing
SQL update statements on a non-SQL defined database.
Module(s) = JBITAB

 

Integer Overflow

The following table definition

CREATE TABLE IDMSSQL.SPACEREP
( RECID SMALLINT,
SIZE SMALLINT,
OCCURS INTEGER,
SPACE_USED INTEGER,
PERCENT_OF_USED SMALLINT
) ;
CREATE UNIQUE INDEX IDX1
ON IDMSSQL.SPACEREP
( RECID ASC )
;

insert into SPACEREP values (
5271, 60, 51636842, 3098210520, 88);
Status = -4 SQLSTATE = 22003 Messages follow:
DB005000 T24901 C0M324: Message for module IDMSBCF, SQL statement number : -1.
DB005162 T24901 C-4M324: Integer overflow

SPACE_USED is an integer. We are trying to store 3098210520, but the limit is 2147483647.

Solution is to defined SPACE_USED as LONGINT.

 

Order By and Union

Normally one cannot use ORDER BY when you use UNION.
For example, here we have two reports on similar data which we want to sort in a particular order and then combine into a single report using UNION.

SELECT * FROM IDMSSQL.DB10 ORDER BY 4 DESC 
UNION 
SELECT * FROM IDMSSQL.DB70 ORDER BY 4 DESC; 

*+ Status = -4 SQLSTATE = 42600 Messages follow: 
*+ Syntax error in line 1 at column 35, token = BY 
*+ DB006001 T35340 C-4M330: Unrecognizable token 

But the desired result can be achieved by using a simple UNION and then doing the sort on the result. This works!
SELECT * FROM IDMSSQL.DB10                                             
UNION                                                                       
SELECT * FROM IDMSSQL.DB70  ORDER BY 4 DESC;                              
*+                                                                          
*+  RECID NAME           SIZE      OCCURS      SP_USED 		 
*+  ----- ----           ----      ------      ------- 		    
*+   5271 COMMNETS          60    51636842    3098210520        
*+   5277 SERVICES          48    46444262    2229324576         
*+   1036 CALLS             80    21163310    1693064800         


Just one more!

A question of Terminology?

BBC had a kind of team quiz program in 2000 called 'The Weakest Link" where 10 guys are asked a series of questions, one after the other. With every correct answer the 'money' is incremented by 20, 50, 100 like that. In each round (about 3 minutes progressively reduced by 10 seconds), the team can accumulate upto 1000 British Pounds. But if one makes a mistake the chain is broken, and the running money falls to zero! As a precaution, participants can say 'BANK' and then the 'gained money' is safe in the bag. But the accumulation starts from zero again. After every round one person will be voted out. The last person left will take all the money. Though theoretically the final person can win 10,000 pounds, mostly the winning sum never exceeds 2-3000 pounds.

One of these programs was re-broadcast on BBC's European Satellite Channel 'BBC Prime' on 29/11/2001. There was a question: "What post did Ken Livingston get elected recently?" The participant answered : "The Mayor of London". The Moderator said: No, the answer I was expecting was 'London Mayor'! Amazing!!! For anyone with common sense the answer was correct with no ambiguity at all.

Late Eighties there was another quiz program on Super Channel where the question was on the country where Pol Pot had terror. The particpant answered: Cambodia. The moderator said: No, Kampuchea!!! Do we need to comment on this?

This issue located at http://www.reocities.com/idmssql/idms111.htm

IDMS/SQL is published quarterly on behalf of IDMS WatchDog Group, Scandinavia, Oslo-Helsinki-Hørsholm 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, VSAM and DB2 are registered trademarks of International Business Machines Corporation. Technical examples are only guidelines and modifications 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.