Browsing Category "db2"

Search This Blog

Powered by Blogger.

Pages

Browsing "Older Posts"

Browsing Category "db2"

Installing DB2 on ubuntu

By TY → Saturday, October 17, 2015

1. Uncomment the Partner repository lines by editing the /etc/apt/sources.list file
Look for the word "partner" in the file /etc/apt/sources.list
Uncomment the line for (by removing the "#") character
# deb http://archive.canonical.com/ubuntu lucid partner
# deb-src http://archive.canonical.com/ubuntu lucid partner
If your Ubuntu release is not lucid, add in the two lines above into the sources.list file

2. Update the package list
$ sudo apt-get update

3. Install DB2 using apt-get
$ sudo apt-get install db2exc

Reference:
http://www.db2teamblog.com/2010/09/db2-express-c-packages-for-ubuntu-1004.html

Backup and Restore DB2 to Another Machine

By TY →

TL;DR;
Use db2look to export DDL and db2move to export data.

The name of the database is "mydb" as an example
On source machine:
$ mkdir migrate
$ cd migrate
$ db2look -d mydb -e -a -o db2look.sql
$ db2move mydb export
$ cd ..
$ tar cf migrate.tar migrate
$ gzip migrate.tar

Transfer migrate.tar.gz to destination machine.
Note: Make sure that the database is created.
If not create it with the following:
Create database [db name, eg:mydb]

On destination machine:
$ gunzip migrate.tar.gz
$ tar xf migrate.tar
$ cd migrate
$ db2 -tvf db2look.sql
$ db2move mydb load

You may encounter -668 error when your application runs on the restored database. You can fix that with SET INTEGRITY.

If you have cyclic dependencies of the tables when you are trying to do SET INTEGRITY, the solution is at this post "Solving cyclic dependency for DB2 SET INTEGRITY".

References:
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002079.html
http://www.ibm.com/developerworks/data/library/techarticle/dm-0403melnyk/

Solving cyclic dependency for DB2 SET INTEGRITY

By TY → Wednesday, October 14, 2015

While I am trying to resolve -668 error for DB2, the solution is to set integrity on the related tables.
However, I ended up trying to set integrity on a set of tables with cyclical dependencies. So the command 
SET INTEGRITY for <table> immediate checked
does not work.

Searching more, I found that the solution is actually the same command, but all the dependent table should be comma separated. Something like the below.

SET INTEGRITY for table_a, table_b, table_c immediate checked

Hope this helps!

DB2 SQL Error: SQLCODE=-668. Fixed with SET INTEGRITY

By TY → Tuesday, October 13, 2015

After I migrated my DB2 data to a new machine, I encountered "DB2 SQL Error: SQLCODE=-668".
At first, I thought that I can do a table reorg to fix it.  However, when I issue the reorg command, the same error code -668 was returned.

After some searching, the solution is to set integrity for the affected tables.

1. To find all the affected tables.
select 'set integrity for '||rtrim(tabschema)||'.'||tabname||' immediate checked' from syscat.tables where status = 'C'

2. Copy and paste the generated statements into db2 console.

3. Repeat a few times to clear all the tables so that the SQL from step 1 returns no more row.

Note, you may experience cyclic dependency on your tables that you are not able to clear. You can check out this post on cyclical dependencies and set integrity for db2.