6 Replies Latest reply: Mar 2, 2012 3:43 PM by Jonathan Shyman RSS

    ARS 7.6.04 database views

    Jonathan Shyman

      I'm working with a client who is on ARS 7.6.04 and I'm not seeing fields they've added to OOB forms in the database views. I've tracked this down to, I believe, a bug in ARS where the VIEWNAME is set with a prefix of SH_ (I think that stands for "status history") incorrectly. When a change is made to the form the ARS drops the SH_ view and rebuilds it and then does it again to create the correct SH_ view. The upswing is new fields do not appear in the database view for the form and therefore cannot be added to Analytics without using the T tables instead, which isn't a good practice.

       

      I've done some digging into this, and managed to duplicate it on a Linux/Oracle install as well as a Windows/MSSQL install, and I've opened a case with BMC to investigate it.

       

      For example:

       

      It seems that an overlaid form stops updating the view sometime after the first modification. I've been able to make multiple field additions and deletions to a form within a short time but then when I try days later it stops working as expected.

       

      When I watch the database I see the view matching the form being updated. In this example, I used a form I'd not touched before: PBM:Known Error. The SQL that is run against the database when I added two character fields looks like this:

       

      DROP VIEW PBM_Known_Error

      CREATE VIEW PBM_Known_Error (Sys_Known_Error_ID,Submitter,Submit_Date,Assigned_To,Last_Modified_By,Last_Modified_Date,Known_Error_Status,Short_Description,Assignee_Groups,InstanceId,Vendor_Assignee_Groups,RootRequestName,Created_from_Template,z1D_Task_instance_Id,Product_Categorization_Tier_1,Product_Categorization_Tier_2,Product_Categorization_Tier_3,Department,Site_Group,Region,LookupKeyword,Product_Name,Manufacturer,Product_Model_Version,Site,Reason_Code,Reason_Description,Return_Code,Assignee_Id,Show_For_Process,Enable_Assignment_Engine,Assignee_Select_Form,Return_Code_Manager,Reason_Code_Manager,Reason_Description_Manager,Assignee_Id_Manager,Assignee_Select_Form_Manager,Show_For_Process_Manager,z1D_RelationAction,DataTags,TicketType,Association_Description,ServiceCI,PKE_CI,ServiceCI_ReconID,PKE_CI_ReconID,z1D_CI_FormName,Auto_Close_Corrected_SEC,Last_Corrected_Date,PKE_CI_FormName,Description,Company,Assigned_Support_Organization,Last_Name,First_Name,Internet_Email,Phone_Number_Business,Categorization_Tier_1,Categorization_Tier_2,Categorization_Tier_3,Support_Group_ID,Stastus_Reason,Detailed_Decription,Resolution,Urgency,Impact,Priority,Priority_Weight,Assigned_Group,Assignee,Problem_Investigation_ID,Investigation_Justification,Vendor_Phone,Assigned_Support_Company,Vendor_Name,Support_Group_ID_Pbm_Mgr,Vendor_Responded_On,Vendor_Ticket_Number,GA_Date_Time,Generic_Categorization_Tier_1,Generic_Categorization_Tier_2,Generic_Categorization_Tier_3,View_Access,First_Reported_On,Investigation_Driver,Support_Company_Pblm_Mgr,Support_Organization_Pblm_Mgr,Assigned_Group_Pblm_Mgr,Assignee_Pblm_Mgr,Problem_Manager_Login,Assignee_Login,PreRelease_Date_Time,Vendor_Contact,Temporary_Workaround,Workaround_Determined_On,Reported_to_Vendor,Patch_Last_Build_ID,Corrective_Model_Version,Infrastructure_Chg_Initiated,Known_Error_ID,Category,Reproduceable_Flag,Assign_To_Vendor,Problem_Investigation_ID_Hold,Searchable,Target_Resolution_Date,Assignment_Method,Corrective_Action_Determined,Publish_Date,Expiry_Date,Archive_Date,Character_Field,Character_Field2) AS SELECT T2190.C1,C2,C3,C4,C5,C6,C7,C8,C112,C179,C60900,C10000001,C10003001,C10003005,C200000003,C200000004,C200000005,C200000006,C200000007,C200000012,C230000009,C240001002,C240001003,C240001005,C260000001,C300270800,C300270900,C300271000,C300271200,C301090500,C301290300,C301321300,C301323700,C301323900,C301324100,C301324300,C301324500,C301326100,C301507500,C301600300,C301626500,C301629100,C303497300,C303497400,C303519300,C303522900,C303523900,C303598200,C303598300,C303790700,C1000000000,C1000000001,C1000000014,C1000000018,C1000000019,C1000000048,C1000000056,C1000000063,C1000000064,C1000000065,C1000000079,C1000000150,C1000000151,C1000000156,C1000000162,C1000000163,C1000000164,C1000000169,C1000000217,C1000000218,C1000000232,C1000000233,C1000000239,C1000000251,C1000000396,C1000000427,C1000000561,C1000000652,C1000000656,C1000000744,C1000000745,C1000000746,C1000000761,C1000000797,C1000000798,C1000000834,C1000000835,C1000000837,C1000000838,C1000000839,C1000000841,C1000000843,C1000000854,C1000000855,C1000000866,C1000000878,C1000000942,C1000000943,C1000000964,C1000000979,C1000000984,C1000000985,C1000000987,C1000001078,C1000001469,C1000001571,C1000001600,C1000002496,C1000003563,C1000003564,C1000003565,C900000001,C900000002 FROM T2190

      UPDATE arschema SET viewName='PBM_Known_Error' WHERE schemaId=2753

      UPDATE arschema SET viewName='PBM_Known_Error' WHERE schemaId=2190

      DROP VIEW SH_PBM_Known_Error

      CREATE VIEW SH_PBM_Known_Error (Sys_Known_Error_ID,Assigned_TIME,Assigned_USER,Scheduled_For_Correction_TIME,Scheduled_For_Correction_USER,Assigned_To_Vendor_TIME,Assigned_To_Vendor_USER,No_Action_Planned_TIME,No_Action_Planned_USER,Corrected_TIME,Corrected_USER,Closed_TIME,Closed_USER,Cancelled_TIME,Cancelled_USER) AS SELECT entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4,T5,U5,T6,U6 FROM H2190

      UPDATE arschema SET shViewName='SH_PBM_Known_Error' WHERE schemaId=2753

      UPDATE arschema SET viewName='SH_PBM_Known_Error' WHERE schemaId=2190

       

      Note that it starts by dropping the PBM_KNOWN_ERROR view and then recreating it with the two new fields C900000001 and C900000002. It then drops and recreates SH_PBM_KNOWN_ERROR.

       

      Now, here's the same transaction on HPD:Help Desk which is _not_ updating the view:

       

      DROP VIEW SH_HPD_Help_Desk

      CREATE VIEW SH_HPD_Help_Desk (Entry_ID,Submitter,Submit_Date,Assignee_Login_ID,Last_Modified_By,Last_Modified_Date,Status,Short_Description,Assignee_Groups,InstanceId,Vendor_Assignee_Groups,z1G_DefaultVUI,RootRequestName,Created_from_Template,Product_Categorization_Tier_1,Product_Categorization_Tier_2,Product_Categorization_Tier_3,Department,Site_Group,Region,LookupKeyword,Product_Name,Manufacturer,Product_Model_Version,Escalated_,Site,Reason_Code,Reason_Description,Return_Code,Assignee_Id,SRAttachment,Created_By,Show_For_Process,Command,Enable_Assignment_Engine,Assignee_Select_Form,SRInstanceID,HPD_Template_ID,Original_Last_Modified_Date,Original_Last_Modified_By,zD_NextDueDate_Time,PreviousStatus,StageCondition,CurrentStage,CurrentStageNumber,UnknownUser,SRMS_Registry_Instance_ID,SRMSAOIGuid,SRID,TemplateID,DataTags,SLMLookupTblKeyword,Last__Assigned_Date,TicketType,Association_Description,Created_From_flag,Flag_Create_Request,Component_ID,mc_ueid,cell_name,SLMEventLookupTblKeyword,policy_name,status_incident,status_reason2,root_component_id_list,root_incident_id_list,Impact_OR_Root,bOrphanedRoot,OptionForClosingIncident,first_name2,last_name2,Login_ID,Global_OR_Custom_Mapping,use_case,BiiARS_01,BiiARS_02,BiiARS_03,BiiARS_04,BiiARS_05,z1D_TotalBreachedIncidentsCoun,z1D_TotalCriticalIncidentsCoun,ClientLocale,ServiceCI,HPD_CI,TemplateGUID,ServiceCI_ReconID,HPD_CI_ReconID,z1D_CI_FormName,CI,Service,z1D_Template_Name,INCAutoCloseResolved_Sec,z1D_VISFormView,z1D_VISProcessFlowView,z1D_VISTargetForm,z1D_VendorAccess,Direct_Contact_Corporate_ID,KMSGUID,HPD_CI_FormName,Kickback_Count,Last_Kickback_Date,EffortDurationHour,z2AF_Work_Log01001,z2AF_Work_Log02001,z2AF_Work_Log03001,Description,Company,Country,State_Province,City,Organization,Assigned_Support_Organization,Full_Name,Last_Name,First_Name,Middle_Initial,Contact_Client_Type,VIP,Contact_Sensitivity,Country_Code,Area_Code,Local_Phone,Extension,Desk_Location,Mail_Station,Street,Zip_Postal_Code,GEOnet,Internet_E_mail,Corporate_ID,Phone_Number,Categorization_Tier_1,Categorization_Tier_2,Categorization_Tier_3,HR_ID,Site_ID,Assigned_Group_ID,Person_ID,Contact_Company,Service_Type,CI_Tag_Number,Status_PPL,Additional_Location_Details,Status_Reason,Detailed_Decription,Resolution,Incident_Number,Urgency,Impact,Priority,Priority_Weight,Cost_Center,Reported_Source,Assigned_Group,Assignee,Vendor_Phone,Assigned_Support_Company,Shifts_Flag,Assigned_Group_Shift_Name,Assigned_Group_Shift_ID,Owner_Support_Organization,Vendor_Name,Owner_Group,Owner_Support_Company,Owner_Group_ID,Time_Zone,Total_OLA_AcknowledgeEsc_Level,Total_Escalation_Level,Total_OLA_Resolution_Esc_Level,Reported_Date,Responded_Date,Last_Acknowledged_Date,Last_Resolved_Date,Closed_Date,Last_SLA_Hold_Date,Re_Opened_Date,SLA_Hold,Onwer_Group_Uses_SLA,Assigned_Group_Uses_OLA,Last_Date_Duration_Calculated,Effort_Time_Spent_Minutes,Vendor_Ticket_Number,Owner,Owner_Login_ID,Total_Time_Spent,Generic_Categorization_Tier_1,Generic_Categorization_Tier_2,Generic_Categorization_Tier_3,Vendor_Contact,Incident_Association_Type,Original_Incident_Number,Reported_to_Vendor,Patch_Last_Build_ID,Infrastructure_Chg_Initiated,Category,Reproduceable_Flag,Assign_To_Vendor,Broadcasted_Flag,Web_Incident_ID,SLM_Priority,OLA_Hold,SLA_Responded,Acknowledgment_Start_Date,Resolution_Start_Date,EH,DR,SLA_Res_Business_Hour_Seconds,Assignment_Method,Resolution_Category,Next_Target_Date,SLM_Status,Direct_Contact_Internet_E_mail,Vendor_Organization,Vendor_Group,Vendor_Group_ID,Vendor_Email,Vendor_Responded_On,Vendor_Last_Name,Vendor_First_Name,Vendor_Person_ID,Vendor_Resolved_Date,Group_Transfers,Total_Transfers,Individual_Transfers,Vendor_Login_ID,Vendor_Assignment_Status,Resolution_Method,Resolution_Category_Tier_2,Resolution_Category_Tier_3,Closure_Product_Category_Tier1,Closure_Product_Category_Tier2,Closure_Product_Category_Tier3,Closure_Product_Name,Closure_Product_Model_Version,Closure_Manufacturer,SLA_Breach_Reason,SLA_Breach_Exception,Closure_Source,Satisfaction_Rating,Estimated_Resolution_Date,Required_Resolution_DateTime,Inbound,Outbound,Direct_Contact_Company,Direct_Contact_Last_Name,Direct_Contact_First_Name,Direct_Contact_Middle_Initial,Direct_Contact_Phone_Number,Direct_Contact_Organization,Direct_Contact_Department,Direct_Contact_Region,Direct_Contact_Site_Group,Direct_Contact_Site,Direct_Contact_Person_ID,Direct_Contact_Street,Direct_Contact_Country,Direct_Contact_State_Province,Direct_Contact_City,Direct_Contact_Zip_Postal_Code,Direct_Contact_Time_Zone,Direct_Contact_Desk_Location,Direct_Contact_Mail_Station,Direct_Contact_Location_Detail,Direct_Contact_Site_ID,Direct_Contact_Country_Code,Direct_Contact_Area_Code,Direct_Contact_Local_Number,Direct_Contact_Extension,z2AF_Work_Log01002,z2AF_Work_Log02002,z2AF_Work_Log03002,Attachment,Support_Group_Role,Character_Field,Character_Field2) AS SELECT T2116.C1,C2,C3,C4,C5,C6,C7,C8,C112,C179,C60900,C3004100,C10000001,C10003001,C200000003,C200000004,C200000005,C200000006,C200000007,C200000012,C230000009,C240001002,C240001003,

      C240001005,C250000023,C260000001,C300270800,C300270900,C300271000,C300271200,CO300615200||';'||CC300615200||';'||C300615200,C300617700,C301090500,C301284400,C301290300,C301321300,

      C301368700,C301389272,C301390090,C301391782,C301412900,C301540300,C301541000,C301541600,C301541700,C301550900,C301571900,C301572000,C301572100,C301572200,C301600300,C301610100,

      C301612200,C301626500,C301629100,C301667500,C301674600,C301734000,C301735100,C301736700,C301788500,C301807600,C301809900,C301810000,C301827100,C301827300,C301920700,C301920800,

      C301920900,C301921000,C301921100,C301921200,C301921300,C301921400,C301921500,C301921600,C301921700,C301921800,C301921900,C303070100,C303070200,C303356300,C303497300,C303497400,

      C303497500,C303519300,C303522900,C303523900,C303544200,C303544300,C303558600,C303595900,C303601600,C303601700,C303616500,C303720800,C303755200,C303758300,C303790700,C303898800

      ,C303901000,C303979600,CO304247160||';'||CC304247160||';'||C304247160,CO304247170||';'||CC304247170||';'||C304247170,CO304247180||';'||CC304247180||';'||C304247180,C1000000000,C1000000001,

      C1000000002,C1000000003,C1000000004,C1000000010,C1000000014,C1000000017,C1000000018,C1000000019,C1000000020,C1000000022,C1000000026,C1000000027,C1000000028,C1000000029,

      C1000000030,C1000000031,C1000000035,C1000000036,C1000000037,C1000000039,C1000000046,C1000000048,C1000000054,C1000000056,C1000000063,C1000000064,C1000000065,C1000000069,

      C1000000074,C1000000079,C1000000080,C1000000082,C1000000099,C1000000109,C1000000118,C1000000145,C1000000150,C1000000151,C1000000156,C1000000161,C1000000162,C1000000163,

      C1000000164,C1000000169,C1000000188,C1000000215,C1000000217,C1000000218,C1000000239,C1000000251,C1000000296,C1000000298,C1000000300,C1000000342,C1000000396,C1000000422,

      C1000000426,C1000000427,C1000000541,C1000000557,C1000000558,C1000000559,C1000000560,C1000000561,C1000000562,C1000000563,C1000000564,C1000000565,C1000000566,C1000000567,

      C1000000571,C1000000572,C1000000631,C1000000642,C1000000652,C1000000715,C1000000716,C1000000731,C1000000744,C1000000745,C1000000746,C1000000854,C1000000869,C1000000875,

      C1000000878,C1000000942,C1000000964,C1000000984,C1000000985,C1000000987,C1000001025,C1000001165,C1000001259,C1000001288,C1000001296,C1000001317,C1000001319,C1000001445,

      C1000001446,C1000001555,C1000001600,C1000002488,C1000002613,C1000003009,C1000003302,C1000003662,C1000003663,C1000003664,C1000003752,C1000003753,C1000003754,C1000003755,

      C1000003756,C1000003757,C1000003761,C1000003764,C1000003765,C1000003779,C1000003781,C1000003888,C1000003889,C1000003890,C1000003891,C1000003892,C1000003893,C1000003894,

      C1000003895,C1000003896,C1000003897,C1000003898,C1000003899,C1000003988,C1000005261,C1000005661,C1000005735,C1000005736,C1000005781,C1000005782,C1000005783,C1000005784,

      C1000005785,C1000005786,C1000005787,C1000005788,C1000005789,C1000005790,C1000005791,C1000005897,C1000005898,C1000005899,C1000005900,C1000005901,C1000005902,C1000005903,

      C1000005904,C1000005905,C1000005906,C1000005908,C1000005909,C1000005910,C1000005911,CO1000005942||';'||CC1000005942||';'||C1000005942,CO1000005943||';'||CC1000005943||';'||C1000005943,

      CO1000005944||';'||CC1000005944||';'||C1000005944,CO1000005954||';'||CC1000005954||';'||C1000005954,C1000005970,C900000001,C900000002 FROM T2116 LEFT OUTER JOIN B2116  ON T2116.C1 = B2116.C1

      UPDATE arschema SET viewName='SH_HPD_Help_Desk' WHERE schemaId=2750

      UPDATE arschema SET viewName='SH_HPD_Help_Desk' WHERE schemaId=2116

      DROP VIEW SH_HPD_Help_Desk

      CREATE VIEW SH_HPD_Help_Desk (Entry_ID,New_TIME,New_USER,Assigned_TIME,Assigned_USER,In_Progress_TIME,In_Progress_USER,Pending_TIME,Pending_USER,Resolved_TIME,Resolved_USER,Closed_TIME,Closed_USER,Cancelled_TIME,Cancelled_USER) AS SELECT entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4,T5,U5,T6,U6 FROM H2116

      UPDATE arschema SET shViewName='SH_HPD_Help_Desk' WHERE schemaId=2750

      UPDATE arschema SET viewName='SH_HPD_Help_Desk' WHERE schemaId=2116

       

       

      Note that this time the view SH_HPD_Help_Desk was dropped and recreated and NOT HPD_Help_Desk.

      Note also that the new view, with the new fields, is then DROPPED and recreated again.

       

      In short, the HPD_Help_Desk view isn't touched.

       

      Has anyone else seen this behavior?