I have a table called PTBalance with the following columns

a) Acct_Nbr
b) Acct_Type
c) Currency
d) Balance

I want to have a table(Physical also fine) having the following data

a) Acct_nbr
b) Sum(Balance)

Every day PTBalance table is replaced by a new table(drop the existing one, create a new one with latest data)


I created a MQT with acct_nbr and Sum(balance) then I try to delete the base table, db2 did n't allow me because it has MQT reference.

1)

Is there any other utility in DB2 which does not keep reference to base tables?


2) Can I create a physical table not MQT by using Create table as select statement. Can I use group by as part of select criteria?

3) What is the difference between MQT and Create table as select?



Thanks in advance.
Shri

The data in an MQT is periodically refreshed (either every time the data in the underlying tables is changed, or when you issue issue a refesh table command).

Thanks for the reply. I have a requirement to create a summary table or MQT or some utility with no reference to base tables bcz base tables are recreated every day.

If I use MQT , I can't drop base table. Is there any approach in db2 create a table from some sql and no reference to base tables.

Thanks
Shri

Why do you recreate the base table every day? You could just truncate it.

Unfortunatly yes, we load the data into a intermediate tables and swap with original table.

Then you could also construct the new MQT after the load and swap that with the original one.

Thats a good question, But the table has 4-6 Millions of records which might take some time. During that period user won't be able to access neither base table nor MQT.

Is there any way I can construct a intermediate table with no reference to the base table.

SELECT ACCOUNTNUMBER,-1*SUM(TRADEBALANCE) as TRADEBALANCE
FROM PTBALANCE GROUP BY ACCOUNTNUMBER

Out put of the above query should be added to intermediate table with no reference to the base table.

Let me know if additional information is required.

How about loading from a cursor?

I read the documentation for Cursors. It exactly satisfies our requirements. I don't see any documentation on the performance of this cursor utility. Please provide me if you have any?


Thanks for your help all of you.


Shri