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?
我试图将我的一些表存档到同一服务器上的另一个数据库中。但是INSERT INTO ... SELECT ... FROM在构建时给出了一个错误(SQLSTATE = 42704)。该表存在于第二个数据库中。有人能帮忙吗?
6 个解决方案
#1
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.
从您的问题中不清楚使用的是什么版本的DB2。我认为它是Linux,Unix和Windows版本。您希望使用联合来链接这两个数据库。
Does the SELECT part of your query work from LS2DB001? It's worth trying to pin down which database you have the issue with.
查询的SELECT部分是否可以在LS2DB001中运行?值得尝试确定您遇到问题的数据库。
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.
假设问题出在LS2DB001上,如果已定义联合链接的用户对查询中的基表具有权限,请检查它们是否具有系统目录表的权限。如果没有,他们将无法解析并验证您是否可以运行查询。
#2
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)
我们破解了!如果使用以下脚本,那么它可以工作。 LOAD无需在复制的批次行之间进行COMMIT即可运行。 ('事务日志已满......'错误问题也解决了)
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;
连接LS2DB001;导出到IXF消息的“C:\ temp \ TIN_TRIGGER_OUT.IXF”“C:\ temp \ TIN_TRIGGER_OUT.EXM”SELECT * FROM LS2USER.TIN_TRIGGER_OUT;连接重置;连接到LQIFCOLD;从IXF消息的“C:\ temp \ TIN_TRIGGER_OUT.IXF”加载“C:\ temp \ TIN_TRIGGER_OUT.IMM”插入LS2USER.TIN_TRIGGER_OUT复制无索引模式AUTOSELECT;承诺;连接重置;
#3
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.
我在http://www.connx.com/products/connx/Connx%208.6%20UserGuide/CONNXCDD32D/DB2_SQL_States.htm上找到了这个:42704未定义的对象或约束名称。修改SQL语法并重试。
For more help try to be more specific, eg paste the full sql statement, the table scheme etc.
有关更多帮助,请尝试更具体,例如粘贴完整的sql语句,表格方案等。
#4
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.
将结果复制为文本脚本并在其他DB中执行。
#5
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.
执行此操作的最佳方法是创建自定义脚本。根据表的大小(记录的数量),您可以将所有数据选择到内存中,然后将它们翻转,将它们插入到您首先创建的表的副本中,或者可以将数据导出为一个csv文件或一些其他基于文本的文件,然后翻转它以将数据插入另一个表。
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
注意:某些db2数据库(例如iSeries上的数据库)实际上没有“数据库”,它们具有库。使用正确的用户配置文件,您可以同时访问两个库,将表连接在一起或执行操作
create table library/newFilename as
(select * from originallibrary/originalfilename) with data
But this only applies to the iSeries I believe.
但这仅适用于我认为的iSeries。
#6
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?
a)在您的FROM数据库中,以您用于联合链接的用户身份进行连接,您的选择是否正常工作?
b) In your TO db, using the link, does the select work?
b)在你的TO db中,使用链接,选择是否有效?
c) In your TO db, using the link via a stored proc, does the select work?
c)在你的TO db中,通过存储过程使用链接,选择是否有效?
d) In your TO db, using an INSERT...values(x,y,z), can you insert into the table?
d)在你的TO db中,使用INSERT ...值(x,y,z),你可以插入表中吗?
e) In your TO db, via a stored proc, using INSERT...values(x,y,z), can you insert?
e)在你的TO db中,通过存储过程,使用INSERT ... values(x,y,z),你能插入吗?
Without more information, this is the best line of attack I can suggest.
没有更多信息,这是我可以建议的最好的攻击线。
#1
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.
从您的问题中不清楚使用的是什么版本的DB2。我认为它是Linux,Unix和Windows版本。您希望使用联合来链接这两个数据库。
Does the SELECT part of your query work from LS2DB001? It's worth trying to pin down which database you have the issue with.
查询的SELECT部分是否可以在LS2DB001中运行?值得尝试确定您遇到问题的数据库。
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.
假设问题出在LS2DB001上,如果已定义联合链接的用户对查询中的基表具有权限,请检查它们是否具有系统目录表的权限。如果没有,他们将无法解析并验证您是否可以运行查询。
#2
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)
我们破解了!如果使用以下脚本,那么它可以工作。 LOAD无需在复制的批次行之间进行COMMIT即可运行。 ('事务日志已满......'错误问题也解决了)
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;
连接LS2DB001;导出到IXF消息的“C:\ temp \ TIN_TRIGGER_OUT.IXF”“C:\ temp \ TIN_TRIGGER_OUT.EXM”SELECT * FROM LS2USER.TIN_TRIGGER_OUT;连接重置;连接到LQIFCOLD;从IXF消息的“C:\ temp \ TIN_TRIGGER_OUT.IXF”加载“C:\ temp \ TIN_TRIGGER_OUT.IMM”插入LS2USER.TIN_TRIGGER_OUT复制无索引模式AUTOSELECT;承诺;连接重置;
#3
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.
我在http://www.connx.com/products/connx/Connx%208.6%20UserGuide/CONNXCDD32D/DB2_SQL_States.htm上找到了这个:42704未定义的对象或约束名称。修改SQL语法并重试。
For more help try to be more specific, eg paste the full sql statement, the table scheme etc.
有关更多帮助,请尝试更具体,例如粘贴完整的sql语句,表格方案等。
#4
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.
将结果复制为文本脚本并在其他DB中执行。
#5
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.
执行此操作的最佳方法是创建自定义脚本。根据表的大小(记录的数量),您可以将所有数据选择到内存中,然后将它们翻转,将它们插入到您首先创建的表的副本中,或者可以将数据导出为一个csv文件或一些其他基于文本的文件,然后翻转它以将数据插入另一个表。
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
注意:某些db2数据库(例如iSeries上的数据库)实际上没有“数据库”,它们具有库。使用正确的用户配置文件,您可以同时访问两个库,将表连接在一起或执行操作
create table library/newFilename as
(select * from originallibrary/originalfilename) with data
But this only applies to the iSeries I believe.
但这仅适用于我认为的iSeries。
#6
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?
a)在您的FROM数据库中,以您用于联合链接的用户身份进行连接,您的选择是否正常工作?
b) In your TO db, using the link, does the select work?
b)在你的TO db中,使用链接,选择是否有效?
c) In your TO db, using the link via a stored proc, does the select work?
c)在你的TO db中,通过存储过程使用链接,选择是否有效?
d) In your TO db, using an INSERT...values(x,y,z), can you insert into the table?
d)在你的TO db中,使用INSERT ...值(x,y,z),你可以插入表中吗?
e) In your TO db, via a stored proc, using INSERT...values(x,y,z), can you insert?
e)在你的TO db中,通过存储过程,使用INSERT ... values(x,y,z),你能插入吗?
Without more information, this is the best line of attack I can suggest.
没有更多信息,这是我可以建议的最好的攻击线。