Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unanswered: Help required about temporary table

    Hi all,

    I'm trying to declare a global temporary table and insert some rows from a regular table, but it doesn't work.

    Here are my commands:

    step1: DECLARE GLOBAL TEMPORARY TABLE session.test LIKE user.userdata IN USERTEMP NOT LOGGED

    note:The USERTEMP is a user-temporary tablespace created by myself, and the DB2ADMIN user is authorized to user this temporary tablespace.

    step2: INSERT INTO session.test SELECT * FROM user.userdata

    note:The USER.USERDATA is a regular table,it has 230,000 rows.

    The result is: it reports this command in step2 is executed succesfully without any errors, but there is no data in SESSION.TEST.

    Please, who can tell me what's the problem? and how should I insert data from a regular table to a temporary table?

    My DB2 version is 7.2.0.

    Thanks!!
    Last edited by Lin; 12-21-03 at 23:00.

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: Help required about temporary table

    Hi,

    can you record a transcript of your statements and DB2's messages and return codes? This will be of great help, because if it turns out to be a bug, you should be able to document this for IBM support.

    And I would like to have a look at this, too.

    Johann

    Originally posted by Lin
    Hi all,

    I'm trying to declare a global temporary table and insert some rows from a regular table, but it doesn't work.

    Here are my commands:

    step1: DECLARE GLOBAL TEMPORARY TABLE session.test LIKE user.userdata IN USERTEMP NOT LOGGED

    note:The USERTEMP is a user-temporary tablespace created by myself, and the DB2ADMIN user is authorized to user this temporary tablespace.

    step2: INSERT INTO session.test SELECT * FROM user.userdata

    note:The USER.USERDATA is a regular table,it has 230,000 rows.

    The result is: it reports this command in step2 is executed succesfully without any errors, but there is no data in SESSION.TEST.

    Please, who can tell me what's the problem? and how should I insert data from a regular table to a temporary table?

    My DB2 version is 7.2.0.

    Thanks!!

  3. #3
    Join Date
    Dec 2003
    Posts
    4

    Re: Help required about temporary table

    Thank you for your help, I have solved that problem. When I declare a temporary table, there is not a ON COMMIT PRESERVE ROWS parameter in the SQL command, and the default parameter value is ON COMMIT DELETE ROWS.It means when the transaction is committed, all rows in the temporary table will be deleted. When I added this parameter to the command, it can save the data now.

    The right SQL should be:

    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST LIKE USER.USERDATA IN USERTEMP ON COMMIT PRESERVE ROWS NOT LOGGED

    Thanks again!

    Originally posted by jsander
    Hi,

    can you record a transcript of your statements and DB2's messages and return codes? This will be of great help, because if it turns out to be a bug, you should be able to document this for IBM support.

    And I would like to have a look at this, too.

    Johann
    Last edited by Lin; 12-22-03 at 22:17.

Posting Permissions

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