Friday, 31 December 2010

Social Networks for the DBA


There are a lot of social networks about.  I gave up Facebook because I got fed up of people throwing sheep at me.  I managed to solve one Facebook mystery – the reason that all these gorgeous Russian babes wanted to be my friend.  If you look up Jack Whittaker on Google you will find that Jack Whittaker of Culloden, West Virginia won $300 million of the state lottery – he even looks a bit like me! 

Babe Magnet

I can’t get on with Twitter – I don’t have anything to say to the world in less than 140 characters.  Neither do most Twitter users, but they just say it anyway. 

So – LinkedIn, then.  It’s rather more adult than most social networks.  The idea is that you can connect to other professional people, learn what they do, give and get advice on serious and not-so-serious subjects, look for jobs, maybe find potential new members of staff. 

Start with your profile.  Click here to see mine:

Essentially it’s your CV online – what you do, where you work, how long you have been doing it.  You can say as much or as little as you want, you can put all your past jobs in there in or just your current one.  Make sure you say the key words that matter, as this is what people look for.  For example, if you want people to know that you are familiar with SQL Server 2008, make sure it’s in your profile, or your Specialties (sic - sorry about the American spelling). 

Then you want to make connections.  Start with the people you work with – if they are already on LinkedIn, you can send them a request.  Most people are happy to connect to you.  I have 20 or so pending requests - possibly people who wish to deny knowing me, but I suspect really they don't look at Linkedin very often.

Here's some advice that I found useful - it's ten mistakes, so just do the opposite!

Here’s why – suppose I was an employment agency, looking for a French speaking DBA in Bristol.  Select Advanced Search, put in the keywords DBA and French, put in the location, and it finds 30 DBAs for me with at least some knowledge of the language.  I can look at those people’s CVs, connect to them, send them a message if I want.  And it’s free.  Now that’s much more attractive to an agency than paying 20 grand a year to access the job sites. 

From my point of view, the more connections I have, the easier it is for them to find me, and the closer I am to the top of the list.  I have 200 or so connections; those people in turn know 40,000 other people; and the 40,000 know 3,000,000 or more.  So I am three degrees of separation away from all those people.  I may not know the man in Mauritius at the other end of the chain, but I know someone who knows someone who knows him personally – and that means I can get in touch with him directly if I want to.  And if he happens to be looking for a French-speaking DBA to work on a tropical island paradise, he can get in touch with me. 

Helpful hint - if you ever have anything to do with an employment agency, always send them a request to link in - they usually have several hundred contacts.

The other thing that is worth doing is to join groups which are related to your professional interests - so for example, go to groups, type in Qualitative Data Analysis, and it finds three groups.  Join up, read the discussions, contribute.  If someone sounds sensible, tell them so and ask to link to them. 

We were arguing in the office about whether it's worth rebooting a Server on a regular planned preventative maintenance - some said yes, others took the view that if it ain't broke, don't mess with it.  I started a discussion on one of the many DBA groups and pretty soon we had a consensus - leave well alone. 

Remember that you are marketing here, rather than selling - you want agencies to find you and it is easier for them to do so if you know lots of people in the field.  Try to avoid the direct sell - there's a guy who posts on one of my groups to say that he is looking for a job but never posts otherwise - I get the impression that he is posting everywhere, essentially spamming us. 

As well as that, agencies (and even some companies) post jobs on these groups - if a relevant one comes up, great, apply.  But if you see something that isn't relevant, it might interest one of your contacts.  Pass it on - say "It's not really suitable for me, I'm looking for X rather than Y, but I thought you might be interested" 

Think about where that gets you.  Someone in your field now knows what you are looking for.  They feel well-disposed towards you, even if they are not actually job hunting.  If they see X, there's a pretty good chance that they will tell you about it.  If an agency approaches them, there's a good chance that they will refer you. 

So you can market yourself online, you can learn stuff and get technical questions answered, you can have fun.  What’s not to like?

Happy New Year!

Wednesday, 8 December 2010

Job Hunting

OK, so it's a bit off to one side of the other things I have posted on this blog so far.  But I am job-hunting, and no doubt others are too, so someone out there might find it useful.  There seem to be more websites advertising jobs than there are jobs.  Here's my list. 

If you get your CV uploaded, then employment agencies really do trawl through these sites and they really do ring or email you.  But although it's free to you, it isn't free to them - it costs an agency £10,000 a year or more for just one of these sites - so agencies only sign up for one or two.  Hence you need to load your CV on at least a few of these. 

Set up alerts.  This seems to be a scraper site - it picks up the jobs from lots of other sites and delivers them to you in one long email.  I set up an alert for DBA which delivers a manageable list every day.  But add the magic word SQL, and it delivers far too many - so I have to put up with the Oracle jobs too. 

Set up alerts; Upload CV - an old reliable favourite

Set up alerts; Upload CV - another old reliable one

Computer Weekly
Set up alerts; Upload CV

Career Builder
set up alerts; upload CV

CV Library
Set up alerts; Upload CV
Bizarrely, they sent me a list of jobs I might like today, including a Drama Teacher in Mansfield, which is a bit odd since I told them I was looking for a SQL DBA post in Bristol <sigh>

Set up alerts; Upload CV
Thanks to Richard for spotting that I was linking to the US site
CW Jobs
Set Up Alerts; Upload CV

IT Job Board
Set Up Alerts; Upload CV

Planet Recruit
Set Up Alerts; Upload CV


Set Up Alerts
Slightly cockeyed – if you edit your alert, and click on the Save Changes button, it unsubscribes your alert.  There is a Save Changes button and an Unsubscribe button, and someone has screwed up.  It’s easy to work around – just subscribe again – but you need to be aware of it.  I’ve emailed them to report the bug – let’s see if they fix it before you read this!

Total Jobs
Set Up Alerts; Upload CV
I think this is linked to one of the other job sites here.  I went to load my up to date CV and it was already there, but the rest of the entries related to things I was applying for five years ago.  A little bit spooky...

No Good for Me

Jobs in the Public Sector and Charities
Nothing for the DBA here – only one job in the whole country so much as mentions SQL.  Clearly IT employers in this sector shop elsewhere

Job Centre Plus
Have you ever heard of anyone going to the Job centre and coming away with a job?  I did hear of it happening once, but that was in France with their ANPE organisation.  You are welcome to try – but I put in SQL Server DBA and it came back with a couple of Data Input Clerk vacancies

Empty Lemon
Great name!  It's an anagram of Employment, geddit?  
Great logo - it's an empty lemon!  Ha ha!
Shame it never has any DBA jobs...

Still to sign up for:






I haven't bothered with these as I don't think you will find many DBA jobs in The Guardian and the other nationals.  But it doesn't hurt to list them, as what is true for me may not be true for you.  

MyJobGroup Network - e.g.

Guardian Jobs

Independent (

Telegraph Jobs

Times Online

London Jobs


Just London Jobs

Good Luck!

Friday, 3 December 2010

Extracting Different Bits Of A Single Field

So what do you do when some crazy person designs a database with a single field to hold three separate bits of data?  Of course it probably wasn’t designed as such – probably somebody using the database decided that it would be neat if they could record three bits of data about the incoming call.  Rather than asking the developers to add a couple of extra fields, some bright spark decided to use the Category field to contain two or three things. 

So – let me show you what I mean. 

Audit Approach - Audit Approach Manual - Other
NHS - Other NHS
NHS - Audit Responsibilities
NHS - Audit Responsibilities
NHS - Accounting - Financial instruments
Local Government - Housing Benefits
Local Government - Housing Benefits
Local Government - Housing Benefits
NHS - Audit Responsibilities
NHS - Audit Responsibilities
Local Government - IFRS Conversion - PPE and other non-current assets

So sometimes there are two categories, and sometimes three.  Fortunately they always use a hyphen between them. 

But now I have to create a report which reads in this single field and splits it into three – before the first hyphen, between the hyphens, and after the second hyphen (if there is one). 

Category 1

So, the first bit is easy:

       --the original single field
       -- cat 1 - before first hyphen
       substring(ca_full_name, 1, charindex(' - ', ca_full_name) )  
as Category_1,

SubString() lets you read in a text field, select a start point, then select a finish point.  So if your text says “Antidisestablishmentarianism” you can say

select substring ('Antidisestablishmentarianism', 1,4)

and get the result “Anti”. 

But in this case we don’t know where to stop, so instead of specifying the end point as a number, we have to use the Charindex() function. 

select charindex ('i', 'Antidisestablishmentarianism')

This means look through the text until you find the first letter ‘i’. 
The answer is 4

So the following code says look through the name until you find space hyphen space

charindex(' - ', ca_full_name)

Okay so far?

Category 2

Here’s how to get the middle bit.  I’m indebted to Stephen Lasham, who lives in New Zealand and wrote this article back in 2004 (isn’t the Internet wonderful?).  The trouble with Charindex() is that it only finds the first occurrence of the thing you are looking for. 

Extracting a string from between two delimiting characters

       -- cat 2 - from first hyphen to second hyphen or end
       -- when no second hyphen,
       WHEN CHARINDEX(' - ',(SUBSTRING(ca_full_name,
CHARINDEX(' - ',ca_full_name,1)+1,99))) = 0
       -- then extract from first hyphen to the max length of 99
       THEN LTRIM(RTRIM(SUBSTRING(ca_full_name,
CHARINDEX(' - ',ca_full_name,1)+3,99)))
       -- else extract from the first hyphen up to second hyphen
       /* <text>> */ ca_full_name,
       /* <start > */      CHARINDEX(' - ',ca_full_name,1) + 3,
       /* <length> */      CHARINDEX(' - ', SUBSTRING(ca_full_name,
                            CHARINDEX(' - ', ca_full_name, 1)
+1,    99)) - 2 ) ) )
       END    AS Category_2,

So this time, there are two steps.  If there isn’t a second hyphen i.e. there are only two categories in the field, start not at the beginning but at the first hyphen, then read to the end.  The Ltrim() and Rtrim() bits get rid of any leading or trailing spaces, so it looks nice. 

If there IS a second hyphen, Stephen’s code starts at the first hyphen, then reads to the second hyphen. 

Category 3

Okay, nearly there. 

--  cat 3 - after second hyphen (if present)
       -- when no second hyphen,
       WHEN CHARINDEX(' - ',(SUBSTRING(ca_full_name,
CHARINDEX(' - ',ca_full_name,1)+1,99))) = 0
       -- then print nothing
       THEN (SUBSTRING(ca_full_name,0,0))
       -- read back from the end until you get to the hyphen
       reverse(substring(reverse(ca_full_name), 1,
charindex(' - ', reverse(ca_full_name))-1))
       END                   as Category_3

So, if there isn’t a second hyphen, then print nothing at all (start at 0, stop at 0).  Because there isn’t any third bit, see?

But if there is a third bit, what I’ve done is read it from the end using the Reverse() function

select reverse ('Antidisestablishmentarianism')

Which of course gives us msinairatnemhsilbatsesiditnA. 

The code reads in the text, reverses it, reads through it until it gets to the hyphen.  Then it takes the backwards text up to that point and reverses it again so that it comes out facing the right way.  So we end up with this:

Local Government
Whole of Government Accounts
Accounts and Accounting
Service concessions and leases
IFRS Conversion
Employee benefits
Local Government
Capital Finance
Financial Instruments
Local Government
Operational and Legal Environment

Foundation Trusts
Additional Services
Non-Audit Services
Foundation Trusts
Service concessions and leases
Audit Responsibilities

Local Government
IFRS Conversion
IFRS conversion
Local Government
Operational and Legal Environment

All done!  I hope this is useful.  And just think – none of this would have been necessary with a better database design in the first place.