Managing Tables of Data in CICS By Fred Schuff 1750 words deck: CICS provides some excellent facilities for the storage and management of temporary tables of data, something essential when handling online applications which are often difficult to manage and costly to move from task to task. Online applications often have to manage significantly large volumes of data -- too much data to just stuff into local storage areas because they are cumbersome to manage and expensive to pass from task to task. Under CICS, the normal mechanism for passing data is the COMMAREA and the general consensus is to limit the COMMAREA to a reasonable size, usually less than 8 KB. Yet applications often encounter relatively large amounts of data which they must process for a period of time without storing that data permanently. One such case is data selected from the database to display for the user. The amount of data could be one or 10,000 records depending upon the application. There are several techniques used to handle this type of situation. First, only fetch "n" records and save the "keys" of the first and last record displayed. To scroll forward, fetch the next "n" records using the value of the last record displayed. To scroll backward, fetch the previous "n" records using the value of the first record displayed. This is fine if your DBMS supports this type of access, like IMS or VSAM. However, with a DBMS like DB2, this is not so simple. A set of values is constructed when a CURSOR is opened rather than just positioning within the database. The FETCH will return the next record of the "n" record set. This means that most of the processing will be done each time the CURSOR is OPENed to the database even though only a small subset of data is used. This type of application lends itself to storing all of the retrieved data and then scrolling through the table of data using a much cheaper service provided by some Data Manager. CICS provides some excellent facilities for the storage and management of temporary tables (not to be confused with DB2 tables) of data. The ability to create, modify and retrieve this data makes application development easier and more flexible. One method is to use MAIN storage (memory) through the GETMAIN service. A second is to use TS-Queues (Temporary Storage Queues) which can use MAIN storage (memory) or AUX(illary) storage (DASD). A third method is to use VSAM files but this is more difficult to manage because it requires definition of the VSAM files to CICS. Therefore, it is not addressed in this discussion. The decision was to use TS-Queues because the amount of storage required was variable and could become significant, 100 KB or more. The TS-Queues would be backed by AUXiliary storage rather than MAIN memory. CICS, via VSAM, uses Expanded Storage for the data until it is required to roll that data off to real DASD. TS-Queues also persist across transaction boundaries so the data was not lost between displays in the customary pseudo-conversational design. GETMAIN with HOLD would also persist but tasks which did not clean up the GETMAINed storage would have a bigger impact on the total system that TS-Queues which were not DELETEd. The development of a "service routine" to manage tables of data was required to alleviate the need for this management in each application program. Secondly, direct access to native CICS services was not available under NATURAL. Thirdly, the data had to be sorted, or re-sort for presentation to the user. And finally, each concurrent user's data had to be independent of each other user's data. Even running under native CICS, it was prudent to externalize that service from each application into a Data Manager service routine. SERVICE ROUTINE FACILITIES The service routine was fairly simple -- except that it was coded completely in COBOL-II. There are six functions: CREATE, DESTROY, STORE, FETCH, RESET and SORT. CREATE -- Initialize the control information in a "Token" which is 96-bytes of user-supplied storage. The Token contains information to process the data table from request to request (this will be discussed later). Create also performs a DESTROY. DESTROY -- Delete the TS-Queue(s) for this data using "DELETEQ TS". STORE -- Store a record in the TS-Queue either in sequence or randomly using the record number, or item number, of the data record. FETCH -- Fetch a record from the TS-Queue either sequentially or randomly by the record number of the data record. The ability to fetch "n" records at a time was also provided to optimize processing where pages of data would be displayed. Records could be fetched either in the original record sequence, as they were stored, or in a sorted sequence (see SORT). RESET -- Resets the sequence pointer in the Token so sequential retrieval of records would begin at the first record location. The original sequence pointer or one of the sorted sequence pointers can be reset. SORT -- Sort the records into a user-defined sequence from data fields within the stored data records. This sort process is executed after all of the data records are STOREd in the data table. Multiple sorted sequences can be created for each data table. The TOKEN provides a simple means to pass data from the service routine to itself across multiple calls from the application environment. This is necessary to maintain information about the data tables during the processing. For example, how many records are stored in the data table at any time? That counter is in the Token area and maintained by the service routine. The only possible problem is that the Token could be altered by the application program and therefore result in processing errors or even ABENDs. The SORT process allows for keys to be constructed from one to five data fields in the data record. Each field can be from one to 64 bytes and can be sequenced in Ascending or Descending order. The total length of a key can be up to 64 bytes. The number of segments, five, and the total length of the key, 64, are completely arbitrary values. Several other built-in functions can be added to the Data Manger. A FETCHB, Fetch Backwards, would allow processing to be in reverse order. This can be accomplished by requesting records by record number and decrementing the counter rather than incrementing it. A Previous "page" of data would be retrieved by subtracting the page size from the record counter and fetching those "n" records. Another option is to provide an FETCHF, Fetch with Filter, to fetch the next record with a specified value, or Filter, occurring within each row. THE TS-QUEUE(s) Each TS-QUEUE has an 8-character name. For each user to have a separate queue, the normal convention is to use four characters in the name to hold the terminal identifier (tttt) which is in the CICS Environment Information Block (EIB). One of the remaining characters is used to allow multiple queues for each data table. Therefore, backing into it, the data table has a unique 3-character name (XXX) assigned by the application developer. The multiple queues result from the sorting capability. The queue sequence number "0" contains the STOREd data as loaded by the application. Queues "1", "2", "3"... contain the information to return the records in sorted sequence. The sorted data TS-Queues actually contain lists of record numbers in the sorted sequence. Each record number is a four-byte binary number and the queues are built with 1000 record numbers per record. This makes it more efficient to create and access this data. The SORT process will read the "0" queue, extract the sort key data and save it along with the record number. This table is then sorted and only the record numbers are written into the sort sequence TS-Queues in blocks of 1000. Retrieval of records in sorted order requires retrieval of a sorted item list block (TSQ=XXXntttt where "n"=the sort sequence number) and then retrieval of the item number from the sorted item list in the real data (TSQ=XXX0tttt). Sort uses the CICS GETMAIN to obtain temporary memory for the table of keys and record numbers. Because of the CPU loop to sort the keys, a "dummy" TS-Queue read is issued periodically to allow CICS to believe the task is not really in a CPU loop. This is preferable to altering the limit in CICS for determining if the task is in a loop. The CPU intensity is because the code is written in COBOL and it is CPU intensive. Although it may trigger the CICS limit, it is not really a lot of time and is executed once to create the sorted sequence. LIMITATIONS The use of CICS services does impose some limitation on the data tables. Each data record can be from one to 32,767 bytes in length. The service routine imposes a fixed, record-length restriction which is not a TS-Queue limitation. The total record count must be less than 65,535 records. This is a TS-Queue limit. If the record count limit is a problem, then it is simple enough to block "n" records in a TSQ item and get a record count of n * 65,535. FINAL NOTES CICS continues to be used as a platform for system development and systems will have to continue to evolve using these tools. As CICS migrates to the AS/400, RS/6000, Windows and OS/2 (PCs), etc., these tools will migrate with them. This effort will not be lost on the mainframe development environment. Also, the development of tools and service routines will be needed as mainframe systems are re- engineered to be more efficient. The client/server environment continues to evolve into multiple layers with the data storage and retrieval layer residing on DBMS servers, like MVS for DB2. And CICS is right in the middle of the gateway interface on the MVS and/or the client side. NOTE: The COBOL-II Command Level code for the Data Manager can be obtained by sending a 3.5" HD or 5.25" HD diskette and a SASE (Self Addressed and Stamped Envelope) to the author at: Fred Schuff, 15 Chateau Circle, Wayne, PA 19087. You may also contact the author directly at (610) 647-5306 or on the Internet at schuff@netaxs.com. Was this article of value to you? If so, please circle Reader Response Card No. NaSPA member Fred Schuff is an independent consultant specializing in systems design and integration. He is located in the Philadelphia area and can be reached at (601) 647-5306. callout: The sorted data TS-Queues actually contain lists of record numbers in the sorted sequence. Each record number is a four-byte binary number and the queues are built with 1000 record numbers per record. This makes it more efficient to create and access this data. callout: The use of CICS services does impose some limitation on the data tables. Each data record can be from one to 32,767 bytes in length. The service routine imposes a fixed, record-length restriction which is not a TS-Queue limitation. The total record count must be less than 65,535 records.