MBO Server Cache Database Rebuild

[former_member731352]

Introduction

This article provides the simplest way to rebuild a MBO server cache database. We can improve the database performance by rebuilding the cache database periodically. The rebuild resets the indexes and eliminates any empty space in the SQL database. This also helps in reducing a huge file size from the cache database log file.

In our environment, we faced MBO CacheDB services getting crashed frequently. Based on analysis, we found the CacheDB log file size was higher(110GB) which was causing the service to crash on its own. After rebuilding the database, the log file size was highly reduced to few MBs and also the CacheDB service became stable.

Overview

SQL Anywhere database includes the Unload utility(dbunload) which can be used to perform multiple operations.

We can use the Unload utility to export one, many, or all the database tables. We can export table data, and table schemas. To rearrange the database tables, we can also use dbunload to create the necessary SQL script files and modify them as needed. These files can be used to create identical tables in different databases. We can unload tables with structure only, data only, or with both structure and data.

Syntax

dbunload [ options ] [ directory ]

Some of the dbunload options are listed below.

Option	Description
@DATA	Read in options from the specified environment variable or configuration file.
-ac value	Connect to an existing database and reload the data directly into it, combining the operations of unloading a database and reloading the results into an existing database
-an database	Combine the operations of unloading a database, creating a new database, and loading the data using this option.
-ap size	Set the page size of the new database.
-ar [directory]	Create a new database with the same settings as the old database, reload it, and replace the old database.
-c value	Specify the connection parameters for the source database.
-cp	Compress the table data output files by appending the COMPRESSED keyword to the UNLOAD TABLE statements it executes.
-d	With this option, none of the database definition commands are generated (CREATE TABLE, CREATE INDEX, and so on); reload.sql contains statements to reload the data only.
-dc	Force all computed columns in the database to be recalculated.
-er	Remove encryption from encrypted tables during an unload procedure.
-m	Do not preserve user IDs for databases involved in replication.
-n	Do not unload database data; reload.sql contains SQL statements to build the structure of the database only.
-q	Run in quiet mode—do not display messages or windows

Prerequisites

• Stop the MBO CacheDB and SampleDB services.


• Operating system user with Administrator roles.

Grant the required DB privilege

SQL Anywhere 17 introduced new system privileges, which, when doing a rebuild, or creating a new database get assigned to either the SYS_AUTH_SA_ROLE or the SYS_AUTH_SSO_ROLE.

But when performing an upgrade to SQL Anywhere 17, these new system privileges instead get assigned to SYS_UPGRADE_ROLE_ROLE.

It is then up to the user to determine where those privileges need to be granted, and manually GRANT/REVOKE those privileges.

If the user does not REVOKE the system privileges from SYS_UPGRADE_ROLE_ROLE, then a rebuild will fail.

1. Display the roles assigned to SYS_UPGRADE_ROLE_ROLE using the below statement.

• In Interactive SQL, log in as a user with administration and exercise rights on the UPGRADE ROLE system privilege, and execute a statement that calls the sp_displayroles system procedure to display the privileges that have been granted to the UPGRADE ROLE system privilege.

CALL sp_displayroles ( 'SYS_UPGRADE_ROLE_ROLE', 'expand_down' );

2. Running the following query will generate all the REVOKE and GRANT statements that we will need. This example script assigns the roles to the DBA account, but any ADMIN account will work.  Simply replace "dba" with whichever account we use as our admin account.

begin

declare roles_cursor cursor for

select role_name, grant_type from  sp_displayroles ( 'SYS_UPGRADE_ROLE_ROLE', 'expand_down' );    declare roleName char(128);

declare grantType char(128);

declare sqlStmt long varchar;

open roles_cursor;

rolesLoop: loop

fetch roles_cursor into roleName, grantType;

if sqlcode <> 0 then leave rolesLoop endif;

set sqlStmt = string( 'grant ', roleName, ' to dba with ', grantType, ' option;');

message sqlStmt to client;

set sqlStmt = string( 'revoke ', roleName, ' from SYS_UPGRADE_ROLE_ROLE;' );

message sqlStmt to client; 

end loop;

end;

 

3. Once we have ran the SQL query the output will look something like below

grant MANAGE ODATA to dba with ADMIN option;

revoke MANAGE ODATA from SYS_UPGRADE_ROLE_ROLE;

grant VERIFY ODATA to dba with ADMIN ONLY option;

revoke VERIFY ODATA from SYS_UPGRADE_ROLE_ROLE;

grant MANAGE LISTENERS to dba with ADMIN option;

revoke MANAGE LISTENERS from SYS_UPGRADE_ROLE_ROLE;

grant MANAGE TIME ZONE to dba with ADMIN option;

revoke MANAGE TIME ZONE from SYS_UPGRADE_ROLE_ROLE;

grant MANAGE CACHED PLANS to dba with ADMIN option;

revoke MANAGE CACHED PLANS from SYS_UPGRADE_ROLE_ROLE;

grant ACCESS USER PASSWORD to dba with ADMIN option;

revoke ACCESS USER PASSWORD from SYS_UPGRADE_ROLE_ROLE;

grant ACCESS DISK INFORMATION to dba with ADMIN option;

4. The final step is running these generated GRANT/REVOKE statements. As mentioned, the DBA account can be switched out for any ADMIN account.

It is important to first GRANT the role to the admin account before REVOKEing it from SYS_UPGRADE_ROLE_ROLE or else an error will be thrown.

Main

Rebuild the MBO Cache Database

1. Stop the MBO CacheDB and SampleDB services.

2. Set up a new directory to hold the rebuilt database. Example D:\temp\newdb

3. In a DOS command window with administrative privilege set the PATH variable to include the SQLAnywhere\BIN64 directory. Example:

set PATH=D:\SAP\MobilePlatform3\MR30\Servers\SQLAnywhere\BIN64;%PATH%

4. In the DOS command window execute the following command, changing the password from 'xxx' to the correct password. Make sure to have a backup copy of the original default.db file.

dbunload -c "uid=dba;pwd=xxx;DBF=c:\SAP\MobilePlatform3\MR30\Servers\UnwiredServer\data\default.db” –an D:\temp\newdb\default.db

Completion screenshot:

5. Copy the newly rebuilt default.db and log to the UnwiredServer\data directory and start the MBO server.

Replace the copied files to Unwired server/data directory.

Note: We can take a backup of existing log file to be safer.

6. Start the MBO CacheDB and SampleDB services.

 

Conclusion

With this article we have successfully rebuild the MBO server cache database using the SQL dbunload utility. We have also learnt how to drastically reduce the CacheDB log file size and to improve the CacheDB performance.

Further Read:

Please refer to the following Sybase link and SAP notes for more details:

• KBA 2497132 - What is the simplest way to rebuild the MBO server cache database?


• KBA 2470858 - Error on SYS_UPGRADE_ROLE_ROLE when rebuilding SQL Anywhere database


• Sybase DBUNLOAD Utility official documentation

Do you still have Questions?

If you have any questions, feel free to comment in this blog post. Or you can post your questions in the community tag SAP Mobile Platform. or you can write up an incident under BC-SYB-SQA.