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

    Unanswered: Global temporary tables

    Hi,

    What are the requirements to create a Global Temporary table in a Select Query. I mean:

    1. Do we need to have sepearate tablespace in the DB
    2. Do we require special access on the tables/tablespaces

    Step 1:
    I am trying to create a Global Temporary table using this query:

    EXEC SQL DECLARE GLOBAL TEMPORARY TABLE TNH_NUM_HIST (TNH_SEC_ID CHAR(10) NOT NULL ) ON COMMIT PRESERVE ROWS NOT LOGGED
    ;

    Step 2: Inserting a record using :
    EXEC SQL INSERT INTO SESSION.TNH_NUM_HIST VALUES ( :mc__5 )
    ;
    where mc__5 is the Host variable where value is getting inserted from another DB query

    Step 3elect record from Global Temporary table

    EXEC SQL DECLARE DMTNH02_1 CURSOR FOR
    SELECT tnh_SEC_ID
    FROM
    SESSION.TNH_NUM_HIST
    ;

    EXEC SQL OPEN DMTNH02_1;

    retValue = sqlca.sqlcode;

    while(retValue == 0 )
    {

    EXEC SQL FETCH DMTNH02_1 INTO
    :nms_sec_id
    ;


    When I do this, I get DB error -501


    What could be the possible reason for this error??
    I am using UDB 8.1 FP2 running on AIX 5.1

    Gulshan

  2. #2
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96

    Re: Global temporary tables

    Are you running these commands using a commandfile or the CLP?

    If so, it's because autocommit is active and your cursor is automatically closed after it is openend. Use the -a switch to turn off autocommit and it should work.

    Hope this helps


    Originally posted by gulshan_gandhi
    Hi,

    What are the requirements to create a Global Temporary table in a Select Query. I mean:

    1. Do we need to have sepearate tablespace in the DB
    2. Do we require special access on the tables/tablespaces

    Step 1:
    I am trying to create a Global Temporary table using this query:

    EXEC SQL DECLARE GLOBAL TEMPORARY TABLE TNH_NUM_HIST (TNH_SEC_ID CHAR(10) NOT NULL ) ON COMMIT PRESERVE ROWS NOT LOGGED
    ;

    Step 2: Inserting a record using :
    EXEC SQL INSERT INTO SESSION.TNH_NUM_HIST VALUES ( :mc__5 )
    ;
    where mc__5 is the Host variable where value is getting inserted from another DB query

    Step 3elect record from Global Temporary table

    EXEC SQL DECLARE DMTNH02_1 CURSOR FOR
    SELECT tnh_SEC_ID
    FROM
    SESSION.TNH_NUM_HIST
    ;

    EXEC SQL OPEN DMTNH02_1;

    retValue = sqlca.sqlcode;

    while(retValue == 0 )
    {

    EXEC SQL FETCH DMTNH02_1 INTO
    :nms_sec_id
    ;


    When I do this, I get DB error -501


    What could be the possible reason for this error??
    I am using UDB 8.1 FP2 running on AIX 5.1

    Gulshan

  3. #3
    Join Date
    Jun 2003
    Posts
    24

    Re: Global temporary tables

    I am not running the query using a commandfile or CLP. CICS transaction (OLTP) is invoking the DB2 query.


    Originally posted by GertK
    Are you running these commands using a commandfile or the CLP?

    If so, it's because autocommit is active and your cursor is automatically closed after it is openend. Use the -a switch to turn off autocommit and it should work.

    Hope this helps

  4. #4
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96

    Re: Global temporary tables

    Originally posted by gulshan_gandhi
    I am not running the query using a commandfile or CLP. CICS transaction (OLTP) is invoking the DB2 query.

    Just to be sure

    You do set retValue to sqlca.sqlcode within your while loop?

  5. #5
    Join Date
    Jun 2003
    Posts
    24
    Yes, I do set and check the value for sqlca.sqlcode.
    Some times, I get this db error '-1034' and the CICS system goes down.

Posting Permissions

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