DB2 Temprorary Tables v Linkage

[DavidMarek]

I have a large application that needs to share data. Initially the data will be read from an IMS datbase but there are 2 options to pass the data around to the other programs that need it.

1. Via Linkage. This would involve creating large copybooks for each program (so that the programs are unique and not dependant on one large copybook), but there is a storage overhead plus the cost of moving the data around to the subprograms.

2. DB2 declared temporary tables. The data is inserted into a temp DB2 table then the subprograms that need data, SELECT the information they need.


Do you have any advice on the performance benefits of the DB2 option? The application is large, several hundred COBOL programs and roughly 500K on the table.


Thanks,

 

[Crox]

Hi,

You can also create a routine that manages some variables. The language environment has possibilities to create temporary data, for example to contain segments. In the maintaining routine, you manage in working-storage some key-info and pointers.

To do it all by linkage is possible and is fast.

To do it by pointers with a managing routine, is easier to maintain for all the programs except the managing routine which is a little bit more difficult. But.. I developed once one and it was very nice to have one. The program that gets the data, puts it into that managing routine - we can call it MR - with some keys and every other program can ask the data using those keys. Perhaps the SSA or other keys? Or the concatenated key or ....

I would try to avoid using DB2 for this if you don't need it. It will be slower and if there are many processes at the same time, you will get locking situations which are not nice to handle and even if you can handle them nice, it will slow down your system a lot.

As I read once:
"nothing is more killing than doing the wrong things more efficient"

Regards,

Crox

 

[slade]

Hi David,

You say there are "several hundred COBOL pgms" and that the data is passed around among the subpgms.

Can one correctly infer that the data is passed around among several hundred COBOL sub pgms"? The other ques is: 500K records? Bytes of data?

If this is going to be a very complicated mainline pgm calling (linking) to a variety of subs, the data is really stationary once it is read into memory. It isn't "moved around" but made available to the subs via pointers to stationary areas.

The COBOL "EXTERNAL" function may have some applicability for you here.

Of course the traditional batch data processing model is also available and may prove to be a less challenging and risky approach.

Sounds interesting though. Care to share any more details?

Regards, Jack.

 

[DavidMarek]

Thanks for the reply.

The application is a complex calculation application with a large requirement for storage.

The main storage is a set of arrays that is dynamcially allocated via an assembler routine so that, depending on data, we can vary the storage from the order of 10's of K to 70Mb. This space is allocated via a controller and a copybook holds the pointers of the addresses of the 01 levels.

In addtion to this there is a separate storage area that is populated then updated by different subprograms, then read by further subprograms. These subprograms need their own distinct view of the data, so we are against using a large copybook and making this available to all subprograms, as changes to a single piece of data would result in all programs needing recompiled. The alternative is to have small copybooks for the subprograms that only contain the data they need. However there is a storage overhead with this and a performance hit in the time take to move the data from the main data store to these individual copybooks.

This data is a collection of arrays that will vary on size depnding on index values, hence the appeal of DB2 tables where we simply read until SQL CODE 100.

We looked at the EXTERNAL verb in COBOL, but the version we have, EXTERNAL data is always below the line, and all data for our application must be above the line.

 

[Crox]

Hi,

It is possible to put the external data above the line. It is a matter of installation.

Regards,

Crox