dhis2-users team mailing list archive

[Marta Vila <martavila@xxxxxxxxx>]

Hi all,

I´m running a live version with data and metadata imported and it all works
fine but I don´t find the way to generate the Analytic tables.

If I run them manually I get this Process failed: Exception during
execution   (log attached).

If I remember well it expects the tables to be generated by the automatic
process so i scheduled the task yesterday to be executed daily and left the
laptop on... but nothing changes...
Maybe it got suspended and the task was never executed... (will change that
now and lets see tomorrow) but anyway, if that is my problem, I suggest it
may be useful to generate the tables even if the task is triggered manually
for the first time.


Version: 2.13Build revision: 12908Build date: 2013-12-04 16:24

* INFO  11:42:10,857 Create organisation unit structure table SQL: CREATE TABLE _orgunitstructure ( organisationunitid INTEGER NOT NULL PRIMARY KEY, level INTEGER, idlevel1 INTEGER, uidlevel1 CHARACTER(11), idlevel2 INTEGER, uidlevel2 CHARACTER(11), idlevel3 INTEGER, uidlevel3 CHARACTER(11), idlevel4 INTEGER, uidlevel4 CHARACTER(11), idlevel5 INTEGER, uidlevel5 CHARACTER(11), idlevel6 INTEGER, uidlevel6 CHARACTER(11)); (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:10,903 Organisation unit structure table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:10,911 Create category option combo name table SQL: CREATE TABLE _categoryoptioncomboname ( categoryoptioncomboid INTEGER NOT NULL PRIMARY KEY, categoryoptioncomboname VARCHAR(250) ) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:10,988 Category option combo name table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:11,112 Create data element group set table SQL: CREATE TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHAR (250), "Health Data Elements " VARCHAR (250), "mCaHnjHSgW0" CHARACTER(11), PRIMARY KEY ( dataelementid ) ) (CreateDataElementGroupSetTableStatement.java [taskScheduler-7])
* INFO  11:42:11,130 Data element group set table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:11,211 Create indicator group set table SQL: CREATE TABLE _indicatorgroupsetstructure ( indicatorid INTEGER NOT NULL, indicatorname VARCHAR (250), PRIMARY KEY ( indicatorid ) ) (CreateIndicatorGroupSetTableStatement.java [taskScheduler-7])
* INFO  11:42:11,212 Indicator group set table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:11,307 Create organisation unit group set table SQL: CREATE TABLE _organisationunitgroupsetstructure ( organisationunitid INTEGER NOT NULL, organisationunitname VARCHAR (250), "Ownership" VARCHAR (250), "xPpF0XXMxdT" CHARACTER(11), "Type" VARCHAR (250), "nLsuwTKm63F" CHARACTER(11), "Type of Health Facility" VARCHAR (250), "tzIlEDCf6Ke" CHARACTER(11), PRIMARY KEY ( organisationunitid ) ) (CreateOrganisationUnitGroupSetTableStatement.java [taskScheduler-7])
* INFO  11:42:11,335 Organisation unit group set table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:11,339 Create category structure table SQL: CREATE TABLE _categorystructure ( categoryoptioncomboid INTEGER NOT NULL, categoryoptioncomboname VARCHAR (250), "Age groups under/over 5" VARCHAR (250), "g1FhSyJjgMf" CHARACTER(11), "default" VARCHAR (250), "fotp6ViKEQv" CHARACTER(11), PRIMARY KEY ( categoryoptioncomboid ) ) (CreateCategoryTableStatement.java [taskScheduler-7])
* INFO  11:42:11,342 Category table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:11,355 Create data element structure SQL: CREATE TABLE _dataelementstructure ( dataelementid INTEGER NOT NULL PRIMARY KEY, dataelementname VARCHAR(250), periodtypeid INTEGER, periodtypename VARCHAR(250) ) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:11,387 Data element table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:11,557 Create period structure SQL: CREATE TABLE _periodstructure (periodid INTEGER NOT NULL PRIMARY KEY, iso VARCHAR(10) NOT NULL, daysno INTEGER NOT NULL, daily VARCHAR(10), weekly VARCHAR(10), monthly VARCHAR(10), bimonthly VARCHAR(10), quarterly VARCHAR(10), sixmonthly VARCHAR(10), yearly VARCHAR(10), financialapril VARCHAR(10), financialjuly VARCHAR(10), financialoct VARCHAR(10)) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:11,560 Date period table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:11,654 Create date period structure SQL: CREATE TABLE _dateperiodstructure (dateperiod DATE NOT NULL PRIMARY KEY, daily VARCHAR(10), weekly VARCHAR(10), monthly VARCHAR(10), bimonthly VARCHAR(10), quarterly VARCHAR(10), sixmonthly VARCHAR(10), yearly VARCHAR(10), financialapril VARCHAR(10), financialjuly VARCHAR(10), financialoct VARCHAR(10)) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:16,731 Period table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:16,733 Create data element category option combo SQL: CREATE TABLE _dataelementcategoryoptioncombo (dataelementuid VARCHAR(11) NOT NULL, categoryoptioncombouid VARCHAR(11) NOT NULL) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:16,733 Insert data element category option combo SQL: insert into _dataelementcategoryoptioncombo (dataelementuid, categoryoptioncombouid) select de.uid as dataelementuid, coc.uid as categoryoptioncombouid from dataelement de join categorycombos_optioncombos cc on de.categorycomboid = cc.categorycomboid join categoryoptioncombo coc on cc.categoryoptioncomboid = coc.categoryoptioncomboid (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:16,740 Create data element category option combo index SQL: CREATE INDEX dataelement_categoryoptioncombo ON _dataelementcategoryoptioncombo (dataelementuid, categoryoptioncombouid) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:16,741 Data element category option combo table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:16,743 [Level: INFO, category: DATAMART, time: Wed Dec 11 11:42:16 CET 2013, message: Updating resource tables] (InMemoryNotifier.java [taskScheduler-7])
* INFO  11:42:16,838 Create organisation unit structure table SQL: CREATE TABLE _orgunitstructure ( organisationunitid INTEGER NOT NULL PRIMARY KEY, level INTEGER, idlevel1 INTEGER, uidlevel1 CHARACTER(11), idlevel2 INTEGER, uidlevel2 CHARACTER(11), idlevel3 INTEGER, uidlevel3 CHARACTER(11), idlevel4 INTEGER, uidlevel4 CHARACTER(11), idlevel5 INTEGER, uidlevel5 CHARACTER(11), idlevel6 INTEGER, uidlevel6 CHARACTER(11)); (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:16,853 Organisation unit structure table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:16,855 Create category option combo name table SQL: CREATE TABLE _categoryoptioncomboname ( categoryoptioncomboid INTEGER NOT NULL PRIMARY KEY, categoryoptioncomboname VARCHAR(250) ) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:16,857 Category option combo name table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:16,870 Create data element group set table SQL: CREATE TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname VARCHAR (250), "Health Data Elements " VARCHAR (250), "mCaHnjHSgW0" CHARACTER(11), PRIMARY KEY ( dataelementid ) ) (CreateDataElementGroupSetTableStatement.java [taskScheduler-7])
* INFO  11:42:16,873 Data element group set table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:16,876 Create indicator group set table SQL: CREATE TABLE _indicatorgroupsetstructure ( indicatorid INTEGER NOT NULL, indicatorname VARCHAR (250), PRIMARY KEY ( indicatorid ) ) (CreateIndicatorGroupSetTableStatement.java [taskScheduler-7])
* INFO  11:42:16,877 Indicator group set table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:16,889 Create organisation unit group set table SQL: CREATE TABLE _organisationunitgroupsetstructure ( organisationunitid INTEGER NOT NULL, organisationunitname VARCHAR (250), "Ownership" VARCHAR (250), "xPpF0XXMxdT" CHARACTER(11), "Type" VARCHAR (250), "nLsuwTKm63F" CHARACTER(11), "Type of Health Facility" VARCHAR (250), "tzIlEDCf6Ke" CHARACTER(11), PRIMARY KEY ( organisationunitid ) ) (CreateOrganisationUnitGroupSetTableStatement.java [taskScheduler-7])
* INFO  11:42:16,897 Organisation unit group set table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:16,901 Create category structure table SQL: CREATE TABLE _categorystructure ( categoryoptioncomboid INTEGER NOT NULL, categoryoptioncomboname VARCHAR (250), "Age groups under/over 5" VARCHAR (250), "g1FhSyJjgMf" CHARACTER(11), "default" VARCHAR (250), "fotp6ViKEQv" CHARACTER(11), PRIMARY KEY ( categoryoptioncomboid ) ) (CreateCategoryTableStatement.java [taskScheduler-7])
* INFO  11:42:16,904 Category table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:16,914 Create data element structure SQL: CREATE TABLE _dataelementstructure ( dataelementid INTEGER NOT NULL PRIMARY KEY, dataelementname VARCHAR(250), periodtypeid INTEGER, periodtypename VARCHAR(250) ) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:16,923 Data element table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:16,931 Create period structure SQL: CREATE TABLE _periodstructure (periodid INTEGER NOT NULL PRIMARY KEY, iso VARCHAR(10) NOT NULL, daysno INTEGER NOT NULL, daily VARCHAR(10), weekly VARCHAR(10), monthly VARCHAR(10), bimonthly VARCHAR(10), quarterly VARCHAR(10), sixmonthly VARCHAR(10), yearly VARCHAR(10), financialapril VARCHAR(10), financialjuly VARCHAR(10), financialoct VARCHAR(10)) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:16,934 Date period table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:16,941 Create date period structure SQL: CREATE TABLE _dateperiodstructure (dateperiod DATE NOT NULL PRIMARY KEY, daily VARCHAR(10), weekly VARCHAR(10), monthly VARCHAR(10), bimonthly VARCHAR(10), quarterly VARCHAR(10), sixmonthly VARCHAR(10), yearly VARCHAR(10), financialapril VARCHAR(10), financialjuly VARCHAR(10), financialoct VARCHAR(10)) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:18,831 Period table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:18,833 Create data element category option combo SQL: CREATE TABLE _dataelementcategoryoptioncombo (dataelementuid VARCHAR(11) NOT NULL, categoryoptioncombouid VARCHAR(11) NOT NULL) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:18,833 Insert data element category option combo SQL: insert into _dataelementcategoryoptioncombo (dataelementuid, categoryoptioncombouid) select de.uid as dataelementuid, coc.uid as categoryoptioncombouid from dataelement de join categorycombos_optioncombos cc on de.categorycomboid = cc.categorycomboid join categoryoptioncombo coc on cc.categoryoptioncomboid = coc.categoryoptioncomboid (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:18,836 Create data element category option combo index SQL: CREATE INDEX dataelement_categoryoptioncombo ON _dataelementcategoryoptioncombo (dataelementuid, categoryoptioncombouid) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO  11:42:18,836 Data element category option combo table generated (DefaultResourceTableService.java [taskScheduler-7])
* INFO  11:42:18,837 [Level: INFO, category: DATAMART, time: Wed Dec 11 11:42:18 CET 2013, message: Updating analytics tables] (InMemoryNotifier.java [taskScheduler-7])
* INFO  11:42:18,838 Starting update, no of processes: 3: 0:00:00.000 (Clock.java [taskScheduler-7])
* INFO  11:42:18,960 Get tables using earliest: 2011-01-01 00:00:00.0, latest: 2012-11-30 00:00:00.0 (AbstractJdbcTableManager.java [taskScheduler-7])
* INFO  11:42:18,994 Partition tables: [analytics_2011, analytics_2012], last 3 years: false: 0:00:00.156 (Clock.java [taskScheduler-7])
* INFO  11:42:18,995 [Level: INFO, category: DATAMART, time: Wed Dec 11 11:42:18 CET 2013, message: Creating analytics tables] (InMemoryNotifier.java [taskScheduler-7])
* INFO  11:42:19,006 Create SQL: create table analytics_temp_2011 ("mCaHnjHSgW0" character(11),"tzIlEDCf6Ke" character(11),"xPpF0XXMxdT" character(11),"nLsuwTKm63F" character(11),"uidlevel1" character(11),"uidlevel2" character(11),"uidlevel3" character(11),"uidlevel4" character(11),"uidlevel5" character(11),"uidlevel6" character(11),"daily" character varying(10),"weekly" character varying(10),"monthly" character varying(10),"bimonthly" character varying(10),"quarterly" character varying(10),"sixmonthly" character varying(10),"yearly" character varying(10),"financialapril" character varying(10),"financialjuly" character varying(10),"financialoct" character varying(10),de character(11) not null,co character(11) not null,level integer,daysxvalue double, daysno integer not null, value double) (JdbcAnalyticsTableManager.java [taskScheduler-7])
* INFO  11:42:19,018 Create SQL: create table analytics_temp_2012 ("mCaHnjHSgW0" character(11),"tzIlEDCf6Ke" character(11),"xPpF0XXMxdT" character(11),"nLsuwTKm63F" character(11),"uidlevel1" character(11),"uidlevel2" character(11),"uidlevel3" character(11),"uidlevel4" character(11),"uidlevel5" character(11),"uidlevel6" character(11),"daily" character varying(10),"weekly" character varying(10),"monthly" character varying(10),"bimonthly" character varying(10),"quarterly" character varying(10),"sixmonthly" character varying(10),"yearly" character varying(10),"financialapril" character varying(10),"financialjuly" character varying(10),"financialoct" character varying(10),de character(11) not null,co character(11) not null,level integer,daysxvalue double, daysno integer not null, value double) (JdbcAnalyticsTableManager.java [taskScheduler-7])
* INFO  11:42:19,020 Created analytics tables: 0:00:00.182 (Clock.java [taskScheduler-7])
* INFO  11:42:19,020 [Level: INFO, category: DATAMART, time: Wed Dec 11 11:42:19 CET 2013, message: Populating analytics tables] (InMemoryNotifier.java [taskScheduler-7])
* INFO  11:42:19,038 Populate SQL: insert into analytics_temp_2011 ("mCaHnjHSgW0","tzIlEDCf6Ke","xPpF0XXMxdT","nLsuwTKm63F","uidlevel1","uidlevel2","uidlevel3","uidlevel4","uidlevel5","uidlevel6","daily","weekly","monthly","bimonthly","quarterly","sixmonthly","yearly","financialapril","financialjuly","financialoct",de,co,level,daysxvalue, daysno, value) select degs."mCaHnjHSgW0",ougs."tzIlEDCf6Ke",ougs."xPpF0XXMxdT",ougs."nLsuwTKm63F",ous."uidlevel1",ous."uidlevel2",ous."uidlevel3",ous."uidlevel4",ous."uidlevel5",ous."uidlevel6",ps."daily",ps."weekly",ps."monthly",ps."bimonthly",ps."quarterly",ps."sixmonthly",ps."yearly",ps."financialapril",ps."financialjuly",ps."financialoct",de.uid,co.uid,ous.level,cast(dv.value as double) * ps.daysno as daysxvalue, ps.daysno as daysno, cast(dv.value as double) as value from datavalue dv left join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid left join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid left join _categorystructure cs on dv.categoryoptioncomboid=cs.categoryoptioncomboid left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid left join _periodstructure ps on dv.periodid=ps.periodid left join dataelement de on dv.dataelementid=de.dataelementid left join categoryoptioncombo co on dv.categoryoptioncomboid=co.categoryoptioncomboid left join period pe on dv.periodid=pe.periodid where de.valuetype = 'int' and de.domaintype = 'aggregate' and pe.startdate >= '2011-01-01' and pe.startdate <= '2011-12-31' and dv.value is not null and dv.value regexp '^(-?[0-9]+)(\.[0-9]+)?(E\d+)?$' and ( dv.value != '0' or de.aggregationtype = 'average' or de.zeroissignificant = true )  (JdbcAnalyticsTableManager.java [SimpleAsyncTaskExecutor-11])
* INFO  11:42:19,058 Populate SQL: insert into analytics_temp_2012 ("mCaHnjHSgW0","tzIlEDCf6Ke","xPpF0XXMxdT","nLsuwTKm63F","uidlevel1","uidlevel2","uidlevel3","uidlevel4","uidlevel5","uidlevel6","daily","weekly","monthly","bimonthly","quarterly","sixmonthly","yearly","financialapril","financialjuly","financialoct",de,co,level,daysxvalue, daysno, value) select degs."mCaHnjHSgW0",ougs."tzIlEDCf6Ke",ougs."xPpF0XXMxdT",ougs."nLsuwTKm63F",ous."uidlevel1",ous."uidlevel2",ous."uidlevel3",ous."uidlevel4",ous."uidlevel5",ous."uidlevel6",ps."daily",ps."weekly",ps."monthly",ps."bimonthly",ps."quarterly",ps."sixmonthly",ps."yearly",ps."financialapril",ps."financialjuly",ps."financialoct",de.uid,co.uid,ous.level,cast(dv.value as double) * ps.daysno as daysxvalue, ps.daysno as daysno, cast(dv.value as double) as value from datavalue dv left join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid left join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid left join _categorystructure cs on dv.categoryoptioncomboid=cs.categoryoptioncomboid left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid left join _periodstructure ps on dv.periodid=ps.periodid left join dataelement de on dv.dataelementid=de.dataelementid left join categoryoptioncombo co on dv.categoryoptioncomboid=co.categoryoptioncomboid left join period pe on dv.periodid=pe.periodid where de.valuetype = 'int' and de.domaintype = 'aggregate' and pe.startdate >= '2012-01-01' and pe.startdate <= '2012-12-31' and dv.value is not null and dv.value regexp '^(-?[0-9]+)(\.[0-9]+)?(E\d+)?$' and ( dv.value != '0' or de.aggregationtype = 'average' or de.zeroissignificant = true )  (JdbcAnalyticsTableManager.java [SimpleAsyncTaskExecutor-13])
* INFO  11:42:19,140 [Level: ERROR, category: DATAMART, time: Wed Dec 11 11:42:19 CET 2013, message: Process failed: Exception during execution] (InMemoryNotifier.java [taskScheduler-7])