31.1 Database Best Practices

This documentation provides instructions to rebuild the Sybase database by using the DBISQL utility. You can choose to rebuild and validate the database by using any other utility that is recommended in the Sybase SQL Anywhere documentation.

Section 31.1.1, Rebuilding the Sybase Database

31.1.1 Rebuilding the Sybase Database

If your ZENworks database is an embedded or external Sybase database, you should rebuild the database so that it runs on the latest version of the Sybase database engine.

Make sure that you have archived your database credentials.

To archive the credentials of an external Sybase database, contact your database administrator.

To archive the credentials of an embedded or external OEM Sybase database, perform the following tasks on the database server:
1. Make sure the database service is running.
  
  On Windows: In the Windows Services, make sure that the status of Novell ZENworks Embedded Datastore is Started.
  
  On Linux: At the console prompt, enter /etc/init.d/sybase-asa status to verify the status of the database. If the database is not running, enter /etc/init.d/sybase-asa start.
2. Obtain the Sybase connection information by running the zman dgc command.
3. Provide the credentials of the ZENworks administrator when prompted.
4. Copy and save the database username and password in to a text file.
Stop the Novell ZENworks Embedded Datastore service, if it is running.
- On Windows: Do the following:
  1. From the Windows desktop Start menu, click Settings > Control Panel.
  2. Double-click Administrative Tools > Services.
  3. Right-click the Novell ZENworks Embedded Datastore service, then click Stop, or select the Novell ZENworks Embedded Datastore service, then click on the toolbar.
- On Linux: At the console prompt, enter /etc/init.d/sybase-asa stop.
Stop all the ZENworks Services on the all the ZENworks Servers in the Management Zone.
- On Windows: Do the following
  1. Execute the following command at the server prompt:
    
    novell-zenworks-configure -c Start
  2. Enter the number next to the Stop action.
- On Linux: Do the following:
  1. Execute the following command at the server prompt:
    
    /opt/novell/zenworks/bin/novell-zenworks-configure -c Start
  2. Enter the number next to the Stop action.
At the console prompt, go to the Sybase database directory. By default, it is %ZENWORKS_HOME%\database on Windows, and /var/opt/novell/zenworks/database on Linux.
Take a reliable backup of the zenworks_zone_name.db and Zenworks_zone_name.log files.

For detailed information on how to take an immediate backup of the files of the embedded Sybase database, see Section 29.3, Backing Up the Embedded Sybase SQL Anywhere Database. For detailed information on how to take an immediate backup of the files of the external Sybase database, see Section 30.1, Backing Up the External Sybase Database.
Start the Novell ZENworks Embedded Datastore service.
- On Windows: Do the following:
  1. From the Windows desktop Start menu, click Settings > Control Panel.
  2. Double-click Administrative Tools > Services.
  3. Right-click the Novell ZENworks Embedded Datastore service, then click Start, or select the Novell ZENworks Embedded Datastore service, then click on the toolbar.
- On Linux: At the console prompt, enter /etc/init.d/sybase-asa start.
(Conditional) If your database is installed on Linux, run the following script file:

source /opt/novell/zenworks/share/sybase/bin32/sa_config.sh
Ensure that the database authentication has been set up by verifying that the database_authentication attribute in the saopts.sql file has been configured.

The saopts.sql file is located in the %ZENWORKS_HOME%\share\asa\scripts\ directory on Windows, and in the /opt/novell/zenworks/share/sybase/scripts/ directory on Linux. The database_authentication attribute is located in the following entry in the saopts.sql file:
```
if not exists( select * from SYS.SYSOPTION
 where ucase( "option" )  = ucase( 'database_authentication' ) ) then
    set option PUBLIC.database_authentication = <value>;
end if
go
```
If the value of set option PUBLIC.database_authentication is empty, continue with Step 8.a to launch the DBISQL utility and to configure the database authentication; else skip to Step 9.
1. Launch the DBISQL utility.
  1. At the command prompt, go to the %ZENWORKS_HOME%\share\ASA\ directory on Windows or to the /opt/novell/zenworks/share/sybase/bin32s directory on Linux.
  2. Enter the dbisql command.
  3. In the Identification tab, specify the database credentials.
  4. Click the Database tab, then specify the name of database service that is currently running.
  5. Click OK.
2. In the SQL Statements section, specify the following query:
```
select setting
from sysoptions 
   where "option" like 'database%' >># output_filename
```
3. Click Execute SQL Statement(s).
  
  The results of the query are written to the output file that you specify in the query.
4. Copy the result of the query from the output file, and paste it as the value of the database_authentication attribute in the saopts.sql file. The saopts.sql file is located in the %zenworks_home%\share\asa\scripts\ directory on Windows, and in the /opt/novell/zenworks/share/sybase/scripts/ directory on Linux.
  
  The database_authentication attribute is located in the following entry in the saopts.sql file:
```
if not exists( select * from SYS.SYSOPTION
 where ucase( "option" )  = ucase( 'database_authentication' ) ) then
    set option PUBLIC.database_authentication = <output_of_the_query_run_in_Step_8b>;
end if
go
```
Stop the Novell ZENworks Embedded Datastore service.
- For the Embedded Database: Stop all the ZENworks services, including the Novell ZENworks Embedded Datastore service:
  1. At the console prompt, run the novell-zenworks-configure -c Start command.
  2. Type the option number corresponding to Stop.
  3. Press Enter twice.
- For the External Database: Stop the Novell ZENworks Embedded Datastore Service by stopping the Windows Services manager on Windows, or by running the /etc/init.d/sybase-asa stop command on Linux.
Create a temporary directory with the name as unload within c:\dbreload\ on Windows or within /tmp/dbreload/ on Linux.
At the console prompt of the database server, run the following command to start the database service:

On Windows: %ZENWORKS_HOME%\database\zenworks_ZONE_NAME.db -n rebuild

On Linux: /var/opt/novell/zenworks/database/zenworks_ZONE_NAME.db -n rebuild
Open another command prompt on the database server, and run the unload command.
1. At the command prompt, go to the %ZENWORKS_HOME%\share\ASA\ directory on Windows or to the /opt/novell/zenworks/share/sybase/bin32s directory on Linux.
2. Run the appropriate command:
  
  On Windows: dbunload -c "UID=zenadmin;PWD=database_password;ENG=rebuild" -an c:\dbreload\unload\zenworks_<management_zone_name>.db
  
  On Linux: dbunload -c "UID=zenadmin;PWD=database_password;ENG=rebuild" -an /tmp/dbreload/unload/zenworks_<management_zone_name>.db
After the database rebuild has been successfully completed, take a reliable backup of the newly built database. The database is located in the c:\dbreload\unload directory on Windows and in the /tmp/dbreload/unload directory on Linux.

If you encounter any issues during the rebuild process, contact Novell Support.
Stop the Novell ZENworks Embedded Datastore service by using the command:
- On Windows: Right-click the Rebuild icon located in Windows taskbar, then click Shutdown.
- On Linux: At the console prompt, enter q.
Overwrite the database and applicable log file in the database directory with the new ones located in the unload directory (zenworks_management_zone_name.*).

The unload directory is located in c:\dbreload\ on Windows or in /tmp/dbreload/ on Linux.
Start the Novell ZENworks Embedded Datastore service.
- For the Embedded Database: Start all the ZENworks services, including the Novell ZENworks Embedded Datastore service:
  1. At the console prompt, run the novell-zenworks-configure -c Start command.
  2. Type the option number corresponding to Start.
  3. Press Enter twice.
- For the External Database: Start the Novell ZENworks Embedded Datastore service in the Services window on Windows, or run the /etc/init.d/sybase-asa start command on Linux.
Start all the ZENworks Services on the all the ZENworks Servers in the Management Zone.
- On Windows: Do the following
  1. Execute the following command at the server prompt:
    
    novell-zenworks-configure -c Start
  2. Enter the number next to the Start action.
- On Linux: Do the following:
  1. Execute the following command at the server prompt:
    
    /opt/novell/zenworks/bin/novell-zenworks-configure -c Start
  2. Enter the number next to the Start action.
Take a backup of the newly created database on a regular basis (daily or weekly).