db2 duplicate a database

I am assuming you already have a Database, and that you do not wish to try and generate the DDL and re-execute it.

New Db

ORIGDB="EIA_OW"
NEWDB="EIA_OW_2"
db2 create  database $NEWDB

BASH Env

DB2 needs a global Symbols to be set, else it will not remember the DB Connection

export DB2DBDFT=$ORIGDB

List Schema names

select schemaname from syscat.schemata

I see something like

SCHEMANAME                                                                                                                      
--------------------------------------------------------------------------------------------------------------------------------
BCUAIX                                                                                                                          
CGT                                                                                                                             
DB2GSE                                                                                                                          
DB2INST1                                                                                                                        
DSADM                                                                                                                           
NULLID                                                                                                                          
NULLIDR1                                                                                                                        
NULLIDRA                                                                                                                        
OPM                                                                                                                             
OW_ENT                                                                                                                          
OW_LNK                                                                                                                          
OW_PETE                                                                                                                         
OW_REF                                                                                                                          
OW_RPT                                                                                                                          
OW_ST0                                                                                                                          
OW_ST1                                                                                                                          
OW_ST2_ENT                                                                                                                      
OW_ST2_LNK                                                                                                                      
OW_VQ                                                                                                                           
SQLJ                                                                                                                            
ST_INFORMTN_SCHEMA                                                                                                              
SYSCAT                                                                                                                          
SYSFUN                                                                                                                          
SYSIBM                                                                                                                          
SYSIBMADM                                                                                                                       
SYSIBMINTERNAL                                                                                                                  
SYSIBMTS                                                                                                                        
SYSPROC                                                                                                                         
SYSPUBLIC                                                                                                                       
SYSSTAT                                                                                                                         
SYSTOOLS                                                                                                                        
  31 record(s) selected.

Either by SQL or grep - I now reduce this to the OW_* schema names.

OW_ENT                                                                                                                          
OW_LNK                                                                                                                          
OW_PETE                                                                                                                         
OW_REF                                                                                                                          
OW_RPT                                                                                                                          
OW_ST0                                                                                                                          
OW_ST1                                                                                                                          
OW_ST2_ENT                                                                                                                      
OW_ST2_LNK                                                                                                                      
OW_VQ        

In bash this looks like

for s in $(db2 select schemaname from syscat.schemata | grep ^OW_)
do
   bla bla $a
done

Complete Duplicate script

I am not 100% sure if this handles Constraints, especially cross schema constraints.

But for tables/indexes etc. This works well

#!/bin/bash

ORIGDB="EIA_OW"
NEWDB="EIA_OW_2"
USER=db2inst1
PASS=passw0rd
export DB2DBDFT=$ORIGDB

#
# Only uncomment this 1 time
#
#db2 create  database $NEWDB


db2 connect to $ORIGDB user $USER using $PASS
for s in $(db2 select schemaname from syscat.schemata | grep ^OW_)
do
 echo "db2move $ORIGDB COPY -sn $s -co TARGET_DB $NEWDB USER $USER USING $PASS"
done

This will output the commands that need to be executed i.e.

db2move EIA_OW COPY -sn OW_ENT -co TARGET_DB EIA_OW_2 USER db2inst1 USING passw0rd
db2move EIA_OW COPY -sn OW_LNK -co TARGET_DB EIA_OW_2 USER db2inst1 USING passw0rd
db2move EIA_OW COPY -sn OW_PETE -co TARGET_DB EIA_OW_2 USER db2inst1 USING passw0rd
db2move EIA_OW COPY -sn OW_REF -co TARGET_DB EIA_OW_2 USER db2inst1 USING passw0rd
db2move EIA_OW COPY -sn OW_RPT -co TARGET_DB EIA_OW_2 USER db2inst1 USING passw0rd
db2move EIA_OW COPY -sn OW_ST0 -co TARGET_DB EIA_OW_2 USER db2inst1 USING passw0rd
db2move EIA_OW COPY -sn OW_ST1 -co TARGET_DB EIA_OW_2 USER db2inst1 USING passw0rd
db2move EIA_OW COPY -sn OW_ST2_ENT -co TARGET_DB EIA_OW_2 USER db2inst1 USING passw0rd
db2move EIA_OW COPY -sn OW_ST2_LNK -co TARGET_DB EIA_OW_2 USER db2inst1 USING passw0rd
db2move EIA_OW COPY -sn OW_VQ -co TARGET_DB EIA_OW_2 USER db2inst1 USING passw0rd