Managing regularized data rollup schedule and storage

Document ID : KB000028469
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

How to manage regularized data rollup storage and schedules in eHealth

Solution:

There are two ways we can manage the regularized data rollup processing in the Oracle DB.

Disable it entirely from ever functioning.
Extend the amount of regularized as-polled data points are stored

To accomplish option 1 it requires accessing the SQLPlus prompt and running some DB commands.

To accomplish option 2 it requires $NH_USER command line access to run the nhModifyTimeAlignSettings command.

Option 1: Disabling regularized data rollups

WARNING: Doing so should only be done when sufficient disk space is available to store the data.

Connect to the eHealth SQLPlus prompt in the eHealth Oracle DB:

A - eHealth release 6.3.1 or newer:

SqlPlus $NH_USER/NH_USER_Password@$NH_DB_CONNECT_STRING

- Where $NH_USER is default ehealth install owner admin user
- Where NH_USER_Password is the $NH_USER user password. Use nhManageUsers command detailed below to check password if unknown.
- Where you also must add '@$NH_DB_CONNECT_STRING'
- If unsure what the connect string is use this command to determine it:

env | grep -i connect

B - eHealth releases older than 6.3.1:

SqlPlus $NH_USER/NH_USER_Password

- Where $NH_USER is default ehealth install owner admin user
- Where NH_USER_Password is the $NH_USER user password. Use nhManageUsers command detailed below to check password if unknown.

If unsure what equivalent user is to be used for 'ehealth', run 'echo $NH_USER'.
If the response is 'george' the entry in the command would be 'george'.

There are some situations where the $NH_USER Pasword has been changed. In situations like that where the user may be 'ehealth', the password is not the same. So using 'ehealth/ehealth' will fail. In such situations, if unsure what the correct password for the user is, this command can be run to help identify it:

nhManageUsers -user <userName> -showPass

For example this is a default eHealth user that hasn't been changed:

$ nhManageUsers -user ehealth -showPass
ehealth

The next step is to identify a job in Oracle and disable it. The job we're seeking has a job ID that is needed to disable it.

The job we seek is labeled "nh_reg_process.maintain_partitions". To find it and its Job ID execute this SQLPlus command:

select what,job from user_jobs;

The results will look similar to this as an example:

SQL> select what,job from user_jobs;
WHAT
--------------------------------------------------------------------------------
       JOB
----------
nh_reg_process.load_nh_date_time_dim;
        18
nh_regdata_schema.add_reg_partitions(TRUE);
        22
nh_reg_process.load_nh_date_dim;
        20
 
WHAT
--------------------------------------------------------------------------------
       JOB
----------
NH_VC_MAIN.ADDNODEPARTITION();
        21
nh_reg_process.maintain_partitions(TRUE);
        23

We can see each job listed and its ID below it. The Job ID we're interested in for this example is 23.

We now take the job ID and run this SQLPlus command:

exec dbms_job.broken(23,TRUE);

Of course substitute your systems job ID for the one in the example above (the value 23).

How do we confirm the change is made? We run this SQLPlus query:

select job, what, next_date, broken from user_jobs;

The output will look similar to this:

 
SQL> select job, what, next_date, broken from user_jobs;
       JOB
----------
WHAT
--------------------------------------------------------------------------------
NEXT_DATE B
--------- -
        18
nh_reg_process.load_nh_date_time_dim;
23-SEP-14 N
        22
nh_regdata_schema.add_reg_partitions(TRUE);
23-SEP-14 N
 
       JOB
----------
WHAT
--------------------------------------------------------------------------------
NEXT_DATE B
--------- -
        20
nh_reg_process.load_nh_date_dim;
23-SEP-14 N
        21
NH_VC_MAIN.ADDNODEPARTITION();
 
       JOB
----------
WHAT
--------------------------------------------------------------------------------
NEXT_DATE B
--------- -
23-SEP-14 N
        23
nh_reg_process.maintain_partitions(TRUE);
01-JAN-00 Y

Look for the nh_reg_process.maintain_partitions entry. Note the value after '01-JAN-00'. If we see a Y present it indicates the job is disabled and it will not run the next time it is called.

To enable the job once more the command would be, using the above examples:

exec dbms_job.broken(23,FALSE);

To validate the job is disabled look for an N instead of a Y value in the output of the command:

select job, what, next_date, broken from user_jobs;

Option 2: Extend the amount of regularized as-polled data points are stored

Taking this step can be used to limit the amount of data stored, or it can be used to extend the amount of time the data is stored. Again, great care should be used here as this will directly impact the DB size, performance and can cause a down eHealth system if care is not taken.

The nhModifyTimeAlignSettings command run as the $NH_USER is what we call to change the settings.

Run this command to see its full usage:

nhModifyTimeAlignSettings -h

Run this command to see the current settings:

nhModifyTimeAlignSettings -list

As an example to set all of your Regularized data retention to a year of more for base, hourly and daily retention you would run the commands:

nhModifyTimeAlignSettings -baseDuration 365
nhModifyTimeAlignSettings -hourlyDuration 52
nhModifyTimeAlignSettings -daillyDuration 52

Note this only modifies the amount of time the data is stored in its various forms. It does not disable the processing of the data.