0 Basics of DB2_1

Start an instance in DB2

As an instance owner on the host running db2, issue the following command

$ db2start

Stopping the instance in DB2

$ db2stop

Connect to the database as instance owner in DB2

$ db2

As a user of the database:

$source ~instance/sqllib/db2cshrc (csh users)

$ . ~instance/sqllib/db2profile (sh users)

$ db2 connect to databasename

Create a table in DB2

$ db2-> create table employee

(ID SMALLINT NOT NULL,

NAME VARCHAR(9),

DEPT SMALLINT CHECK (DEPT BETWEEN 10 AND 100),

JOB CHAR(5) CHECK (JOB IN ('Sales', 'Mgr', 'Clerk')),

HIREDATE DATE,

SALARY DECIMAL(7,2),

COMM DECIMAL(7,2),

PRIMARY KEY (ID),

CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) > 1986 OR SALARY > 40500) )

A simple version of create table in DB2

db2-> create table employee ( Empno smallint, Name varchar(30))

Create a schema in DB2

If a user has SYSADM or DBADM authority, then the user can create a schema with any valid name. When a database is created, IMPLICIT_SCHEMA authority is granted to PUBLIC (that is, to all users). The following example creates a schema for an individual user with the authorization ID 'joe'

CREATE SCHEMA joeschma AUTHORIZATION joe

Create an alias in DB2

The following SQL statement creates an alias WORKERS for the EMPLOYEE table:

CREATE ALIAS WORKERS FOR EMPLOYEE

You do not require special authority to create an alias, unless the alias is in a schema other than the one owned by your current authorization ID, in which case DBADM authority is required.

Create an Index in DB2

The physical storage of rows in a base table is not ordered. When a row is inserted, it is placed in the most convenient storage location that can accommodate it. When searching for rows of a table that meet a particular selection condition and the table has no indexes, the entire table is scanned. An index optimizes data retrieval without performing a lengthy sequential search. The following SQL statement creates a non-unique index called LNAME from the LASTNAME column on the EMPLOYEE table, sorted in ascending order:

CREATE INDEX LNAME ON EMPLOYEE (LASTNAME ASC)

The following SQL statement creates a unique index on the phone number column:

CREATE UNIQUE INDEX PH ON EMPLOYEE (PHONENO DESC)

Drop a database in DB2

Db2 drop database sample

Alter tablespace in DB2

Adding a Container to a DMS Table Space You can increase the size of a DMS table space (that is, one created with the MANAGED BY DATABASE clause) by adding one or more containers to the table

space. The following example illustrates how to add two new device containers (each with 40 000 pages) to a table space on a UNIX-based system:

ALTER TABLESPACE RESOURCE

ADD (DEVICE '/dev/rhd9' 10000,

DEVICE '/dev/rhd10' 10000)

The following SQL statement drops the table space ACCOUNTING:

DROP TABLESPACE ACCOUNTING

You can reuse the containers in an empty table space by dropping the table space but you must COMMIT the DROP TABLESPACE command, or have had AUTOCOMMIT on, before attempting to reuse the containers. The following SQL statement creates a new temporary table space called TEMPSPACE2:

CREATE TEMPORARY TABLESPACE TEMPSPACE2 MANAGED BY SYSTEM USING ('d')

Once TEMPSPACE2 is created, you can then drop the original temporary table space TEMPSPACE1 with the command: DROP TABLESPACE TEMPSPACE1

Add Columns to an Existing Table

When a new column is added to an existing table, only the table description in the system catalog is modified, so access time to the table is not affected immediately. Existing records are not physically altered until they are modified using an UPDATE statement. When retrieving an existing row from the table, a null or default value is provided for the new column, depending on how the new column was defined. Columns that are added after a table is created cannot be defined as NOT NULL: they must be defined as either NOT NULL WITH DEFAULT or as nullable. Columns can be added with an SQL statement. The following statement uses the ALTER TABLE statement to add three columns to the EMPLOYEE table:

ALTER TABLE EMPLOYEE

ADD MIDINIT CHAR(1) NOT NULL WITH DEFAULT

ADD HIREDATE DATE

ADD WORKDEPT CHAR(3)

GrantPermissions by Users in DB2

The following example grants SELECT privileges on the EMPLOYEE table to the user HERON:

GRANT SELECT ON EMPLOYEE TO USER HERON

The following example grants SELECT privileges on the EMPLOYEE table to the group HERON:

GRANT SELECT ON EMPLOYEE TO GROUP HERON

GRANT SELECT,UPDATE ON TABLE STAFF TO GROUP PERSONNL

If a privilege has been granted to both a user and a group with the same name, you must specify the GROUP or USER keyword when revoking the privilege. The following example revokes the SELECT privilege on the EMPLOYEE table from the user HERON:

REVOKE SELECT ON EMPLOYEE FROM USER HERON

To Check what permissions you have within the database

SELECT * FROM SYSCAT.DBAUTH WHERE GRANTEE = USER AND GRANTEETYPE = 'U'

SELECT * FROM SYSCAT.COLAUTH WHERE GRANTOR = USER

At a minimum, you should consider restricting access to the SYSCAT.DBAUTH, SYSCAT.TABAUTH, SYSCAT.PACKAGEAUTH, SYSCAT.INDEXAUTH, SYSCAT.COLAUTH, and SYSCAT.SCHEMAAUTH catalog views. This would prevent information on user privileges, which could be used to target an authorization name for break-in, becoming available to everyone with access to the database. The following statement makes the view available to every authorization name:

GRANT SELECT ON TABLE MYSELECTS TO PUBLIC

And finally, remember to revoke SELECT privilege on the base table:

REVOKE SELECT ON TABLE SYSCAT.TABAUTH FROM PUBLIC

Delete Records from a table in DB2

db2-> delete from employee where empno = '001'

db2-> delete from employee

The first example will delete only the records with emplno field = 001 The second example deletes all the records

Import Command in DB2

Requires one of the following options: sysadm, dbadm, control privileges on each participating table or view, insert or select privilege, example:

db2->import from testfile of del insert into workemployee

where testfile contains the following information 1090,Emp1086,96613.57,55,Secretary,8,1983-8-14

or your alternative is from the command line:

db2 " import from 'testfile' of del insert into workemployee"

db2 <>

db2 import from test file of del insert into workemployee

Load Command in DB2

Requires the following auithority: sysadm, dbadm, or load authority on the database:

example: db2 "load from 'testfile' of del insert into workemployee"

You may have to specify the full path of testfile in single quotes

Authorization Level:

One of the following:

sysadm

dbadm

load authority on the database and

INSERT privilege on the table when the load utility is invoked in INSERT mode, TERMINATE mode

(to terminate a previous load insert operation), or RESTART mode (to restart a previous load insert operation)

INSERT and DELETE privilege on the table when the load utility is invoked in REPLACE mode,

TERMINATE mode (to terminate a previous load replace operation), or RESTART mode (to restart a previous load replace operation)

INSERT privilege on the exception table, if such a table is used as part of the load operation.

Caveat:

If you are performing a load operation and you CTRL-C out of it, the tablespace is left in a load pending state. The only way to get out of it is to reload the data with a terminate statement

First to view tablestate:

Db2 list tablespaces show detail will display the tablespace is in a load pending state.

Db2tbst

Here is the original query

Db2 "load from '/usr/seela/a.del' of del insert into A";

If you break out of the load illegally (ctrl-c), the tablespace is left load pending.

To correct:

Db2 "load form '/usr/seela/a.del' of del terminate into A";

This will return the table to its original state and roll back the entries that you started loading.

If you try to reset the tablespace with quiesce, it will not work. It's an integrity issue