Pgm nhiRegStats: Sql Error occurred during operation select r.sample_time, r.element_id

Products

• CA eHealth

Issue/Introduction

Problem:

Following error is seen in eHealth log.

06/06/14 15:34:18,Error,Pgm nhiRegStats[Active]: Sql Error occurred during operation select r.sample_time, r.element_id, nvl(r.delta_time,0),
r.var1,
r.var2,
r.var3,
r.var4,
r.var5,
r.var6,
r.var7,
r.var8,
r.var9,
r.var10,
r.var11,
r.var12,
r.var13,
r.var14,
r.var15,
r.var16,
r.var17,
r.var18,
r.var19,
r.var20,
r.var21,
r.var22,
r.var23,
r.var24,

 

Environment:

 

Microsoft Windows 2008 32 bit and 64 bit.

Solaris 10 and RHEL 5.6 and above.

 

 

 

Cause:

Please run the following command from eHealth server:

$NH_HOME/bin/sys/nhisql "select count(*) from nh_reg0_wip where delta_time < 0"

If the returned count is anything other than zero then the issue is with the poll times for some rows of data came in as negative values. Since a negative time is impossible, the nhiRegStats process which actions the data fails out continuously.

 

Resolution:

This issue can be fixed by changing those values to a positive number. Please follow the steps below to resolve.

1. Open a command line interface window and switch to a bash prompt
   bash
   The cursor will become a dollar sign ($)
2. Make a backup copy of the problem table before making changes
   exp $NH_USER/[email protected]$NH_DB_CONNECT_STRING tables=nh_reg0_wip file=nh_reg0_backup.dmp
3. Stop eHealth
   nhServer stop
4. Start sqlplus
   sqlplus $NH_USER/[email protected]$NH_DB_CONNECT_STRING
5. Update the problem table and commit changes with two commands, then exit
   sqlplus
   update NH_REG0_WIP set delta_time = 299 where delta_time < 0;
   commit;
   exit
6. Start eHealth
   nhServer start
7. Exit the Bash prompt
   exit

Note: You may experience a temporary performance issue after fixing the negative values, as the nhiRegStats process will start working on the accumulated data that it couldn't touch while the error was in effect. You will want to watch the DB drive to be sure that there are no space issue during this time. If you see that the drive is filling up, you can run the following command to clear out old archive logs.

e:/ehealth/bin/nhDbMaint -u $NH_USER -d $NH_RDBMS_NAME -deleteArchiveLogs

-----

Environment

Release: LHDEVC99000-6.3-eHealth-Live Health Upgrade Elements
Component: