COLLATE USING IDENTITY

[Nanchil]

Hi,
We created a database (DB2 UDB 7.2 on solaris 8) without this
(COLLATE USING IDENTITY ) option. But we need this now for binary
sorting. The database is siebel database. Is it possible to drop and
recreate the database with COLLATE USING IDENTITY option and restore
the database. Please advise.

Nanchil.

[Blair Adamache]

If the database was created in Unicode, backup/restore should work.

If the database was not created in unicode, you'll need to export the
data using a tool like db2move, capture other objects (triggers etc)
using db2look -e, and then recreate those objects, and load or import
the tables in the newly created database.

Nanchil wrote:

Hi,
We created a database (DB2 UDB 7.2 on solaris 8) without this
(COLLATE USING IDENTITY ) option. But we need this now for binary
sorting. The database is siebel database. Is it possible to drop and
recreate the database with COLLATE USING IDENTITY option and restore
the database. Please advise.

Nanchil.

[Ken]

We did this by unloading the databases using db2move, dropping the
database, recreate the database with the collate using identity and
then reloading with db2move:

1 - run db2look
2 - export database using db2move
3 - unload the db configuration (get db configuration for x)
4 - drop tablespaces (one database left tags in the raw devices)
5 - drop db (I had to remove some "lost+found" directories and
uncatalog one of the databases)
6 - create db with collate using identity (same name as was dropped)
7 - update db configuration
8 - backup the db
9 - ran sql from db2 look to create tables (probably not necessary)
10 - backup the db
11 - use db2load to load the db
12 - run stats
13 - create procedures
14 - create new packages (we had the dbrm's in a directory)
15 - update user and group permissions for tables...
16 - set integrities on constraints
17 - rebind QMF
18 - rebind CLI/ODBC with Client Configuration Assistant
19 - verify connections
20 - backup db

I think we tried doing it with a backup and restore and the collating
sequence was not what we want when we were done with the restore so we
went with the unload and reload. We were lucky it got caught before
we were in production.

Ken

na*****@hotmail.com (Nanchil) wrote in message news:<6c*************************@posting.google.c om>...

Hi,
We created a database (DB2 UDB 7.2 on solaris 8) without this
(COLLATE USING IDENTITY ) option. But we need this now for binary
sorting. The database is siebel database. Is it possible to drop and
recreate the database with COLLATE USING IDENTITY option and restore
the database. Please advise.

Nanchil.

[Nanchil]

Hi All,
Thanks for your responses. I think we have to do the export and
import the database. Thanks for your help.

Nanchil.

lf****@volcanomail.com (Ken) wrote in message news:<74**************************@posting.google. com>...

We did this by unloading the databases using db2move, dropping the
database, recreate the database with the collate using identity and
then reloading with db2move:

1 - run db2look
2 - export database using db2move
3 - unload the db configuration (get db configuration for x)
4 - drop tablespaces (one database left tags in the raw devices)
5 - drop db (I had to remove some "lost+found" directories and
uncatalog one of the databases)
6 - create db with collate using identity (same name as was dropped)
7 - update db configuration
8 - backup the db
9 - ran sql from db2 look to create tables (probably not necessary)
10 - backup the db
11 - use db2load to load the db
12 - run stats
13 - create procedures
14 - create new packages (we had the dbrm's in a directory)
15 - update user and group permissions for tables...
16 - set integrities on constraints
17 - rebind QMF
18 - rebind CLI/ODBC with Client Configuration Assistant
19 - verify connections
20 - backup db

I think we tried doing it with a backup and restore and the collating
sequence was not what we want when we were done with the restore so we
went with the unload and reload. We were lucky it got caught before
we were in production.

Ken

na*****@hotmail.com (Nanchil) wrote in message news:<6c*************************@posting.google.c om>...

Hi,
We created a database (DB2 UDB 7.2 on solaris 8) without this
(COLLATE USING IDENTITY ) option. But we need this now for binary
sorting. The database is siebel database. Is it possible to drop and
recreate the database with COLLATE USING IDENTITY option and restore
the database. Please advise.

Nanchil.

[Sherman Lau]

Nanchil,

What is the codepage or codeset of your database? If your database is UTF-8
or 1208, the default collation is binary already.

Sherman

"Nanchil" <na*****@hotmail.com> wrote in message
news:6c*************************@posting.google.co m...

Hi All,
Thanks for your responses. I think we have to do the export and
import the database. Thanks for your help.

Nanchil.

lf****@volcanomail.com (Ken) wrote in message

news:<74**************************@posting.google. com>...

We did this by unloading the databases using db2move, dropping the
database, recreate the database with the collate using identity and
then reloading with db2move:

1 - run db2look
2 - export database using db2move
3 - unload the db configuration (get db configuration for x)
4 - drop tablespaces (one database left tags in the raw devices)
5 - drop db (I had to remove some "lost+found" directories and
uncatalog one of the databases)
6 - create db with collate using identity (same name as was dropped)
7 - update db configuration
8 - backup the db
9 - ran sql from db2 look to create tables (probably not necessary)
10 - backup the db
11 - use db2load to load the db
12 - run stats
13 - create procedures
14 - create new packages (we had the dbrm's in a directory)
15 - update user and group permissions for tables...
16 - set integrities on constraints
17 - rebind QMF
18 - rebind CLI/ODBC with Client Configuration Assistant
19 - verify connections
20 - backup db

I think we tried doing it with a backup and restore and the collating
sequence was not what we want when we were done with the restore so we
went with the unload and reload. We were lucky it got caught before
we were in production.

Ken

na*****@hotmail.com (Nanchil) wrote in message news:<6c*************************@posting.google.c om>...

Hi,
We created a database (DB2 UDB 7.2 on solaris 8) without this
(COLLATE USING IDENTITY ) option. But we need this now for binary
sorting. The database is siebel database. Is it possible to drop and
recreate the database with COLLATE USING IDENTITY option and restore
the database. Please advise.

Nanchil.