<div>
&gt;&gt;&quot;I need to extract records for those input list of ids from &quot;xyz&quot; table &nbsp;... &nbsp;every 15 min.&quot; 
 
and do what? What happens to those rows? 
 
By the way: It sounds more like you need a stored procedure, not a materialized view
</div>

<div>
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 &quot;xyz&quot; 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.
</div>

<div>
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.
</div>

<div>
Here is an introduction to Materialized Views from ibm.com 
<a href="https://www.ibm.com/developerworks/data/library/techarticle/dm-0509melnyk/index.html" rel="ugc">https://www.ibm.com/developerworks/data/library/techarticle/dm-0509melnyk/index.html</a> 
 
It starts with this sample code:
<pre><code class="code-10 nolinks" id="code-20-42794807-1">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
</code></pre>
So you see it is a create table script, but it uses a select query 
 
create table XYZ as ( 
 
&nbsp; &nbsp;select query here 
 
&nbsp; &nbsp;) 
data initially deferred refresh immediate
</div>

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

<div class="indent">
create table XYZ_EXTRACT_MV as ( 
 
&nbsp; &nbsp;select query here 
 
&nbsp; &nbsp;) 
data initially deferred refresh immediate
</div>
</div>

<div>
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.
</div>

Srikanth Vishnuvajhala

<div>
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 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; CURRENT_DATE 
&nbsp; &nbsp; , (CURRENT_DATE - 3 MONTH) 
from sysibm.sysdummy1
</div>

<div>
Thank you so much for your replies Paul
</div>

<div>
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, 
&nbsp; &nbsp; Tomas Helgi
</div>

<div>
<div class="content wysiwyg-content">
I want to create a Materialized View in IBM-DB2, which is called Materialized Query Table. I have a table &quot;xyz&quot; and it has say &quot;n&quot; columns and one of them is &quot;id&quot;. I am given list of ids as input. I need to extract records for those input list of ids from &quot;xyz&quot; table using the materialized view every 15 min. 
 
Can you please help me how to solve this. Thank you.
</div>
</div>

I want to create a Materialized View in IBM-DB2, which is called Materialized Query Table. I have a table "xyz" and it has say "n" columns and one of them is "id". I am given list of ids as input. I need to extract records for those input list of ids from "xyz" table using the materialized view every 15 min.

Can you please help me how to solve this. Thank you.

Creating Materialized View in DB2

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.