Anh’s Status 3/3/2016
Resolved INC0071250, INC0067892
Completed converting ods_kbm_ld_override. It’s now ready for test https://services.adcom.uci.edu/jira/browse/KFSDW-1119
Supervised Bryce
Weekly meeting working with Analytix team.
Completed all ODS new and modified tables. Remaining talend jobs to convert to Informatica: uci, pdp.
Completed https://services.adcom.uci.edu/jira/browse/KFSDW-1149. It's ready for test.
Anh’s Status 2/04/2016 – 2/17/2016
Completed configuring all outbound jobs to run periodically via triggers
Add shared objects for all outbounds jobs
Closed https://services.adcom.uci.edu/jira/browse/DWH-1673
Added trigger for s_m_UCI_UCOP_EFA100_T, s_m_UCI_UCOP_EFA200_T. They're ready whenever the data is available.
Ods jobs: Completed converting the post informatica jobs. The jira below is “ready for test”
https://services.adcom.uci.edu/jira/browse/KFSDW-731
Anh’s Status 1/04/2016 – 1/14/2016
Completed and closed the following jiras:
https://services.adcom.uci.edu/jira/browse/KFSDW-1022
https://services.adcom.uci.edu/jira/browse/KFSDW-1043
https://services.adcom.uci.edu/jira/browse/KFSDW-1037
https://services.adcom.uci.edu/jira/browse/KFSDW-1047
Completed outbound jobs for this month-end to be cumulative:
UCI_UCOP_CFSBAL_RCRD_10
UCI_UCOP_CFSBAL_RCRD_11
UCI_UCOP_CFSBAL_RCRD_20
UCI_UCOP_CFSBAL_RCRD _30
UCI_UCOP_CFSBAL_RCRD_31
UCI_UCOP_CFSAST_RCRD_12
UCI_UCOP_CFSAST_RCRD_13
UCI_SMS_FEED_DETAIL_T
UCI_SMS_FEED_SUMMARY_T
UCI_LD_BUD100_STAGING_T
UCI_LD_STF100_STAGING_T
UCI_LD_STF200_STAGING_T
Anh’s Status 12/03/2015 – 12/17/2015
Closed https://services.adcom.uci.edu/jira/browse/KFSDW-586
Closed https://services.adcom.uci.edu/jira/browse/KFSDW-986
Closed https://services.adcom.uci.edu/jira/browse/KFSDW-974
https://services.adcom.uci.edu/jira/browse/KFSDW-1017
https://services.adcom.uci.edu/jira/browse/KFSDW-1036
Anh’s Status 11/12/2015 – 11/25/2015
Closed
Closed
Closed .
Closed
Closed
Completed on dev. Need to verify before turning over to production
Anh’s Status 10/01/2015 – 10/29/2015
- Worked with the DBA team to investigate kfs daily jobs failed this week. The cause of the failure was that the backup of our databases collided with our nightly jobs. DBA team
rescheduled it at a different time. The problem was solved. Daily jobs have been running fine. - Verified, fixed to add value 2 for precision to all amounts in the fact columns.
- Worked with BI team to investigate, fixed mismatches between Talend and Informatica that they found in the kfs informatica ods, dimension and fact. Several amount columns were incorrect in both versions such as gl_income_amt due to missing fiscal period joins. They were fixed, ready to roll out.
- ODS: Solved mismatches, identified the sources for missing tables in Informatica. They will need to be created before roll out.
- Dimension: Mismatches were fixed, verified. These jobs are ready to be rolled out to production this w/e.
- Fact: Mismatches were fixed, verified. Verified, fixed to add value 2 for precision to all amounts. It's ready to be rolled out this w/e.
- All verify queries and their results were recorded in the status sheet in folder \\abs.uci.edu\dfs\adcom\shares\docs\SystemDev\KUALI Project\D-1 Data Warehouse\ETL\UCI
- Created jiras to turn over Informatica dimension, fact to production.
- Wrote plan to roll out in JIRA https://services.adcom.uci.edu/jira/browse/KFSDW-898
- Coordinated with Web Service and our teams to run their outbound jobs for month-end next month while I'll be in Mexico attending my niece's wedding. My 6-day vacation will be 11/3 - 11/10.
- Supervised Bryce with verifying Informatica data using recently creating queries including the ones to calculate gl balance amounts to test. He completed, closed his jiras.
Plan on Friday 11/29 - Saturday 11/30 for roll out Informatica dimension, fact jobs to production:
Friday: Setup, verify connections to production DB. Unplug Talend dimension, fact jobs.
Saturday: Jobs run, verify results, debug, fix if necessary.
11/2 Update : Migration went well on the week end. This task is completed.
Anh’s Status 9/14/2015 – 9/25/2015
- I was able to solve problem: Exception in thread "main" java.lang.Error: Unresolved compilation problem: at routines.UciUtils.fiscalYearDWH(UciUtils.java:485) by making copies of all available jar files on the talend server.
- Balance fact: Worked with Analytix team to get balance jobs fixed. No more mismatches. Both balance and gl entries jobs are now ready for Cognos developers to test before they can be rolled out.
- Doc details dim: Worked with Analytix team to solve problems in doc detail dim job. They fixed it but their solution is not portable due to overwrite sql query in the design. We’re working with them to enhance the solution.
- Purchasing fact jobs: I exported all 9 jobs to Unix to run them due to more memory needed. Ran all 9. Configured each job to save in their tables to compare for mismatches in sub jobs. Reported to them to fix.
- Configured and verified dim, fact tables were written at the test production DBs as expected ie account_1-3 to kfs_stage_infor, the last account table to dss_kfs DBs. They’re ready to be scheduled. I scheduled them to run on the test production to stage_kfs_infor, dss_kfs DBs.
- Scheduled daily (Tue - Sat) at 6 AM for dim jobs, 9 AM for fact jobs on the test production.
- Cloned test environment from Talenddev01 to oit docs because Matt doesn’t have access to the one on talenddev01
- Trained Matt on informatica, mapping, queries to test informatica dim, fact jobs.
- Completed jira https://services.adcom.uci.edu/jira/browse/KFSDW-876
Anh’s Status 9/1/2015 – 9/11/2015 (4 working days: 8th - 11th. Off 2 vacation days and Labor Day)
Regarding "Investigated with Analytix the 3 ods jobs that failed all of a sudden", they edited these jobs, dropped prefix DB schema on tables. I fixed them. They've been running fine since.
Fixed the informatica CAFP job because Analytix had wrong column name. It now runs fine.
Completed and verified month end outbound loads Informatica CFSBAL, CAFP, and SMS workflows for September below:
[dss].[ods_kfs].[UCI_UCOP_CFSBAL_RCRD_10_T]
[dss].[ods_kfs].[UCI_UCOP_CFSBAL_RCRD_11_T]
[dss].[ods_kfs].[UCI_UCOP_CFSBAL_RCRD_20_T]
[dss].[ods_kfs].[UCI_UCOP_CFSBAL_RCRD_30_T]
[dss].[ods_kfs].[UCI_UCOP_CFSBAL_RCRD_31_T]
[dss].[ods_kfs].[UCI_UCOP_CAFP_ACCOUNT_T]
[dss].[ods_kfs].[UCI_UCOP_CAFP_FUND_T]
[dss].[ods_kfs].[UCI_SMS_FEED_SUMMARY_T]
[dss].[ods_kfs].[UCI_SMS_FEED_DETAIL_T]
Created queries to give Bryce to verify balance 1 job using account number and worked with Analytix on balance 1 job. Test passed. No mismatch.
Created a new variable and configured informatica dimensions, facts workflows so that they can be scheduled to run nightly on the test production to stage_kfs_infor to avoid colliding with the Talend jobs.
Did trouble-shoot the failed tests that Bryce reported on the dim jobs: account, doc type, project, object code, chart. Need to configure them to run in synched with Talend then retest them.
Anh’s Status 8/17/2015 – 8/28/2015
Worked with the DBA, Middle Ware teams on INC0037523 INC0037600. They now completed, closed.
Rolled out the Informatica jobs that load KFS Web Services successfully to production. They’ve been running nightly without any issue.
Completed, closed the following jiras:
https://services.adcom.uci.edu/jira/browse/KFSDW-848
https://services.adcom.uci.edu/jira/browse/KFSDW-821
https://services.adcom.uci.edu/jira/browse/KFSDW-743
https://services.adcom.uci.edu/jira/browse/KFSDW-785
https://services.adcom.uci.edu/jira/browse/KFSDW-848
https://services.adcom.uci.edu/jira/browse/KFSDW-853
https://services.adcom.uci.edu/jira/browse/KFSDW-850
https://services.adcom.uci.edu/jira/browse/KFSDW-852
https://services.adcom.uci.edu/jira/browse/KFSDW-786
Fixed the informatica SMS Feed workflow because the design was not correct, truncated the sms feed detail table, wiped out history of data. I recovered all history data to the table. It now loads new records, keeping history data from previous months.
I was able to size the memory needed as 3G for the Unix talend dimension jobs to run on node3dev after getting error “…insufficient memory…”. After that the jobs ran without error, updated current data to all the dimension tables. Analytix needed them to verify against their Informatica dimension jobs.
Created queries to give Bryce to connect to mysql through the linked server because he doesn’t have mysql client installed on his PC.
Investigated with Analytix the 3 ods jobs that failed all of a sudden today after they ran every night without any issue for the last couple weeks. We should hear from them soon.
Anh’s Status 7/27/2015 – 7/31/2015 (1 week status)
Big thanks to Valerie for keeping production data load smoothly after Talend officially expired 2 days ago.
Completed section:
- Created a list of ods jobs for Bryce to verify. Tammy volunteered to help with this task.
- Found problem, reported to Analytix their pcard workflow as to why they included all sessions for all FP_PR tables after pcard session loads those tables.
- Warren came to ask me to help with doing queries to see the prefixes for employeeNumber. I gave him records what what he needed.
- Rolled Informatica staging jobs to production. Problem with some uci tables are not available during nightly run, but are there when running manually for debugging purpose.
- Worked with Jeric from dba team on the intermitten when sql terminated a staging job at night. Created now ticket
- Created new jiras for verifying oltp, ods jobs:https://services.adcom.uci.edu/jira/browse/KFSDW-821, https://services.adcom.uci.edu/jira/browse/KFSDW-820
- Most ODS, OLTP jobs had error running. Solved problem by creating the table and data in it to set up environment below:
insert into [dss_kfs].[dbo].[ODS_LINKED_SERVER_DETAILS]("Linked_Server","MYSQL_Schema","SqlServer_Schema","Module")
values ('MYSQL_PROD','dwhm_pps','ods_kbm','ODS_pps')
insert into [dss_kfs].[dbo].[ODS_LINKED_SERVER_DETAILS]("Linked_Server","MYSQL_Schema","SqlServer_Schema","Module")
values ('MYSQL_PROD','dwhm_ppscurr','ods_kbm','ODS_ppscurr')
insert into [dss_kfs].[dbo].[ODS_LINKED_SERVER_DETAILS]("Linked_Server","MYSQL_Schema","SqlServer_Schema","Module")
values ('ORACLE_PRD','KFSPRD','ods_ksams','ODS_ksams')
insert into [dss_kfs].[dbo].[ODS_LINKED_SERVER_DETAILS]("Linked_Server","MYSQL_Schema","SqlServer_Schema","Module")
values ('ORACLE_PRD','KFSPRD','ods_kbm','ODS_kbm')
insert into [dss_kfs].[dbo].[ODS_LINKED_SERVER_DETAILS]("Linked_Server","MYSQL_Schema","SqlServer_Schema","Module")
values ('ORACLE_PRD','KFSPRD','ods_kfs','ODS_kfs')
- Debugging issue with Talend ods jobs didn’t load for FP_PR tables.
Update: As soon as we removed the duplicate records in the [dss_kfs].[dbo].[ODS_LINKED_SERVER_DETAILS] table, jobs loaded these tables fine. We need to keep an eye on this table if the same problem occurs. Thanks Austin. - Tammy and Bryce are doing a good job finding problems in the informatica jobs. I already informed Analytix and asked to have their jobs (ods, oltp) ready by Monday. We’ll rerun the tool to verify them.
- Done fixing, verified the check_log.exe script on unix to page.
Not Completed section:
Work on OrgLevelsforLDAP and the ksams_webservices jobs.Work on outbound jobs to run on production.
Continue working with the Conversion team on the Informatica conversion task.
Continue supervising Bryce on his assigned tasks.
Anh’s Status 7/20/2015 – 7/24/2015 (1 week status)
Completed section:
Work is in progress with Outbound: Completed setting file trigger for uciBud100PopulateTablesJob,uciStf100PopulateTablesJob. For testing purpose, these jobs ran when their trigger files are created then it moved them to a directory for archiving purpose. Because the Informatica scheduler doesn’t have option for month end date, these jobs are currently scheduled to run monthly on the 5th. Because we have jobs that run every 2 months, 6 months, quarterly, etc. I think that it’s a better approach to have a batch file to turn the flag true or false when it sees the file trigger then when the flag is true then it'll trigger to run the job. I wrote a batch script to return value true if it finds the file otherwise false. I need to research how to make informatica event trigger to see this value.
Completed OLTP jobs. Only job that failed is uci load title because of the Oracle table has primary key criteria causing duplicate key error in this job.
Completed ODS jobs. Austin helped with them, said that there’s remaining errors to be fixed. I’ll get to those as soon as I can.
Responded to 9-page email full of queries and questions from Analytix to move forward beginning of the week. More long emails from them since to respond to almost daily. They’ve been asking us to rerun talend jobs averaging every day this week after they’ve either overridden talend tables, or found mismatches between them. They also asked to drop talend dimension tables including all foreign keys constraints to start a fresh load. We did all what they requested. As of their status sent to us today on dimension jobs, none was fixed. Their status hasn’t changed since last week. 9 out of 14 dimension jobs still have fail status.
Completed seting up all necessary workflows for daily run and for Bryce to fill in the email notification.
Trained Bryce to get familiar with Informatica workflow manager in order to set up email notification for all informatica session in the daily workflows. He has completed staging, dimension jobs on both dev and production. He’ll continue with remaining sessions.
Not Completed section:
Work on Talend jobs so they can run on Unix by next Wed when talend license will expire.
Continue working with the Conversion team on the Informatica conversion task.
Continue supervising Bryce on his assigned tasks.
Continue with the outbound jobs.
Anh’s Status 7/13/2015 – 7/17/2015 (1 week status)
Completed section:
Completed setting up and configuring connection to run informatica staging jobs on lunar to load oracle tables from production to stage_kfs_infor
Completed setting up and configuring informatica dim, fact jobs to run on lunar to dss schema
Completed setting up and configuring informatica dim, fact jobs to run on solar to dss_kfs schema
Done fixing staging krew job. It ran on prod without problem. Created queries to verify data. They match. I need to run it on dev so Bryce can use the tool to verify all data in the tables.
Supervised, provided input to Bryce to complete verifying all staging, dim, fact jobs that ran ok so far. Send problems/mismatches to Conversion team to fix.
Created, updated jiras, status speadsheets for reporting problems, fixes for all teams to follow progress.
Requested dba to create oracle_prd linked server on lunar so we can load oracle production to stage. It's available now.
Verified informatica staging job on production:
Talend: [stage_kfs].[dbo].[AP_SUM_ACCT_T] 75,157 recs
Informatica:[stage_kfs_infor].[dbo].[AP_SUM_ACCT_T] 75,162 recs (6 records more than Talend as expected due to the time when the job ran)
Interesting 6 records difference because the LST_UPDT_TS from the source was created after talend job ran, before informatica one ran.
Because of this reason, I rescheduled staging job to run at the same time as talend: 00:02 AM in order to accurately verify informatica jobs.
Requested Brian to create new mysql DBs for testing Informatica jobs:
Worked with Conversion team to fix 6 staging tables, or else their dim,fact jobs won’t match:
CA_ACCOUNT_T did not pass due to differences in data
GL_ENTRY_T did not pass due to differences in data
KREW_DOC_TYP_T did not pass due to differences in column schema (SEC_XML)
UC_FUND_T did not pass due to differences in data
KREW_ACTN_TKN_T did not pass due to differences in data
KREW_DOC_HDR_T did not pass due to differences in data
scheduled to run daily for dim jobs at 9:00 AM, fact jobs at 9:30 AM on prod.
Coordinated with Tammy, DBA team that we run talend and informatica staging jobs on solar starting at the same time: 00:02 in order to get identical staging data for both. So far no issue.
Done fixing staging krew job. It ran on prod without problem. Created queries to verify data. They matched. I need to run it on dev so Bryce can use the tool to verify all data in the tables.
Completed setting up OLTP jobs. Tested run some jobs without error. Will continue this task next week.
Not Completed section:
Continue working on OLTP tasks.
Plan to work on trigger files.
Continue working with Conversion team to ensure they fix problems in their jobs.
Continue working issues with the converstion task.
Anh’s Status 7/6/2015 – 7/10/2015 (1 week status)
Completed section:
Completed adding stored procedure, latest sql files to https://services.adcom.uci.edu/jira/browse/KFSDW-590
Completed rescheduling on unix, deploy2 to run start running jobs on Sunday nights (was Monday nights) due to kfs running on Sunday.
https://services.adcom.uci.edu/jira/browse/KFSDW-800
https://services.adcom.uci.edu/jira/browse/KFSDW-801
INC0031493 for Jeric to create oracle_prd linked server on solar. This is completed.
Austin created new connection stage_kfs_infor on lunar, stage_kfs_infor_prod on solar.
Status on load_budget_fund_groups_fromExcel: We told them not to convert. It is not needed since Durendal built a front end for users.
https://services.adcom.uci.edu/jira/browse/KFSDW-804
Not Completed section:
Configure jobs to run by file triggers
Set up and configure connection to run informatica staging jobs on lunar to load oracle tables from production to stage_kfs_infor
set up and configure informatica dim, fact jobs to run on lunar to dss schema
set up and configure informatica dim, fact jobs to run on solar to dss_kfs schema
Continue working, debugging with Analytix team
Anh’s Status 6/822/2015 – 7/03/2015
Completed section:
Completed the preliminary test of Informatica Asset fact job. It failed because it loads way too many more records, inserts records every time the job runs without truncating the table. The conversion team is working on fixing it. Will need to continue test on this job as soon as they complete their fix.
Completed https://services.adcom.uci.edu/jira/browse/KFSDW-745
Completed https://services.adcom.uci.edu/jira/browse/KFSDW-744
Completed https://services.adcom.uci.edu/jira/browse/KFSDW-748
Completed testing informatica jobs using queries from Manav. They passed.
Created a new tab "UCI Test By Queries" in the spreadsheet InformaticaJobsCompletionStatus_{today}.xlsx to test using queries. I've found problems in their converted balance, gl entries fact jobs. I sent them to the conversion team to fix.
Completed INC0029671: Jeric to copy data from dss_kfs to dss schema. Plan for informatica jobs to run on production to test.
Created 13 new jiras (KFSDW-771 throught 783). Per our conversation, I’ve assigned most to Bryce, to myself the most difficult ones (balance, gl entries facts)to assign to Bryce to verify informatica jobs by comparing data between Talend and Informatica tables.
Worked with conversion team to debug their jobs: They used wrong type as real instead of decimal(19,2) for amount columns.
Reopened jira for Jeric to create the sql link server on solar for production https://services.adcom.uci.edu/jira/browse/DWH-1604. He finished.
INC0030044 Jeric to fix problem not able to see oracle tables in oracle _dev linked server on nebula.
Added more amount columns to balance, gl entries fact table to test. Sent problems found to Conversion team.
Cleaned up talenddev01 server to gain up to 7GB of disk space. It was full, unable the Conversion team to perform their debugging.
wf_staging_cg has error running. Reported to Conversion team.
Created tables KFS_D_DOC_FISCAL_PERIOD, KFS_D_PROJECT and their primary keys for talend and informatica schemas to get ready for sqldiff tool to run.
Not complete yet section:
(A Friendly reminder: UCI off on Friday 7/3. My vacation day or loose it on Thurs. 7/2)
Remaining fact jobs to verify: Need to add more tests on gl entries fact, AssetFact, AssetPaymentFact, Account dimension.
Export all talend jobs on new server
ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT
Anh’s Status 6/8/2015 – 6/19/2015
Highlight:
Completed section:
Completed Talend pager duty week 6/6 - 6/12.
Completed solving fiscal year end/month end problems in outbound jobs, CAFP and CFSBAL due to duplicated records error. These jobs ran fine after that. Data matched the Oracle side.
Worked with Analytix daily to solve issues. We found out that some staging tables in Oracle kfscfg were empty or wiped out preventing them from doing their fact jobs. Tom R. copied data from stage production to dev. They had data to complete dimension and fact jobs.
Completed
Completed
Completed
Completed
Not complete yet section:
Remaining fact jobs to verify: AssetFact, AssetPaymentFact, balance fact 3,4,5 (1,2 finished, matched records count between Informatica and Talend).
Need to work with DBA to restore data to dss schema. Plan for informatica jobs to run on production to test.
Export all talend jobs on new server
ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT
Anh’s Status 5/25/2015 – 6/5/2015
Highlight:
- Completed section:
Push all kfs jobs (1 by 1) back 1 hour:
Completed modifying unix cripts to push back as well to alert when jobs are not done.
Created service now ticket INC0025478 to request zena installation on informatica server so that Jarrod's app can create etl file triggers. Update: Jason Lin said that there's no more license. We're replanning this task.
Provided input to Analytix regarding ods jobs, mysql linked server.
Resolved discrepancies in ODS jobs with Analytix.
Completed integrating tests for all staging jobs which took double amount of time to run. They're fixing these so that the can complete running nightly within 1-hour window.
Created ldap_people,ldap_people_his under schema 'people_m' in MYSQL per Analytix's request.
Not complete yet section:
Talend pager duty week 6/6 - 6/12.
same as
Export all talend jobs on new server
ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT
Anh’s Status 5/11/2015 – 5/22/2015
Highlight:
- Completed section:
Talend pager duty week 5/9 - 5/15.
Analytix Informatica staging_uci job is now solved, completed.
They confirmed that they are able to access our Informatica server.
System DSN Connection was created, inform them.
- Not complete yet section:
Talked to Jarrod to have trigger files created on the Informatica server (Windows) for Informatica jobs to run for month-end. He mentioned that the best approach is to have Zena agent install on this server so his app can tell Zena to create files there. We need to discuss this more as soon as Analytix finishes their tests with smsfeed jobs which they already imported in our repository.
Grace mentioned that she needs to have table, columns in MySQL.
Conversion: on-going task.
Providing MySQL connections to Analytix as soon as DBA created them.
Export all talend jobs on new server
ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT
Anh’s Status 4/27/2015 – 5/08/2015
Highlight:
- Completed section:
Competed
Completed:
Completed
Completed sending DDL script to create ldap_peole table to Analytix
Completed
Informatica Conversion: Analytix sent the first job, staging_uci. This job encountered errors (timestamp type columns, non-existing tables in kfscfg). After debugging it, I sent them the errors with suggestions to resolve.
Updated - Not complete yet section:
Informatica Conversion: ongoing task.
Talend pager duty next week 5/9 - 5/15
Export all talend jobs on new server
ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT
Anh’s Status 4/13/2015 – 4/24/2015
Highlight:
- Completed section:
1) Completed fixing staging krns job
2) Pager duty this week.
3) Completed .
Completed setting up to schedule this job to run nightly.
4) Emailed ddls scripts to Analytix per their request to create tables below:
MYSQL Tables : title_db2
SQLSERVER Tables: UCI_LD_CSF_TRACKER_SNAPSHOT_T, employee_basic, agingreport_det_kfs, agingreportcon_kfs
To protect sensitive data in the file, I used webfiles with password to send agingreportdet.dat
5) Completed
6) Completed new job syncInvoiceNumberSundryDebtors for
7) Completed the change to sd-nebula to remaining talend jobs
- Not complete yet section:
1) Make changes to new host sd-nova
2) Export all talend jobs on new server
3) ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT
4) Change host name to ods talend jobs to replace Nebula
5) Need to fix
6) Need to schedule syncInvoiceNumberSundryDebtors to run on production
Anh’s Status 3/28/2015 – 4/10/2015
Highlight:
- Completed section:
1) Fixed uciSmsGIFeed. ods_kfs.UCI_SMS_FEED_DETAIL_T table now has all records for Feb. data with batch date ’06-MAR-15’, matching Oracle side: 31,647 records, debit amount total is $3,647,165,482.52.
2) dwhreader:
- Completed ap,ar,cm,fp,fs,kr,krew,krim,ld,pdp,pur,sh,tem,uci
- Completed staging_uc: Talend is buggy that it loads uci tables not just uc (without the i) when using UC_%. Talend sucks. The syntax to make the staging_uc job work is:
"1 = 2) union select table_name as tname from all_tables where (owner = '" + context.KFSINT1_Schema + "' and TABLE_NAME like 'UC_%' and TABLE_NAME not like 'UC_HOME_DEPT_T' and TABLE_NAME not like 'UC_ACCT_NBR_GEN_T' and TABLE_NAME not like 'UCI_%'" - Completed OLTP jobs: utilities_award_data_to_dwhm_fs, utilities_award_data_to_dwhm_ods, utilities_ldap_all_tables, utilities_CostSharing, utilities_webservices, copy_ods_data_dss, copy_dss_kfs_data_to_dss, copy_dss_data_to_dss_2, prod_verify_kfs_data, kill_long_suspended_queries, uciLoadTitle
- Completed ODS jobs: ods_ca, ods_pur_po, prod_ods_etl_and_accounting, ods_kfs_pcard, ods_kfs_tem, ods_kfs_pdp, ods_kbm_employee_snapshot, ods_kfs_ldap_people_employee_basic, Outbound_External_Interface_Table_ETL, uciBud100PopulateTablesJob,uciStf100PopulateTablesJob, uciStf200PopulateTablesJob, uciUcopCafpJob, uciUcopCfsbalJob, uciUcopEfa100Job, uciSmsGlFeedJob, uciUcopEfa200Job
3) Staging_TEM job ran fine after Warren cleaned up in Oracle side.
4) Received ODBC installed on PC. Informatica can connect to Oracle production
5) Analytix: Provided our versions of our DBs that they asked. They confirmed access to talenddev server. Attended weekly status meeting this Friday morning. They're going to send me a list of sqlsver tables so I can send them DDLs to them to create these tables.
6) uciSmsGIFeed: The job loaded month-end data this month without problem. FYI - Following day, this was the 2nd time this month that Jarrod reran, regenerated the trigger. The job ran fine 2nd time.
7) Completed
Not complete yet section:
1) Staging krns job loaded all tables but Talend sent false notification. I need to debug this job.
2) Export all talend jobs on new server
3) ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT
4) Change host name to ods talend jobs to replace Nebula
5) SBSO: with the newest revision to the re-mapping logic: When I came back from my 1-day vacation (due to many accumulated vacation hours, use it or loose it), I was told that we hold off on this task until further direction.
6) Pager duty next week.
Anh’s Status 3/16/2015 – 3/27/2015
Highlight:
- Completed section:
1) Fixed prod_all_ldap_tables by increasing length of LAST_UPDATE_TS from 23 to 30.
2) Fixed doc details job from failing due to error "java.lang.OutOfMemoryError: GC overhead limit exceeded". I increased to 15G. Job ran fine. Data is up-to-date.
3) dwhreader:
a) Completed dev_AssetDimensionMaster_creates_trigger
b) The staging_cg job failed at the iterate in toraclelist component due to tables as synonyms in dwhreader. Thanks Valerie. I put in the fix. This job now ran fine. Completed.
c) Completed dev_dim_account_master_creates_trigger.
d) Completed dev_staging_ca has the new change.
4) JIRA KFSDW-612: Users confirmed the fix. It's now closed.
5) Fixed prod_dim_asset_master, prod_dim_account_master by using the older version in tac. The cause of the jobs failed is unknown, and is under investigation. The only change made was in tOracleList component in the staging_cg job in dev for talend to see dwhreader schema. To solve this problem, I used the previous version before the change in tOracleList. The job has been running fine without problem.
6) Created JIRA, linked with JIRA
7) Debugged prod_staging_krew job that failed on production last night. I’m in the process of debugging all 50+ krew tables, 1 by 1. So far loaling table KREW_DOC_HDR_EXT_DT_T from kfsprd caused error "java.sql.SQLException: Only dates between January 1, 1753 and December 31, 9999 are accepted.". Some records that with value of VAL (only date column in this table) has date that is out-of-range mentioned above. I brought this up to Warren's attention. He deleted these records. The nightly job ran fine after that.
8) Staging_TEM job failed with date out-of-range error. Warren identified the mistyped date from users, said that he will clean up data this week end.
- Not complete yet section:
1) dwhreader:
a) staging_uc has Incorrect syntax near ')' after trying many different syntaxes.
b) Remaining staging jobs: ap,ar,cm,fp,fs,kr,krew,krim,krns,ld,pdp,pur,sh,tem,uci
2) uciSmsGIFeed: The request was not approved for adding the time next to the date to Jarrod's JIRA kfsi-11864. I still need to proceed with creating an additional key using batch_date in order to load the Feb data. Steps required are: Move all data from 'ods_kfs.UCI_SMS_FEED_DETAIL_T to a temp table before dropping it in order to add another key to this table. Move data from the temp table back to it, including data for Feb that was saved in [dss_kfs].[ods_kfs].[z_temp_from_oracle_UCI_SMS_FEED_DETAIL_T]
3) Export all talend jobs on new server
4) ACCOUNT_CG_PRPSL_NBR not being populated in dss_kfs.KFS_D_ACCOUNT
5) SBSO: with the newest revision to the re-mapping logic
6) Change host name to ods talend jobs to replace Nebula