Basic CDB information and monitoring

This is a summary of some facts about the Cache or Consolidated Database (CDB) that is part of SUP. Everything was tested on my 64-bit SUP 2.1 installation with local data tier. CDB and several other databases in SUP are SQL Anywhere databases. To learn more about them, I recommend http://dcx.sybase.com/index.html where you can get the reference documentation for all versions (also localized) and even discuss with the documentation authors if you register a free mySybase account.

ODBC Connections

• CDB is accessed via an ODBC connection that is registered with Windows.
• To administer your ODBC connections run
  %systemdrive%\Windows\System32\Odbcad32.exe
• Choose "Connection Pooling" tab and ensure that pooling is enabled for all "SQL Anywhere *" drivers
• Choose "System DSN" tab to see "default-cdb" Data Source Name (DSN) that is used by SUP
• You could configure some connection properties here, but to my knowledge this will be overwritten by the parameters you use to start the database servers hosting CDB (see CDB properties)

CDB Properties

• Change directory to
  "C:\Sybase\UnwiredPlatform\Servers\SQLAnywhere12\BIN32" and run
  dbisql -c dsn=default-cdb_32bit
  to open an interactive SQL console for the CDB
  [NB: Since SUP does not provide the 64-bit dbisql, we have to access CDB with the 32-bit ODBC connection]
• Run SQL command by entering
  "call sa_eng_properties()" and pressing F5
• The resulting table contains all runtime properties of CDB and you can mark & copy them
• One of the most interesting properties is "CommandLine", which gives the parameters that where used to launch the server that hosts CDB. These should be interpreted with
  http://dcx.sybase.com/1201/en/pdf/dbadmin12.pdf or
  http://dcx.sybase.com/1201/en/pdf/dbusage12.pdf

CDB Monitoring

• Open an interactive SQL console for the CDB as in CDB Properties
• Using SQL commands, enable request level logging, do something on the CDB, and disable it again
  
  
  • Enabling
    • call sa_server_option('request_level_logging','SQL+HOSTVARS+REPLACE');
    • call sa_server_option('request_level_log_file', 'cdb-requests.log');
      
      
  • Check Status
    • select property('RequestLogFile'), property('RequestLogging');
      
      
  • Disabling
    • call sa_server_option('request_level_logging','NONE');
    • call sa_server_option('request_level_log_file', '');
      
      
• You can analyze the data on any SQL Anywhere database (e.g. your local one) not necessarily CDB. To do this, run SQL commands
  
  
  • See Profiles, sorted
    • call sa_get_request_profile('cdb-requests.log');
    • select * from satmp_request_profile order by avg_ms desc;
      
      
  • See Specific Times
    • call sa_get_request_times('cdb-requests.log');
    • select * from satmp_request_time;
      
      
• In order to interpret the data you may need the CDB schema (see next part)

CDB Schema

• Run
  "C:\Sybase\UnwiredPlatform\Servers\SQLAnywhere12\BIN64\dbunload" -c dsn=default-cdb -r cdb-schema.sql -n
  to unload CDB schema (tables, procedures,...) into text file "cdb-schema.sql", no data is saved and database is unaffected. This can be done while the DB is running.
• Open the text file in your favorite editor. This contains the SQL statements required to create a skeleton (structure without data) of your CDB.

Have fun playing with this.