Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    14

    Unanswered: Informix query Issues

    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.

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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;
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Nov 2009
    Posts
    14
    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

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  5. #5
    Join Date
    Nov 2009
    Posts
    14
    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.

  6. #6
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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)
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  7. #7
    Join Date
    Nov 2009
    Posts
    14
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •