Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    24

    Unanswered: Global Temporary Table

    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.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2003
    Posts
    24
    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.

  4. #4
    Join Date
    Jun 2003
    Posts
    24
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •