DVLA computer database platform

Anybody any idea what it is? My guess would be Oracle but confirmation
would be useful and I haven't managed to do that by googling.

Even if it isn't would it not be reasonable to expect that it can be
queried using SQL?

If that is the case, how many records at a guess and would a query
similar to the one below take hours and hours to run?


SELECT COUNT(vin) as "Number of vehicles"
FROM vehicles
WHERE make = 'XYZ' AND SUBSTR(vin,1,3) = '123'

Assuming vin is an indexed field (which seems reasonable), possibly make
as well.


--
+-------------------------------------------------------------------+
| Pete Fisher at Home: |
| Aprilia Shiver Yamaha WR250Z/Supermoto "Old Gimmer's Hillclimber" |
| Gilera GFR * 2 Moto Morini 2C/375 |
+-------------------------------------------------------------------+

 

Too many unknowns IMHO

Why on *earth* do you want to know?

--
Catman MIB#14 SKoGA#6 TEAR#4 BOTAFOF#38 Apostle#21 COSOC#3
Tyger, Tyger Burning Bright (Remove rust to reply)
116 Giulietta 3.0l Sprint 1.7 GTV TS GT 3.2 V6
Triumph Sprint ST 1050: It's blue, see.
#www.cuore-sportivo.co.uk

 

A FOI enquiry that DVLA say they could do, but would cost more than £600
to answer so they won't.

Just to find out how many Gilera GFRs are actually registered in the UK.
All Gileras have a unique 3 digit code as the first three characters of
the VIN. I don't want the registration marks, or the VINs , just a
count. I suspect less than 50.

I think this may be their standard response to anything as a first '****
off' attempt. I wouldn't mind being able to charge 600 quid for typing
one line of SQL and writing one, probably 2 digit number in a letter or
email. Still it probably requires a working party, to form a committee
to set up a team to scale the task and write a full documentation suite
before it can be done. That's what the outsourced rip-off merchants I
used to have to deal with would say to justify it.


Idle curiosity TBF, the question has been asked of me more than once. I
don't have a mole at Piaggio these days, though when I did they guessed
possibly only two batches of 25 were officially imported.

So my next FOI enquiry is going to be:

"Can the DVLA vehicle registration database be queried using Structured
Query Language?"



--
+-------------------------------------------------------------------+
| Pete Fisher at Home: |
| Aprilia Shiver Yamaha WR250Z/Supermoto "Old Gimmer's Hillclimber" |
| Gilera GFR * 2 Moto Morini 2C/375 |
+-------------------------------------------------------------------+

 

You know, I'm not sure that the sum is too unreasonable.

For example, we manage services for a bunch of our clients, outfits as
big as the DVLA.

We have their databases, for good reason, because if they had them and
had direct access to them, there's high potential of stuff getting
broken.

So if one of those clients wanted a similar query running over the
dbs, then they'd have to come to us and take it out of pre-paid
support 'units'.

After all the faffing around, emails, testing the query, running it,
creating a report, etc, it'd probably cost something like that because
I'll bet the DVLA don't have the databases in house.

 

Well, there is a distinct possiblity that it's more complex that that,
and the risk determination etc etc may well cost more.

Heh.


--
Catman MIB#14 SKoGA#6 TEAR#4 BOTAFOF#38 Apostle#21 COSOC#3
Tyger, Tyger Burning Bright (Remove rust to reply)
116 Giulietta 3.0l Sprint 1.7 GTV TS GT 3.2 V6
Triumph Sprint ST 1050: It's blue, see.
#www.cuore-sportivo.co.uk

 

Locked in and held to ransom. Sounds familiar.

They did send an obviously standard response with a list of 'agents' who
might do it, for a fee naturally, including Experian.

Surely it's not beyond the wit of someone to allow *read only* querying
(hence no fear of breaking anything). I used to be able to do that on
the Local Land and Property Gazetteer using JDBC.

As I said, a one line query with a two line 'report'. The tea boy could
do it in a window running in one corner of a work station while he
surfed for porn on his tea break.

It was the response I expected from them TBH. I used to have to answer
FOI enquiries before I retired so I know the tricks for fobbing people
off.

I would rather they had given me an honest response like catman, a sort
of official WGAF.
--
+-------------------------------------------------------------------+
| Pete Fisher at Home: |
| Aprilia Shiver Yamaha WR250Z/Supermoto "Old Gimmer's Hillclimber" |
| Gilera GFR * 2 Moto Morini 2C/375 |
+-------------------------------------------------------------------+

 

What fucking risk?

Good job I don't really GAF.

--
+-------------------------------------------------------------------+
| Pete Fisher at Home: |
| Aprilia Shiver Yamaha WR250Z/Supermoto "Old Gimmer's Hillclimber" |
| Gilera GFR * 2 Moto Morini 2C/375 |
+-------------------------------------------------------------------+

 

I wouldn't be too sure that you will get an accurate figure though. I
used to work at one of the companies that licenses this data from the
DVLA, and was surprised how often, particularly for older or less
common makes and models, you just get some generic text such as "Other
non-British makes".

Or do you think you could identify a GFR (as opposed to any other
Gilera) just from part of the VIN?

 

That wouldn't totally surprise me, that's why I wouldn't rely on
'model'. I know of at least one 'GFR' as indicated on the V5 that's
actually a Crono (as confirmed by the VIN - and photos).

From my post up there ^^^^

"All Gileras have a unique 3 digit code as the first three characters of
the VIN." I should have added - which identifies the model.

Prefix Model 'rest of VIN'
164 Crono 125 30001> Engine #165.....
167 GFR 125 1006> Engine #166.....


That's the problem in a nutshell this data is 'valuable'. In fact the
agencies they pointed me to couldn't answer this exact query anyway as
their anonymised subsets don't even include the VIN. I could though ask
one of them how many Make=Gilera and Model=GFR and Postcode = AB1 2YZ
apparently, for an eye watering fee no doubt.

--
+-------------------------------------------------------------------+
| Pete Fisher at Home: |
| Aprilia Shiver Yamaha WR250Z/Supermoto "Old Gimmer's Hillclimber" |
| Gilera GFR * 2 Moto Morini 2C/375 |
+-------------------------------------------------------------------+

 

Point of order - none of those is written by Catman - he has a man to
do that sort of thing, don't y'know.

 

I crave the indulgence of the house and will consider a rephrased
retort.


--
+-------------------------------------------------------------------+
| Pete Fisher at Home: |
| Aprilia Shiver Yamaha WR250Z/Supermoto "Old Gimmer's Hillclimber" |
| Gilera GFR * 2 Moto Morini 2C/375 |
+-------------------------------------------------------------------+

 

Try running a large query on a database that's being used for (I expect)
lots of other stuff. Is it going to grind to a halt, and cause lots of
other people to be unable to work, increase in calls to the helldesk,
possibly cause a load of other systems to start barfing, which may, or
may not then need manual intervention.

You'd hope not, but having run systems rather smaller[1] than the DVLA's
there's no way on earth I'd let that kind of query be run without
getting some very serious impact analysis. Which in itself costs money.

If I was presented with that FOI, in a similar situation, I'd be
sympathetic, but operating within the rules that the boss puts down, it
would almost certainly be either too 'risky' or too expensive.

Sorry

[1] Depending on your definition of smaller. While our systems were not
particularly well designed, and had to operate in near real time, we
were adding records at something like a rate of 20 million rows per day.
At that point even the daily *purge* had a noticeable performance
impact, and if you did it outside very well defined traffic limits
firstly the system as a whole would fall in a heap, and second you'd be
asked to explain which part of the business processes you were having
trouble understanding. The type of question that you suggest would have
brought it to it's knees almost immediately[2]

[2] We had a non-production, offline DB to do precisely that kind of
thing on. Was updated daily. Even that caused issues until we realised
that it was sharing spindles with the production DB [3]

[3] Yes, the whole operation was held together with chewing gum and
string, but we had no money to do anything else bu make money [4}

[4] Yes, yes I know.

--
Catman MIB#14 SKoGA#6 TEAR#4 BOTAFOF#38 Apostle#21 COSOC#3
Tyger, Tyger Burning Bright (Remove rust to reply)
116 Giulietta 3.0l Sprint 1.7 GTV TS GT 3.2 V6
Triumph Sprint ST 1050: It's blue, see.
#www.cuore-sportivo.co.uk

 

See my other answer. Select * from messages sort by message_received;

Would bring at least one of the systems I used to manage to a screeching
halt. Almost immediately.


--
Catman MIB#14 SKoGA#6 TEAR#4 BOTAFOF#38 Apostle#21 COSOC#3
Tyger, Tyger Burning Bright (Remove rust to reply)
116 Giulietta 3.0l Sprint 1.7 GTV TS GT 3.2 V6
Triumph Sprint ST 1050: It's blue, see.
#www.cuore-sportivo.co.uk

 

I see your document writer starts early on Thursdays.

 

Surely any properly designed system should cope? I used to work every
day with a Siebel CRM system with an Oracle back end that stored every
single service request and complaint received by a large local authority
for the last five years. It had a simple QBE front end for us mere
users. The worst that could happen IME was that it would 'time out'.


How many registered vehicles, compared with say the number of individual
names in the 1911 census records ? Or nearly all the births, marriages
and deaths registered in the UK since 1837 (have a look at FreeBMD)? A
properly designed front end should be able to isolate the database from
the query so as to prevent such problems and if I was the DVLA FOI
officer I would have jolly well made sure it was included in the
Information Requirements Analysis for the system. I wasn't expecting
unfettered access to Joe Public to write their own SQL.

If all else fails have a 'shadow' database that queries are allowed to
hit. That's how the suppliers of our departmental system approached
allowing web interface enquiries.

Which should have been done years ago IMO. FFS if a volunteer setup like
FreBMD can manage it !

Ah yes, I forgot, Freedom of Information does not mean Free Information.

De nada.

--
+-------------------------------------------------------------------+
| Pete Fisher at Home: |
| Aprilia Shiver Yamaha WR250Z/Supermoto "Old Gimmer's Hillclimber" |
| Gilera GFR * 2 Moto Morini 2C/375 |
+-------------------------------------------------------------------+

 

See my other reply.

--
+-------------------------------------------------------------------+
| Pete Fisher at Home: |
| Aprilia Shiver Yamaha WR250Z/Supermoto "Old Gimmer's Hillclimber" |
| Gilera GFR * 2 Moto Morini 2C/375 |
+-------------------------------------------------------------------+

 

What on earth makes you think the DVLA's system is properly designed?
Otherwise I'd agree.

ITYF the system was designed and built before the FOI act came in. ICBW
though. As such, the requirements analysis probably never included ad
hoc queries, even if there *was* a proper requirements analysis...

Also our approach. Also expensive. Spend the money, or reject ad hoc
queries? Which option would you prefer you tax money was spent on (to
bring it to a personal level) [1]

Strue, sadly

[1] Vastly over simplistic, I know.


--
Catman MIB#14 SKoGA#6 TEAR#4 BOTAFOF#38 Apostle#21 COSOC#3
Tyger, Tyger Burning Bright (Remove rust to reply)
116 Giulietta 3.0l Sprint 1.7 GTV TS GT 3.2 V6
Triumph Sprint ST 1050: It's blue, see.
#www.cuore-sportivo.co.uk

 

Good point fairly made.

I'm not asking for a web query interface for the public, though they
manage it for the Census records. Just assuming that as DVLA own the
data they might want to be able to do constrained queries easily
themselves. With the right front end you could eliminate the nightmare
scenarios you are quoting. When I was dealing with FOI enquiries we had
to be in a position to justify the cost of (not) answering them. OK, if
it meant building a new front end, or complex queries requiring more
than one pass to collate the answer we would use the too expensive
argument. If it meant just running the report generator on our database
with parameters that would easily extract the required answer we would
do it for nothing. For example, how many food premises in Smethwick were
inspected last year, or how many Permitted Process applications were
received. Though TBF if we couldn't answer those we would be in trouble
with the FSA (no not that one) or DEFRA.

As I said, because at our place none of the highly paid IT professionals
could work out how to verify an address entered in to a Mandoforms web
form service request against the LLPG [1] I had to roll my own JDBC
plugin to squirt off a couple of SQL queries. Once armed with the
connection string (read only naturally) it took me about half an hour to
figure out the table structure and how to do it.


Does the DVLA vehicle check and on-line SORN/tax syatem hit the live
database I wonder? Perhaps a shadow one (probably not up to date)
already exists.

If ICBA I might email one of their agents for a quote just out of
interest, but as I said, they don't have the VIN, so it may be
impossible to sort the GFR wheat from the scooter chaff anyway,

[1] There was a built in SOAP service for just such a requirement but
none of the outsourced IT bods knew or cared how it worked. You can
guess why.
--
+-------------------------------------------------------------------+
| Pete Fisher at Home: |
| Aprilia Shiver Yamaha WR250Z/Supermoto "Old Gimmer's Hillclimber" |
| Gilera GFR * 2 Moto Morini 2C/375 |
+-------------------------------------------------------------------+

 

I like your style. Mind you, it's probably already been left on a USB
hard drive on the back seat of a bus.

Actually, all I need is a filtered subset with two fields. In fact, just
the first three characters of one of them. How hard can that be - oh
hang on, it's not difficulty, it's 'commercial value' we are really
talking about.


--
+-------------------------------------------------------------------+
| Pete Fisher at Home: |
| Aprilia Shiver Yamaha WR250Z/Supermoto "Old Gimmer's Hillclimber" |
| Gilera GFR * 2 Moto Morini 2C/375 |
+-------------------------------------------------------------------+

 

It was designed by EDS. How could it be any less than perfect?