468,242 Members | 1,635 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

Unable to select from temporary tables

Hi,

I'm trying to declare and use temporary tables. I have written the
following code in Java. Creating the tablespace (i can see the
tablespace created using the Control Center), the temporary table and
even inserting the values execute fine (even the executeUpdate while
inserting returns a positive number indicating that rows have been
inserted). However, when I select * from the temporary table, 0 rows
are returned and the while loop is never entered. Any ideas why this is
happening?? Is it some configuration of my database?
Thanks in advance! Any help would be greatly appreciated... :)

==============================
import java.sql.*;

public class TempTableTest {

public static void main(String[] args) {
try {

Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").ne wInstance();
String machine = "jdbc:db2:pmi";
String userName = "db2admin";
String password = "somepassword";
Connection conn =
DriverManager.getConnection(machine,userName, password);

Statement stmt = conn.createStatement();
stmt.executeUpdate("CREATE USER TEMPORARY TABLESPACE usertemp1 " +
" MANAGED BY SYSTEM " + " USING ('usertemp')");

stmt.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE temptb1 " +
" LIKE mantest_featsum " +
" NOT LOGGED " +
" IN usertemp1");

stmt.executeUpdate(
"INSERT INTO session.temptb1 " +
"(SELECT
slidebarcode,total,gp2count,gp3count,gp5count,gp7c ount,gp9count FROM
mantest_featsum)");
ResultSet rs = stmt.executeQuery("SELECT * FROM session.temptb1");

while (rs.next())//Fetch a row of data
{
rs.getInt(2);
}
stmt.close();
conn.close();

}
catch (Exception e) {
System.out.println(e.getMessage());
}

}
}

Nov 12 '05 #1
2 2169
Check out the ON COMMIT PRESERVE ROWS option, or turn auto-commit off.

Miro

Nov 12 '05 #2
Andy S. wrote:
Hi,

I'm trying to declare and use temporary tables. I have written the
following code in Java. Creating the tablespace (i can see the
tablespace created using the Control Center), the temporary table and
even inserting the values execute fine (even the executeUpdate while
inserting returns a positive number indicating that rows have been
inserted). However, when I select * from the temporary table, 0 rows
are returned and the while loop is never entered. Any ideas why this is
happening?? Is it some configuration of my database?

<snip>
By default global temporray tables are defined ON COMMIT DELETE ROWS.
So if your client does autocommit all the insret work is in vain.
Try:
DECLARE GLOBAL .... ON COMMIT PRESERVE ROWS
And see what happens.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Paulo Andre Ortega Ribeiro | last post: by
2 posts views Thread by mfyahya | last post: by
2 posts views Thread by Oliver Stratmann | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.