By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

Unable to select from temporary tables

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
Check out the ON COMMIT PRESERVE ROWS option, or turn auto-commit off.

Miro

Nov 12 '05 #2

P: n/a
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.

Browse more DB2 Database Questions on Bytes