Search

Ramchandra's Weblog

Just ……everything about DB2 LUW

Category

Db2 – data movements/migration

Script to Reset Identity Columns

db2 connect to <database>
db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
db2 “select ‘select ”ALTER TABLE ‘|| rtrim(TBCREATOR) ||’.’||TBNAME ||’ ALTER COLUMN ‘||NAME ||’ RESTART WITH ”’ ||’||cast( max(‘||NAME||’)+1 as char(15)) ||”;” from ‘|| rtrim(TBCREATOR) ||’.’||TBNAME ||’;’ from   SYSIBM.SYSCOLUMNS     where IDENTITY=’Y'” > temp_identity1.sql
db2 -tvf temp_identity1.sql|grep ‘ALTER TABLE’  >  temp_identity2.sql
cat temp_identity2.sql|grep ‘;’$ > temp_identity3.sql
db2 -tvf temp_identity3.sql > temp_identity4.sql
db2 UNQUIESCE DATABASE

Remove Backup Pending state without taking backup

doyouDB2.com – Remove Backup Pending state without taking backup

Remove Backup Pending state without taking backup

DROPING REFERENCIAL INTEGRITY CONSTRAINT IN DB2

steps for droping and recreating RI:-

1. genrate RI by using db2look:
db2look -d dbname -e -a -td @ -l -o dbname.sql

2.check the RI
select tbname, relname, refkeyname from sysibm.sysrels

create script for droping RI
select ‘ALTER TABLE ‘||tbname||’  DROP CONSTRAINT ‘||relname   from sysibm.sysrels

3.do your operation data migration/ db2move ..etc

4. recreate RI (keep only RI part in dbname.sql delete the rest)
db2 -td@ -vf dbname.sql

Production to Development: Moving Databases

Production to Development: Moving Databases

DB2 migration across different platforms.

DB2 migration across different platforms.

DB2 Basics: Cloning DB2 Databases Using Redirected Restore

DB2 Basics: Cloning DB2 Databases Using Redirected Restore

Using DB2 utilities to clone databases across different platforms

Using DB2 utilities to clone databases across different platforms

DB2 Universal Database versus Oracle data movement

DB2 Universal Database versus Oracle data movement

Blog at WordPress.com.

Up ↑