Database requirements

Check the requirements for external relational databases used to store reporting data.

Db2 on Cloud

Configuration requirements

Supported version

Db2 on Cloud with a Standard or Enterprise plan

Page size

  • Required: 32 KB (default for Standard and Enterprise plans)
  • Tablespace requirement: 32 KB page size for the reporting schema

Code set

UTF-8

Connection capacity

  • Recommended minimum: 500 connections
  • Large configuration: 1000 connections

Ensure that the MAXAPPLS database configuration parameter supports the required concurrency.

Buffer pool configuration

  • Required: 32 KB buffer pool
  • Memory estimate: Approximately 16 GB multiplied by the number of wkc-bi-data-service pods for total memory (allocate 50-60% for the buffer pool).

Configuration example

-- Create database with 32K default page size and UTF-8
CREATE DATABASE reporting PAGESIZE 32 K USING CODESET UTF-8;

Schema setup

  • One dedicated schema (for example, REPORTING) must be created for reporting data.
  • One dedicated database user (for example, reporting_user) must be created with full privileges on the schema.
  • The reporting service:
    • Creates and drops tables, views, functions, triggers, and sequences.
    • Performs DML operations (INSERT, UPDATE, DELETE, SELECT).
  • The reporting service does not drop the schema itself.

Setup example:

-- Option 1 - Application-owned schema (recommended)
-- reporting_user owns and manages all objects within the reporting schema.
CREATE SCHEMA <reporting> AUTHORIZATION <reporting_user>;
GRANT CONNECT ON DATABASE TO USER <reporting_user>;

-- Option 2 - DBA-controlled schema with delegated object privileges
-- DBA retains schema ownership; reporting_user has full object-level control within the schema.
CREATE SCHEMA <reporting>;
GRANT USAGE ON SCHEMA <reporting> TO USER <reporting_user>;
GRANT CREATEIN ON SCHEMA <reporting> TO USER <reporting_user>;

PostgreSQL

Configuration requirements

Supported version

PostgreSQL 12 or later

Page size

8 KB (default, no configuration required)

Encoding

UTF8

Max connections (max_connections)

  • Recommended minimum: 500 sessions
  • Large configuration: 1000 sessions

Configuration example

-- Default (if cluster already UTF8):
CREATE DATABASE reporting;

-- Explicit UTF-8 encoding
CREATE DATABASE reporting ENCODING 'UTF8';

Schema setup

  • One dedicated schema (for example, reporting) must be created for reporting data.
  • One dedicated database user (for example, reporting_user) must be created with full privileges on the schema.
  • The reporting service:
    • Creates and drops tables, views, functions, triggers, and sequences.
    • Performs DML operations (INSERT, UPDATE, DELETE, SELECT).
  • The reporting service does not drop the schema itself.

Setup example:

-- Option 1 - Application-owned schema (recommended)
-- reporting_user owns and manages all objects within the reporting schema.
CREATE USER <reporting_user> WITH PASSWORD '<password>';
GRANT CONNECT ON DATABASE <db> TO <reporting_user>;
CREATE SCHEMA <reporting_schema> AUTHORIZATION <reporting_user>;

-- Option 2 - DBA-controlled schema with delegated object privileges
-- DBA retains schema ownership; reporting_user has full object-level control within the schema.
CREATE USER <reporting_user> WITH PASSWORD '<password>';
GRANT CONNECT ON DATABASE <db> TO <reporting_user>;
CREATE SCHEMA <reporting_schema>;
GRANT USAGE, CREATE ON SCHEMA <reporting_schema> TO <reporting_user>;
ALTER ROLE <reporting_user> SET search_path = <reporting_schema>;

Microsoft SQL Server and Microsoft Azure SQL Database

Configuration requirements

Supported version

  • Microsoft SQL Server 2022 or later
  • Microsoft Azure SQL Database (managed service, always current)

Page size

8 KB (default, no configuration required)

Collation

Case-insensitive UTF-8 collation (for example, Latin1_General_100_CI_AS_SC_UTF8)

Max connections

  • Default (32767) is sufficient
  • Application requirement: 500 connections (1000 for large configuration)

Configuration example:

-- Default (if instance collation already UTF8 CI):
CREATE DATABASE reporting;

-- Explicit case-insensitive UTF8 collation:
CREATE DATABASE reporting COLLATE Latin1_General_100_CI_AS_SC_UTF8;

Schema setup

  • One dedicated schema (for example, reporting) must be created.
  • One dedicated database user (for example, reporting_user) must be created with full privileges on the schema.
  • The reporting service:
    • Creates and drops tables, views, functions, triggers, and sequences.
    • Performs DML operations (INSERT, UPDATE, DELETE, SELECT).
  • The reporting service does not drop the schema itself.

Setup example:

-- Option 1 - Application-owned schema (recommended)
-- reporting_user owns and manages all objects within the reporting schema.
CREATE LOGIN <reporting_user> WITH PASSWORD = '<Password>';
USE <ReportingDb>;
CREATE USER <reporting_user> FOR LOGIN <reporting_user>;
CREATE SCHEMA <reporting> AUTHORIZATION <reporting_user>;
ALTER USER <reporting_user> WITH DEFAULT_SCHEMA = <reporting>;

-- Option 2 - DBA-controlled schema with delegated object privileges
-- DBA retains schema ownership; reporting_user has full object-level control within the schema.
CREATE LOGIN <reporting_user> WITH PASSWORD = '<Password>';
USE <ReportingDb>;
CREATE USER <reporting_user> FOR LOGIN <reporting_user>;
CREATE SCHEMA <reporting>;
GRANT CONTROL ON SCHEMA::<reporting> TO <reporting_user>;
ALTER USER <reporting_user> WITH DEFAULT_SCHEMA = <reporting>;