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 <bobjolli...@gmail.com> 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...@gmail.com> 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.picker...@gmail.com> 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...@gmail.com> > >> wrote: > >>> > >>> Am using psql. see attached if its correct loading procedure. > >>> > >>> On Fri, Jan 26, 2018 at 2:42 AM, Knut Staring <knu...@gmail.com> > wrote: > >>>> > >>>> Just run everything in your PgAdmin SQL interface. > >>>> > >>>> On Thu, Jan 25, 2018 at 1:22 PM, moses mwale <isoftcom...@gmail.com> > >>>> 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.picker...@gmail.com> 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...@gmail.com > > > >>>>>> 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...@gmail.com > > > >>>>>>> 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.picker...@gmail.com> 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" <knu...@gmail.com> 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...@gmail.com> > 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@lists.launchpad.net > >>>>>>>>>>> Unsubscribe : https://launchpad.net/~dhis2-devs > >>>>>>>>>>> More help : https://help.launchpad.net/ListHelp > >>>>>>>>>>> > >>>>>>>>>> > >>>>>>>>>> _______________________________________________ > >>>>>>>>>> Mailing list: https://launchpad.net/~dhis2-devs > >>>>>>>>>> Post to : dhis2-devs@lists.launchpad.net > >>>>>>>>>> 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.picker...@gmail.com > >>>>>> 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.picker...@gmail.com > >> tel:+46764147049 > > > > > > > > > > -- > > developer_lusaka_systems > > > > _______________________________________________ > > Mailing list: https://launchpad.net/~dhis2-devs > > Post to : dhis2-devs@lists.launchpad.net > > Unsubscribe : https://launchpad.net/~dhis2-devs > > More help : https://help.launchpad.net/ListHelp > > > -- developer_lusaka_systems
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp