Help with sql error

whenever I fire INSERT INTO rmtadmin.ISMCAMPCOMMCH (COMMSTATUS, CAMPCODE, COMMCODE, COMMDATE, customer_id) SELECT 'P' ,'989898', '141414', '06/01/1999',customer_id FROM sm.HLD22FL WHERE sm.HLD22FL.customer_id NOT IN (SELECT customer_id FROM TMPCH51444) at the database it comes back with 'long transaction aborted'. Any ideas why? Or what I can do to make it work?

Simon Leigh wrote in message <7jofv2$kkn$1@neptunium.btinternet.com>... >whenever I fire >INSERT INTO rmtadmin.ISMCAMPCOMMCH (COMMSTATUS, CAMPCODE, COMMCODE, >COMMDATE, customer_id) SELECT 'P' ,'989898', '141414', '06/01/1999',>customer_id FROM sm.HLD22FL WHERE sm.HLD22FL.customer_id NOT IN (SELECT >customer_id FROM TMPCH51444) >at the database it comes back with 'long transaction aborted'. > >Any ideas why? Or what I can do to make it work? > > > Long transaction means that the logs got 'too full', so the first suggestion would be to increase the log space. That depends on how many inserts you are making (maybe do a select count(*) from sm.HLD22FL (and rest of statement) to see how many inserts you are causing. Also check the long transaction high water mark in the config file to see what percentage of logs full causes the abort. Can you run this transaction in exclusive mode? Is there other activity on the system? If there is other update activity, maybe it is the time it is taking your transaction to run and not the number of updates that it is making (so that the critical number of logs fill up with other users stuff by the time your transaction bombs). If that is the case, do you have the proper indexes on the selected tables to cause index scans as opposed to sequential scans? Alternatively, break this down into a series of SQL statements with commits between them (maybe break by range of customer_id). Just a few random thoughts... Doug Agnew

Simon Leigh wrote: > > whenever I fire > INSERT INTO rmtadmin.ISMCAMPCOMMCH (COMMSTATUS, CAMPCODE, COMMCODE, > COMMDATE, customer_id) SELECT 'P' ,'989898', '141414', '06/01/1999',> customer_id FROM sm.HLD22FL WHERE sm.HLD22FL.customer_id NOT IN (SELECT > customer_id FROM TMPCH51444) > at the database it comes back with 'long transaction aborted'. > > Any ideas why? Or what I can do to make it work? You are filling your logical logs so the obvious solution is to increase the number of logs. If that is not practical, you have to limit the size of a transaction. Two suggestions: 1) Add an additional filter to the WHERE clause to limit the size of the transaction by copying only a subset of the data at a time. Then you can run additional versions of this insert with modified selects to get all the data you desire. 2) Get my utility dbcopy.ec, which was written with just this problem in mind. Dbcopy.ec is part of the package I submitted to the IIUG Software Repository named utils2_ak. It is FASTER than INSERT INTO... SELECT ... FROM... and can copy from one server to another between databases, even with different logging modes. The commandline to dowhat your INSERT does would be: dbcopy -f 1000 -d sm -D rmtadmin -t hld22fl -T ismcampcommch \\ -s 'SELECT "P" commstatus, "989898" campcode, "141414" commcode, "06/01/1999" commdate, customer_id FROM %s WHERE customer_id NOT IN (SELECT customer_id FROM tmpch51444)' Art S. Kagel