Hi Experts,

How to change the schema name of an object??

Now all my object are under administrator schema...I want to change all the objects from admnistrator schema to user1 and in another database i want to change schema of some particular objects ..

How can i achieve this??

Admin_copy_schema

Hi,

When I tried I am getting error.

ADMIN_COPY_SCHEMA(administrator,appuser,copy,rknai r,userspace1,userspace1, error_table_schema, error_table_name)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "(" was found following "ADMIN_COPY_SCHEMA".
Expected tokens may include: "DELIMITED_TYPE_IDENTIFIER". SQLSTATE=42601

SQL0104N An unexpected token "(" was found following "ADMIN_COPY_SCHEMA". Expected tokens may include: "DELIMITED_TYPE_IDENTIFIER

chk for syntax of procedure call

something like this:

connect to sample;
call ADMIN_COPY_SCHEMA('administrator','appuser','copy' ,'rknai r','userspace1','userspace1', 'error_table_schema', 'error_table_name');

HI Rahul & Marcus ,

Thanks for the quick reply..

I am a newbie to DB2 can u please tell me what exactly i have to do...

My requiremnet is to change the schema from administrator to appuser..

TIA

You can use the admin_copy_schema stored procedure to help you with renaming (it actually copies) the schema. Please refer to the following URLs for more info:
IBM DB2 9.5 Information Center for Linux, UNIX, and Windows
Conveniently create new versions of your database objects


Here is an example of copying schema 'TEST' to TEST2':

test@p6db2serv /home/test > db2 "call SYSPROC.ADMIN_COPY_SCHEMA ('TEST','TEST2','COPY', NULL, NULL, NULL, 'COPYSCHEMA', 'COPYERROR')"

Value of output parameters
--------------------------
Parameter Name : ERRORTABSCHEMA
Parameter Value : COPYSCHEMA

Parameter Name : ERRORTABNAME
Parameter Value : COPYERROR

Return Status = 0


Note: admin_copy_schema needs to use the systoolspace tablespace. If you don't have it, it needs to created prior to using the procedure. Here is an example of creating it in an automatic storage db:

test@p6db2serv /home/test > db2 "CREATE TABLESPACE SYSTOOLSPACE MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4"
DB20000I The SQL command completed successfully.



The old schema can be removed using the admin_drop_schema stored procedure. Here is an example:

test@p6db2serv /home/test > db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA('TEST', NULL, 'ERRORSCHEMA', 'ERRORTABLE')"

Value of output parameters
--------------------------
Parameter Name : ERRORTABSCHEMA
Parameter Value : ERRORSCHEMA

Parameter Name : ERRORTAB
Parameter Value : ERRORTABLE

Return Status = 0


Please check the restrictions/usage notes in the manual. I'd highly recommend taking a backup prior to copying/dropping the schema if this is an important database (just in case something happens).

Thanks Bella

Hi Bella ,

Wheni tried to execute the command I am getting error

db2 => call SYSPROC.ADMIN_COPY_SCHEMA ('administrator','appuser','COPY', NULL,N
ULL, NULL, 'COPYSCHEMA', 'COPYERROR')
SQL0443N Routine "*MIN_COPY_SCHEMA" (specific name "") has returned an error
SQLSTATE with diagnostic text "Source schema does not exist, or contains no
objects". SQLSTATE=38000


All user tables are under administrator schema...

TIA

error code signal a lot. see if 'administrator' exists
rather chk for schema in upper case

Thanks Rahul,

It worked

db2 v8.2 on AIX 5/db2 v9.5 on AIX 6
We need to change schema for abt 200 db2 tables (containing data) from AAA to BBB.

db2move seem like a good idea but it's working only between databases.

Looks like admin_copy_schema does it within same database, but I need to copy tables data and rename indexes schemas as well. How and can I use it for this purpose? I guess we would have to drop existing packages and recreate them for new schema as this is a condition for this API.

The database is on v8.2 now so I am thinking we might have to wait till we upgrade to v9.5 and then do it.

Any thoughts?