Installing HealthViews for PostgreSQL

Setting Up the HealthViews Schema

It is recommended that the HealthViews materialized views be located in their own schema, typically called healthviews. The schema should be owned by the role responsible for loading the HealthViews materialized views. Using an administrative role, type the following:

create schema healthviews;
alter schema healthviews owner to hv_user;

Installing the Stored Procedures

Topics:

Installing the hash64 Function
Installing the fn_hvtabstatscollector Function
Installing the fn_hvpartstatcollector Function

Once the healthviews database has been created, three functions need to be installed:

hash64
fn_hvtabstatcollector
fn_hvpartstatcollector

Scripts for all the stored procedures can be found in the \stored_procs folder in the HealthViews.zip file.

Installing the hash64 Function

HealthViews uses the hash64 function to create unique hashed values for ID fields. Using hashed values for ID fields allows for faster joins.

The hash64 function uses a compiled C object and is PostgreSQL version specific. Information Builders distributes a version compiled for PostgreSQL 9.3 and PostgreSQL 9.6. The compiled object can be found in a \9.3 or \9.6 subdirectory, depending on the version. A script to register the function (register.sh) can be found in the \source directory.

Copy the appropriate ibi_functions.so and register.sh files to a common directory. Then, set the permissions to execute by typing:

sudo chmod 777 ibi_functions.so
sudo chmod 777 register.sh

The register.sh shell script contains the following:

#!/bin/bash
PGHOME="$PWD"
UDFHOME="$PWD"
if [[ $# -ne 1 ]]; then
  echo "USAGE: register.sh <DB_NAME>"
  exit 1
fi
L_DBNAME=${1}
psql -d ${L_DBNAME} <<EOF
create or replace function hash64(text) returns int8
as '$UDFHOME/ibi_functions.so', 'hash64'
language C strict immutable;
EOF

It is necessary to execute register.sh as the postgres user. Ensure that there are appropriate permissions for the entire directory tree to the register.sh and ibi_functions.so files before proceeding.

Next, register the function by typing:

./register.sh [db_name]

where [db_name] is the name of the database being used by Omni-HealthData and HealthViews. When the script completes, there should be a hash64 function listed in the public schema for your database, as shown in the following image.

For PostgreSQL versions other than 9.3 and 9.6, the source files used to compile ibi_functions.so can be found in the \source directory. You will also need the PostgreSQL header files installed on your system. Use your package manager to install the header files appropriate for your version of PostgreSQL. For example, Debian distributions of PostgreSQL would use package postgresql-server-dev-9.5, which can be installed by typing:

sudo apt-get install postgresql-server-dev-9.5

Installing the fn_hvtabstatscollector Function

The fn_hvtabstatscollector function is used to compile statistics on the HealthViews data. It tracks the number of records in each HealthViews table and also the number of records added since the last time the table was updated.

The fn_hvtabstatscollector.sql script is provided for installing the fn_tabstatscollector function. Execute the script in the public schema. After running, fn_tabstatscollector should be listed as a stored procedure in the public schema, as shown in the following image.

Installing the fn_hvpartstatcollector Function

The fn_hvpartstatcollector function is used to collect statistics on any partitions in the healthviews database.

The fn_hvpartcollector.sql script is provided for installing the fn_hvpartstatcollector function. Execute the script in the public schema. After running, fn_hvpartstatcollector should be listed as a stored procedure in the public schema, as shown in the following image.

Other Setup Steps

A table, hv_availability, needs to be created in the public schema. The aa_hv_availability.sql script for this is provided in the setup_scripts directory. Run this script in the public schema prior to the first load of Healthviews.