Frequently asked questions about the enterprise database

There are several common questions concerning the deployment and configuration of the enterprise database.

1. If the initial High Availability (HA) configuration was performed using DDL, are there any other permissions required to perform the upgrade with DDL?

   The answer is no, as long as you have applied the DDL migration scripts (fixupxx.sql) for that particular HCL Traveler release and the appGrants.sql. It is recommended that you run the appGrants.sql every time DDL migration scripts are executed, as this will configure permissions for new tables if any have been added in that release.

2. What is the default value for the NTS_AUTO_DBSCHEMA parameter in notes.ini?

   The default is true, which means HCL Traveler will perform automatic schema migration.

3. Is it possible to switch from DDL to Automatic Schema migration when performing an HCL Traveler upgrade? If so, how?

   Yes, you can switch to automatic schema migration by removing the configuration parameter NTS_AUTO_DBSCHEMA=false from notes.ini.

4. What permissions are required for switching from DDL to Automatic Schema migration during an upgrade?

   If you are switching to automatic schema migration, you cannot use the LNTUSER as it is for DB2, MySQL, or SQL Server. This user was only granted specific permissions for HCL Traveler operations, and does not include permissions for modifying schema or altering the database. As a result, a schema upgrade will fail and the HCL Traveler server will not start. The DB administrator must configure LNTUSER with schema privileges to create/alter/drop tables, columns, primary keys, and indexes, as well as grant database alter permission. LNTUSER, by default, should have INSERT, UPDATE, SELECT and DELETE permissions for all database objects (tables).

   For SQL Server the schema authorization must be altered:

   ALTER AUTHORIZATION ON SCHEMA :: LNT TO LNTUSER

   For DB2, LNTUSER must have permission to create and drop schema:

   GRANT CREATEIN, ALTERIN, DROPIN ON SCHEMA LNT TO LNTUSER

   For MySQL, LNTUSER must have the following permissions:

   grant CREATE, ALTER, DROP, INDEX on TRAVELER.* to 'LNTUSER'@'%';

   Note: Please refer to the SQL server or DB2 documentation for more information on setting these permissions, as well as the Database permissions topic in the HCL Traveler documentation.
5. What DDL migration script(s) are needed for upgrading HCL Traveler HA to the latest release?
   Start with the fixup#.sql listed below and run all remaining fixup#.sql scripts. For example, if upgrading to Traveler 14.0.1 from 14.0.0.0, run the fixup36.sql script. If upgrading to Traveler 14.0.1 from Traveler 9.0.1.21, run the fixup scripts, in order, listed for 10.0.1.2, 11.0.0.0, 14.0.0.0 and ending with 14.0.1.0 (e.g fixup33.sql, fixup34.sql, fixup35.sql and fixup36.sql). When this completes, you should also run appGrants.sql to ensure any new schema elements have the appropriate access rights.

   Table 1. Migration scripts

   Migration from...	Start with script...
   14.0.1.0	fixup36.sql
   14.0.0.0	fixup35.sql
   11.0.0.0	fixup34.sql
   10.0.1.2	fixup33.sql
   9.0.1.21	fixup32.sql
   9.0.1.3	fixup20.sql
   9.0.1	fixup12.sql
   9.0.0.1	fixup11.sql
   9.0.0.0	fixup10.sql
   8.5.3 Upgrade Pack 2	fixup8.sql
   8.5.3 Upgrade Pack 1	fixup6.sql
   Earlier than 8.5.3 Upgrade Pack 1	fixup5.sql

6. What are the recommended settings for DB2 transaction logs?

   By default, the size of the DB2 transaction log is small, and an HCL Traveler server under heavy load can easily exceed the default size. As a result, it is recommended to set the transaction log to a larger size during HA setup. You should also monitor the DB2 transaction log space and check the utilization percentage. If it is high, or if you see DB2 transaction related errors SQLCODE=-964, SQLSTATE=57011, SQLERRMC=null, then adjust the following log settings to higher values: LOGFILESIZ, LOGPRIMARY, LOGSECOND. As a reference, the settings used during performance load testing were LOGFILSIZ (8192), LOGPRIMARY (10), and LOGSECOND (200).

   For example, perform the following commands on the DB2 server (or, if DB2 is configured with standby mirrors, on each DB2 server in the cluster):

   db2 update db cfg for traveler using LOGFILSIZ 8192 LOGPRIMARY 10 LOGSECOND 200
   db2stop force
   db2start
   db2 activate db traveler

7. When does a schema normally get changed?

   Schema changes occur only during major releases, upgrades, and/or to resolve a critical issue. Documentation will be provided for any fix pack or major release.

8. Can the enterprise database be setup for fail over?

   The enterprise database server can be mirrored, replicated, clustered or setup in an active-active configuration, however the Traveler servers in a Traveler HA pool must all connect to the same physical database server. When fail over occurs, all Traveler servers in the HA pool must fail over to the same secondary database server. Running a Traveler HA pool where the Traveler servers are connected to different physical database servers is not supported and may result in bad user experiences including missing data on their mobile devices and/or frequent re-syncing of data. For specific information about how to setup your enterprise database with fail over, see your database providers documentation.

9. How can we improve MySQL database performance?
   We recommend the INNODB_BUFFER_POOL_SIZE variable be changed first. This value can be set as high as 80% of physical memory for a server that is dedicated to running INNODB. Please refer to this article for greater detail. Next, we recommend setting the MAX_CONNECTIONS variable to the number of traveler servers in the high availability pool multiplied by 300. Finally, we recommend moving the DB files to separate high throughput drive.

   Note: Please refer to the SQL server, MySQL, or DB2 documentation, as well as the Database permissions topic, for more information on setting these permissions.