We need it for auditing
-
Our data is divided into four main areas, each with its own logical data structures and underlying db tables.
We have a master audit table that contains an xml representation of all four data structures for every record in our database.
In each row, we have xml-ified versions of every data structure. All strings are filled to their maximum allowable size (based on the other tables) with blanks, even if they contain no data. Every logical group of fields has createdBy, changedBy, createdDate and changedDate fields. The createdBy and changedBy fields always contain the same value: the app login.
We all know that xml drastically increases the number of bytes required to represent a given object, but this thing has 64K rows for data that a quick glance tells me can be represented in under 100 bytes.
This table also accounts for 80% of the storage used in our database.
Why do we care? Because we ran out of space, don't have the budget to buy more disks, and are under a strict mandate to audit everything.
Ok, I get the auditing requirement, but we're under mandate to store the information, not mountains of blanks. It can be done intelligently. I proposed converting the xml to name=value pairs and writing a small routine to convert that back into our data structures so the auditors will be happy.
The Auditors were not happy. We need EVERYTHING stored, including the empty fields! But we can store an empty field more efficiently than by filling it with blanks. NO! We need you to store EVERYTHING! Ok, but you'll need to stick a crowbar in your wallet and provide the disks required for that. There's no money in our (Auditor's) budget for new disks. Then make a choice!
And it's only 9AM.
-
Nobody ever said you had to be smart to be an auditor, right?
-
But representing a varchar(10) containing "" as " " is altering the actual data and killing the auditability (is that a word? It is now!) of said data, isn't it?
-
Storing XML in the database indeed tends to eat quite some storage.
If all the sane options have been crushed by management, may i suggest gzipping the xml? :)
XML compresses like there's no tomorrow!
-
@Oneway said:
may i suggest gzipping the xml?
I thought of that, but the code that inserts data into this table is scattered across multiple applications, and there isn't time to code/test/deploy them all in this case.
I've been trying to figure out how to do it in [PL/]SQL (I have the luxury of being able to add a trigger, AND the fact that these rows are NEVER updated). Any advice would be welcomed!
-
@C-Octothorpe said:
I think the requirement is the other way around. You have to be a dumb fuck to be an auditor.Nobody ever said you had to be smart to be an auditor, right?
-
@snoofle said:
@Oneway said:
may i suggest gzipping the xml?
I thought of that, but the code that inserts data into this table is scattered across multiple applications, and there isn't time to code/test/deploy them all in this case.
I've been trying to figure out how to do it in [PL/]SQL (I have the luxury of being able to add a trigger, AND the fact that these rows are NEVER updated). Any advice would be welcomed!
"Excuses! That's all I ever get from you! So go and find where it's scattered across all the applications then! Get it done by lunchtime!"
-
@steenbergh said:
But representing a varchar(10) containing "" as " " is altering the actual data and killing the auditability (is that a word? It is now!) of said data, isn't it?
TOWTF (The Original WTF) is that, if the fields are storing all those blanks, they probably were defined as char(10) originally, not varchar(10).
TRWTF is the auditors not realizing trailing blanks are almost always insignificant, and that they can be gotten rid of.
-
I think the newer versions of SQL Server support automatic zipping of the actual database file. Probably not applicable to you, but just FYI.
-
@snoofle said:
And it's only 9AM.
Wait... what happened to the emergency from yesterday? The app you got the specs late for with the crazy short timeline you have to ship no matter what?
Does your company pull you off one emergency to do another? Is the app just in limbo now? Or maybe you just work 18 hour days and dedicate 6 hours to each daily emergency that comes up? Or maybe Snoofle is actually 14 people!
Seriously, you need to move to the West Coast where this shit doesn't happen. At least, I can tell you this for sure: if I was managing one emergency, and I was pulled off it to manage another, I'd leave the office and walk out that fucking door and go to Hawaii for a week. Thankfully I live in a Right To Work state.
@snoofle said:
The Auditors were not happy. We need EVERYTHING stored, including the empty fields! But we can store an empty field more efficiently than by filling it with blanks. NO! We need you to store EVERYTHING! Ok, but you'll need to stick a crowbar in your wallet and provide the disks required for that. There's no money in our (Auditor's) budget for new disks. Then make a choice!
Oblig. Red Dwarf:
Captain: "You have to give up the cat."
Lister: "I'm not going to give up the cat."
Captain: "Then you have 6 months in stasis."
Lister: "I'm not going to do 6 months in stasis!"
Captain: "Choose."I like just laying out the two options, put them both on the table, then say: "choose".
Or if you want a stupid geeky solution, IF this thread's assumption is correct that the blanks in the XML file are due to char(25) fields instead of having varchar(25) fields, you could pretty easily write a stored procedure to remove the blanks based on the length of each field, and a View to (virtually) replace them-- that should allow you to save enough space to keep it going another few weeks at least, and if an application has compatibility problems, you just point them to the View instead of the actual table.
-
@snoofle said:
@Oneway said:
may i suggest gzipping the xml?
I thought of that, but the code that inserts data into this table is scattered across multiple applications, and there isn't time to code/test/deploy them all in this case.
I've been trying to figure out how to do it in [PL/]SQL (I have the luxury of being able to add a trigger, AND the fact that these rows are NEVER updated). Any advice would be welcomed!
OK, obvious solutions first: why not use a trigger to call an external function to gzip the data?
-
@Mason Wheeler said:
OK, obvious solutions first: why not use a trigger to call an external function to gzip the data?
... which then emails the zip file to Gmail (which has plenty of storage space) with a unique subject line. Whenever the data is retrieved, connect to Gmail via IMAP, download the email with the unique subject line, unzip the zip file, and work with the XML.
-
@blakeyrat said:
Seriously, you need to move to the West Coast where this shit doesn't happen.
Getting pulled off of yesterday's emergency in order to deal with today's emergency is, unfortunately, standard practice in a lot of companies. Either you have been extremely fortunate, or, the West Coast is some sort of magical alternate-dimension utopia.
-
@El_Heffe said:
@blakeyrat said:
Seriously, you need to move to the West Coast where this shit doesn't happen.
Getting pulled off of yesterday's emergency in order to deal with today's emergency is, unfortunately, standard practice in a lot of companies.Yeah, but it's definitely NOT NORMAL for laid-back West Coast companies. I don't know, I really only have a lot of experience with Seattle companies, but it's definitely not normal in Seattle.
-
@all:
The original specs for the table were varchar2(...); The person who coded it said (exact quote from svn): "We fill the unused fields with blanks to prevent xml parsing errors".
I'm kind of the only experienced person here at this point so they throw me at something to diagnose and recommend, then throw junior developers at it to grind away while I try and solve the next problem. It's actually kind of an interesting role - if you give me the resources to do what the internal customers want, and junior developers that have even a slight clue.
With that in mind, the emergency from yesterday will be running for about the next 10 days straight. Personally, I am NOT going to tell my wife that I have to work over Thanksgiving (ah, the joys of being an independent consultant), but the others were told that they WILL be working over the holiday.
Re: de/compressing xml-in-clob: the DBAs that own the DB (it's not ours) will not allow us to make calls out of the db because of security concerns. Personally, I think they're using that as a blanket catch-all for I-can't-be-bothered. Essentially, the only way I'm going to pull this off is if I can do the de/compression in PL/SQL inside a trigger or SQL statement. Googling provides all sorts of solutions - outside the db, or coding a Java sproc, but we're not allowed to do that here.My take: if they tie my hands, then they get to buy lots of shiny new hardware; probably just before the db runs out of space. Then the DBAs have to deal with it.
-
@blakeyrat said:
@snoofle said:
And it's only 9AM.
Wait... what happened to the emergency from yesterday? The app you got the specs late for with the crazy short timeline you have to ship no matter what?
Does your company pull you off one emergency to do another? Is the app just in limbo now? Or maybe you just work 18 hour days and dedicate 6 hours to each daily emergency that comes up? Or maybe Snoofle is actually 14 people!
He just does the work of 14 people.
Seriously, you need to move to the West Coast where this shit doesn't happen. At least, I can tell you this for sure: if I was managing one emergency, and I was pulled off it to manage another, I'd leave the office and walk out that fucking door and go to Hawaii for a week. Thankfully I live in a Right To Work state.
Possible Pedantry Alert: I think you mean at-will. Right-to-work laws deal with union membership, specifically prohibiting a closed shop (i.e. requiring union membership as a condition of employment). Now if your company is unionized, I am quite curious to hear about this. For some reason that particular misunderstanding is very common on programmer forums.
More to the point, as I recall, snoofle is a contractor, so walking out would probably have very unpleasant consequences.
I do like your proposed solution though, that's pretty clever. I have a feeling some auditor will drop the hammer on it though.
-
@snoofle said:
if they tie my hands, then they get to buy lots of shiny new hardware
Didn't you RTFA? The auditors said there's NO MONEY... It's *your* problem. :)
-
@Justice said:
Possible Pedantry Alert: I think you mean at-will. Right-to-work laws deal with union membership, specifically prohibiting a closed shop (i.e. requiring union membership as a condition of employment). Now if your company is unionized, I am quite curious to hear about this. For some reason that particular misunderstanding is very common on programmer forums.
Oh! Did I use the wrong term! Let me just check to see if I care...
No, seems like I don't care.
-
@blakeyrat said:
Oh! Did I use the wrong term! Let me just check to see if I care...
No, seems like I don't care.
Just stop with your pedophillic dickweedery already.
-
@snoofle said:
I'm kind of the only experienced person here at this point so they throw me at something to diagnose and recommend, then throw junior developers at it to grind away while I try and solve the next problem. It's actually kind of an interesting role - if you give me the resources to do what the internal customers want, and junior developers that have even a slight clue.
That's very similar to how I work. We hire batches of contractors with project money or funding for a group of bug fixes. At the beginning and end of the fiscal year, it's only the full-timers. After the yearly funding starts rolling in, we end up with four or five times our normal staff.
Anyways, we get good and bad contractors. At the moment, we have a whole mess of under-performers. It takes them a week to do what I can do in a day. So, it's actually more productive for me to spend the week heads-down ignoring them than it is to dole out work to four contractors and babysit them all week. I recently turned around a project that was getting behind by telling two of the contractors to spend their time making pretty icons. It's pretty sad when the best contribution you can make to a team is to get out of the way.
-
@blakeyrat said:
[quote user="Justice"]Possible Pedantry Alert: I think you mean at-will. Right-to-work laws deal with union membership, specifically prohibiting a closed shop (i.e. requiring union membership as a condition of employment). Now if your company is unionized, I am quite curious to hear about this. For some reason that particular misunderstanding is very common on programmer forums.
Oh! Did I use the wrong term! Let me just check to see if I care...
No, seems like I don't care.[/quote]
Someone posts something under a "possible pedantry alert" and you... bother to post that you don't care?
<font size="-1">oblig. rejoiner: "and you bother to post back to him about it?" yes. i'm camping out at Panera hoping that the power will be back on when I drive home. slow night.
-
@Jaime said:
So, it's actually more productive for me to spend the week heads-down ignoring them than it is to dole out work to four contractors and babysit them all week. I recently turned around a project that was getting behind by telling two of the contractors to spend their time making pretty icons. It's pretty sad when the best contribution you can make to a team is to get out of the way.
Not as sad as when the best contribution you can make to a team is to get out of the way and you don't do it even when they tell you to.
-
The main criterion for being an auditor is that you don't have enough personality to be an accountant.
-
@snoofle said:
My take: if they tie my hands, then they get to buy lots of shiny new hardware; probably just after the db runs out of space.
FTFWIH (Fixed that for what is happening)?
Too bad the floods in Thailand has tripled the price of harddrives over the last couple of weeks!
-
@Zemm said:
Too bad the floods in Thailand has tripled the price of harddrives over the last couple of weeks!
Guess I'm glad I got my 2TB drive previous winter.
-
@steenbergh said:
Oooh, good catch. Snoofle, ask the auditors if they are concerned that the current system loses track of the number of trailing spaces in the data.But representing a varchar(10) containing "" as " " is altering the actual data and killing the auditability (is that a word? It is now!) of said data, isn't it?
You can also probably fry their branes by asking how to tell if some spaces get swapped with others, but that would be gratuitous cruelty.
-
@snoofle said:
My take: if they tie my hands, then they get to buy lots of shiny new hardware; probably just before the db runs out of space. Then the DBAs have to deal with it.
Good plan. Make it their problem, not your problem.
"Here are your choices, along with the CBA docs. Pick one. Until you do, I'm working on something else."
-
@snoofle said:
I've been trying to figure out how to do it in [PL/]SQL (I have the luxury of being able to add a trigger, AND the fact that these rows are NEVER updated). Any advice would be welcomed!
PL/SQL? So this is Oracle. You can use Table Compression if the DB version >= 11.1
"Table compression saves disk space and reduces memory use in the buffer cache. Table compression can also speed up query execution during reads. There is, however, a cost in CPU overhead for data loading and DML. Table compression is completely transparent to applications."
-
@snoofle said:
are under a strict mandate to audit everything.
You're not "auditing" it just by storing it. "Auditing" involves looking at the data and trying to make inferences about its validity, e.g. by examining it for consistency against other data.
If you are really strictly required to audit all of the data, then someone is not doing his job (or her job... quite likely "her" given the overall tenor of your post). Maybe that's something you can work with.
Finally, you said in another post that you are "allowed" to use a trigger. Allowing a trigger in a database that's apparently supposed to be pretending it's important is the real Meta-WTF here.
And your job sounds suicidally bad. Have you considered looking for a real job, e.g. table-wiper in at Spurger King?
-
@aliquot said:
@snoofle said:
I've been trying to figure out how to do it in [PL/]SQL (I have the luxury of being able to add a trigger, AND the fact that these rows are NEVER updated). Any advice would be welcomed!
PL/SQL? So this is Oracle. You can use Table Compression if the DB version >= 11.1
And, you can use backspace in SQL*Plus.
Oh wait...
-
@bridget99 said:
And, you can use backspace in SQL*Plus.
Oh wait...
???Something wrong with your terminal?
-
@Xyro said:
@bridget99 said:
And, you can use backspace in SQL*Plus.
Oh wait...
???Something wrong with your terminal?
Maybe I could fix this. I would rather just hate Oracle, though. I'm a Wolverine, and my hatred keeps me warm... a Wolverine.
-
@bridget99 said:
@Xyro said:
@bridget99 said:
And, you can use backspace in SQL*Plus.
Oh wait...
???Something wrong with your terminal?
Maybe I could fix this. I would rather just hate Oracle, though. I'm a Wolverine, and my hatred keeps me warm... a Wolverine.
Your hatred is contributing to global warming, and I feel it is my duty as a postmodern citizen of the local ethic majority to quench my existential Western guilt by doing something about it.
Type "
You just have a mismatch between what your terminal emulator is sending and the shell is expecting; one is ^? and the other is ^H.stty erase", then hit your backspace key, then press enter. That will assign whatever character code your backspace key sends to the "erase" control.
-
@Xyro said:
Type "
You just have a mismatch between what your terminal emulator is sending and the shell is expecting; one is ^? and the other is ^H.stty erase", then hit your backspace key, then press enter. That will assign whatever character code your backspace key sends to the "erase" control.What the... is this one of those Star Trek plots where they're getting messages from the past and when they finally arrive to the source of the message they find the people they've been talking to have been dead for years?
-
@El_Heffe said:
@El_Heffe said:@blakeyrat said:
Seriously, you need to move to the West Coast where this shit doesn't happen.
Getting pulled off of yesterday's emergency in order to deal with today's emergency is, unfortunately, standard practice in a lot of companies. Either you have been extremely fortunate, or, the West Coast is some sort of magical alternate-dimension utopia.@blakeyrat said:
Seriously, you need to move to the West Coast where this shit doesn't happen.
Getting pulled off of yesterday's emergency in order to deal with today's emergency is, unfortunately, standard practice in a lot of companies. Either you have been extremely fortunate, or, the West Coast is some sort of magical alternate-dimension utopia.Lot of person thrive on emergency after emergency! The next emergency will be escape mecanism for curent emergency.
-
If it's a CLOB then have the DB's turn Compression and Deduplication on at the CLOB level and Compression on at the table level, and rebuild the tables and indexes.
It's built into Oracle.
You will need at least a temporary increase in DB Size of at least the used space of your largest table.
-
Just like with the economic system known as capitalism, it's all about shifting the problem to someone else. This is an easy one:
- Have the entire disk compressed. Blame the DBAs for any performance problems.
- Install a ramdisk driver and put the table in RAM. Blame the backup system, or lack thereof, when it explodes.
-
@blakeyrat said:
@Xyro said:
Type "
You just have a mismatch between what your terminal emulator is sending and the shell is expecting; one is ^? and the other is ^H.stty erase", then hit your backspace key, then press enter. That will assign whatever character code your backspace key sends to the "erase" control.What the... is this one of those Star Trek plots where they're getting messages from the past and when they finally arrive to the source of the message they find the people they've been talking to have been dead for years?
Yes,^H. damnit^U^U^C
CS sucks.
-
@Faxmachinen said:
Just like with the economic system known as capitalism, it's all about shifting the problem to someone else.
Your ideas are intriguing to me, and I wish to subscribe to your newsletter.
-
@blakeyrat said:
@Xyro said:
Type "
You just have a mismatch between what your terminal emulator is sending and the shell is expecting; one is ^? and the other is ^H.stty erase", then hit your backspace key, then press enter. That will assign whatever character code your backspace key sends to the "erase" control.What the... is this one of those Star Trek plots where they're getting messages from the past and when they finally arrive to the source of the message they find the people they've been talking to have been dead for years?
Thanks! After I posted my little backspace tirade, I opened up SQLPlus, and did some Serious Real Work (a registered trademark of Oracle Corporation) and I found that my complaint was not quite accurate. Backspace does seem to work for me. I guess what I was perceiving was that, even with backspace support, SQLPlus is still just a terminal program, lacking the amenities of a GUI or even a BASH shell session. I've been told I need to get a "Toad", but I just can't bring myself to use something called Toad. Eww!
-
@bridget99 said:
I've been told I need to get a "Toad",
Toad:
1) is Java
2) sucksWhat you need is a database with actual management tools, which is to say not-Oracle.
-
@blakeyrat said:
@bridget99 said:
I've been told I need to get a "Toad",
Toad:
1) is Java
2) sucksWhat you need is a database with actual management tools, which is to say not-Oracle.
Toad is Java? It never ceases to amaze me that people try and make production-quality software in an interpreted language. Writing Toad in Java is just one small step away from trying to sell people Access / VBA applications. An Attorney I know thought he could get rich doing that. I kept trying to tell him, "everyone is going to know that this is just Microsoft Access; if you shell out a little time or money getting this into a real language, maybe you've got a shot." There's simply no convincing some people, though.
-
@bridget99 said:
@blakeyrat said:
Yeah, which is why nobody uses C# or VB.Net.@bridget99 said:
I've been told I need to get a "Toad",
Toad:
1) is Java
2) sucksWhat you need is a database with actual management tools, which is to say not-Oracle.
Toad is Java? It never ceases to amaze me that people try and make production-quality software in an interpreted language. Writing Toad in Java is just one small step away from trying to sell people Access / VBA applications. An Attorney I know thought he could get rich doing that. I kept trying to tell him, "everyone is going to know that this is just Microsoft Access; if you shell out a little time or money getting this into a real language, maybe you've got a shot." There's simply no convincing some people, though.
-
On a related note, what other alternatives are there for a general purpose DB-snooping tool?
We used Toad to look at the data from Sybase, SQL-server, Oracle and Access DBs when we had to get data from a buttload of databases into a separate application (Websense DLP).
It was handy to be able to check the validity of all the SQL-statements in the same tool.
TRWTF is ofc that we have a nice big biztalk infrastructure for this purpose, but Websense couldn't use anything but ODBC or CSV to get the data...
-
@Pilsner said:
On a related note, what other alternatives are there for a general purpose DB-snooping tool?
We used Toad to look at the data from Sybase, SQL-server, Oracle and Access DBs when we had to get data from a buttload of databases into a separate application (Websense DLP).
It was handy to be able to check the validity of all the SQL-statements in the same tool.
TRWTF is ofc that we have a nice big biztalk infrastructure for this purpose, but Websense couldn't use anything but ODBC or CSV to get the data...
Oracle's [url=http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html]SQL Developer[/url] exists, but I'm not sure how general-purpose it is. Theoretically it can be used on non-Oracle databases, but I have no experience with that.
Warning: this product uses a virtual machine that interprets bytecode and later compiles it to native machine language. Unfortunately, it pretty much does fulfill the stereotype of being a slow, bloated Java application with painfully foreign GUI elements. It's also from Oracle, so that doesn't help its case either. Take it or leave it, but I use it pretty frequently without too many tears.
-
@bridget99 said:
An Attorney I know thought he could get rich doing that [trying to sell Access apps]. I kept trying to tell him, "everyone is going to know that this is just Microsoft Access; if you shell out a little time or money getting this into a real language, maybe you've got a shot." There's simply no convincing some people, though.
I was test manager on a big project that had a SQL Server backend but I couldn't shake the feeling that it was a port from MS Access. I realise you can make a badly designed DB in any product but the ease of accessibility of MS Access would make it favourite.
If your attorney friend has a decent database design then a move to one of the more enterprise-level engines would add an air of respectability.
-
@Xyro said:
@Pilsner said:
Oracle's SQL Developer exists, but I'm not sure how general-purpose it is. Theoretically it can be used on non-Oracle databases, but I have no experience with that.On a related note, what other alternatives are there for a general purpose DB-snooping tool?
We used Toad to look at the data from Sybase, SQL-server, Oracle and Access DBs when we had to get data from a buttload of databases into a separate application (Websense DLP).
It was handy to be able to check the validity of all the SQL-statements in the same tool.
TRWTF is ofc that we have a nice big biztalk infrastructure for this purpose, but Websense couldn't use anything but ODBC or CSV to get the data...
Warning: this product uses a virtual machine that interprets bytecode and later compiles it to native machine language. Unfortunately, it pretty much does fulfill the stereotype of being a slow, bloated Java application with painfully foreign GUI elements. It's also from Oracle, so that doesn't help its case either. Take it or leave it, but I use it pretty frequently without too many tears.
I think someone earlier mistook TOAD for Oracle SQL Developer. TOAD is not Java and does not suck. Oracle's Sql Developer is and does.
-
In the other thread I was agreeing with you; in this thread I'm not.
Oracle SQL Developer is Java and Sucks. Toad is not and does not. (Or at least it doesn't appear to be Java).
I've been using TOAD for 4 and a half years. Without it I would have Oracle would have forced me to defenestrated myself. With it i just want to defenstrate the server.
-
@Xyro said:
Oracle's SQL Developer exists, but I'm not sure how general-purpose it is. Theoretically it can be used on non-Oracle databases, but I have no experience with that.
Theoretically it can be used on Oracle databases too, but I would advise against that.
-
@Sutherlands said:
@bridget99 said:
I'm not sure what your point is but C# and VB.Net are not interpreted languages. At compile-time it is compiled into IL and at first execution it uses a Just-In-Time compiler.@blakeyrat said:
Yeah, which is why nobody uses C# or VB.Net.@bridget99 said:
I've been told I need to get a "Toad",
Toad:
1) is Java
2) sucksWhat you need is a database with actual management tools, which is to say not-Oracle.
Toad is Java? It never ceases to amaze me that people try and make production-quality software in an interpreted language. Writing Toad in Java is just one small step away from trying to sell people Access / VBA applications. An Attorney I know thought he could get rich doing that. I kept trying to tell him, "everyone is going to know that this is just Microsoft Access; if you shell out a little time or money getting this into a real language, maybe you've got a shot." There's simply no convincing some people, though.
