Temporary tables in transactional systems

[stevexff]

I have a situation where where we filter records based on access groups. Currently (it's an older system) we get a list of groups using any one of a number of reasonably complex queries up front and put them into working storage, and then open a cursor on another query. As we retrieve each row we check it against the list in storage. It's not practical to use a nested query, as the variety of group listing criteria leads to a large number of possible queries which would make it hard to maintain.

Would creating this list as a temporary table on a per-transaction basis be a viable option? It would allow us to use the database to filter the records by doing a simple join with the temporary table rather than doing it in the code. I've tried to find an example on the Net without success, and the IBM manuals kind of imply by omission that temporary tables are aimed at a batch or one-off query environment.

As far as I can see the pros are[ul][li]simpler coding[/li][li]no cataloging of declared temporary tables[/li][li]reduced logging for declared temporary tables[/li][/ul]and the cons are[ul][li]incremental binds for joins with temporary tables[/li][li]extra cost for table creation (although not using a model could reduce catalog access for this)[/li][li]without trying it, I don't know if each CICS transaction will create its own declared temporary table that can't be seen by any other UOW[/li][/ul]

Does anyone have any experience of using declared temporary tables with CICS transactions?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[fredericofonseca]

Where I work I implemented the use of session tables - with DECLARE GLOBAL TEMPORARY TABLE - (CREATE GLOBAL TEMP TABLE has other issues)

These tables are only valid during the transaction and for that particular session - e.g. other users will not bee seen by any other transaction.

The way we work with them has not so far required "sharing" it with other programs on the same transaction, so I can not say if it works correctly if that is something you need. You would need to try it out.

Performance wise it is normally worthwhile doing - we had online programs decreasing their runtime from 10m to less than 1 min. difference on batch jobs was even bigger as volumes a lot higher. I think at the moment one of our production programs has 10 temporary tables being created and used on the same main cursor afterwards - performance is great.

The cost of creating the table and doing the bind is negligeble.

As a note - declared temp tables ARE LOGGED - commit and roll back will perform as in any other tables.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[stevexff]

Thanks Frederico, that's a big help. I didn't want to invest much time in a proof of concept if it was a no-no from the start. But it looks promising - I'll let you know how it goes.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]