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

Article Id: 17649
Status: Published
Updated On: 14-02-2018 08:06
Legacy Id: TEC616181
Products:
CA eHealth , CA eHealth , CA eHealth , CA eHealth , EHEALTH , 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: