Dong Shin 10.29.2013

  • weird MySQL database problem on-site, but resolved – details at Phil’s blog
  • very slow query response in RA retrieving list of projects on-site
    • create a buffer table?
    • enable cache? – http://www.cyberciti.biz/tips/enable-the-query-cache-in-mysql-to-improve-performance.html
  • working on Obligations Update query – update_obligations_queries.sql
    • updated queries to work on all obligations, not just EA
    • remove obligations update part in the python script!
    • create two views to stage the data for update
      • __view_obligations_by_contracts - monthly obligations data by requisition id in COGNOS data
      • __view_obligations_by_req_id - monthly obligations summary data by req id used in FA
    • update query for Obligations/Outlays table
      • UPDATE obligations_outlays oo
        LEFT JOIN __view_obligations_by_req_id AS vv
        ON vv.bc_uid = oo.funding_id
        SET oo.month_1 = vv.month_1, oo.month_2 = vv.month_2, oo.month_3 = vv.month_3, oo.month_4 = vv.month_4, oo.month_5 = vv.month_5, oo.month_6 = vv.month_6,
        oo.month_7 = vv.month_7, oo.month_8 = vv.month_8, oo.month_9 = vv.month_9, oo.month_10 = vv.month_10, oo.month_11 = vv.month_11, oo.month_12 = vv.month_12
        WHERE type = ‘Reported FACTS Obligated $:’
        AND year=2013 AND year_count=1