 |
| Special November 2016 - Basic tableset administration |
|---|
| Category : Admin |
| Level : Medium |
| Back to Overview |
Hello again. The topic of this workshop is to understand basic tableset mangagement for cego. Tableset are transcations spaces within a cego datbase instance. A tableset can be seen as a container, where several database objects can be stored. It is recommended to create a dedicated tableset for an application.
In the following, we will see how a tableset is basically managed via the cego admin console cgadm For this, a running cego database system should be available. Please refer to the cego documentation to Getting Started to build up the database system from scratch.
After connecting via the admin console, we first check out for already configured tablesets. This might look like
CGADM > list tableset; +----------------+----------------+----------------+ | TSL | TSL | TSL | | NAME | RUNSTATE | SYNCSTATE | +----------------+----------------+----------------+ | lit | ONLINE | SYNCHED | | sysmt | ONLINE | SYNCHED | +----------------+----------------+----------------+ Tableset list ok ( 0.037 s ) |
We will now define a new tableset webspecial. For this, we just call
CGADM > define tableset webspecial; Tableset webspecial defined ok ( 0.043 s ) CGADM > |
To check out for the configured tableset parameters, we can list it
CGADM > show tableset webspecial; +---------------------+---------------------------------------+ | TABLESETINFO | TABLESETINFO | | PARAMETER | VALUE | +---------------------+---------------------------------------+ | Tableset | webspecial | | RunState | DEFINED | | SyncState | SYNCHED | | Primary | bigmac | | Secondary | bigmac | | Mediator | bigmac | | RootPath | /usr/local/cgdb | | Ticket | /usr/local/cgdb/webspecial_ticket.xml | | InitFile | null | | Checkpoint | 0 | | SystemPageTotal | 100 | | SystemPageUsed | 0 | | TempPageTotal | 100 | | TempPageUsed | 0 | | AppPageTotal | 3000 | | AppPageUsed | 0 | | SortAreaSize | 10000000 | | Tid | 0 | | CLSN | 0 | | NLSN | 0 | | ArchMode | OFF | | AutoCorrect | ON | | TableCache | OFF | | TC MaxEntryNum | 0 | | TC MaxEntrySize | 0 | | TC UsedSize | 0 | | QueryCache | OFF | | QC MaxEntryNum | 0 | | QC MaxEntrySize | 0 | | QC UsedSize | 0 | | LogSize | 1000000 | | LogFile | /usr/local/cgdb/webspecial_redo0.log | | LogFile | /usr/local/cgdb/webspecial_redo1.log | | LogFile | /usr/local/cgdb/webspecial_redo2.log | +---------------------+---------------------------------------+ Tableset information ok ( 0.001 s ) |
The location for the data and log files still looks not good. We want to change it to store all files under a different path. By setting the tsroot variable, we change all relevant path definitions to the new path. This can be done with
CGADM > set tsroot '/usr/local/cgdb/data' for webspecial; TSRoot set ok ( 0.002 s ) |
We also want to change the size of the initial data file from 3000 pages to 4000 pages.
CGADM > set appsize 4000 for webspecial; App size set ok ( 0.041 s ) |
The checkpoint interval should be set to 4 hours. Since the value is given in seconds, this results in 4*60*60 = 14400
CGADM > set checkpoint 14400 for webspecial; Checkpoint interval set ok ( 0.042 s ) |
If we check the configuration again, it looks appropriate now.
CGADM > show tableset webspecial; +---------------------+--------------------------------------------+ | TABLESETINFO | TABLESETINFO | | PARAMETER | VALUE | +---------------------+--------------------------------------------+ | Tableset | webspecial | | RunState | DEFINED | | SyncState | SYNCHED | | Primary | bigmac | | Secondary | bigmac | | Mediator | bigmac | | RootPath | /usr/local/cgdb/data | | Ticket | /usr/local/cgdb/data/webspecial_ticket.xml | | InitFile | null | | Checkpoint | 14400 | | SystemPageTotal | 100 | | SystemPageUsed | 0 | | TempPageTotal | 100 | | TempPageUsed | 0 | | AppPageTotal | 3000 | | AppPageUsed | 0 | | SortAreaSize | 10000000 | | Tid | 238871 | | CLSN | 955739 | | NLSN | 0 | | ArchMode | ON | | AutoCorrect | ON | | TableCache | OFF | | TC MaxEntryNum | 0 | | TC MaxEntrySize | 0 | | TC UsedSize | 0 | | QueryCache | OFF | | QC MaxEntryNum | 0 | | QC MaxEntrySize | 0 | | QC UsedSize | 0 | | LogSize | 1000000 | | LogFile | /usr/local/cgdb/data/webspecialredo0.log | | LogFile | /usr/local/cgdb/data/webspecialredo1.log | | LogFile | /usr/local/cgdb/data/webspecialredo2.log | +---------------------+--------------------------------------------+ Tableset information ok ( 0.001 s ) |
The tsroot parameter just can be changed, if the tableset is still in state DEFINED. Other parameters like checkpoint, init file, sortareasize, etc. could be also set up later on, if the tableset has been already created
If we are fine with our tableset configuration, we can create it. During this step, all relevant data and log files are created and initialized.
CGADM > create tableset webspecial; Tableset webspecial created ok ( 0.746 s ) |
After successful creation, the tableset should be in state OFFLINE.
CGADM > list tableset; +----------------+----------------+----------------+ | TSL | TSL | TSL | | NAME | RUNSTATE | SYNCSTATE | +----------------+----------------+----------------+ | lit | ONLINE | SYNCHED | | sysmt | ONLINE | SYNCHED | | webspecial | OFFLINE | SYNCHED | +----------------+----------------+----------------+ Tableset list ok ( 0.043 s ) |
So it is ready to get online.
CGADM > start tableset webspecial; Tableset webspecial started ok ( 0.042 s ) |
Just if the tableset is online, it can be accessed by any database client.
CGADM > list tableset; +----------------+----------------+----------------+ | TSL | TSL | TSL | | NAME | RUNSTATE | SYNCSTATE | +----------------+----------------+----------------+ | lit | ONLINE | SYNCHED | | sysmt | ONLINE | SYNCHED | | webspecial | ONLINE | SYNCHED | +----------------+----------------+----------------+ Tableset list ok ( 0.042 s ) |
Now we add a very graceful permission entry for the new tableset to role ALL. We just give here the basic permission configuration command, please refre to the users guide to get a more detailed description about user and permission management for cego.
CGADM > set permission webspecial_P with tableset=webspecial,filter='ALL',right=ALL for ALL; Permission webspecial_P set ok ( 0.000 s ) |
If the tableset is online and appropriate permission is granted, it can be accessed by any database client.
bigmac.fritz.box> cgclt --server=... --tableset=webspecial .... CGCLT > show systemspace; +-----------+-----------+-----------+ | SYSINFO | SYSINFO | SYSINFO | | SPACE | NUMPAGES | USEDPAGES | +-----------+-----------+-----------+ | SYSTEM | 100 | 16 | | TEMP | 100 | 15 | | DATAFILE | 4000 | 0 | +-----------+-----------+-----------+ 3 tuples ok ( 0.000 s ) |
Any other datafile can be added online or offline to the tableset. In the following, we show how the application data space is expanded
CGADM > list datafile for webspecial; +------------------------------------------+--------------+--------------+--------------+ | DATAFILEINFO | DATAFILEINFO | DATAFILEINFO | DATAFILEINFO | | FILENAME | FILETYPE | SIZE | USED | +------------------------------------------+--------------+--------------+--------------+ | /usr/local/cgdb/data/webspecial.sys | SYSFILE | 100 | 16 | | /usr/local/cgdb/data/webspecial.temp | TEMPFILE | 100 | 15 | | /usr/local/cgdb/data/webspecial_data.dat | APP | 4000 | 0 | +------------------------------------------+--------------+--------------+--------------+ Tableset information ok ( 0.000 s ) CGADM > add app datafile '/usr/local/cgdb/data/webspecial_data02.dat' size 4000 to webspecial; Datafile added ok ( 0.217 s ) CGADM > list datafile for webspecial; +--------------------------------------------+--------------+--------------+--------------+ | DATAFILEINFO | DATAFILEINFO | DATAFILEINFO | DATAFILEINFO | | FILENAME | FILETYPE | SIZE | USED | +--------------------------------------------+--------------+--------------+--------------+ | /usr/local/cgdb/data/webspecial.sys | SYSFILE | 100 | 16 | | /usr/local/cgdb/data/webspecial.temp | TEMPFILE | 100 | 15 | | /usr/local/cgdb/data/webspecial_data.dat | APP | 4000 | 0 | | /usr/local/cgdb/data/webspecial_data02.dat | APP | 4000 | 0 | +--------------------------------------------+--------------+--------------+--------------+ Tableset information ok ( 0.001 s ) |
If the tableset is not needed anymore, it can be dropped. For this, it first has to be stopped.
CGADM > stop tableset webspecial; Tableset webspecial stopped ok ( 0.005 s ) |
Now it can be dropped. Please note, that all stored data is removed. It is recommended to keep any kind of backup, if the data should be restored later on
CGADM > drop tableset webspecial; Tableset webspecial dropped ok ( 0.002 s ) |
After the tableset has been dropped, it is still defined in the database. Either it could be recreated or if not needed anymore, it can be finally removed.
CGADM > remove tableset webspecial; Tableset webspecial removed ok ( 0.000 s ) |
Hope this workshop was helpful and gave a basic understanding how to handle cego tableset configurations. Thanks for joining !