Blog Posts

• An intermedaite landing zone: Some off-machine storage location that can hold the data while in transit - e.g a NAS or SAN volume. This will not be the same size as the database but some fraction of it. It is a workspace for intermediate files, not permanant storage
  
• Content boundaries: A means to define and manage content bthese are tags or quantities in the data itself. They need to be consistent for all tables so that the backups and restore operate the same way. Of course, if we don't have these, we need to apply them.
  
• A restoration database: a separate database that will be used for the restoration process, because we won't be restoring the data directly into the table from whence it came. Why not? There is an increasing likelihood that the data structures have changed since the last backup. If the structure has changed, we cannot reload the data into it. We need a way to shepherd the data back into the machine.
  
• Processes: that capture / backup / restore the data using content boundaries and common utilities. These are typically control scripts that are easily configured, deployed and maintained
  
• Archival Database (next): Physical content replicas of the masters that hold all of the original information in a form useful for backup and restore. Whether they keep the data active/online is immaterial. Their role is to interface with the backup/restore mechanisms so that any data once made offline can easily be made online through this database.
  

 

Protocol

• On a per-table basis, use the content boundaries to manufacture/compile a set of checkpointed instructions for each table to support extraction and restoration
  
  
• Execute the extractions to create flat files
  
  
• Each extraction will pull data to a Netezza-compressed flat file and also supply another file with the metadata instructions necessary to restore the file's contents. On a per-file basis, these instructions will include:
  • Original table definition, used to construct a template of the table in the restoration database
    
    
  • File load instructions used to get the content back into the machine
    
    
  • Transformation command, used to move data from the restoration database into its original home
    
    
• The files will be extracted (using content boundaries) onto the storage landing zone
  
• The smaller extractions can be performed in parallel, are independent and form their own checkpointed process
  
• The backup techology will begin its backup process once the second (metadata companion) file arrives for the set. The metadata companion file behaves as a trigger.
  
• Once the backup for the file-set is complete, the backup/restore deletes the file and its companion
  
• This decouples the backup/restore processing from the database (completely) so that it can focus solely on file-based backup and restore
  
• For restoration, both the content file(s) and companion metadata file(s) will be retrieved and placed on the storage landing zone
  
• The metadata will be used to reconstruct an empty intermediate version of the original table in the Restoration Database
  
• Load the table using nzload
  
• Then the metadata will be used (even a preformulated SQL statement) to copy the data into the original target
  
• Throw away the intermediate load table, delete the data file and its companion metadata file
  
• The restoration process can be run as many ways-parallel as supported by nzload
  
• The restoration process can also be surgical, with the more agile ability to restore data in smaller segments
  

 

The above protocol, while likely easy to pull off an administer, still has a number of moving parts that the Netezza based-equivalent (later) will not have.

SAN-based hybrid

The only difference between the above protocol and one using a SAN-based storage mechanism, is the absence of a formal backup/restore technology. Rather the SAN is the long-term storage location and we perform the incremental extractions onto it. Rather than delete the files, we keep them.

This has significant implications for the cost of the SAN. After all, if we intend to interface this to the Netezza machine, we would not want common NAS storage because it is too slow and the vendors actively disclaim their technology from being viable for data warehousing. The primary reason is that the network and CPUs are set up for load-balancing, like a transactional database, but not bulk onload/offload of the data.

Not only will we need enough SAN to backup the environment, but to also carry fathers and grandfathers if need be (this is a policy decision). With checkpointed extracts, the father/grandfather issue is largely moot. This is because once a checkpointed extract of older data is pulled and stored, it won't be changing and capturing another one just like it has no value. 

Netezza-based Hybrid

In this approach we leverage another Netezza machine like a DR server, as our backup, archival and restore foundation. It can easily hold the information quantity. The difference here is in price, of course, since a fully-functional TwinFin is more expensive than most common SAN installations. However, the High Capacity Server (below) mitigates this pricing problem while delivering a consistent data experience.

One primary benefit of performing backups into the DR server is that it can automatically serve the role of a hot-swap server in case of failure in the primary server.

For this scenario to work however, we would want streaming replication between the active databases and the DR server so that the data is being reconciled while being processed. This allows us to have a fully functional hot-swap if the primary crashes, and we can continue uninterrupted while the primary is serviced. Word to the wise on this kind of scenario however, bringing back the primary means that it is out of sync, since the secondary took over for a span of time. So we would need to be able to reverse the streaming replication to make it whole.

Scenarios like this often embrace the practice of operationally swapping the two machines on defined boundaries, like once a month or once a quarter, where they actually switch roles each time. This allows the operations staff to gain confidence in the two machines as redundant to each other in every way. I have seen cases like this where the primary machine went down, the secondary machine kicked in seamlessly and all was well. I have also seen cases where the principals kept the DR server up to date but when it came time to operationally switch, some important piece (usually in the infrastructure between the devices) was missing causing the failover itself to fail. It is best to have a plan in place, but it's better to have tested the plan and that it actually works. 

Protocol 

• On a per-table basis, use the content boundaries to manufacture/compile a set of checkpointed instructions for each table to support extraction and restoration
  
  
• Using a variation of the nzmigrate utility, perform the table-level extractions
  
  
• Extract data in Netezza-compressed form to a flat file
  
  
• Load the flat file into a restoration database in the second machine
  
  
• Perform the transform-execution to copy data from the restoration database into the target table
  
  
• The extractions, restoratons and transformations can all leverage simple scripts and catalog metadata, not become dependent on deeply hand-crafted code
  
  
• Intermediate (SAN) landing zone requires less space because the data is being transferred in Netezza-compressed format and it is cleaned-up on-the-fly
  
  
• Transfer is quicker because Netezza-compressed data is written to the data-slices directly instead of coming through the host.
  
  

A word on Netezza-compressed transfer. I wrote about this in Netezza Transformation but it is important to highlight here. We performed an experiment moving half a terabyte scattered across a hundred or more tables. This data was moved from its original home to a database in another machine. The first method used simple SQL-extract into an nzLoad component. This process took over an hour. The second method used transient external tables with compression, coupled with an nzload in compressed mode. The entire transfer took less than six minutes. This was because the compressed form of the data was already 14x compressed.

In other experiment using over 20x compression for the data, we were able to transfer ten terabytes in less than an hour. This kind of data transfer speaks well for the streaming replication necessary for DR server operation (above) but underscores the fact that even when transferring between Netezza machines, it's as though we haven't left the machine at all.

Netezza-based High-Capacity Server

This option is simply a form of the Netezza-based hybrid (above) but on a dedicated server designed to support backup and recovery.

The better part about this server is that is has more disk drives and fewer CPUs, making it far more cost effective for storage than common SAN devices. Couple this with the minimal overhead required for transferring data between machines, and the ability to surgically control the content with the content-boundaries and catalog metadata, and we get the best of all worlds with this device.

Not only that, but it is also scalable to support storage of all other Netezza devices in the shop as well as any non-Netezza device where we simply want to capture structured information for archival purposes. The High Capacity server is queryable also, meaning that even the ad-hoc folks will find some value in keeping the data online and available.

Lastly, in Spring 2010, as part of the safe-harbor presentation one of the principals at IBM Netezza announced plans for a replication server. I can only imagine that this device will deliver us from any additional hiccups associated with streaming replication that we might now be doing in script or other utility control language.

At Brightlight, our data integration framework (nzDIF) has the nz_migrate techniques built directly into the flow substrate of the processing controller, as well as the enforcement and maintenance of the aforementioned content boundaries. We are actively acquiring and applying best, most scalable and simplified approaches as a solution framework firmly lashed to a purpose-built machine. I am a big proponent of encouraging Enzees to take on these things themselves, or at least let us coach you on how to make it happen. The solutions are simple because the Netezza platform itself is simplified in its operation. Stand on the shoulders of genius - the air is good up here.

Host: Ladies and Gentlemen, I'm your host here with Hairball Plotter-
Hairy: Call me Hairy
Host: (Pause) Oookay, I'm here with Hairy Plotter and he's going to tell us about some of his - er -

Hairy: Innovations

Host: Innovations? I thought they were just the opposite -

Hairy: Well, eccentric innovations anyhow. You know, the kind of stuff that gets the job done, but with a lot
more moving parts!

Host: Please explain

Hairy: Well, you see, it's not incumbent upon me as a consultant to work myself out of a job. I need a way
to stay employed and ensconced, and complexity is just the ticket

Host: How so?

Hairy: The more complex the environment, the better job security I have

Host: We have a caller from Ontario on the line, a question for Mr. Plotter?

Caller: Yes, I'm an IT manager and I've found that when people try to embed themselves as you describe, they create risk
for me, and I don't like that.

Hairy: Thanks for that input. I'll file that.

Caller: Wait a second, are you blowing me off?

Hairy: What difference does it make? If you like it or you don't, you're still stuck with me.

Caller: Unless I find something better.

Hairy: I'll just milk your budget so there's nothing to spend on something better. Works for me.

Caller: But that doesn't work for me.

Hairy: Noted.

Caller: But -
(click)

Host: We have another caller from a financial services firm in New York. Caller you're on the air.

Caller: Thanks, I agree with Hairy on this. Contractors are punted around and treated like fodder. We need a way to
keep our jobs and pay our bills. Working ourselves out of a job doesn't fit that mold. We need more ways to
make work for ourselves, even if it's artificial.

Host: Artificial?

Hairy: Well said!

Host: Wait a second, artificial?

Hairy: Well, of course its artificial. All of us are smart enough to do it better, faster, smarter. Heck, I could deploy a
high-reuse/low maintenance implementation that basically lets you run things lights-out.

Host: Good for you!

Hairy: Oh no, so not good for me. Once it's deployed I get a final paycheck and have to ride into the sunset. Seems romantic
but the sunset doesn't pay the bills!

Host: So you find a way to make yourself useful.

Hairy: No, I find a way to make myself necessary. Usefulness is for suckers.

Host: Now wait a second -

Hairy: Now listen. Everyone does it. At one of my favorite clients, all of the contractors are on a perpetual time-and-materials contract. When we
had some folks show up on a fixed-price gig, they practically pulled their eyeballs out when they couldn't get any urgency out of anyone.

Host: Why not?

Hairy: Because all those time-and-materials folks had a vested interest in protracting the work until the next day or the next week. Practically

every time we said we needed something right away because our clock was ticking, they would offer it up for delivery "next Friday" or some such.

But you know, If they ever looked ike they were wrapping up, it could spell curtains for their cushy little tushy.

Host: (laughs) I see what you mean.

Hairy: It's just how the game is played.

Host: So IT staffing is just a game?

Hairy: Musical chairs. In so many ways.

Host: So what are some of the ways that you - well - set this up?

Hairy: Invest in a lot of wool.

Host: Sorry?

Hairy: We'll be pulling it over the manager's eyes.

Host: Oh, I see, that's kind of funny.

Hairy: Thought you would like that. But seriously, we take the simplest, most direct way to implement something, so that we
know exactly what it would look like, then do the opposite.

Host: Seriously?

Hairy: Well of course. If we do the simplest approach, there's no room for a hero to step in an save the day. Lots of little
virtual terrorists running around in a complex system. Think about how the 9/11 terrorists were able to evade
electronic surveillance - they stayed off the grid and used cash, and were able to fool the other systems into thinking
they were out of the country.

Host: So you use the same technique?

Hairy: Well, the same forms of deception of course. If we built over-complex systems accidentally people would say that we're
clever, but if we do it deliberately, we're clearly diabolical. I would never deliberately paint myself to look bad, so we have to be a little

deceptive, right?

Host: Hmm, so this is a little disturbing. I want to know - oh we have a caller from Orlando.

Caller: Hey man, I want to come work with you.

Hairy: Shoot me a resume.

Host: Stay on the line and we'll take your contact information. There's another caller from Dallas Texas. Dallas, you're on the air.

Caller: Hey, this whole deliberate-snowstorm thing is really a different way to look at things.

Hairy: Snowstorm, I like that. When one of my first mentors realized this technique, he said in a gruff voice - "You're a blizzard, Hairy."
At least I'm not slithering - around you know like those snakepits where the idea-mongers hang out. Ideas are good for a spell, then they wear off.
Almost like-   Like snake-charming, or snake-whispering.

Host: But I mean, doing it deliberately seems, well -

Hairy: I know what you're about to say. But think about it. Whether we do it deliberately or accidentally, the outcome is
the same. They are interested in the system, but we're interested in self-preservation. If I have to choose, I say, don't
do self-preservation by accident. Do it deliberately. This means set yourself up to be necessary.
Host: Explain necessary?

Hairy: Of course. The more complex the system the more they need you. If it's simple, what do they need you for?

Host: But if they figure out what you're doing, you're cooked, right?

Hairy: But if I work myself out of a job, the outcome is the same. No paycheck.

Host: So you're saying that most data warehousing is just accidental brilliance?

Hairy: Acccidental or deliberate, the outcome is the same.

Host: Oh, please, don't go fatalist on me.

Hairy: Come on, man. Most folks can't pull this off whether accidental or deliberate. The fact is, we still stand up a functional,
operating environment. Consumers are being fed, users are getting what they want. Forget the fact that the
environment is stood up on pallettes and serviced by swarming people on rollerskates.

Host: I- suppose.

Hairy: Again, if someone stood up the working environment and had all this stuff in it, you'd call it clever. Only when
you realize that the complexity is artificial would you take exception to it. If you never realize this, I will always
have a paycheck and you will always have an operational system. You'll never fire me, because I'm your most visible hero.

Host: Ever heard of Munchhausen-by-proxy syndrome?

Hairy: Not familiar with it, no.

Host: Where a care-giver deliberately harms their child in order to be seen as the savior that delivers them from harm.

Hairy: I see a pattern, sure.

Host: You said it yourself, diabolical.

Hairy: Only if they realize it. Perception is the key.

Host: But you're admitting to it here. On the air.

Hairy: Nobody will remember it. For one good reason: It's so outrageous that it simply cannot be true.

Host: Unbelievable.

Hairy: ExACTLY!

Host: But what about scalability? When volumes increase, invariably the complex systems are crushed.

Hairy: Oh please, only about five percent of all implementations have that issue, so if I stay away from those, I'm gold.

Host: But what if one of your implementations grows into this? Seems like you'd have some explaining to do.

Hairy: What are the odds? I can easily place myself in the lower-scale zone and make a good living at it.

Host: But you would agree that the larger the scale, the more the need for simplicity?

Hairy: I don't do scale, so why would I care? I blow smoke into a manager's face, nose or other orifices and take a paycheck. What
does scale mean to me?

Host: You have a lot of disparaging things to say about these decision-makers. Aren't they your customers?

Hairy: Look, you have people like me, who work the magic, and people who sit in the office, smug in their confidence that
they know exactly what to do and how to do it. My objective is to make sure they stay on the outside looking in,
snuggled next to their branded-coffee cup and blissfully unaware of my agenda. We call them the Smuggles.

Host: Smuggles?

Hairy: Yeah, Smug people who snuggle with coffee. Their users are the Smuggees. Smug people who know data but don't know beans
about how to make it operational. Always offering opinions. Who cares what they think?

Host: Clearly not you.

Hairy: Well, I care what they think to an extent. As long as their reports are running to spec, it doesn't matter how we pulled
it off, only that they get the data they want.

Host: The "how" and "what" question. I've heard that before.

Hairy: And what I've heard before, is the endless droning of users dictating to us how we should deploy the systems.

Host: And what do you do with that?

Hairy: If their suggestion will make the environment more complex and difficult to manage, we're all over it. If it makes the
environment easier to operate, we push back. We call it the "you asked for it" policy. It's a theme, you see.

Host: Yes, I can see that.

Hairy: O I love 'em. Nothing is better than deliberately choosing a platform that can't cut the mustard. I mean, they're not really
hard to pick, you know. Imagine getting almost to the end of the project and hitting that hard
wall - flying right into it like a blind witch on a runaway broom. It's hysterical to watch all these
folks running around like headless chickens. You can't pay for that kind of entertainment. But of course,
they do pay for it, and handsomely.

Host: Until they realize that you're in the center of it?

Hairy: Eye of the storm you mean, where the seas are calm. I never lose my cool, so they always think things
are under control until someone pulls the single thread that unravels it all. I have plausible deniability. Keeps
me working and the paychecks coming. Sweet.

Host: Don't you think this is a bit - you know - underhanded?

Hairy: The mass layoffs of the turn of the century were underhanded. They created a 1099-Culture that basically means all of us are
mercenaries. Soldiers of fortune. We go to the highest bidder no matter what. There's no conscience in that existence, especially when we could
be working for one company today and their competitor tomorrow. Those companies treat the 1099's like batteries - plug 'em in, burn 'em out and toss 'em.

Host: Seems a bit cynical.

Hairy: One of the better parts about this kind of consulting is that I can propose the solution without
actually producing anything. Then I can flit from flower to flower, pollinating these ideas. They pay
me for the ideas, not the actual work, you see.

Host: So you propose, but you don't actually execute?

Hairy: Execution is somone else's problem. Why should I stick around to see if the proposals actually work? If
they don't, there's another feather in my hairball cap, or rather another notch in my mayhem gun. But
if the solution works, good grief, all that work for nothing? May as well stay home and play video
games.

Host: What would you like to leave our audience with?

Hairy: Oh, I suppose, don't worry, be happy and all that. I have a new book coming out called Managing Expectations.

Host: So tell us about that.

Hairy: It's all about how to give the user a false sense of security while we do whatever we want. I'm all about what's expedient
for me, but most of the time when the client sees what I'm doing, they love it because it makes things expedient for them as well. Soon
everyone is following the beat of the same drummer.

Host: Which would be?

Hairy: Get it done no matter what the cost. That way, I can charge whatever I want. Cost is no limit.

Host: I see. I think.

Hairy: When you think about it, most IT folk want to do things the expedient way anyhow. Over-thinking the problem seems so stodgy to them.
In fact, most of the time when we're going through the analysis phase, I can see it written in their expression and practically popping from
their eyeballs.

Host: What's that?

Hairy: The desire to start coding! Heck, practically every conversation is punctuated with how they intend to do it - long before the analysis
is complete. That's because IT folk just don't have the patience for analysis. They want to get coding. I just set the expectation that we'll
code first and analyze later.

Host: So when does the analysis come into play?

Hairy: What analysis? If we start coding the analysis never happens. There's never enough time. After all, the herald cry of
expedience is - "If you don't have time to do it right, when will you have time to do it over?" What do you get when you start coding
without any analysis? Hairball city. That's my stomping ground.

Host: Thank you for your time Mr. Plotter. This has been very - uh - enlightening.

Hairy: My pleasure

(pause)

Host: That was Hairy Plotter with his half-baked stints. Be with us next time when Jason Statham joins us for
"Data Transporter 2", where he kicks a bunch of back-ends with ETL tools. Until then, happy computing and keep that data flowing!

--

--

--

I offer a simple note of thanks to the folks at IBM Netezza who executed what is arguably the best Enzee Universe ever.

I also thank the many of you who attended the Best Practices sessions on Monday. I counted over 450 in attendance just by chair-count. I realize that there is enormous curiosity and hunger among the Enzees for these topics and conversations and I hope you feel that your time was honored and it was worthwhile to you.

About the music: I had a number of questions about the "bumper music" played during session breaks. These were selections from Tron:The Legacy soundtrack, Blue Man Group, other movie themes from last year including James Bond, Mission Impossible and a cut from Steppenwolf.

Of all the questions asked during the session, the one that interested me most was "What does Netezza have that Oracle does not?" Honestly, we only had a few minutes left in the question-and-answer session and if I gave this answer any honor, it would have taken hours. I wrote up some of the differences in both Netezza Underground and Netezza Transformation, but perhaps its time to revisit these in an open discussion so that they are up-to-date and out-in-front. The problems for Oracle have only become more apparent over the years, so it will be easier to clarify and differentiate without requiring a deep technical dive (in other words, they are obvious on the surface).

I was also offered the privilege to sit for an online interview with Mike Kearney - linked here: http://www.youtube.com/user/NetezzaCorp#p/c/55393907B7B3BDBE/3/aLQMoa1S8P8

But keep in mind that this was after all day in the hot-seat with you guys without breakfast or lunch (whew!) so the whole day was a bit of a whirlwind.

I wish each and every attendee (and even those who wanted to attend) all the best and hope to see you again soon.

The long-awaited "SQL" to Netezza Underground has hit Amazon.Com.

http://www.amazon.com/Netezza-Transformation-without-alchemy-sorcery/dp/1453874054/ref=sr_1_1?ie=UTF8&s=books&qid=1306624705&sr=1-1

This book tackles some deeper issues around transforming our data warehouse, our approaches and even our thinking to align with the arrival of the TwinFin. No, it's not the TwinFin we'll transform, but that the TwinFin will transform us. Once again, a little tongue-in-cheek irony

The book offers working examples of checkpointing, exception handling (primary/foreign key), windowing (SCDs and deduplication) as well as a cookbook on more details to watch for in a migration project. And of course, is replete with Case Study Short and a whole chapter on Case Studies. There's also an appendix at the end to offer up some simple scripting jump-start routines that can make bash so much easier.

I also threw my hat into the ring on the Exadata question, as well as several other products that need a kick in the pants.

Okay folks, the Enzee Universe is upon us and the Best Practice sessios is fired up, the power is vibrating the tracks and the train is about to leave the station. It's a good ride - so get on it.

We have a lot of stuff planned for the Best Practice session, to kick off Monday morning before the keynote. It's a six-hour run but hopefully not a white-knuckle experience. As with last year, there's more material than we have time for, so even if you're a little late, jump right in because there's no better time to join than right away.

Last year we had over three hundred-fifty in attendance. The conversations and participation last year was very high so we're expecting an even more scintillating performance this year, from you. That's right, the Enzees were the stars last year, turning out in number and unafraid to speak out on the tough issues and cross-pollinate their knowledge to everyone. I received an avalanche of feedback last year on the hot-seat sessions and the different subjects.

As with last year, we'll stay on the core tracks but we have some significant updates to the material. So bring your notepad, your thinking cap and hey, maybe even a megaphone if the temperature rises too high. Ahh, but no voice is too great or small in the sessions because the purpose is for all of us to walk away better prepared and enabled to tackle the next-great-solution with a Twinfin at our side.

So get a good breakfast, arrive early if you need to, and we'll get moving when the starting pistol sounds.

See you in Boston!

So a number of months ago I was in a chat with several colleagues on their use of nz_reclaim on their Mustang systems. If you are a TwinFin user, this really doesn't apply (after all, on the TwinFIn under the covers, the nzreclaim just calls the groom function anyhow).

As discussed at the conference and on many forum posts here, the nz_reclaim is one of those options of last resort. People say no, we use a CTAS or other mechanism. The problem with the CTAS, they lament, is that this resets all the rowids in the table and makes it a candidate for a full backup.Groom of course, mitigates all this.

But back to Mustang space, because most of us entered the frontier on a Mustang,

In their particular environments, which I am seeing is more the case in many places, they run an operational reclaim on their largest tables. Other places just kick off a reclaim on the weekend and let it scan through all the tables. This works great at first, then starts to present problems as we add databases and tables. Then we need something a bit more efficient.

If we apply a simple set of queries on the table before engaging the nz_reclaim, even on the largest tables, we can defer the nz_reclaim operation to execute when it's necessary, based on applied thresholds. I mentioned this at the sessions in the past years at the Enzee conferences, but afterward people would ask - how do you find the percentages you are talking about, or to discover those thresholds?

So the underpinning mechanics are fairly simple. Each time we perform a delete operation, this is actually a soft-delete. The record is not removed from the used-space of the table, and if you do a lot of operational deletes, you could end up with a lot of unusable dead space owned by the table and unavailable for other use. The update performs a full-record delete-and-insert. So in the end, the problem is the same - we need to clean up the dead space. The TwinFin uses the groom, but the Mustangs use the nz-reclaim, which works a lot like a disk-defragmentation. It is a very expensive operation (in duration especially) and locks the table from access.

I will depend upon your own acumen for deriving a list of tables to submit to the function below. It performs the check on the table and gives you a thumbs-up or down based on the provided threshold. I have given a default here of 20 percent, which basically means that if you compare the total records in the table to the total records deleted, if the total records deleted exceeds the threshold, it is declared a candidate. Now that you have a list of candidates, execute nzreclaim on them.

The best part of this is that even if you have a very large table that has not exceeded the threshold (say by the weekend run it has only 10 percent dead space) you can forego reclaiming it until the threshold is exceeded. I know of several sites that have operational deletes and updates that use these kinds of thresholds, and they don't perform reclaims nearly as often as those sites that don't use thresholds, or are using an all-or-nothing schedule.

In order to "see" the deleted records, we need to access the deletexid field, a hidden column on each Netezza row. If this value is set to "0" it means that the record is normal. If the value is non-zero, it means that the record has been deleted and will be affected by a reclaim. Of course, normal queries ignore the records that have a non-zero deletexid.

The function is called nz_reclaim_candidate(), and it accepts the name of a database, a table and an optional parameter for a percentage threshold

Note here that TARGET_XFR contain the name of a database on the Netezza machine. All the other assets are manufactured for the demonstration

To start out, I'll make a table and add some records to it, forgive the clunkiness of these singleton inserts, as I am only doing this so you can see what I am putting into the table.

nzsql -a -d $TARGET_XFR <<!

create table zx1 (int1 integer) ;

insert into zx1 select 1;

insert into zx1 select 1;

insert into zx1 select 1;

insert into zx1 select 1;

insert into zx1 select 1;

insert into zx1 select 2;

insert into zx1 select 2;

insert into zx1 select 2;

insert into zx1 select 3;

insert into zx1 select 3;

insert into zx1 select 3;

insert into zx1 select 3;

---- now delete some records - this will be 25 percent of the table

delete  from zx1 where int1 = 2;

--now lets try to see the records we deleted - this query normally won't show them

select deletexid from zx1;

--- now lets set up the switch to show the deleted records, active only for this session

set show_deleted_records=true;

-- and now we can see the deleted records. It is now simple enough to compare total row count to deleted count, and get a percentage

select deletexid from zx1;

!

# Now let's declare the bash function

#==================================

nz_reclaim_candidate()

#==================================

{

exec >&2

DBNAME=$1

TABNAME=$2

MAX_PCT=$3

if [ "x${MAX_PCT}" = "x" ] ; then MAX_PCT=20 ; fi

MDEL=$(nzsql -q -d $DBNAME -A -t -c "set show_deleted_records=true;select count(*) from $TABNAME where deletexid <> 0;" )

echo "delete count=$MDEL"

if [ $MDEL -eq 0 ] ; then return 0 ; fi

MCNT=$(nzsql -q -d $DBNAME -A -t -c "select count(*) from $TABNAME ;" )

echo "table count=$MCNT"

if [ $MCNT -lt $MDEL ] ; then return 1 ; fi

MPCT=$(nzsql -q -d $DBNAME -A -t -c "select (${MDEL}*100)::bigint / ${MCNT}::bigint  ; " )

echo "percentage deleted=$MPCT "

if [ $MPCT -ge $MAX_PCT ] ; then return 1 ; fi

return 0

}

#### Here you would have a list of tables – I can show you how to grab a list of tables from the database if you want ########

###  So lets go through the table list and it will find the table's percentage based on the counts

#=================================================================================

TABNAMES=zx1

for TABNAME in $TABNAMES

do

mtab=$(nz_reclaim_candidate $TARGET_XFR $TABNAME 20 )

mret=$?

if [ $mret -eq 0 ]

then

echo "Not a Candidate"

else

echo "NzReclaim Candidate"

fi

done

##### drop the test case ####################

nzsql -a -d $TARGET_XFR <<!

drop table zx1;

!

Hopefully this can provide some value to those of you who have mature Mustang environments and need some relief from the "global" nzreclaim approach.

Happy hunting!

At the June conference we covered a lot of practices around harnessing the Netezza machine for fun and profit. But the buzz on ELT was rising then, and when I arrived (and remained after) the November London Roadshow, people were practically tackling me in the hallways to learn more about how ELT works, how well it work, and how practical it is.

Well, the ELT buzz is growing even louder. I've been practically inundated with requests for more information on ELT and how it operates. Of all the opinions on the subject, the conversation usually boils down to the irreducible: How do we get plain vanilla queries to do more than be, well, you know - plain vanilla queries? I mean, the business logic and flow rules are all bound into it! And if the queries can be thrown into the machine with reckless abandon how can we possibly make any sense of them? ELT means forming structured flows of information using intermediate tables as checkpoints. Netezza is one of the few platforms in the world that can do this effortlessly and productively. Anyone now exposed to this (like a common RDBMS developer) would claim that ELT is unworkable on any database. Well, perhaps on your database, but not on Netezza.

Of course, we cannot allow arbitrary SQL to enter the flow can we? But does this mean arbitrarily submitted, or arbitrarily formed? You see, if the SQL has a consistent form, we have all the freedom we need for arbitrary submission. I suppose it's the "degree" of arbitrary we're looking for no?

Case in point, a vendor tells me "Oh, you can send any arbitrary query to the machine and.." Of course he means anything, absolutely anything our developers choose to concoct of their own cleverness or ignorance, or anywhere in between. In fact, cleverness is perhaps the least of our worries since the clever queries often follow some degree of structured discipline. It's the queries produced by the newbies that pose the most danger. It's hard to remediate a lot of work that took place "in the wild", so to speak. Of course, for those who like their wild-game-SQL or pehaps free-range-SQL, I will not disparage their personal tastes. But this is enterprise computing after all, and many of my personal tastes are already checked at the door.

And as I've noted in the past, metadata is where it all happens anyhow so let's take a short dive into the future of ELT and what I suspect is the next wave of stuff we'll see.

1 - insert/select queries, while they are the ultimate bread-and-butter of ELT, their hard-wired form should not rule the roost. One need not fear being enslaved by the queries, but should instead harness them. In fact, the queries should never completely exist in one place, but be manufactured at run time. Now, our BI tools already do this, right? We post a request into our favorite BI tool and it leverages its metadata repository to form-the-perfect-query. That's where we need to be. Provide a template and some steering logic, but the query itself is manufactured on-the-fly.

2 - Automate everything, from DDL to physical model, from intake to reposit, and it should be a fairly simple approach. When we takea look at the patterns of what we're trying to accomplish, we are essentially punting around metadata (tables, columns, data types, rules) and these are the object-oriented assets shaping the solution-at-hand. We should not be in the businss of crafting SQL statements (let a product do that for us) but in the business of shaping logic. The ETL tools purport to do this but cannot scale (for set-based operations) as effortlessly as Netezza can. So in some ways, the ELT aficionado is stuck in a bubble waitng for the technologies to mature.

3 - Think in terms of metadata, factories and steering logic. Okay, what does that mean? Factories mean components that take raw parts and manufacture them into SQL statements and other assets. This allows us to drive practically everything from knowledge of the Netezza catalog. Source table to target table? All we need are the rules in the middle and we're done, right? So if we can make simple lists of rules that are tied to particular table/colums as targets, we are now in the zone to leverage a factory to manufacture the proper insert/select statement to send to the machine. We don't really need to have the predefied insert/select, only a list of target columns tied to rules. The rules then become our source map, and the target columns become our fodder for manufacturing the insert-clause. Bolt a filter / where-clause on the end of it and yes, we've have valid SQL. But wait, what about steering logic? As my factory is manufacturing the query, I can make all kinds of decisions about the table and columns as I encounter them. For example, if I am making an extraction query to face a database, as I encounter each column in the catalog, I can compare this to yet another repository of information that can tell me, does this column use a different name on the extraction source than the target? Perhaps I need to exclude it from the query altogether? Perhaps I need to  alter the value of a given column to a default (data masking) or I may need to bolt-on columns to the end of the structure. In short, the steering logic is a simple set of exports that I can tie by-name to the source and table name so that it is surgically applied as necessary. This means every extraction is now programmable because we've built the programmability into the factory - the switches and signals serve as steering logic as the factory weaves the perfect query.

What is BI anyhow? Some companies who do all-data-warehousing have jumped into BI thinking it's just the icing on the cake, so to speak. Others embrace BI as their primary focus but then drive into a pit of miry clay when they have to access data untouched by the scrubbing bubbles of an enterprise-class data warehouse. So which is it? Data warehousing and BI? Does BI encompass data warehousing? I've had this conversation with so many people who cannot seem to put their finger on the definition. One argued passionately that we should simple consider data warehousing and BI to be one and the same, such that when we talk about BI, we're talking about both. Then not five minutes later he claimed that Microsoft Excel was the most widely used BI tool in the world, to which I asked wryly, "since when did Excel start doing data warehousing and ELT" - he sort of stared at me, not knowing why I had asked the question, forgetting his prior claims as though second thoughts entirely. My simple assertion is that the open marketplace calls them out as different - BI is not data warehousing and likely never will be.

Cached stuff - Like any system, catalog-hits can become cumbersome and the factories leveraging them can begin to take protracted lifecycles for building out the queries, some of which can be very complex. However, if they are based on static metadata (like that in the catalog) it is easier to cache the manufactured assets (less any table/column-centric adjustments) and avoid re-manufacturing these assets each and every time we need them. In one case, manufacturing the assets for each extract/load took about five minutes to complete when this metadata had to be manufactured from scratch each time. After caching, however the entire duration became less than a minute. Many ELT-oriented tools actually do consume the catalog but unfortunately  do not consider this aspect of data processing - that the metadata itself - when manufactured from static assets, becomes a repeatable product. A perfect candidate for a cache.

Transform metadata - talk about "all the rage". Hey, people want ELT, but they don't want to risk losing track of how to trace data lineage through the multiple stages of SQL statements. After all, by the time the data actually arrives in our BI tool, we might see the data undergo hundreds of insert/select queries to arrive at the final data outcome. What if an analyst wants to perform track-back lineage of the data, or perhaps even do an impact analyis of the environment? These are impossible without metadata-driven artifacts, rather than allowing SQL guys to manufacture any arbitrary query on-the-fly.  What does this look like? Well, think in terms of source columns, rules applied to those columns and their outome sent to target columns. Then we need a filter clause (join, where, filter, group) that is easily identified. Of all this, we need to capture rules, participating tables, participating columns (both in the source to target map and in the filter clause). It's not really all that hard to capture it if the developers have followed certain rules.

Rules-based asset manufacturing - (a spinoff of one of the above) is simply providing a structured template where developers can formulate and test a single query for accuracy, or multiple dependent queries. But in either case, the metadata in the catalog can provide us with the the raw structures -  columns, tables, etc and we can then formulate templates from these. We then hand off this template to a developer and they fill in the details. They can't really get themselves into trouble because if the developer tries to do something illegal or ignorant, it's all part of the game to us. Our environment requires the developer to adapt - or the query simply won't run. I was on a site where we had only a handful of basic programming rules. Easy enough - like falling off a log. But rules will not protect us from someone who is willing to break them. Here we have templates that provide the balance between rigidity and infinite flexibility. Make the template work and you are by definition in-compliance.

Without elaborating into infinite detail, the realm of ELT is still maturing. ETL tools jockey for position in push-down approaches, but know that if they are really good at this, they are essentially working themselves out of a job. If for no better reason than they have effectively harnesses a Netezza machine to do all of the heavy-lifting their own hard-won capabilities currently do. And if so, the irony is that any one of their smaller competitors stands to gain much more in this marketing space because they have less to lose. After all, we don't buy a power-hitter ETL tool and relegate it to data-transport/transfer activities (it's new role alongside the Netezza machine) but a smaller player would love to fill this spot, grow it, mature it and deliver it as a packaged method to create fire-from-fingertips.

What would be possible in your world if you had a database that could  use all of the information relevant to your analysis: as much business  history as you have; all telemetry data generated each day; every  customer action and interaction? What if you did not need to have  specialized expertise to use such a high powered tool? What if you could  accurately predict, in advance, how long this tool would take to answer  your hardest problem (even when the answer is not 42!)?

I  would like to introduce you to the Netezza TwinFin analytic appliance.  As an appliance the TwinFin is designed for a particular function -  turning your data into useful information. This is more than just  looking into the rear-view mirror to see what has happened. It is the  ability to take millions and billions of events and identify patterns  such as network security breaches from 30 days of cflow and DPI data;  answer what-if questions like the bank that wants to figure out the  impact of reducing the hold time on uncleared credit card  authorizations; and predict your customers' future behavior like the  mobile carrier combining subscriber experience at the network level with  analysis of call center interactions, CDRs and call forwarding  configuration to determine those subscribers at the highest risk to  churn in the next 30 days.

The Netezza TwinFin delivers  these capabilities with a focus on very high performance, providing  excellent business value, in an appliance package that is simple to  install, deploy and operate. Customers that have migrated their analytic  application to Netezza routinely tell us that 80% or more of their  DBA's time is freed up to focus on business problems instead of internal  database and table management. The TwinFin does this with several  incredibly powerful technologies including field programmable gate  arrays (FPGAs) deployed in an asymmetric massively parallel,  shared-nothing architecture controlled by an advanced SQL language  parser and optimizer.

Just like the fuel-injection in  your car, or the aerodynamics of lift in an airplane, you don't need to  know what these are or how they work to take advantage of them to get  you where you need to go. The Netezza TwinFin analytic appliance  delivers its functionality in a package for which you already have  expertise. It is deployed on an IP network and programmed with SQL. It  is already integrated with most data management and reporting tools and  will integrate right into your existing environment.

So  take your biggest data set, your toughest queries and get to know the  TwinFin. We love nothing more than the opportunity to show you in  person. Visit us at http://www.netezza.com/testdrive/ and get to know the TwinFin at your site.