IBM DB2 - Database drop commands

 IBM DB2 - Database drop commands 

Removing The Old Database:

Login into db2inst2 Login and execute the below commands

db2 disconnect all

db2 deactivate database <<Database Name>>

db2 drop database <<Database Name >>

prompting back to directory: 

rm –Rf HBMEXP(Old Extracted Folder)

rm –Rf HBMDATABASE1(where the old db2 DB available)

IBM DB2 - Database Creation Steps

IBM DB2 - Database Creation Steps 

Below provided are the steps I have used to create a database in IBM db2 database engine. 

Connect to db2 prompt after installation:

$ db2

(c) Copyright IBM Corporation 1993,2007

Command Line Processor for DB2 Client 10.1.0

You can issue database manager commands and SQL statements from the command prompt. For example:

    db2 => connect to sample

    db2 => bind sample.bnd

db2 => db2start

DB20000I  The DB2START command completed successfully.

db2 =>

*****************************************************************

Create a new database with the name  'database1'

$ db2 create db database1 on /glotus4/db2/db1 using codeset UTF-8 territory US pagesize 4096 dft_extent_sz 2;

DB20000I  The CREATE DATABASE command completed successfully.

$ db2 connect to database1;

   Database Connection Information

 Database server        = DB2/AIX64 10.1.0

 SQL authorization ID   = DASUSR2

 Local database alias   = DATABASE1

$ db2 create database partition group dpg_32k;

DB20000I  The SQL command completed successfully.

$ db2 create bufferpool bp_32k size 1000 automatic  pagesize 32 k;

DB20000I  The SQL command completed successfully.

$

***************************************************************

Set all the database parameters:

$ db2 update db cfg using DBHEAP AUTOMATIC;

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

$ db2 update db cfg using APPLHEAPSZ 4096;

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

$ db2 update db cfg using APP_CTL_HEAP_SZ 1024;

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

SQL1363W  Database must be deactivated and reactivated before the changes to one or more of the configuration parameters will be effective.

$ db2 update db cfg using LOGFILSIZ 10240;

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

SQL1363W  Database must be deactivated and reactivated before the changes to one or more of the configuration parameters will be effective.

$ db2 update db cfg using AUTO_RUNSTATS OFF;

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

$ db2 disconnect all;

DB20000I  The SQL DISCONNECT command completed successfully.

$ db2 connect to database1;

   Database Connection Information

 Database server        = DB2/AIX64 10.1.0

 SQL authorization ID   = DASUSR2

 Local database alias   = DATABASE1

$ db2 create bufferpool bpool size 500 pagesize 32K;

DB20000I  The SQL command completed successfully.

**********************************************************

Create tablespaces on the database:

$ db2 CREATE TEMPORARY TABLESPACE TEMPSPACE2 pagesize 32k EXTENTSIZE 80 bufferpool bpool;

DB20000I  The SQL command completed successfully.

$ db2 create large tablespace DATA in dpg_32k pagesize 32 k extentsize 2 bufferpool bp_32k AUTORESIZE YES INCREASESIZE 5 PERCENT MAXSIZE NONE;

DB20000I  The SQL command completed successfully.

$ db2 create large tablespace INDEX in dpg_32k pagesize 32 k  extentsize 2 bufferpool bp_32k AUTORESIZE YES INCREASESIZE 5 PERCENT MAXSIZE NONE;

DB20000I  The SQL command completed successfully.

$

**********************************************************

Grant privileges to the user:

$ db2 GRANT DBADM, CREATETAB, BINDADD, CONNECT, CREATE_NOT_FENCED_ROUTINE, IMPLICIT_SCHEMA, LOAD, CREATE_EXTERNAL_ROUTINE,  QUIESCE_CONNECT, SECADM ON DATABASE TO USER db2inst2;

DB20000I  The SQL command completed successfully.

$

$ db2 grant use of tablespace DATA to user db2inst2;

DB20000I  The SQL command completed successfully.

$ db2 grant use of tablespace INDEX to user db2inst2;

DB20000I  The SQL command completed successfully.

$

db2 => db2 catalog tcpip node database1 remote <ip address> server 50000 

*****************************************************

Catalog the ip and node details 

catalog tcpip node database1 remote <ip address> server 50000 

catalog tcpip node database1 remote <ip address> server 1525

catalog dcs database database1 as database1

catalog database database1 as database1 at node SAN_NODE authentication dcs

connect to database1 user dasusr1: Database Connection Information Database server = DB2/AIX64 9.5.0 SQL authorization ID = < schema_name > Local database alias = <database name>

*****************************************************

Created node must be displayed upon testing:

db2 => LIST NODE DIRECTORY

Node Directory

Number of entries in the directory = 2

Node 1 entry:

 Node name                      = DATABASE1

 Comment                        =

 Directory entry type           = LOCAL

 Protocol                       = TCPIP

 Hostname                       = <ip address>

 Service name                   = 50000

db2 => db2 LIST DATABASE DIRECTORY

SQL0104N  An unexpected token "db2" was found following "BEGIN-OF-STATEMENT".

Expected tokens may include:  "SELECT".  SQLSTATE=42601

db2 => LIST DATABASE DIRECTORY

System Database Directory

Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = DATABASE1

 Database name                        = DATABASE1

 Node name                            = DATABASE1

 Database release level               = f.00

 Comment                              =

 Directory entry type                 = Remote

 Catalog database partition number    = -1

 Alternate server hostname            =

 Alternate server port number         =

Space Reclamation Procedure for Oracle tables - Move tablespace

 Space Reclamation Procedure for oracle objects: move tablespace

There are various space reclamation procedures available for the oracle database and the tables. Below is one of the methodologies to move all the tables and objects to the new tablespace. Though I have run it successfully in an environment, I remember correcting it while running it. 

Pre-requisites:

1. Run it in test environment before running it in any important environment.

2. Have enough space in the new tablespace before initiating the scripts. 

3. If a lob segment is not moved, then that particular table's data has to be moved to a new table created. 

4. Always connect to the schema to be moved to run the scripts. Must not run as a 'SYS' user. 

5. Tablespaces 'TBSDATA' and 'TBSINDEX' were created for the below example. 

TEMP TABLES:

CREATE TABLE RECLAIM_SEGMENTS AS SELECT * FROM USER_SEGMENTS; CREATE TABLE COMPLETED_TABLES AS SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE 1=2; CREATE TABLE NOT_COMPLETED AS SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE 1=2;

PL/SQL PROCEDURE: 

SET SERVEROUTPUT ON BEGIN FOR cur_rec IN (SELECT SEGMENT_NAME, SEGMENT_TYPE FROM  RECLAIM_SEGMENTS ) LOOP BEGIN EXECUTE IMMEDIATE 'ALTER TABLE  ' || cur_rec.SEGMENT_NAME || ' enable row movement '; EXECUTE IMMEDIATE 'ALTER TABLE  ' || cur_rec.SEGMENT_NAME || ' MOVE TABLESPACE TBSLIVEDATA  parallel 20 nologging '; EXECUTE IMMEDIATE 'ALTER TABLE  ' || cur_rec.SEGMENT_NAME || ' MOVE LOB(XMLRECORD) STORE AS (TABLESPACE TBSDATA)  parallel 20 nologging '; BEGIN FOR cur_index in (select INDEX_NAME from DBA_INDEXES where table_name='' || cur_rec.SEGMENT_NAME  || ''   and INDEX_NAME NOT LIKE '%SYS%'  and INDEX_NAME NOT LIKE '%LOB%'    and owner='USER') LOOP EXECUTE IMMEDIATE 'ALTER INDEX  ' || cur_index.INDEX_NAME ||' REBUILD TABLESPACE TBSINDEX  parallel 10 nologging '; END LOOP; END; EXECUTE IMMEDIATE 'INSERT INTO COMPLETED_TABLES  VALUES (''' || cur_rec.SEGMENT_NAME || ''')'; EXECUTE IMMEDIATE 'DELETE FROM RECLAIM_SEGMENTS WHERE SEGMENT_NAME ='''|| cur_rec.SEGMENT_NAME || ''''; EXECUTE IMMEDIATE 'ALTER TABLE  ' || cur_rec.SEGMENT_NAME || ' disable row movement '; Commit; EXCEPTION WHEN OTHERS THEN EXECUTE IMMEDIATE 'INSERT INTO NOT_COMPLETED  VALUES (''' || cur_rec.SEGMENT_NAME || ''')'; DBMS_OUTPUT.put_line('FAILED: COULD NOT BE ALTERED ' || cur_rec.SEGMENT_TYPE || ' "' || cur_rec.SEGMENT_NAME || '"'); END; END LOOP; END; /

PL/SQL procedure to Drop all the objects from the oracle user

Procedure to drop the objects from a Oracle Schema : 

Below is the pl/sql procedure to drop the user objects using condition. 

Use it with EXTREME CAUTION, as the below steps of procedure drops the objects from the connected user. 

STEP 1: 

Create a temporary table with object_name and object_type as the columns, create a combined index as it gives better performance. 

SQL 1: create table DEV1_OBJECTS_DEL as select object_name, object_type from user_objects; 

SQL 2: create unique index ix_DEV_OBJECTS on DEV1_OBJECTS_DEL( object_name, object_type ) parallel 10; 

STEP 2: 

Run the below procedure, the procedure refers to the 'DEV1_OBJECTS_DEL' table and drops the objects in the table. 

PL/SQL 1: 

----------------------------------------------------------------------------------------- 

SET SERVEROUTPUT ON;

DECLARE

cursor cur is SELECT object_name, object_type FROM  DEV1_OBJECTS_DEL;

BEGIN

FOR cur_rec IN CUR LOOP

BEGIN

EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END LOOP;

END;

/

---------------------------------------------------------------------------------------------- 

NOTE: SYS objects and SYSTEM generated constraints cannot be dropped using the procedure, you need to drop it manually. 

Happy Coding.