Database | Hexónio Blog

Oracle Table and Database Size

To obtain the table size on an Oracle database, the following script can be used:


SELECT lower(table_name) AS tableName

    ,tablespaceName

    ,totalRows

    ,blocks*8/1024 AS Mbytes

FROM all_tables 

WHERE owner = USER

ORDER BY 1,2;

Performing the sum of the sizes of all tables allow us to get an estimate of the database size:


SELECT sum(blocks*8/1024) AS TotalMbytes

FROM all_tables 

WHERE owner = USER;

The previous script may not be accurate because it actually gets the sum of the size of the tables, which can be simply viewed as the data size, since a database is more than just data on tables.

Install Oracle Database in Linux

This is a guide that will show how to install Oracle Database (version 12c at this time) in Linux (Oracle Linux 7 is used).
The following is assumed:

Oracle Linux (or any Oracle Database approved Linux distribution) is installed with a minimum working set, i. e. working from the command line, as it is accessible via SSH.
A desktop running a Linux distribution with a window manager is used and the connections are performed to the server via SSH.

To simplify things, let’s say Oracle Linux is installed using the default “Minimal Install” option.

Setting up Linux

First thing to do is to update the system using

sudo yum update -y

Then add necessary software

sudo yum install -y binutils.x86_64 compat-libcap1.x86_64 gcc.x86_64 gcc-c++.x86_64 glibc.i686 glibc.x86_64 glibc-devel.i686 glibc-devel.x86_64 ksh compat-libstdc++-33 libaio.i686 libaio.x86_64 libaio-devel.i686 libaio-devel.x86_64 libgcc.i686 libgcc.x86_64 libstdc++.i686 libstdc++.x86_64 libstdc++-devel.i686 libstdc++-devel.x86_64 libXi.i686 libXi.x86_64 libXtst.i686 libXtst.x86_64 make.x86_64 sysstat.x86_64 zip unzip openssh-server

Getting GUI on Server

The Oracle Database installer is graphical a Java desktop application, and thus a window manager is required. If your server already has a graphical window manager, you can skip this

yum grouplist

Will show the available options. The easiest is just

sudo yum groupinstall "Server with GUI"

Though you may only require

sudo yum groupinstall "X Window System"

Or even a more lightweight option

sudo yum install -y xorg-x11-xauth xorg-x11-apps

Additional packages installation may be required depending.

Setting up Remote Display

The next step is to prepare to server to run the graphical applications in the desktop.

Server

In the server perform the following:

If you use the C shell, type:
```
setenv DISPLAY desktop-ip:n.n
```
If you use the Bourne shell, type:
```
DISPLAY=desktop-ip:n.n
export DISPLAY
```
If you use the Korn shell, type:
```
export DISPLAY=desktop-ip:n.n
```

Replace desktop-ip with the desktop IP address and n.n with the desktop display number.screen number (usually 0:0)

If the server and the desktop are all on the same network, usually setting DISPLAY to localhost:10.0 is good enough.

Desktop

In the desktop perform the following:

xhost +
ssh -X user@server

Where server is the server IP, user is your server username and login using your credentials.

If you get a warning or error message about xauth, don’t worry about it. If you logout and login again the same way, it should have disappeared since thew first login should have fixed it.

To check if everything is working properly, after authenticated in the server from the desktop with using ssh -X, do

xclock

and the clock application should appear in the desktop screen.

Running Oracle Database Installer

The Oracle Database installer comes in a zip file, e.g. linuxx64_12201_database.zip, confirm the version and architecture is the correct one. Assuming the Oracle Database installer is ready for install, i.e. the zip file is already on the server (sftp may help on transferring the file from the desktop to the server), uncompress the file

unzip linuxx64_12201_database.zip

and then run the installer

cd database
./runInstaller

A precondition checkup is performed by the installer and you may need to do some additional things, such as install extra software packages. It is highly recommended that you do not ignore the dependencies (maybe except the swap) as it may have prevent the database to execute correctly.
During the installation, you will be required to run some scripts manually with administration privileges.

Finishing It

After the installation you may think that opening up a browser and pointing it to “https://server-ip:5500/” may be enough, but that may not be the case.

Setting up Database Administrator User

To add a databse administrator, from the server command line, run

sqlplus /as sysdba

create user admin identified by pass;
grant dba to admin; 
conn admin/pass;

Where “admin” is the username for the administration and “pass” is the correspondent password.

Firewall

Oracle database works on specific ports, thus it is mandatory that the firewall allows them to carry traffic:

firewall-cmd --zone=public --add-port=1521/tcp --add-port=5500/tcp --add-port=5520/tcp --add-port=3938/tcp --permanent
firewall-cmd --reload

Environment Settings

Edit your .bash_profile and set the following

TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/<INSTALL_DIR>/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=<ORA_SID>; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/lib64; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

Where <INSTALL_DIR> is the Oracle installation directory (you can check the installation log “oracle/oraInventory/logs“) and <ORA_SID> is the Oracle SID (usually “oracle”).

Ready

Now try https://server-ip:5500/em/ where server-ip is the server IP address. It is necessary to accept the certificate and the to have Flash installed, since Enterprise Manager is a Flash application that is served under HTTPS.
If the application does not execute properly, try using Internet Explorer from Microsoft Windows to see if it is working.

Install and Configure PostgreSQL

Here’s how to correctly install and configure PostgreSQL.

Install

First update the system

sudo apt-get update

Then install the packages:

sudo apt-get install postgresql postgresql-contrib

Create Database and User

Now let’s create a PostgreSQL roles and databases.

PostgreSQL uses the concept of roles to distinguish the variety of users that can connect to a database. After a fresh install, the default PostgreSQL user is actual named “postgres”.

sudo su postgres

and add a user

createuser -e -P USERNAME

set the password for the specified USERNAME.

Now create a database and assign the user to it

createdb -O USERNAME DATABASENAME

Connect to the database using

psql -d DATABASENAME -U USERNAME

And that’s it.

Correctly Uninstall PostgreSQL

Uninstalling PostgreSQL may leave some undesired files. Here’s how to purge it:

First, uninstall all packages from PostgreSQL from the system using

sudo apt-get --purge remove postgresql\*

Then remove all the configuration and library stuff

sudo rm -r /etc/postgresql/
sudo rm -r /etc/postgresql-common/
sudo rm -r /var/lib/postgresql/

And finally, remove the user and group

sudo userdel -r postgres
sudo groupdel postgres

Upgrade PostgreSQL from 9.1 to 9.3 on Kubuntu

This seven steps will perform the upgrade of PostgreSQL from version 9.1 to version 9.3.
This also works in Ubuntu and it can also be used to upgrade between any version numbers.

To upgrade between any versions, just changed the 9.1 for the legacy version number and the 9.3 for the new version number.

First install the necessary dependencies

sudo apt-get update
sudo apt-get -y install python-software-properties

Second, add the PostgreSQL repository

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Third, setup the repository

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'

Fourth, install PostgreSQL 9.3.

sudo apt-get install postgresql-9.3 postgresql-server-dev-9.3 postgresql-contrib-9.3

Fifth, perform the upgrade process.
The upgrade process is performed by having both servers running at the same time. Note that the new 9.3 version will run on a different port, 5433 as specified in the script bellow, and it will be set to the default port latter when the legacy version is uninstalled and the default port becomes available.

sudo su -l postgres

psql -d template1 -p 5433
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
\q #logout from database

service postgresql stop
/usr/lib/postgresql/9.3/bin/pg_upgrade -b /usr/lib/postgresql/9.1/bin -B /usr/lib/postgresql/9.3/bin -d /var/lib/postgresql/9.1/main/ -D /var/lib/postgresql/9.3/main/ -O " -c config_file=/etc/postgresql/9.3/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.1/main/postgresql.conf"
exit # logout postgresql back to previous user

Sixth, remove the 9.1 version.

sudo apt-get remove postgresql-9.1

Seventh, set the new version server port back to the default value and restart the service.

sudo vim /etc/postgresql/9.3/main/postgresql.conf # find old port of 5433 and change it to 5432
sudo service postgresql restart

Print the result of a cursor in Oracle PL/SQL

While developing Oracle PL/SQL code, sometimes one needs to debug via the old way, using DBMS_OUTPUT.PUT_LINE or inserting data into a table for a later inspection.
But when working with cursors, this is not an easy task, specially if one does not know the real output of the cursor when using dynamic SQL or simply because the cost of writing a specific output for each cursor one needs to evaluate is too high.

In order to aid this task, here’s a procedure that will print out the result of any cursor. By default, it will print the first 10 rows, but to print the entire result, just call the PRINT_CURSOR procedure with the v_maxRows = 0.

create or replace
PROCEDURE PRINT_CURSOR (
p_refcursor IN OUT SYS_REFCURSOR, v_maxRows IN NUMBER default 10 )
AS
v_desc DBMS_SQL.DESC_TAB;
v_cols BINARY_INTEGER;
v_cursor BINARY_INTEGER;
v_varchar2 VARCHAR2(4000);
v_number NUMBER;
v_date DATE;
v_data VARCHAR2(32767);
v_curRow NUMBER;
BEGIN
/* Convert refcursor "parameter" to DBMS_SQL cursor... */
v_cursor := DBMS_SQL.TO_CURSOR_NUMBER(p_refcursor);
/* Describe the cursor... */
DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_cols, v_desc);
/* Define columns to be fetched. We're only using V2, NUM, DATE for example... */
FOR i IN 1 .. v_cols
LOOP
IF v_desc(i).col_type = 2 THEN
DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_number);
ELSIF v_desc(i).col_type = 12 THEN
DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_date);
ELSE
DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_varchar2, 4000);
END IF;
END LOOP;
/* Now output the data, starting with header... */
DBMS_OUTPUT.NEW_LINE;
FOR i IN 1 .. v_cols
LOOP
v_data := v_data ||
CASE v_desc(i).col_type
WHEN 2 THEN
LPAD(v_desc(i).col_name, v_desc(i).col_max_len+1)
WHEN 12 THEN
RPAD(v_desc(i).col_name, 22)
ELSE
RPAD(v_desc(i).col_name, v_desc(i).col_max_len+1)
END || ' ';
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_data);
v_data := NULL;
FOR i IN 1 .. v_cols
LOOP
v_data := v_data ||
CASE v_desc(i).col_type
WHEN 2 THEN
LPAD('-', v_desc(i).col_max_len+1, '-')
WHEN 12 THEN
RPAD('-', 22, '-')
ELSE
RPAD('-', v_desc(i).col_max_len+1, '-')
END || ' ';
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_data);
/* Fetch all data... */
v_curRow := v_maxRows;
WHILE DBMS_SQL.FETCH_ROWS(v_cursor) > 0
LOOP
v_data := NULL;
FOR i IN 1 .. v_cols
LOOP
IF v_desc(i).col_type = 2 THEN
DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_number);
v_data := v_data || LPAD(v_number, v_desc(i).col_max_len+1) || ' ';
ELSIF v_desc(i).col_type = 12 THEN
DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_date);
v_data := v_data || RPAD(v_date, 22) || ' ';
ELSE
DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_varchar2);
v_data := v_data || RPAD(v_varchar2, v_desc(i).col_max_len+1) || ' ';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_data);
if v_maxRows <> 0 then
v_curRow := v_curRow - 1;
exit when 0 = v_curRow;
end if;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;

Enable and Disable constraints and triggers in Oracle

During data migrations it’s common to have constraints disabled, usually for loading performance or testing purposes.
To aid in such task, we’re sharing two simple scripts, one for disabling and another for enabling.

To disable all enabled constraints and triggers, just execute:

BEGIN
-- Constraints
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = 'ENABLED'
ORDER BY c.constraint_type DESC)
LOOP
-- DISABLE
execute immediate 'alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name;
END LOOP;

-- Triggers
FOR ut IN
(select ut.table_owner, ut.trigger_name
from user_triggers ut
where ut.status = 'ENABLED')
LOOP
-- DISABLE
execute immediate 'ALTER TRIGGER "' || ut.table_owner || '"."' || ut.trigger_name || '" DISABLE;';
END LOOP;
END;
/

To enable all disabled constraints and triggers, just execute:

BEGIN
-- Constraints
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = 'DISABLE'
ORDER BY c.constraint_type DESC)
LOOP
-- ENABLE
execute immediate 'alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name;
END LOOP;

-- Triggers
FOR ut IN
(select ut.table_owner, ut.trigger_name
from user_triggers ut
where ut.status = 'DISABLE')
LOOP
-- ENABLE
execute immediate 'ALTER TRIGGER "' || ut.table_owner || '"."' || ut.trigger_name || '" ENABLE;';
END LOOP;
END;
/

For XML Path Equivalent in Oracle For String Aggregation

During a migration from SQL Server to Oracle 11g, I’ve came across with a specific functionality of T-SQL FOR XML PATH.
In the scope of the migration, the result of the select clause where this was applied was the concatenation of the values of a column per key.

Here’s an example of what I’ve found on T-SQL during the migration:

Declare @tbl1 table (
id int,
Col1 varchar(50),
Col2 varchar(50)
)

INSERT INTO @tbl1
SELECT 1,'A001','Y'

INSERT INTO @tbl1
SELECT 2,'A002','N'

INSERT INTO @tbl1
SELECT 3,'A003','N'

INSERT INTO @tbl1
SELECT 4,'A004','Y'

INSERT INTO @tbl1
SELECT 5,'A005','N'

INSERT INTO @tbl1
SELECT 6,'A006','N'

INSERT INTO @tbl1
SELECT 7,'A007','N'

INSERT INTO @tbl1
SELECT 8,'A008','Y'

-- Show current values
select * from @tbl1

-- Get all values when col2 has value 'N'
SELECT c.col1 AS [text()]
FROM @tbl1 c
WHERE c.col2 = 'N'
FOR XML PATH(''),type

-- Aggregate all values from col1 using col2 as key
select c1.col2 [Yes/No],
(SELECT c.col1 AS [text()]
FROM @tbl1 c
WHERE c.col2 = c1.col2
FOR XML PATH(''),type) as Value
FROM @tbl1 c1
group by c1.col2
order by c1.col2

The execution of this T-SQL script shows how it is possible transform the result of a query into a new result where the values are aggregated by col1, and all col2 values are concatenated.

In order to achieve this kind of aggregate transformation in Oracle, one has to make use of an associative array, as Tom explains in the “String Concatenation” how one can do this. Using the examples in the AskTom web site, first we create the “STRING_AGG_TYPE” type:

CREATE OR REPLACE TYPE string_agg_type AS object
(
total VARCHAR2(4000),

static FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type) RETURN NUMBER,

member FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type, VALUE IN VARCHAR2) RETURN NUMBER,

member FUNCTION ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,

member FUNCTION ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type) RETURN NUMBER
);
/

And the correspondent body:

CREATE OR REPLACE TYPE BODY string_agg_type
IS

static FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type)
RETURN NUMBER
IS
BEGIN
sctx := string_agg_type( NULL );
RETURN ODCIConst.Success;
END;

member FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type, VALUE IN VARCHAR2 )
RETURN NUMBER
IS
BEGIN
-- NOTE: Insert a separator in the empty string if you wish
self.total := self.total || '' || VALUE;
RETURN ODCIConst.Success;
END;

member FUNCTION ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER)
RETURN NUMBER
IS
BEGIN
-- NOTE: ltrim(self.total, '') if you've specified a separator in ODCIAggregateIterate
returnValue := LTRIM(self.total);
RETURN ODCIConst.Success;
END;

member FUNCTION ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
RETURN NUMBER
IS
BEGIN
self.total := self.total || ctx2.total;
RETURN ODCIConst.Success;
END;

END;
/

Then we create the aggregated function “stragg” of the type we’ve just created:

CREATE OR REPLACE FUNCTION stragg(input VARCHAR2 ) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/

We are now able to make a similar aggregation in Oracle:

CREATE TABLE tbl1 (
id NUMBER(10),
Col1 VARCHAR(50),
Col2 VARCHAR(50)
);
/

INSERT INTO tbl1 (id, col1, col2)
VALUES (1,'A001','Y');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (2,'A002','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (3,'A003','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (4,'A004','Y');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (5,'A005','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (6,'A006','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (7,'A007','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (8,'A008','Y');
/
COMMIT;
/

-- Show current values
SELECT * FROM tbl1;
/

-- Get all values when col2 has value 'N'
SELECT c.col2, STRAGG(c.col1)
FROM tbl1 c
WHERE c.col2 = 'N'
GROUP BY c.col2;
/

-- Aggregate all values from col1 using col2 as key
SELECT c1.col2 "Yes/No",
(SELECT STRAGG(c.col1)
FROM tbl1 c
WHERE c.col2 = c1.col2
) AS val
FROM tbl1 c1
GROUP BY c1.col2
ORDER BY c1.col2;
/

-- drop table tbl1;

And that’s it. This will work in 9i and beyond, for 8i, check the 8i workaround in the same AskTom thread.

DB2 copy schema with tables

Sometimes one needs to automate a DB2 schema copy, clone or simple backup.
If the requirements are just to create a new schema and copy the tables and their content into the new schema, it seems to be a simple task.
But this simple task soon reveals to be a hard one, which can be achieved with a quite simple script.

The first step is to created a specific schema for this automation task, e. g. COPY_DATABASE_SCHEMA, so all the procedures will be created there.

The procedure that copies the table content is easy to code. Just declare a cursor that selects the source table data and load it into the target table.
Here is the procedure that performs the copy of the table content:

/**
* Copies, with replacement, the content from the source table
* into the target table.
* It will not check for success, i.e. no loading validation
* will be performed.
*
* @sourceTable: the full qualified source table name
* @targetTable: the full qualified target table name
*/
CREATE PROCEDURE COPY_DATABASE_SCHEMA.COPY_TABLE(
sourceTable VARCHAR(128),
targetTable VARCHAR(128))
LANGUAGE SQL
BEGIN
DECLARE v_version_number INTEGER DEFAULT 1;
DECLARE v_cursor_statement VARCHAR(32672);
DECLARE v_load_command VARCHAR(32672);
DECLARE v_sqlcode INTEGER DEFAULT -1;
DECLARE v_sqlmessage VARCHAR(2048) DEFAULT '';
DECLARE v_rows_read BIGINT DEFAULT -1 ;
DECLARE v_rows_skipped BIGINT DEFAULT -1;
DECLARE v_rows_loaded BIGINT DEFAULT -1;
DECLARE v_rows_rejected BIGINT DEFAULT -1;
DECLARE v_rows_deleted BIGINT DEFAULT -1;
DECLARE v_rows_committed BIGINT DEFAULT -1;
DECLARE v_rows_part_read BIGINT DEFAULT -1;
DECLARE v_rows_part_rejected BIGINT DEFAULT -1;
DECLARE v_rows_part_partitioned BIGINT DEFAULT -1;
DECLARE v_mpp_load_summary VARCHAR(32672) DEFAULT NULL;

SET v_cursor_statement =
'DECLARE C1 CURSOR FOR SELECT * from ' || sourceTable;
SET v_load_command =
'load from C1 of cursor insert into ' || targetTable;

CALL db2load(1, v_cursor_statement, v_load_command, v_sqlcode,
v_sqlmessage, v_rows_read, v_rows_skipped,
v_rows_loaded, v_rows_rejected, v_rows_deleted,
v_rows_committed, v_rows_part_read,
v_rows_part_rejected, v_rows_part_partitioned,
v_mpp_load_summary) ;
END#

Please note that the target table must already be created in order for this procedure to work, and the script terminator is # and not the usual ;.

This procedure is the hardest part of the script. Copying the contents of any table requires a dynamic prepared query statement to use as a cursor, and the load command is not available inside a procedure, so the db2load procedure from SYSPROC must be used.

The next procedure creates the schema, the tables, and copies the tables contents:

/**
* Copies an entire schema into a new schema named with the current date.
* @sourceSchema: the source schema name
* @targetSchema: the target schema name
* @tableNameSelection: the tables name to include ('%' for all tables)
*/
CREATE PROCEDURE COPY_DATABASE_SCHEMA.COPY_DATABASE(
sourceSchema VARCHAR(50),
targetSchema VARCHAR(50),
tableNameSelection VARCHAR(150)
)
LANGUAGE SQL
BEGIN
-- Variables
DECLARE stmtSchema VARCHAR(250);
DECLARE stmtTableStructure VARCHAR(200);
DECLARE stmtTableContents VARCHAR(250);
DECLARE stmtAlias VARCHAR(250);
DECLARE tableName VARCHAR(128);
DECLARE numPages BIGINT;
DECLARE nError INTEGER DEFAULT 0;
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE V_SQL VARCHAR(200);
DECLARE V_STMT STATEMENT;
DECLARE V_LOAD_STMT STATEMENT;
DECLARE TGT_TABLE_CUR CURSOR WITH HOLD WITH RETURN FOR V_STMT;
DECLARE LOAD_CUR CURSOR FOR V_LOAD_STMT;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;

-- Create schema
SET stmtSchema = CHAR('CREATE SCHEMA ' concat CHAR(targetSchema));
EXECUTE IMMEDIATE stmtSchema;
SET CURRENT SCHEMA targetSchema;

-- Copy tables and views
SET V_SQL = 'SELECT name, npages FROM SYSIBM.SYSTABLES
WHERE CREATOR = ''' || TRIM(sourceSchema) || '''
AND NAME LIKE ''' || TRIM(tableNameSelection) || '''
order by name';
PREPARE V_STMT FROM V_SQL;
OPEN TGT_TABLE_CUR;

fetch_loop:
LOOP
FETCH TGT_TABLE_CUR INTO tableName, numPages;
IF at_end <> 0 THEN
LEAVE fetch_loop;
ELSE
SET stmtTableStructure = 'CREATE TABLE ' ||
targetSchema || '.' || tableName || ' LIKE ' ||
sourceSchema || '.' || tableName ;
EXECUTE IMMEDIATE stmtTableStructure;

IF numPages > 0 THEN
CALL COPY_DATABASE_SCHEMA.copy_table(
sourceSchema || '.' || tableName,
targetSchema || '.' || tableName);
END IF;
END IF;
END LOOP fetch_loop;

CLOSE TGT_TABLE_CUR;
END#

It dynamically issues DB2 SQL commands to create the schema and the tables structure and makes use of the previously created COPY_TABLE to copy the tables content. See the code comments for more information.

To use this just

CALL COPY_DATABASE_SCHEMA.COPY_DATABASE('sourceSchemaName','targetSchemaName','%');

These procedures have some limitations, for instance views are not supported.
Nevertheless, it is a good start to all that need to clone a schema or a table, since all the missing items can be gathered from the database catalog.

If you to drop an old schema, for instance to recreate it with a a new updated version, you can drop drop the schema with this procedure.

DB2 drop schema and tables

Dropping a non-empty schema in DB2 is not as easy as it seems. The schema must be empty in order to be dropped, i. e. it cannot have any objects in it.

The following script procedure will drop a schema after all tables have been dropped:

/**
* Drops an entire schema even if it has tables.
* Views are not supported.
* @schemaName: the name of the schema to drop
*/
CREATE PROCEDURE COPY_DATABASE_SCHEMA.DROP_SCHEMA(
schemaName VARCHAR(50)
)
LANGUAGE SQL
BEGIN
-- Variables
DECLARE stmtDropSchema VARCHAR(250);
DECLARE stmtDropTable VARCHAR(250);
DECLARE tableName VARCHAR(128);
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE V_SQL VARCHAR(200);
DECLARE V_STMT STATEMENT;
DECLARE TGT_TABLE_CUR CURSOR WITH RETURN FOR V_STMT;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;

-- Copy tables and views
SET V_SQL = 'SELECT name FROM SYSIBM.SYSTABLES
WHERE CREATOR = ''' || TRIM(schemaName) || '''
AND CREATOR NOT LIKE ''SYS%''';
PREPARE V_STMT FROM V_SQL;
OPEN TGT_TABLE_CUR;

fetch_loop:
LOOP
FETCH TGT_TABLE_CUR INTO tableName;
IF at_end <> 0 THEN LEAVE fetch_loop;
ELSE
SET stmtDropTable = 'DROP TABLE ' ||
schemaName || '.' || tableName;
EXECUTE IMMEDIATE stmtDropTable;
END IF;
END LOOP fetch_loop;

CLOSE TGT_TABLE_CUR;

-- Drop schema
SET stmtDropSchema = CHAR('DROP SCHEMA ' concat
CHAR(schemaName) concat ' RESTRICT');
EXECUTE IMMEDIATE stmtDropSchema;
END#

Note that the procedure ends with a # instead of the regular ; terminator.
Also note that the procedure is created under the COPY_DATABASE_SCHEMA schema, which most likely may not exist in your database.

To use this just

CALL COPY_DATABASE_SCHEMA.DROP_SCHEMA('deleteSchemaName');