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

ARS 7.6.04 database views

jshyman

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?

  • 1. ARS 7.6.04 database views
    Jim Wilson

    You may be lucky enough to get a response from this forum, but I would recommend that you cross-post to an AR System forum.

  • 2. ARS 7.6.04 database views
    jshyman

    Jim, I thought so too but this doesn't seem to affect ARS at all. It has a HUGE impact on Analytics, though and a coworker of mine just ran into it using another reporitng platform. The views simply are not being updated and it is all because a "sh" was left out of a single database command.

     

    I'll try posting over there, though.

     

    Thanks, Jim!

  • 3. ARS 7.6.04 database views
    Jim Wilson

    Hi Jonathan,

     

    Did you ever get to the root cause of this?  Do you have anything that you can share with the Analytics community?

     

    Thanks & Regards,

    Jim

  • 4. ARS 7.6.04 database views
    jshyman

    Yes, Jim. The problem was a bug in ARS 7.6.04 that is fixed in 7.6.04 SP1.

     

    The ARS server was incorrectly setting the viewname in the arschema table after the first change to an overlaid form. After the first change to the overlay the view would not be updated with new information.

     

    There are three steps to fix it:

     

    1. Upgrade to 7.6.04 SP1 (or SP2, I assume) or get a HF from BMC support

    2. Fix the arschema table in the database by removing the sh_ prefix from the viewname column for all rows returned by the query:

     

    SELECT name, viewname, shviewname from arschema where viewname=shviewname

     

    3. Open each affected form in Dev Studio, add a field, save the form to regenerate the view correctly, remove the field you added, save the form.

     

    HTH,

     

    J.T.

  • 5. ARS 7.6.04 database views
    cavenger

    Thanks for clearly spelling out this issue. Do you happen to know if there is a recorded defect or official documented KB fix for this issue?

  • 6. ARS 7.6.04 database views
    jshyman

    As I understand it, this is corrected in Analytics 7.6.0.5