Creating Materialized View in DB2

>>"I need to extract records for those input list of ids from "xyz" table  ...  every 15 min."

and do what? What happens to those rows?

By the way: It sounds more like you need a stored procedure, not a materialized view

Thanks for the reply PortletPaul. It is taking a lot of time to extract records as the id field is not indexed and there are millions of records in the "xyz" table. The table already has several indexes and don't want to add another index. The extracted records are sent back to the caller. I need a materialized view only and not a stored procedure.

I have very rarely used DB2 (and only for queries) so I don't really have strong background for that db, but if this was Oracle I would add an index in preference to the complexity and storage of a materialized view.

ps: I am just trying to clarify your question. Doubt I can offer the wanted code.

No problem Paul.Thank you.

Here is an introduction to Materialized Views from ibm.com
https://www.ibm.com/developerworks/data/library/techarticle/dm-0509melnyk/index.html

It starts with this sample code:

connect to sample
...
 
alter table employee add unique (empno)
alter table department add unique (deptno)
 
create table emp as (select e.empno, e.firstnme, e.lastname, e.phoneno, d.deptno,
 substr(d.deptname, 1, 12) as department, d.mgrno from employee e, department d
  where e.workdept = d.deptno)
   data initially deferred refresh immediate
 
set integrity for emp immediate checked not incremental
 
select * from emp
 
EMPNO  FIRSTNME     LASTNAME        PHONENO DEPTNO DEPARTMENT   MGRNO
------ ------------ --------------- ------- ------ ------------ ------
000010 CHRISTINE    HAAS            3978    A00    SPIFFY COMPU 000010
000020 MICHAEL      THOMPSON        3476    B01    PLANNING     000020
000030 SALLY        KWAN            4738    C01    INFORMATION  000030
000050 JOHN         GEYER           6789    E01    SUPPORT SERV 000050
000060 IRVING       STERN           6423    D11    MANUFACTURIN 000060
000070 EVA          PULASKI         7831    D21    ADMINISTRATI 000070
000090 EILEEN       HENDERSON       5498    E11    OPERATIONS   000090
000100 THEODORE     SPENSER         0972    E21    SOFTWARE SUP 000100
000110 VINCENZO     LUCCHESSI       3490    A00    SPIFFY COMPU 000010
000120 SEAN         O'CONNELL       2167    A00    SPIFFY COMPU 000010
000130 DOLORES      QUINTANA        4578    C01    INFORMATION  000030
...
000340 JASON        GOUNOT          5698    E21    SOFTWARE SUP 000100
 
  32 record(s) selected.
 
connect reset

Select allOpen in new window

So you see it is a create table script, but it uses a select query

create table XYZ as (

   select query here

   )
data initially deferred refresh immediate

sorry poor use of XYZ, the new table's name should indicate it is a materialised view (in my opinion)

Thank you Paul. One last thing. After you create materialized view emp in the above example. How do I create a index on empno
I am a java developer and learning databases. Also, can you give me query to get the data in the last 3 months with oracle. I will modify it to DB2.

For minus 3 months it will be something like this. (nb: I don't have access to DB2 so haven't been able to verify.)

select                    
      CURRENT_DATE
    , (CURRENT_DATE - 3 MONTH)
from sysibm.sysdummy1

Thank you so much for your replies Paul

Hi,

You can create multiple indexes on MQT table as on any other table. :)

create index xyz_extract_mv_ix1 on xyz_extract_mv ( col1, col2);

Also what version of DB2 and on what platform are you using ?

Also note that it is very important to have the table and indexes in separate tablespaces where each tablespace uses separate bufferpools for performance reasons. Especially for large tables.

Regards,
    Tomas Helgi