InfoSphere Optim

 View Only
  • 1.  Archiving Views

    Posted Thu July 16, 2020 12:08 PM
    Good day,

    When setting up access definition to archive views for DB2 LUW, we are getting the following error in the access definition "view error" in the Type field. Any idea what could cause this error. The views are created with a substring function.

    CREATE VIEW AUDH.vCR_AP_EV ( CR_AP_EV_ID,  CR_AP_NO, PD_TP_ID, EV_TP_ID, SRC_STM_ID, RANK, EFF_DTTM, OUTC_CODE, OUTC_DSC, OBJ_CR_AP_EV_ID, XML_REQ, XML_RESP, EXEC_ID, USR_ID, CNL_ID, SLTN_CMPT_VRSN_NBR, EV_OPRN ) AS SELECT CR_AP_EV_ID, CAST (SUBSTR(CR_AP_NO,1,length(CR_AP_NO)) AS VARCHAR(255)) as CR_AP_NO, PD_TP_ID, EV_TP_ID, SRC_STM_ID, RANK, EFF_DTTM, OUTC_CODE, OUTC_DSC, OBJ_CR_AP_EV_ID, XML_REQ, XML_RESP, EXEC_ID, USR_ID, CNL_ID, SLTN_CMPT_VRSN_NBR, EV_OPRN FROM AUDH.CR_AP_EV

    DB20000I  The SQL command completed successfully.

     

     


     



    ------------------------------
    David Mogashoa
    ------------------------------

    #InfoSphereOptim
    #Optim


  • 2.  RE: Archiving Views

    Posted Fri July 17, 2020 09:17 AM
    David,

    I believe that this is caused because your VIEW does not include all columns exactly as they are in the actual underlying table.  Imagine what an issue could be caused if Optim allowed you to archive just a part of the physical row and then deleted the actual row.  

    Hope this helps.
    Fred Booker
    fbooker@abmartin.com

    ------------------------------
    Fred Booker
    ------------------------------



  • 3.  RE: Archiving Views

    Posted Fri July 17, 2020 02:06 PM
    Fred's point about being careful with selecting a portion of the data via a view is a valid issue if this is meant to be a true archive. That being said, we have used views that don't entirely match the underlying table columns and it has worked in Access Definitions, so these errors may be for other reasons. 

    What error detail is showing in the PR0TOOL logs? That may give you some clues as to what Optim is not liking.

    ------------------------------
    Keith Tidball
    Progressive Insurance
    ------------------------------



  • 4.  RE: Archiving Views

    Posted Mon July 20, 2020 10:14 AM
    Hi Keith,

    Thanks for the respond.

    When it is created first time and all access granted to optim user it gives this error. Then after couple of times after being dropped and recreated then it works. Not sure why first time it is not working and thats what I was trying to find out.
    Thanks

    Regards,
    David


    ------------------------------
    David Mogashoa
    ------------------------------