How to use DB2 SELECT query result to create table

Sometimes we may want to use DB2 to define the result of the query as another table, but since DB2 does not support to define a table with a method like create table as select from Oracle, it might be more difficult.

In this blog, we summarize 3 ways to define DB2 tables.

Before that, we need to first create an example table, and insert a few statements.

create table xxx(col1 varchar(10));

insert into xxx values ('1');

commit;

create table YYY(col1 varchar(10),col2 varchar(20));

insert into YYY values ('1','ZXT');

commit;

describe select xxx.col1,yyy.col2 from xxx,YYY where xxx.col1=yyy.col2;

(1) Define by conventional form

Describe sql to get DDL.

You can proceed as follows:

(describe select xxx.col1,yyy.col2 from xxx,YYY where xxx.col1=yyy.col2;)

Then create the table according to DDL, and insert the data into the table.

(2) Define by summary table

To succeed, the following 3 steps are required:

1. Create a summary table

create table MM as

(

Select xxx.col1,yyy.col2 from xxx,YYY where xxx.col1=yyy.col2

)data initially deferred

Refresh deferred;

2. Refresh data

Refresh table MM;

3. Change the summary table to a general table

alter table MM drop materialized query;

This is a method which is commonly used, and the data can be refreshed all at once!

(3) Define the form of the table structure only

The following 2 steps are required:

1. Create a table---only define the table structure

Create table MM as

(

Select xxx.col1,yyy.col2 from xxx,YYY where xxx.col1=yyy.col2

) Definition only;

Note: This step only defines the table structure, which is similar to the create XX as statement from Oracle.

2. Insert the data

Insert into MM select xxx.col1,yyy.col2 from xxx,YYY where xxx.col1=yyy.col2;

Commit;

Above all, the last two methods are recomended.