We run version 8 of DB2 and based on some information I found on the internet I'm under the impression with version 8 and later you can create dynamic scrollable cursors without the need to define temporary databases/tablespaces. I'm trying to do that. I created a small cobol stub program which is intended to start at a specific record in a defined cursor and select each previous record moving backwards. My compile/bind works, but when I execute the program and the cursor tries to open, I get a -904 sqlcode. The reason code 00E7009A gives the following result:

Explanation: A DECLARE GLOBAL TEMPORARY TABLE statement or OPEN CURSOR
(of a scrollable cursor) was issued, but the operation cannot be performed
due to one of the following reasons:

o A TEMP database has not been created yet.

o A table space has not been created in the TEMP database yet.

o No table space in the TEMP database has a page size that is large
enough for the declared global temporary table.

o No table space in the TEMP database has a page size of at least 8K.
System Action: The operation is not allowed.

Do I really need to create a temp database in order to declare a dynamic scrollable cursor, or do I need some sort of default changed with our DB2 settings?

Any help or direction would be greatly appreciated.

Thanks very much,
Ed F.

Are you sure you're using a dynamic scrollable cursor?
(Syntax: DECLARE name SENSITIVE DYNAMIC SCROLL CURSOR FOR query.)

A static scrollable cursor will indeed need a temp table, but since a dynamic cursor is "fully sensitive" it would not make sense to materialize the result set into a temp table: the data has to be accessed directly in order to be sensitive to any changes, including INSERTs.
Actually, you would get an SQLCODE -243 during BIND when your query would need materialization (e.g. because of an ORDER BY without the presence of an appropriate index).

Peter,

Thank you very much for your answer. I never did have the correct syntax for declaring a dynamic scrollable cursor. I tried it with your syntax example and it works great.

Thanks again,
Ed