Knowledge Base

1. Verify if the definition of VPX_HIST_STAT[1,2,3,4] has the column stat_val with datatype Numeric(38). If not, stop the VirtualCenter Server service. For more information, see Stopping, starting, or restarting vCenter services (1003895).
2. Back up the vCenter Server database. 
3. Run these SQL statements against the vCenter Server database:
   
   alter table VPX_HIST_STAT1 alter column stat_val numeric(38)
   alter table VPX_HIST_STAT2 alter column stat_val numeric(38)
   alter table VPX_HIST_STAT3 alter column stat_val numeric(38)
   alter table VPX_HIST_STAT4 alter column stat_val numeric(38)
    
4. Invoke these SQL statements to drop the existing views:
   
   drop view VPXV_HIST_STAT_DAILY
   drop view VPXV_HIST_STAT_WEEKLY
   drop view VPXV_HIST_STAT_MONTHLY
   drop view VPXV_HIST_STAT_YEARLY
     
5. Four views are created based on these modified vpx_hist_stat[1,2,3,4] tables. You need to re-create these views with the names:
   • VPXV_HIST_STAT_DAILY
   • VPXV_HIST_STAT_WEEKLY
   • VPXV_HIST_STAT_MONTHLY
   • VPXV_HIST_STAT_YEARLY
      
     Note: The definition of these views can be found in the schema definition file VCDB_<dbtype>.sql, where <dbtype> is mssql, oracle, or db2]. These files are normally found in C:\Program Files\VMware\Infrastructure\VirtualCenter Server on the vCenter Server.
      
     Copy and paste the create view command for VPXV_HIST_STAT_DAILY from the schema definition file and run it in an SQL query to re-create the view. 
      
     For example, the create view command for VPXV_HIST_STAT_DAILY looks like:
      
     /*==============================================================*/
     /* View: VPXV_HIST_STAT_DAILY                                   */
     /*==============================================================*/
     create view VPXV_HIST_STAT_DAILY as
     select   st.SAMPLE_TIME
          , st.SAMPLE_INTERVAL
          , sd.ID as STAT_ID
          , sd.NAME as STAT_NAME
          , sd.GROUP_NAME as STAT_GROUP
          , case sd.ROLLUP_TYPE
              when 0 then 'average'
              when 1 then 'maximum'
              when 2 then 'minimum'
              when 3 then 'latest'
              when 4 then 'summation'
              when 5 then 'none'
                end as STAT_ROLLUP_TYPE
              , sc.COUNTER_ID
          , si.MOID AS ENTITY
          , d.DEVICE_NAME
          , sdtd.DEVICE_TYPE_NAME
          , hs.STAT_VAL as STAT_VALUE
     from    VPX_SAMPLE_TIME1 st
         ,VPX_DEVICE d
             left outer join VPX_STAT_DEV_TYPE  sdt  on d.DEVICE_ID=sdt.DEVICE_ID
             left outer join VPX_STAT_DEV_TYPE_DEF sdtd on sdt.DEVICE_TYPE_ID=sdtd.DEVICE_TYPE_ID
         , VPX_STAT_COUNTER sc
         , VPX_STAT_DEF sd
         , VPX_HIST_STAT1 hs
         , VPX_STAT_ID si   
     where hs.counter_id=sc.COUNTER_ID
     and   sc.STAT_ID = sd.ID
     and   sc.DEVICE_ID=isnull(d.DEVICE_ID,1)
     and   hs.TIME_ID = st.TIME_ID
     and   sc.ENTITY_ID=si.ID
     ;
      
     Note: Run only one create view command query at a time. Running all 4 queries at the same time results in the error:
      
     Msg 156, Level 15, State 1, Procedure VPXV_HIST_STAT_DAILY, Line 41
     Incorrect syntax near the keyword 'create'.
      
6. Copy and paste the create view command for VPXV_HIST_STAT_WEEKLY from the schema definition file and run it in an SQL query to re-create the view. 
7. Copy and paste the create view command for VPXV_HIST_STAT_MONTHLY from the schema definition file and run it in an SQL query to re-create the view. 
8. Copy and paste the create view command for VPXV_HIST_STAT_YEARLY from the schema definition file and run it in an SQL query to re-create the view. 
9. Restart the vCenter Server service. For more information, see Stopping, starting, or restarting vCenter services (1003895).

Note: If you have upgraded to vCenter Server 4.1 and you are experiencing performance gap related issues, see Performance graphs display only the realtime data after upgrading to vCenter Server 4.1 (1030819).