Results 1 to 12 of 12

Thread: Schema Change

  1. #1
    Join Date
    Jan 2009
    Posts
    153

    Unanswered: Schema Change

    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??

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Admin_copy_schema
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Jan 2009
    Posts
    153
    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

  4. #4
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    chk for syntax of procedure call
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    something like this:

    connect to sample;
    call ADMIN_COPY_SCHEMA('administrator','appuser','copy' ,'rknai r','userspace1','userspace1', 'error_table_schema', 'error_table_name');
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2009
    Posts
    153
    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

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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).

  8. #8
    Join Date
    Jan 2009
    Posts
    153
    Thanks Bella

  9. #9
    Join Date
    Jan 2009
    Posts
    153
    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

  10. #10
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    error code signal a lot. see if 'administrator' exists
    rather chk for schema in upper case
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  11. #11
    Join Date
    Jan 2009
    Posts
    153
    Thanks Rahul,

    It worked

  12. #12
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    change schema for tables

    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?
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •