Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Scotland
    Posts
    17

    Unanswered: Ingres 2.0 set nologging

    Hi,

    My problem is this:

    I'm trying to set nologging for a large database load. I can set nologging via sql but it is only valid for the current session.

    I have tried setting it up as an enviroment variable in ingprenv
    ingsetenv ING_SET_DBNAME "set nologging"

    When I issue this I get an SQL error -34000. Set nologging can only be issued by the dbowner.

    Does anyone know how to set up the enviroment variable as the dbowner?

    Thanks in advance for any help

    Cheers

    S.

  2. #2
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    I've only ever done this inside the current session. I don't believe that you can set this up as an environmental variable.

    There are methods to reduce the amount of logging that may help like having the target table as heap, with no secondary indexes on it.
    Regards
    Dbabren

  3. #3
    Join Date
    Jan 2003
    Location
    Scotland
    Posts
    17
    Hi,

    I managed to get it set. I had to log in as the db owner and add ingres super-user to it's group.
    I then issued the following command
    ingsetenv ING_SET_DBNAME "set nologging"

    I then ran a job and it ran with no logging. After the job finishes Ingres issues a set logging statement automatically.

    Thanks for your advice

    S.
    Last edited by sypher; 09-10-03 at 09:04.

  4. #4
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    Hi back -

    Another way to speed this up might be to turn of journaling for the database. You will need to take a checkpoint after your load to turn journaling back on again, but during the load this will remove the i/o required to writ to the journal files.

    Hope this helps
    Regards
    Dbabren

  5. #5
    Join Date
    Jan 2003
    Location
    Scotland
    Posts
    17
    Hi,

    I know what you mean. It wasn't really a speed issue I had. I was running an accounts restructure job which has half a million transactions. The transaction log kept overflowing.
    The only way to solve it was set nologging, which I hate 'cause its so risky. I could also break the job into smaller batches, but the guys running the job want it done in one go.
    Cheers for your advice, I'll take notice of switching journalling off.

    Cheers

    S.

Posting Permissions

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