Hi,
I am running my application on AIX platform with UDB8.1 FP4 database. CICS is my OLTP. I am using GLOABAL TEMPORARY TABLE as :

EXEC SQL DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST (TEST_SEC_ID char(10)) NOT LOGGED;

EXEC SQL INSERT INTO SESSION.TEST (TEST_SEC_ID)
(SELECT distinct
NMS_SEC_ID
FROM
NMS_NUM_SCHM
WHERE
NMS_SEC_NUM = :mc__402
)

I am selecting the data from SESSION.TEST using cursor as:
EXEC SQL
DECLARE q_dmcaax94_F CURSOR FOR
SELECT
CAA_CA_ID
FROM
CAA_CA
WHERE
CAA_SEC_ID IN (SELECT TEST_SEC_ID from SESSION.TEST)
;

When i run this query i get db error = -727. Pls note that the userspace(USERSPACE1) exist in the database.

Have you created a user temporary tablespace ???


SQL0727N An error occurred during implicit system action type
"<action-type>". Information returned for the error
includes SQLCODE "<sqlcode>", SQLSTATE "<sqlstate>"
and message tokens "<token-list>".

Explanation:

The processing of a statement or command has caused the database
manager to implicitly perform additional processing. During this
processing an error was encountered. The action attempted is
shown by the "<action-type>":


1 implicit rebind of a package

2 implicit prepare of a cached dynamic SQL statement

3 implicit regeneration of a view

4 This return code is reserved for use by DB2.

5 incremental bind of a static SQL statement, not bound during
package bind time

The sqlcode, sqlstate and message token list (each token is
separated by the vertical bar character) are provided. The
message tokens may be truncated. See the corresponding message
for the "<sqlcode>" for further explanation of the error.

The original SQL statement or command that caused the
"<action-type>" cannot be processed and the implicit system
action was not successful.

Federated system users: You may have received this message
because you dynamically prepared an SQL statement in a
pass-through session and then tried to execute the statement
after the session was closed.

User Response:

Check the message associated with the SQLCODE of the SQL
statement that failed. Follow the action suggested by that
message.

For an invalid package, the REBIND command can be used to
recreate the error or to explicitly validate the package once the
cause of the error has been resolved.

For a failure while regenerating a view, the name of the view
that failed is recorded in the administration notification log.
The failing view can be dropped or a change made to the statement
or command that caused the view regeneration.

Federated system users: If the statement that failed was
dynamically prepared in a pass-through session, open another
pass-through session, write and prepare the statement again, and
execute it while the session is still open.

sqlcode : -727

sqlstate : 56098

Sorry for late reply but I was able to resolve the problem. The reason was that there was no user temporary tablespace. I created one and it worked.
Thanks.

Apart from creation of global temporary table, I want to improve the performance of my query. Currently the query is using UNION as:

insert into SESSION.TEST (SELECT distinct
NMS_SEC_ID
FROM
NMS_NUM_SCHM
WHERE
NMS_SEC_NUM = :mc__402
AND
(
NMS_NMBRNG_SCHM = :mc__400
OR
:mc__400 = 0
)
UNION
SELECT
NMH_SEC_ID
FROM
NMH_NUM_SM_HTY
WHERE
NMH_SEC_NUM = :mc__402
AND
(
NMH_NMBRNG_SCHM = :mc__400
OR
:mc__400 = 0
)
UNION
SELECT
TNS_SEC_ID
FROM
TNS_NUM_SCHM
WHERE
TNS_SEC_NUM = :mc__402
AND
(
TNS_NMBRNG_SCHM = :mc__400
OR
:mc__400 = 0
)
)
)


I have broken the abobe query to 3 individual SQL query that will insert the data in SESSION.TEST table. My question is:

1. Will it improve performance or should i stick to UNION query?
2. For table NMS_NUM_SCHM, should I create index on NMS_NMBRNG_SCHM and NMS_SEC_NUM or on NMS_NMBRNG_SCHM, NMS_SEC_NUM and NMS_SEC_ID ?
The same is applicable ofr table NMH and TNS also.