2. Prerequisites

2.1. Prepare the SQL-server

Make sure that the database ‘red_demo’ and the schema ‘emp’ exist.

In the database ‘red_demo’ in the schema ‘emp’ create an employee table of division WEST:

  • Each time a row is changed the column UpdatedOn is set to the current time.

  • Rows can be logically deleted by setting the Deleted flag to true.

  • We assume that rows are never physically deleted; therefore, an incremental load is safe.

 1USE red_demo;
 2
 3create table emp.Person
 4(
 5    EmpId bigint,
 6    FirstName varchar(100),
 7    LastName varchar(100),
 8    Salary decimal (8,2),
 9    Birthday date,
10    Department varchar(10),
11    CreatedOn datetime not null,
12    UpdatedOn datetime,
13    Deleted bit,
14    primary key (EmpId)
15);

Now create some entries:

1USE red_demo;
2
3insert into emp.Person (EmpId, FirstName, LastName, Salary, Birthday, Department, CreatedOn, UpdatedOn, Deleted)
4values
5('001', 'William', 'Cooper', 3500.64, '19720707', 'HR', convert(date, '2025-08-25'), null, null),
6('002', 'Cynthia', 'Wilkes', 4500.55, '19651010', 'IT', convert(date, '2025-08-30'), convert(date, '2025-09-01'), null),
7('003', 'Robert', 'Stanton', 6400.00, '19680726', 'IT', convert(date, '2025-08-30'), convert(date, '2025-09-01'), null);

Finally create a read only user RED_RO:

1CREATE LOGIN red_ro
2WITH PASSWORD = 'Sx123456';
3
4USE red_demo;
5CREATE USER red_ro FOR LOGIN red_ro;
6GRANT SELECT ON emp.person TO red_ro;

2.2. Prepare the Connection in Exasol

As SYS user create a connection in Exasol to the SQL-server.

1create connection RED_DEMO_MSSQL to 'jdbc:sqlserver://<ip_address>:<port>;trustServerCertificate=true' 
2user 'RED_RO' identified by 'Sx123456';
3
4grant connection RED_DEMO_MSSQL to R_OWNER_DBITSA;
5grant access on connection RED_DEMO_MSSQL to R_OWNER_DBITSA;

Finally test the connection:

1import from jdbc at RED_DEMO_MSSQL statement 'select 1';

2.3. Create an Application and Security Manager Account in Exasol

As SYS user create the user ‘ADAM’ and grant the roles R_APPMGR and R_SECMGR, which make this user Application Manager and Security Manager. With p_authentication NULL the user gets the default password, which must be changed at the first login.

 1execute script API_SCURTY.CREATE_USER (
 2      'ADAM'                            -- p_user_name
 3     ,'HU'                              -- p_user_type
 4     ,null                              -- p_authentication (default password)
 5     ,true                              -- p_private_sbx
 6     ,null                              -- p_quota
 7     ,'AppMgr. & SecMgr.'               -- p_comment
 8     ,'<your mail address>'             -- p_email
 9);
10
11grant R_APPMGR, R_SECMGR to ADAM;

If the RED framework has been configured properly, you receive a mail which tells you how to change the default password. Otherwise log in as ADAM with the default password and change it:

1alter user ADAM identified by "<new password>" replace "Firstpw123+";

Note

‘Firstpw123+’ is the default password for human users after the installation. Change it by setting the parameter sec_hu_def_passwd with API_SCURTY.ADM_SET_PARAM.

Finally, check the user settings in SCURTY.REP_ALL_USERS.

2.4. Open a Business Version

Changes to the PSA metadata (chapter 4) require an open business version. Check the current business version in REDSYS.ADM_CURRENT_BVERSION. If its status is CLOSED, open a new one as application manager:

1execute script API_REDADM.OPEN_BV(
2    '1.0.1'                      -- p_version
3   ,'RED quickstart'             -- p_desc
4);

2.5. Configure BDomains in RED

As application manager ‘ADAM’ add the two BDomains for the divisions EAST and WEST.

1execute script API_REDADM.ADD_BDOMAIN(
2    'DIV_EAST'                   -- p_bdomain
3   ,'file loads from div. EAST'  -- p_description
4);
5
6execute script API_REDADM.ADD_BDOMAIN(
7    'DIV_WEST'                   -- p_bdomain
8   ,'DB loads from div. WEST'    -- p_description
9);

Check the result in REDADM.RED_BDOMAINS.

2.6. Logon and Suspend the Framework

Logon to the RED-server and switch to the redsys user:

sudo -iu redsys

Assuming that you have prepared an environment named ‘DEV1’, switch to it and suspend the DB jobs:

[redsys@red01 ~]$ dev1
----------------------------------------------------------
current environment: DEV1
exit with CTRL-D
----------------------------------------------------------

DEV1 [redsys@red01 dev1]$ redctl suspend
DEV1 [redsys@red01 dev1]$