Informix query Issues

pbangalore · #1 (**permalink**) 11-18-09, 12:58

I should admit that i am a beginner in informix, but however i have worked in other databases such as oracle, sybase and sql server.

I am trying to delete all the records from one table ent_attr, while it exists in an other table ca_retainco_entities. I tried so many ways like

1) delete ea
from ent_attr ea,ca_retainco_entities ca
where ea.entity_id = ca.ent_id

The above sql stmt gives me an syntax error at position 0 on line 2

2) delete from ent_attr
where entity_id in (select ent_id from ca_retainco_entities)

With the above sql stmt it says
240: Could not delete a row
134: ISAM erroL no more locks
Error in line 2,position 59

We even increased the lock size to a huge number

I am not sure how to tackle this, help is greatly appreciated on both of the queries.

ceinma · #2 (**permalink**) 11-18-09, 19:42

item 1) informix don't support this syntax.

item 2) you can increase your LOCKS on ONCONFIG , or just lock the entire table.

Code:

begin work;
lock table xyz in exclusive mode; 
delete from xyz where ....
;
commit work;

pbangalore · #3 (**permalink**) 11-19-09, 13:12

I tried by locking the entire table as you suggested, with locks at 1,000,000 and now i am getting the error 458: Long transaction aborted

ceinma · #4 (**permalink**) 11-19-09, 14:47

ok, long transaction occur because your logical log don't support the amount of data deleted... you have 3 solutions:
- change your database to nolog :
ontape -s -L0 -N <your_database>
or
ondblog nolog <you_database>
Be careful , doing this, no transaction is allowed , if have others sessions connected you can have some trouble.

2) change your table to RAW (nolog), delete and back to standard:
alter table <xyz> type(raw);
delete...
alter table <xyz> type(standard);

3) add more logical logs
this is more complicade and need to change the database configuration... talk with your DBA.

pbangalore · #5 (**permalink**) 11-19-09, 15:01

I will defenetly try as you suggested.

Also i was wondering if there is any way we could delete like 100,000 at a time. Go back and get the next 100,000 and delete it.

ceinma · #6 (**permalink**) 11-20-09, 05:36

yes, but for this you will need write some code.
You can write a SPL (stored procedure) , write in your desire language or use some utilities like this: International Informix Users Group (ind this same page, looking for "delete", and you will found others scritpts.

There many ways to write this, here is the an example of more simple code:
this is a pseudo-code, based on SPL language

Code:

vCounter=0
foreach with hold
  select rowid into vR from table_xyz where <filters>
  if vCounter = 0 then 
     begin work;
  end if
  delete from table_xyz where rowid = vR
  vCounter=vCounter+1
  if vCounter >= 100 then 
    commit work;
    vCounter =0
  end if     
end foreach

This code won't perform very fast, there others ways to do this faster , sending all ROWIDs in the same statement ....
For information about ROWID , read the manual: Using Rowids (IDS)

pbangalore · #7 (**permalink**) 11-23-09, 11:20

I tried now using the procedure approach. Now i am getting the error at the very end of stored procedure(Last line End Procedure)
CREATE PROCEDURE spinco_process()
BEGIN
/*I have code here in between*/
END
END PROCEDURE;

I am not sure what syntax error in the last line is?
Also how would you execute the procedure through execute?

Help is really appreciated

Internet Services

Web Development

Digital Marketing

Consumer Tech