While the client program cgclt is be used to perform any database oriented queries,
the admin program cgadm is more useful for any database administration tasks.
Information status retrieval but also standard admin tasks like managing tablesets or threads, the admin program
is used for any kind of this.
cgadm is a command based utility which supports GNU readline library editing and history features.
Please note
For fully distributed tableset environments as provided with version Cego 2.0 and higher, it is highly recommended to
use the cgadm admin program to manage tableset, since the program synchronizes the corresponding tableset information
for all affected nodes ( mediator, primary and secondary ).
All supported features of cgadm are explained in the following section.
7.1 Connecting to the database
To invoke cgadm, a running cego database daemon is required.
See section Daemon mode how to start up the
cego database daemon.After the cego daemon has been started successful, the cgadm can be invoked
using appropriate connection parameters. For example, this may be
$ cgadm --server=localhost --port=2000 --user=cgadm/cgadm
CGADM >
|
This connects the admin program the the database host localhost to port 2000 with the admin user cgadm.
7.2 Retrieving database informtion
After establishing a connection to the cego database server, several commands are available to get information
about the corresponding database system or about managed tablesets.
7.2.1 Pool information
Information about the current buffer pool state can be retrieved using the show pool command
CGADM > show pool;
+---------------------+----------------+
| POOLINFO | POOLINFO |
| PARAMETER | VALUE |
+---------------------+----------------+
| Page Size | 16384 |
| Total Pages | 3000 |
| Used Pages | 61 |
| Free Pages | 2939 |
| Dirty Pages | 102 |
| Fixed Pages | 0 |
| Persistent Pages | 30 |
| No Sync Pages | 0 |
|-------------------- |--------------- |
| Stat Start | 18:21:40 |
| Cur Fix Count | 67057 |
| Max Fix Count | 100000 |
| Disk Reads | 27 |
| Disk Writes | 0 |
| Read Delay | 0.026 msec |
| Write Delay | 0.000 msec |
+---------------------+----------------+
PoolInfo
ok ( 0.002 s )
|
A more detailed information about the bufferpool entries can be listed uding the list pool command
CGADM > list pool;
+-----------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+
| PEL | PEL | PEL | PEL | PEL | PEL | PEL | PEL |
| POS | OCCSTATE | ISDIRTY | NUMFIXES | TABSETID | FILEID | PAGEID | FIXSTAT |
+-----------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+
| 0 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 1 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 2 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 3 | PERSISTENT | n | 0 | 2 | 2 | 0 | 25 |
| 4 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 5 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 6 | PERSISTENT | n | 0 | 2 | 2 | 1 | 25 |
| 7 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 8 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 9 | PERSISTENT | n | 0 | 2 | 2 | 2 | 25 |
| 10 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 11 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 12 | PERSISTENT | n | 0 | 2 | 2 | 3 | 25 |
| 13 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 14 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 15 | PERSISTENT | n | 0 | 2 | 2 | 4 | 25 |
| 16 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 17 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 18 | PERSISTENT | n | 0 | 2 | 2 | 5 | 25 |
| 19 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 20 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 21 | PERSISTENT | n | 0 | 2 | 2 | 6 | 25 |
| 22 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 23 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 24 | PERSISTENT | n | 0 | 2 | 2 | 7 | 25 |
| 25 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 26 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 27 | PERSISTENT | n | 0 | 2 | 2 | 8 | 25 |
| 28 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 29 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
| 30 | PERSISTENT | n | 0 | 2 | 2 | 9 | 25 |
| 31 | NOTOCCUPIED | n | 0 | 0 | 0 | 0 | 0 |
...
|
7.2.2 Lock information
There are several types of locks, which can be monitored.
- Record locks ( rec )
- Order Record locks ( orec )
- RB Record locks ( rbrec )
- System Record locks ( sysrec )
- Bufferpool locks ( bp )
- Datapage locks ( dp )
- Index page locks ( ix )
- System page locks ( sp )
- RB page locks ( rb )
- Datafile locks ( df )
- Threadpool locks ( tp )
- Tableset locks ( ts )
As a sample, we list the current state of the threadpool and bufferpool locks.
CGADM > list tp lock;
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| LOCKINFO | LOCKINFO | LOCKINFO | LOCKINFO | LOCKINFO | LOCKINFO |
| LOCKID | LOCKCOUNT | RD HITS | RD DELAY | WR HITS | WR DELAY |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| ADMQUEUE | 4 | 0 | 0 | 40512 | 0 |
| DBQUEUE | 10 | 0 | 0 | 90703 | 0 |
| LOGQUEUE | 3 | 0 | 0 | 1420 | 0 |
| DBM | 0 | 7 | 0 | 80393 | 0 |
| XML | 0 | 1900 | 0 | 1641 | 0 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
LockInfo
ok ( 0.003 s )
CGADM > list bp lock;
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| LOCKINFO | LOCKINFO | LOCKINFO | LOCKINFO | LOCKINFO | LOCKINFO |
| LOCKID | LOCKCOUNT | RD HITS | RD DELAY | WR HITS | WR DELAY |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| POOLLOCK-0 | 0 | 0 | 0 | 132 | 46 |
| POOLLOCK-1 | 0 | 0 | 0 | 179 | 62 |
| POOLLOCK-2 | 0 | 0 | 0 | 130 | 45 |
| POOLLOCK-3 | 0 | 0 | 0 | 177 | 62 |
| POOLLOCK-4 | 0 | 0 | 0 | 137 | 47 |
| POOLLOCK-5 | 0 | 0 | 0 | 144 | 49 |
| POOLLOCK-6 | 0 | 0 | 0 | 193 | 66 |
| POOLLOCK-7 | 0 | 0 | 0 | 137 | 47 |
| POOLLOCK-8 | 0 | 0 | 0 | 137 | 47 |
| POOLLOCK-9 | 0 | 0 | 0 | 185 | 63 |
| POOLLOCK-10 | 0 | 0 | 0 | 137 | 47 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
LockInfo
ok ( 0.004 s )
|
An aggregated view for the described lock groups can be retrieved using the lockstat command
CGADM > lockstat;
+---------------------+-----------+---------------------+---------------------+---------------------+---------------------+---------------------+
| LOCKSTAT | LOCKSTAT | LOCKSTAT | LOCKSTAT | LOCKSTAT | LOCKSTAT | LOCKSTAT |
| LOCKID | NUMLOCK | LOCKCOUNT | RD HITS | RD DELAY | WR HITS | WR DELAY |
+---------------------+-----------+---------------------+---------------------+---------------------+---------------------+---------------------+
| ADMQUEUE | 1 | 4 | 0 | 0 | 29526 | 1079842958 |
| DBQUEUE | 1 | 10 | 0 | 0 | 86099 | 2602100737 |
| LOGQUEUE | 1 | 3 | 0 | 0 | 466 | 809861541 |
| DBM | 1 | 0 | 1 | 0 | 73649 | 9019546 |
| XML | 1 | 0 | 0 | 0 | 30641 | 21600 |
| RECLOCK | 1000 | 0 | 7400 | 1834 | 0 | 0 |
| ORDERRECLOCK | 31 | 0 | 1 | 0 | 0 | 0 |
| RBRECLOCK | 31 | 0 | 0 | 0 | 0 | 0 |
| SYSRECLOCK | 31 | 0 | 0 | 0 | 14800 | 3800 |
| SYSPAGELOCK | 41 | 0 | 126188 | 32082 | 41 | 19 |
| DATAPAGELOCK | 41 | 0 | 30 | 0 | 14678 | 3670 |
| IDXPAGELOCKGROUP | 41 | 0 | 0 | 0 | 0 | 0 |
| RBPAGELOCK | 41 | 0 | 5 | 2 | 0 | 0 |
| DATAFILELOCK | 11 | 0 | 6 | 9 | 454 | 127 |
| POOLLOCK | 11 | 0 | 0 | 0 | 538258 | 131530 |
+---------------------+-----------+---------------------+---------------------+---------------------+---------------------+---------------------+
LockStat
ok ( 0.025 s )
|
7.2.3 Thread administration
The cego daemon manages three thread pools for database, administration and log requests.
An overwiew about the the general status of the pools can be retrieved with
CGADM > threadinfo;
+---------------------+------------+------------+
| THREADINFO | THREADINFO | THREADINFO |
| TYPE | TOTAL | ACTIVE |
+---------------------+------------+------------+
| DB Thread | 10 | 0 |
| Admin Thread | 5 | 1 |
| Log Thread | 3 | 0 |
+---------------------+------------+------------+
ThreadInfo
|
For each of the three thread pools ( db, adm and log ), the current state of the
pool can be retrieved. The current working stat of each thread is indicated and
the last operation, which has been performed by the thread.
CGADM > threadinfo db;
+------------+------------+
| THREADINFO | THREADINFO |
| THREADID | STATUS |
+------------+------------+
| 0 | READY |
| 1 | READY |
| 2 | READY |
| 3 | READY |
| 4 | READY |
| 5 | READY |
| 6 | READY |
| 7 | READY |
| 8 | READY |
| 9 | READY |
+------------+------------+
DBThreadinfo
ok ( 0.002 s )
|
CGADM > threadinfo db 1;
+----------------------+
| THREADINFO |
| LASTACTION |
+----------------------+
| select * from mytab; |
+----------------------+
DBThreadinfo
ok ( 0.002 s )
|
Active running queries can be aborted using the abort command
CGADM > abort db thread 1;
Thread aborted
ok ( 0.002 s )
|
7.3 User administration
For any database or adminstration task, a user has to log on to cego using an account with appropriate permissions.
To enable a user for any task, a specific role has to assgned to a user. User roles itself have to be created and managed.
The administration of roles and users is described in the following.
7.3.1 Role management
Roles are created using the create role statement. Any identifier can be used
for the role name.
CGADM > list role;
+----------------+
| ROLELIST |
| ROLE |
+----------------+
| thisrole |
| thatrole |
+----------------+
Role List
ok ( 0.001 s )
|
CGADM > create role myrole;
+-----------+---------------------+---------------------------------------------------+
| ADM | ADM | ADM |
| ROLE | HOSTNAME | MSG |
+-----------+---------------------+---------------------------------------------------+
| mediator | dude.local | Role created |
+-----------+---------------------+---------------------------------------------------+
Admin action finished
ok ( 0.001 s )
|
Now a permission entry can be added to the new created role.
CGADM > add permission myperm with tableset=TS1, filter='ALL', right=READ to role myrole;
+-----------+---------------------+---------------------------------------------------+
| ADM | ADM | ADM |
| ROLE | HOSTNAME | MSG |
+-----------+---------------------+---------------------------------------------------+
| mediator | dude.local | Permission added |
+-----------+---------------------+---------------------------------------------------+
Admin action finished
ok ( 0.002 s )
|
This entry adds read permission for all objects located in tableset TS1.
CGADM > show role myrole;
+----------------+----------------+----------------+----------------+
| ROLEINFO | ROLEINFO | ROLEINFO | ROLEINFO |
| PERMID | TABLESET | FILTER | PERM |
+----------------+----------------+----------------+----------------+
| myperm | TS1 | ALL | READ |
+----------------+----------------+----------------+----------------+
Admin action finished
ok ( 0.001 s )
|
CGADM > remove permission myperm from role myrole;
+-----------+---------------------+---------------------------------------------------+
| ADM | ADM | ADM |
| ROLE | HOSTNAME | MSG |
+-----------+---------------------+---------------------------------------------------+
| mediator | dude.local | Permission removed |
+-----------+---------------------+---------------------------------------------------+
Admin action finished
ok ( 0.001 s )
|
If a role is no more used, it can be dropped.
CGADM > drop role myrole;
+-----------+---------------------+---------------------------------------------------+
| ADM | ADM | ADM |
| ROLE | HOSTNAME | MSG |
+-----------+---------------------+---------------------------------------------------+
| mediator | dude.local | Role dropped |
+-----------+---------------------+---------------------------------------------------+
Admin action finished
|
7.3.2 User management
Users are created using the add user statement.
CGADM > add user erwin identified by 'erwin';
+-----------+---------------------+---------------------------------------------------+
| ADM | ADM | ADM |
| ROLE | HOSTNAME | MSG |
+-----------+---------------------+---------------------------------------------------+
| mediator | dude.local | Added user locally |
+-----------+---------------------+---------------------------------------------------+
Admin action finished
ok ( 0.001 s )
|
CGADM > assign role myrole to erwin;
+-----------+---------------------+---------------------------------------------------+
| ADM | ADM | ADM |
| ROLE | HOSTNAME | MSG |
+-----------+---------------------+---------------------------------------------------+
| mediator | dude.local | Role added |
+-----------+---------------------+---------------------------------------------------+
Admin action finished
ok ( 0.001 s )
|
CGADM > list user;
+-----------+-----------+-----------+-----------+-----------+
| USERINFO | USERINFO | USERINFO | USERINFO | USERINFO |
| NAME | ROLE | TRACE | NUMREQ | NUMQUERY |
+-----------+-----------+-----------+-----------+-----------+
| cgadm | admin | | | |
| lemke | ALL | | | |
| erwin | myrole | | | |
+-----------+-----------+-----------+-----------+-----------+
User Info
ok ( 0.001 s )
|
CGADM > create role thatrole;
+-----------+---------------------+---------------------------------------------------+
| ADM | ADM | ADM |
| ROLE | HOSTNAME | MSG |
+-----------+---------------------+---------------------------------------------------+
| mediator | dude.local | Role created |
+-----------+---------------------+---------------------------------------------------+
Admin action finished
ok ( 0.001 s )
|
CGADM > assign role thatrole to erwin;
+-----------+---------------------+---------------------------------------------------+
| ADM | ADM | ADM |
| ROLE | HOSTNAME | MSG |
+-----------+---------------------+---------------------------------------------------+
| mediator | dude.local | Role added |
+-----------+---------------------+---------------------------------------------------+
Admin action finished
ok ( 0.001 s )
|
CGADM > list user;
+-----------+-----------------+-----------+-----------+-----------+
| USERINFO | USERINFO | USERINFO | USERINFO | USERINFO |
| NAME | ROLE | TRACE | NUMREQ | NUMQUERY |
+-----------+-----------------+-----------+-----------+-----------+
| cgadm | admin | | | |
| lemke | ALL | | | |
| erwin | thatrole,myrole | | | |
+-----------+-----------------+-----------+-----------+-----------+
User Info
ok ( 0.001 s )
|
CGADM > remove role myrole from erwin;
+-----------+---------------------+---------------------------------------------------+
| ADM | ADM | ADM |
| ROLE | HOSTNAME | MSG |
+-----------+---------------------+---------------------------------------------------+
| mediator | dude.local | Role removed |
+-----------+---------------------+---------------------------------------------------+
Admin action finished
ok ( 0.001 s )
|
CGADM > passwd user erwin identified by 'hubert';
+-----------+---------------------+---------------------------------------------------+
| ADM | ADM | ADM |
| ROLE | HOSTNAME | MSG |
+-----------+---------------------+---------------------------------------------------+
| mediator | dude.local | Passwd changed |
+-----------+---------------------+---------------------------------------------------+
Admin action finished
ok ( 0.001 s )
|
7.4 Retrieving tableset informtion
An overview of all known tablesets can be retrieved using the list tableset command.
CGADM > list tableset;
+-----------+----------+-------------+-------------+-----------+-----------+
| TSL | TSL | TSL | TSL | TSL | TSL |
| NAME | RUNSTATE | SYNCSTATE | MEDIATOR | PRIMARY | SECONDARY |
+-----------+----------+-------------+-------------+-----------+-----------+
| TS1 | ONLINE | SYNCHED | dude.local |dude.local |dude.local |
+-----------+----------+-------------+-------------+-----------+-----------+
Admin action finished
ok ( 0.001 s )
|
In the sample above, one tableset is registered for the corresponding node and it is currently in offline status.
A more detailed view for a specific tableset can be retrieved, by using the info tableset command.
CGADM > info tableset TS1;
+---------------------+---------------------+
| TABLESETINFO | TABLESETINFO |
| PARAMETER | VALUE |
+---------------------+---------------------+
| RunState | ONLINE |
| SyncState | SYNCHED |
| Primary | dude.local |
| Secondary | dude.local |
| Mediator | dude.local |
| RootPath | ./db |
| Ticket | ./db/TS1ticket.xml |
|-------------------- |-------------------- |
| SystemPageTotal | 100 |
| SystemPageUsed | 15 |
| TempPageTotal | 3000 |
| TempPageUsed | 17 |
| AppPageTotal | 1000 |
| AppPageUsed | 16 |
|-------------------- |-------------------- |
| SortAreaSize | 10000000 |
| Tid | 0 |
| CLSN | 4516 |
| NLSN | 4517 |
| ArchMode | OFF |
+---------------------+---------------------+
Admin action finished
ok ( 0.003 s )
|
The contained objects of a tableset can be listed with the list object command
CGADM > list object for TS1;
+-----------+---------------------+
| OBJINFO | OBJINFO |
| Type | Name |
+-----------+---------------------+
| TABOBJ | t1 |
| TABOBJ | t2 |
+-----------+---------------------+
Admin action finished
ok ( 0.001 s )
|
7.4.1 Datafile information
Information about registered datafiles can be retrieved using the list datafile command.
CGADM > list datafile for TS1;
+---------------------+--------------+--------------+--------------+
| DATAFILEINFO | DATAFILEINFO | DATAFILEINFO | DATAFILEINFO |
| FILENAME | FILETYPE | SIZE | USED |
+---------------------+--------------+--------------+--------------+
| ./db/chkdb_TS1.sys | SYSFILE | 100 | 15 |
| ./db/chkdb_TS1.temp | TEMPFILE | 3000 | 17 |
| ./db/data01.dbf | APP | 1000 | 29 |
| ./db/data02.dbf | APP | 500 | 0 |
| ./db/sys02.dbf | SYS | 500 | 0 |
| ./db/temp02.dbf | TEMP | 500 | 0 |
+---------------------+--------------+--------------+--------------+
Admin action finished
ok ( 0.003 s )
|
Please note : The size information for any data-, system- or tempfile is available, just if the tableset has been started up.
7.4.2 Redo Log information
CGADM > list logfile for TS1;
+-------------------+-----------+---------------------+---------------------+---------------------+
| LOGFILE | LOGFILE | LOGFILE | LOGFILE | LOGFILE |
| NAME | STATUS | SIZE | OFFSET | USAGE |
+-------------------+-----------+---------------------+---------------------+---------------------+
| ./db/TS1redo0.log | ACTIVE | 1000000 | 4 | 0% |
| ./db/TS1redo1.log | FREE | 1000000 | 0 | 0% |
| ./db/TS1redo2.log | FREE | 1000000 | 0 | 0% |
+-------------------+-----------+---------------------+---------------------+---------------------+
Admin action finished
ok ( 0.004 s )
|
7.4.3 Archive Log information
CGADM > list archlog for TS1;
+-------------+-------------+
| ARCHLOGINFO | ARCHLOGINFO |
| ARCHID | ARCHPATH |
+-------------+-------------+
| AL1 | ./arch |
+-------------+-------------+
Admin action finished
ok ( 0.004 s )
|
7.4.4 Transaction information
If there are any open user transaction in a tableset, the corresponding transaction objects can be retrieved with cgadm
For each transaction a dedicated rollback table is created called rbcatlog<tid>. The transaction file stores the modified tuple information to perform either a commit or rollback for the transaction.
To view the open transaction for a tableset, the list transaction command is used
CGADM > list transaction for TS1;
+----------------+-----------+
| TAINFO | TAINFO |
| RBO | NUMOP |
+----------------+-----------+
| rbcatlog4711 | 28332 |
+----------------+-----------+
Admin action finished
|
The NUMOP attributes indicates the number of operations, which have been performed for the transaction.
Please note: Since an update operation is reduced to a delete and an insert operation, there are two operations for each tuple in the rollback table.
If during an update operation an appropriate index is used, the index is updated at the end of the update operations. During the update, another object occurs for the transaction.
As an example, we take a look at the folling update operation.
A table exists with an index on attribute a and the following update operation is performed
CGCLT > update t1 set b = 'hugo' where a = 4000;
9 tuples updated
ok ( 0.138 s )
|
Since a is not modified during this operations and a can be used to retrieve the corresponding tuples, the index on a is used as a virgin index.
All update operations are stored in the rollback table updtab4712
CGADM > list transaction for TS1;
+----------------+-----------+
| TAINFO | TAINFO |
| RBO | NUMOP |
+----------------+-----------+
| updtab4712 | 24905 |
| rbcatlog4712 | 49810 |
+----------------+-----------+
Admin action finished
|
If all tuples are retrieved an updated, now the virgin index is treated.
This means, all updated tuples are added to the virgin index.
During the commit ( or rollback ), the cleanup of the transaction is performed.
Either the old entries are deleted and the new entries are made visible ( commit ) or the new entries are deleted and the old entries are reactivated ( rollback ).
7.5 Realtime Monitor
A realtime overview of a running cego database system is show with the included real time monitor.
To invoke the monitor, you have to call cgadm with the --mon option.
$ cgadm --server=localhost --port=2000 --user=cgadm/cgadm --mon
|
7.6 Basic tableset administration
With the commands described in this section, the most important administration tasks from the Cego database administration can be done.
This includes definition and creation of new tableset, starting and stopping but also dropping and removing tablesets.
7.6.1 Definition
To create a default tableset, just type
CGADM >define tableset TS1;
mediator on game : Table set defined
Mediator request succesfully completed
ok ( 0.001 s )
|
This defines tableset TS1 using the default parameters as listed in the following table.
| Parameter | Meaning | Default |
|---|
| tsroot | Root path for system and log files | ./ |
| syssize | Size of the system space in number of pages | 100 |
| tmpsize | Size of the temporary space in number of pages | 100 |
| tmpsize | Size of the tempory space in number of pages | 100 |
| logsize | Size of a redolog file in bytes | 1000000 |
| lognum | Number of configured redolog files | 3 |
| ordersize | Max order and grouping buffer size | 1000000 |
| mediator | Name of the mediator host | local hostname |
| primary | Name of the primary host | local hostname |
| secondary | Name of the secondary host | local hostname |
For more special tableset definitions, you may set any of the listed attributes to appropriate values.
The attribute values for syssize, tmpsize and logsize are specified as integer values.
The attribute values for logfilename, meiator, primary and secondary are specified as string values.
CGADM >define tableset TS1 tsroot='./db' syssize=100 tmpsize=100 logsize=100000 lognum=3 ordersize=1000000 mediator='game' primary='geek' secondary='sam';
mediator on game : Table set defined
ok ( 0.001 s )
|
Please note :
In the following, just single node configurations are discussed assuming the prerequisite
| mediator = primary = secondary |
For multinode configurations, see the corresponding section.
7.6.2 Creation
After a tableset has been defined, it can be created. This process creates all required system, temp, datafiles and redo log files for
the corresponding tableset. Since all information is availale after the tableset defintion, the creation command is quite easy.
CGADM > create tableset TS1;
mediator on game : Tableset created
ok ( 0.081 s )
|
7.6.3 Start and stop
To use a previous created tableset, it must be started up to be online. The start command is used to
bring a tableset online.
CGADM > start tableset TS1;
Table set started
ok ( 0.002 s )
|
With the start command, the cleanup and forceload option can be used.
The cleanup option performs a cleanup of all datafiles. This means, datafiles are analysed for allocated but never used pages. This may occur after a system crash.
The forceload option can be used, if all view and procedure objects should be compiled and load at tableset startup.
The options can also be used in combination
CGADM > start tableset TS1 cleanup forceload;
Table set started
ok ( 0.002 s )
|
CGADM > list tableset;
+-----------+----------+-----------+-----------+-----------+-----------+
| TSL | TSL | TSL | TSL | TSL | TSL |
| NAME | RUNSTATE | SYNCSTATE | MEDIATOR | PRIMARY | SECONDARY |
+-----------+----------+-----------+-----------+-----------+-----------+
| TS1 | ONLINE | SYNCHED | game | game | game |
+-----------+----------+-----------+-----------+-----------+-----------+
mediator on game : Tableset list retrieved
ok ( 0.016 s )
|
To set the tableset offline it can be stopped using the stop command.
CGADM > stop tableset TS1;
Table set stopped
ok ( 0.002 s )
|
CGADM > list tableset;
+-----------+----------+-----------+-----------+-----------+-----------+
| TSL | TSL | TSL | TSL | TSL | TSL |
| NAME | RUNSTATE | SYNCSTATE | MEDIATOR | PRIMARY | SECONDARY |
+-----------+----------+-----------+-----------+-----------+-----------+
| TS1 | OFFLINE | SYNCHED | game | game | game |
+-----------+----------+-----------+-----------+-----------+-----------+
mediator on game : Tableset list retrieved
ok ( 0.016 s )
|
If the database was not shutdown smooth, but instead it has been crashed, it may be useful to
use the cleanup option during startup. The cleanup option checks out each datafile for allocated
page slots which are not really used. This may happen if pages have already been allocated in buffercache
but have not written down to disk.
CGADM > start tableset TS1 cleanup;
Table set started
ok ( 0.002 s )
|
7.6.4 Drop
If a tableset is not need any more or should be destroyed for any reasons, it can be removed with the drop command.
CGADM > drop tableset TS1;
mediator on game : Table set dropped
ok ( 0.027 s )
|
This deletes all corresponding system , data and redo logfiles for the corresponding tableset.
After the tableset has been dropped, the tableset defintion is still available in the database configuration file.
7.6.5 Remove
To remove the tableset definition from the database configuration file, it can be deleted using the remove
command.
CGADM > remove tableset TS1;
mediator on game : Tableset removed
ok ( 0.001 s )
|
Before a tableset can be removed, it must be dropped using the drop command.
7.6.6 Datafile expansion
CGADM > add app datafile './newdata.dbf' size 1000 to tableset TS1;
Data file added
ok ( 0.846 s )
|
CGADM > add sys datafile './sysdata.dbf' size 1000 to tableset TS1;
Data file added
ok ( 0.846 s )
|
CGADM > add temp datafile './tempdata.dbf' size 1000 to tableset TS1;
Data file added
ok ( 0.846 s )
|
7.6.7 Tableset verification
CGADM > verify tableset TS1;
+----------------+-----------------------------------------+---------------------------------------------------+
| TSVERIFY | TSVERIFY | TSVERIFY |
| TYPE | NAME | STATUS |
+----------------+-----------------------------------------+---------------------------------------------------+
| Table | t1 | ok |
| Table | t2 | ok |
| Table | t3 | ok |
| View | myView1 | ok |
| View | myView2 | ok |
| Procedure | myProc1 | ok |
| Procedure | myProc2 | ok |
+----------------+-----------------------------------------+---------------------------------------------------+
Tableset TS1 verified
ok ( 4.418 s )
|
7.6.8 Tableset correction
After system crash, it may happen, that index objects are invalidated. This is because index tables
are for performance reasons not synchronized with the checkpoint and so they are not written consistently.
If indexes are modified after the last checkpoint, they are invalidated after crash recovery.
To validate index objects for a tableset, this can be done with the following command
CGADM > correct tableset TS1;
+----------------+-----------------------------------------+---------------------------------------------------+
| TSVERIFY | TSVERIFY | TSVERIFY |
| TYPE | NAME | STATUS |
+----------------+-----------------------------------------+---------------------------------------------------+
| Table | t1 | Index i1 corrrected |
| Table | t2 | ok |
| Table | t3 | ok |
+----------------+-----------------------------------------+---------------------------------------------------+
Tableset TS1 corrected
ok ( 4.418 s )
|
7.6.9 Checkpoint setting
To enforce periodic tableset checkpoints ( memory sync with the filesystem ), checkpoints have to be set up.
This is done with the checkpoint command.
CGADM > set checkpoint interval 3600 for TS1;
Checkpoint interval set
ok ( 0.000 s )
|
The checkpoint interval is specified in seconds, so the command above sets the checkpoint interval to one hour.
7.7 Backup administration
The cgadm program provides several commands for managing backups of a cego database.
In the following section these commands are described. For a more detailed description of the
overall backup procedure, please refer to section Backup and recovery.
7.7.1 Import and export
For several reasons, it can be necessary to export any data of a database to a file and import the file again to any other or the same database.
One obvious reasons is reorganisation of the database. During the lifetime of the database, fragmtation of the user data can slow down performance and
waste disk space. In this case, it is useful, to perform a reorganization of the tableset or just a single table.
Other reasons for export and import operations can be found in system migration or system copy requirements.
In this sense, a new database be be build up very easy also on a different operating system or hardware.
Cego provides three modes of doing data import and export.
- XML mode
- Binary mode
- Plain mode
XML exports are used to generate a readable file in XML format. The format is appropriate for heterogenous system copies or system migrations.
It also can be used for editing reasons, since the exported files can be modified using any standard text editor.
From the cgadm command line console, XML export is generated with
CGADM > export xml tableset TS1 to './ts1.xml';
Table set exported
ok ( 0.002 s )
|
The resulting files contains all objects which are stored in the tableset TS1. This also includes any indexes, key, checks and counters.
It can be imported with the following import command
CGADM > import xml tableset TS1 from './ts1.xml nologging';
Table set imported
ok ( 0.004 s )
|
The nologging option disables logging all import operations to the redo logs. The option should be used, if no recovery is needed for the
import and import performance is an important issue.
A more faster way to export and import data is provided with the binary mode. The resulting file is machine dependent and jsut can be used
for target systems with the same hardware system architecture. Binary is much faster than XML and should be used, if performance is a main issue.
The corresponding commands are
CGADM > export binary tableset TS1 to './ts1.dmp';
Table set exported
ok ( 0.002 s )
|
CGADM > import binary tableset TS1 from './ts1.dmp';
Table set imported
ok ( 0.002 s )
|
For huge table data and restricted export requirements, the plain mode can be used. This mode reads and writes the each table row as a stream
to and from the dump file and makes no column structure analysis. As a result, huge amount of data can be exported and imported very fast.
Using a 2.7 GHz CPU with a modern fast IO subsystem, throughput of more then 100000 rows per second can be achieved.
The method is restricted to tables without blob columns and makes no reorganisation of the data on column level.
The corresponding commands are
CGADM > export plain tableset TS1 to './ts1.dmp';
Table set exported
ok ( 0.002 s )
|
CGADM > import plain tableset TS1 from './ts1.dmp';
Table set imported
ok ( 0.002 s )
|
7.7.2 Archive log mode
The archiving mode is enabled for a dedicated tableset using the cgadm admin program.
Connect with cgadm to the database and enable archive mode with
CGADM > enable archlog for TS1;
mediator on game : ArchMode enabled
ok ( 0.000 s )
|
This enables the archive logging for tableset TS1
To disable the archive log mode for a tableset, use the command
CGADM > disable archlog for TS1;
mediator on game : ArchMode disabled
ok ( 0.000 s )
|
7.7.3 Archive log location
If the archiving mode is used, a valid archive log location must be configured.
The log location must have enough space to save all written logfiles ( until they are
saved to any other third level storage ).
For redundancy reaosons, more than one log location can be configured for a tableset.
In this case, the log locations are written in parallel with the offline database log files.
To add an archive log location to a tableset with the following command
CGADM > add archlog AL1 path './arch' to TS1;
mediator on game : added archlog locally
ok ( 0.000 s )
CGADM >
|
If the log location should be not written any more, the location entry can be removed with
CGADM > remove archlog AL1 from TS1;
mediator on game : Archlog removed
ok ( 0.000 s )
|
7.7.4 Online backup mode
To make a backup of a tableset while the tableset is online and in use can be done using the online backup mode.
To switch a tableset to the online backup mode, use the command
CGADM > begin backup TS1';
Begin backup mode
ok ( 0.002 s )
|
Now a backup of the tableset can be done. After the backup has been finished, the backup mode of the
tableset should be ended. This is done with
CGADM > end backup TS1';
Backup mode finished
ok ( 0.002 s )
|
For a detailed description how to create and restore an online backup of a tableset, please refer to
section Backup and recovery
7.7.5 Tableset recovery
If a recovery of a tableset is required, this can be done using cgadm.
After all required datafiles and logfiles have been provided, the recovery is started with
CGADM > recover tableset TS1;
mediator on game : Recovery finished
ok ( 0.338 s )
|
This recovers all redo logs in the known archive log locations until the recent available log entry ( up-to-crash recovery ).
If you rather would like to perform a recovery to an earlier point in time, this can be done using the point-in-time recovery. To do this, you have to provide a valid timestamp with the recover command
CGADM > recover tableset TS1 to '2007-08-12 10:47:39 CEST';
mediator on game : Recovery finished
ok ( 0.338 s )
|
Depending on the amount of log data, which has to be recovered, the recovery process can take a while.
You can follow the process, by tracing the database protocol file.
