Topic
  • 15 replies
  • Latest Post - ‏2012-06-01T00:08:45Z by bluey01
SystemAdmin
SystemAdmin
1632 Posts

Pinned topic Best way to copy tables between databases

‏2012-03-05T22:51:34Z |
I have a DB2 Express-c database V8.2 running on a Linux server and I need to copy some of the tables to a new DB2 Express-C database V9.7 on a Windows 2008 Server. I've tried to export theses tables to an IXF file and then Load them with the Import Utility into V9.7. Everything I've tried fails because of the Identity column. How can I import these tables into the new database and preserve the Identity column value?
Updated on 2012-06-01T00:08:45Z at 2012-06-01T00:08:45Z by bluey01
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Best way to copy tables between databases

    ‏2012-03-06T18:09:13Z  
    Either create the new identity column as GENERATED BY DEFAULT instead of GENERATED ALWAYS, or use the import option (see documentation) to ignore identity creation.

    Andy
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Best way to copy tables between databases

    ‏2012-03-06T18:41:37Z  
    Either create the new identity column as GENERATED BY DEFAULT instead of GENERATED ALWAYS, or use the import option (see documentation) to ignore identity creation.

    Andy
    Either way, will the value in the Identity column be preserved? This is a key field that is referenced by numerous other tables in the database. Thanks Dave
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Best way to copy tables between databases

    ‏2012-03-06T19:39:01Z  
    Either way, will the value in the Identity column be preserved? This is a key field that is referenced by numerous other tables in the database. Thanks Dave
    In Data Studio when run the Load > With Import Utility and specify The Import file with the path I get a file not found error. Where does this file need to be placed for the Data Studio Import Utility to pick it up?

    
    CALL SYSPROC.ADMIN_CMD( 
    'IMPORT FROM "C:\Users\dfrazell\Desktop\Exports\users.ixf" OF IXF MESSAGES ON SERVER INSERT INTO PROJECT.USERS' )     Result set 1 -------------- ROWS_READ            ROWS_SKIPPED         ROWS_INSERTED        ROWS_UPDATED         ROWS_REJECTED        ROWS_COMMITTED       MSG_RETRIEVAL                                                                                                                                                                                                                                           MSG_REMOVAL -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -                    -                    -                    -                    -                    - SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS(
    '13790_DB2ADMIN')) AS MSG                                                                                                                                                                                          CALL SYSPROC.ADMIN_REMOVE_MSGS(
    '13790_DB2ADMIN') 1 record(s) selected. Return Status = 0 SQL20397W  Routine 
    "SYSPROC.ADMIN_CMD" execution has completed, but at least one error, 
    "SQL3196", was encountered during the execution. More information is available. Query execution time => 2 s: 364 ms   SQL3196N The input file was not found. SQL3110N The utility has completed processing.  
    "0" rows were read from the input file.
    
  • lakshman
    lakshman
    67 Posts

    Re: Best way to copy tables between databases

    ‏2012-03-06T20:25:18Z  
    Either way, will the value in the Identity column be preserved? This is a key field that is referenced by numerous other tables in the database. Thanks Dave
    Hi

    The easiest way to copy tables between the databases by using the Data Studio copy/paste functionality by using you can copy most of the objects and data.

    Here are the steps ...

    1. In the Data Source Explorer expand the source data connection, and then expand the database folders to expose the objects that you want to copy.

    2. Under the source connection, select one or more objects. Use the Ctrl key and Shift key to select multiple objects. Right-click the selected objects, and then click Copy.

    3. Expand the data connection in which you want to paste the copied objects, right-click the database node or a schema node, and then click Paste. The Paste Database Objects wizard opens.

    This functionality uses JDBC api to copy tables and data and doesn't use the any utilities. Please note that for the performance reasons we limit each copy only for 100 objects and 10000 rows.

    Please try and let us know your feedback.
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Best way to copy tables between databases

    ‏2012-03-06T20:56:56Z  
    • lakshman
    • ‏2012-03-06T20:25:18Z
    Hi

    The easiest way to copy tables between the databases by using the Data Studio copy/paste functionality by using you can copy most of the objects and data.

    Here are the steps ...

    1. In the Data Source Explorer expand the source data connection, and then expand the database folders to expose the objects that you want to copy.

    2. Under the source connection, select one or more objects. Use the Ctrl key and Shift key to select multiple objects. Right-click the selected objects, and then click Copy.

    3. Expand the data connection in which you want to paste the copied objects, right-click the database node or a schema node, and then click Paste. The Paste Database Objects wizard opens.

    This functionality uses JDBC api to copy tables and data and doesn't use the any utilities. Please note that for the performance reasons we limit each copy only for 100 objects and 10000 rows.

    Please try and let us know your feedback.
    I like the copy and paste process but I can't get past the following error.

    A default user temporary tablespace does not exist. For successful data movement create the user temporary tablespace with page size suggested in exception.
    A default table space could not be found with a page size of at least "4096" that authorization ID "DB2ADMIN" is authorized to use.. SQLCODE=-286, SQLSTATE=42727, DRIVER=4.12.79

    I authorized the DB2ADMIN to tablespace USERSPACE1 that has a page size of 4096. How do I set or create default user temporary tablespace
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Best way to copy tables between databases

    ‏2012-03-06T21:35:49Z  
    I like the copy and paste process but I can't get past the following error.

    A default user temporary tablespace does not exist. For successful data movement create the user temporary tablespace with page size suggested in exception.
    A default table space could not be found with a page size of at least "4096" that authorization ID "DB2ADMIN" is authorized to use.. SQLCODE=-286, SQLSTATE=42727, DRIVER=4.12.79

    I authorized the DB2ADMIN to tablespace USERSPACE1 that has a page size of 4096. How do I set or create default user temporary tablespace
    Alright. Got pass my last error. Now I have two issues.

    1. How can I preserve the Identity key value? The data in field UUID is the unique key to the table so I need to preserve the value from the source table.
    2. Copy ended with the following error even though all 177 rows were copied:
    "An unexpected token "" was found following ")". Expected tokens may include: "AS".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.62.80"

    Thanks - Dave
  • lakshman
    lakshman
    67 Posts

    Re: Best way to copy tables between databases

    ‏2012-03-07T02:42:30Z  
    Alright. Got pass my last error. Now I have two issues.

    1. How can I preserve the Identity key value? The data in field UUID is the unique key to the table so I need to preserve the value from the source table.
    2. Copy ended with the following error even though all 177 rows were copied:
    "An unexpected token "" was found following ")". Expected tokens may include: "AS".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.62.80"

    Thanks - Dave
    Hi David,

    Its good to know that you able to figure out of creating a user temporary table space. We use a global temporary table for moving the data from database to another for performance reasons hence it requires the temporary table space.

    As Andy mentioned it requires to change the type of IDENTITY column "GENERATED BY DEFAULT" so that it can insert the values into destination table and preserve the values as in source.

    To diagnose the error, can you please send us the SQL statement which it got failed by enabling the connection trace (select the connection, right click and open properties , go to "Tracing" tab , uncheck the "Disable Filter" and select check box "Statement Calls")
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Best way to copy tables between databases

    ‏2012-03-07T02:50:13Z  
    • lakshman
    • ‏2012-03-07T02:42:30Z
    Hi David,

    Its good to know that you able to figure out of creating a user temporary table space. We use a global temporary table for moving the data from database to another for performance reasons hence it requires the temporary table space.

    As Andy mentioned it requires to change the type of IDENTITY column "GENERATED BY DEFAULT" so that it can insert the values into destination table and preserve the values as in source.

    To diagnose the error, can you please send us the SQL statement which it got failed by enabling the connection trace (select the connection, right click and open properties , go to "Tracing" tab , uncheck the "Disable Filter" and select check box "Statement Calls")
    I don't understand where to change the GENERATED BY DEFAULT value? Do I change it in the paste wizard? I don't see an option for that.

    Thanks Dave
  • lakshman
    lakshman
    67 Posts

    Re: Best way to copy tables between databases

    ‏2012-03-08T02:07:01Z  
    I don't understand where to change the GENERATED BY DEFAULT value? Do I change it in the paste wizard? I don't see an option for that.

    Thanks Dave
    In the last page of Paste Wizard there is a check box "Open edit file for editing" as showed in the attached screen shot, please select the check box and it will open the file in the editor and you can change the "CREATE TABLE" definition and deploy from there itself.

    The data will be stored in the separate file <filename>_data.sql in the same folder of the project. You can open this file and deploy the data using the editor. If you use the default project ".sqlxeditor_project" folder while saving the file which is temporary development project which doesn't show up in the project explorer. The recommend way is to create a new your own development project from Project explorer, by using the context menus New -> Data Development Project .
    Otherwise, please use the navigator view (windows -> show view -> Other -> General -> Navigator) to view the files under default project ".sqlxeditor_project"
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Best way to copy tables between databases

    ‏2012-03-09T15:02:37Z  
    • lakshman
    • ‏2012-03-08T02:07:01Z
    In the last page of Paste Wizard there is a check box "Open edit file for editing" as showed in the attached screen shot, please select the check box and it will open the file in the editor and you can change the "CREATE TABLE" definition and deploy from there itself.

    The data will be stored in the separate file <filename>_data.sql in the same folder of the project. You can open this file and deploy the data using the editor. If you use the default project ".sqlxeditor_project" folder while saving the file which is temporary development project which doesn't show up in the project explorer. The recommend way is to create a new your own development project from Project explorer, by using the context menus New -> Data Development Project .
    Otherwise, please use the navigator view (windows -> show view -> Other -> General -> Navigator) to view the files under default project ".sqlxeditor_project"
    Alright, I'm about ready to give up on Data Studio as a development environment due to the number of issues I'm having.

    Anyway, the copy and paste option still fails for the following two reasons:

    1. The <filename>_data.sql file that's created in the project folder doesn't include the UUID field which contains our Identity data. All other fields are present.
    2. When I do deploy the data file using the editor, it fails to re-load all 177 records. 112 records fail to load due to an error:
    
    
    "Unexpected Text ",
    " encountered"
    
    . It appears there's text fields that have no data that are represented in the INSERT INTO statement as a space between each comma (,) delimiter. so for example, a snippet looks like
    
    _... ,
    'name', , 0, 1, ..._
    
    when it should look like
    
    _... ,
    'name',
    '', 0, 1, ..._
    
    . I can hand correct the INSERT statement to work.

    None the less the UUID field is still missing.

    If this doesn't work, what other options do I have to copy data from the other database?

    Thanks - Dave
  • lakshman
    lakshman
    67 Posts

    Re: Best way to copy tables between databases

    ‏2012-03-12T19:24:32Z  
    Alright, I'm about ready to give up on Data Studio as a development environment due to the number of issues I'm having.

    Anyway, the copy and paste option still fails for the following two reasons:

    1. The <filename>_data.sql file that's created in the project folder doesn't include the UUID field which contains our Identity data. All other fields are present.
    2. When I do deploy the data file using the editor, it fails to re-load all 177 records. 112 records fail to load due to an error: <pre class="jive-pre"> "Unexpected Text ", " encountered" </pre> . It appears there's text fields that have no data that are represented in the INSERT INTO statement as a space between each comma (,) delimiter. so for example, a snippet looks like <pre class="jive-pre"> _... , 'name', , 0, 1, ..._ </pre> when it should look like <pre class="jive-pre"> _... , 'name', '', 0, 1, ..._ </pre> . I can hand correct the INSERT statement to work.

    None the less the UUID field is still missing.

    If this doesn't work, what other options do I have to copy data from the other database?

    Thanks - Dave
    Hi Dave,

    We are sorry for issues which are blocking your progress and we completely understand the inconvenience. I am opening the defects for the issues and definitely, we will address these issues in upcoming release. Thanks a lot for reporting the problems.

    There are couple of options ...

    1. You can use the load commands or run any CLP or admin commands from Data Studio but it requires configuration of Database Administrative Service(DAS) or SSHD daemon. Please visit the following URL for configuration of setup.

    http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.base.install.doc/topics/c_dsa_remote.html

    It requires one time setup to run or use any admin tasks in Data Studio but If you think its too much time to setup. please use DB2 CLP itself on the server to run the load commands.

    Here is the DB2 link which provides the examples on load utility with different identity options to load the tables which has identity columns.

    http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0004601.htm
    2. Other option you can use Migrate objects and data tasks available in the Data Studio. Here is the URL
    which provides details information.

    http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.uom.ui.doc/topics/c_migratingdata_cont.html

    As of now I recommend you to use option-1 using the load commands on DB2 server by using CLP since you already have the DB2 setup and don't need go thru any extra setups. Thanks for using Data Studio and we really appreciate your feedback.

    Hope this helps.
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Best way to copy tables between databases

    ‏2012-03-14T12:52:46Z  
    • lakshman
    • ‏2012-03-12T19:24:32Z
    Hi Dave,

    We are sorry for issues which are blocking your progress and we completely understand the inconvenience. I am opening the defects for the issues and definitely, we will address these issues in upcoming release. Thanks a lot for reporting the problems.

    There are couple of options ...

    1. You can use the load commands or run any CLP or admin commands from Data Studio but it requires configuration of Database Administrative Service(DAS) or SSHD daemon. Please visit the following URL for configuration of setup.

    http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.base.install.doc/topics/c_dsa_remote.html

    It requires one time setup to run or use any admin tasks in Data Studio but If you think its too much time to setup. please use DB2 CLP itself on the server to run the load commands.

    Here is the DB2 link which provides the examples on load utility with different identity options to load the tables which has identity columns.

    http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0004601.htm
    2. Other option you can use Migrate objects and data tasks available in the Data Studio. Here is the URL
    which provides details information.

    http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.uom.ui.doc/topics/c_migratingdata_cont.html

    As of now I recommend you to use option-1 using the load commands on DB2 server by using CLP since you already have the DB2 setup and don't need go thru any extra setups. Thanks for using Data Studio and we really appreciate your feedback.

    Hope this helps.
    Thanks for the information. I'll work through the options.

    • Dave
  • XDT3_Harrie_de_Bie
    XDT3_Harrie_de_Bie
    1 Post

    Re: Best way to copy tables between databases

    ‏2012-05-03T11:49:57Z  
    I use db2move, works like charm.
    You can edit the *lst file to whatever you need.
  • sarah_07
    sarah_07
    1 Post

    Re: Best way to copy tables between databases

    ‏2012-05-31T23:57:05Z  
    Yes, I see that the best way to copy tables between databases is copy and paste. Thanks for the step by step procedure.
  • bluey01
    bluey01
    41 Posts

    Re: Best way to copy tables between databases

    ‏2012-06-01T00:08:45Z  
    • sarah_07
    • ‏2012-05-31T23:57:05Z
    Yes, I see that the best way to copy tables between databases is copy and paste. Thanks for the step by step procedure.
    You could also try using the IBMDatamovement tool - I hvae used this to move/migrate databases between different servers and DB2 versions