Setting up the WorkZone database

Before you deploy the WorkZone application, you will need to prepare a database instance to run it.

Prerequisites

An empty Oracle database with Oracle Text must exist

An empty Oracle database with Oracle Text must exist. See supported Oracle versions in the Support Matrix.

In addition to the tablespace that is created by the Oracle installation of the database, you need one more tablespace named DATA. WorkZone data will be stored in this tablespace. The size of this tablespace depends on the amount of cases, record, names, and processes you need to store. See Database and application sizing guidelines for more information about the space requirements.

Note: We have transformed from using many tablespaces in WorkZone’s Oracle deployment to using only one tablespace, the DATA tablespace. If you want to be more granular in your storage segmentation of the database, please see Manually creating tablespaces.

WorkZone Database (DB) Upgrade container

To install the WorkZone database, you must run the WorkZone DB upgrade container and environment variables for it. The DB Upgrade container creates the schemas and tablespaces that are needed for running WorkZone. The DB Upgrade container is run as part of the Helm/werf deployment scripts, so you basically need to run the werf deployment and then the database schemas will be populated before installing the rest of WorkZone. See Installation using werf and Helm.

Environment variable	Description
WORKZONE_DB_HOSTNAME	The hostname for the database server.
WORKZONE_DB_PORT	The port number to use. Normally it is 1521 or 1522.
WORKZONE_DB_SID	The database SID or service name.
WORKZONE_DB_DSN	DB01. Do not change.
WORKZONE_DB_PROTOCOL	TCPS. Do not change.
WORKZONE_DB_SERVER_MODE	NONE. Do not change.
WORKZONE_DB_SYS_USERNAME	admin or sys (the database administrator schema).
WORKZONE_DB_SYS_PASSWORD	password for admin or sys
WORKZONE_DB_SJUSER_USERNAME	sjsysadm (the WorkZone database administrator user). Do not change.
WORKZONE_DB_SJUSER_PASSWORD	<Password>. The password of sjsysadm. Contact your organization's DBA about the password requirements.
WORKZONE_DB_TYPE	Enter NORMAL, which is the default, or ARCHIVEDB for an archive database. If you specify ARCHIVEDB, remember to specify the WORKZONE_ARCHIVE_PERIOD variable.
WORKZONE_ARCHIVE_PERIOD	Enter the period for the archive database that you want to install or upgrade. The default is 0, which is the NORMAL database.
WORKZONE_TABLESPACEMAP	DATA (Default)
WORKZONE_CULTURES	Cultures to use. Separate the cultures by semicolon, for example da-DK;en-GB. The first culture is the default culture.
WORKZONE_CORPORATE_ACCESS_CODE	Y or N to use corporate access code.
WORKZONE_FREETEXTINDEX	Y or N to create the freetext on new the new database. Set it to Y if you start on an empty database. If you have to load data/import data from an old database, set it to N because it is better to create the indexes after all data is loaded/imported.

Use the below environment variables.

Example:


WORKZONE_DB_HOSTNAME: "20.229.71.246"
WORKZONE_DB_PORT: "1521"
WORKZONE_DB_SID: DB01
WORKZONE_DB_DSN: DB01
WORKZONE_DB_PROTOCOL: TCP
WORKZONE_DB_SERVER_MODE: DEDICATED
WORKZONE_DB_SERVER_CERT_DN:
WORKZONE_DB_SYS_USERNAME: sys
WORKZONE_DB_SYS_PASSWORD: ******
WORKZONE_DB_SJUSER_USERNAME: sjsysadm
WORKZONE_DB_SJUSER_PASSWORD: sjsysadmpassword
WORKZONE_TABLESPACEMAP: DATA
WORKZONE_CULTURES: "en-GB;da-DK;ja-JP;de-DE"
WORKZONE_CORPORATE_ACCESS_CODE: "true"
WORKZONE_FREETEXTINDEX: "true"
WORKZONE_CONFIGURATION: AllFeatures_Standard

Manually creating tablespaces

If you want to control the tablespaces, you can create them manually. This section provides guidance on manually definition of tablespaces, and is only needed in case of there is a requirement to do so.

Physical and logical tablespaces

A WorkZone database consists of a number of logical tablespaces. The logical tablespaces must be mapped to one or more physical tablespaces.

If you want to use the database for test purposes, you only need a few physical tablespaces, but if you want to use it for production including a large amount of data, it is recommended that each logical tablespace is mapped to its own physical tablespace.

To use different configurations of the logical tablespaces, depending on the type of data to be saved in the tablespaces, a number of logical tablespaces are used. This makes it possible to optimize Oracle resources.

As a minimum you must create one physical tablespace.

Logical tablespaces in the WorkZone database

Logical name	Physical default name	Description
TS_STAM_DATA	SJ_STAM_DATA	Used for tables with a few rows, that is, metadata such as postal codes, countries, classification scheme and help registers.
TS_DATA	SJ_DATA	Used for tables with many rows, such as cases and documents, that is, data produced every day.
TS_STAM_INDEX	SJ_STAM_INDEX	Used for indexes with a few rows and thus for indexes on tables in the logical tablespace TS_STAM_DATA.
TS_INDEX	SJ_INDEX	Used for indexes with many rows and thus for indexes on tables in the logical tablespace TS_DATA.
TS_DATA_LOB	SJ_DATA_LOB	Used for ”LOB” segments, especially for the free text table register_text ”LOB”. ”LOB” stands for ”Large objects”. In the free text table all cases, documents, contacts, addresses and tasks are saved as XML in an ”LOB”, and this ”LOB” is free text indexed via Oracles Intermedia Text.
TS_DATA_IMT	SJ_DATA_IMT	Used for Oracle free text index for the WorkZone free text table register_text.
TS_IMPMGR	SJ_IMPMGR	Used for saving data from an external source in the format of the source, for instance data from the CPR register. If you do not use external sources, or if you only need to use very few data from external sources, you can use the logical tablespace TS_DATA instead of using this one.
TS_ARKIV	SJ_ARKIV_CAPTIA	Used for the tables for the archive. The archive is the place where all documents are saved. The documents are saved in binary format and possibly in text format, for instance, if a TIFF document has been OCR processed.
TS_ARKIV_LOB	SJ_ARKIV_CAPTIA_LOB	Used for the ”BLOB” segment of the archive, that is, the document itself.
TS_LOG	SJ_LOG	Used by the use log.
TS_DEBUG	SJ_DEBUG	Used for debug purposes.

If you run on an on-premises database and use the default naming TS, which is replaced by SJ, you can

use the setting:

WORKZONE_TABLESPACEMAP=#SAME

If you have more than one area that does not follow the naming convention, you need to supply the setting:

WORKZONE_TABLESPACEMAP=TS_DATA=<navn1>;TS_INDEX=<navn2>;TS_STAM_DATA=<navn3>;TS_STAM_INDEX=<navn4>;TS_DATA_LOB=<navn5>;TS_DATA_IMT=<navn6>;TS_ARKIV=<navn7>;TS_ARKIV_LOB=<navn8>;TS_IMPMGR=<navn9>;TS_LOG=<navn10>;TS_DEBUG=<navn11>

Where the different names (<navn1>,<navn2>, … <navn11>) do not need to be different. For example:

WORKZONE_TABLESPACEMAP=TS_DATA=DATA;TS_INDEX=DATA; TS_STAM_DATA=STAM_DATA;TS_STAM_INDEX=STAM_DATA;TS_DATA_LOB=DATA_LOB;TS_DATA_IMT=DATA;TS_ARKIV=ARKIV;TS_ARKIV_LOB=ARKIV_LOB;TS_IMPMGR=DATA;TS_LOG=LOG;TS_DEBUG=LOG

Recommended sizes of tablespaces

All tablespaces must be handled locally and they must be configured to uniform sizes.

The following table shows the recommended sizes of the tablespaces.

Note: The Ts_size column only lists a minimum value. The actual size that to use depends on the amount of data that must be saved in the database. It is not required to use autoextend.

Tablespace	Ts_size (minimum)	Next-size	Uni_size
SJ_STAM_DATA	40 MB	10 MB	128 KB
SJ_DATA	300 MB	50 MB	1 MB
SJ_STAM_INDEX	40 MB	10 MB	64 KB
SJ_INDEX	300 MB	50 MB	256 KB
SJ_DATA_LOB	100 MB	50 MB	4 MB
SJ_DATA_IMT	100 MB	10 MB	128 KB
SJ_IMPMGR	50 MB	10 MB	1 MB
SJ_ARKIV_CAPTIA	50 MB	10 MB	128 KB
SJ_ARKIV_CAPTIA_LOB	100 MB	50 MB	4 MB
SJ_LOG	100 MB	10 MB	4 MB
SJ_DEBUG	100 MB	10 MB	1 MB