How to migrate view tables into real tables?

[glhernandez]

Is it possible to make view into real tables?

By the way I'm using DB2 10.1 - Data Studio

[Rabbit]

You can use the select into syntax to create a table from a SQL statement. However, you should think hard about whether you actually need to create a table from the view.

[vijay2082]

Hi,

You can use below steps to create your table form a view.

C:\Users\vijay.HARSH2082>db2start
06/10/2012 12:52:09     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

C:\Users\vijay.HARSH2082>db2level
DB21085I  Instance "TEST" uses "32" bits and DB2 code release "SQL09058" with
level identifier "06090107".
Informational tokens are "DB2 v9.5.800.186", "s110603", "IP23264", and Fix Pack
"8".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".


C:\Users\vijay.HARSH2082>db2 connect to test

   Database Connection Information

 Database server        = DB2/NT 9.5.8
 SQL authorization ID   = VIJAY
 Local database alias   = TEST

## Create view on syscat.table as below

C:\Users\vijay.HARSH2082>db2 "create view myview as select * from syscat.tables"
DB20000I  The SQL command completed successfully.

## Check your view.TYpe =V show it's a view

C:\Users\vijay.HARSH2082>db2 "select char(tabschema,30) tabschema, char(tabname,30) tabname,TYPE from syscat.tables where tabname='MYVIEW'"

TABSCHEMA                      TABNAME                        TYPE
------------------------------ ------------------------------ ----
VIJAY                          MYVIEW                         V

  1 record(s) selected.

## Create a table from your view

C:\Users\vijay.HARSH2082>db2 "create table vijay.mytabfromview like vijay.myview"
DB20000I  The SQL command completed successfully.

## check your table. Type=T shpws it's a table

C:\Users\vijay.HARSH2082>db2 "select char(tabschema,30) tabschema, char(tabname,30) tabname,TYPE from syscat.tables where tabname='MYTABFROMVIEW'"

TABSCHEMA                      TABNAME                        TYPE
------------------------------ ------------------------------ ----
VIJAY                          MYTABFROMVIEW                  T

  1 record(s) selected.


C:\Users\vijay.HARSH2082>db2 "select count(*) from vijay.mytabfromview"

1
-----------
          0

  1 record(s) selected.

Cheers, Vijay

[glhernandez]

Can I make this directly in sql editor? the select part?

[vijay2082]

yes, you can put it in sql editor and execute it form there.

Cheers, Vijay

[glhernandez]

Thank you very much! . You have help me a lot. Hope to learn more from you. Thank you again

Best Regards,

G.L Hernandez