dhis2-devs team mailing list archive

[moses mwale <isoftcom.ic@xxxxxxxxx>]

Thanks all you are really help i appreciate so much. let me do test first
on the backed up db if satisfied will run the merge on the Prod. Thanks.

On Fri, Jan 26, 2018 at 3:24 AM, Bob Jolliffe <bobjolliffe@xxxxxxxxx> wrote:

> If you really must work on your production database directly then at
> the very least you should shutdown the dhis2 instance while you make
> changes.  Naturally you want to keep that down time to a minimum.
>
> So I would suggest a sequence of:
>
> (i) make a copy of the database to test these procedures
> (ii) test out all of this sql stuff against your db copy (making sure
> you keep your sql in files so you can rerun against production)
> Once you are happy you can do what you need to do quickly and correctly,
> (iii) shutdown the dhis2 instance
> (iv) make a backup of database (again)
> (v) run the sql
> (vi) restart dhis2 instance
>
> Stay safe.
>
> On 25 January 2018 at 13:05, moses mwale <isoftcom.ic@xxxxxxxxx> wrote:
> > This is live (Production) db created a backup. if something goes wrong
> will
> > revert. But the function itself doesnt affect anything right? what am
> going
> > to write now will i guess by merging duplicate orgs right?
> >
> > On Fri, Jan 26, 2018 at 2:59 AM, Jason Pickering
> > <jason.p.pickering@xxxxxxxxx> wrote:
> >>
> >> That looks correct.
> >>
> >> It is of course worth saying, that you should not attempt to perform
> this
> >> procedure on your production system, unless you have thoroughly tested
> it in
> >> a development environment!
> >>
> >> Regards,
> >> Jason
> >>
> >>
> >>
> >> On Thu, Jan 25, 2018 at 1:55 PM, moses mwale <isoftcom.ic@xxxxxxxxx>
> >> wrote:
> >>>
> >>> Am using psql. see attached if its correct loading procedure.
> >>>
> >>> On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring <knutst@xxxxxxxxx>
> wrote:
> >>>>
> >>>> Just run everything in your PgAdmin SQL interface.
> >>>>
> >>>> On Thu, Jan 25, 2018 at 1:22 PM, moses mwale <isoftcom.ic@xxxxxxxxx>
> >>>> wrote:
> >>>>>
> >>>>> okay nice thanks, is it to load user-defined functions the same way
> you
> >>>>> load stored procedures, by packaging the Java class or classes into
> a JAR
> >>>>> file and then loading the JAR file using the LOAD CLASSES statement?
> >>>>>
> >>>>> On Thu, Jan 25, 2018 at 11:38 PM, Jason Pickering
> >>>>> <jason.p.pickering@xxxxxxxxx> wrote:
> >>>>>>
> >>>>>> Hi Moses,
> >>>>>>
> >>>>>> You need to load both of those functions into your database.
> >>>>>>
> >>>>>> 1) Script to delete an orgunit is here
> >>>>>> 2) Script to merge two orgunits is here
> >>>>>>
> >>>>>> After that, just call the function with
> >>>>>>
> >>>>>> SELECT merge_organisationunits(source_uid,dest_uid ,strategy);
> >>>>>>
> >>>>>> where `source_uid` is the UID of the organisation unit you want to
> get
> >>>>>> rid of, `dest_uid` is the UID of the organisation unit you want to
> keep and
> >>>>>> move data to
> >>>>>> and `strategy` is one of the following:
> >>>>>>
> >>>>>> SUM: Returns the sum of the values, when there is overlapping data.
> >>>>>> MAX: Returns the max of the values, when there is overlapping data.
> >>>>>> MIN: Returns the min of the values, when there is overlapping data.
> >>>>>> AVG: Returns the mean of the values, when there is overlapping data.
> >>>>>> LAST: Returns the last value entered, when there is overlapping
> data.
> >>>>>> FIRST: Returns the first vale entered, when there is overlapping
> data.
> >>>>>>
> >>>>>> Note that this only applies to numeric data. For other data
> (Boolean,
> >>>>>> text, etc) which cannot be aggregated numerically, the last value
> will
> >>>>>> always be taken.
> >>>>>>
> >>>>>> The merge script will not handle situations where you have
> overlapping
> >>>>>> tracker/event data, so you would need to figure out how to handle
> that
> >>>>>> yourself!
> >>>>>>
> >>>>>> Hope that helps to clarify and good luck!
> >>>>>>
> >>>>>> Regards,
> >>>>>> Jason Pickering
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>> On Thu, Jan 25, 2018 at 5:30 AM, moses mwale <isoftcom.ic@xxxxxxxxx
> >
> >>>>>> wrote:
> >>>>>>>
> >>>>>>> Hey Jason And Knut, thanks for the information, i have been trying
> to
> >>>>>>> understand the sql script shared and tried to execute but to no
> effect,
> >>>>>>> please can you help me understand where to place the source id and
> orgunit
> >>>>>>> ids in the script after several attempts of try its has became
> more complex.
> >>>>>>>
> >>>>>>> I have attached a datavalue table and 1 duplicate orgunit to be
> >>>>>>> merged. please help me.
> >>>>>>>
> >>>>>>> On Mon, Jan 8, 2018 at 8:26 PM, moses mwale <isoftcom.ic@xxxxxxxxx
> >
> >>>>>>> wrote:
> >>>>>>>>
> >>>>>>>> Thanks very much, allow me to go through and implement the given
> >>>>>>>> knowledge
> >>>>>>>>
> >>>>>>>> On Sun, Jan 7, 2018 at 11:29 PM, Jason Pickering
> >>>>>>>> <jason.p.pickering@xxxxxxxxx> wrote:
> >>>>>>>>>
> >>>>>>>>> Hi Moses
> >>>>>>>>> We have some SQL scripts for this here.
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>> https://github.com/dhis2/dhis2-utils/blob/master/
> resources/sql/merge_orgunits.sql
> >>>>>>>>>
> >>>>>>>>> This script will not handle tracker data but could probably be
> >>>>>>>>> adapted fairly easily to do so.
> >>>>>>>>>
> >>>>>>>>> Regards,
> >>>>>>>>> Jason
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>> On Jan 7, 2018 7:28 AM, "Knut Staring" <knutst@xxxxxxxxx> wrote:
> >>>>>>>>>>
> >>>>>>>>>> Hi Moses,
> >>>>>>>>>> I would take a backup of the database and then do this with an
> sql
> >>>>>>>>>> script, just changing the sourceid (the database internal
> referent to the
> >>>>>>>>>> organisationunitid) in the datavalues table. But you may get
> blocked if the
> >>>>>>>>>> same period has been filled for both, in which case you may
> want to do a
> >>>>>>>>>> "NOT IN" or left join.
> >>>>>>>>>>
> >>>>>>>>>> Knut
> >>>>>>>>>>
> >>>>>>>>>> On Jan 7, 2018 10:29, "moses mwale" <isoftcom.ic@xxxxxxxxx>
> wrote:
> >>>>>>>>>>>
> >>>>>>>>>>> Hey devs is it possible to merge facilities without losing
> data,
> >>>>>>>>>>> some admin accidentally created other duplicates units into
> the system.
> >>>>>>>>>>> anyone knows how its done?
> >>>>>>>>>>>
> >>>>>>>>>>> developer_lusaka_systems
> >>>>>>>>>>>
> >>>>>>>>>>> _______________________________________________
> >>>>>>>>>>> Mailing list: https://launchpad.net/~dhis2-devs
> >>>>>>>>>>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> >>>>>>>>>>> Unsubscribe : https://launchpad.net/~dhis2-devs
> >>>>>>>>>>> More help   : https://help.launchpad.net/ListHelp
> >>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>> _______________________________________________
> >>>>>>>>>> Mailing list: https://launchpad.net/~dhis2-devs
> >>>>>>>>>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> >>>>>>>>>> Unsubscribe : https://launchpad.net/~dhis2-devs
> >>>>>>>>>> More help   : https://help.launchpad.net/ListHelp
> >>>>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>> --
> >>>>>>>> developer_lusaka_systems
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>> --
> >>>>>>> developer_lusaka_systems
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>> --
> >>>>>> Jason P. Pickering
> >>>>>> email: jason.p.pickering@xxxxxxxxx
> >>>>>> tel:+46764147049
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>> --
> >>>>> developer_lusaka_systems
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> --
> >>>> Knut Staring
> >>>>
> >>>> Department of Information, Evidence and Research
> >>>> World Health Organization, Geneva, Switzerland
> >>>> Office: +41 22 791 3683 Mob1: +33 6 4434 2931 Mob2: +47 9188 0522
> >>>> Skype:     knutstar
> >>>
> >>>
> >>>
> >>>
> >>> --
> >>> developer_lusaka_systems
> >>
> >>
> >>
> >>
> >> --
> >> Jason P. Pickering
> >> email: jason.p.pickering@xxxxxxxxx
> >> tel:+46764147049
> >
> >
> >
> >
> > --
> > developer_lusaka_systems
> >
> > _______________________________________________
> > Mailing list: https://launchpad.net/~dhis2-devs
> > Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> > Unsubscribe : https://launchpad.net/~dhis2-devs
> > More help   : https://help.launchpad.net/ListHelp
> >
>



-- 
developer_lusaka_systems