• RFox (unregistered)

    That's not the frist time this has happened eitjer

  • (cs)
    What’s this I hear about you designing an overly complex migration process?
    Answer: No, the process is very simple. It involves pointing this friendly GAU-8 at them and opening fire.
  • Anonymous (unregistered)

    Seriously, can anyone suggest the correct way to handle this situation? That will be very useful for my future career.

  • Groundhog Boy (unregistered) in reply to Anonymous

    Yes, get someone else to do it. You don't win any cookies with this.

    Btw. this has to be The Netherlands. Yes?

  • krf (unregistered) in reply to Anonymous

    From my experience, you have two choices, prefered one being genocide with strong second of suicide.

  • k (unregistered)

    common case of an idiot (boss) who knows someone smart (not) who is known for beeing capable to develop a high end solution (idiotic pile of wtf) to solve their problem. And when you try to untangle this pile of bullshit you get criticism. so good...

  • Saskia (unregistered) in reply to Groundhog Boy
    Groundhog Boy:
    Yes, get someone else to do it. You don't win any cookies with this.

    Btw. this has to be The Netherlands. Yes?

    My thoughts/question exactly....

  • popostutzen (unregistered)

    I worked in a german health body for a while and the same kind of insanity is going on there. Doesn't have to be a dutch thing.

  • (cs) in reply to Anonymous
    Anonymous:
    Seriously, can anyone suggest the correct way to handle this situation? That will be very useful for my future career.

    The response to "What’s this I hear about you designing an overly complex migration process?" should be "I didn't design it. The overly complex migration process was already here when I started." followed by details of a couple of the ways the existing process can and did break.

    And add "But if you give me a chance I think I can design a simpler and less error-prone one." But only, of course, if you are prepared to follow through with that.

  • Flukywotist (unregistered) in reply to Anonymous

    Been in almost this exact situation and the smoothest way i found to deal with this, is to do it twice.

    Once their way which nearly always fails (best to race through this so you have more time).

    then the second time around is to do it properly and not tell them.

    It works far smoother and will save you getting annoyed by your boss

    if you can put a fake dos window on your computer throwing up gibberish every few seconds it will help keep them from interupting you.

  • Walky_one (unregistered) in reply to Saskia
    Saskia:
    Groundhog Boy:
    Yes, get someone else to do it. You don't win any cookies with this.

    Btw. this has to be The Netherlands. Yes?

    My thoughts/question exactly....

    Could easily be Switzerland too...

  • Wayne (unregistered)

    I did such a DB2->SQL Server migration, used Microsoft Access as the middlware. I was able to copy most of the tables via ODBC from DB2 and put them directly in SQL Server, but I only needed a direct copy with no parsing.

    But here I think it's probably a case of 'Sucks to be you.'

  • the beholder (unregistered) in reply to Walky_one
    Walky_one:
    Saskia:
    Groundhog Boy:
    Yes, get someone else to do it. You don't win any cookies with this.

    Btw. this has to be The Netherlands. Yes?

    My thoughts/question exactly....

    Could easily be Switzerland too...

    Hell, this could be anywhere the government regulations are braindead i.e. pretty much everywhere around the globe. Though I'm fairly certain this did not happen in Brazil due to:

    a) we have just two or three national social insurance institutions, unless you count at the city level. And then 20 is much much lesser than what we have. (Really, this is an understatement).

    b) A friend of mine is involved in the exact same migration, and I know they're still going through the bumps on the road described by the article.

  • Piotr (unregistered) in reply to Walky_one
    Walky_one:
    Saskia:
    Groundhog Boy:
    Yes, get someone else to do it. You don't win any cookies with this.

    Btw. this has to be The Netherlands. Yes?

    My thoughts/question exactly....

    Could easily be Switzerland too...

    Really?

    Consider a small European country with more than 20 social insurance institutions...

    "more than 20" sounds a little bit off in context of swiss health insurances.

    http://de.wikipedia.org/wiki/Liste_der_zugelassenen_Krankenversicherer_in_der_Schweiz

  • Steve (unregistered)

    Oracle and DB2 are a great boon to billable hours.

  • (cs) in reply to Anonymous
    Anonymous:
    Seriously, can anyone suggest the correct way to handle this situation? That will be very useful for my future career.

    start typing resume. then seek different job.

  • (cs)
    “Security is very important to us”

    No, no it is not.

  • EnterpriseIdiot (unregistered)

    "Security is very important to us."

    Other than "move", how does one deal with a catch-22 scenario where not doing the work gets you fired, and doing the work gets you in trouble because approval didn't come in time for the access to do the work you need to do to not get in trouble?

  • (cs) in reply to Groundhog Boy
    Groundhog Boy:
    Yes, get someone else to do it. You don't win any cookies with this.

    Btw. this has to be The Netherlands. Yes?

    You may be cLose. This story happen in Beglium. NAgesh was witness.

  • rob (unregistered)

    "they already have a data transfer process"

    Red flag statement.

    No, you still have to map the target data using your data sources, sometimes it is a straight element copy, sometimes a transformation, sometimes a default value.

    Sometimes additional data has to be "discovered" and mapped. For example, migrating a person record where the legacy data lacks an e-mail address, and the new target system requires a valid email. This requires creating lists of the person records to be migrated and a process for obtaining those email addresses.

    No one has a pre-existing process for data transformations, at best they have data transfer methods that have been used in the past for moving data, but the process does not exist for transforming the data.

    As a snide side note, the description of the x-field or customer field is not really that bad, this is typical cobol where the records had to be fixed length and to minimize the number of files, different record layouts were used within the same file. Not normalized database theory, but a pragmatic practice from the past.

  • (cs) in reply to EnterpriseIdiot
    EnterpriseIdiot:
    "Security is very important to us."

    Other than "move", how does one deal with a catch-22 scenario where not doing the work gets you fired, and doing the work gets you in trouble because approval didn't come in time for the access to do the work you need to do to not get in trouble?

    Well, you start by documenting the non-access issue as soon as you can. Emails to bosses, that sort of thing. You also hassle the approval-givers as much as you can/dare(*) - the idea here is to get them to do their job so you'll go away and leave them alone.

    (*) Delete as appropriate. I recommend deleting Mordac the Preventer.

  • Cliff (unregistered)

    Sometimes there's an argument for rekeying the damn lot as well. Seriously, price it up compared with the developer months or years, especially if it involves deriving or finding data. You get the data into as useful a form for the typing pool as you can, and let them get on with it.

  • Maltz (unregistered) in reply to devjoe
    devjoe:
    Anonymous:
    Seriously, can anyone suggest the correct way to handle this situation? That will be very useful for my future career.

    The response to "What’s this I hear about you designing an overly complex migration process?" should be "I didn't design it. The overly complex migration process was already here when I started." followed by details of a couple of the ways the existing process can and did break.

    And add "But if you give me a chance I think I can design a simpler and less error-prone one." But only, of course, if you are prepared to follow through with that.

    Also, when you run across a problem that you can't adequately solve with your own authority, run it up the flag pole and let your boss know sooner than later. Perhaps there really is a good reason things are done the way they are that you aren't aware of, or perhaps bringing the issue to the attention of higher-ups can get the right people in the same room to find a solution that meets everyone's needs. At the very least, your boss will want to be aware of the problem. People hate to be blind-sided.

  • (cs)

    That column - formatting stuff is child's play compared with the input file format for MODTRAN. Trust me: I know :-(

  • DB Elf (unregistered)

    I was going to add a comment but "Security is very important to us."

  • Klimax (unregistered)

    First thought it was this country, but then hit COBOL. No such thing here. (Central and Eastern Europe)

    Otherwise it wouldn't be likely too far from reality...

  • (cs)

    I was buttuming that this was par for the course in all countries.

  • John (unregistered) in reply to Dragnslcr
    Dragnslcr:
    “Security is very important to us”

    No, no it is not.

    Yes it is, or someone might find out :-)

  • John (unregistered) in reply to EnterpriseIdiot
    EnterpriseIdiot:
    "Security is very important to us."

    Other than "move", how does one deal with a catch-22 scenario where not doing the work gets you fired, and doing the work gets you in trouble because approval didn't come in time for the access to do the work you need to do to not get in trouble?

    You document everything. And make sure that you have a paper trail to take to your lawyer when you get him to file for unfair dismissal.

  • Shill (unregistered)
    Field ‘M’ is a substring across one of the database columns from the Patient database.

    What the hell is a "substring across a database column"?

  • John (unregistered)

    "suggest the correct way"

    Take their output as is, run it through your own code to translate into another format that makes sense.

    The hard part will be getting a list of all the rules.

  • Butthats 4Eva (unregistered)

    Well, the whole process is littered w/ wtfs... but this one really chaps my hide:

    "Since the data entry clerks weren’t allowed to access the test database (“It’s a development environment, and they’d only get confused,” William explained), the data had to be loaded into production. There, the clerks would correct it. Philipp had no access to production (“Security is very important to us”), so the DBA would copy the corrected data back to the test environment."

    Really? Someone tell me this is a joke.. If you really had to correct this data without a GUI, why not issue a sql script via your development staff to be issued to the test environment only? Or have a restore\SAN copy and reapply corrected script? And what's all this intermingled talk about test/development frankenbeast?

    As for the old school flat files, that's just a part of life. Just fluff for the story. But that process w/ the DBAs, test, and prod environments... gives me the shudders.

  • (cs) in reply to Butthats 4Eva
    Butthats 4Eva:
    As for the old school flat files, that's just a part of life. Just fluff for the story. But that process w/ the DBAs, test, and prod environments... gives me the shudders.
    That really depends on whether the flat files were using lots of EBCDIC tricks to make everything denser, or were coming from some sort of system that packed 6 characters into each 30 bit word with some sort of reverse endianness crazy in it too. Pray to God that they didn't use some native floating point format in there “for efficiency”, and that they never thought of having a special marker in a field that indicated that the following record was an extension of the current one.

    The “changing the meaning of a part of a supposedly-fixed-width record according to the value elsewhere” is a good example of bad data structure smell. It's where you tend to find things that would genuinely get better if they were converted to XML; at least you wouldn't be stuck with the fixed-length madness and the resulting contortions to work around it.

    (Not to take away the WTF-worthy handling of the current DBs though. Or the all-too-common “Change is against our security policy!” attitude being spread around. So much fail, so little space to comment on it…)

  • My Name Is Missing (unregistered)

    HIPAA violation, you can't just email patient data to some random dude named Stephen. Then again the HIPAA police seem less capable than Ferguson's.

  • erlando (unregistered)

    Everyone is missing the monster WTF in this story: They exchanged confidential patient data via email!

    Nevermind the flat files, strange "security" policies and head-up-arse DBAs.

    They. Exchanged. Patient. Data. Via. Email.

  • Butthats 4Eva (unregistered) in reply to dkf
    dkf:
    Butthats 4Eva:
    As for the old school flat files, that's just a part of life. Just fluff for the story. But that process w/ the DBAs, test, and prod environments... gives me the shudders.
    That really depends on whether the flat files were using lots of EBCDIC tricks to make everything denser, or were coming from some sort of system that packed 6 characters into each 30 bit word with some sort of reverse endianness crazy in it too. Pray to God that they didn't use some native floating point format in there “for efficiency”, and that they never thought of having a special marker in a field that indicated that the following record was an extension of the current one.

    The “changing the meaning of a part of a supposedly-fixed-width record according to the value elsewhere” is a good example of bad data structure smell. It's where you tend to find things that would genuinely get better if they were converted to XML; at least you wouldn't be stuck with the fixed-length madness and the resulting contortions to work around it.

    (Not to take away the WTF-worthy handling of the current DBs though. Or the all-too-common “Change is against our security policy!” attitude being spread around. So much fail, so little space to comment on it…)

    I agree with everything you say here. I think the problem is that a number of customers your company might work with won't change the formats they've been running with for the past decade or three. And agreeing to what the customer wants done can be the difference (from your competitor) that wins the huge contract. Figuring that sometimes these contracts can make or break companies, it might be a small price to pay to support their current layout(s).

  • laoreet (unregistered) in reply to My Name Is Missing
    My Name Is Missing:
    HIPAA violation, you can't just email patient data to some random dude named Stephen.
    Yeah! Everyone knows that only dudes named Nick, Albert or Bartholomew are HIPAA-approved.
  • Norman Diamond (unregistered) in reply to Shill
    Shill:
    Field ‘M’ is a substring across one of the database columns from the Patient database.
    What the hell is a "substring across a database column"?
    It used to be a string before my cat found it.
  • Martin (unregistered)

    Just call the police.

    This "process" can get you into jail..

  • skull (unregistered) in reply to Groundhog Boy

    I guessed it was Switzerland

  • (cs)

    Minus the HIPAA violations, this seems to be my project. Right now I am integrating a new SAP system into a customers IT landscape, and boy, everything they have right now screams WTF. Around 20 to 30 systems, all with different IDs for the same data object (it is a big publisher, so books and music sheets and CDs stuff like that), with no specs whatsoever in writing that anyone understands. Who hasn't worked with their homegrown shit systems for the last 15 years.

    Just now I got off the phone where I had to say "sorry, adding a line that this db table is mailed to some data entry clerks" does not mean to me that a. I start programming some job that sends an email, or b. that I have a clue as to what that email should look like. Table attached as CSV? Concatenated into one big string?".

    Before that I wrote an email that no, that other random ass table is currently not in any form integrated into our processes yet, because everyone was unsure whether we needed it, so I started with other, more important crap. Now it is needed (no one really knows why, it contains goods movement data which we also track somewhere else, already redundantly), and we needs it NOW!!!11eleven. I already heard via chat from a coworker who is onsite with the customer as opposed to me in the office that my email triggered some shouting and screaming... /sigh. And /evilgrin

    I just remarked to a coworker that maybe I should smoke weed on the job before I hurt someone.

  • (cs) in reply to Butthats 4Eva
    Butthats 4Eva:
    I agree with everything you say here. I think the problem is that a number of customers your company might work with won't change the formats they've been running with for the past decade or three. And agreeing to what the customer wants done can be the difference (from your competitor) that wins the huge contract. Figuring that sometimes these contracts can make or break companies, it might be a small price to pay to support their current layout(s).
    I've dealt with fixed-width records before (it was ancient scientific data though — 150 years of sunspot records, recurated a few times along the way — so no security problems) and as long as the data really is regular, it's not a big deal. Lots of languages have the right tools, if you know how to use them.

    It's the abuse of them that's the problem. It's equivalent to having columns in your spreadsheet that determine the meaning of other columns. If you're seeing that, good luck. (And take the time to really understand what's going on; once someone opens that cesspit, they tend to abuse it massively.)

  • Halo (unregistered)

    Dealing with financial ETL processes I see this sort of thing all the time. Two things, first always request the COBOL source for the file specs, they effectively are the documentation and are fairly easy to read.

    Second, you're MUCH better off using COBOL to read those files. It is COBOL's flexibility in defining structural layouts that produces these insane files but COBOL has absolutely no problem reading them, even in the case of discriminator-driven redefinitions. You can then define a series of data structures to normalize the data into other formats (even XML) and transform the source file quite easy and almost declaratively. COBOL can be fairly easy to integrate with other environments and languages and runs on virtually any platform.

    For those who might balk at writing something as arcane and verbose as COBOL, as long as you keep it to doing what it does best, file ETL, it honestly feels more like writing SQL and the majority of the code is declarative.

  • (cs) in reply to Halo
    Halo:
    Dealing with financial ETL processes I see this sort of thing all the time. Two things, first always request the COBOL source for the file specs, they effectively are the documentation and are fairly easy to read.

    Second, you're MUCH better off using COBOL to read those files. It is COBOL's flexibility in defining structural layouts that produces these insane files but COBOL has absolutely no problem reading them, even in the case of discriminator-driven redefinitions. You can then define a series of data structures to normalize the data into other formats (even XML) and transform the source file quite easy and almost declaratively. COBOL can be fairly easy to integrate with other environments and languages and runs on virtually any platform.

    For those who might balk at writing something as arcane and verbose as COBOL, as long as you keep it to doing what it does best, file ETL, it honestly feels more like writing SQL and the majority of the code is declarative.

    Rubbish -- all you need to do is write a FORTRAN program to interpret the COBOL file structure and job's a good 'un.

  • BillR (unregistered) in reply to cellocgw
    cellocgw:
    That column - formatting stuff is child's play compared with the input file format for MODTRAN. Trust me: I know :-(

    We produce an analytical application, which can accept many forms of input. Over the years, we come across potential customers who would like information moved from the aging application they use and into our app, and they often say that if this can be done then they would also like to buy our product.

    Latest feat was migrating from an end-of-lifed application made by an out-of-business company that uses a proprietary (and encrypted) storage backend. But they thankfully have an API. The only trouble is if you make an identical API request a second time, or a third, you might get a different result set a second time, or a third time. Or you might get the exact same result set -- twenty calls in a row. Until number 21, in which case you get different data out of it.

    I'm still not terribly sure what the heck is causing it to give out wonky results. It's not time/date based, and happens with contrived input. I think we'll just make one call and use whatever we get back.

    Oh, and the not-well-formed XML it spits out isn't really any bother at all...

  • (cs) in reply to Piotr
    Piotr:
    Walky_one:
    Saskia:
    Groundhog Boy:
    Yes, get someone else to do it. You don't win any cookies with this.

    Btw. this has to be The Netherlands. Yes?

    My thoughts/question exactly....

    Could easily be Switzerland too...

    Really?

    Consider a small European country with more than 20 social insurance institutions...

    "more than 20" sounds a little bit off in context of swiss health insurances.

    http://de.wikipedia.org/wiki/Liste_der_zugelassenen_Krankenversicherer_in_der_Schweiz

    Switzerland ahs 23(or 26 depending on how you count) "states"-That would match. Also there recently was an effort to make such a joint system for something similar to social security and cost went over board (eg. triple digit millions...) so that some states left the project and it's still not in production AFAIK. And it was IBM who was contracted (DB2...). So yeah, sounds a lot like it's from that project. Patient information could just be an obfuscation because information from social security is certainly very sensitive as well.

  • Unknown (unregistered) in reply to Groundhog Boy

    You get the process documented in writing. Then you make sure your boss understands why this will be time consuming. Every failed attempt is also relayed to your boss with why. In short, you always cover your ass.

  • Bobby HashTables (unregistered) in reply to Halo
    Halo:
    Dealing with financial ETL processes I see this sort of thing all the time. Two things, first always request the COBOL source for the file specs, they effectively are the documentation and are fairly easy to read.

    Second, you're MUCH better off using COBOL to read those files.

    Having done similar have to agree with the first point, ask for COBOL source, it's the best documentation you will get in that sort of environment. Have to strongly disagree with the second point, COBOL may be ok at doing ETL, but it makes you feel dirty and can make management think COBOL is useful and should stay for longer. Use Perl, Python, C# or anything else to do the ETL, COBOL is like Ebola, it even infects the doctors.

  • noname (unregistered) in reply to erlando
    erlando:
    Everyone is missing the monster WTF in this story: They exchanged confidential patient data via email!

    Nevermind the flat files, strange "security" policies and head-up-arse DBAs.

    They. Exchanged. Patient. Data. Via. Email.

    If they ever lose their production database they can ask the NSA for a backup.

  • Better curtains than Windows (unregistered) in reply to My Name Is Missing
    My Name Is Missing:
    HIPAA violation, you can't just email patient data to some random dude named Stephen. Then again the HIPAA police seem less capable than Ferguson's.

    Because US law totally applies to European governments!

Leave a comment on “The Data Migration”

Log In or post as a guest

Replying to comment #:

« Return to Article