From Ingres Community Wiki

Description

A new flag for copydb and unloadb, -nologging, which when specified generates a set nologging statement in the copy.in script. This can be useful for large data loads where the copy would not qualify for bulk loading and the transaction logfile is not big enough for the entire transaction.

Since one needs to be the DBA of the database in order to have the permission to run set nologging, one must be the DBA in order to run copydb with -nologging. One needs to be the DBA (or a user with Security Administrator privilege) in order to run unloaddb with or without this flag, however with -nologging a set session authorization {DBA} statement will precede the set nologging statement to set the effective user.

This enhancement was implemented as part of the UK IUA Code Sprint 2009.

Ingres Enhancement Number

• Trac # 386
• SIR number 122202

Examples

Copydb

$ copydb -nologging mydb
INGRES COPYDB Copyright 2010 Ingres Corporation
Unload directory is '/home/ingres/tmp'.
Reload directory is '/home/ingres/tmp'.
There are 0 sequences owned by user 'ingres'.
There is one table owned by user 'ingres'.
There are 0 views owned by user 'ingres'.
There are 0 events owned by user 'ingres'.
There are 0 procedures owned by user 'ingres'.
There are 0 rules owned by user 'ingres'.

COPYDB has created the scripts copy.out and copy.in. From an Ingres
prompt, run sql mydb < copy.out to copy the data.

$ more copy.in

/* SQL script created by COPYDB, version II 10.0.0 (int.lnx/00)NPTL. */

\sql
set autocommit on
\p\g
set nojournaling
\p\g
\sql
set session with privileges=all
\p\g
\nocontinue
set nologging
\p\g
\continue

        /* TABLES */
\nocontinue
create table test(

Note that there is a \nocontinue before the set nologging and \continue afterwards. This is because if the set nologging fails the script should terminate. However it's possible for there to be other object types created before the tables (e.g. sequences) and without the -nologging flag terminal monitor would be in continue mode at this point so the \continue is there to make this consistent. If there are no such objects it may look a little odd that there is a \continue immediately followed by a comment then a \nocontinue (as in the above example)

Unloaddb

$ unloaddb -nologging mydb
INGRES UNLOADDB Copyright 2010 Ingres Corporation
Unload directory is '/home/ingres/tmp'.
Reload directory is '/home/ingres/tmp'.
There are 0 sequences in the database.
There are 53 tables in the database.
There are 0 views in the database.
There are 0 events in the database.
There are 0 procedures in the database.
There are 0 rules in the database.

UNLOADDB has created the scripts 'unload.ing' and 'reload.ing'.

Don't forget to run unload.ing before destroying the database
and to run 'CKPDB +j mydb' to enable journaling of the system catalogs.

$ more copy.in

/* SQL script created by UNLOADDB, version II 10.0.0 (int.lnx/00)NPTL. */

\sql
set autocommit on
\p\g
set nojournaling
\p\g
\sql
set session with privileges=all
\p\g
\include /devsrc/main/m2/install/build/ingres/files/iiud.scr
\include /devsrc/main/m2/install/build/ingres/files/iiud65.scr
\sql
set session authorization "ingres"
\p\g
\nocontinue
set nologging
\p\g
\continue

        /* SEQUENCES */
set session authorization ingres
\p\g
create sequence pmseq as integer8
    start with 1 increment by 1
    minvalue 1 maxvalue 9223372036854775807
    cache 20 no cycle no order
\p\g

        /* TABLES */
\nocontinue

Note that in the case of unloaddb a set session authorization command is used to set the effective user to the DBA of the database since one needs to be the DBA to issue a set nologging command.

Copydb Without DBA Permission

$ copydb -nologging mydb
INGRES COPYDB Copyright 2010 Ingres Corporation
E_CD0027 You (testenv) are not the dba for this database.
    You must be the dba of the database to run with the nologging option.