Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2008
    Posts
    3

    Unanswered: "Logical Logs + Alter table" Problems

    Hello!

    We have a system with 20 logical logs (4096 Kb size each one). we have a problem with a SQL statment (alter table) because these logical logs seems to be full and the database do a roll back. Is it possible that this kind of sql instruction, fill all the logical logs?

    Thanks in advance!

  2. #2
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    Yes, you are right. Even though you have 20 logical logs only 10 will be used because you are getting into what Informix calls a "long transaction" and the IDS engine will use the remaining 10 for the rollback so it can keep track of all database activity.

    I'll assume that the table you're altering is pretty large so you need to have more logical logs available.

  3. #3
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    or.. you can disable temporary the log (ontape -N or ondblog), execute the alter and active again the log..

    if you usually do backup of you IDS, you need execute a new backup level 0...

    Is recommend don't have any user connected to you database when you disable the log...
    ________________________________________
    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).
    ________________________________________

  4. #4
    Join Date
    Jan 2008
    Posts
    3
    Thx very much for your replies!

    Now... I have a new question (yeah, i'm new informix administration)

    What is the difference between:

    a) ontape -N
    b) ontape -U dbname

    I've read that is possible to change the status from buffered mode to unbuffered mode with b) option. Is it the same that option a)?

  5. #5
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311

    Cool A little explanation

    options you have:
    -A set the following database(s) to ansi logging
    -B set the following database(s) to buffered logging
    -N set the following database(s) to no logging
    -U set the following database(s) to unbuffered logging

    A little explanation:
    -A : DML and DDL works only with ANSI SQL standards, theoretically if you develop your application over this standard, you can migrate to ANY database easily

    -B : Works with IDS standards, transaction(*) is actived , flush of any modified data to disk is buffered (**)

    -N : Works with IDS standards, transaction(*) is NOT actived , flush of any modified data to disk are executed immediately

    -U : Works with IDS standards, transaction(*) is actived, the flush of any modified data to disk are executed immediately when the transaction is commited, don't matter if the buffer is full or not..

    (*) begin/commit/rollback
    (**) look for LOGBUFF and PHYSBUFF on your $ONCONFIG file ; see the command "onstat -l"
    ________________________________________
    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).
    ________________________________________

  6. #6
    Join Date
    Jan 2008
    Posts
    3

    Thumbs up

    Thank you very much for your answer! Your explanation is very clear and usefull! Thx again!

Posting Permissions

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