Data Warehousing & Tools, New Menu

DB2 – DDL (Data Definition Language)

SQL is the language we can interact with RDBMS. When referring to DB2 SQL, the following will give brief idea on DDL.

  • DROP

The above 3 are called Data definition language.

  • Creating a schema
    A schema provides a logical grouping of SQL objects. To create a schema, use the CREATE SCHEMA statement.
  • Creating a table
    A table can be visualized as a two-dimensional arrangement of data that consists of rows and columns. To create a table, use the CREATE TABLE statement.
  • Creating a table using LIKE
    You can create a table that looks like another table. That is, you can create a table that includes all of the column definitions from an existing table.
  • Creating a table using AS
    You can create a table from the result of a SELECT statement. To create this type of table, use the CREATE TABLE AS statement.
  • Creating and altering a materialized query table
    A materialized query table is a table whose definition is based on the result of a query, and whose data is in the form of precomputed results that are taken from the table or tables on which the materialized query table definition is based.
  • Declaring a global temporary table
    You can create a temporary table for use with your current session. To create a temporary table, use the DECLARE GLOBAL TEMPORARY TABLE statement.
  • Creating and altering an identity column
    Every time a row is added to a table with an identity column, the identity column value for the new row is generated by the system.
  • Using ROWID
    Using ROWID is another way to have the system assign a unique value to a column. ROWID is similar to identity columns. But rather than being an attribute of a numeric column, it is a separate data type.
  • Creating and using sequences
    Sequences are similar to identity columns in that they both generate unique values. However, sequences are objects that are independent of any tables. You can use sequences to generate values quickly and easily.
  • Creating descriptive labels using the LABEL ON statement
    Sometimes the table name, column name, view name, index name, sequence name, alias name, or SQL package name does not clearly define data that is shown on an interactive display of the table. You can create a more descriptive label for these names by using the LABEL ON statement.
  • Describing an SQL object using COMMENT ON
    After you create an SQL object, such as a table or view, you can provide object information for future reference using the COMMENT ON statement.
  • Changing a table definition
    You change the definition of a table by adding a column, changing an existing column definition, such as its length or default value, dropping an existing column, adding a constraint, or removing a constraint.
  • Creating and using ALIAS names
    When you refer to an existing table or view, or to a physical file that consists of multiple members, you can avoid using file overrides by creating an alias. To create an alias, use the CREATE ALIAS statement.
  • Creating and using views
    A view can be used to access data in one or more tables or views. You create a view by using a SELECT statement.
  • Creating indexes
    You can use indexes to sort and select data. In addition, indexes help the system retrieve data faster for better query performance.
  • Catalogs in database design
    A catalog is automatically created when you create a schema. There is also a system-wide catalog that is always in the QSYS2 library.
  • Dropping a database object
    The DROP statement deletes an object. Depending on the action requested, any objects that are directly or indirectly dependent on that object might also be deleted or might prevent the drop from happening.

Source: IBM