Outbound details not getting populated in Infomart

[sorav84]

Hi all,

Here is the issue description.I have setup Infomart 8.0 to accumalate data for outbound solution.
Now the configuration check is done & jobs are running fine , as long as Job_Extract is extracting voice details from IDB (details gathered by ICON from SIP TServer) it is ok & data is being added into Infomart core tables. But when it is about to extract data from IDB with role ICON_OCS it does not . I have checked all the configuration thoroughly. But nothing is coming out.


Here is the log snippet.PFA whole log  for reference

I have posted the whole log for one job. Here I can see issue in the begining , may be I am wrong.Please point any mistake u see. I ran this Job Extract ICON with DAP point to IDB collecting OCS details.

See the interval it sees for TServer , CFG , IServer & for OCS. hwm & interval.

for  the two it sees date 19th sept 2011 but for OCS it sees 14th feb 2037. I dont have IServer in our environment.

I have even redeployed all the 3 ICON instances & checked created date in ICON OCS tables.

------------------------------------------------------------------------------------------------------------------


2011-09-20 04:28:49,375 DEBUG main        35000 JobController: tick 2011-09-20T04:28:49.375Z, 15000
2011-09-20 04:28:49,375 DEBUG main        35000 SQLUtils.executeUpdate (DELETE dbo.CTL_SCHEDULED_JOBS WHERE CTL_SCHEDULED_JOBS.SCHEDULE_ID = 18,null) - enter
2011-09-20 04:28:49,375 DEBUG main        35000 SQLUtils.executeUpdate - exit returning 1
2011-09-20 04:28:49,390 INFO  ecp-1-3106  20103 Job 'Job_ExtractICON' started. Version='8.0.101.04' built '2011-06-01 13:06:56 UTC'.
2011-09-20 04:28:49,390 INFO  ecp-1-3106  30041 Job step TRUNCATE_TMP started.
2011-09-20 04:28:49,453 INFO  ecp-1-3106  30042 Job step TRUNCATE_TMP completed successfully.
2011-09-20 04:28:49,453 INFO  ecp-1-3106  30041 Job step EXTRACT started.
2011-09-20 04:28:49,453 DEBUG ecp-1-3106  35000 SQLUtils.executeUpdate (TRUNCATE TABLE dbo.CTL_EXTRACT_METRICS,null) - enter
2011-09-20 04:28:49,453 DEBUG ecp-1-3106  35000 SQLUtils.executeUpdate - exit returning -1
2011-09-20 04:28:49,453 DEBUG ecp-1-3106  35000 SQLUtils.executeUpdate (INSERT INTO dbo.CTL_EXTRACT_METRICS (LAST_CFG_EXTRACT_TS) VALUES (1316492929),null) - enter
2011-09-20 04:28:49,453 DEBUG ecp-1-3106  35000 SQLUtils.executeUpdate - exit returning 1
2011-09-20 04:28:49,453 DEBUG ecp-1-3106  35000 Querying SQL=[SELECT * FROM dbo.CTL_DS]
2011-09-20 04:28:49,468 DEBUG ecp-1-3106  35000 Querying SQL=[SELECT CTL_EXTRACT_HWM.TABLE_NAME,CTL_EXTRACT_HWM.DATA_SOURCE_KEY,CTL_EXTRACT_HWM.DATA_SOURCE_TYPE,CTL_EXTRACT_HWM.EXTRACT_START_TIME,CTL_EXTRACT_HWM.EXTRACT_END_TIME,CTL_EXTRACT_HWM.ROW_COUNT,CTL_EXTRACT_HWM.MAX_TS,CTL_EXTRACT_HWM.JOB_ID,CTL_EXTRACT_HWM.JOB_NAME,CTL_EXTRACT_HWM.JOB_VERSION,CTL_EXTRACT_HWM.DAP_NAME,CTL_EXTRACT_HWM.DSS_ID,CTL_EXTRACT_HWM.ICON_DBID,CTL_EXTRACT_HWM.PROVIDERTAG FROM dbo.CTL_EXTRACT_HWM WITH(NOLOCK) WHERE  EXISTS (SELECT 1 FROM (SELECT (MAX(CTL_EXTRACT_HWM.MAX_TS)) ts,(TABLE_NAME) tn,(DATA_SOURCE_KEY) dsk FROM dbo.CTL_EXTRACT_HWM WITH(NOLOCK) GROUP BY CTL_EXTRACT_HWM.TABLE_NAME,CTL_EXTRACT_HWM.DATA_SOURCE_KEY) hwm WHERE CTL_EXTRACT_HWM.TABLE_NAME = hwm.tn AND CTL_EXTRACT_HWM.DATA_SOURCE_KEY = hwm.dsk AND CTL_EXTRACT_HWM.MAX_TS = hwm.ts)]
2011-09-20 04:28:49,531 DEBUG ecp-1-3106  35000 TSERVER: calculateInterval(): chunkSize=900, stuckThreshold=1731600

    [b]hwm=[2011-09-19 06:40:39,2011-09-19 16:41:25(1316414439,1316450485)]
interval=[2011-09-19 06:40:40,2011-09-19 16:56:25(1316414440,1316451385)][/b]

2011-09-20 04:28:49,531 DEBUG ecp-1-3106  35000 TSERVER: fillDss, chunk=[2011-09-19 06:40:40,2011-09-19 16:56:25(1316414440,1316451385)]
2011-09-20 04:28:49,531 DEBUG ecp-1-3106  35000 TSERVER: hasAvailableDssData=false, ExtractWindow without sessions above horizon: TSERVER, [2011-09-19 06:40:40,2011-09-19 16:56:25(1316414440,1316451385)], 0 sessions:,
2011-09-20 04:28:49,531 DEBUG ecp-1-3106  35000 TSERVER: ExtractWindow with sessions above horizon: TSERVER, [2011-09-19 06:40:40,2011-09-19 16:56:25(1316414440,1316451385)], 0 sessions:,
2011-09-20 04:28:49,531 DEBUG ecp-1-3106  35000 TSERVER: ExtractWindow after set_last_second_flag(): TSERVER, [2011-09-19 06:40:40,2011-09-19 16:56:25(1316414440,1316451385)], 0 sessions:,
2011-09-20 04:28:49,531 DEBUG ecp-1-3106  35000 ISERVER: calculateInterval(): chunkSize=900, stuckThreshold=1731600
    hwm=[2010-01-01 00:00:00,2010-01-01 00:00:00(1262304000,1262304000)]
interval=[2010-01-01 00:00:01,2010-01-01 00:15:00(1262304001,1262304900)]
2011-09-20 04:28:49,531 DEBUG ecp-1-3106  35000 ISERVER: fillDss, chunk=[2010-01-01 00:00:01,2010-01-01 00:15:00(1262304001,1262304900)]
2011-09-20 04:28:49,531 DEBUG ecp-1-3106  35000 ISERVER: hasAvailableDssData=false, ExtractWindow without sessions above horizon: ISERVER, [2010-01-01 00:00:01,2010-01-01 00:15:00(1262304001,1262304900)], 0 sessions:,
2011-09-20 04:28:49,546 DEBUG ecp-1-3106  35000 ISERVER: ExtractWindow with sessions above horizon: ISERVER, [2010-01-01 00:00:01,2010-01-01 00:15:00(1262304001,1262304900)], 0 sessions:,
2011-09-20 04:28:49,546 DEBUG ecp-1-3106  35000 ISERVER: ExtractWindow after set_last_second_flag(): ISERVER, [2010-01-01 00:00:01,2010-01-01 00:15:00(1262304001,1262304900)], 0 sessions:,
2011-09-20 04:28:49,546 DEBUG ecp-1-3106  35000 OCS: calculateInterval(): chunkSize=900, stuckThreshold=1731600

  [b]hwm=[2037-02-14 15:32:21,2037-02-14 15:32:21(2118238341,2118238341)]
interval=[2037-02-14 15:32:22,2037-02-14 15:47:21(2118238342,2118239241)][/b]

2011-09-20 04:28:49,546 DEBUG ecp-1-3106  35000 OCS: fillDss, chunk=[2037-02-14 15:32:22,2037-02-14 15:47:21(2118238342,2118239241)]
2011-09-20 04:28:49,546 DEBUG ecp-1-3106  35000 Querying SQL=[SELECT DSS_ID,ICON_DBID,ICON_DBID_PRIM,DS_DBID,DS_DBID_PRIM,DS2_DBID,DS_TYPE,FEVENT_DSUTC,LEVENT_DSUTC,NODATA_IUTC,DSCONN_SDSTS,DSCONN_STIME,DSCONN_ETIME FROM dbo.G_DSS_GOS_PROVIDER WHERE (LEVENT_DSUTC >= 2118238342 OR NODATA_IUTC >= 2118238312) AND DS_TYPE = 3 ORDER BY DSCONN_SDSTS ASC]
2011-09-20 04:28:49,546 DEBUG ecp-1-3106  35000 OCS: siHasDataOrNodataBelowHorizon= true, DSS=[2037-02-13 01:17:22,2038-01-19 02:55:17(2118100642,2147482517)], ND_TS=1970-01-01 00:00:00(        0), gos, dsKey=  2, DSS[dssId=1734692480, iconDbId=127, iconDbIdPr=0, dsDbId=111, dsDbIdPr=0, ds2DbId=101, fEventDsTs=2118100642, lEventDsTs=2147482517, nodata_UTC_Ts=        0, dsConnSdsTs=1316377832, dsConnStartTime=2011-09-18 20:30:32(1316377832), dsConnEndTime=2011-09-19 06:42:25(1316414545)]
2011-09-20 04:28:49,546 DEBUG ecp-1-3106  35000 OCS: hasAvailableDssData=true, ExtractWindow without sessions above horizon: OCS, [2037-02-14 15:32:22,2037-02-14 15:47:21(2118238342,2118239241)], 1 sessions:,
[2037-02-13 01:17:22,2038-01-19 02:55:17(2118100642,2147482517)], ND_TS=1970-01-01 00:00:00(        0), gos, dsKey=  2, DSS[dssId=1734692480, iconDbId=127, iconDbIdPr=0, dsDbId=111, dsDbIdPr=0, ds2DbId=101, fEventDsTs=2118100642, lEventDsTs=2147482517, nodata_UTC_Ts=        0, dsConnSdsTs=1316377832, dsConnStartTime=2011-09-18 20:30:32(1316377832), dsConnEndTime=2011-09-19 06:42:25(1316414545)]
2011-09-20 04:28:49,546 DEBUG ecp-1-3106  35000 OCS: ExtractWindow with sessions above horizon: OCS, [2037-02-14 15:32:22,2037-02-14 15:47:21(2118238342,2118239241)], 1 sessions:,
[2037-02-13 01:17:22,2038-01-19 02:55:17(2118100642,2147482517)], ND_TS=1970-01-01 00:00:00(        0), gos, dsKey=  2, DSS[dssId=1734692480, iconDbId=127, iconDbIdPr=0, dsDbId=111, dsDbIdPr=0, ds2DbId=101, fEventDsTs=2118100642, lEventDsTs=2147482517, nodata_UTC_Ts=        0, dsConnSdsTs=1316377832, dsConnStartTime=2011-09-18 20:30:32(1316377832), dsConnEndTime=2011-09-19 06:42:25(1316414545)]
2011-09-20 04:28:49,546 DEBUG ecp-1-3106  35000 OCS: ExtractWindow after set_last_second_flag(): OCS, [2037-02-14 15:32:22,2037-02-14 15:47:21(2118238342,2118239241)], 1 sessions:,
[2037-02-13 01:17:22,2038-01-19 02:55:17(2118100642,2147482517)], ND_TS=1970-01-01 00:00:00(        0), gos, dsKey=  2, DSS[dssId=1734692480, iconDbId=127, iconDbIdPr=0, dsDbId=111, dsDbIdPr=0, ds2DbId=101, fEventDsTs=2118100642, lEventDsTs=2147482517, nodata_UTC_Ts=        0, dsConnSdsTs=1316377832, dsConnStartTime=2011-09-18 20:30:32(1316377832), dsConnEndTime=2011-09-19 06:42:25(1316414545)]
2011-09-20 04:28:49,546 DEBUG ecp-1-3106  35000 CFG: calculateInterval(): chunkSize=831057809, stuckThreshold=2493173427

    [b] hwm=[2011-09-19 09:50:38,2011-09-19 09:50:38(1316425838,1316425838)]
interval=[2011-09-19 09:50:39,2038-01-19 03:14:07(1316425839,2147483647)][/b]

[genesysguru]

Hi, I would double check your ICON role as I seem to remember that the documentation was inconsistent in this area. Also check your connections to InfoMart ETL.

http://genesysguru.com/blog/

[sorav84]

I believe , though its not written in GIM 8.0 dep guide but shud i include CCON_adata_GIM.xml file which includes GSW_CALL_ATTEMPT_GUID as it was done in 7.6

[sorav84]

Hi all,

Good news. The issue is resolved & now I can see the data in outbound tables.

I redeployed all the solutions from begining & it worked.The issue I can see over here is system date has been changed to pervious date then was changed back to current one.

Though it doesnt affect ICON but GIM surely doesnt work properly.

[sorav84]

Looks like road of issues will not end for me. I am stuck in aggregation issue.

The  aggregation is running fine but it is not writing any data into aggregate tables.

all i can see starting aggregation  then 0 rows written to the table .Data is present in core & outbound tables.

Thanks in advance
sorav

[fnunezsa]

Have you deployed the Reporting and Analytics Aggregates? If you haven't done so, then you won't see any data in your aggregate tables. AFAIK there are both 7.6 aggregate tables and 8.0 aggregate tables in GIM 8.0, but only 8.0 aggregate tables are populated after Reporting and Analytics Aggregates have been deployed.

From GIM 8.0 Oracle Reference Manual:
[quote]The aggregation job aggregates the transformed data continuously, in environments in which GI2 or the RAA package is deployed.[/quote]

From GIM RAA 8.0 Deployment Guide:
[quote]The 7.6 aggregate tables, however, can coexist with 8.0.x aggregate tables
within an 8.0 Info Mart, but the RAA aggregation engine does not populate the
7.6 tables.[/quote]

[Allan]

There is no mention that this customer is using Interactive Insights and was not aware that RAA is a mandatory component. Post your aggregate options and we can check them over