How to upload a copy of the Database Schema to the IBM Support System

This procedure comprises two steps:

1. Create a logical copy of your database schema.
2. Upload the copy to the IBM Support FTP Server.

1. Create a logical copy of your database schema (Oracle/DB2)

Creating a copy of your DB schema on Oracle

Please use Oracle data pump for exporting database dump files. More information could be found here
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#i1007466

It is recommended to run these steps from your DB server and stop the MDM application.

1) Create directory object in Oracle where you want to download the dump.
Login to SQLPlus or any SQL interface using the DB Admin user and execute the following statements:

SQL> CREATE DIRECTORY DUMP_FILES1 AS '/u02/dbdumps';
SQL> GRANT READ, WRITE ON DIRECTORY dump_files1 to public;

a. You can have any other name instead of dump_files1, this name should be used for expdp command later.
b. You may already have directory objects available, you can verify those by using queries below. Make sure you have sufficient space and permission to use the directory object.
SELECT * FROM dba_directories;
SELECT * FROM dba_objects WHERE object_type = 'DIRECTORY';

2) Run export command
Run the command expdp as follows:

expdp <db admin>/<admin pwd> DIRECTORY=dump_files1 DUMPFILE=<pmr_no_seperated_by_dots>.expdp%U.dmp FILESIZE=1g PARALLEL=20 SCHEMAS=<mdm schema> STATUS=300 LOGFILE=<pmr_no_seperated_by_dots>.export.log

Substitute the variables <db admin>, <admin pwd>, <pmr_no_seperated_by_dots> and <mdm schema> appropriately. The filenames should contain a substitution variable (%U), which implies that multiple files may be generated.

For example:
expdp system/manager DIRECTORY=dump_files1 DUMPFILE=12345.999.000.expdp%U.dmp FILESIZE=1g PARALLEL=30 SCHEMAS=mdmuser STATUS=300 LOGFILE=12345.999.000.export.log

expdp can also invoked by using a parameter file. Include all the parameters for expdp in a file, say complete_export.par, and then you can invoke the expdp command as below:

expdp system/manager parfile=complete_export.par

# ---- Contents of complete_export.par file ----
DIRECTORY=dump_files1
DUMPFILE=12345.999.000.expdp%U.dmp
FILESIZE=1g
PARALLEL=30
SCHEMAS=mdmuser
STATUS=300
LOGFILE=12345.999.000.export.log

To minimize the size of the dump files, you might use following expdp parameter file to avoid provision of unneeded data. Please check with support whether current version rows are sufficient. Before using the parameter file, you need to modify the parameters; please replace the DIRECTORY and SCHEMAUSER settings and adopt the parameter PARALLEL to your environment.

expdp <db admin>/<admin pwd> parfile=exp_curr_version.par

exp_curr_version.par


Create a copy of your DB schema on DB2

Using db2_export.sh (attached to this Technote) to export the MDM schema.
It also generates required SQL scripts to create tables, indexes and sequences with current values and exports everything and stores it as a tar file in the backup directory provided.

Syntax:
db2_export.sh --db=<databasename> --dbuser=<exportuser> --dbpassword=<exportuserpassword> --backupdir=<bkpdirectory> --bkpfile=<bkpfilename>

databasename: Name of the database from where you want to export the schema
exportuser: Name of the MDM database schema user to export. It is the parameter 'username' of the [db] section in the env_settings.ini file.
dbuserpassword: Password of the MDM database schema user that is getting exported.
bkpdirectory: Name of the directory including PATH where you want to export the database schema. This needs to be a full qualified path. (Where you want to create the dump tar file)
bkpfilename: Name of the backup tar file

For example:
db2_export.sh --db=mdmdb --dbuser=mdm1 --dbpassword=passwd --backupdir=/u01/backup --bkpfile=july10bkp

Notes:

1. Make sure that the db2_export.sh script is saved in UNIX format if it is copied from your Windows laptop or desktop to the db server. Otherwise, the system can introduce an end of line characters that make this script fail.
2. Only a DB2 DBA should run these scripts. If you want to take a logical backup of your DB schema, please get assistance from a DB2 DBA.

Pre-requisites:

1. Shutdown the MDM application connected to the DB schema that is getting exported before running db2_export.sh.
2. Make sure that the backup directory exists and the path is correct. Make sure that the owner of the backup directory is the db2 instance owner on the DB server (for example db2inst1).
3. Never use a directory which has db2 data files (SMS and DMS files) as the backup directory.
4. Make sure that the backup directory is created on local disks on the DB server (not NFS)
5. Copy db2_export.sh to the db server as db2instance owner; Login to the DB server as the DB instance owner (for example db2inst1) to run the db2_export.sh. Do not run this from application server.

db2_export.sh

Note: The script is using gtar if it is available, otherwise tar. If the files that will be compressed are bigger than 8 GB, you could encounter problems if tar is used.

2. Uploading the export files to the IBM FTP Server

After you have exported your DB schema, you will need to upload it to a location where the MDM Support team is able to access it.

If you have a PMR open with MDM Support, just follow the instructions below. If you do not have a PMR open yet, please contact MDM Support first.

Steps to upload the DB copy:

1. Tar / GZip the exported file(s), e.g. "gtar -czf mdm_dump.tgz mdm1.dmp mdm2.dmp mdm3.dmp".
   Note: It is mandatory to create a Tar / GZip.
2. Rename the zipped file to contain the PMR number for which this DB copy has been requested. E.g. if your PMR number is "12345,999,000", rename the file to "12345.999.000.mdm_dump.tgz".
3. In case the zipped file is bigger than 1 GB, we recommend that you split the file to avoid problems with the FTP upload/download. Use the 'split' command on UNIX, so that you can upload smaller chunks. To create 1 GB chunks you can use the command:
   
   split -b 1024m 12345.999.000.mdm_dump.tgz 12345.999.000.mdm_dump
   
   The chunks will be named:
   
   12345.999.000.mdm_dumpaa, 12345.999.000.mdm_dumpab, ...
   
   Then you can upload the 1 GB chunks to our FTP server.
4. Connect to the ECuRep FTP server using your favorite FTP client.
5. Login using anonymous as username and your email address as password.
6. Switch file transfer mode to "binary", e.g. by typing "bin" in the Unix command line FTP client.
7. Change directory to "/toibm/linux".
8. Upload the DB copy, e.g. using "put 12345.999.000.mdm_dump.tgz".

For further reference on how to upload files to the ECuRep system, please refer to this link:
http://www.ibm.com/support/pages/node/739283