ShowTable of Contents
Next steps shows how to move JCR database from windows DB2 to AIX DB2, this steps can applied on any other cross-platform systems, but it was tested only from windows to AIX.
The move happened between DB2 9.7 on windows 2003 to DB2 9.7 on AIX 6.1 for Portal 184.108.40.206
First Step: Create JCR database on AIX database
Run the following commands on the AIX DB2 server system to create the JCR database
db2 "CREATE DB JCRDB using codeset UTF-8 territory us PAGESIZE 8192"
db2 "UPDATE DB CFG FOR JCRDB USING applheapsz 4096"
db2 "UPDATE DB CFG FOR JCRDB USING app_ctl_heap_sz 1024"
db2 "UPDATE DB CFG FOR JCRDB USING stmtheap 32768"
db2 "UPDATE DB CFG FOR JCRDB USING dbheap 2400"
db2 "UPDATE DB CFG FOR JCRDB USING locklist 1000"
db2 "UPDATE DB CFG FOR JCRDB USING logfilsiz 4000"
db2 "UPDATE DB CFG FOR JCRDB USING logprimary 12"
db2 "UPDATE DB CFG FOR JCRDB USING logsecond 20"
db2 "UPDATE DB CFG FOR JCRDB USING logbufsz 32"
db2 "UPDATE DB CFG FOR JCRDB USING avg_appls 5"
db2 "UPDATE DB CFG FOR JCRDB USING locktimeout 30"
db2 "UPDATE DB CFG FOR JCRDB using AUTO_MAINT off"
db2 "CONNECT TO JCRDB"
db2 "CREATE BUFFERPOOL ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 8000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 8000 PAGESIZE 32 K"
db2 "CREATE BUFFERPOOL CMBMAIN4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE REGULAR TABLESPACE ICMLFQ32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLFQ32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMLNF32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLNF32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMVFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMVFQ04') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE REGULAR TABLESPACE ICMSFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMSFQ04') BUFFERPOOL ICMLSFREQBP4"
db2 "CREATE REGULAR TABLESPACE CMBINV04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('CMBINV04') BUFFERPOOL CMBMAIN4"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('icmlssystspace32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING ('icmlssystspace4') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "DISCONNECT JCRDB"
Visit next link to see in details how to configure and create JCR database.
Step two: Copy JCR schema
From the windows database export the JCR schema, using next command
db2look -d JCRDB -e -a -o c:\JCR_DB\db2look.sql
Where JCRDB JCR database Name
Note: if the AIX database name will not be the same as the windows database, edit the file and change the first line (CONNECT TO JCRDB;).
The reason for exporting the schema in separate step instead of just using the DB move, is the DB move doesn't export the constraints the tables are not assigned to their appropriate table space, so this step creates the tables with their constraints and table space assignment.
1. Copy generated schema db2look.sql to the AIX database, for example on opt/JCR_DB folder.
2. Go to the directory: cd /opt/JCR_DB
3. Run the command to create the schema on AIX database:
db2 -tvf db2look.sql > db2look_result.txt
Note: In some cases running Step Three point 10 could fail giving constraint error, in that case try to split db2look.sql to two parts first one contains creating the tables (db2look_tables.sql) the other contains the constraints (db2look_constraints.sql), generated db2look.sql will have tables creation statemnts on the upper part and constrains on the lower part. run the command on this step on db2look_tables.sql instead on db2look.sql and run "db2 -tvf db2look_constraints.sql > db2look_result.txt" after "Step Three"
4. Check the result file db2look_result.txt and make sure no error occurred.
Step Three: db2move
On windows database run db2move to get the database, we are here only interested on the data rows not the schema so we will use LOAD mode instead of IMPORT.
1. Create folder to store the database on it, for example c:\JCR_Move
2. Go to the folder: cd JCR_Move
3. Run export command:
4. Copy the files to AIX server, for example opt/JCR_DB
5. Go to the folder: cd /opt/JCRDB_DB/
Note you need to be on the root user not the database user, if you run it using DB user you might have privilege issues
6. Connect to AIX database:
7. Run move command:
db2move JCRDB load -u dbinst2 -p password
8. Check the LOAD.out log and make sure no errors are there
Note: SYSTOOLS schema was not moved on "Copy JCR schema" step so you will get error on their tables, you can ignore them
9. After finishing db2move command many database tables are in the Load Pending state, and you can’t do any transactions on them, so you change their state with executing SET INTEGRITY statement with the IMMEDIATE CHECKED option on the tables, run following script to show what are the tables need to be fixed:
db2 "select 'set integrity for '||tabschema||'.'||tabname ||' immediate checked' from syscat.tables where type = 'T' and status = 'C'" > /opt/JCRDB_DB/db2FixCheckPending.sql
10. Run the result on db2 to fix integrity issue, this procedure need to be run more than one time, because there are some dependencies will be resolved with each run
Step Four: Configure the Portal with the new database
1. Run the ConfigEngine connect-database command to configure the portal with new database, see next link for more details:
2. Restart the portal and you should be able to see your WCM libraries