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.

Steps to create a database using windows cmd prompt:

Steps to create a database using windows cmd prompt:

Pre-requisite:

1. Oracle Database must be installed:

2. Basic knowledge of script and database is must.

3. Run the below scripts in ORACLE HOME path. 

Step 1:

Set dbname and parameters

set /p ORACLE_HOME=Enter the ORACLE_HOME path:

cd %ORACLE_HOME%

cd ../jjj../..

cd oradata

set /p name=Enter the database name:

mkdir %name%

cd %name%

echo "YOUR FILES WILL BE STORED ON THIS DIRECTORY: %cd%"

mkdir diag

Step 2:

Create file 1.sql:

echo startup pfile='%cd%\init%name%.ora' nomount; > 1.sql

echo create spfile from pfile='%cd%\init%name%.ora'; >> 1.sql

Step 3:

Create init.ora file with parameters and values

echo pga_aggregate_target=922746880 > init%name%.ora

echo sga_target=2751463424 >> init%name%.ora

echo compatible='11.2.0.0.0' >> init%name%.ora

echo control_files='%cd%/control01.ctl','%cd%\control02.ctl' >> init%name%.ora

echo db_block_size=8192 >> init%name%.ora

echo db_name=%name% >> init%name%.ora

echo diagnostic_dest='%cd%\diag' >> init%name%.ora

echo processes=600 >> init%name%.ora

echo undo_tablespace='undotbs' >> init%name%.ora

echo open_cursors=5000 >> init%name%.ora

Step 3:

Create file 2.sql with create database query.

echo CREATE DATABASE "%name%" > 2.sql

echo LOGFILE GROUP 1 '%cd%\redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '%cd%\redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '%cd%\redo03.log' SIZE 50M BLOCKSIZE 512

echo DATAFILE '%cd%\system01.dbf' size 500m REUSE >> 2.sql

echo SYSAUX DATAFILE '%cd%\sysaux01.dbf' SIZE 325M REUSE >> 2.sql

echo UNDO TABLESPACE undotbs DATAFILE '%cd%\undotbs01.dbf' SIZE 900M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED >> 2.sql

echo DEFAULT TABLESPACE users DATAFILE '%cd%\users01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED >> 2.sql

echo DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '%cd%\temptbs01.dbf' SIZE 200M REUSE; >> 2.sql

echo CREATE TABLESPACE T24DATA DATAFILE '%cd%\T24DATA.DBF' SIZE 500M AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; > tablespace.sql

echo CREATE TABLESPACE T24INDEX DATAFILE '%cd%\T24INDEX.DBF' SIZE 500M AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; >> tablespace.sql

Step 4:

Create file user.sql with privileges 

echo CREATE USER t24 IDENTIFIED BY t24 DEFAULT TABLESPACE T24DATA QUOTA UNLIMITED ON T24DATA QUOTA UNLIMITED ON T24INDEX; > user.sql

echo grant connect to t24; >> user.sql

echo grant resource to t24; >> user.sql

echo grant query rewrite to t24; >> user.sql

echo grant create synonym to t24; >> user.sql

echo grant drop any procedure to t24; >> user.sql

echo grant create any table to t24; >> user.sql

echo grant select any table to t24; >> user.sql

echo grant update any table to t24; >> user.sql

echo grant insert any table to t24; >> user.sql

echo grant delete any table to t24; >> user.sql

echo grant drop any table to t24; >> user.sql

echo grant create any index to t24; >> user.sql

echo grant alter any index to t24; >> user.sql

echo grant drop any index to t24; >> user.sql

echo grant create any view to t24; >> user.sql

echo grant drop any view to t24; >> user.sql

echo grant create any directory to t24; >> user.sql

echo grant exp_full_database to t24; >> user.sql

echo grant imp_full_database to t24; >> user.sql

echo @%ORACLE_HOME%/rdbms/admin/catblock.sql >> user.sql

echo grANT EXECUTE ON DBMS_LOCK TO T24; >> user.sql

Step 5:

Create file run.sql with order of files to run 

echo @1.sql; > run.sql

echo @2.sql; >> run.sql

echo @%ORACLE_HOME%/rdbms/admin/catalog.sql; >> run.sql

echo @%ORACLE_HOME%/rdbms/admin/catproc.sql; >> run.sql

echo CONNECT system/system as sysdba; >> run.sql

echo @%ORACLE_HOME%/sqlplus/admin/pupbld.sql; >> run.sql

echo startup force >> run.sql

echo @tablespace.sql; >> run.sql

echo @user.sql; >> run.sql

Update the %name% with actual db name you are going to create and run the below steps. :

ORADIM -NEW -SID %name%

set ORACLE_SID=%name%

sqlplus / as sysdba < run.sql

Linux script file to create database in Oracle

To create oracle database, user and tablespace in linux, use the below script file. Please go through the script file carefullly before executing or creating the database, as the values of sga_target and pga_aggregate_target are provided very less


kasi.sh : 

cd $ORACLE_HOME
ORACLEHOME=$ORACLE_HOME
cd ../../..
cd oradata
echo "Please enter the database name"
read name
mkdir $name
cd $name
dir_path=`pwd`;
echo "YOUR FILES WILL BE STORED ON THIS DIRECTORY: $dir_path"

cat > 1.sql <<EOF
startup pfile='$dir_path/init$name.ora' nomount;
create spfile from pfile='$dir_path/init$name.ora';
EOF

cat > init$name.ora <<EOF
pga_aggregate_target=922746880
sga_target=2751463424
compatible='11.2.0.0.0'
control_files='$dir_path/control01.ctl','$dir_path/control02.ctl'
db_block_size=8192
db_name='$name'
diagnostic_dest='$ORACLE_BASE/diag'
processes=600
undo_tablespace='undotbs'
open_cursors=5000
EOF

cat > create$name.sql <<EOF
CREATE DATABASE "$name"
LOGFILE
  GROUP 1 '$dir_path/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '$dir_path/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '$dir_path/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE '$dir_path/system01.dbf' size 500m REUSE
SYSAUX DATAFILE '$dir_path/sysaux01.dbf' SIZE 325M REUSE
UNDO TABLESPACE undotbs DATAFILE '$dir_path/undotbs01.dbf' SIZE 900M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TABLESPACE users DATAFILE '$dir_path/users01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '$dir_path/temptbs01.dbf' SIZE 200M REUSE;
EOF

cat > tablespace.sql << EOF
CREATE TABLESPACE DATA DATAFILE '$dir_path/DATA.DBF' SIZE 500M AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
EOF

cat > user.sql << EOF
CREATE USER user1 IDENTIFIED BY pass1 DEFAULT TABLESPACE DATA QUOTA UNLIMITED ON DATA;
grant connect to user1;
grant resource to user1;
grant query rewrite to user1;
grant create synonym to user1;
grant drop any procedure to user1;
grant create any table to user1;
grant select any table to user1;
grant update any table to user1;
grant insert any table to user1;
grant delete any table to user1;
grant drop any table to user1;
grant create any index to user1;
grant alter any index to user1;
grant drop any index to user1;
grant create any view to user1;
grant drop any view to user1;
grant create any directory to user1;
grant exp_full_database to user1;
grant imp_full_database to user1;
@?/rdbms/admin/catblock.sql;
grANT EXECUTE ON DBMS_LOCK TO user1;
EOF

export $ORACLE_SID=$name;
sqlplus -s /nolog << EOF
CONNECT / as sysdba;
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
@1.sql;
@create$name.sql;
@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catproc.sql;
conn system/manager;
@?/sqlplus/admin/pupbld.sql;
startup force;
@tablespace.sql;
@user.sql;
shut immediate
EOF

DELETE RECORDS USING BULK COLLECT - ORACLE

Below is the procedure to delete records from table1 using bulk collect method. In the below example the limit is specified as 100, 000 records.


Pl/Sql :

SET SERVEROUTPUT ON
DECLARE
TYPE t_bulk_del IS TABLE OF TABLE2%ROWTYPE;
l_tab t_bulk_del;
CURSOR bulk_del IS
SELECT RECID FROM TABLE2;
BEGIN
OPEN bulk_del;
LOOP
FETCH bulk_del
BULK COLLECT INTO l_tab LIMIT 100000;
forall vloop in 1 .. l_tab.count
delete from TABLE1 where recid = l_tab(vloop);
commit;
EXIT WHEN l_tab.count = 0;
DBMS_OUTPUT.put_line(l_tab.count || ' rows');
END LOOP;
CLOSE bulk_del;
END;
/