<HTML ><HEAD ><TITLE >Basic System and Database Administration</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.63 "><LINK REL="HOME" TITLE="Ingres II HOWTO" HREF="index.html"><LINK REL="PREVIOUS" TITLE="The Installation Process" HREF="install.html"><LINK REL="NEXT" TITLE="Ingres/Net" HREF="net.html"></HEAD ><BODY CLASS="SECT1" BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#840084" ALINK="#0000FF" ><DIV CLASS="NAVHEADER" ><TABLE WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="3" ALIGN="center" >Ingres II HOWTO</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="bottom" ><A HREF="install.html" >Prev</A ></TD ><TD WIDTH="80%" ALIGN="center" VALIGN="bottom" ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="bottom" ><A HREF="net.html" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="ADMIN" >6. Basic System and Database Administration</A ></H1 ><P >In this section I outline some of the basic tasks of the <SPAN CLASS="APPLICATION" >Ingres</SPAN > system administrator and the <SPAN CLASS="APPLICATION" >Ingres</SPAN > database administrator. You will also see what tools <SPAN CLASS="APPLICATION" >Ingres</SPAN > provides to perform these tasks. In the following I suppose you are logged in as ingres.</P ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="STARTSTP" >6.1. Starting and Stopping Ingres</A ></H2 ><P >You have already seen how to start <SPAN CLASS="APPLICATION" >Ingres</SPAN >: </P ><TABLE BORDER="0" BGCOLOR="#E0E0E0" WIDTH="100%" ><TR ><TD ><FONT COLOR="#000000" ><PRE CLASS="PROGRAMLISTING" >$ ingstart </PRE ></FONT ></TD ></TR ></TABLE ><P >To stop <SPAN CLASS="APPLICATION" >Ingres</SPAN >, use the <B CLASS="COMMAND" >ingstop</B > command:</P ><TABLE BORDER="0" BGCOLOR="#E0E0E0" WIDTH="100%" ><TR ><TD ><FONT COLOR="#000000" ><PRE CLASS="PROGRAMLISTING" >$ ingstop </PRE ></FONT ></TD ></TR ></TABLE ><P ><B CLASS="COMMAND" >ingstop</B > only stops <SPAN CLASS="APPLICATION" >Ingres</SPAN > if the are no active user sessions. If you want to stop the system regardless of user sessions, use the following form:</P ><TABLE BORDER="0" BGCOLOR="#E0E0E0" WIDTH="100%" ><TR ><TD ><FONT COLOR="#000000" ><PRE CLASS="PROGRAMLISTING" >$ ingstop -force </PRE ></FONT ></TD ></TR ></TABLE ><P >In this case, after you have killed <SPAN CLASS="APPLICATION" >Ingres</SPAN >, check if it released all shared memory segments and semaphores it had used: </P ><TABLE BORDER="0" BGCOLOR="#E0E0E0" WIDTH="100%" ><TR ><TD ><FONT COLOR="#000000" ><PRE CLASS="PROGRAMLISTING" >$ ipcs -a </PRE ></FONT ></TD ></TR ></TABLE ><P >If you see shared memory segments or semaphores in <B CLASS="COMMAND" >ipcs</B >'s output that are still attached to the ingres user, release them with <SPAN CLASS="APPLICATION" >Ingres'</SPAN > <B CLASS="COMMAND" >ipcclean</B > utility:</P ><TABLE BORDER="0" BGCOLOR="#E0E0E0" WIDTH="100%" ><TR ><TD ><FONT COLOR="#000000" ><PRE CLASS="PROGRAMLISTING" >$ ipcclean </PRE ></FONT ></TD ></TR ></TABLE ><DIV CLASS="WARNING" ><P ></P ><TABLE CLASS="WARNING" WIDTH="100%" BORDER="0" ><TR ><TD WIDTH="25" ALIGN="CENTER" VALIGN="TOP" ><IMG SRC="../images/warning.gif" HSPACE="5" ALT="Warning"></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><P >Take care: forcing <SPAN CLASS="APPLICATION" >Ingres</SPAN > to stop might make your databases inconsistent.</P ></TD ></TR ></TABLE ></DIV ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="NEWUSERS" >6.2. New Ingres Users and Locations</A ></H2 ><P >In order for any user to have access to the <SPAN CLASS="APPLICATION" >Ingres</SPAN > installation, you have to define them as <SPAN CLASS="APPLICATION" >Ingres</SPAN > users with the <B CLASS="COMMAND" >accessdb</B > utility.</P ><P >Start <B CLASS="COMMAND" >accessdb</B >:</P ><TABLE BORDER="0" BGCOLOR="#E0E0E0" WIDTH="100%" ><TR ><TD ><FONT COLOR="#000000" ><PRE CLASS="PROGRAMLISTING" >$ accessdb </PRE ></FONT ></TD ></TR ></TABLE ><P >Select the <SPAN CLASS="GUIMENUITEM" >Users</SPAN > option, then <SPAN CLASS="GUIMENUITEM" >Create</SPAN >.</P ><P >Here, enter the name of the user. You do not have to modify permissions.</P ><P ><SPAN CLASS="GUIMENUITEM" >Save</SPAN >, then <SPAN CLASS="GUIMENUITEM" >End</SPAN >, and <SPAN CLASS="GUIMENUITEM" >End</SPAN >.</P ><P >You can also use <B CLASS="COMMAND" >accessdb</B > to create new locations, change their types or extend databases to new locations. The usage of <B CLASS="COMMAND" >accessdb</B > is covered in the <I CLASS="CITETITLE" >System Reference Guide</I > and in the <I CLASS="CITETITLE" >Database Administrator's Guide</I >.</P ><P >As an alternative to <B CLASS="COMMAND" >accessdb</B >, you can maintain users and locations by running <SPAN CLASS="ACRONYM" >SQL</SPAN > commands on <SPAN CLASS="DATABASE" >iidbdb</SPAN > (<B CLASS="COMMAND" >create user</B >, <B CLASS="COMMAND" >create location</B >, etc.). The syntax of these commands can be found in the <I CLASS="CITETITLE" >SQL Reference Guide</I >.</P ><DIV CLASS="WARNING" ><P ></P ><TABLE CLASS="WARNING" WIDTH="100%" BORDER="0" ><TR ><TD WIDTH="25" ALIGN="CENTER" VALIGN="TOP" ><IMG SRC="../images/warning.gif" HSPACE="5" ALT="Warning"></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><P >Since the ingres user has unlimited power of changing and possibly destroying any element of an <SPAN CLASS="APPLICATION" >Ingres</SPAN > installation, it is highly advisable that you only use this account for carrying out administrative tasks. Create another Linux user and set its environment to that of ingres. Register it as an <SPAN CLASS="APPLICATION" >Ingres</SPAN > user via <B CLASS="COMMAND" >accessdb</B > and use this account for everyday work.</P ></TD ></TR ></TABLE ></DIV ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="CREATEDB" >6.3. Creating and Destroying Databases</A ></H2 ><P >In subsection <A HREF="install.html#CHECK" >Checking the Installation</A > you created a new database. You did not specify any options in the</P ><TABLE BORDER="0" BGCOLOR="#E0E0E0" WIDTH="100%" ><TR ><TD ><FONT COLOR="#000000" ><PRE CLASS="PROGRAMLISTING" >$ createdb test </PRE ></FONT ></TD ></TR ></TABLE ><P >command. Therefore the values stored in <TT CLASS="ENVAR" >II_DATABASE</TT >, <TT CLASS="ENVAR" >II_CHECKPOINT</TT >, etc., became locations for the <SPAN CLASS="DATABASE" >test</SPAN > database. You could have specified each location explicitly:</P ><TABLE BORDER="0" BGCOLOR="#E0E0E0" WIDTH="100%" ><TR ><TD ><FONT COLOR="#000000" ><PRE CLASS="PROGRAMLISTING" >$ createdb test -d<data location> -c<checkpoint location> -j<journal location> -b<dump location> -w<work location> </PRE ></FONT ></TD ></TR ></TABLE ><P >You can remove a database with the <B CLASS="COMMAND" >destroydb</B > command:</P ><TABLE BORDER="0" BGCOLOR="#E0E0E0" WIDTH="100%" ><TR ><TD ><FONT COLOR="#000000" ><PRE CLASS="PROGRAMLISTING" >$ destroydb test </PRE ></FONT ></TD ></TR ></TABLE ><DIV CLASS="WARNING" ><P ></P ><TABLE CLASS="WARNING" WIDTH="100%" BORDER="0" ><TR ><TD WIDTH="25" ALIGN="CENTER" VALIGN="TOP" ><IMG SRC="../images/warning.gif" HSPACE="5" ALT="Warning"></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><P >Be careful, because <SPAN CLASS="APPLICATION" >Ingres</SPAN > will not prompt you before destroying the database.</P ></TD ></TR ></TABLE ></DIV ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="COLL" >6.4. Collation Sequences</A ></H2 ><P >The collation sequence determines which of any two character strings should be considered less than the other. In <SPAN CLASS="APPLICATION" >Ingres</SPAN >, every database can have its own sort order. You can specify the collation sequence when creating the database:</P ><TABLE BORDER="0" BGCOLOR="#E0E0E0" WIDTH="100%" ><TR ><TD ><FONT COLOR="#000000" ><PRE CLASS="PROGRAMLISTING" >createdb test -lhun </PRE ></FONT ></TD ></TR ></TABLE ><P >If you omit the <TT CLASS="OPTION" >-l</TT > parameter, the database will have the default collation sequence which is determined by the implicit sort order of the code set of the <SPAN CLASS="APPLICATION" >Ingres</SPAN > installation (<TT CLASS="ENVAR" >II_CHARSET</TT >).</P ><P >If you want to use your own collation sequence (it is <TT CLASS="CONSTANT" >hun</TT > in the example above), you have to create a definition file first. The structure of this file must obey to simple rules by which you specify the absolute or relative ordering of letters and/or strings in your language. This file must then be compiled by the <B CLASS="COMMAND" >aducompile</B > utility for <SPAN CLASS="APPLICATION" >Ingres</SPAN > to be able to use it.</P ><P >The Spanish collation sequence and the collation based on the <SPAN CLASS="ACRONYM" >DEC</SPAN > Multinational Character Set are available both in source (<TT CLASS="FILENAME" >spanish.dsc</TT > and <TT CLASS="FILENAME" >multi.dsc</TT >), and compiled form (<TT CLASS="FILENAME" >spanish</TT > and <TT CLASS="FILENAME" >multi</TT >). </P ><P >You specify these collation sequences in the following way:</P ><TABLE BORDER="0" BGCOLOR="#E0E0E0" WIDTH="100%" ><TR ><TD ><FONT COLOR="#000000" ><PRE CLASS="PROGRAMLISTING" >createdb test -lspanish </PRE ></FONT ></TD ></TR ></TABLE ><P >or</P ><TABLE BORDER="0" BGCOLOR="#E0E0E0" WIDTH="100%" ><TR ><TD ><FONT COLOR="#000000" ><PRE CLASS="PROGRAMLISTING" >createdb test -lmulti </PRE ></FONT ></TD ></TR ></TABLE ><P >The compiled definition files for a collation sequence must be in the <TT CLASS="FILENAME" >$II_SYSTEM/ingres/file/collation</TT > directory. The syntax rules of the definition files can be found in the <I CLASS="CITETITLE" >System Reference Guide</I >. It may also be useful to examine the definition files for the Spanish and the <SPAN CLASS="ACRONYM" >DEC</SPAN > Multinational collations.</P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="BACKUP" >6.5. Backup and Recovery</A ></H2 ><P >You can back up an <SPAN CLASS="APPLICATION" >Ingres</SPAN > database or certain tables in it with the <B CLASS="COMMAND" >ckpdb</B > utility. The following command backs up the <SPAN CLASS="DATABASE" >test</SPAN > database:</P ><TABLE BORDER="0" BGCOLOR="#E0E0E0" WIDTH="100%" ><TR ><TD ><FONT COLOR="#000000" ><PRE CLASS="PROGRAMLISTING" >$ ckpdb test </PRE ></FONT ></TD ></TR ></TABLE ><DIV CLASS="NOTE" ><P ></P ><TABLE CLASS="NOTE" WIDTH="100%" BORDER="0" ><TR ><TD WIDTH="25" ALIGN="CENTER" VALIGN="TOP" ><IMG SRC="../images/note.gif" HSPACE="5" ALT="Note"></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><P >Checkpoints can be taken online.</P ></TD ></TR ></TABLE ></DIV ><P >Restoring a database can be done with the <B CLASS="COMMAND" >rollforwarddb</B > command:</P ><TABLE BORDER="0" BGCOLOR="#E0E0E0" WIDTH="100%" ><TR ><TD ><FONT COLOR="#000000" ><PRE CLASS="PROGRAMLISTING" >$ rollforwarddb test </PRE ></FONT ></TD ></TR ></TABLE ><P >By default, <B CLASS="COMMAND" >rollforwarddb</B >, using the latest checkpoint and all journal files created since that checkpoint, restores the database to its last committed state. However, you can specify a point in time to restore the database to the state it was in at that time. You can go back as far as 16 checkpoints (<SPAN CLASS="APPLICATION" >Ingres</SPAN > stores data for the last 16 checkpoints in the control file of the database).</P ><P >Both <B CLASS="COMMAND" >ckpdb</B > and <B CLASS="COMMAND" >rollforwarddb</B > accept many parameters. You can read more about these commands in the <I CLASS="CITETITLE" >System Reference Guide</I >. Besides, you should read Michael Leo's paper on <SPAN CLASS="APPLICATION" >Ingres</SPAN > backup and recovery at <A HREF="http://www.naiua.org/papers/backup99.zip" TARGET="_top" > http://www.naiua.org/papers/backup99.zip</A >.</P ><P >Both <B CLASS="COMMAND" >ckpdb</B > and <B CLASS="COMMAND" >rollforwarddb</B > use a template file (<TT CLASS="FILENAME" >$II_SYSTEM/ingres/files/cktmpl.def</TT >). By modifying this file, you can customize the Linux commands that do the physical backup and restore of the data files. Consult the <I CLASS="CITETITLE" >Database Administrator's Guide</I > for the syntax of this file.</P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="CONFIG" >6.6. Configuring Ingres</A ></H2 ><P >Most <SPAN CLASS="APPLICATION" >Ingres</SPAN > parameters can be set via the <B CLASS="COMMAND" >cbf</B > utility. This is the program by which you can specify the number of <SPAN CLASS="ACRONYM" >DBMS</SPAN > servers, the sizes of different caches and a lot of other variables. The usage of <B CLASS="COMMAND" >cbf</B > is detailed in the <I CLASS="CITETITLE" >System Reference Guide</I >.</P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="MONIT" >6.7. Monitoring Ingres</A ></H2 ><P >You can use the <B CLASS="COMMAND" >ipm</B > utility to monitor a running <SPAN CLASS="APPLICATION" >Ingres</SPAN > system (<SPAN CLASS="APPLICATION" >Visual DBA</SPAN > only runs on Win32). With <B CLASS="COMMAND" >ipm</B >, you can monitor and manage user sessions, and also the locking and logging subsystems.</P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="MESSAGE" >6.8. Message Files</A ></H2 ><P >The <SPAN CLASS="APPLICATION" >Ingres</SPAN > message files reside in the <TT CLASS="FILENAME" >$II_SYSTEM/ingres/files</TT > directory. The most important of these is <TT CLASS="FILENAME" >errlog.log</TT >. Should any problems arise during the running of <SPAN CLASS="APPLICATION" >Ingres</SPAN >, this is the file to check first.</P ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="install.html" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="net.html" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >The Installation Process</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" > </TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Ingres/Net</TD ></TR ></TABLE ></DIV ></BODY ></HTML >