Questions about DB2

[Firehawk®]

Hi,
I have a great experience with DB (Oracle and others) and I am using DB2
now.
Then, I have few questions:

1. How drop a schema (user) and all objects of this schema, like a Oracle
"drop user cascade" ?

2. Suposing, 2 Databases, DB1 and DB2, each one with a schema, schema1 in
DB1 and schema2 in DB2.
How to import data( tables) from schema1 in DB1 to schema2 in DB2 ?
I made tests with db2move, but I need import to SAME schema, is from schema1
to schema1 or from schema2 to schema2.
Using Oracle, imp fromuser=schema 1 touser=schema2

3. How to copy table datas from schema1 to schema2 in same DB ? Using insert
select statement ? And from schemas in differents DBs ?

Thanks in Advance,
Marcelo

[Serge Rielau]

Firehawk® wrote:

Hi,
I have a great experience with DB (Oracle and others) and I am using DB2
now.
Then, I have few questions:

1. How drop a schema (user) and all objects of this schema, like a Oracle
"drop user cascade" ? Careful. At least in DB2 a USER is not equal to a SCHEMA.
A SCHEMA is nothing more than a qualifier to group objects.
By default your default qualifier happens to be the same as your user login.
Do drop a schema you would drop all the objects in the schema first and
then use the DROP SCHEMA statement once it is empty.
You can find all objects in a schema by querying the SYSCAT views.
2. Suposing, 2 Databases, DB1 and DB2, each one with a schema, schema1 in
DB1 and schema2 in DB2.
How to import data( tables) from schema1 in DB1 to schema2 in DB2 ?
I made tests with db2move, but I need import to SAME schema, is from schema1
to schema1 or from schema2 to schema2.
Using Oracle, imp fromuser=schema 1 touser=schema2 Do you mean _a_ table or _all_ tables from the source schema?
Either way. The way I would do this is to create NICKNAMEs for each
table I cared about and then do INSERT INTO schema2.T1 SELECT * FROM
schema2_nick.T1 ;
3. How to copy table datas from schema1 to schema2 in same DB ? Using insert
select statement ? And from schemas in differents DBs ?

Sure, insert with select would work. If the target table is empty I
would use the ACTIVATE NOT LOGGED INITIALLY clause in ALTER TABLE to
speed things up. You can also open a cursor on the source table and load
into the target from the cursor.
The same works across DBs, again using NICKNAME.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Ian]

Firehawk® wrote:

Hi,
I have a great experience with DB (Oracle and others) and I am using DB2
now.
Then, I have few questions:

1. How drop a schema (user) and all objects of this schema, like a Oracle
"drop user cascade" ?
Unfortunately, there is no equivalent to this in DB2.
2. Suposing, 2 Databases, DB1 and DB2, each one with a schema, schema1 in
DB1 and schema2 in DB2.
How to import data( tables) from schema1 in DB1 to schema2 in DB2 ?
I made tests with db2move, but I need import to SAME schema, is from schema1
to schema1 or from schema2 to schema2.
Using Oracle, imp fromuser=schema 1 touser=schema2
db2move doesn't support this directly. However (this is a hack), if you
edit the file 'db2move.lst' that gets created by the 'db2move export'
operation and change the schema it should work to create the tables in
the correct schema.
3. How to copy table datas from schema1 to schema2 in same DB ? Using insert
select statement ? And from schemas in differents DBs ?

Either insert/select or export and import/load.

Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Dave Hughes]

Hi,

1. How drop a schema (user) and all objects of this schema, like a
Oracle "drop user cascade" ?
Unfortunately (or fortunately, depending on your level of paranoia :-)
DB2 does not have this capability. The syntax for DROP SCHEMA is

DROP SCHEMA <name> RESTRICT;

The "RESTRICT" clause is mandatory and explicitly indicates that the
schema must be empty before it is capable of being dropped.
2. Suposing, 2 Databases, DB1 and DB2, each one with a schema,
schema1 in DB1 and schema2 in DB2.
How to import data( tables) from schema1 in DB1 to schema2 in DB2 ?
I made tests with db2move, but I need import to SAME schema, is from
schema1 to schema1 or from schema2 to schema2.
Using Oracle, imp fromuser=schema 1 touser=schema2
I'm not 100% sure I understand the question. You say you need to import
to the "SAME" schema, yet the Oracle command you're using specifies
different schemas (maybe I'm misunderstandin g what the command does)?

Anyway, if you need to copy tables from SCHEMA1 in DB1 to SCHEMA1 in
DB2, the db2move utility should be just what you're looking for. Just
use:

db2move DB1 EXPORT -tc SCHEMA1 -u DB1USER -p DB1PASS
db2move DB2 IMPORT -u DB2USER -p DB2PASS

Where DBnUSER and DBnPASS are the usernames/passwords for users with
access to the relevant DB.

However, if you need to copy tables from SCHEMA1 in DB1 to SCHEMA2 in
DB2 (or SCHEMA2 in DB1 for that matter), it's not as simple...
Unfortunately, AFAIK, there is no easy solution to this. However, if
you're on a Linux/UNIX environment (or a Windows environment with
Cygwin) you can use the scripting tools available to make things a
little easier:

* Use db2move DB1 EXPORT -tc SCHEMA1 to export all the tables from
SCHEMA1 to PC/IXF files. db2move will also create some other files,
including one called "db2move.ls t" which contains a bang (!) separated
list of fields:

!"SCHEMA"!"TABL E"!data_file.ix f!export_log.ms g!

* Write a script which will convert the db2move.lst file into a series
of IMPORT commands to re-create the tables in the new schema.
Personally, I'd probably use awk for this (others might prefer sed,
perl, python, or something else). The output should be of the form:

IMPORT FROM $3 OF IXF REPLACE_CREATE INTO NEWSCHEMA.$2;

Where $2 and $3 are fields from the db2move.lst file. All in all it
shouldn't take more than a few lines of script to do this. If you need
an example, I can probably knock one together and post it here.

Unfortunately, this will only transfer *tables* from one schema to
another (db2move doesn't handle anything else), though as the manual
page specifies, the db2look tool can be used to export DDL to re-create
other objects. I suspect modifying db2look's output to replace the
schema would be more difficult - though not impossible.

Does anyone else know of a better way to go about doing this?
3. How to copy table datas from schema1 to schema2 in same DB ? Using
insert select statement ? And from schemas in differents DBs ?

See the previous answer.
HTH, Dave.

--
Dave
Remove "_nospam" for valid e-mail address

"Never underestimate the bandwidth of a station wagon full of CDs doing
a ton down the highway" -- Anon.

[Dave Hughes]

On 2004-04-08, Serge Rielau scribbled:

[Snip]

2. Suposing, 2 Databases, DB1 and DB2, each one with a schema,
schema1 in DB1 and schema2 in DB2.
How to import data( tables) from schema1 in DB1 to schema2 in DB2 ?
I made tests with db2move, but I need import to SAME schema, is
from schema1 to schema1 or from schema2 to schema2.
Using Oracle, imp fromuser=schema 1 touser=schema2

Do you mean a table or all tables from the source schema?
Either way. The way I would do this is to create NICKNAMEs for each
table I cared about and then do INSERT INTO schema2.T1 SELECT * FROM
schema2_nick.T1 ;

Now that's a neat trick! I'll definitely remember that one for future
reference. Thanks, Serge.

3. How to copy table datas from schema1 to schema2 in same DB ?
Using insert select statement ? And from schemas in differents DBs
?

Sure, insert with select would work. If the target table is empty I
would use the ACTIVATE NOT LOGGED INITIALLY clause in ALTER TABLE to
speed things up. You can also open a cursor on the source table and
load into the target from the cursor. The same works across DBs,
again using NICKNAME.

Another little trick for better performance in copying/moving tables
when using the db2move utility is to do

db2move DB1 EXPORT
db2move DB2 IMPORT -io CREATE
db2move DB2 LOAD

The EXPORT creates the PC/IXF files for all the tables to be moved. The
IMPORT with -io CREATE just re-creates the _structure_ of the tables in
the destination database, after which LOAD is used to rapidly load the
data into the tables (LOAD is _much_ faster than IMPORT which can be
important when working with millions of rows of data).

HTH, Dave.

--
Dave
Remove "_nospam" for valid e-mail address

"Never underestimate the bandwidth of a station wagon full of CDs doing
a ton down the highway" -- Anon.

[Ken]

"Dave Hughes" <da*********@wa veform.plus.com > wrote in message news:<xn******* *********@usene t.plus.net>...

Unfortunately, this will only transfer *tables* from one schema to
another (db2move doesn't handle anything else), though as the manual
page specifies, the db2look tool can be used to export DDL to re-create
other objects. I suspect modifying db2look's output to replace the
schema would be more difficult - though not impossible.

Does anyone else know of a better way to go about doing this?

Well, db2move is a nice little tool, but it isn't difficult to write
your own version. This gives you the ability to include additional
functionality, like:
- runstats
- deletions
- extract selection criteria
- change schema/table names
- force connections
- lock tables
- audit trail
- notification
- etc

I've written a few of these for moving data between a warehouse and
its data marts. A stripped-down version of one is included below.
Here's how it would typically work:
- extract version (not provided but very similar) is scheduled to
run at a regular time
- transport process (not provided) automatically pushes extract file
to mart
- mart load (included below) is scheduled to run every minute -
looking continuously for a file to load

The following version is intended to show how this would work, rather
than be a complete solution - though 90% of the code is there. I
basically took a functional process, then removed just a little
application-specific & manageability code. It's written in korn shell
- so should run fine on unix. Should also run fine in bash for linux.
Would work with minimal changes in cygwin on windows. Same concept
works just fine in perl/python/tcl/rexx/etc.
#!/usr/bin/ksh

#----------------------------------------------------------------------------
# Load config values:
#----------------------------------------------------------------------------
data_dir=$HOME/data/mart3
temp_dir=$HOME/data/mart3/temp
archive_dir=$HO ME/archive/mart3
log_dir=$HOME/logs/mart3
script_name=mar t3_load.ksh
.. $HOME/.profile

#----------------------------------------------------------------------------
# Note:
# 1. assumes that $log_dir is defined
#----------------------------------------------------------------------------
function load_table
{
load_data_dir=$ 1
file_name=$2
table_name=$3
operation=$4
file_type=$5

echo " loading table: $table_name"
echo " loading table: $table_name" >> ${log_dir}/details.log

db2 " LOAD FROM ${load_data_dir }/${file_name} \
OF ${file_type} \
MESSAGES ${log_dir}/mart3_messages. dat \
WARNINGCOUNT 1 \
$operation INTO ${table_name} \
ALLOW READ ACCESS "\

${log_dir}/details.log

rc=$?
if [ $rc -gt 2 ]; then
print " load error in ${table_name}: rc=$rc"
exit 1
fi
}

#----------------------------------------------------------------------------
# IDENTIFY NEXT LOAD FILE
#----------------------------------------------------------------------------

source_file=`ls ${data_dir}/mart3_ld-????????.dat.ta r.gz | head -n 1`
export logical_date=`e cho $source_file | cut -c44-51`
#----------------------------------------------------------------------------
# BEGIN PRINTING AUDIT TRAIL
#----------------------------------------------------------------------------

echo "============== =============== ======" > ${log_dir}/details.log
echo "= mart3_load starting =" >> ${log_dir}/details.log
echo "= logical date: $logical_date =" >> ${log_dir}/details.log
echo "= source file: $source_file =" >> ${log_dir}/details.log
echo "============== =============== ======" >> ${log_dir}/details.log
echo " " >> ${log_dir}/details.log
#----------------------------------------------------------------------------
# UNCOMPRESS FILE
#----------------------------------------------------------------------------

cp ${data_dir}/mart3_ld-${logical_date) .dat.tar.gz ${temp_dir}

cd ${temp_dir}
`gzip -d ${temp_dir}/mart3_ld-${logical_date) .dat.tar.gz `
rc=$?
if [ $rc -ne 0 ]; then
print " gzip failed with rc=$rc"
exit 1
fi
#----------------------------------------------------------------------------
# UN-TAR FILE
#----------------------------------------------------------------------------

`tar -xvf ${temp_dir}/mart3_ld-${logical_date) .dat.tar`
rc=$?
if [ $rc -ne 127 ]; then
print " tar failed with rc=$rc"
exit 1
fi
#----------------------------------------------------------------------------
# CONNECT TO DATABASE
#----------------------------------------------------------------------------
db2 CONNECT TO mart3
if [ $? -ne 0 ]; then
print " error on database connect"
exit 1
fi
#----------------------------------------------------------------------------
# LOAD FILES
# args: dir, file, table, op, filetype
#----------------------------------------------------------------------------

load_table $temp_dir dim_location.ix f mart3.dim_locat ion replace ixf
load_table $temp_dir dim_state.ixf mart3.dim_state replace ixf
load_table $temp_dir dim_time.ixf mart3.dim_time replace ixf
load_table $temp_dir dim_supplier.ix f mart3.dim_suppl ier replace ixf
load_table $temp_dir dim_customer.ix f mart3.dim_custo mer replace ixf
load_table $temp_dir dim_product.ixf mart3.dim_produ ct replace ixf

load_table $temp_dir fac_labor.ixf mart3.fact_labo r insert ixf
load_table $temp_dir fac_material.ix f mart3.fact_mate rial insert ixf
load_table $temp_dir fac_project.ixf mart3.fact_proj ect insert ixf
load_table $temp_dir fac_revenue.ixf mart3.fact_reve nue insert ixf

#----------------------------------------------------------------------------
# TERMINATE
#----------------------------------------------------------------------------

db2 CONNECT RESET
if [ $? -ne 0 ]; then
print " error on database connect reset at end of process"
exit 1
fi

rm ${temp_dir}/*.dat
rm ${temp_dir}/*.tar
rm ${temp_dir}/*.gz
mv ${data_dir}/mart3_ld-${logical_date) .dat.tar.gz ${archive_dir}
echo "============== =============== ==" > ${log_dir}/details.log
echo "= mart3_load terminating =" >> ${log_dir}/details.log
echo "============== =============== ==" >> ${log_dir}/details.log
echo " " >> ${log_dir}/details.log

mail -s "mart3_load results" ma***@rpt.acme. com <
${log_dir}/details.log
echo "terminatin g normally"

exit 0