Technote February 2000:
COBOL Access + Version 5
new features

Skip records into cursors:

After a suite of START / READ NEXT made by a Cobol Program, or after a 'generic' START (See Tech Note 99_04) , the server maintains a cursor, dedicated to this client, and pointing all records corresponding to initial start. Each following READ NEXT becomes a fetch from this cursor.

But sometimes, application can require to process only some 'slices' from records set: By example, into accounting management, you can start on 'class 1' accounts (all records beginning by digit 1), but to really process only the sub-class 140xxx to 149xxx AND 174xxx to 177xxx.

In previous releases, it was necessary to read sequentially all records, to only retain and process interesting : May be, we read 500 records (exchanged between server and client) to only process 25 records on client side !

Now, with version 5, it's possible to make the same generic start for account numbers '1% ' (that will create a cursor containing all records matching a digit 1 in first position, so 500 records in our example).

If you want to be directly positionned on the first record containing "140" into first positions, you can :

announce to Cobol Access + that the following READ NEXT will be a skip :

MOVE 15 TO ACC-OP.

CALL "SGBD" USING ACC-OP, API_INTERFACE.

Prepare the search key value into I-O buffer :

MOVE "My_Search_key_value" TO KEY_AREA. ( value 140 followed by NULL or spaces )

Execute the skip + read :

READ NEXT .......

AT END........

If I-O successfull, you have now into your buffer the first record matching your search key value, or immediatly greater than !

This sequence will execute a READ NEXT skipping all records of cursor till the first KEY_VALUE greater or equal the Search_Key_Value.

If no record corresponds to search key value ( end of cursor reached ) , a status End-Of-File or AT END is returned to Cobol program.

This kind of skip can be done many times, on the same active cursor, to retrieve many 'slices' or subsets of pre-selected records.

Each time you execute a new CALL "SGBD" type 15, before the READ NEXT, you'll execute a skip + read !

All records skipped are'nt transfered through network : only server executes the search !

That can increase a lot the performances !

It's available with all Cobols & RDBMS supported by Cobol Access +, under UNIX and WINDOWS ( except Informix SE under Unix, because no cursors supported !)

Dynamic & transparent management of network links :

With version 5 of Cobol Access +, when you're configured to use Client/Server architecture through TCP/IP network, the management of network link has been rewritten to increase fialibility, and avoid a lot of possible errors.

When a Cobol client uses remote DIDs ( located on different servers ), a network connexion is done to each server, to read and store locally informations concerning all DIDs declared into PLUSDBLIST variable.

With version 5, this link is immediatly released after dictionaries processing : May be this client will never open any database tables, and will work locally, only ! So, it's interesting to don't keep opened a link which will never be used till end of Cobol session.

On server, the number of visible clients connected ( into network management tools and views ) will decrease a lot, simplifying the management.

Later, if this Cobol client requires to really access on a database table, managed by Cobol Access +, the link will be automatically re-established, and kept opened.

This 'active' link will be also released if the Cobol client stay more than 5 minutes with no file opened on this server, and no transaction in process.

On first new OPEN statement, the link will be re-activated by Cobol Access +.

On server side, only real active clients will be visible as 'connected'.

It's available with all Cobols & RDBMS supported by Cobol Access +, under UNIX and WINDOWS.

A new feature : LiteSQL

Cobol Access + has been designed to transparently emulate Cobol data management ( Indexed files ) on many Database systems.

So, all Cobol statements like START, READ, READ NEXT, REWRITE, ...... are fully supported.

But these statements are based on key fields into files, and only these key fields can be used to retrieve and manage one record on each statement.

Into SQL world, any field can be used as criteria to manipulate a set of records (with variable performances, of course, if you want to use a non index field as criteria ) , and ONE STATEMENT can process a large number of records, in one 'time'.

The target of LiteSQL is, of course, to keep emulation of Cobol comportment, but opening possibilities to ALSO use, if necessary, the SQL possibilities.

That must be done without any additional plug-in into Cobol environment or runtime, because to add ORACLE client modules, or INFORMIX client modules, to a standard Cobol runtime, requires a high level expertise, and becomes complex when you must upgrade ORACLE or INFORMIX, by example.

Principles :

• No SQL coding inside Cobol programs !

• Cobol programs only manipulate Cobol statements ( READ, READ NEXT,.....) and Cobol datas ( WORKING STORAGE , in Cobol format )

• Programming procedure must be compatible across different RDBMS

Limits :

• any SELECT statement can be executed on one (and only one) database table, but result must be full rows (complete records declared into corresponding FD)

• a SELECT must be applied on a table known into the dictionary (DID)

• any UPDATE , manipulating many rows in one time, can be used

• any DELETE , applied on many rows, can be executed

• INSERT is'nt supported, because it does'nt give any interest, against Cobol WRITE statement, inserting also a full row.

• The string containing parameters values, prepared by Cobol application, is limited today to 2 Kbytes ( 2,048)

• These feature is NOT available for INFORMIX Standard Engine under UNIX, because in this case, Cobol Access + access directly to physical C-ISAM files, without any SQL engine : better performances, less functionalities !

Method :

You must create, manually or using an assistant running under Windows, 'external' requests, stored into text files, accessible for the server (these requests must be located into the directory containing all DIDs on servers, and pointed by PLUSDICO variable ).

requests are suffixed as '.req'

a request file , named req001.req must contain something like this :

• #:RMTORA\REQ001\1\\

• I:2

• S:SELECT DEPTCODE, DEPTSECTION, DEPTNAME, TO_CHAR( DEPTDATE, 'YYYYMMDDHH24MISS'), PRODNAME, PRODORDER, PRODMAKE, DEPTSALES, DEPTBUDGET FROM TSTDEPTS WHERE ( (DEPTSECTION >= %1% AND DEPTSECTION < %2% )) ORDER BY DEPTCODE ASC;

The first line defines the concerned DID (RMTORA) , precises the request name (REQ001) and gives the statement type ( 1=SELECT, 2=UPDATE, 3=DELETE)

The second line precises how many parameters must be given by client, to execute this request ( in our case, 2 parameters are required )

The third line contains the SQL statement to execute, where parameters are positionned and marked with sequences %n%, where n is the parameter index into list of values prepared by Cobol application.

An application, using this request , must :

• Have a working area declared as follow

• Prepare these parameters before to call Cobol Access + API :

MOVE "DIDNAME" TO BAB-NAME.

MOVE "TSTDEPTS" TO LITE-TABLENAME. ( table concerned by our SQL example )

MOVE "req001" TO LITE-REQNAME.

MOVE 2 TO LITE-NBITEM.

MOVE "24\31\" TO LITE-PARAMS.

·        In our example, we'll select records where DEPTSECTION (numeric) is >= 24 and  < 31; 24 & 31 are our parameters values, separated by '\' (back slash) characters. The parameter string must be also terminated by \

·        If a parameter requires string value, the string must also contain the quote characters.

·         example :" '01-JAN-2000'\'31-DEC-2000'\ "

MOVE 21 TO ACC-OP.

CALL "SGBD" USING ACC-OP, API-INTERFACE.

• The SQL statement will be loaded by server, a cursor created, %n% sequences will be bounded into cursor structure, the statement will be submitted to RDBMS engine to be compiled, and finally executed.

• If some result is expected ( SELECT statements, by example), result returned by RDBMS will be verified by Cobol Access + server, and cursor will be kept opened if some result is pending; If no result is expected (UPDATE or DELETE) or if statement fails (SELECT with bad criteria values), the cursor is closed by server.

• Status and indicator are returned to Cobol program.

If execution was fine, status returned into BAB-STATUS = 0; Otherwise, see Appendix A of error codes to interpret it.

If a cursor is available after execution ( after successfull SELECT, only), the field LITE-CURSAVAILABLE will contain "Y"; after UPDATE or DELETE statements, this field will contain "N".

If LITE-CURSAVAILABLE contains "Y", the Cobol program can execute a loop of READ NEXT AT END, to read records resulting of SELECT execution.

The demo Cobol programs, installed with Cobol Access + package, contain example of use.

Of course, it can also use the 'skip records' described in current document.

It's available with all Cobols & RDBMS supported by Cobol Access +, under UNIX and WINDOWS ( except Informix SE under Unix, because no SQL nor cursors supported !)

Increase performances with INFORMIX Dynamic Server:

With INFORMIX Dynamic Server version 7.30 and following, it's now possible to insert some 'HINTS' into SQL statements submitted to engine, to force optimizer to use some particular index, and to avoid full scans on tables if optimizer choice is not correct.

This possibility is really efficient, when Cobol applications execute START NOT LESS THAN on complex keys ( containing many fields, of different types ).

With version 5 of Cobol Access +, this possibility is used, and performances are really better on large tables.

So, if you're running this kind of RDBMS engine, you can add the value +H into the variable PLUSSW string (switches) , before to start the Cobol Access + service on a DID.

All statements sensible to optimizer choices will include these 'HINTS' options.

Warning : If you try to use the switch +H with an engine supporting not this feature (version less than 7.30, or not I.D.S.) , you'll have an error on first database access !

It's available under UNIX and WINDOWS !