{"id":1955,"date":"2013-03-22T16:56:52","date_gmt":"2013-03-22T08:56:52","guid":{"rendered":"http:\/\/rmohan.com\/?p=1955"},"modified":"2013-03-22T16:56:52","modified_gmt":"2013-03-22T08:56:52","slug":"db2-commands","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=1955","title":{"rendered":"DB2 Commands"},"content":{"rendered":"

Start an instance <\/span><\/p>\n

As an instance owner on the host running db2, issue the following command<\/p>\n

$ db2start<\/p>\n

Stopping the instance <\/span><\/p>\n

$ db2stop<\/p>\n

Connect to the database as instance owner <\/span><\/p>\n

$ db2<\/p>\n

as a user of the database:<\/p>\n

$source ~instance\/sqllib\/db2cshrc (csh users)<\/p>\n

$ . ~instance\/sqllib\/db2profile (sh users)<\/p>\n

$ db2 connect to databasename<\/p>\n

Create a table <\/span><\/p>\n

$ db2-> create table employee<\/p>\n

(ID SMALLINT NOT NULL,<\/p>\n

NAME VARCHAR(9),<\/p>\n

DEPT SMALLINT CHECK (DEPT BETWEEN 10 AND 100),<\/p>\n

JOB CHAR(5) CHECK (JOB IN (‘Sales’, ‘Mgr’, ‘Clerk’)),<\/p>\n

HIREDATE DATE,<\/p>\n

SALARY DECIMAL(7,2),<\/p>\n

COMM DECIMAL(7,2),<\/p>\n

PRIMARY KEY (ID),<\/p>\n

CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) > 1986 OR SALARY > 40500) )<\/p>\n

A simple version:<\/p>\n

db2-> create table employee ( Empno smallint, Name varchar(30))<\/p>\n

Create a schema <\/span><\/p>\n

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’<\/p>\n

CREATE SCHEMA joeschma AUTHORIZATION joe<\/p>\n

Create an alias <\/span><\/p>\n

The following SQL statement creates an alias WORKERS for the EMPLOYEE table:<\/p>\n

CREATE ALIAS WORKERS FOR EMPLOYEE<\/p>\n

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.<\/p>\n

Create an Index: <\/span><\/p>\n

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<\/p>\n

non-unique index called LNAME from the LASTNAME column on the EMPLOYEE table, sorted in ascending order:<\/p>\n

CREATE INDEX LNAME ON EMPLOYEE (LASTNAME ASC)<\/p>\n

The following SQL statement creates a unique index on the phone number column:<\/p>\n

CREATE UNIQUE INDEX PH ON EMPLOYEE (PHONENO DESC)<\/p>\n

Drop a database: <\/span><\/p>\n

Db2 drop database sample<\/p>\n

Alter tablespace <\/span><\/p>\n

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<\/p>\n

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:<\/p>\n

ALTER TABLESPACE RESOURCE<\/p>\n

ADD (DEVICE ‘\/dev\/rhd9’ 10000,<\/p>\n

DEVICE ‘\/dev\/rhd10’ 10000)<\/p>\n

The following SQL statement drops the table space ACCOUNTING:<\/p>\n

DROP TABLESPACE ACCOUNTING<\/p>\n

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:<\/p>\n

CREATE TEMPORARY TABLESPACE TEMPSPACE2 MANAGED BY SYSTEM USING (‘d’)<\/p>\n

Once TEMPSPACE2 is created, you can then drop the original temporary table space TEMPSPACE1 with the command: DROP TABLESPACE TEMPSPACE1<\/p>\n

Add Columns to an Existing Table<\/p>\n

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<\/p>\n

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:<\/p>\n

ALTER TABLE EMPLOYEE<\/p>\n

ADD MIDINIT CHAR(1) NOT NULL WITH DEFAULT<\/p>\n

ADD HIREDATE DATE<\/p>\n

ADD WORKDEPT CHAR(3)<\/p>\n

GrantPermissions by Users <\/span><\/p>\n

The following example grants SELECT privileges on the EMPLOYEE table to the user HERON:<\/p>\n

GRANT SELECT ON EMPLOYEE TO USER HERON<\/p>\n

The following example grants SELECT privileges on the EMPLOYEE table to the group HERON:<\/p>\n

GRANT SELECT ON EMPLOYEE TO GROUP HERON<\/p>\n

GRANT SELECT,UPDATE ON TABLE STAFF TO GROUP PERSONNL<\/p>\n

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:<\/p>\n

REVOKE SELECT ON EMPLOYEE FROM USER HERON<\/p>\n

To Check what permissions you have within the database<\/p>\n

SELECT * FROM SYSCAT.DBAUTH WHERE GRANTEE = USER AND GRANTEETYPE = ‘U’<\/p>\n

SELECT * FROM SYSCAT.COLAUTH WHERE GRANTOR = USER<\/p>\n

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:<\/p>\n

GRANT SELECT ON TABLE MYSELECTS TO PUBLIC<\/p>\n

And finally, remember to revoke SELECT privilege on the base table:<\/p>\n

REVOKE SELECT ON TABLE SYSCAT.TABAUTH FROM PUBLIC<\/p>\n

Delete Records from a table <\/span><\/p>\n

db2-> delete from employee where empno = ‘001’<\/p>\n

db2-> delete from employee<\/p>\n

The first example will delete only the records with emplno field = 001 The second example deletes all the records<\/p>\n

Import Command <\/span><\/p>\n

Requires one of the following options: sysadm, dbadm, control privileges on each participating table or view, insert or select privilege, example:<\/p>\n

db2->import from testfile of del insert into workemployee<\/p>\n

where testfile contains the following information 1090,Emp1086,96613.57,55,Secretary,8,1983-8-14<\/p>\n

or your alternative is from the command line:<\/p>\n

db2 ” import from ‘testfile’ of del insert into workemployee”<\/p>\n

db2 < test.sql where test.sql contains the following line:<\/p>\n

db2 import from test file of del insert into workemployee<\/p>\n

Load Command: <\/span><\/p>\n

Requires the following auithority: sysadm, dbadm, or load authority on the database:<\/p>\n

example: db2 “load from ‘testfile’ of del insert into workemployee”<\/p>\n

You may have to specify the full path of testfile in single quotes<\/p>\n

Authorization Level:<\/p>\n

One of the following:<\/p>\n

sysadm<\/p>\n

dbadm<\/p>\n

load authority on the database and<\/p>\n

INSERT privilege on the table when the load utility is invoked in INSERT mode, TERMINATE mode<\/p>\n

(to terminate a previous load insert operation), or RESTART mode (to restart a previous load insert<\/p>\n

operation)<\/p>\n

INSERT and DELETE privilege on the table when the load utility is invoked in REPLACE mode,<\/p>\n

TERMINATE mode (to terminate a previous load replace operation), or RESTART mode (to restart a<\/p>\n

previous load replace operation)<\/p>\n

INSERT privilege on the exception table, if such a table is used as part of the load operation.<\/p>\n

Caveat:<\/p>\n

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<\/p>\n

First to view tablestate:<\/p>\n

Db2 list tablespaces show detail will display the tablespace is in a load pending state.<\/p>\n

Db2tbst <tablespace state><\/p>\n

Here is the original query<\/p>\n

Db2 “load from ‘\/usr\/seela\/a.del’ of del insert into A”;<\/p>\n

If you break out of the load illegally (ctrl-c), the tablespace is left load pending.<\/p>\n

To correct:<\/p>\n

Db2 “load form ‘\/usr\/seela\/a.del’ of del terminate into A”;<\/p>\n

This will return the table to it’s original state and roll back the entries that you started loading.<\/p>\n

If you try to reset the tablespace with quiesce, it will not work . It’s an integrety issue<\/p>\n

 <\/p>\n

DB2BATCH- command <\/span><\/p>\n

Reads SQL statements from either a flat file or standard input, dynamically prepares and describes the statements and returns an answer set: Authorization: sysadmin .and Required Connection -None..eg<\/p>\n

db2batch -d databasename -f filename -a userid\/passwd -r outfile<\/p>\n

DB2expln – DB2 SQL Explain Tool <\/span><\/p>\n

Describes the access plan selection for static SQL statements in packages that are stored in the DB2 common server systems catalog. Given the database name, package name ,package creator abd section<\/p>\n

number the tool interprets and describes the information in these catalogs.<\/p>\n

DB2exfmt – Explain Table Format Tool <\/span><\/p>\n

DB2icrt – Create an instance <\/span><\/p>\n

DB2idrop – Dropan instance <\/span><\/p>\n

DB2ilist – List instances <\/span><\/p>\n

DB2imigr – Migrate instances <\/span><\/p>\n

DB2iupdt – Update instances <\/span><\/p>\n

Db2licm – Installs licenses file for product ; <\/span><\/p>\n

db2licm -a db2entr.lic<\/p>\n

DB2look – DB2 Statistics Extraction Tool <\/span><\/p>\n

Generates the updates statements required to make the catalog statistics of a test database match those of a production. It is advantageous to have a test system contain asubset of your production system’s data.<\/p>\n

This tool queries the system catalogs of a database and outputs a tablespace n table index, and column information about each table in that database Authorization: Select privelege on system catalogs Required<\/p>\n

Connection – None. Syntax<\/p>\n

db2look -d databasename -u creator -t Tname -s -g -a -p -o<\/p>\n

Fname -e -m -c -r -h<\/p>\n

where -s : generate a postscript file, -g a graph , -a for all users in the database, -t limits output to a particular tablename, -p plain text format , -m runs program in mimic mode, examples:<\/p>\n

db2look -d db2res -o output will write stats for tables created in db<\/p>\n

db2res in latex format<\/p>\n

db2look -p -a -d db2res -o output – will write stats in plain text format<\/p>\n

DB2 -list tablespaces show detail <\/span><\/p>\n

displays the following information as an example:<\/p>\n

Tablespaces for Current Database<\/p>\n

Tablespace ID = 0<\/p>\n

Name = SYSCATSPACE<\/p>\n

Type = System managed space<\/p>\n

Contents = Any data<\/p>\n

State = 0x0000<\/p>\n

Detailed explanation:<\/p>\n

Normal<\/p>\n

Total pages = 2925<\/p>\n

Useable pages = 2925<\/p>\n

Used pages = 2925<\/p>\n

Free pages = Not applicable<\/p>\n

High water mark (pages) = Not applicable<\/p>\n

Page size (bytes) = 4096<\/p>\n

Extent size (pages) = 32<\/p>\n

Prefetch size (pages) = 32<\/p>\n

Number of containers = 1<\/p>\n

db2tbst – Get tablespace state. <\/span><\/p>\n

Authorization – none , Required connection none, syntax db2tbst tabpespace-state:The state value is part of the output of list tablespaces example<\/p>\n

db2tbst 0X0000 returns state normal<\/p>\n

db2tbst 2 where 2 indicates tablespace id 2 will also work<\/p>\n

DB2dbdft – environment variable <\/span><\/p>\n

Defining this environment variable with the database you want to connect to automatically connects you to the database . example setenv db2dbdft sample will allow you to connect to sample by default.<\/p>\n

CLP – Command Line Processor Invocation: <\/span><\/p>\n

db2 starts the command line processor. The clp is used to execute database utilities, sql statements and online help. It offers a variety of command options and can be started in :<\/p>\n

1. interactive mode : db2-><\/p>\n

2. command mode where each command is prefixed by db2<\/p>\n

3. batch mode which uses the -f file input option<\/p>\n

Update the configuration in the database : <\/span><\/p>\n

Db2 =>update db cfg for sample using maxappls 60<\/p>\n

MAXFILOP = 64 2 – 9150<\/p>\n

db2 => update db cfg for sample using maxappls 160<\/p>\n

db2 => update db cfg for sample using AVG_APPLS 4<\/p>\n

db2 =>update db cfg for sample using MAXFILOP 256<\/p>\n

can see updated parameters from client<\/p>\n

tcpip ….. not started up properly Check the DB2COMM variable if it it is set<\/p>\n

db2set DB2COMM <enter><\/p>\n

How to terminate the database if processes are still attached:<\/p>\n

db2 force applications all<\/p>\n

db2stop<\/p>\n

db2start<\/p>\n

db2 connect to dbname (locally)<\/p>\n

How to trace logs withing the db2diag.log file:<\/p>\n

Connections to db fails:<\/p>\n

Move the db2diag.log from the sqllib\/db2dump directory to some other working directory ( mv db2diag.log <some other working directory )<\/p>\n

db2 update dbm cfg using diaglevel 4<\/p>\n

db2stop<\/p>\n

db2start<\/p>\n

db2trc on -l 8000000 -e 10<\/p>\n

db2 connect to dbname (locally)<\/p>\n

db2trc dump 01876.trc<\/p>\n

db2trc flw 01876.trc 01876.flw<\/p>\n

db2trc fmt 01876.trc 01876.fmt<\/p>\n

db2trc off<\/p>\n

Import data from ascii file to database<\/p>\n

db2 ” import from inp.data of del insert into test”<\/p>\n

db2 “load from ‘\/cs\/home\/tech1\/seela\/inp.data’ of del insert into seela.seela”<\/p>\n

db2 < test.sql<\/p>\n

Revoke permissions from the database from public:<\/p>\n

db2 => create database GO3421<\/p>\n

DB20000I The CREATE DATABASE command completed successfully.<\/p>\n

Now I want to revoke connect, createtab bindadd on database from public<\/p>\n

On server: db2 => revoke connect , createtab, bindadd on database from public<\/p>\n

Now on client, as techstu, I tried to connect to go3421<\/p>\n

db2 => connect to go3421<\/p>\n

SQL1060N User “TECHSTU ” does not have the CONNECT privilege. SQLSTATE=08004<\/p>\n

Now I have to grant connect privilege to group ugrad<\/p>\n

On server:<\/p>\n

db2 => grant connect, createtab on database to group ugrad<\/p>\n

DB20000I The SQL command completed successfully.<\/p>\n

Tested on client I can connect successfully.<\/p>\n

Now on the client, I can connect as a student, list tables but not select. I<\/p>\n

can still describe tables<\/p>\n

To prevent this:<\/p>\n

On server<\/p>\n

revoke select on table syscat.columns from public<\/p>\n

Now on client, I cannot describe but also on my tables.<\/p>\n

db2 => revoke select on table syscat.columns from public<\/p>\n

DB20000I The SQL command completed successfully.<\/p>\n

db2 => grant select on table syscat.columns to group ugrad<\/p>\n

On server:<\/p>\n

db2 => revoke select on table syscat.indexes from public<\/p>\n

DB20000I The SQL command completed successfully.<\/p>\n

select * from syscat.dbauth will display all the privileges for<\/p>\n

dbadm authority:<\/p>\n

DBADMAUTH CREATETABAUTH BINDADDAUTH CONNECTAUTH<\/p>\n

NOFENCEAUTH IMPLSCHEMAAUTH LOAD AUTH<\/p>\n

select<\/p>\n

TABNAME,DELETEAUTH,INSERTAUTH,SELECTAUTH from<\/p>\n

syscat.tabauth<\/p>\n

grant connect, createtab<\/p>\n

grant connect, createtab on database to user techstu<\/p>\n

to group ugrad<\/p>\n

Instance Level Authority <\/span><\/p>\n

db2 get dbm cfg<\/p>\n

db2 get admin cfg<\/p>\n

db2 get db cfg<\/p>\n

CLP using filename on the command line<\/p>\n

Db2 -f filename.clp<\/p>\n

The -f option directs the clp to accept input from file.<\/p>\n

Db2 +c -v +t infile .. The option can be prefixed by a + sign or turned on by a letter with a -sign<\/p>\n

+c is turned off, -v turned on and -f turned on<\/p>\n

c is for commit, v for verbose and f for filename<\/p>\n

-t termination character is set to semicolon<\/p>\n","protected":false},"excerpt":{"rendered":"

Start an instance <\/p>\n

As an instance owner on the host running db2, issue the following command<\/p>\n

$ db2start<\/p>\n

Stopping the instance <\/p>\n

$ db2stop<\/p>\n

Connect to the database as instance owner <\/p>\n

$ db2<\/p>\n

as a user of the database:<\/p>\n

$source ~instance\/sqllib\/db2cshrc (csh users)<\/p>\n

$ . ~instance\/sqllib\/db2profile (sh users)<\/p>\n

$ db2 connect to databasename<\/p>\n

[…]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/1955"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1955"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/1955\/revisions"}],"predecessor-version":[{"id":1957,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/1955\/revisions\/1957"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1955"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1955"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1955"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}