cross clmn

Thursday, 10 March 2016

Migration of all Databases with all users and grant & privileges of IBM Netezza from one to Another server

 #############################################################################

Migration of all Databases with all users and grant & privileges of IBM Netezza from one  to Another server

##############################################################################


Please Use Below steps to migrate from one server to another server.

1. Take DDL of All Users.

nz_ddl_user > nz_ddl_user.sql

2. Take DDL of All Groups one by one Important 

nz_ddl_group GroupName >  nz_ddl_group_GroupName .sql

3. Take DDL of Grant given to groups one by one Important 

nz_ddl_grant_group DBname > nz_ddl_grant_group_DBname .sql

4. Take DDL of Grants to given to all user.

nz_ddl_grant_user > nz_ddl_grant_user.sql

5. Move all SQL file to other server and import it.
From OS run it.

nzsql < nz_ddl_user.sql

connect to each database and run

nzsql -d dbname -f  nz_ddl_group_GroupName .sql

 connect to each database and run

nzsql -d dbname -f nz_ddl_grant_group_DBname .sql

nzsql <  nz_ddl_grant_user.sql

6. Now Migrate All Databases using nz_migrate, below are the few examples.

nz_migrate -shost Source-Host -thost Target-Host -sdb NZDB -tdb NZDB -suser admin -spassword Source_passwd  -tuser admin -tpassword targetpasswd -format binary  -genStats Express  -CreateTargetTable YES -CreateTargetDatabase yes -threads 10 -Status


Note :- If you want to rename database during nz_migrate

nz_migrate -shost Source-Host -thost Target-Host -sdb POMSDB-tdb N_POMSDB -suser admin -spassword Source_passwd  -tuser admin -tpassword targetpasswd -format binary  -genStats Express  -CreateTargetTable YES -CreateTargetDatabase yes -threads 10 -Status



Posted by at

6 comments:

  1. Shripad19 January 2017 at 10:46

    In step 5, you are saying connect to each database and run the script. However, you haven't migrated all user databases to target server, so to which databases you are connecting in step 5?

    ReplyDelete
  2. Shripad19 January 2017 at 10:48

    I am working on a request to migrate all Netezza databases from 1 server to another new server and since databases are not migrated to new server, in step 5, to which databases I should connect to run those sqls?

    ReplyDelete
    Replies
    1. apple19 January 2017 at 11:05

      This comment has been removed by the author.

      Delete
  3. Shripad19 January 2017 at 12:09

    Never mind, I am able to migrate users and groups. I am all set for now.

    ReplyDelete
  4. Shripad19 January 2017 at 12:10

    No need to create dummy database.

    ReplyDelete
  5. apple19 January 2017 at 17:36

    Yes, you may create group without creating database but, it would be your security violation, if you create group out of database , your object access would publically accessible

    ReplyDelete

Subscribe to: Post Comments (Atom)