copying a table from one database to another

Question

I am trying to archive some of my tables into another database on the same server. However the INSERT INTO...SELECT...FROM gives me an error (SQLSTATE=42704) on build. The table exists in the second database. Can anyone help with this?

Answer 1

I found this on http://www.connx.com/products/connx/Connx%208.6%20UserGuide/CONNXCDD32D/DB2_SQL_States.htm: 42704 Undefined object or constraint name. Revise SQL syntax and retry.

For more help try to be more specific, eg paste the full sql statement, the table scheme etc.

Answer 2

You can do

 Select 'insert into tblxxxx (blabla,blabal) values(' + fld1 + ',' + fld2 + ',' ...... + ')'

From tblxxxxxx

copy the result as a text script and execute it in the other DB.

Answer 3

The best way to do this would be to create a custom script. Depending on the size of the tables (how many records) you could either do a select of all of the data into memory and then roll over them inserting them into a copy of the table you create first, or you could export the data out as a csv file or some other text based file and then roll over that to insert the data into the other table.

If you do not have some sort of formal backup procedures that could do this already, this would be your best bet.

Note: some db2 databases, such as those on an iSeries do not actually have "databases", they have libraries. With the right user profile you can access two libraries at the same time, joining tables from them together or doing a

create table library/newFilename as 
(select * from originallibrary/originalfilename) with data

But this only applies to the iSeries I believe.

Answer 4

None of the above suggestions have worked. Has anyone actually done this successfully?

Answer 5

Hi,

It's not clear from your question what version of DB2 is being used. I'll presume that it's the Linux, Unix & Windows version. You look to be using federation to link the two databases.

Does the SELECT part of your query work from LS2DB001? It's worth trying to pin down which database you have the issue with.

Presuming that the problem is on LS2DB001, if the user you have defined the federated link with has permissions on the base tables in the query, check also that they have permissions on the system catalog tables. If not, they would not be able to parse and validate that you can run the query.

Answer 6

Hi Anita,

I'm writing this response as another answer so I have more space.

I can only suggest breaking the steps down to their components, and working through to see where the error is occuring. Again, I'm assuming you're using federation:

a) In your FROM db, connecting as the user you're using for the federated link, does your select work?

b) In your TO db, using the link, does the select work?

c) In your TO db, using the link via a stored proc, does the select work?

d) In your TO db, using an INSERT...values(x,y,z), can you insert into the table?

e) In your TO db, via a stored proc, using INSERT...values(x,y,z), can you insert?

Without more information, this is the best line of attack I can suggest.

Answer 7

We've cracked it! If the following script is used then it works. The LOAD works without having to COMMIT in between batches of rows copied. ('Transaction Log full...' error problem is also solved)

CONNECT TO LS2DB001; EXPORT TO "C:\temp\TIN_TRIGGER_OUT.IXF" OF IXF MESSAGES "C:\temp\TIN_TRIGGER_OUT.EXM" SELECT * FROM LS2USER.TIN_TRIGGER_OUT; CONNECT RESET; CONNECT TO LQIFCOLD; LOAD FROM "C:\temp\TIN_TRIGGER_OUT.IXF" OF IXF MESSAGES "C:\temp\TIN_TRIGGER_OUT.IMM" INSERT INTO LS2USER.TIN_TRIGGER_OUT COPY NO INDEXING MODE AUTOSELECT; COMMIT; CONNECT RESET;