Using DB2 objects: Creating modes, tables, and views

Use DB2 objects: Create a schema, table, and view. Generally, data is stored in a relational table in DB2. Each table consists of multiple columns and rows. Table columns are defined during table creation. You can also add or delete tables after they are created. The data stored in the table must be consistent with the data column definition. Each table can have multiple indexes and views. A view is like a logical table. It consists of a SELECT statement that can be obtained from one or more tables or views. Unlike Relational Tables, data in views does not need to be physically stored on hard disks. Data is obtained when a view is queried. In addition to the definition stored in the system catalog, the view does not use physical space. After creating a view, you can use Data Manipulation Language (DML) to query the view or even update the view. The view provides flexible data access functions, allowing you to access a subset of a table or join the result sets of multiple tables, while hiding the data complexity in the base table. To provide a logical view of database objects (such as tables, indexes, and views), use one or more modes to classify them. Schema is a logical classification of database objects. You can create multiple database objects in the same or different modes. For example, in the table space SYSCATSPACE, all basic system tables and indexes are grouped in the same mode SYSIBM. All view groups of basic cataloguing tables and indexes are in the SYSCAT or SYSSTAT mode. This exercise demonstrates how to create a schema, table, and view: 1. In the Control Center, click All Databases> HELLOWLD> Schemas. Let's take a look at the existing mode created by DB2 when creating a database. Click Create New Schema in the lower-right window. 2. In the Create Schema wizard, enter HWLD as the new Schema name. Use the default Authorization Name. Click OK to run the CREATE SCHEMA command. Similarly, Click Show SQL to view the actual DB2 command. After the command is complete, check whether HWLD is displayed in the mode view. 3. specify the mode that the object should belong to when creating the object. If the mode name is not explicitly specified, the user ID is used as the mode by default, however, the condition is that the user should have the IMPLICIT_SCHEMA privilege (the next tutorial in this series will discuss the privilege in detail ). For more information, see links to the entire Hello World Series. 4. To create a table, select All Databases> HELLOWLD> Tables on the left of the Control Center window. In the lower-right window, click Create New Table. The Create New Table wizard window is started. It will take you to Create a Table. 5. In the Create Table wizard, select HWLD from the drop-down menu as the mode name. Enter AUTHOR as the table name. Enter descriptive comments. Click Next. 6. On the Columns page, click Add to Add Columns to the AUTHOR table. On the Add Column page, specify AUTHOR_NAME as the Column name, And VARCHAR as the data type. The length is 50. Click OK. 7. Click Add to Add the second column. Use AUTHOR_ID as the column name. The data type is Integer. Click OK. Add the third column, MODULE_NAME as the column name. The data type is CHARACTER and the length is 20. Click OK. 8. You should see these three Columns on the Columns page. Figure create table-column 9, click Next. The Data Partitions page is displayed. 10. Data partitioning is a new feature in DB2 9. It allows partitioning of large tables across multiple tablespaces. Click Next to skip this step because the AUTHOR table is not a partition table. 11. On the Table spaces page, select TBSP_DATA1 as the tablespace. Select Use Separate Index Space and select TBSP_INDEX1 as the Index tablespace. You can also specify a separate tablespace for any large object, but this is not required here. Click Next. 12. If TBSP_DATA1 or TBSP_INDEX1 is not displayed, cancel the Create Table wizard and ensure that the Table space view is refreshed from the Control Center. 13. You do not need to define any primary key or unique key, dimension, or constraint. Click Next on the subsequent page until the Summary page is reached. 14. Use Show SQL on the Summary page to view the actual Create Table command. Click Finish to create the HWLD. AUTHOR table. 15. The DB2 Message window (DB20000) should be displayed, indicating that the command has been successfully completed and there is no error. Close the message window. 16. In the Control Center Tables view, click the created AUTHOR table. The column definition, mode, and creator of the table are displayed in the displayed window. Figure Control Center -- Tables view 16. After checking the HWLD. AUTHOR table, add another column, AUTHOR_DOC. Make it an XML column because you need to store the XML document directly in this XML column. 17. Right-click AUTHOR, select Alter from the menu, and open the Alter Table wizard. Click Add to start the Add Column wizard. 18. In the Add Column wizard, enter AUTHOR_DOC as the Column name and select XML as the data type. Select Nullable to indicate that this column can contain NULL values. Click OK. 19. Check the Alter Table wizard and confirm that a new XML column has been added. There is an option to store table data in a compressed format. If the space is a big problem or the table data is large, you can consider using this option. Here, the data is saved in non-compressed format. Click OK to complete the ALTER TABLE operation. The message DB20000 should be returned. Close the message window. In the Control Center, select Table> Author and you will see the fourth column with the Data Type of XML. 20. To Create a View based on the Author table, choose Control Center> All Databases> HWLD> Views> Create New View. The Create View Wizard will show you how to Create a View. In the Create View wizard, select HWLD as the View mode and enter DB2_AUTHOR as the View name. 21. Replace the SQL statement with the following:

　　(AUTHOR_NAME, MODULE_NAME)　　AS　　SELECT AUTHOR_NAME, MODULE_NAME FROM HWLD.AUTHOR WHERE MODULE_NAME='DB2 UDB'

 

22. Select None for the check option to make the view attribute read-only. Click OK to create the view DB2_AUTHOR. 23. In Control Center, select the DB2_AUTHOR view to see the view definition in the lower-right panel of the screen. Figure view-Show Related Objects 24. Click Show Related Objects. On the Show Related page, click the Tables tab. Displays information about the base table of this view. Click Close.