DB2 - Create statement

First Page
Next Page

DB2 Create statement

CREATE statement is used to create the DB2 objects. The database objects include schemas, tables, views, sequences, catalogs, indexes, and aliases,

Syntax

CREATE SCHEMA Schema-name

TABLE:
	CREATE TABLE Table name
	(Columns definition,
	Primary key/foreign key definition,
	[LIKE reference table name]
	ON database name. tablespace name)

VIEW:
	CREATE VIEW View name[Columns names] AS
	(SELECT *[columns names] FROM database name. tablespace name.tablename)
	ONdatabase name. tablespace name

	CREATE SEQUENCE sequence-name
	CREATE ALIAS alias-name
	CREATE INDEX index-name

CREATE can be performed on the below list of Objects.

CREATE ALIAS
CREATE DATABASE
CREATE FUNCTION
CREATE INDEX
CREATE PACKAGE
CREATE PROCEDURE 
CREATE ROLE
CREATE SEQUENCE
CREATE STOGROUP
CREATE SYNONYM
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW

What is DB2 Create statement ?

The CREATE TABLE statement defines a table. The definition must include its name and the names and attributes of its columns. The definition can include other attributes of the table, such as its primary key or check constraints. To create a created temporary table, use the CREATE GLOBAL TEMPORARY TABLE statement. To declare a declared temporary table, use the DECLARE GLOBAL TEMPORARY TABLE statement.

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

The privileges held by the authorization ID of the statement must include either DBADM authority, or CREATETAB authority in combination with further authorization, as described here, One of the following privileges and authorities, USE privilege on the table space, SYSADM, SYSCTRL.

Plus one of these privileges and authorities, IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the table does not exist, CREATEIN privilege on the schema, if the schema name of the table refers to an existing schema.

If a subtable is being defined, the authorization ID must be the same as the owner of the root table of the table hierarchy. To define a foreign key, the privileges held by the authorization ID of the statement must include one of the following on the parent table, REFERENCES privilege on the table REFERENCES privilege on each column of the specified parent key, CONTROL privilege on the table DBADM authority.

Create database views in Db2 using SQL

You can use SQL to create a view on tables and feature classes in an enterprise geodatabase to restrict what columns or records are available to view users, or you can define a view to join information from two tables or a feature class and a table. When you include the spatial column in the view definition, view users can visualize the features in a map in an ArcGIS Desktop client.

The examples in this topic show how to create views in an IBM Db2 database. One view restricts user access to specific columns. The other view presents content from two different tables. The examples are based on tables with the following definitions −

Table definition for employees

CREATE TABLE employees(
 emp_id integer not null,
 name varchar(32), 
 department smallint not null,
 hire_date date not null
);

Table definition for regions

CREATE TABLE regions(
 objectid integer not null,
 emp_id integer not null,
 reg_id integer not null,
 rname varchar(32),
 region st_geometry 
);

Grant privileges on the tables

If the user creating the view is not the owner of the table or tables on which the view is based, the table owner must grant the view creator at least the privilege to select from the tables. In this example, the tables on which the views are based (employees and regions) are owned by the user gdb. The user creating the views is user rocket.

db2 => connect to testdb user gdb using gdb.dbg
   Database Connection Information
 Database server        = DB2 10.5.5
 SQL authorization ID   = GDB
 Local database alias   = TESTDB

db2 => GRANT SELECT 
 ON employees 
 TO USER rocket;

db2 => GRANT SELECT 
 ON regions 
 TO USER rocket;

Create a view to restrict access

In this example, user rocket creates a view (view_dept_201) on the employees table to restrict access to only those rows where the department is 201 - 

db2 => connect to testdb user rocket using nopeeking
   Database Connection Information
 Database server        = DB2 10.5.5
 SQL authorization ID   = ROCKET
 Local database alias   = TESTDB

db2 => CREATE VIEW view_dept_201 
 AS SELECT emp_id, name, department, hire_date 
 FROM gdb.employees 
 WHERE department = 201;

Create a view to join two tables

In this example, the view—emp_regions_view—joins a spatial table (feature class) with a nonspatial table based on the emp_id column. The view includes the ObjectID, spatial column (region), and region name (rname) from the regions feature class along with the employee name and ID from the employees table.

connect to testdb user gdb using gdb.dbg 
   Database Connection Information 
Database server      = DB2 10.5.5 
SQL authorization ID = GDB 
Local database alias = TESTDB

CREATE VIEW emp_regions_view 
 AS SELECT (e.emp_name,e.emp_id,r.objectid,r.rname,r.region)
 FROM employees e, regions r
 WHERE e.emp_id = r.emp_id;
First Page
Next Page