Topic
  • 4 replies
  • Latest Post - ‏2012-01-24T09:22:12Z by vzlomanov
vzlomanov
vzlomanov
3 Posts

Pinned topic Copying schema in HADR database

‏2012-01-12T13:35:05Z |
How can I copy schema in HADR database via CLI?
Infocenter tells me that only DDL copymode is supported for HADR database (http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0022035.htm).

Env: aix, db2 9.5
Updated on 2012-01-24T09:22:12Z at 2012-01-24T09:22:12Z by vzlomanov
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Copying schema in HADR database

    ‏2012-01-23T23:55:28Z  
    I've never used a HADR Database, but have you considered the DB2 db2move command?
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Copying schema in HADR database

    ‏2012-01-24T01:30:57Z  
    Hi,

    You could do that copy by taking the output of a select in the catalog, creating a DDL and a DML and then execute it. Something like this:

    
    db2 -x 
    "select 'create table NEW_SCHEMA.' || rtrim(tabname) || ' like OLD_SCHEMA.' || rtrim(tabname) || ';' from syscat.tables where tabschema = 'OLD_SCHEMA' and type = 'T'" | db2 +p -tv   db2 -x 
    "select 'insert into NEW_SCHEMA.' || rtrim(tabname) || ' select * from OLD_SCHEMA.' || rtrim(tabname) || ';' from syscat.tables where tabschema = 'OLD_SCHEMA' and type = 'T'" | db2 +p -tv
    


    The first sentence will create tables with the same structure of the original tables in the original schema.

    The second sentence will populate the new tables with the content of the original tables.

    Optionally, you can change the isolation level before the insertion in order to prevent modifications in the original table.
  • vzlomanov
    vzlomanov
    3 Posts

    Re: Copying schema in HADR database

    ‏2012-01-24T09:20:44Z  
    I've never used a HADR Database, but have you considered the DB2 db2move command?
    I need to create schema copy within the same database, but with different name.
    Infocenter says:
    "The TARGET_DB cannot be the same as the source database and must be a local database. The ADMIN_COPY_SCHEMA procedure can be used for copying schemas within the same database."
    (http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0002079.htm)

    ADMIN_COPY_SCHEMA:
    "Only DDL copymode is supported for HADR databases."
    (http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.dbobj.doc/doc/r0052064.html)

    So, It's looks like I can't do this using built-in features
  • vzlomanov
    vzlomanov
    3 Posts

    Re: Copying schema in HADR database

    ‏2012-01-24T09:22:12Z  
    Hi,

    You could do that copy by taking the output of a select in the catalog, creating a DDL and a DML and then execute it. Something like this:

    <pre class="jive-pre"> db2 -x "select 'create table NEW_SCHEMA.' || rtrim(tabname) || ' like OLD_SCHEMA.' || rtrim(tabname) || ';' from syscat.tables where tabschema = 'OLD_SCHEMA' and type = 'T'" | db2 +p -tv db2 -x "select 'insert into NEW_SCHEMA.' || rtrim(tabname) || ' select * from OLD_SCHEMA.' || rtrim(tabname) || ';' from syscat.tables where tabschema = 'OLD_SCHEMA' and type = 'T'" | db2 +p -tv </pre>

    The first sentence will create tables with the same structure of the original tables in the original schema.

    The second sentence will populate the new tables with the content of the original tables.

    Optionally, you can change the isolation level before the insertion in order to prevent modifications in the original table.
    I'll try it, thanks