Thursday, 12 February 2015

Modern Jobs - the Process Development Coordinator

Our company Intranet had a job post today.  I have not the faintest idea what a Process Development Coordinator does. 

So I read the Job Description

This is a varied role, working on a premium brand which is still in growth phase. The account is open 7 days per week between the hours of 0800 -2100 and you will be rostered rotationally across those hours, however the majority of your hours will be worked 9-5:30 Monday to Friday. This is a high profile role with lots of client contact and requires the personal qualities of self-motivation, learning orientation, analytical thinking, process orientation and patience.

Candidates must have a demonstrable understanding of training.

The main purpose of this role is:
·           Undertake ongoing root cause analysis to identify opportunities for process improvement
·           To work  with the clients to ensure consistency of process and approach across both sites
·           To provide recommendations around achieving a demonstrable improvement in challenging external quality measures
·           Manage the Training Associate team workflow
·           support operations to achieve high standards to enhance the customer experience and increase productivity
·           Share in the operational workflow and customer facing duties.
Required abilities
·         Committed to the delivery of an exceptional level of customer service
·         Excellent communication skills
·         Ability to pay close attention to detail
·         Natural ability to inspire, motivate and energise others
·         Shows respect to others in a positive manner and builds strong working relationships
·         Strong team player and role model
·         Enthusiastic, positive, resourceful and resilient
·         PC literate

I'm still none the wiser.  Am I getting old?

Tuesday, 10 February 2015

The Missing Sundays Mystery

One of our key customers sends us a datafile every day, and it automatically gets loaded.  Except sometimes - it doesn't...

<Cue theme from "The Twilight Zone">

So I wrote some code to see when data was being loaded.  Sure enough, it works every day of the week, but on the Sunday between Christmas and New Year - it failed to load.  The following Sunday - it worked fine!  But since then, it failed every Sunday.  Only on Sunday.  Then on Monday, a double dose of data gets loaded. 

Now - I didn't tinker with the database - it's an Oracle system, running under a Cron job  under Unix.  So a long way over to the Dark Side.  I don't even have access to the box it runs on - even if I wasn't afraid to touch it, I couldn't tinker. 

And the developers swear blind that they haven't mucked about with the application for months...

I looked in the FTP site - there was a litter of .tmp files, apparently some sort of by-product of the loading, renaming and moving process.  No clues there though, and removing them made no difference. 

Luckily the client was able to send us a log showing us what files were sent and at what date and time.  Helpfully, he highlighted the missing Sundays in yellow.  and all became clear.  The ones that worked were sent to us at various times ranging from about 0500 to 0700.  The ones that failed were sent to us at various times between 0700 and 0800. 
Guess when the Cron job runs?

The job to send us the data is automated - but runs when other things finish, hence the variety of times.  And Sundays?  "That's the day we bounce our servers..."

Friday, 24 January 2014

SSMA - SQL Server Migration Assistant

At a DBA reunion dinner just before Christmas I was talking to a friend and mentioned that I was using SSMA to migrate from Oracle to SQL Server 2012.  "Poor you," he said "That program's a real dog."

I was surprised at his attitude.  I had by that time migrated a couple of databases without a great deal of difficulty and since Christmas I have migrated about 20.  Small things, admittedly, nothing larger than a dozen tables and a couple of million rows of data,  but still, "dog" seemed unfair.  Turned out he had last used the Migration Assistant that came with SQL Server 2000 - and like so much else, Microsoft has been doing some work on it.

Somewhere in Redmond, WA - early this century...
 "OK, guys, we wanna make it easy for people to move to us from Oracle - any ideas?"
 "Er, Mr Gates, how about a migration assistant that actually works?"
"Smart thinking Wilbur, here's a few million dollars, go away and spend it wisely".

 Here's what Wilbur came up with:

I log onto my development server and click on the icon you see here.  The SQL Server Migration Assistant for Oracle opens.  (Incidentally, I believe that there are at least four SSMAs - one for Access, this one for Oracle, one for Sybase and one for MySQL.  Plus versions.)  Here's the team blog.

 Up comes the splash screen:

and then the home screen:

Two menu bars at the top.
Top left quadrant is the Oracle Metadata Explorer.
Bottom Left Quadrant is the SQL Server Metadata Explorer. 
The other two quadrants are for showing you information, and they aren't terribly important - I'll touch on them in passing. 

Getting Started

Click on File, then on New Project.  Up pops the New Project Dialog box - enter a name e.g. Oracle2SQL - Databasename, and click on OK.  If you have lots of different databases to convert, it's worth making a note of which project is which - maybe start an outlook task and put the project name in the Title. 

Connect to Oracle

      Click on Connect to Oracle on the menu at the top of the screen – it will come up and ask for the connection details, and especially the password.  It's a good idea to get the Oracle DBAs to make a copy for you, so that you are never extracting data from the live system.  

      Enter the password.  After you click on Connect, check the bottom of the screen for progress messages (it takes a couple of minutes - loading this, loading that).  Wait for the magic word "Done".

      The top right quadrant now fills up with a list of the various schemas - the Oracle Source Schema, and the SQL Server Target Schema.  There is also a Type Mapping tab and a Properties tab.  You can make changes here if you want to.  I haven't done - generally it does a pretty good job without me tinkering.  And while I'm not totally certain about some of its decisions, I prefer to change them in SQL Server, where I can see the context.  More on this later. 

         Select the Oracle Schema (equivalent of the SQL database) to be converted:


Connect to SQL Server

        Click on the menu option that says "Connect to SQL Server".  

If it’s a new database, enter the name and SSMA will create it.   Do not change the name - Do NOT change from CAPS to Title Case, do not even fix a typo.  Be especially careful if you are doing one job after another – it assumes that you want to do the previous one again.  If you miss this and accept the existing name, you are probably better off abandoning this one and starting it again, being more careful next time. 

      As with Connecting to Oracle, this takes a little while - not as long-winded as Oracle though.  Look out for the word "Done".  When it appears, select the destination database in the SQL Server Metadata Explorer box.  The bottom right hand quadrant fills up with SQL Server information.  Honestly, it's so dull, there's no point in looking at it - I certainly don't intend to show you a picture.

In the Oracle metadata explorer box, highlight the schema to be converted - the easiest way is to right click on it - then Click on Convert Schema.  (For some reason Microsoft show Ctrl R as a shortcut, even though they underline the n of Convert.  Looks a bit sloppy to me, but I’ve never bothered with these shortcuts as in this case it’s easiest to do it with the mouse). 

Check for Warning messages in the dialog box at the bottom of the screen – it may be helpful to copy them to a Notepad box, or an Outlook task, if you created one earlier.

Right click on the Database name in SQL Server metadata explorer and select Synchronise with the Database.  It spins its wheels for a few moments and then pops up a box that is entitled "Synchronise with the Database". 

You are going to find this difficult to believe, but what you do now is press OK.  And off it jolly well goes, whistling a quiet little tune to itself.  It synchs the Oracle structure with the SQL Structure.  No data as yet, but we are getting there.  

      Leave the SSMA program for a moment, go into SQL Server, and take a full backup of the database. You could do this later if you prefer.  Select each table in turn, Script as Drop & Create to New Window,    
      Review the possible errors noted above in the Outlook task.  

      A Few Words about Data Types

      The data types in Oracle do not exactly match those in T-SQL, but the defaults do the job pretty well.  Despite this, I can fairly safely guarantee  that the commonest warning message you see will be:
Warnings: O2SS0356: Conversion from NUMBER datatype can cause data loss.
      This takes the Oracle NUMBER datatype and guesses that it’s a SQL Server FLOAT.  And of course, it depends.  Consider whether float should be INT e.g. for an ID, or FLOAT e.g. for a height. On most of the databases I’ve converted, INT seems to be a better choice.  

      Strings get turned to Varchar – fair enough – you might want to think about Nvarchar if you have any Unicode records.   One exception - varchar(1) should be char(1).  (What earthly use is a varchar(1), eh?)

      Then Execute to change the table.            
Save Script in Data Management Program documentation e.g. Q:\Data Management Programme\Oracle2SQL Migration\SQL\Be_The_Key

      Run exec sp_helpdb [BE_THE_KEY_ENG_NIR]  and note the logical and physical file names in your Outlook Task.  DO NOT CHANGE THE NAMES AT THIS STAGE!   Even if they are horribly wrong.  Do that and SSMA will decline to migrate the data, and you have to start all over again. 
      See notes on untangling the database.

      Migrating the Data 

      OK, back to SSMA now.  In Oracle Metadata Explorer, right click on the Schema and select Migrate Data.  Off it goes - you can watch the numbers clicking upwards if you like.  Be patient - it isn't hugely quick, especially of you have something complicated,  But generally it will think for a while and then come up with a Finished message:

      Look out for that success rate column  - it should be straight 100%.  If it isn't, you will need to do something about it, but usually it comes out fine.  Save the project, save the schema, and Exit.  Congratulations - you have just migrated an Oracle database to SQL Server.