DB2 Create Table
Extract important details from both the tables and keep it in a virtual table
After deciding column and entering all fields and data types for your DB2 create table, go to option 15, it can differ in your product, you might not get option 15 directly, you might have option DB2 and inside that DB2 you might have option DB2 V10. So, please check your product properly. Then go to SPUFI, you can also use QMF for creating the table.
Here, we will get to SPUFI option 1, we have to give input filename “INPUT.FILE.HERE”, where we have to write SQL operation and then get the output of the SQL operation from “OUTPUT.FILE.HERE”.
And save your table.
For giving comment in SPOFI we have to use two hyphens(–).
How to create views in DB2
Views are created as virtual table they are not a physical table if you delete the main table from which the view is created the view also gets deleted. Based on the situation we have to create the views and we have to analyze whether views are needed or not.
For creating view select the list of columns from the table from which you want to retrieve these fields.
Give semicolon for execution in COMMAND.
In place of view, we can create synonyms and alias as well.
ALTER TABLE in DB2
To add a new column, rename a column, changing the property of a new column like length or data type of a particular column in a table we use ALTER TABLE in DB2.
For details go to DDL operation.txt.
The syntax for changing the name of a column: ALTER TABLE STORE RENAME COLUMN OLD COLUMN NAME TO NEW COLUMN NAME;
Example, ALTER TABLE STORE RENAME COLUMN STORE LOCATION TO STORE_LOC;
DB2 DROP A TABLE
The DB2 DROP A TABLE is used to delete a table permanently. When we use to drop all the views synonyms or indexes are automatically dropped.