NOTES:
- Need LOAD privilege - easiest is to grant DBADM privilge
Step 1: Export from Source DB
D:\IBM\Backups\DB2>db2move DMDB export -aw -l lobs -sn sa
Application code page not determined, using ANSI codepage 1252
***** DB2MOVE *****
Action: EXPORT
Start time: Tue Jul 08 09:39:22 2014
All schema names matching: SA;
Connecting to database DMDB ... successful! Server : DB2 Common Server V10.1.4
Binding package automatically ... D:\IBM\SQLLIB\BND\DB2COMMON.BND ... successful
!
Binding package automatically ... D:\IBM\SQLLIB\BND\DB2MOVE.BND ... successful!
EXPORT: 267324 rows from table "SA "."ACCOUNT"
EXPORT: 2 rows from table "SA "."ACCOUNT_TYPE_CODE"
EXPORT: 267324 rows from table "SA "."ACCOUNT_WACHOVIA"
EXPORT: 520479 rows from table "SA "."ACCT_PURCH_SUM"
EXPORT: 4 rows from table "SA "."ACOSESSION"
EXPORT: 28 rows from table "SA "."ACOSESSIONSTATUS"
EXPORT: 6 rows from table "SA "."ACO_CONTACTCHANNEL"
EXPORT: 148306 rows from table "SA "."ACO_CONTACT_LOG"
EXPORT: 20000 rows from table "SA "."ACO_OPTOUTLIST"
EXPORT: 0 rows from table "SA "."ACO_PROPOSED_CONTACTS"
EXPORT: 13 rows from table "SA "."ACO_VERSION"
EXPORT: 50 rows from table "SA "."AC_PROFILE_FIELDS"
EXPORT: 50 rows from table "SA "."AGE_RANGE"
EXPORT: 0 rows from table "SA "."AQ_INBOUND"
EXPORT: 0 rows from table "SA "."AQ_OUTBOUND"
EXPORT: 5 rows from table "SA "."BEHAVIOR"
EXPORT: 59922 rows from table "SA "."BEHAVIORAL_STATE"
EXPORT: 5 rows from table "SA "."BEHAVIOR_BANKING"
EXPORT: 5 rows from table "SA "."BEHAVIOR_CREDITCARD"
EXPORT: 5 rows from table "SA "."BEHAVIOR_INSURANCE"
EXPORT: 7 rows from table "SA "."CATEGORY"
EXPORT: 7 rows from table "SA "."CATEGORY_FS"
EXPORT: 74153 rows from table "SA "."CONTACT_LOG"
EXPORT: 30 rows from table "SA "."CONTACT_PERIOD"
EXPORT: 199922 rows from table "SA "."CUSTOMER"
EXPORT: 247852 rows from table "SA "."CUSTOMER_ACCOUNT"
EXPORT: 199922 rows from table "SA "."CUSTOMER_CONTACT"
EXPORT: 399844 rows from table "SA "."CUSTOMER_MODEL_SCORE"
EXPORT: 501148 rows from table "SA "."CUSTOMER_MODEL_SCORE_DEMO"
EXPORT: 730 rows from table "SA "."CUSTOMER_PERIOD"
EXPORT: 358907 rows from table "SA "."CUSTOMER_SUPPRESSION"
EXPORT: 212 rows from table "SA "."DIMENSIONTABLE"
EXPORT: 503 rows from table "SA "."DIMENSIONTABLEOLD"
EXPORT: 48 rows from table "SA "."DIMENSION_WACHOVIA"
EXPORT: 4 rows from table "SA "."EDUCATION"
EXPORT: 4 rows from table "SA "."ETHNIC"
EXPORT: 2 rows from table "SA "."GENDER"
EXPORT: 195605 rows from table "SA "."HOUSEHOLD"
EXPORT: 0 rows from table "SA "."HOUSEHOLD_MODEL_SCORE"
EXPORT: 354219 rows from table "SA "."HOUSEHOLD_SUPPRESSION"
EXPORT: 438 rows from table "SA "."INCOME_RANGE"
EXPORT: 0 rows from table "SA "."INDIV"
EXPORT: 0 rows from table "SA "."INDIV_CONTACT"
EXPORT: 0 rows from table "SA "."INSURANCE_PRODUCT"
EXPORT: 397 rows from table "SA "."JCDIMENSIONTABLE"
EXPORT: 22000 rows from table "SA "."JCTMP"
EXPORT: 8 rows from table "SA "."LANGUAGE"
EXPORT: 7 rows from table "SA "."MODEL"
EXPORT: 98 rows from table "SA "."OCCUPATION"
EXPORT: 944 rows from table "SA "."OGANIZATION_BANKING"
EXPORT: 8 rows from table "SA "."ORGANIZATION_"
EXPORT: 944 rows from table "SA "."ORGANIZATION_CREDITCARD"
EXPORT: 944 rows from table "SA "."ORGANIZATION_INSURANCE"
EXPORT: 944 rows from table "SA "."ORGANIZATION_VIEW"
EXPORT: 343 rows from table "SA "."POT_VALUE_RANGE"
EXPORT: 116586 rows from table "SA "."PROD"
EXPORT: 325 rows from table "SA "."PRODS2"
EXPORT: 982 rows from table "SA "."PRODUCT"
EXPORT: 1018 rows from table "SA "."PRODUCT_BANKING"
EXPORT: 189 rows from table "SA "."PRODUCT_CLASS"
EXPORT: 189 rows from table "SA "."PRODUCT_CLASS_FS"
EXPORT: 982 rows from table "SA "."PRODUCT_VIEW"
EXPORT: 10000 rows from table "SA "."PROSPECT"
EXPORT: 5 rows from table "SA "."RELATIONSHIP"
EXPORT: 165277 rows from table "SA "."RELATIONSHIP_STATE"
EXPORT: 7 rows from table "SA "."RELIGION"
EXPORT: 160 rows from table "SA "."REPORTDIMENSIONTABLE"
EXPORT: 2764 rows from table "SA "."RESPONSE"
EXPORT: 290 rows from table "SA "."RESPONSE_PERIOD"
EXPORT: 0 rows from table "SA "."RESPONSE_TYPE"
EXPORT: 199922 rows from table "SA "."RETAILPROD"
EXPORT: 533207 rows from table "SA "."SALES"
EXPORT: 4 rows from table "SA "."SALES_CHANNEL"
EXPORT: 72 rows from table "SA "."SALES_GROUP"
EXPORT: 533207 rows from table "SA "."SALES_ITEM"
EXPORT: 533207 rows from table "SA "."SALES_ITEM_VIEW"
EXPORT: 718 rows from table "SA "."SALES_PERIOD"
EXPORT: 944 rows from table "SA "."SALES_RESOURCE"
EXPORT: 2 rows from table "SA "."SCORE_PERIOD"
EXPORT: 2 rows from table "SA "."SCORE_TYPE"
EXPORT: 3 rows from table "SA "."SEED"
EXPORT: 34 rows from table "SA "."SUB_CATEGORY"
EXPORT: 34 rows from table "SA "."SUB_CATEGORY_FS"
EXPORT: 7 rows from table "SA "."SUPPRESSION"
EXPORT: 7956 rows from table "SA "."TEMPORARY"
EXPORT: 267404 rows from table "SA "."TEMP_DAN_ACCOUNT"
EXPORT: 199922 rows from table "SA "."TEMP_DAN_CUSTOMER"
EXPORT: 0 rows from table "SA "."TESTTWO"
EXPORT: 427 rows from table "SA "."TRANSACTION_MART"
EXPORT: 199922 rows from table "SA "."VALUE_STATE"
EXPORT: 9 rows from table "SA "."V_ACCT_BAL"
EXPORT: 10 rows from table "SA "."V_ACCT_BALANCE_DIMENSION"
EXPORT: 2 rows from table "SA "."V_ACCT_TYPE_DIMENSION"
EXPORT: 8 rows from table "SA "."V_AGE_DIMENSION"
EXPORT: 3 rows from table "SA "."V_CUSTOMERVALUE_DIMENSION"
EXPORT: 3 rows from table "SA "."V_CUSTOMER_CUSTVALUE_DIMEMSION"
EXPORT: 3 rows from table "SA "."V_CUSTOMER_DAYSLASTPURCH_DIMEN"
EXPORT: 9 rows from table "SA "."V_CUSTOMER_INCOME_DIMENSION"
EXPORT: 399844 rows from table "SA "."V_CUSTOMER_MODEL_SCORE"
EXPORT: 199922 rows from table "SA "."V_CUSTOMER_VALUE"
EXPORT: 301 rows from table "SA "."V_CUSTOMER_VALUE_DIMENSION"
EXPORT: 3 rows from table "SA "."V_CUSTOMER_VALUE_M3"
EXPORT: 3 rows from table "SA "."V_CUSTOMER_VALUE_MONTH_1_DIMEN"
EXPORT: 5 rows from table "SA "."V_CUSTOMER_VALUE_MONTH_2_DIMEN"
EXPORT: 3 rows from table "SA "."V_CUSTOMER_VALUE_MONTH_3_DIMEN"
EXPORT: 3 rows from table "SA "."V_DAYSSINCEPURCH_DIMENSION"
EXPORT: 25 rows from table "SA "."V_LAST_TRANSACTION_DATE"
EXPORT: 982 rows from table "SA "."V_PRODUCT_SELECT"
EXPORT: 982 rows from table "SA "."V_PRODUCT_SELECT_FS"
EXPORT: 50 rows from table "SA "."V_PROFILE_CUSTOMER_AGE"
EXPORT: 8 rows from table "SA "."V_PROSPECT_AGE_DIMENSION"
EXPORT: 0 rows from table "SA "."V_PROSPECT_AVGBALANCE_DIMENSIO"
EXPORT: 0 rows from table "SA "."V_PROSPECT_AVG_BALANCE_DIMENSI"
EXPORT: 76 rows from table "SA "."V_PROSPECT_REGION_DIMENSION"
EXPORT: 12 rows from table "SA "."V_PURCHASES_LAST_12_MONTHS"
EXPORT: 76 rows from table "SA "."V_REGION_DIMENSION"
EXPORT: 497663 rows from table "SA "."V_SALES_DETAIL"
EXPORT: 36 rows from table "SA "."V_SALES_TIME_DIMENSION"
EXPORT: 36 rows from table "SA "."V_TIME_DIMENSION"
EXPORT: 42195 rows from table "SA "."ZIPCODES"
EXPORT: 42192 rows from table "SA "."ZIPNOV99"
EXPORT: 29470 rows from table "SA "."ZIPS"
Disconnecting from database ... successful!
End time: Tue Jul 08 09:41:37 2014
D:\IBM\Backups\DB2>
http://www.dbatodba.com/db2/how-to-do/how-to-use-db2move
Step 2: Create TMPDB and grant db2adm to schema2
db2 => CREATE DATABASE tmpdb USING CODESET UTF-8 TERRITORY US PAGESIZE 32 k
SQL1047N The application is already connected to another database.
db2 => disconnect
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"DISCONNECT". Expected tokens may include: "<identifier>". SQLSTATE=42601
db2 => disc
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "disc".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
db2 => help
DB21052I "db2ic" has been submitted for execution.
db2 => connect reset
DB20000I The SQL command completed successfully.
db2 => CREATE DATABASE tmpdb USING CODESET UTF-8 TERRITORY US PAGESIZE 32 k
DB20000I The CREATE DATABASE command completed successfully.
db2 => connect to tmpdb
Database Connection Information
Database server = DB2/NT64 10.1.4
SQL authorization ID = ADMINIST...
Local database alias = TMPDB
db2 => create regular tablespace datamart pagesize 32k
DB20000I The SQL command completed successfully.
db2 => GRANT USE OF TABLESPACE datamart TO USER datamart WITH GRANT OPTION
DB20000I The SQL command completed successfully.
db2 => REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC
DB20000I The SQL command completed successfully.
db2 => grant dbadm on database to user datamart
DB20000I The SQL command completed successfully.
db2 => quit
DB20000I The QUIT command completed successfully.
Step 3: Import into temp DB
Z:\Export\DB2>db2move tmpdb import -io replace_create -l lobs
Application code page not determined, using ANSI codepage 1252
***** DB2MOVE *****
Action: IMPORT
Start time: Tue Jul 08 11:21:45 2014
Connecting to database TMPDB ... successful! Server : DB2 Common Server V10.1.4
Binding package automatically ... D:\IBM\SQLLIB\BND\DB2COMMON.BND ... successful
!
Binding package automatically ... D:\IBM\SQLLIB\BND\DB2MOVE.BND ... successful!
* IMPORT: table "SA "."ACCOUNT"
-Rows read: 267324
-Inserted: 267324
-Rejected: 0
-Committed: 267324
* IMPORT: table "SA "."ACCOUNT_TYPE_CODE"
-Rows read: 2
-Inserted: 2
-Rejected: 0
-Committed: 2
* IMPORT: table "SA "."ACCOUNT_WACHOVIA"
-Rows read: 267324
-Inserted: 267324
-Rejected: 0
-Committed: 267324
* IMPORT: table "SA "."ACCT_PURCH_SUM"
-Rows read: 520479
-Inserted: 520479
-Rejected: 0
-Committed: 520479
* IMPORT: table "SA "."ACOSESSION"
-Rows read: 4
-Inserted: 4
-Rejected: 0
-Committed: 4
* IMPORT: table "SA "."ACOSESSIONSTATUS"
-Rows read: 28
-Inserted: 28
-Rejected: 0
-Committed: 28
* IMPORT: table "SA "."ACO_CONTACTCHANNEL"
-Rows read: 6
-Inserted: 6
-Rejected: 0
-Committed: 6
* IMPORT: table "SA "."ACO_CONTACT_LOG"
-Rows read: 148306
-Inserted: 148306
-Rejected: 0
-Committed: 148306
* IMPORT: table "SA "."ACO_OPTOUTLIST"
-Rows read: 20000
-Inserted: 20000
-Rejected: 0
-Committed: 20000
* IMPORT: table "SA "."ACO_PROPOSED_CONTACTS"
-Rows read: 0
-Inserted: 0
-Rejected: 0
-Committed: 0
* IMPORT: table "SA "."ACO_VERSION"
-Rows read: 13
-Inserted: 13
-Rejected: 0
-Committed: 13
* IMPORT: table "SA "."AC_PROFILE_FIELDS"
-Rows read: 50
-Inserted: 50
-Rejected: 0
-Committed: 50
* IMPORT: table "SA "."AGE_RANGE"
-Rows read: 50
-Inserted: 50
-Rejected: 0
-Committed: 50
* IMPORT: table "SA "."AQ_INBOUND"
-Rows read: 0
-Inserted: 0
-Rejected: 0
-Committed: 0
* IMPORT: table "SA "."AQ_OUTBOUND"
-Rows read: 0
-Inserted: 0
-Rejected: 0
-Committed: 0
* IMPORT: table "SA "."BEHAVIOR"
-Rows read: 5
-Inserted: 5
-Rejected: 0
-Committed: 5
* IMPORT: table "SA "."BEHAVIORAL_STATE"
-Rows read: 59922
-Inserted: 59922
-Rejected: 0
-Committed: 59922
* IMPORT: table "SA "."BEHAVIOR_BANKING"
-Rows read: 5
-Inserted: 5
-Rejected: 0
-Committed: 5
* IMPORT: table "SA "."BEHAVIOR_CREDITCARD"
-Rows read: 5
-Inserted: 5
-Rejected: 0
-Committed: 5
* IMPORT: table "SA "."BEHAVIOR_INSURANCE"
-Rows read: 5
-Inserted: 5
-Rejected: 0
-Committed: 5
* IMPORT: table "SA "."CATEGORY"
-Rows read: 7
-Inserted: 7
-Rejected: 0
-Committed: 7
* IMPORT: table "SA "."CATEGORY_FS"
-Rows read: 7
-Inserted: 7
-Rejected: 0
-Committed: 7
* IMPORT: table "SA "."V_TIME_DIMENSION"
-Rows read: 36
-Inserted: 36
-Rejected: 0
-Committed: 36
* IMPORT: table "SA "."ZIPCODES"
-Rows read: 42195
-Inserted: 42195
-Rejected: 0
-Committed: 42195
* IMPORT: table "SA "."ZIPNOV99"
-Rows read: 42192
-Inserted: 42192
-Rejected: 0
-Committed: 42192
* IMPORT: table "SA "."ZIPS"
-Rows read: 29470
-Inserted: 29470
-Rejected: 0
-Committed: 29470
Disconnecting from database ... successful!
End time: Tue Jul 08 11:46:35 2014
Step 4: Grant db2adm on target database
db2 => connect reset
DB20000I The SQL command completed successfully.
db2 => connect to dmdb
Database Connection Information
Database server = DB2/NT64 10.1.4
SQL authorization ID = ADMINIST...
Local database alias = DMDB
db2 => grant dbadm on database to user datamart
DB20000I The SQL command completed successfully.
Step 5: Run DB2MOVE
Z:\Export\DB2>db2move tmpdb copy -sn sa -co TARGET_DB dmdb USER datamart using passw0rd SCHEMA_MAP ((sa,datamart)) TABLESPACE_MAP ((datamart,datamart))
Application code page not determined, using ANSI codepage 1252
***** DB2MOVE *****
Action: COPY
Start time: Tue Jul 08 13:06:21 2014
All schema names matching: SA;
Connecting to database TMPDB ... successful! Server : DB2 Common Server V10.1.
Copy schema SA to DATAMART on the target database DMDB
Create DMT : "SYSTOOLS"."DMT_53bb7c4fcaa41"
Start Load Phase :
db2move finished successfully
Files generated:
-----------------
COPYSCHEMA.20140708130621.msg
LOADTABLE.20140708130621.MSG
Please delete these files when they are no longer needed.
End time: Tue Jul 08 13:11:48 2014
Z:\Export\DB2>
Step 6: Drop TMPDB
D:\IBM\SQLLIB\BIN>db2 connect to tmpdb
Database Connection Information
Database server = DB2/NT64 10.1.4
SQL authorization ID = ADMINIST...
Local database alias = TMPDB
D:\IBM\SQLLIB\BIN>db2 "quiesce database immediate force connections"
DB20000I The QUIESCE DATABASE command completed successfully.
D:\IBM\SQLLIB\BIN>db2 "unquiesce database"
DB20000I The UNQUIESCE DATABASE command completed successfully.
D:\IBM\SQLLIB\BIN>
D:\IBM\SQLLIB\BIN>db2 "force application all"
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
D:\IBM\SQLLIB\BIN>db2stop force
07/08/2014 13:21:04 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
D:\IBM\SQLLIB\BIN>db2start
07/08/2014 13:21:18 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
D:\IBM\SQLLIB\BIN>db2 "drop database tmpdb"
DB20000I The DROP DATABASE command completed successfully.
No comments:
Post a Comment