The Grouch Blog

I hate it when I am wrong

2011-02-28T20:41:00.003-05:00

I recently learned that one of my blog posts about the Oracle MERGE statement has one not one-hundred percent correct statement.

"This means the DELETE does not use column data in the USING clause sub-query; very important. Some column in the UPDATE must be updated for the DELETE to work. To handle this set one or more columns to values that would never appear (in combination) in the data and use those values as a delete trigger."

From Oracle 10g 2 and higher SQL Guide: "Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. That is, the DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion."

To clarify:

The DELETE WHERE condition can reference a column from the USING (the source) but the row table in the MERGE INTO (the target) must be have a column updated.

I need to do more experimentation before I correct previous posts and the best way to code for this. I am guessing that a "transaction" column can be in the source (USING) and then that can be tested on the DELETE WHERE and is part of a DECODE(u.indicator,'DELETE','i.column,u.column) so false data are not needed for columns with a NOT NULL constraint.

PCI compliance

2011-02-02T12:19:00.001-05:00

Why do searches for PCI compliance on the database return only links to people selling software and or consulting services?

New mobos and 16GB memory

2011-01-27T18:40:00.002-05:00

So I'm going through Newegg looking at motherboard plus AMD processor combo deals, tempting myself to spend money. I want a system with 16GB of memory so I can do virtualization (as in Windows DC, SQL Server, Oracle Database all running at the same time). The first five motherboards I look at support 16GB of DDR3 memory in their specifications at Newegg and at the manufacturer website but when you look at the memory compatibility list NONE of them have verified configurations with 16GB of memory. NONE of them. How can you claim support without verification? The most common verified configuration was 2GB of memory. Who the FRAK buys a high end motherboard with the intent of having only 2GB of memory. That will barely run Windows 7 Ultra Professionasupercalifrakkinfantastic 64bit turbo while watching Blue-Ray movies and playing Crysis 2 full screen at 2 billion frames per second.

VMWare teams, part one

2011-01-21T06:00:00.002-05:00

I'm fartin' around with Teams in VMWare Workstation and publishing steps I have used.

Team

What you will need:

Windows Server 2008
VMWare Workstation
hardware with at least 4 GB of memory and 200 GB of disk
patience
some beer or alcohol of choice helps too

Steps:

Create a Team
New Team; named SewerSystem
Don't add VMs yet
Add one LAN segment
Finish

VM1 - Domain Controller

Steps:

Add new VM; Typical for Windows 2008 Server Core (easy install) SepticTank located in the SewerSystem directory
Modify VM configuration to have a single NIC on the LAN segment
Let easy install do the OS and VMware tools, create a user called sadmin
Log in as sadmin after installation completes and reboots a couple times; there should be a command window:
netdom renamecomputer %USERDOMAIN% /NewName:DC1
netsh interface ip set address "Local Area Connection" static 192.168.0.1 255.255.255.0 192.168.0.1 1
net user Administrator Passw0rd1234
shutdown /r /f /t 0 /d p:2:3 /c "STFU"
start /w ocsetup DNS-Server-Core-Role
dcpromo /unattend /AutoConfigDNS=Yes /DomainNetBiosName=grouchnet /NewDomainDNSName=grouch.net /ReplicaOrNewDomain=Domain /NewDomain=Forest /ForestLevel=3 /DomainLevel=3 /SafeModeAdminPassword="Passw0rd1234"
net user a-grouchy "Passw0rd1234" /add /fullname:"A Grouch" /domain
net group "Domain Admins" "a-grouchy" /add /domain
net user e-grouchy "Passw0rd1234" /add /fullname:"A Grouch" /domain
net group "Enterprise Admins" "e-grouchy" /add /domain

Step descriptions:

duh
duh
duh, the easy install can't log in as Administrator so create one
duh, I had to log in once or twice and reboot
rename computer
set NIC to static IP
set Administrator password (was blank)
reboot
configure DNS
configure domain controller (reboots)
add a domain admin user
add to the domain admin group
add an enterprise admin user
add to the enterprise admin group

References:

How to Configure a Server Core Domain Controller: Vanilla to First DC in a Forest

VMWare host-only network, part one

2011-01-20T07:53:00.000-05:00

I'm fartin' around with host-only networking in VMWare Workstation and publishing steps I have used.

VM1 – domain controller with DNS

What you will need:

Windows Server 2008
VMWare Workstation (will differ for other virtualization products)
hardware with at least 4 GB of memory and 200 GB of disk
patience
some beer or alcohol of choice helps too

Steps:

Create a VM for Windows 2008 Server (easy install)
Modify VM configuration to have a single NIC, host-only network
Let easy install do the OS and VMware tools
Log in as Administrator then set up the NIC with a static IP address (usually 192.168.xxx.yyy with yyy usually 128, subnet 255.255.255.0 default gateway 192.168.xxx.001)
Personal preference: change the computer name and description from the default WIN-K7AATU-BARADA-N1KT08 to something reasonable and easy to remember
Reboot
Log in as Administrator
Run dcpromo.exe; Active Directory Domain Services will begin installing
At the Active Directory Domain Services Installation Wizard leave "Use Advanced mode installation"; Next
Next
Create a new domain in a new forest; Next
Enter a Fully Qualified Domain Name of the forest root domain (grouch.net, b.org, example.com, ...); Next
Select Windows Server 2008 as the Forest Functional Level; Next
In Additional Domain Controller Options page, select DNS server; Next
Click Continue for the manual delegation message
Accept the defaults for database, log files and SYSVOL folders; Next
Enter a Directory Services Restore Mode Administrator Password (DSRM); Next
Summary; Next
Check Reboot on completion then go get a beer or five
Log in as Administrator of your new domain controller and finish your beers

References

Throw some server at it

2011-01-19T18:30:00.003-05:00

From one developer to another; use as much memory and resources as possible because the servers can handle it and we need to make our lives easier.

There are a few vomit inducing things that pop into my mind when I hear things like that.

I fully comprehend and realize that the days of C.I.C.S. and nitpicking over kilobytes of memory are over in favor of get it done now and throw resources at it world of today. We have scalable clouds now, right? Why bother doing something efficiently when you can just do it, get it done and make yourself look good (or save your job, or make your resource manager look good). Just add another node.

Something has to give somewhere though.

As noted in a previous post, adding a millisecond to each transaction might seem like nothing in the long run but when millions of transactions are taking place and those milliseconds multiplying like those bunnies from that Monty Python movie; there will be performance issues.

Then the developers complain to the system administrators and users complain to the help desk (who also complain to the system administrators) and web users complain or simply take their business elsewhere which causes the business units to complain to their managers who meet over lunch at a bistro with IT managers who then order the system administrators to fix it.

Resources are finite, even in a cloud.

Resources are finite on the client, too. Many in the younger generation would rather do things on their phones than sit in front of a computer and not every phone has Flash, Silverlight, etc. or enough display space to handle the additional burden of poorly conceived code.

I just thought of a business model. I'll ask your managers if they are having performance problems and then charge by the hour to point out issues that will cause a crap-ton of meetings, lots of paper to be printed, and inner turmoil to increase between the development staff and administrators while users think I'm a minor god (or something).

Another crapplication

2010-12-13T07:08:00.002-05:00

It never ceases to amaze me how software that sells for five figures or more can contain so much shit code. My most recent example: I noticed a CPU hogging process running on our Oracle server and was astonished at two things. First thing was a SELECT * in a static query containing eight inner joins. The second item was that same statement (and over 3000 almost identical statements) was not using a bind variable in the WHERE clause for the primary key (a VARCHAR2(254) containing a GUID).

There is no excuse for production code containing a SELECT * from any number of tables. What made this example even more inexcusable was the table contained a BLOB and judging by the pattern of other SQL statements these queries were pulling back a description and last updated by date.

There is only one excuse that I know of to NOT use bind variables in prepared statements. To be honest, I do not know if this is one hundred percent true of Oracle or other databases, but if the query is selecting by status or other column where the difference between the counts of the values is extreme then it would be best to not use a bind variable on that status. If a table contains a billion orders where status is filled and a couple hundred with a status of cancelled and the query is selecting only by status then using a bind variable might not provide an optimal plan for both situations (it might use a full table scan on filled and index for cancelled). In the above example, the software was creating, parsing, opening, fetching then closing the same logic SQL thousands of times when it should have created, parsed once then bound and executed (reused) thousands of times. It was a single row select by primary key (a GUID even) and that should always be done using a bind variable.

The developers might not be at fault here. The above scenario reeks of poor, generated, object oriented code. Get the primary keys for all of your data objects then read them one by one to display on some dumb-ass web page. More likely: read incoming XML data stream into business logic tier then instantiate all the needed business and data turds then have the business turds use the data turds to retrieve data using the exact opposite of a single, bound, efficient SQL statement, then convert the results of all the turds to XML so the rendering engine can puke it out to a web browser, iDroid, dumb phone, really dumb phone, tablet, pad, tampon, car dashboard, HAM radio or intelligent kitchen appliance.

What frustrates me is that I am seeing this crap in a pricey application. I would be tolerant it were coming from some schmuck trying to write an application to pay his (or her, no wait, probably his) way through college. This application has numerous customers, all of which are suffering with this crapplication and the unlucky system administrators are stuck between the bean counters and the vendor when attempting to tune the piece of weasel poop.

A good Ask Tom article

HTC EVO the first month

2010-11-23T04:14:00.000-05:00

I have had my HTC EVO for over a month now. The experience has been good so far.

I have a couple of nitpicks. The message indicator light should flash while the device is connected to a charger or USB. SMS messages and mail messages should have the option of repeating the notification tone until responded to (like a pager).

Battery life is poor but what can anyone expect. Does anyone really believe they will be watching HD video over 4G without sucking that wafer-thin battery dry in less than an hour? If I rarely use the device the battery will be around 53 percent charged by the end of the day; with no Wi-Fi, 4G, Bluetooth or GPS services active. The wife uses Pandora all day at work so she leaves the phone attached via USB all day. Anyone texting, talking and Facebarfing and Twitching all day will likely not have a charge at the end of the day.

The 4G service is now available around my house out in the middle of nowhere but does not appear to be available in downtown Columbus where I work (strange). When I ran speed tests using 4G I was getting very mixed results depending upon where I was and what destination I was hitting. The down speed was comparable with Wi-Fi but the up-speed was much faster about half the time. The whole 4G experience is going to need a year or two before it is widely usable and stable and firmware for phones with that service is stable.

I have also paid my first bill. The initial shock took a few beers to get through.

Bye to Schelotto, Hejduk, more

2010-11-16T13:27:00.001-05:00

It is definitely a sad day for the Crew organization but in the long run it is just a day. We all knew that good guys like Oughton and Hejduk that have put a face on this team would no longer be able to contribute toward the common goal of the organization and most fans; hardware. Legends like Schelotto that brought class and talent would eventually either move on or be pushed out. Even Padula, the pirate at left back, who just a season or two ago was considered the key component of the Crew championship run, would fade away.

I think what makes today really hard to deal with is the sudden harshness of the events. Our season was over in an instant with a missed penalty and most of the heart, soul and leadership is likely gone just as abruptly. As the shock of the playoff loss set in maybe only a handful of people realized that would be the last time they would see those four players in a Crew uniform; it sure did not for me.

As mentioned in my previous blog post, I was hoping Schelotto was going to be retained one more season to help teach and shape the attacking midfield and his successor. That is what he should have been doing this 2010 season; getting his replacement ready and perhaps even using his last season as a rallying point to will the rest of the team back into the finals. Instead it is an abrupt end that will leave a bitter taste.

It is easy to feel like twice shat feces about this but it is now up to the Crew organization to deliver. They want to go young, fine. Fans have survived youth movements with each coach before Bob.

The most important priority now is to get that skilled player and face for the Crew. My wife knows little about soccer but she knew who Schelotto and Hejduk (the surfer dude with the long hair, no!) were and more importantly what they brought to the team: he was their best player and he was one of the favorite players (big fan favorite, remember the pictures of him in the north end).

We still need a finisher up top. I like Renteria but seriously, it is about time we invested a little more at the position beyond what we have done before.

I also am willing to hold off on judgment until we get some results. It will be months and a couple of drafts before the Crew will know where they need to throw their money. That is going to be a long, suck-ass couple of months, too. We were fortunate to have Schelotto and Hejduk while they were here and should be thankful for it. It is now up to the Crew to find the next group of players for us to be thankful for.

Fan-favorite Schelotto leaving Columbus Crew (TheDispatch)
Schelotto has elite place in city's sports history - great comparison to Sergei Fedorov
Keep GBS around
A letter from Mark McCullers (MassiveReport)

Also, just read on the forums: Barley's Christmas Ale Guest Tapper: Columbus Crew captain Frankie Hejduk

Custom EVO wallpapers

2010-11-11T17:39:00.005-05:00

There appears to be a lot of confusion and mixed information on the best way to do wallpapers for the HTC EVO mobile device. There are about four resolutions and a couple of techniques in various forums. I did some of my own research and this is what I found. I assume you are using a stock device and have not removed the base UI.

The EVO screen is 480 x 800 (pixels; all numbers are in pixels).

There are seven homes per scene; the middle and three to the left and to the right.

The best resolution for incoming images is 960 x 800.

When choosing wallpaper, expand the green boundary box to use the entire image. This results in the least amount of distortion of the final image displayed on the EVO homes.

The top 38 pixels will never be seen; they are covered by the status bar. If you have an embarrassing zit on your forehead or do not want to folks to know you are a NASCAR fan then make sure your acne and logos are in the top 38 pixels of the wallpaper.

The leftmost and rightmost 48 pixels will never be seen. On the left side, pixels 1-48 are hidden, 49-59 can be seen momentarily when swiping right when on the leftmost home, and pixel 60 bleeds onto the leftmost home. On the right side it is the same just in reverse; but the last pixel does not bleed into the rightmost home.

The bottom 56 to 70 pixels are usually hidden by the lock/menu bar except when you pinch and then select a home and when the device is locked. So food stains on your shirt, nipples and muffin tops would go in that area.

The extended homes do not have their own separate wallpaper. The center home is in the middle 480 x 800 pixels of your wallpaper. When you swipe in a direction to get to one of the extended homes the wallpaper is shifted 60 pixels in that direction (240 pixels on each extended side, 60 of those are not visible).

So start with a 840 x 762 image assuming that the bottom 60 or so pixels will be seen when the device is locked but usually will be hidden. Add borders (solid black is fine) to the top, left and right to make the image 960 x 800. Send it to your device using whatever method you choose and then enjoy.

Of course, someone from HTC will come along and probably debunk all of this but it works for me.

The Columbus Crew 2010, summary

2010-11-08T19:53:00.001-05:00

I find myself in a similar situation. Last year on this day I wrote about the premature Columbus Crew exit from the MLS playoffs Thursday to Real Salt Lake. This season the same thing has happened, just at the hands of the Colorado Rapids. So when I look back at that 2009 season post what has changed and what has stayed the same?

I still hate the MLS playoff system and feel it gives the advantage to the lesser record team because the lesser team is likely to use the home field advantage to get more goals in the first game then play a quote boring defensive system assuming the top seeded team must score win by one more goal than the deficit.

There were positive spots to the 2010 season. The Crew made it to the playoffs (falling to Colorado on a missed penalty kick), to the finals of the Lamar Hunt U.S. Open Cup (falling to Seattle), and are still in the CONCACAF Champions Cup/Champions League.

Sagging attendance would probably lead the negative points when the league and cup results are removed. The Crew did not find a replacement for Schelotto (that we know of). Some players that should have been relegated to the bench this season are still signed through next season.

I also think it still boils down to one word: inconsistency. First there was the inconsistent lineup due to injuries to key players like Rogers, O'Rourke, Hesmer and Renteria. Second, inconsistent performances from Rogers, Hejduk, Hesmer, Padula, and Schelotto in some ways. Forwards would be hot one game, go cold, go to the bench for another forward, that forward would be hot, etc. Third, inconsistent coaching. Robert Warzycha seemed to get out-coached near the end of the season (again) and many have questioned his line-up choices.

So (again) where does the Crew go from here?

Warzycha is not the immediate problem. If Kreis and Nowak can coach and get results then Warzycha can. The Crew organization as a whole is degrading before our eyes from the top down. The Crew need a shot in the arm from somewhere whether it be an exciting new player or some new feature of the team or something marketable.

I will probably get laughed at for this but I think we should find some way to get Jon Busch back as backup keeper. Jon is one of those franchise players you would like to always have on your team in some way shape or form. Bring him back to where he started. Gruenebaum would probably go.

In the defense we need to find a replacement for Hejduk in a starting role. Francis can start for Padula if we keep him. I am guessing Iro or Brunner will go to expansion leaving O'Rourke in the mix with Marshall.

Gaven and Carroll are the only midfield players I would keep. Moffat had sub-adequate performances (again, inconsistency). Ekpo and Burns are good players. Rogers is a huge question mark but I would love to see him get a solid season with Schelotto. Oughton and can be kept with the team as long as it is feasible to do so. Schelotto should be retained for one more season but the Crew need to find his replacement this season.

The forward spots need overhauled. Garey will likely go to expansion. Renteria should start and if Mendoza can put in a consistent performance like the one at the home playoff match then he should be in the mix as well. Lenhart is a bench player. We could still use a quality striker but I do not see us getting one, ever (pessimistic but realistic).

If I were asked to name the priorities for the 2011 season they would be: retain Schelotto for one more season as a player, acquire Schelotto's replacement so he can work alongside Guillermo and develop to lead the team. The next thing the Crew needs is a forward capable of playing with Schelotto (and his successor) and scoring a bucket load of goals. Warzycha should be retained as coach unless an obvious and adequate replacement is both available and feasible for the organization. Last, the Crew need a shot in the arm in the form of something exciting in the organization or a charismatic player to get some buzz generated about the team.

Okay, I'm one of the sheep now

2010-10-19T22:15:00.002-04:00

I purchased a HTC EVO smart phone and service plan. I feel, strange...

It is a really nice device. Calling this and all other new generation of devices a phone is really stupid. A phone is something you shove in your ear and talk to someone with. This is a mobile device, not a phone.

It has the Android 2.2 system, etc. etc. You can look all of that crap up on Google.

Speaking of Google, the synchronization with Google contacts was instantaneous. Within two minutes the device was activated and all of my contacts were in the phone and ready to use. That is very awesome. Facebook and Yahoo crap works too.

The process of purchasing the device took two hours. I have a history with, um, Tnirps. It took nine years to clear up this history and it still was not totally clean when we went to the Tnirps store. The Tnirps store was a petri dish of the most odd, obnoxious, and just some of the worst examples of humanity. The chick with the tattoos and the half mohawk was interesting. Then there was the two gangstas that were both stoned solid and bitching about the five minute wait. Disgusting.

What sucks about the HTC EVO is that Tnirps is charging us an extra $10 a month for 4G service that does not exist in any consistently reliable form within a hundred miles of the city. What a crock. The stand Tnirps takes is, tough cookies; pay the $10 and we might get you 4G service by November.

Just because I am one of the sheep now does not make me not hate the people that drive while using their phones and devices. I saw some woman trying to manage her mobile device while driving on I-70 in rush hour traffic; not paying attention at all to the road and vehicles in front of her.

They should create a special lane for cell phone drivers; just like the special lane for bus and taxi services. Every mile should have a turn off that leads into a car crusher so if the driver is not paying attention they get in a one way lane to a 6 foot square of death. If you get to your destination without crashing into someone or causing an accident behind you or getting demolished by the idiot on the phone behind you and can pay attention enough to not get sucked into the car crusher then congratulations.

Once I get past making backgrounds for my home pages and ring tones using Bender from Futurama I might actually learn how to use the device and have it become a valuable business and social (bwah hah hah) asset.

Oracle FULL JOIN or UNION ALL

2010-08-11T16:49:00.002-04:00

Someone was kind enough to argue with me recently about doing a UNION ALL instead of a FULL JOIN in Oracle (in a MERGE). This same poop chute also posted a spam link that probably led to some Trojan downloader.

To make a long post short, Oracle usually does what it thinks is the best way of fetching data and it is spot on most of the time. Hints and fresh statistics can be used when mother optimizer is not right.

Consider this table:

CREATE TABLE megacrap AS
 SELECT
   LEVEL AS mega_id
 , TO_CHAR(TRUNC(SYSDATE)-LEVEL,'MM/DD/YYYY') AS mega_varchar
 , TRUNC(SYSDATE,'YEAR')+LEVEL AS mega_date
 FROM DUAL
 CONNECT BY ROWNUM BETWEEN 1 AND 4000;
ALTER TABLE megacrap ADD(CONSTRAINT pk_megacrap PRIMARY KEY(mega_id));

Updated with this MERGE:

MERGE INTO megacrap m
USING (
 SELECT
  NVL(v1.mega_id,t1.mega_id) AS mega_id
, v1.mega_varchar
, v1.mega_date
 FROM megacrap t1
 FULL JOIN (
  SELECT
    LEVEL AS mega_id
  , TO_CHAR(TRUNC(SYSDATE)-LEVEL,'MM/DD/YYYY') AS mega_varchar
  , DECODE(LEVEL,420,NULL,TRUNC(SYSDATE,'YEAR')+LEVEL) AS mega_date
  FROM DUAL
  CONNECT BY ROWNUM BETWEEN 1 AND 4100 --- 100 new rows
 ) v1 ON v1.mega_id = t1.mega_id
 WHERE v1.mega_id IS NULL OR v1.mega_id NOT IN(1,2,69) --- delete 1,2,69
) u
ON (m.mega_id = u.mega_id)
WHEN MATCHED THEN
UPDATE SET
  m.mega_varchar = u.mega_varchar
, m.mega_date = u.mega_date
WHERE DECODE(m.mega_varchar,u.mega_varchar,1,0) = 0 OR DECODE(m.mega_date,u.mega_date,1,0) = 0
DELETE WHERE m.mega_varchar IS NULL
WHEN NOT MATCHED THEN
INSERT VALUES (
  u.mega_id
, u.mega_varchar
, u.mega_date
);

Guess what the plan looks like: MERGE STATEMENT

MERGE MEGACRAP
- VIEW
  - HASH JOIN OUTER
    - VIEW
      - UNION ALL
        HASH JOIN RIGHT OUTER
        VIEW
        COUNT
        CONNECT BY WITHOUT FILTERING
        FAST DUAL
        INDEX PK_MEGACRAP FAST FULL SCAN
        NESTED LOOPS ANTI
        VIEW
        COUNT
        CONNECT BY WITHOUT FILTERING
        FAST DUAL
        INDEX PK_MEGACRAP UNIQUE SCAN
    - TABLE ACCESS MEGACRAP FULL

Mother optimizer decided a UNION ALL was the best option. Now which SQL statement do you want to code and maintain? The FULL JOIN above or the UNION ALL with GROUP BY or UNION ALL with GROUP BY and ANTI JOIN?

Oracle Database SQL Language Reference 11g Release 2 (11.2)

Cell phones while driving, again

2010-08-09T21:45:00.003-04:00

I have become sick and tired of people in general. The stupidity and callousness of the masses whittles away at my patience every day, day after day. I grasp for signs of humanity but rarely find genuine goodness in anyone. What makes me write today is something that has caused me to rant before. This time the topic could have caused loss of my health or even my life.

Someone on a cell phone nearly ran me off the road this morning on route 33 on my way to work. She started moving over into my lane forcing me to brake and travel over the rumble strips and partially into the grass at about 60 miles per hour. She must have hit her breaks and slowed down as I recovered back onto the road and accelerated as far as I could away from her and her vehicle. I was sick to my stomach for a good thirty minutes after that. I should have tossed my cookies at work, I probably would have felt better.

Two weeks ago someone in a car with a cell phone stuck to her ear blatantly ran a red light in front of me, coming about two feet from colliding with my car. She gestured to me like as if I did something wrong. She ran the red light from a semi-blind spot for me, from behind a bridge.

The was also an asshole that made a left turn in front of me as I was making a right turn but at least he was not on a cell phone; unless he was using hands-free Bluetooth crap.

Cell phones while driving are dangerous. It does not matter how much technology is thrown at this it will not change how irresponsible some people are. These people are not going to change until they collide with another vehicle or wrap their car around a light pole or kill someone. I do not want to be that statistic.

If you get a call then either offer to call back or pull over somewhere and talk.

I do not want my tombstone to read "killed by some douchebag driving a Durango while blabbing on a cell phone"

Back to school, save big?

2010-07-21T06:36:00.004-04:00

I'm looking at the most recent Tramlaw savings newsletter and it had a back to school section aimed at college students. Yes, you new and returning college students can save money on college essentials like MP3 players, Oisiv flat panel LCD TVs and video games.

Huh? What is wrong with that picture.

First question, MP3 players? Get with the times. Most every smart phone and semi-intelligent phone, and flat out dumb phone can play your MP3 library. Why purchase another device; unless you have some Batman utility belt fetish?

Flat panel LCD? How about letting mom and dad have the LCD while you take their hand me down television. Who has the higher percentage of being burglarized and getting that nice, new LCD stolen; someone living on campus maybe?

Video games? Maybe for Christmas but not for new students. I'm also guessing that anyone already into video games has already been nurtured through childhood by five or six game consoles and probably does not need a new one. Serious gamers are going to want a gamer PC (see below) with quad-core, multi-video, SSD in RAID-0 goodness; which they also probably already have.

High ticket electronics for college students should start at laptops/netbooks. In this day and age they are almost a necessity.

In all fairness to Tramlaw, they do have an "off to college" department page that advertises their cheap-ass furniture and essentials like appliances, vacuum cleaners, bedding, cheap storage and microwaves. That department also advertises the MP3 players, video games and mini-fridges that should be secondary to everything else a college student is going to need like outrageously priced books and lab materials, tuition, rent, fuel, utilities, food (Tramlaw covers that one, though), transportation and communications.

But hey, that is what student loans are for, right?

Importing .csv into Google contacts

2010-07-08T11:57:00.000-04:00

Yesterday I started work on creating a comma separated values file to import into Google for my looming smart phone purchase.

I added a fake contact, Rod Vanhugendong, and all of Rod’s contact information including work, personal, birthday, etc. I exported that contact to a .csv file so I could get column headers and know where to put data.

I put a group of contacts in, saved the file and then imported the data.

My first attempt failed because all of the data got shoved into notes, not the individual fields. I searched Google help and found little from Google but numerous posts from individuals having similar issues. There were two solutions so I tried one and that appeared to work, eventually. The solution that worked put everything into sections with column headings like Section 1-Mobile.

More on this later; I had some time to kill during lunch.

Upgrade time, mini rant

2010-07-07T11:43:00.003-04:00

This is a little mini-rant. I was looking to upgrade my home workstation from an AMD X2 to a Phenom II quad-core and noticed there are six-core processors out now. I checked the Newegg reviews and had a laugh.

One purchaser had one of them with with 3TB of RAID10 and dual video cards and 8 GB of memory and the system can miraculously handle anything thrown at it. I would hope so. That system has more processing power than all the African nations that qualified for the World Cup it damn well be able to handle everything thrown at it.

Next you have the overclockers that are looking to squeeze that extra decimal out of their Windows 7 Ultimate score by running the processor with the fastest clock speed and the most cores beyond the unit specifications. It is a concept I just don't understand in the post-Celeron era of the 1990's. After the upgraded cooling the cost is more than the next step up in processors with stock equipment.

I priced out some configurations and folks are dropping anywhere from $1,300 to $2,400 every 3 or 4 months when new technology comes out. Where are they getting the money?

Hands free texting

2010-06-29T22:38:00.003-04:00

I just saw a spot on the local news about hands free texting. You talk into your bluetooth headset and your voice is converted to text so you can text your friends or post an Twitter update on your way home from work.

Seriously?

Why are people so desperate to do anything but drive when they are driving a car? Do we really need more distractions while operating machines capable of causing serious injury and death when used irresponsibly? Does that bitch on her cell phone yelling at me while speeding off in the wrong direction in the Walmart parking lot really need technology to Twitter the deal she just got kitty litter?

Ubuntu 10.04 to WHS shares

2010-06-28T20:38:00.003-04:00

Ok so I have this Windows Home Server and I also have an Ubuntu 10.04 desktop that I would like to connect to this Windows Home Server. I tried Nautilus Windows Network and none of the work groups would mount. The original document from the Internet (duplicated about eighty times on different servers) offers no help beyond the "it should be there".

One post on Ubuntu forums suggested making a directory in /mnt for each Windows Home Server then mounting each share with mount -t cifs. To me this seems like a lot of work but I have not found another way to get this to work so install smbfs and create some directories.

sudo aptitude install smbfs
for i in Documents Music Photos Software Videos; do mkdir -p ~/Grouchy-WHS/$i; done

To get shares to mount I had to specify a user and a password in the options but a single line for loop did the trick.

for i in Documents Music Photos Software Videos; do sudo mount -t cifs -o user=grouchy,password=getbent //Grouchy-WHS.local/$i ~/Grouchy-WHS/$i; done

Okay, now all I have to do is type the above line in whenever I want to get to the shares. That sounded stupid, so I tried making a custom panel launcher. No success. I eventually created a script to handle the mount and unmount.

for i in Documents Music Photos Software Videos; do sudo umount //Grouchy-WHS.local/$i; done

Both work from the terminal but I cannot get them to work from the panel application. It seems so simple.

2010 Oracle VM, part 2, OEM database

2010-04-27T14:50:00.005-04:00

First, create a linked clone of the OEL5.5 VM created in part one; call it something not stupid - like OEL5.5 OEMGRID. Quick note: you might need to bump the memory of this virtual machine to at least 1.5GB else the Oracle Database installer will complain; that did not happen the first time I did this but the second time I was about 3K (yes, 3 measly F'ing K) short of the minimum.

Start the cloned VM and log in as root.

I had the luxury of having a domain name and static IP address so I used the following tool to configure my network and then edited /etc/hosts:

system-config-network-tui
vi /etc/hosts
10.9.8.7  grouch1.grouch.net grouch1
:x

I would reboot now so the rest can be handled through ssh and cut and paste (unless you like typing).

Oracle Database Preinstallation Requirements

Most of the chapter two Preinstallation Requirements have been set by installing .rpm package oracle-validated (part one). The oracle-validated .rpm installs all Oracle Database dependancies and also modifies /etc/sysctl.conf, /etc/security/limits.conf, /etc/modprobe.conf and /boot/grub/menu.lst to include kernel parameters. It also creates some of the users and groups. Login as root and execute this to finish up and add an administrative user:

/usr/sbin/groupadd oper
/usr/sbin/groupadd oracle
/usr/sbin/usermod -g oinstall -G dba,oper,oracle oracle
passwd oracle
/usr/sbin/useradd grouchy
/usr/sbin/usermod -g grouchy -G dba,wheel grouchy
passwd grouchy

Next step is to create the directories where software will be installed, for database files and for recovery files (note: in real life production land these could be on separate disks or one gigundous LUN, but we are happy fun test land so who cares)

mkdir -p /u01/app/oracle
mkdir -p /u02/oradata
mkdir -p /u03/recovery_area
chown -R oracle:oinstall /u01/app/oracle /u02/oradata /u03/recovery_area
chmod -R 775 /u01/app/oracle /u02/oradata /u03/recovery_area

A reboot is not required but why not (what better way to test what you screwed up). Now might also be a good time to take a snapshot in VMWare.

Oracle Grid Infrastructure

The OEM VM will not be clustered and will not be using Automatic Storage Management (ASM) so the Grid Infrastructure for standalone server and Oracle Cluster Synchronization Services (CSS) are not required.

Installing Oracle Database

Login to the VM as root. Connect the database .iso image as a DVD or mount the DVD and connect that to the VM. If empty directory /media/cdrom does not exist then make it. As root, mount the CD (mount -t iso9660 -o ro /dev/cdrom /media/cdrom).

I use PuTTY with X11 forwarding enabled in my Ubuntu workstation (that I am running VMWare Workstation on) to connect to the VM as user oracle and do the installation.

First, edit ~/.bash_profile and append the following:

umask 022
ORACLE_BASE=/u01/app/oracle
ORACLE_SID=orcl
export PATH
export ORACLE_BASE ORACLE_SID
unset ORACLE_HOME
unset TNS_ADMIN

Do a quick . .bash_profile to execute that script. Next, create some directories in the oracle user home (personal preference):

for i in backup crap expimp junk log run save scripts sql temp
do
mkdir ~/$i
done
cd ~/crap

Start installing:

/media/cdrom/runInstaller
Skipped security updates; Yes
Installation Option; Install database software only
Grid Options; Single instance database installation
Product Languages
Database Edition; Enterprise Edition
Installation Location; Oracle Base: /u01/app/oracle Software Location: /u01/app/oracle/product/11.2.0/dbhome_1
Create Inventory; /home/oracle/oraInventory; *1
Operating System Groups;
Prerequisite Checks; all passed
Summary;
Install Product;
Finish

*1 pure laziness on my part because the preinstallation instructions never made nor setup privileges on the default /u01/app/oraInventory but now that I think about it the inventory directory should be outside of the whole OFA structure.

Oracle Database Postinstallation Tasks

I did not do anything here.

Go to Tahiti and check out the installation guide for more information

Updates:

06/22/2010:: Corrected errors, added smart-ass comments, etc. blah blah blah...

My 2010 Oracle VM project, part one

2010-04-26T16:52:00.008-04:00

As I look back through my blog there have been numerous ambitious attempts to do this but all have stopped before they got started. Pretty sad. Such is life, especially when you yearn to have one instead of fiddling around with software when you should be doing anything but.

Here are the constants:

VMWare Workstation 7 on Ubuntu 10.04 64 bit
Oracle Enterprise Linux 5 Release 5 32 bit

Here are my goals:

One VM with OEL, OEM GRID 11.1, Database 11.2 (EM/RMAN repository)
One VM with Database 11.2 using ASM

The first step is to create a VM I can use as a template for my two goal VMs.

Create template OEL5.5 VM:

Create a VM with at least 1GB of memory (2GB if feasible) and one 20GB (to 40GB) virtual disk (bridged network, uncheck automatically connect new USB device, do not connect sound card and printer at power on)
Power on the VM with the Oracle Enterprise Linux 5 Release 5 mounted as an .iso (or physical DVD)
Press ENTER at the "boot:" prompt, next, skip, next
Choose language and keyboard
OEL partitions everything perfectly, no need to review or modify (/boot with LVM with swap and / volumes)
Next, GRUB, DHCP, time zone (uncheck System clock uses UTC)
Set root password
Select Customize now. Next.
Desktop Environments. Uncheck GNOME
Applications. Uncheck all (except maybe Text Based Internet)
Base System. Uncheck Dialup Networking Support and X Window
Base System. Check System Tools; click Optional packages, check oracle-validated
Next, next, installing packages, reboot.
Highlight networking, then run tool
Set Firewall and SELinux both to Disabled
Next
OS installation complete. Login. User root.

Install VMWare tools:

VM: Install VMWare tools (will prompt you to mount something)
mkdir /media/cdrom
mount -o ro /dev/cdrom /media/cdrom
cd
tar -xzf /media/cdrom/VM*tar.gz
cd vmware-tools-distrib
./vmware-install.pl
I kept defaults for everything; installation unmounts the VMWare tools
cd
rm -fr vmware-tools-distrib
shutdown -h now

Take a snapshot of the OEL5.5 virtual machine. This snapshot will be used in the cloning process.

In the next parts I will use this template VM to create the OEM GRID and Database VMs

Updates:

06/22/2010:: Now on Ubuntu 10.04

The Oracle CBO statistical time bomb

2010-04-20T16:36:00.001-04:00

Ever have a query or process that runs fine for years and years and years but then one day just slows to a crawl, going from seconds to hours in execution time? If so, you have run into what I call the Oracle CBO statistical time bomb.

Oracle 10g/11g has a daily job that queries for "stale" and missing optimizer statistics on Oracle table/index objects. Those optimizer statistics are used by the optimizer when choosing the best access plan, join types, index and table scans to use for a SQL query. The default for "stale" is if 10 percent of the data have changed (static in 10g, can be changed in 11g).

Let's assume we have this really, really big invoice table that grows at a constant rate over time. This table has an index on the financial quarter. Each quarter roughly 25,000 new rows are inserted, and then updated 3-5 times each (as status changes, etc. whatever), and some idiot needs to clean up a couple hundred bogus rows each month. Here is the table for 2010:

Financial    Total  Insert  Update  Delete  Changed  Percent
Quarter       Rows    Rows    Rows    Rows     Rows  Changed
2010 Q1  1,000,000  25,000  80,000     169  105,169   %10.51 - daily job gathers statistics
2010 Q2  1,024,836  25,000  80,000     169  105,169   %10.26 - daily job gathers statistics
2010 Q3  1,049,672  25,000  80,000     169  105,169   %10.01 - daily job gathers statistics
2010 Q4  1,074,508  25,000  80,000     169  105,169   %09.78 - not stale yet, still have 2010 Q3 statistics

Without current statistics, the Oracle cost-based optimizer thinks one index and access method would be more efficient than a different index and access method (when it really, really, really is not).

I'll need an Oracle Guru to correct me but here is what happens in 2010 Q4:

Your super-optimized, meticulously coded query, queries the invoices by quarter and by another column existing in a massive subquery.

For 2010 Q1,Q2,Q3 the CBO says there are 25,000 rows reading the quarter index and likely less rows using an index on another column (customer number perhaps) if it does the massive subquery first. The CBO got it right and the massive subquery executes first then reads the table via index on the other column.

For 2010 Q4 the CBO says there are no/few rows for Q4 (when there are really 25,000) so read the invoices using the quarter index and then do a nested loop each time on the massive subquery because the cost of the read by quarter index times the cost of the massive subquery is less than the plan it took above (when it really is not). Instead of doing that nested loop on a couple rows (yawn) it does that nested loop massive subquery 25,000 times. Yikes.

What to do? Plan ahead.

Go to Tahiti and research DBMS_STATS in the Oracle Database PL/SQL Packages and Types Reference. Know your tables and diffuse the time bomb before it goes off. Let the daily job handle the stale statistics. Tables that contain an index on a period of time (quarter, year, etc.) or code based on a period of time (i.e. the Spring 2010 line of Mountain Dew with Ginseng and Xanax) should be researched as candidates for statistics gathering outside of Oracle's daily job. Gather table statistics before the big processing periods.

References

How Does An Execution Plan Suddenly Change When The Statistics (And Everything Else) Remains The Same ? (In Limbo)

Oracle and SSIS; ORA-00903 during verification

2010-04-20T15:44:00.000-04:00

Can you tell I have been suppressing my pain and hate up into a nice little ball, instead of talking about my feelings while laying in some lush, peaceful green meadow, sipping Chamomile tea and popping anti-depressants like candy.

This issue is one of those classic, what the **** is going on here errors.

Here is what I am suffering with:

Windows XP and Windows Server 2003
SQL Server 2008 Integration Services
Native OLE DB\Oracle Provider for OLE DB, Oracle 11g R1 or Oracle 10g
OLE DB Source (Oracle) using a query.

The Problem

Getting ORA-00903 table or view not found during execution on verification step on Data Flow OLE DB Oracle source; even though at design time (in the OLE DB source editor dialog) the SQL parses correctly AND pulls back test data in the source AND the query runs successfully everywhere else using the same credentials.

The Solution

Drink.

Smoke.

Break something.

Then:

in Oracle, CREATE a VIEW for the query
if the query contains a user function you will also need to in Oracle, GRANT EXECUTE ON schema.function TO view_schema_owner WITH GRANT OPTION
in Oracle, GRANT SELECT on VIEW to the USER used by SSIS (if necessary)
in SSIS, use the Advanced Editor on the OLD DB source; Component Properties; AccessMode = OpenRowset, OpenRowset = SCHEMA.view (standard editor will not pull up the view and you are limited to a drop-down)

There that was painless, right?

I have been told that shutting down Visual Studio sometimes helps (it did not). Shutting down Visual Studio works if you switch databases in the connection manager and then get ORA-00903 table or view not found in the OLE DB source. One co-worker stated he rebooted his machine and then things worked (it did not).

I'm sure I was doing something stupid like trying to use a subquery or SYSDATE but WTF; it parsed, it pulled back test data, it works everywhere but the verification step when debugging it.

****!

Oracle and SSIS; SQL tasks and procedures

2010-04-20T15:08:00.002-04:00

I am posting today to hopefully prevent others from the hell I have been through. I really have no idea why I continue to do this as my psyche at this point is so eroded that I should be deriving pleasure from the suffering of others like some serial killer in the making.

Getting Oracle and Microsoft to play nice is like trying to get Sean Hannity and Nancy Pelosi to share a quiet evening together making babies while discussing health care and what to spend their money on. If that reference is dated then consider the choice of wiping with toilet paper or the large grain sandpaper and then using the sandpaper; or perhaps shoving a sea urchin up your nose then pulling it out the other nostril.

Here is what I am suffering with:

Windows XP and Windows Server 2003
SQL Server 2008 Integration Services
Native OLE DB\Oracle Provider for OLE DB, Oracle 11g R1 or Oracle 10g
Execute SQL Task trying to execute a PROCEDURE or PACKAGE.PROCEDURE that has input or output parameters

Connection Manager

The only non-default property is RetainSameConnection set to True (connections to Oracle are expensive). Personally, I see no need for resource pooling but that is another post.

Execute SQL Task

General

ConnectionType

OLE DB

SQLSourceType

SQLStatement:

{call your_stupid_package.dumb_ass_procedure(?,?)}
or
BEGIN your_stupid_package.dumb_ass_procedure(?,?); END;

IsQueryStoredProcedure

False (usually greyed out, but can be set via Properties, but has no bearing on anything)

Parameter Mapping; Add each parameter:

Variable Name: whatever User::variable you have defined already
Direction: Input or Output
Data Type: VARCHAR (I have not been able to get anything else to work yet)
Parameter Name: the left to right sequence number of the parameter starting at zero (so, if you only have one the parameter name is 0)
Parameter Size: the size of the variable

Conclusion

I would cite references but that vast majority of searches dead-end with single posts in forums where some poor slob went begging for help and found none; or sometimes found a reply from another kindred spirit also suffering through the same agony. Those who did have solutions were not passing parameters or were using an Expression to build the query string with parameters (doing their own SQL injection; which also does not help those using output parameters).

The above works, with a couple warnings, but works.

I did go in a ...

2010-03-01T21:04:00.003-05:00

Vincent: Yeah baby, you'd dig it the most. But you know what the funniest thing about Europe is?
Jules: What?
Vincent: It's the little differences. I mean, they got the same s... over there that we got here, but it's just – it's just there it's a little different.
Jules: Example?
Vincent: All right. Well, you can walk into a movie theater in Amsterdam and buy a beer. And I don't mean just like in no paper cup, I'm talking about a glass of beer. And in Paris, you can buy a beer at McDonald's. And you know what they call a Quarter Pounder with Cheese in Paris?
Jules: They don't call it a Quarter Pounder with Cheese?
Vincent: Nah, man, they got the metric system, they wouldn't know what the f... a Quarter Pounder is.
Jules: What do they call it?
Vincent: They call it a "Royale with Cheese".
Jules: "Royale with Cheese".
Vincent: That's right.
Jules: What do they call a Big Mac?
Vincent: A Big Mac's a Big Mac, but they call it "Le Big Mac".
Jules: "Le Big Mac." What do they call a Whopper?
Vincent: I don't know, I didn't go in a...

There was probably some reason or list of reasons why Vincent Vega did not go into that fast food establishment in that opening scene from the movie Pulp Fiction. I really cannot remember why we ended up there tonight. It was probably because it was on the same side of the street and I was wanting to avoid a left turn on a very short light from the eight dozen or more better fast food joints across the street. Maybe it was because I remember seeing a steak burger advertised on the television and felt the remote chance it might be palatable.

"We are out of steak."

A burger joint, out of meat. Holy crap. They offer to substitute two hamburger patties which likely meant we microwaved too many frozen hamburger discs and not enough frozen steak blobs and it would not be feasible to make another batch of steak. So instead of steak I get whatever animal they ground up for hamburger.

They are short handed so it takes about five minutes to assemble our meal from the heated storage bins. Yum. Of course, almost half the staff was out back smoking by the dumpster.

We arrive home and examine our food. My shoes looked more edible than the burger. There were two small, wilted pieces of lettuce hidden within the sauce. The bun tasted horrifying. It was just awful. The wife had a fish sandwich which was equally horrid. There was a thin, transparent film of tartar sauce on top of the processed fish wedge. The bun and breading managed to dominate over both the fish ans tartar sauce. That is just unnatural. How can baked and fried dough overpower bold sauce and fish that has endured months of storage and who knows what?

Ninety minutes have passed and the meal feels like a porcupine in my stomach. I am debating whether I should attack that wad of lard with a shot of hard liquor or just drown it in milk or send a salad into the battlefield of stomach acid. It is just awful feeling.

For the two or three people that read this blog once a month or year, I beg you not to go to this place.

Oracle MERGE, type 2 dimension

2010-02-25T21:41:00.001-05:00

I decided to look into this topic while learning SQL Server Integration Services and debating with my caffeine soaked brain if the slowly changing dimension task container would be more efficient than a T-SQL MERGE statement. I began wondering why the hell we were planning on shipping about 40 gigabytes per day out of Oracle into tables in SQL Server that are truncated (emptied) and rebuilt then query to determine what 2 kilobytes worth of changes took place that day. Why on earth would we want to stress out that multi-core, refrigerator sized server when an underpowered server and network and storage can do all the work?

Fact tables and dimensions are part of data warehousing. A slowly changing dimension is a dimension that slowly changes over time (duh). There are three types of slowly changing dimensions:

Type 1: Update the old values with the new values (do not give a crap about history)
Type 2: Create a new row in the dimension with a new primary (surrogate) key, and optionally update existing rows
Type 3: Update the old values with the new values, and add additional data to the table (date of the change, keep one level of history)

You would figure type 3 would come before type 2 but who cares..? This dimension uses a generated surrogate key for uniqueness (a sequence) and dates to keep track of when the row was effective with a NULL end date indicating this row is the current version of the foreign primary key value and all others contain historical data and attributes.

The same logic used to maintain slowly changing dimensions can also be applied to tracking historical changes to tables.

The code that follows assumes only additions and changes are taking place; after all nobody ever deletes data do they? The dimension table is simple, no clustering, no partitioning, bit-map indexes, or compression, and is purely for example:

CREATE TABLE crap_dimension
( dim_pk_surrogate  NUMBER
, dim_version_beg_date  DATE
, dim_version_end_date  DATE
, dim_pk_foreign  VARCHAR2(10)
, dim_description  VARCHAR2(60)
, dim_bulb  VARCHAR2(05)
, dim_sum  VARCHAR2(08)
)

The MERGE SQL statement is best used for inserts and updates. Here is the single statement used for the entire dimension update:

MERGE /*+ APPEND */
INTO crap_dimension m
USING (
--- Step 1  BEG :: Select new and changed data for the USING section of the MERGE
SELECT
  DECODE(v2.dim_upsert_level,1,v2.dim_pk_surrogate,0) AS dim_pk_surrogate
, v1.dim_pk_foreign
, v1.dim_description
, v1.dim_bulb
, v1.dim_sum
FROM (
--- Step 1a BEG :: Collect data from your OLTP or ODS or POS 
  SELECT
    crap_id AS dim_pk_foreign
  , crap_desc AS dim_description
  , crap_poop AS dim_bulb
  , crap_turd AS dim_sum
  FROM crap_oltp_or_ods_source
--- Step 1a END
--- Step 1b BEG :: Remove identical data attributes already in the dimension
  MINUS
  SELECT
  , dim_pk_foreign
  , dim_description
  , dim_bulb
  , dim_sum
  FROM crap_dimension
  WHERE dim_version_end_date IS NULL
--- Step 1b END
) v1
--- Step 1c BEG :: two rows are needed for updates, update existing and insert new data, LEFT JOIN cartesian product of 
LEFT JOIN (
  SELECT
    dim_pk_surrogate
  , dim_pk_foreign
  , dim_upsert_level
  FROM crap_dimension
  ,   (SELECT LEVEL AS dim_upsert_level FROM DUAL CONNECT BY LEVEL BETWEEN 1 AND 2)
  WHERE dim_version_end_date IS NULL
) v2 ON v2.dim_pk_foreign = v1.dim_pk_foreign
--- Step 1c END
--- Step 1  END
) u
ON (m.dim_pk_surrogate = u.dim_pk_surrogate)
--- Step 2  BEG :: If update, mark the end date
WHEN MATCHED THEN UPDATE 
  SET m.dim_version_end_date = SYSDATE
--- Step 2  END
--- Step 3  BEG :: Insert new data and the new versions of changed data
WHEN NOT MATCHED THEN INSERT
VALUES (
  dim_pk_surrogate_seq.NEXTVAL
, SYSDATE
, NULL
, u.dim_pk_foreign
, u.dim_description
, u.dim_bulb
, u.dim_sum
)
--- Step 3  END
;

I tried to break down the statement into steps.

Step 1a collects data from your ODS or OLTP and can be a simple query or a gigantic, complicated parallel query with a hundred joins and aggregate steps. The important thing to remember is we only want to do this expensive step once. When 1a completes we will have a fairly large result set.

Step 1b subtracts all identical data attribute rows that already exist in the dimension from the previous result set. This should significantly reduce the working set of rows at the expense of a full table scan through our dimension. When 1b completes the working set will contain new data and changed data from the source tables.

Step 1c creates data for updates. If data in the source has changed then two rows will be needed for the dimension; one row to update the existing data making the row historical and one row to insert for the new data making it the current row. The surrogate and foreign primary keys for all current rows are Cartesian joined to values 1 and 2 (one full table scan then hash join instead of two full table scans) and then left joined to the previous result set. New data from the source will not match data and will remain as one row with a NULL surrogate primary key. Changes from the source will match and join to the two rows in the query creating the two rows needed to do the update and insert. The surrogate primary key will be 0 for inserts (assuming the sequence started at 1 or higher) and valued for updates.

Step 1 finishes with rows containing changes to be applied to the dimension. Step 2 updates historical rows simply setting the end date to the current date. Step 3 inserts new data from the source and the new current version of the row for changed data.

More virus crap

2010-02-25T20:27:00.001-05:00

It had been a while since I went on a tour through the blogosphere using the next blog link at the top of the page. After stumbling through a lot of foreign crap in languages I did not understand I come to a blog that starts the hard drive going nuts. Oh F.

When the hard drive goes nuts you usually have a virus or something nasty trying to do something to your computer. AVG caught and probably stopped and cleaned the malicious crap. I was going to post some Oracle MERGE code but now I have to wait until a full system scan completes and bitch about things from my netbook.

So here I am watching Olympic curling (Canada versus Switzerland, ladies) and typing this on my netbook with cats jockeying for space on my lap and on the couch around me. I keep getting the same thought in my head: why the hell am I still blogging? Seriously. Why do I share code and my life with the world? Most of my friends have moved on to do their belly-aching in status updates and micro-blogging. Any therapeutic value from blogging can easily be replaced by a good beer or six (Sam Adams Winter Ale, I have a stockpile of it).

For the untrusted Internet I need to look into programs like Sandboxie or running web browsers inside virtual machines. When I purchase a new computer I will likely be running Linux which still has equal levels of threat, but it seems like fewer viruses are written to exploit Linux and it seems safer.

As I watch the women's Olympic ice hockey gold medal game I wonder if this will be my last blogger rant. Maybe I will start using Google Buzz and join others with one-line status updates like "my computer might be frakked" or "I really hate Microsoft again".

No time for stupid

2010-01-31T20:53:00.001-05:00

I try not to be in a hurry during most of my life. On the roads I try not to rush things especially in rough weather. When developing I try to do things right the first time instead of pushing something out and then dealing with the problems later. There is one instance, however, when I become angry; when my time gets wasted by stupid people.

There should be a gate with a short IQ test guarding the entrance of every self-scan lane at a supermarket. Tonight I picked the self-scan lane with the fewest people in it. One person with two small containers. That seemed reasonable enough. Not for this complete waste of human genetic material.

The first problem, this dude was eating out of one of the containers. After scanning the first, unopened container he places it on top of the machine instead of into the bag or at least onto the area that measures the weight of the item you just scanned. So he waits until the bagging error message comes up and presses the button to skip bagging.

Next, this idiot tries to scan the container he is eating out of. His fingers are sticky to begin with so he is already limited in dexterity. The bar code is on the side of the container so he then tries to angle the container so it will scan and not spill the contents. It never occurs to this idiot during the eight or nine attempts to scan the container to put the lid back on the container for the two or three seconds needed to scan the damn thing. Eventually, he does get the thing to scan. Does he put it in the bag? No, he puts it on top of the machine and waits the five to ten seconds for the scanner to yell at him so he can press the skip bagging button.

He then starts feeding money into the cash input as the machine repeatedly asked him if he was done scanning crap and was ready to start feeding the thing money. You are probably wondering where the cell phone comes in. It was about ten seconds before the machine locked up and required the cashier to come over and fix things. At least he did not stop screwing up the self-scan machine to talk on his phone but instead placed the phone on top of the machine and talked to it. However, he is now out of cash and wants to start feeding coins into a coil slot with tape on it and a large sign next to it stating the device cannot accept change.

He pouts for a moment and then barks at the cashier to come fix the machine. It takes the cashier about a minute or two to log in to the machine, reset it, eject the cash in the cash feeder and then set his whole transaction to be handled elsewhere to keep things moving. Thank you. What should take twenty seconds on a rough day takes more than five minutes as the lines at the 20 items or less and self-scan extend beyond the edge of the aisles (but are still moving).

I should not be worried about five minutes out of the (hopefully) millions of minutes in my life but I do let it get to me. I hate waiting for stupid crap like that.

Yahoo! sucks on my netbook

2009-12-29T21:04:00.002-05:00

I tried the new Yahoo! Mail today. I switched back to "classic" mail immediately. The first issue:

There seems to be a screen resolution problem. Your screen resolution is set below our minimum recommendation. When it's set under 1024 x 768 pixels, the all-new Yahoo! Mail won't look as good. You can continue with your current settings, but you won't be able to enjoy the full effect.

Really? My netbook has a 1024x600 resolution and I can go to 1024x768 with a scrolling desktop. The scrolling desktop is annoying and makes things unusable. Applications should scroll, not desktops.

The new mail does not render well in Opera. I use Opera because I think the scaling feature is superior to Firefox, IE and Chrome. It is nice to be able to size the contents of the web page to 70 to 90 percent while maintaining usability and readability. That is a nice feature when you have that 1024 x 600 netbook display. At 100 percent it looks like crap and does not get any better at zoomed levels.

I tried using Firefox and IE to see the full effects I should be enjoying. There are fixed frames and an annoying status bar, menu, message scroll, etc. I can see why they want a large fixed resolution.

I have been a Yahoo! mail user since 1998. That is over a decade of using Yahoo! One of the reasons why I liked Yahoo! was simplicity. While other web sites were going through the phases of hipness like massive image filled pages, Java applets, tons of unwanted JavaScript, excessive use of CSS and everything - Yahoo! kept most things simple. As the years passed, however, all of their services and applications got fatter. Messenger got fatter so I ended up using Gaim (Pidgin). Mail started getting fatter so I started using Google Mail more. Workstations are more powerful now than years ago and are able to handle newer technologies but it seems like some applications and web sites are forgetting that hardware using them are getting smaller or are coming from non-traditional sources (like game consoles and media devices).

Maybe I am just getting old.

(I also realize the reason for some of the new Web 2.0 stuff is for smaller devices like phones and hand-helds).

Oracle Data Pump for data refreshes part 4

2009-11-23T07:47:00.000-05:00

Part one, part two and part three.

This is a follow-up to the third part of this series with some real-world examples to make corrections and to justify some points.

I ran into a known 10g bug when attempting a DATA_ONLY export using parallel processing (ORA-00001: unique constraint () violated. The bug was fixed with 11g (Oracle forums). The source database was small so I did ran parallel=1 to get it to work. For larger databases that might not be feasible. The next test would be to modify the first export to get data and metadata and then do the DATA_ONLY on the first import step.

Other than the error above, the export and import for the first refresh went smoothly. The second import was more of a complete account copy do I could not use exactly what I specified in part three.

I did one export and one import. The export went quickly and without error. The import ground to a halt at the TABLE_STATISTICS step due to a bug in the Oracle Data Pump when schema and/or tablespace remapping is used. I had to terminate the import. Forums suggest using a EXCLUDE=STATISTICS in the parameter file to avoid these two incredibly slow steps and then rebuilding the statistics after the import (done through Enterprise Manager or whatever). The export of 57 GB of data took roughly 12 minutes. The import of that data took roughly 20 minutes for just the data. Source objects seemed to take a while and, as mentioned, the statistics took forever.

The data pump is a remarkable improvement over the previous import and export utilities. What took hours before can now be accomplished in less than an hour.

More to come.

Microblogging

2009-11-23T02:10:00.000-05:00

I made this comment on the soccer boards I occasionally frequent but it was a decent rant.

I dislike micro-blogs like Twitter and social web sites used like micro-blogs. Sentence fragments are replacing meaningful dialog. I get so tired of the cryptic crap like "so many things wrong with today." What? Did your cat die? Are you on the rag? Did you get a skunky beer? Is your boss licking your coffee cup while you sleep at your desk?

What?

It seems like every tweet or status update from a friend below the age of 27 is like this.

The problem with these sentence shortened cries for attention is that unless you are tight within the sphere of friends and know what is going on you are left to draw your own conclusions about what is really going on in their lives. This is especially true for public figures that post these dramatic little giblets about their lives leading to four or fifty different interpretations to what they actually meant. Twitter updates sometimes worse than the Bible for interpretation.

Why the drama?

I realize that I am not using the latest and greatest iDroid portable media server/game console/camera/asset manager/phone and probably do not understand what is really going on. I understand that text messaging is the cool thing to be doing right now and that these short cryptic messages are really some English sub-language I will never understand or whatever. But, if you have friends or people that you want to be up-to-date with every little detail of your life then are you really doing a disservice to friends left guessing about the seriousness of the situation? Yoda is a better conversationalist.

The Columbus Crew 2009, summary

2009-11-08T16:59:00.003-05:00

The Columbus Crew exited the major league soccer playoffs Thursday to Real Salt Lake. After winning the Supporter Shield for best record at the end of the regular season the reigning MLS champions lost and away match 0-1, a home match 3-2 and 4-2 on aggregate goals for the playoff series. Now that the sting has edged I feel able to comment on it.

My first thought is that I hate the first round of the MLS playoff system. The team with the best record plays away first and then comes back home for one game. Two games where the best team gets a home field advantage by playing the last game of the two games at home. To me that gives the advantage to the lesser record team because the better record team will be more likely to play defensively for a tie to take the series back home for the win or play defensively to not give up too many goals to take back home and score more goals. If the lesser team gets more goals in the first game then they hold a distinct advantage in game two because the better record team must score one more goal than they did leaving the lesser team to play a clogged defense and counter attack game. This is a moot point since the Crew lost both games. So what happened?

There are lots of opinions on what happened but I think it all boils down to one word: inconsistency.

First there was the inconsistent lineup due to injuries to key players like Schelotto, Hesmer and Marshall. Brunner, Iro, Zayner and O'Rourke stepped up with admirable performances in place of Chad Marshall. Problems with injuries and players not being at full performance complicated mid to late season matches that also included Open Cup and Champions League games. There were also games where players were called up to national teams causing complications in starting lineups.

Second, inconsistent performances. There are a lot of names in this list: Rogers, Hejduk, Gaven, Hesmer, Moreno, and even Schelotto in some ways. Defender Chad Marshall outperformed Moreno, Garey and Lenhart in point production. Moreno spent more time acting than scoring. He looked dangerous one time, the first game he came back from injury. Rogers had one goal in 1,700 minutes playing for the Crew.

Third, inconsistent coaching. Robert Warzycha took over the championship team from 2008 from Sigi Schmid. Some games made him look like a genius but in reality we got lucky. Revolving line-ups were forced by injuries, suspensions, and the introduction of Champions League play. They were also made by Warzycha. The 2008 Crew marched into the playoffs with the starting eleven players they were going to field the entire playoffs. Schelotto did not play the first game of the playoffs leading to questions about the team and the coaching staff.

So where does the Crew go from here?

Warzycha and the coaching staff need to explain their position but should also keep their jobs going forward to next season. There are people calling for Warzycha to be let go but who do we replace him with? Schelotto? If not Schelotto then who do we replace him with and who would we be able to afford?

Goalkeeping and defense has some interesting aspects to it. Hesmer is likely not going anywhere and stays in goal. Gruenebaum will likely be kept as a more than capable backup unless his trade value dictates otherwise. Marshall and O'Rourke were the central defense last season and they took us to a championship. Eric Brunner stepped up this season in central defense while Chad Marshall was injured and should be kept as a key part of the future. Unfortunately that leaves O'Rourke, a strong player out of luck and looking for a position. Gino Padula was just as strong this season as last so O'Rourke cannot occupy his position. On the right side, Crew heart and soul Frankie Hejduk began to show his age especially in the playoffs. Jed Zayner played adequately on the right side when Hejduk was out with injury or the national team and should probably, and unfortunately for Hejduk get the start in 2010 on the right side. This still leaves a strong player, O'Rourke, looking for a position. It also leaves international Andy Iro (England) on the bench and given Iro's 2009 performance was both good and slightly less than adequate he will be retained unless there is an opportunity to obtain better players by trading him.

The 2009 midfield was inconsistent due to injuries, national team call-ups, and coaching. The only consistent player was Brian Carroll who should be retained. Guillermo Barros Schelotto was the offense this season which is only good if other players are also contributing, which they were not. O'Rourke was used as a defensive midfielder this season but Adam Moffat returned in 2009 with both good and sub-adequate performances (again, inconsistency). On the outside Ekpo led the team with assists and had some key goals, Rogers had one fewer goals and assists than Ekpo with fewer minutes and games played. Eddie Gaven has six goals but no assists. This is an adequate contribution but leads to questions regarding who to start and keep for 2010. Crew fan favorite Duncan Oughton remained with the team in 2090 with limited action and as can be kept with the team as long as it is feasible to do so. The remaining midfield did not spend much time on the field and will likely end up as inconsequential players that can be waived, traded or kept.

The forward spots will likely endure the most scrutiny this season and for good reason. The bash brothers, Lenhart and Garey, showed a couple games of promise and dominance followed by barely adequate performances. This is my personal opinion but neither forward has provided a franchise worthy performance. Alejandro Moreno produced a couple game winning goals this season and spent time injured but he has earned a reputation as a flopper and most if not all referees in the league are not buying his acting. Since he is not scoring a large number of goals to offset this he becomes a liability and his position in 2010 will likely be as a bench player or not with the Crew at all. Emilio Renteria came aboard mid-season and had equal portions of bad and good in his performances. Both Moreno and Renteria are from Argentina and I would probably favor the younger Renteria going forward.

If I were asked to name the priorities for the 2010 season they would be: retain Schelotto for one more season as a player, acquire Schelotto's replacement so he can work alongside Guillermo and develop to lead the team. I do not think Gaven, Rogers, Ekpo, Moffat or any of the developmental players are those players so the Crew need to identify someone to fill that role from another MLS team or from another league. Next would be to retain Marshall, Hesmer, Carroll and Padula as they are all key players in the Crew roster. The next thing the Crew needs is a forward capable of playing with Schelotto (and his successor) and scoring a bucket load of goals. Ekpo, Brunner, O'Rourke and Zayner are young and show promise and value for the Crew future. Rogers and Gaven are both young but are both question marks (in my opinion) as both have the potential to create great value but have slumped during the 2010 season. Hejduk and Moreno should have their roles relegated. Last, Warzycha should be retained as coach unless an obvious and adequate replacement is both available and feasible for the organization.

Windows Home Server, part one

2009-11-08T14:20:00.003-05:00

I recently decided to take the plunge and invest in newer technology for our home server. I wanted a small, efficient system to replace the iDEQ mini computer running Windows Server 2003. I was favoring an Ubuntu Linux system running on a VIA green computing platform. Unfortunately, the VIA motherboard was too unstable to run Windows or Linux on. I gave up after swapping power supplies and memory; everything else is integrated into the motherboard. During that time the Intel Atom based hardware and Windows Home Server (WHS) appeared. Since Windows in some flavor will always be running somewhere in the house and WHS was getting favorable reviews I took the plunge and purchased an all-in-one unit with the WHS license.

The Hardware

I purchased an Acer easyStore H340 for $360 (before tax and shipping). It features the Intel Atom 230 processor, a Western Digital WD10EAVS 1 TB hard drive, 2 GB DDR2 memory and has 5 USB 2.0 ports, 1 eSATA port and a gigabit Ethernet port. The front door opens to reveal four SATA drive bays (the non-system drives are hot swappable). Windows Home Server (WHS) with Power Pack 1 comes installed and ready to finish on the TB disk drive. The McAfee Total Protection was installed but not active, sitting there waiting for me to activate the trial and then pay later.

The unit is smaller than any of the mini-computers I currently own. It is also quiet and unassuming and would quite at home in any basement likely near the router. There are indicator lights on the left side for power, drive and network activity and a multicolored status indicator light. Each drive has a multicolored status indicator light.

I added a WD10EADS 1 TB hard drive to the system. The drive trays are plastic and cheap feeling so I was careful with the tray I put the hard drive into and when I slid it back into place. It was recognized by the server and I added it to the storage pool without issue. There are close to 2 TB of storage in the pool.

After doing some reading I decided to purchase a 750GB hard drive but not add it to the storage pool. This drive will be used for bit torrent downloads, distributed updates and other high activity data. The 750GB was likely overkill but only $12 more than a 300 GB drive. When you do not add the drive to the pool the corresponding indicator light stays purple instead of a happy blue like the other drives in the pool.

I also purchased a Seagate 1TB hard drive and external enclosure to serve as a backup drive. I was able to hook up the external drive, dedicate it to backups and then make a quick backup of what was on our server.

The Software - Windows Home Server

Windows Home Server (WHS) came pre-installed on the Acer machine and almost ready to go upon start up. Connecting to the WHS begins with the connector software that comes on an accompanying disc. Shares are created for pictures, video, software, recorded TV programs, and for a public folder. WHS seems to prefer Windows authentication and having your user names and passwords synchronized across multiple computers. This was an issue for me because I had an older computer with one user name and most newer computers had my full name as the user name. I renamed the one account and broke a security license key for a piece of software I really do not use anymore. Still, one minor aggravation to play well with WHS.

Management is performed (or should be performed) through the Home Server Console. The console cannot be resized which is an annoyance for those of us that have netbook computers and limited vertical resolutions. Many options are compacted into an one panel and the administrator can expand the console and WHS through plug-in modules. Remote desktop can be used to maintain the server but with caution; anything specific to WHS should be managed by WHS and if you do things outside of WHS you can potentially screw up WHS.

I had problems removing McAfee software. I was not happy with Acer at this point. Why install trial software that nags you without providing a means for un-installing it? I rushed in and used remote desktop to connect and then remove the software with errors. I had to reboot the server to get the components to disappear from the console. I have not checked the registry or disks to see if the product was removed completely.

I have not used the workstation backup feature of WHS. The workstations I have have too much junk on them and I do not like the idea of my netbook being awakened in the middle of the night to have a backup performed. I prefer to have control over tasks like backups and updates on the netbook devices.

Another feature I have not used yet is the ability to put things on the Internet through WHS. Exposing a WHS to the world is scary without doing some reading first. I am happy right now with having the WHS closed to the rest of the planet. Since I am on that topic, how do you think ISPs are going to react to the idea of a technology adequate family putting a server on the Internet. Imagine what happens when someone on this planet figures out to hack the default implementation of WHS. Millions of potential zombie computers and personal data stores open to damage.

The primary goal of WHS is to centralize the protection, access, organization and storage of home data and services. This assumes at least one or more home workstations and portables are in use in the home (it would not make sense to pay for a license and have a server without one). These computers would be routinely backed-up to WHS. Media is to be stored centrally on the WHS server so multiple computers and other devices can get to it. So if everything is centralized for security and convenience then why are two important aspects of Windows not part of WHS.

Why no integration with Microsoft Security Essentials (MSE)?

MSE is free for XP, Vista and 7 users and regularly goes to the Internet for software and virus list updates. WHS coordinates the backup of devices on a regular schedule, why not centralize the security definitions and MSE software updates instead of having each home device connecting to the Internet to do so? Make a plug-in for MSE within WHS, which would download security definitions and updates to protect itself and provide them as a primary source for computers on the home network (the Internet being the secondary source if the WHS was unavailable or the computer were away from home).

Why no integration with Windows Server Update Services?

WSUS is also a free download but is geared more towards corporate environments. I have WSUS running on the current home server and it was nice to have a local copy of the 12 to 16 updates Microsoft just released on Tuesday. With WSUS the patches were downloaded once and then distributed locally to my two desktops in minutes. The same updates took roughly 40 minutes on my netbook, which is new and not currently pulling from the WSUS server. There are instructions on how to install WSUS on WHS available on the Internet but there is no support and no guarantees future updates to WHS would not cause a failure of WSUS and/or WHS for software not installed through plug-in. As with the security services, provide a plug-in for WHS to download and be the primary source for software updates for all local devices that can get to the WHS (leaving the Internet as the secondary source).

Summary

There will be follow-ups to this post as I start getting deeper into WHS and as more and more plug-ins and options become available.

New software

2009-10-29T15:38:00.001-04:00

A lot of recent software updates have appeared in the past 48 hours. The latest version of Ubuntu 9.10 (Karmic Koala) has been released. So download UKK or wait for the KKK (KDE/Kubuntu 9.10 Karmic Koala) if that is what you prefer. Opera released version 10.01 of Opera 10 browser with "turbo". "Neato". Mozilla released version 3.5.4 of Firefox 3.5 browser. Mozilla also released SeaMonkey 2.0; the all-in-one Internet application suite.

Get stuff:

Oracle Data Pump for data refreshes part 3

2009-10-29T12:34:00.002-04:00

Part one and part two.

This part gives you the UNIX scripts and SQL that seem to work best in the environments I work in. the standard disclaimers apply. This likely is not the best way of going about this (or it might be) and I provide zero support for this. If it works for you, fantastic; send me some beer. If it frustrates you to no end because most of it works but you constantly find little annoyances and Oracle manuals are absolutely no help; welcome to my world.

The following UNIX steps assume the user on the server has appropriate privileges to execute Oracle binaries and has the environment set correctly.

prod_expdp.sh

This shell script sets the ORACLE_SID environment variable and then executes the data pump export utility twice; once for data and once for sequence metadata.

#!/bin/sh
# prod_expdp.sh - run datapump exports for a complete refresh of PROD to TEST
#
ORACLE_SID=prod
expdp test_schema/testing PARFILE=prod_expdata.par
expdp test_schema/testing PARFILE=prod_expmeta.par

prod_expdata.par

The next two files are parameter files used by the export. The contents of the first file, for the data extract, is self-explanatory. The PARALLEL parameter was not used but included here because you will want some level of parallelism for optimal extract performance. The %U on the DUMPFILE parameter assigns a unique number to each extract file for parallel processing (4 files in this example). This parameter file should contain any EXCLUDE statements for tables that do not need to be exported.

DIRECTORY=test_dp_folder
DUMPFILE=prod_data_%U.dmp
LOGFILE=prod_data_exp.log
CONTENT=DATA_ONLY
SCHEMAS=test_schema
PARALLEL=4

prod_expmeta.par

The second parameter file is for sequence metadata. Why two exports? I have two reasons why, in my opinion, there should be two. The main reason was to separate metadata (small) from table data (massive). You cannot do a metadata and data import in the same import job without doing all objects and I did not want the second step processing a gigantic data extract file just to get the microscopic slice of metadata. Second reason, INCLUDE and EXCLUDE are exclusive; only one can be used per execution and most extracts will want to exclude table data (if any).

DIRECTORY=test_dp_folder
DUMPFILE=prod_meta_%U.dmp
LOGFILE=prod_meta_exp.log
CONTENT=METADATA_ONLY
SCHEMAS=test_schema
INCLUDE=SEQUENCE

Extracted files will need to be relocated to the test server.

test_impdp.sh

This shell script sets the ORACLE_SID environment variable, executes SQL*Plus to build some SQL, executes the data pump import utility twice - once for data and once for sequence metadata, and then executes SQL*Plus to do post import SQL and invalid object compiles.

#!/bin/sh
# test_impdp.sh - run pre/post SQL create SQL, datapump imports for a complete refresh of PROD to TEST
#
ORACLE_SID=test
sqlplus -s test_schema/testing @test_impsql.sql
impdp test_schema/testing PARFILE=test_impdata.par
impdp test_schema/testing PARFILE=test_impmeta.par
sqlplus -s test_schema/testing @test_imp_post.sql
sqlplus -s test_schema/testing @compile_invalid.sql
sqlplus -s test_schema/testing @compile_invalid.sql
sqlplus -s test_schema/testing @compile_invalid.sql

test_impdata.par

The data import parameter file is similar to the data export parameter file but with the extra TABLE_EXISTS_ACTION parameter instructing the data pump to truncate (empty) the table if it exists.

DIRECTORY=test_dp_folder
DUMPFILE=prod_data_%U.dmp
LOGFILE=prod_data_imp.log
CONTENT=DATA_ONLY
SCHEMAS=test_schema
PARALLEL=4
TABLE_EXISTS_ACTION=TRUNCATE

test_impmeta.par

The metadata import parameter file is self-explanatory.

DIRECTORY=test_dp_folder
DUMPFILE=prod_meta_%U.dmp
LOGFILE=prod_meta_imp.log
CONTENT=METADATA_ONLY
SCHEMAS=test_schema

test_impsql.sql

This SQL script is where all the magic happens. This is where all of the pain-in-the-ass, that is damned annoying, why oh why Oracle do you punish us you gigantic corporate fudge-packers, gets taken care of. This script dynamically builds two SQL scripts: a pre-import script and a post-import script. All enabled triggers and referential constraints (foreign keys) must be queries so they can be disabled and then enabled. Do not enable a constraint or trigger that was disabled; it was likely disabled for a reason likely by some developer behind your back. The script must also query all sequences so they can be dropped before import. The data pump import does not have a parameter or option to replace an existing sequence, so sequences must be dropped pre-import. Dropping the sequence causes two problems: privileges granted to those sequences will be lost and all source types containing the sequence will become invalid. The post-import SQL must grant privileges so this SQL script will query existing privileges (the schema owner must be able to query the all_tab_privs view).

SET FEEDBACK OFF TERMOUT OFF TRIMSPOOL ON VERIFY OFF PAGESIZE 0 LINESIZE 200
SPOOL test_imp_pre.sql
SELECT 'ALTER TRIGGER '||trigger_name||' DISABLE;' 
FROM user_triggers
WHERE base_object_type = 'TABLE' AND status = 'ENABLED'
ORDER BY trigger_name;
SELECT 'ALTER TABLE '||table_name||' DISABLE CONSTRAINT '||constraint_name||';' 
FROM user_constraints
WHERE constraint_type = 'R' AND status = 'ENABLED'
ORDER BY table_name;
SELECT 'DROP SEQUENCE '||sequence_name||';'
FROM user_sequences
ORDER BY sequence_name;
SPOOL OFF
SPOOL test_imp_post.sql
SELECT 'ALTER TRIGGER '||trigger_name||' ENABLE;' 
FROM user_triggers
WHERE base_object_type = 'TABLE' AND status = 'ENABLED'
ORDER BY trigger_name;
SELECT 'ALTER TABLE '||table_name||' ENABLE CONSTRAINT '||constraint_name||';' 
FROM user_constraints
WHERE constraint_type = 'R' AND status = 'ENABLED'
ORDER BY table_name;
SELECT 'GRANT '||privilege||' ON '||object_name||' TO '||grantee||';'
FROM user_objects
JOIN all_tab_privs ON table_schema = USER AND table_name = object_name
WHERE object_type = 'SEQUENCE'
ORDER BY object_name;
SPOOL OFF
@test_imp_pre.sql
QUIT

compile_invalid.sql

The generic SQL script will query invalid source objects (made invalid by the sequence being dropped), create SQL to compile those invalid source objects, and then execute that SQL. This generic script will need to be executed at least once but up to six times due to objects being invalidated when an object is compiled. For example: if procedure A calls procedure B that uses sequence C, both A and B will be invalid when the sequence is dropped. If A then B is compiled, A will be valid until B is compiled then will become invalid. The second execution of the compile script will compile A and since nothing depends upon A we are done.

SET FEEDBACK OFF TERMOUT OFF TRIMSPOOL ON VERIFY OFF PAGESIZE 0 LINESIZE 200
SPOOL compile_invalid_pass.sql
SELECT 'ALTER '||DECODE(SUBSTR(object_type,1,7),'PACKAGE','PACKAGE',object_type)||' '||object_name||' COMPILE'||DECODE(object_type,'PACKAGE BODY',' BODY;','PACKAGE',' PACKAGE;',';')
FROM user_objects
WHERE object_type IN ('TRIGGER','PACKAGE BODY','PACKAGE','FUNCTION','PROCEDURE') AND status = 'INVALID'
ORDER BY object_name, object_type;
SPOOL OFF
@compile_invalid_pass.sql

Sequence of Events

run export script
1. export table data
2. export metadata for sequences
move exported data to test server (if not on the production server)
run import script
1. run SQL to create pre and post-import SQL
  1. query referential constraints that need to be disabled
  2. query triggers that need to be disabled
  3. query sequences that need to be dropped
  4. query referential constraints that need to be enabled
  5. query triggers that need to be enabled
  6. query privileges to sequences that need to be granted on sequences that were dropped and then imported
  7. run the pre-import SQL
    1. disable referential constraints
    2. disable triggers
    3. drop sequences
2. import table data
3. import metadata for sequences
4. run the post-import SQL
  1. enable referential constraints
  2. enable triggers
  3. grant privileges on sequences to users
5. run compile SQL
  1. query for invalid objects
  2. compile source types made invalid after the sequence drop
6. run compile SQL
  1. query for invalid objects
  2. compile source types made invalid after the last compile
7. run compile SQL
  1. query for invalid objects
  2. compile source types made invalid after the last compile

You are probably thinking to yourself “holy fudging fudge, all of that bullfrog for a stupid little sequence?” I know I am. Hundreds of other individuals are thinking the same thing. There are other ways of handling sequences. Generate DDL for the sequence and then drop and create it; but that has the same effect as above (loss of privileges and invalid objects). Select MAX() and use that value to build SQL to drop and create it; same problems as above. Select MAX(), alter the increment by property of the sequence, get the next value, then restore the increment by property. The last two would require prior knowledge of the table(s) and column(s) using the sequence. One of our production databases is a vendor package that has over forty sequences so some up-front work would be required to match sequence to column to table. The last solution is cleanest because it does not require the recompilation of source objects or restoration of privileges. The whole issue could be avoided in the value of the sequence was considered a data object instead of a metadata object.

The next part will hopefully deal with the dirty details like how much undo/redo is generated, what about performance, etc.

Oracle Data Pump for data refreshes part 2

2009-10-21T16:00:00.001-04:00

In part 1 tables were created and populated with data. The following UNIX steps assume the user on the server has appropriate privileges to execute Oracle binaries and has the environment set correctly. We now use the data pump to export the whole schema:

ORACLE_SID=prod
expdp test_schema/testing DIRECTORY=test_dp_folder DUMPFILE=prod_full_%U.dmp LOGFILE=prod_full_exp.log SCHEMAS=test_schema

Get the dump files to the target server and then run the import:

ORACLE_SID=test
impdp test_schema/testing DIRECTORY=test_dp_folder DUMPFILE=prod_full_%U.dmp LOGFILE=prod_full_imp.log SCHEMAS=test_schema EXCLUDE=USER

The EXCLUDE=USER assumes the schema owner user already exists in the target database and avoids the annoying error.

The tables, data, constraints, triggers and sequences have been populated. One thing to notice, however: the NEXTVAL for the sequence is 141 and not the 124 from the production system. The reason for this is default CACHE value (20) used when the sequence was created. The sequence imported will always be a multiple of the CACHE setting plus the start value. If your application cannot deal with gaps in the sequence value then use the NOCACHE setting when creating the sequence otherwise every shutdown will introduce gaps and every refresh of test data will introduce gaps.

The next part will document how to refresh data.

CRUD with the Oracle MERGE statement

2009-09-28T11:23:00.002-04:00

Most of this post came about after a lengthy search of the clicker-web for information on Oracle materialized views. There are three or fifty situations at work where such a construct might be helpful to performance and overall health of the system (like when five different folks are running the same query over and over and over again).

Most situations that could benefit from a materialized view are "complex materialized views" according to Oracle manuals and guide (i.e. I am doing something more complicated than "hello world" in SQL). Others have documented that the materialized view update is really a MERGE behind the scenes. I am quite familiar with a MERGE and that was actually my first choice before I thought to myself "there is probably something already created for this complicated crap". Why recreate the square wheel, right? In this case the square wheel might be more efficient in the long run.

First, I will create a quick table and add some constraints to it:

CREATE TABLE table_full_of_crud AS
 SELECT
   LEVEL AS id
 , TRUNC(SYSDATE,'YEAR')+(LEVEL-1) AS some_stupid_date
 , CASE MOD(LEVEL,7) WHEN 0 THEN NULL ELSE TO_CHAR(TRUNC(SYSDATE,'YEAR')+(LEVEL-1),'DAY"," MONTH D YYYY') END AS varchar_crud
 FROM DUAL
 CONNECT BY ROWNUM BETWEEN 1 AND 120;
ALTER TABLE table_full_of_crud
 ADD (CONSTRAINT pk_table_full_of_crud  PRIMARY KEY(id))
 MODIFY (some_stupid_date NOT NULL);

Here is what I consider to be a well-formed, structured MERGE that will handle new rows, updated rows and rows that need to be deleted:

MERGE /*+ append */ INTO table_full_of_crud  m
USING (
SELECT
  NVL(v1.id,t1.id) AS id
, NVL(v1.some_stupid_date,TRUNC(SYSDATE)-12345) AS some_stupid_date
, v1.varchar_crud
FROM table_full_of_crud t1
FULL JOIN (
 SELECT * FROM (
   SELECT
     LEVEL AS id
   , TRUNC(SYSDATE,'YEAR')+(LEVEL-1) AS some_stupid_date
   , CASE MOD(LEVEL,20)
     WHEN 0 THEN NULL
     WHEN 7 THEN TO_CHAR(TRUNC(SYSDATE,'YEAR')+(LEVEL-1),'DAY"," MONTH D YYYY')
     ELSE TO_CHAR(TRUNC(SYSDATE,'YEAR')+(LEVEL-1),'MM/DD/YYYY')
     END AS varchar_crud
   FROM DUAL
   CONNECT BY ROWNUM BETWEEN 1 AND 160
  )
  WHERE MOD(id,69) > 0
) v1 ON v1.id = t1.id
) u
ON (m.id = u.id)
WHEN MATCHED THEN
 UPDATE SET
   m.some_stupid_date = u.some_stupid_date
 , m.varchar_crud = u.varchar_crud
 WHERE NOT(
   (m.some_stupid_date = u.some_stupid_date)
   AND DECODE(m.varchar_crud,u.varchar_crud,'EQ') IS NOT NULL
 )
 DELETE
 WHERE m.some_stupid_date = (TRUNC(SYSDATE)-12345)
   AND m.varchar_crud IS NULL
WHEN NOT MATCHED THEN
 INSERT (m.id, m.some_stupid_date, m.varchar_crud)
 VALUES (u.id, u.some_stupid_date, u.varchar_crud);

Here is a line by line explanation of all this crap:

The start of the MERGE statement, with append hint (direct path, etc.) INTO table; I use a table alias of "m" to indicate this is the table having data merged into it (makes sense)
USING; we will be using a subquery
SELECT (our subquery)
The NVL of the primary key is required due to the FULL [OUTER] JOIN; if the row needs deleted then the merge data primary key will be NULL, if inserted the original primary key will be NULL, if update neither will be NULL
The NVL of this column is due to the NOT NULL constraint and to use as our "delete trigger"; "delete triggers" should be a value that will never appear in the column
VARCHAR2 column
FROM the table that will be the target of the merge;
FULL [OUTER] JOIN is used to return the rows that exist in either table
SELECT the merge data; this subquery will contain new rows and rows that might need updating in the merge target table
data
data
data
data
data modify some rows to null values
data keep some rows the same
data modify the rest
data
data
data
data
data delete the 69th key value (remove it from the subquery)
data
End of using subquery; assign table alias of "u" (again, makes sense)
ON here is where (usually) primary key values are matched back to each other and influence the next two sections; what to do when things do or do not match
If the condition above evaluates to TRUE then the "WHEN MATCHED THEN" section is performed
UPDATE the target table
SET column value
SET column value
I use the WHERE clause of the merge update clause to determine if the row columns have changed or not; if they have NOT changed then why have the undo and redo overhead (???)
If a column cannot be NULL then a simple comparison will work
If a column can be NULL then DECODE can be used on both columns in the comparison
End of NOT(
DELETE from the target table
The WHERE clause check for the "delete trigger(s)" in (TRUNC(SYSDATE)-12345)
AND check for null columns (the FULL JOIN will produce NULL values in columns without corresponding rows in the merge data)
The "WHEN NOT MATCHED THEN" is performed when merge data cannot be matched to rows in the merge target
So INSERT (columns)
With new VALUES (from the merge subquery data)

The USING sub-query will almost always contain a complex piece of SQL to collect new and updated rows. The handle deletes of rows in the merge target table it needs to be joined to the query data with a FULL OUTER JOIN. If deletes are not needed, then the FULL JOIN is not needed. Here is a visual representation of the FULL OUTER JOIN:

ID1 X1   Y1     ID2 X2   Y2
1   data data   1   data data
2   data data   2   data data
3   data data   4   data data
4   data data   5   data data
5   data data   6   data data

After FULL JOIN

ID1  ID2  X2   Y2
1    1    data data
2    2    data data
3    NULL NULL NULL
4    4    data data
5    5    data data
NULL 6    data data

The non-primary key columns in rows that need to be deleted will be NULL. This can be used in the topmost section of the USING sub-query (via NVL) to create delete trigger data, which brings me to the DELETE mechanism.

The hardest part of the above statement was getting the DELETE clause to work. Reading the Oracle 10g Database SQL Reference helped somewhat; especially this point:

The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. That is, the DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted.

This means the DELETE does not use column data in the USING clause sub-query; very important. Some column in the UPDATE must be updated for the DELETE to work. To handle this set one or more columns to values that would never appear (in combination) in the data and use those values as a delete trigger.

I always add a WHERE clause to the UPDATE to determine if anything has changed. If nothing has changed then why generate the undo and redo data? Simply compare non-null columns and then null-evaluated columns to determine if data have changed.

The INSERT clause is the same as the INSERT statement we all know and love.

One discussion I came across on the Internet was about using a UNION ALL instead of the FULL OUTER JOIN. The UNION ALL performs better (one full index scan) than the FULL JOIN (two index scans and then a UNION-ALL). UNION ALL is a set operator, however, and updated rows would be duplicated (one unique row from the merge table and one from the query data). Something could probably be written but the FULL JOIN is simple, easy to maintain and worth whatever fractional overhead (compared to the actual merge) incurred.

Drugs, doctors, dumb gripe

2009-09-25T17:43:00.001-04:00

I heard the most interesting conversation outside the door on the examination room I was waiting in. I was there for what felt like an upper respiratory infection (or more likely remnants of) and subsequent examination. A pharmaceutical representative was talking to the doctor about a drug. A normal human would have been shocked by the conversation but with my current mindset I was able to shrug it off and not be surprised.

Why? This crap is everywhere. There is not a single organization or area that does not have these back door shenanigans.

Welcome to the world.

People pile poop on the bug drug companies and they deserve every last ounce but they also deserve credit for developing life saving and life enhancing medicines. What I find equally offensive are the people who poop on those pharmaceutical companies but support the natural, organic and alternative products. They are doing the same thing – making money off a product and potentially causing harm to individuals where medicine is needed to prevent illness or death.

What was said? Drug rep asks about drug. Doctor says there is too much conflicting information so he prescribes drug. If drug benefits patient he lets them stay on the drug. Drug rep wants to target a one year life cycle per patient for the drug. Other things were said. Mentions of the conversation getting on Internet were made. It was humorous to them.

A co-worker once said they have a drug to wake you up, a drug to keep you awake, to put you to sleep, a to make you happy, to make you sad, so you just do not care anymore, to get you hard, to make you stay hard, to make the pain go away and a drug to make you – well they have a drug for everything.

Bad mood music playlist

2009-09-21T21:11:00.001-04:00

My current "Bad Mood Music" playlist...

Exodus - "Deranged", from "Pleasures of the Flesh"
Overkill - "I Hate", from "The Years Of Decay"
Wrathchild America - "Surrounded By Idiots", from "3D"
Anthrax - "Antisocial", from "State of Euphoria"
Nailbomb - "24 Hour Bullshit", from "Point Blank"
Fight - "Kill It", from "War of Words"
GWAR - "America Must Be Destroyed", from "America Must Be Destroyed"
Stormtroopers of Death - "Speak English or Die", from "Speak English or Die"
Sacred Reich - "The American Way", from "The American Way"
Corrosion of Conformity - "Vote With a Bullet", from "Blind"
Megadeth - "Into The Lungs Of Hell", from "So Far, So Good... So What! [Remaster]"
Megadeth - "Set The World Afire", from "So Far, So Good... So What! [Remaster]"
Metallica - "Battery", from "Master of Puppets"
Death Angel - "The Ultra-Violence", from "The Ultra-Violence (Remastered)"
Pantera - "Mouth For War", from "Vulgar Display of Power"
Marilyn Manson - "The Beautiful People", from "Antichrist Superstar"
Disturbed - "Down With The Sickness", from "The Sickness"
Limp Bizkit - "Break Stuff", from "significant other"
Slipknot - "People = Shit", from "Iowa"
Static X - "I'm With Stupid", from "Wisconsin Death Trip"
Anthrax - "Chromatic Death", from "Attack of the Killer B's"
Sepultura - "Attitude", from "Roots"
Slayer - "War Ensemble", from "Seasons In The Abyss"
Exodus - "War is my Sheppard", from "Tempo of the Damned"
Pantera - "War Nerve", from "The Great Southern Trendkill"
Slayer - "Exile", from "God Hates Us All"
Suicidal Tendencies - "How Will I Laugh Tomorrow (Heavy Emotion Version)", from "Controlled by Hatred/Feel Like Shit"

Oracle Data Pump for data refreshes part 1

2009-09-21T16:00:00.002-04:00

This post will be the first in a multi-part post about the Oracle data pump utility. My goal for this series of posts is to (hopefully) provide a road map for Oracle database users to use the data pump utilities to refresh one or more test/development databases from a periodic snapshot of production data.

The data pump import and export utilities were added alongside the older export and import utilities in version10g of Oracle Database. These utilities can be used to make logical backups of metadata and data in databases from the entire database down to an individual table.

The first thing I noted when doing a snapshot of a production database at work is that when enough parallel processes are given to the data pump it smokes the pants off the previous utilities. What took hours now took minutes. That made me happy and I am sure my arteries will be happy without the 4am runs to White Castle while the export chugged along. Note, I have not done an import of production data yet and there is a reason for that – the unknown.

Like any good administrator I wanted to understand this new tool before I just started restoring data to databases. What about jobs? How do I handle sequences? Unfortunately the Oracle documentation is painfully lacking in that area. The 10g documentation for the old export has a section called "considerations when exporting database objects" that explains how to handle sequences, LOB columns, synonyms, etc. It also has a lengthier section on "considerations when importing database objects" which was also helpful. The 10g and 11g documentation has examples but lacks the depth of information the previous two utilities had. The 10g documentation has more useful information about the old utilities than it does about the fancy new ones and the 11g documentation for the data pump is about the same as the 10g data pump (11g has a legacy mode to support the old utility parameters, btw). This meant I had to set up a database to test the following items that could be affected by a data refresh:

constraints
referential constraints (i.e. foreign keys)
indexes
statistics
triggers
sequences

There are other objects like dimensions and quotas but those items will wait until a later time and date.

The first major difference between the old and new utilities is the use of DIRECTORY objects in the data pump utilities. The system DBA will need to create a directory object and grant privileges to it or use the default directory object named DATA_PUMP_DIR (I recommend creating your own so you have more control over who and what and how much).So as someone with the appropriate privileges:

CREATE USER test_schema IDENTIFIED BY "testing"
   PROFILE DEFAULT
   DEFAULT TABLESPACE users
   QUOTA UNLIMITED ON users
   ACCOUNT UNLOCK;
GRANT CONNECT,DBA TO test_schema;
CREATE OR REPLACE DIRECTORY test_dp_folder AS '/u69/oraldata/testdpump';
GRANT READ,WRITE ON DIRECTORY test_dp_folder TO test_schema;

Next create tables.

--- Time periods
CREATE TABLE time_periods
 AS
SELECT
  time_date
, EXTRACT(YEAR FROM time_date) AS time_year
, EXTRACT(MONTH FROM time_date) AS time_month
, EXTRACT(DAY FROM time_date) AS time_day
, TO_NUMBER(TO_CHAR(time_date,'d')) AS time_day_of_week
FROM (
  SELECT TRUNC(SYSDATE,'YEAR')+(LEVEL - 1) AS time_date
  FROM DUAL
  CONNECT BY ROWNUM BETWEEN 1 AND ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),36)-TRUNC(SYSDATE,'YEAR')
);
ALTER TABLE time_periods ADD (CONSTRAINT pk_time_periods PRIMARY KEY(time_date));
--- Customer table
CREATE TABLE customers
 AS
SELECT
  LEVEL AS customer_id
, 'Test customer #'||TO_CHAR(LEVEL,'000000000') AS customer_name
FROM DUAL
CONNECT BY ROWNUM BETWEEN 1 AND 100;
ALTER TABLE customers ADD (CONSTRAINT pk_customers PRIMARY KEY(customer_id));
--- Purchase order table
--- A sequence
CREATE SEQUENCE seq_pk_purchase_orders;
SELECT seq_pk_purchase_orders.NEXTVAL FROM dual;
--- Table with foreign keys
CREATE TABLE purchase_orders
( po_id  NUMBER
, po_date  DATE
, po_cust  NUMBER
, po_tag  VARCHAR2(64)
, po_tot_items  NUMBER
, po_tot_value  NUMBER
, CONSTRAINT pk_purchase_orders PRIMARY KEY(po_id)
, CONSTRAINT fk_po_date FOREIGN KEY(po_date) REFERENCES time_periods(time_date)
, CONSTRAINT fk_po_cust FOREIGN KEY(po_cust) REFERENCES customers(customer_id)
);
--- A trigger that modifies the PO tag
CREATE OR REPLACE TRIGGER tr_po_tag
  BEFORE INSERT ON purchase_orders FOR EACH ROW
BEGIN
  :new.po_tag := CASE WHEN :new.po_tag IS NULL THEN 'SGPO' ELSE 'CUST '||:new.po_tag END;
END;
/

The SQL is used to insert test data into the purchase orders table

INSERT /*+ APPEND */ INTO purchase_orders
SELECT
  seq_pk_purchase_orders.NEXTVAL
, TRUNC(TRUNC(SYSDATE,'YEAR')+DBMS_RANDOM.value(0,(SELECT COUNT(time_date) FROM time_periods)))
, TRUNC(DBMS_RANDOM.value(1,(SELECT COUNT(customer_id) FROM customers)))
, CASE MOD(r1,69) WHEN 0 THEN NULL ELSE DBMS_RANDOM.string('A',12) END
, CEIL(r2)
, CEIL(r2*123456)/100
FROM (
  SELECT LEVEL AS r1, ABS(DBMS_RANDOM.normal) AS r2
  FROM DUAL
  CONNECT BY ROWNUM BETWEEN 1 AND 123
) v1
;
COMMIT;

Life sucks sometimes

2009-09-08T21:12:00.001-04:00

You ever get to a point in your life where everything is getting better and you are beginning to feel happy about life and then you get smacked in the face? Well, I got slapped and punched and kicked and jabbed in the heart and spit upon all in the course of about a week.

I would love to share some opinions but to be blunt, it does not matter. The Internet is the wrong place to share such opinions.

Things are getting better and returning to normal, though. Hopefully that means I will have time to post some Oracle crap and some gripes about stuff.

Oracle 10g database authentication

2009-08-14T14:34:00.005-04:00

This is my attempt to get my collective knowledge of authorization in the Oracle 10g Database world in one location. The following steps and information assume you have access to a super-user (i.e. AS SYSDBA) and a database with a "USERS" tablespace.

First, create the schema owner user, read and update roles and then grant built-in roles with necessary system privileges to the users. Also grant the read role to the write role.

CREATE USER test_schema IDENTIFIED BY "t3sting"
 PROFILE DEFAULT
 DEFAULT TABLESPACE users
 QUOTA UNLIMITED ON users
 ACCOUNT UNLOCK;
CREATE USER test_user IDENTIFIED BY "t3sting"
 PROFILE DEFAULT
 DEFAULT TABLESPACE users
 QUOTA 5M ON users
 ACCOUNT UNLOCK;
CREATE ROLE test_READ NOT IDENTIFIED;
CREATE ROLE test_CRUD IDENTIFIED BY "supercomplicatedpassword";
GRANT CONNECT TO test_schema,test_user;
GRANT RESOURCE TO test_schema;
GRANT test_READ TO test_CRUD;

Next, connect as schema owner to create objects and then grant object privileges to them to the read and write roles

SQL> CONN test_schema
Enter password:
Connected.

CREATE TABLE test_table
  COMPRESS
  AS
SELECT
  TO_CHAR(time_date,'YYYYMMDD')||'.01' AS time_id
,  time_date
,  EXTRACT(YEAR FROM time_year_beg_date) AS time_year
,  time_year_beg_date
,  ADD_MONTHS(time_year_beg_date-1,12) AS time_year_end_date
,  EXTRACT(MONTH FROM time_date) AS time_month
,  TRUNC(time_date,'MONTH') AS time_month_beg_date
,  ADD_MONTHS(time_date-1,12) AS time_month_end_date
,  TO_CHAR(time_date,'Day') AS time_day_of_week
,  CASE time_date
    WHEN time_year_beg_date THEN 'New Years Day'
    WHEN NEXT_DAY(time_year_beg_date+30,'MON')+14 THEN 'Presidents Day'
    WHEN NEXT_DAY(ADD_MONTHS(time_year_beg_date,5)-1,'MON')-7 THEN 'Memorial Day'
    WHEN ADD_MONTHS(time_year_beg_date,6)+3 THEN 'Independance Day'
    WHEN NEXT_DAY(ADD_MONTHS(time_year_beg_date,8)-1,'MON') THEN 'Labor Day'
    WHEN ADD_MONTHS(time_year_beg_date,10)+10 THEN 'Veterans Day'
    WHEN NEXT_DAY(ADD_MONTHS(time_year_beg_date,10)-1,'THU')+21 THEN 'Thanksgiving'
    WHEN ADD_MONTHS(time_year_beg_date,11)+24 THEN 'Christmas'
    ELSE NULL
  END AS time_holiday_description
FROM (
  SELECT
     TRUNC(SYSDATE,'YEAR')+(LEVEL - 1) AS time_date
  ,  TRUNC(TRUNC(SYSDATE,'YEAR')+(LEVEL - 1),'YEAR') AS time_year_beg_date
  FROM DUAL
  CONNECT BY ROWNUM BETWEEN 1 AND ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),36)-TRUNC(SYSDATE,'YEAR')
);

Table created.

SQL> DESC test_table
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TIME_ID                                            VARCHAR2(11)
TIME_DATE                                          DATE
TIME_YEAR                                          NUMBER
TIME_YEAR_BEG_DATE                                 DATE
TIME_YEAR_END_DATE                                 DATE
TIME_MONTH                                         NUMBER
TIME_MONTH_BEG_DATE                                DATE
TIME_MONTH_END_DATE                                DATE
TIME_DAY_OF_WEEK                                   VARCHAR2(9)
TIME_HOLIDAY_DESCRIPTION                           VARCHAR2(16)

SQL> SELECT COUNT(*) FROM test_table;

 COUNT(*)
----------
     1095

GRANT SELECT ON test_table TO test_READ;
GRANT INSERT,UPDATE,DELETE ON ON test_table TO test_CRUD;

The test_user USER has CREATE SESSION system privileges so test_user can create a session and connect to Oracle and see PUBLIC objects. The test_user cannot see or access test_schema.test_table

SQL> CONN test_user
Enter password:
Connected.

SQL> DESC test_schema.test_table
ERROR:
ORA-04043: object test_schema.test_table does not exist

Authorization to schema objects can be granted: directly to the USER, indirectly through a ROLE, or through granted execute privilege on a source object (PROCEDURE, FUNCTION or PACKAGE) created with AUTHID DEFINER.

GRANT SELECT ON test_table TO test_user;

Privileges granted directly to the USER are recognized immediately

SQL> DESC test_schema.test_table
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TIME_ID                                            VARCHAR2(11)
TIME_DATE                                          DATE
TIME_YEAR                                          NUMBER
TIME_YEAR_BEG_DATE                                 DATE
TIME_YEAR_END_DATE                                 DATE
TIME_MONTH                                         NUMBER
TIME_MONTH_BEG_DATE                                DATE
TIME_MONTH_END_DATE                                DATE
TIME_DAY_OF_WEEK                                   VARCHAR2(9)
TIME_HOLIDAY_DESCRIPTION                           VARCHAR2(16)

SQL> SELECT DISTINCT time_holiday_description FROM TEST_SCHEMA.test_table;

TIME_HOLIDAY_DES
----------------
Veterans Day
New Years Day
Independance Day
Memorial Day
Presidents Day
Labor Day
Thanksgiving

8 rows selected.

As schema owner user revoke the privilege from the USER and then grant the read ROLE to USER test_user.

REVOKE SELECT ON test_table FROM test_user;
GRANT test_READ TO test_user;

*
ERROR at line 1:
ORA-01919: role 'TEST_READ' does not exist

Unless granted the privilege to do so, the schema owner USER cannot GRANT a ROLE to a USER; a super-user must do it.

GRANT test_READ TO test_user;

The ROLE privileges are not available until the USER reconnects or sets the ROLE

SQL> DESC test_schema.test_table
ERROR:
ORA-04043: object test_schema.test_table does not exist

SQL> SET ROLE test_READ;

Role set.

SQL> DESC test_schema.test_table
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TIME_ID                                            VARCHAR2(11)
TIME_DATE                                          DATE
TIME_YEAR                                          NUMBER
TIME_YEAR_BEG_DATE                                 DATE
TIME_YEAR_END_DATE                                 DATE
TIME_MONTH                                         NUMBER
TIME_MONTH_BEG_DATE                                DATE
TIME_MONTH_END_DATE                                DATE
TIME_DAY_OF_WEEK                                   VARCHAR2(9)
TIME_HOLIDAY_DESCRIPTION                           VARCHAR2(16)

SQL> UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day'
WHERE time_id LIKE '____1107%';
UPDATE TEST_SCHEMA.test_table
                  *
ERROR at line 1:
ORA-01031: insufficient privileges

The test_user USER cannot update test_schema.test_table without privileges. The test_schema USER granted the privileges to the test_CRUD ROLE so a super-user can GRANT that ROLE to test_user.

GRANT test_CRUD TO test_user;

Connect as test_user and try again.

SQL> CONN test_user
Enter password:
Connected.
SQL> UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day'
WHERE time_id LIKE '____1107%';

3 rows updated.

SQL> ROLLBACK;

Rollback complete.

Notice how test_user was able to update the table but did not need the password for the test_CRUD role? When a ROLE is granted to a USER or another ROLE it is as a DEFAULT ROLE and all default roles are applied when the session is created. The USER must be altered by a super-user to indicate what roles are default (or not default).

ALTER USER test_user DEFAULT ROLE ALL EXCEPT test_CRUD;

The test_user USER will not have the test_CRUD ROLE when it creates a session.

SQL> CONN test_user
Enter password:
Connected.

SQL> UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day'
WHERE time_id LIKE '____1107%';

UPDATE TEST_SCHEMA.test_table
                  *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> SET ROLE test_CRUD;
SET ROLE test_CRUD
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'TEST_CRUD'

SQL> SET ROLE test_CRUD IDENTIFIED BY "supercomplicatedpassword";

Role set.

SQL> UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day'
WHERE time_id LIKE '____1107%';

3 rows updated.

SQL> ROLLBACK;

Rollback complete.

Source types assume the privileges of the source type definer or invoker (definer rights and invoker rights). Create a PROCEDURE as USER test_schema:

CREATE OR REPLACE PROCEDURE set_special_day AUTHID DEFINER
AS
BEGIN
 UPDATE TEST_SCHEMA.test_table
 SET time_holiday_description = 'Special Day'
 WHERE time_id LIKE '____1107%';
 COMMIT;
END;
/
GRANT EXECUTE ON set_special_day TO test_user;

Reconnect as USER test_user. When the PROCEDURE is invoked the rights and privileges of the owner are used, not the current user (invoker). A schema owner by default has privileges to all objects created in their schema.

SQL> conn test_user
Enter password: *******
Connected.

SQL> EXEC TEST_SCHEMA.set_special_day

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT time_holiday_description FROM TEST_SCHEMA.test_table;

TIME_HOLIDAY_DES
----------------
Veterans Day
New Years Day
Independance Day
Memorial Day
Presidents Day
Special Day
Labor Day
Thanksgiving

9 rows selected.

Replace the PROCEDURE as USER test_schema but with AUTHID CURRENT_USER

CREATE OR REPLACE PROCEDURE set_special_day AUTHID CURRENT_USER
AS
BEGIN
 UPDATE TEST_SCHEMA.test_table
 SET time_holiday_description = 'Special Day 2'
 WHERE time_id LIKE '____1107%';
 COMMIT;
END;
/

Reconnect as USER test_user.

SQL> CONN test_user
Enter password: *******
Connected.
SQL> EXEC TEST_SCHEMA.set_special_day
BEGIN TEST_SCHEMA.set_special_day; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST_SCHEMA.SET_SPECIAL_DAY", line 4
ORA-06512: at line 1

There are no privileges granted directly or through default roles to the current USER, test_user. Grant all roles as default to USER test_user using a super-user:

ALTER USER test_user DEFAULT ROLE ALL;

Reconnect as USER test_user.

SQL> conn test_user
Enter password: *******
Connected.

SQL> EXEC TEST_SCHEMA.set_special_day

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT time_holiday_description FROM TEST_SCHEMA.test_table;

TIME_HOLIDAY_DES
----------------
Veterans Day
New Years Day
Independance Day
Memorial Day
Presidents Day
Special Day 2
Labor Day
Thanksgiving

9 rows selected.

The confusion for me was with AUTHID DEFINER. What if USER test_user were granted temporary privileges to create objects in the test_schema schema?

GRANT DBA TO test_user;

Now create the PROCEDURE as USER test_user using the recently granted built-in DBA ROLE:

SET ROLE DBA;
CREATE OR REPLACE PROCEDURE TEST_SCHEMA.set_special_day AUTHID DEFINER
AS
BEGIN
 UPDATE TEST_SCHEMA.test_table
 SET time_holiday_description = 'Special Day 3'
 WHERE time_id LIKE '____1107%';
 COMMIT;
END;
/

Now have super-user REVOKE all the fun roles from USER test_user:

REVOKE dba, test_CRUD FROM test_user;

Reconnect as USER test_user and try to execute the PROCEDURE:

SQL> CONN test_user
Enter password: *******
Connected.
SQL> EXEC TEST_SCHEMA.set_special_day

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT TIME_HOLIDAY_DESCRIPTION FROM TEST_SCHEMA.test_table;

TIME_HOLIDAY_DES
----------------
Veterans Day
New Years Day
Independance Day
Memorial Day
Special Day 3
Presidents Day
Labor Day
Thanksgiving

9 rows selected.

Confused? I was until I looked at the owner of the procedure.

SQL> SELECT owner, authid FROM dba_procedures WHERE object_name = 'SET_SPECIAL_DAY';

OWNER                          AUTHID
------------------------------ ------------
TEST_SCHEMA                    DEFINER

When defining source types the DEFINER for AUTHID DEFINER is really the USER that is owner of the schema where the source type was created; not the USER doing the CREATE. When the test_schema USER created the PROCEDURE it by coincidence was also the owner.

Long time, no gripe (cell phones)

2009-07-31T18:39:00.003-04:00

I meant to post this a little while ago so July would have something in it but I held off. As I age like wine into vinegar I do not feel like ranting as often. There is still the same amount of stupid in the world but if you cannot offer solutions or ideas to help resolve the issues you are ranting about then all of that typing and editing is mere therapy for your mind. It sure is not therapy for my hands; they will hurt more than my mind. However, one thing has repeatedly pissed me off for months now: cell phones.

First, let me rant about cell phone users that call or text while operating a vehicle. Pass the law now. I find very few reasons to have a conversation longer than 30 seconds while you are on the road. If you are on the phone more than a minute then pull over. This goes out to the:

dingbat on a cell phone that pulled out of the apartment complex in front of me causing me to throw my brakes and skid, that was one heart attack I did not need that early in the morning
idiot trying both text and talk while riding cowboy on a bicycle that was on a road with a 50 mph speed limit
douche bag that tailgated my fiancée for 30 minutes and then when traffic stopped abruptly got angry, pulled alongside her and proceeded to cuss her out while still on the damned cell phone
granny on I-70 driving 40 mph while on the cell phone as near collisions stacked up behind her
tool in front of me on the off ramp that was not paying attention when the light turned green forcing me to wait another light; my bladder wants revenge
dimwit not paying attention to the arrows in the Walmart parking lot that actually yelled at me for going the right direction but getting in her way (while on the cell phone the entire time)
bonehead at the merge close to where I live that used his yacht-sized vehicle to merge at the last second, without turn signal, less than an inch from clipping me
dilrods behind me, left of me and in front of me not paying attention, boxing me in, almost causing an accident

There are a few more but that is all I remember.

Second beef: cell phone commercials that portray other cell phone owners as gigantic pussies. Like that guy that goes crying in the rain as if his entire family were just fed to lions on the Serengeti. "He can't twitter, facebook or youtube." Oh, poor baby. My life is just over because I cannot tweet that I am on my phone standing in the rain somewhere because I am too stupid to find shelter. Boo hoo. Forty million children will starve today but I am in total anguish because I cannot get a status update from the teenager I am stalking on Twitter. Boo hoo. Then there is the family of losers complaining about how the other loser family members are using their cell phones to do crap they used to do on a computer at the same level of annoyance. OMG, Dad posts too much on Facebook. You poor suffering child. How awful your life must be. You are using a $100 cell phone inside a screened in California room while millions have lost their jobs and do not know if they will be able to feed their children in the future. Just awful; get that spoiled brat some Flinstones chewable Xanax, encase her in bubble wrap and roll her down a hill.

Last gripe (that I feel like typing about) – Bluetooth headset talkers that get upset because they think you are listening in on their conversation. You dumbass we have no choice but to listen to your conversation as you stand there and emote with your arms as you are talking and laughing and yelling and carrying on. I think the next time I am going to cuddle up in fetal position and just start yelling loudly "la la la la, I cannot hear you, la, la la la, la la la, not listening, la la la, trying to tune your annoying ass out, la la la."

I could go on but have probably alienated half of my audience and friends by now, although single digits have never bothered me in the past.

Solutions? I despise legislation but banning cell phone usage in vehicles except for emergency usage would be a start. Laws can be revoked or changed when they live out their usefulness. Commercials? Don't buy the product. Bluetooth blabber mouths? Maybe try to kill them with kindness and politeness and if that does not work then kill them with something else?

Afternoon at the library

2009-06-19T16:12:00.002-04:00

I really wish I knew how to use the 1.3 mega pixel camera on my netbook. I was at a public library today, killing an hour or two waiting until my sweetheart was off work. You know how libraries are supposed to be quiet places where people can read or study or blog without interruption and annoyance. Two problems with that.

The first is a teenage kid sitting about six feet from the sign clearly stating to refrain from using cellular phones and setting them to silent. He has been on the phone on the same call for fifty minutes while also playing on a Playstation portable. So it's "hmm. Yeah. Mumble mumble. Yeah. Umm. Mumble. The game... Yeah. Um. (voice on phone is louder than him at this point). Yeah. Yeah. Muh. Yeah. Mumble. Mumble. Broken English. Mumble. A'ight. Mumble. Yeah. Yeah. No. No. That wasn't me. Mumble. Mumble. She don't know. Laugh. Mumble. Ah yes. Yeah. No. Mumble. Mumble. Yeah. Uh huh. Nuh-huh. Yeah. Mumble mumble mumble. You get bored. Mumble. I can have that. Blah blah blah. Heh heh heh." over the clackity-clack of whatever he was playing.

Second was the one year old bouncing off everything like a pinball and squealing at the top of his/her lungs. There is no parental guidance to be found. The kid will make a pretty good running back when he or she grows up.

"Mumble. Yeah. A'ight. I just wanna go. Yeah. Yeah. Mumble mumble. Yeah. Umm. Mumble. Yeah. Um. No. Yeah. Yeah. Muh. Yeah. Mumble. Mumble. Broken English. Mumble. A'ight. Mumble. Yeah. Yeah. No. No. That wasn't me. Mumble. Mumble. Whatever she do is okay. Mumble. Yeah."

I am wondering to myself. If I grab the lamp next to me and proceed to bash this individual's skull in would a) the police be called and I hauled off to jail for murder, b) I be loudly applauded until the librarian shushes everyone, or c) I silence the entire building. I can live with the last two. So those odds are 66/33 that I will be in a happier state after clubbing this dude. Tempting.

Finally. After the 64 minute mark the douche bag with the phone hung up and left. The child is getting tired and cranky but quieter if you can believe that.

Sleep

2009-06-10T01:23:00.000-04:00

I have trouble getting to sleep. I have had this problem for years. It was never a problem until my new neighbors moved into the apartment behind mine and then began having sex at all hours of the night and morning. There is nothing worse than awakening from a decent sleep to the sound of a dog whimpering in heat. The situation changed as I began living with someone. She could fall asleep in minutes while it took me sometimes up to an hour. Of course, she snored. It was hard to sleep with an outboard motor next to me so I would simply escape to the relative silence of the couch with the nearby aquariums. I tried numerous things to help me get to sleep faster, here are the results.

Over-the-counter sleep aids do not work very well with me. Most are just a double dose of Benadryl (diphenhydramine). If it did work, great. If it did not or I was awakened during the night I would spend hours in a haze.

I tried herbal supplements like melatonin and Valerian root. Neither supplement helped me get to sleep. The valerian sometimes had the same effect the diphenhydramine would have. I tried Valerian in capsule form and in Sleepy Time Extra tea. I also tried zinc supplements via tablets and also through ZMA. There were no long term effects.

I tried limiting and eliminating caffeine. That would make a short term difference but nothing would happen long term (more than a couple days). Caffeine intake did not affect how long it took to get to sleep. I also tried varying my exercise and as with the caffeine, no long term changes.

I tried turkey, eating a banana, and drinking a protein shake. None of them worked.

I tried reading before bed and that did not work.

I tried different pillows including full sized, wheat husk and memory foam but there was really no change.

I tried laying flat on my back on a hard floor. I tried cooling my wrists under running water.

There was only one thing that seemed to have benefit: background noise. I had a noisy desk fan I would use to provide background noise. My girlfriend at that time hated that fan. There were times where the fan would be louder than normal or would make squeaking noises as it got older. I would have to take it apart and clean in. My current love of my life has a white noise generator that we use on the “night” setting to get a constant sound of crickets in a field or something like that.

My fiancee also has problems getting to sleep. She has taken Ambien (Zolpidem), Lunesta (Eszopiclone) and ?. She had a prescription for the Ambien and samples of the Lunesta and suggested that I try both and see what happens.

Both drugs were very effective. I could get to sleep regardless of noise from snoring or a television. I stayed asleep, and was able to awaken in time for work.

The Ambien was too effective. Apparently I received an alert on my work phone at around three in the morning, got up, acknowledged the message and then went back to sleep. I do not remember that ever taking place. Once on Ambien I could not get off Ambien without having a really bad night or two or five. Since I was borrowing from my fiancee's prescription and not remembering alerts from work I had to take the Ambien off my list.

The Lunesta was not addictive and I was able to leave the drug, have trouble falling asleep as usual, but still get sleep. The Lunesta left an awful taste in my mouth but I prefer sleep over funny tasting water any night. At this time, there is no generic equivalent for Lunesta.

I went to my physician and had him write a prescription for the Lunesta. I have been taking the Lunesta for a week now in half doses (cutting the pill in half). Sleep has been more than adequate so the drug is doing the job I am asking it to do. Eventually, I want to only use the drug from time to time when I need it (usually Sunday nights). Our feline inhabitants have slightly altered sleeping arrangements in the Grouchy household so the couch and spare bedroom are not realistic options. Eventually the spare bedroom will be available but until then I will probably continue taking the Lunesta when I feel I will need to.

The Oracle schema owner user

2009-06-09T15:00:00.000-04:00

Most of this blog post is based upon this late 2006 post by Ponder Stibbons. I took those ideas and ran with it.

The usual disclaimers apply. I assume you know what a schema is and what a schema owner is. I also assume you know a little bit about Oracle SQL and Database. This post is based release 10g and will be tested on 11g. All SQL in this post can be freely used, modified, claimed as your own, whatever. All SQL here should be used as a guideline and not used verbatim.

The first steps require SYSDBA or adequate rights and create components used for all schema owners including roles, a profile and perhaps a tablespace.

/*
 * Step 1: create table space in non-ASM server (you have probably already done this)
 */
CREATE SMALLFILE TABLESPACE crap_data
 LOGGING
 DATAFILE
  '/u02/oradata/crapdb/CRAP_DATA1.dbf' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 5000M
 , '/u03/oradata/crapdb/CRAP_DATA2.dbf' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 5000M
 , '/u04/oradata/crapdb/CRAP_DATA3.dbf' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 5000M
 , '/u05/oradata/crapdb/CRAP_DATA4.dbf' SIZE 500M AUTOEXTEND ON NEXT 250M MAXSIZE 5000M
 EXTENT MANAGEMENT LOCAL
 SEGMENT SPACE MANAGEMENT AUTO;
/*
 * Step 2: create a profile
 */
CREATE PROFILE schema_owner_profile
  LIMIT CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL UNLIMITED
  CONNECT_TIME UNLIMITED
  IDLE_TIME UNLIMITED
  SESSIONS_PER_USER UNLIMITED
  LOGICAL_READS_PER_SESSION UNLIMITED
  LOGICAL_READS_PER_CALL UNLIMITED
  PRIVATE_SGA UNLIMITED
  COMPOSITE_LIMIT UNLIMITED
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_GRACE_TIME UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_VERIFY_FUNCTION NULL;
/*
 * Step 3: create roles
 */
CREATE ROLE schema_owner_role NOT IDENTIFIED;
CREATE ROLE schema_admin_role NOT IDENTIFIED;
-- or
CREATE ROLE schema_admin_role IDENTIFIED BY "30charactercomplexpassword";
/*
 * Step 4: grant privileges to both roles
 */
GRANT CREATE SESSION TO schema_owner_role;
GRANT ALTER SESSION TO schema_owner_role, schema_admin_role;
/*
 * The bare minimum for a schema administrator: the ability to create source and
 * user types that use the "invoker rights clause"
 */
GRANT CREATE PROCEDURE TO schema_admin_role;
GRANT CREATE TYPE TO schema_admin_role;
/*
 * Additional privileges
 */
--- Scheduler
GRANT CREATE JOB TO schema_owner_role, schema_admin_role;
--- Convenience
GRANT CREATE TABLE TO schema_admin_role;
GRANT CREATE CLUSTER TO schema_admin_role;
GRANT CREATE VIEW TO schema_admin_role;
GRANT CREATE SEQUENCE TO schema_admin_role;
GRANT CREATE TRIGGER TO schema_admin_role;
--- Data warehousing
GRANT CREATE MATERIALIZED VIEW TO schema_admin_role;
GRANT CREATE DIMENSION TO schema_admin_role;
--- Data cartridge
GRANT CREATE OPERATOR TO schema_admin_role;
GRANT CREATE INDEXTYPE TO schema_admin_role;
--- Restrict synonyms to database administrators ?
GRANT CREATE SYNONYM TO schema_admin_role;

The first step in the block creates a tablespace. It is usually a good idea to give each schema its own tablespace with a similar name but not necessary. The statement will look different if you are using ASM or different mount points (of course). Some articles suggest using the "USERS" tablespace and others suggest using common tablespaces. Use whatever your organization has determined to be best practice.

Step two creates a profile for all schema owner users. Ponder's post recommends not using built-in or Oracle delivered stuff. I agree. If it comes from a vendor, do not use it; copy it and then tailor it to your specific needs.

The third step is to create a role. I chose to create two roles: a default role for all sessions and an administrative role used only for administrative tasks. To do administration on objects in the schema would then require the administrative role to be set before constructive or destructive changes took place. The downside to this approach could prohibit certain GUI tools. The upside is slightly better security and one additional barrier to careless individuals that occasional accidently screw something up repeatedly. The default role is needed to create a session and might also be helpful if you are using the Oracle scheduler and defining jobs that run as the schema owner (more on that later).

The last step (four) is to grant privileges to the two roles. The default role needs to create a session. In my opinion, the roles should be able to alter their sessions (this is a potential risk). The administrative role should have the ability to create procedures and create types. Most source and user types can be defined with an "invoker rights clause" that allows the package to execute using the privileges of the user that defined the source or object or using the privileges of the user executing or using the source or object. If doing security through stored procedures, AUTHID DEFINER is usually used when defined by the schema owner to allow the procedure full DML access to any object in the schema (and then execute privileges are given to user roles). Additional privileges are for convenience and the scheduler so define what you think is needed.

Creating schema owner users is a three step process from this point forward (assuming all use the same tablespace). Create the user, grant the roles, and then set the default role.

/*
 * Create the user, grant roles, set "owner" role to default
 */
CREATE USER crapbase
 PROFILE schema_owner_profile
 IDENTIFIED BY "30charactercomplexpassword"
 DEFAULT TABLESPACE crap_data
 QUOTA UNLIMITED ON crap_data
 ACCOUNT LOCK;
GRANT schema_owner_role TO crapbase;
GRANT schema_admin_role TO crapbase;
ALTER USER crapbase DEFAULT ROLE schema_owner_role;
/*
 * Create roles the application might use to access data in the schema
 */
CREATE ROLE crapbase_read_role NOT IDENTIFIED;
CREATE ROLE crapbase_full_role IDENTIFIED BY "30charactercomplexpassword";

The first step is to create the user. Use the profile created previously. Set the default tablespace and give unlimited quota on that tablespace else the schema owner will not be able to define objects that require persistence like tables. Most documents recommend leaving the account locked and then unlocking it when maintenance is to be performed.

The second step is to grant the two roles to the new user.

The last step is to alter the user so the administrative role is not a default role and must be set to obtain those privileges.

One optional step would be to create roles used for read only and full access to objects in the new schema. Individual users would be granted these roles as default or not as default and then the application would set the role(s). Instead of read and full, there could be a more elaborate role structure based upon your organizational requirements.

Oracle Scheduler

A schema can have jobs defined to it in addition to tables, views and procedures. Jobs can be created by the schema owner or by a user with CREATE ANY JOB privileges. Jobs created by the schema owner user or by other users in the schema are owned by the schema owner user. Jobs execute with the privileges of that user. The schema user account can be locked and jobs will continue to execute. In one of our environments, the default role has the CREATE JOB privilege so a job can create another job based upon data in other tables.

Security

The schema owner user should have a strong password that is changed regularly and should be locked unless needed to define a source or object type with AUTHID DEFINER invoker rights. If someone gets in as schema owner they have full control over all data in that schema. Strict roles should be used to limit access to schema data. Users with administrative roles should be used to create tables, indexes, etc. within the schema.

Grouchy gripe day today

2009-06-05T06:39:00.003-04:00

I have a couple things I would like to get off my chest this morning.

One. I cannot stand the pseudo techno-intellectuals that make statements based on superior hardware and then assume everything applies downward. Windows 7 runs great on my Intel Core i7 Extreme Edition with 12 GB of DDR3 1866 memory and two Western Digital Raptors in a RAID 0 configuration. No ****? Startup time is fantastic. Well, it should be. You have hardware that God has to wait in line for and everything runs smooth and performance is fantastic. Just because your newest version of Windows or Linux runs incredibly fast on your mega box it does not mean it will perform just as well on a more standard machine given a one or two year upgrade time frame.

Two, I cannot stand web sites that cram every unnecessary Web 2.0 component on their home pages. What works well on your developer workstation with its Core 2 Quad Extreme with 16GB of memory might not work as well on my AMD 754 socket processor and 1 GB of memory or my netbook with its hamster sized screen, Atom processor and 2GB of memory. I just drug my parents kicking and screaming out of the Windows 98 world where processors were named by number and their megahertz clock speed into the land of Windows XP and processors named after sissy, woodland creatures. They do not give a rat's ass if your tag cloud rotates or if that multi-gigabyte, centralized news / ad / feature thing in the middle of your page looks good. They would be more interested in a dancing baby or Jesus.

Sorry. Today I offer zero solutions, only gripes.

Building a time dimension with SQL

2009-05-27T21:02:00.007-04:00

The time dimension can be found in the majority of star-schema based data warehouses. There are literally dozens of ways to generate the values in the table at the lowest level of the dimension.

The easiest way is to simply plug the contents of the table into a spreadsheet and then import the spreadsheet into a table. Seriously... Why write all the complicated code when you can manually put it all in, show it to users, let them bitch about it, make changes, fix their frak-ups, and then cram the data into a table. The table is not built on an hourly/daily/monthly basis like other dimensions. However, if you wish to use SQL (Oracle) then maybe I can help.

If at any point during this conversation you the reader notice room for improvement, please comment.

The Table

My personal preferences for time dimensions:

define columns only for the basic values and values that are too expensive to calculate within a view, do not define columns that can be easily calculated with date math or TO_CHAR functions within a view
do not bother with partitioning or logging
use table compression

Most time dimensions I see include columns for day of month, end of week, day in year, etc. My reasoning behind storing only the base values and complex values and then doing columns like day in year in a view is two-fold. First is I/O reduction. I would rather do the blocked read of two or three blocks and some CPU rather than do five to ten blocked reads. Second is flexibility. If someone is crawling up your ass for another group of columns so their horribly inefficient DSS actually looks feasible the nerd simply adds the column to the view. Regardless of what you store in it, the time dimension is small compared to other dimensions and the fact table so why bother with partitioning overhead. The table can be recreated so use NOLOGGING. Compression will reduce the size by about thirty percent leading to fewer I/O requests at the expense of CPU. I always want CPUs working.

Creating Values

MERGE /*+ parallel(time_dimension,4) append */
INTO time_dimension m
USING (  -- Step 7: use the record set as part of the merge
  WITH period_sf AS ( --- Step 1 - factored subquery for the various periods from operational databases
   SELECT
    'Craptastic' AS period_id
   ,  TRUNC(SYSDATE,'YEAR') AS period_beg_date
   ,  TRUNC(ADD_MONTHS(SYSDATE,12),'YEAR')-1 AS period_end_date
   FROM dual
   WHERE 1 = 1
  )
  , distinct_years_sf AS ( --- Step 2: factored subquery for all distinct years
    SELECT DISTINCT TRUNC(period_beg_date,'YEAR') AS year_beg_date
    FROM period_sf
    UNION
    SELECT DISTINCT TRUNC(period_end_date,'YEAR') AS year_beg_date
    FROM period_sf
  )
  SELECT  -- Step 6: select base values for the date, values that will enter the time dimension
    TO_CHAR((v1.year_beg_date + v2.day_num),'YYYYMMDD')||'.01'  AS time_id
  , v1.year_beg_date + v2.day_num  AS time_date
  , v3.period_id  AS time_period_id
  , v3.period_beg_date  AS time_period_beg_date
  , v3.period_end_date  AS time_period_end_date
  , v1.year_beg_date + v2.day_num - v3.period_beg_date + 1  AS time_period_day
  , CASE (v1.year_beg_date + v2.day_num)
      WHEN v1.year_beg_date THEN 'New Years Day'
      WHEN v1.year_presidents_date THEN 'Presidents Day'
      WHEN v1.year_easter_date THEN 'Easter'
      WHEN v1.year_memorial_date THEN 'Memorial Day'
      WHEN v1.year_independance_date THEN 'Independance Day'
      WHEN v1.year_labor_date THEN 'Labor Day'
      WHEN v1.year_veterans_date THEN 'Veterans Day'
      WHEN v1.year_thanksgiving_date THEN 'Thanksgiving'
      WHEN v1.year_christmas_date THEN 'Christmas'
      ELSE NULL
    END AS time_holiday_text
  FROM ( --- Step 4: generate values for each 
    SELECT
      year_id
    , year_beg_date
    , year_end_date
    , CASE
        WHEN year_id BETWEEN 1885 AND 1970 THEN year_beg_date + 52
        WHEN year_id BETWEEN 1971 AND 9999 THEN NEXT_DAY(year_beg_date + 30,'MON') + 14
        ELSE NULL
      END AS year_presidents_date
    , CASE
        WHEN year_id BETWEEN 1900 AND 2199 THEN NEXT_DAY(year_end_date - 244 + easter_value + (CASE WHEN easter_value < 5 THEN 17 ELSE -12 END),'SAT') - 34 + (CASE year_id WHEN 2079 THEN 7 ELSE 0 END)
        ELSE NULL
      END AS year_easter_date
    , CASE
        WHEN year_id BETWEEN 1882 AND 1970 THEN year_end_date - 215
        WHEN year_id BETWEEN 1971 AND 9999 THEN NEXT_DAY(year_end_date - 214,'MON') - 7
        ELSE NULL
      END AS year_memorial_date
    , CASE
        WHEN year_id BETWEEN 1882 AND 9999 THEN year_end_date - 180
        ELSE NULL
      END AS year_independance_date
    , CASE
        WHEN year_id BETWEEN 1894 AND 9999 THEN NEXT_DAY(year_end_date - 122,'MON')
        ELSE NULL
      END AS year_labor_date
    , CASE
        WHEN year_id BETWEEN 1954 AND 1970 THEN year_end_date - 50
        WHEN year_id BETWEEN 1971 AND 1977 THEN NEXT_DAY(year_end_date - 92,'MON') + 21
        WHEN year_id BETWEEN 1978 AND 9999 THEN year_end_date - 50
        ELSE NULL
      END AS year_veterans_date
    , CASE
        WHEN year_id BETWEEN 1863 AND 1938 THEN NEXT_DAY(year_end_date - 31,'THU') - 7
        WHEN year_id = 1939 THEN NEXT_DAY(year_end_date - 61,'THU') + 21
        WHEN year_id = 1940 THEN NEXT_DAY(year_end_date - 61,'THU') + 14
        WHEN year_id BETWEEN 1941 AND 9999 THEN NEXT_DAY(year_end_date - 61,'THU') + 21
        ELSE NULL
      END AS year_thanksgiving_date
    , CASE
        WHEN year_id BETWEEN 1870 AND 9999 THEN year_end_date - 6
        ELSE NULL
      END AS year_christmas_date
    FROM ( --- Step 3: calculate a few values for the years in the second step
      SELECT
        EXTRACT(YEAR FROM year_beg_date) AS year_id
      , year_beg_date
      , ADD_MONTHS(year_beg_date,12) - 1 AS year_end_date
      , TRUNC(MOD(EXTRACT(YEAR FROM year_beg_date) / 38 * 1440,60) / 2) AS easter_value
      FROM distinct_years_sf
    )
  ) v1
  JOIN ( --- Step 5: generate numbers 0 through 366 and join to the years
    SELECT (LEVEL - 1) AS day_num FROM DUAL CONNECT BY ROWNUM BETWEEN 1 and 367
  ) v2 ON v2.day_num BETWEEN 0 AND (v1.year_end_date - v1.year_beg_date)
  LEFT JOIN period_sf v3 ON (v1.year_beg_date + v2.day_num) BETWEEN period_beg_date AND period_end_date
  ORDER BY time_id
) u
ON (m.time_id = u.time_id)  --- Step 8: update or insert (i.e. MERGE)
WHEN MATCHED THEN UPDATE
  SET
    m.time_date            = u.time_date
  , m.time_period_id       = u.time_period_id
  , m.time_period_beg_date = u.time_period_beg_date
  , m.time_period_end_date = u.time_period_end_date
  , m.time_period_day      = u.time_period_day
  , m.time_holiday_text    = u.time_holiday_text
WHEN NOT MATCHED THEN INSERT
  VALUES
  ( u.time_id
  , u.time_date
  , u.time_period_id      
  , u.time_period_beg_date
  , u.time_period_end_date
  , u.time_period_day     
  , u.time_holiday_text
  )

Most time dimensions will have primary units within periods or intervals of time. Sales might have sales periods. Colleges might have semesters. Television might have quarters and sweeps weeks within them. The source for these periods could be one or more tables in an OLTP database or ODS or perhaps already in the data warehouse if using a star-snowflake hybrid schema. Each source should be a factored sub-query containing the period primary key and the dates the period starts and ends. Oracle will create a temporary table for each sub-query. For this example, I chose one period based upon the current year (table dual is available on every Oracle database).

The second step is to generate a record set from all period sub-queries with distinct years (the first date of each year). The sub-query is also factored because it could be used more than once to generate record sets based on individual years.

The next few steps create data specific for each year in the above sub-query. I do this to avoid doing repetitive calculations for all 365 or so days in each year (do you really want the server calculating when Easter occurs for each day or just do it once for each year; yeah I thought so).

Next, each year is joined to a day number, 0 through the end of the year.

Each factored period sub-query that will have values in the time dimension is then LEFT JOINed to the years plus days result set. The left join is required because there might not be overlaps in the various periods. For example: a set of sales periods existed before, during, and then after a corporate acquisition.

The columns from the result set are fed into the USING part of the MERGE statement. The results are either inserted into or used to update the time dimension. The /*+ APPEND */ hint is used to trigger a direct-path insert for table compression and the /*+ PARALLEL */ hint is optional but could speed up the process depending upon your server.

The above example is very, very simplistic. There is an entire data warehousing language and concepts documentation library that I have yet to digest. I am also certain there are better ways of doing the same thing I have done in the SQL above. I could have also used PL/SQL and loop constructs to build the dimension table. To be honest, there are more important things in life.

Feel free to comment on my SQL brain fart. Suggest improvements. Steal the code and call it your own, basking in the glory.

Yahoo messenger connection refused

2009-04-22T08:23:00.003-04:00

It seems like there are other folks that have noticed connections to Yahoo Messenger are getting a "connection refused" error. I get errors in the morning all the time. Using advice from this page, I tried hard coding an IP address in place of the scs.msg.yahoo.com and it connected first time. This is not a long term solution by any means. The post also suggested doing an ipconfig /flushdns from a Windows command prompt. But as with other errors I am simply spreading the word.

Jackets... Playoffs...

2009-04-21T22:44:00.001-04:00

To quote myself from Bigsoccer:

After that first period and those two strange goals it is clear this just is not meant to be...

I don't even hear Bill Murray from Caddyshack saying "Cinderella story. Outta nowhere. It looks like a mirac... It's in the goal! It's in the goal! It's in the goal!"

All I have in my head now is that "Bag of Weed" song from Family Guy this Sunday.

The very first playoff game in Nationwide Arena history was over in one minute. With enough electricity in the building to power the cities of Columbus and Detroit what was the worst thing that could happen? A Detroit goal about one minute into the game? How about another Detroit goal at the end of the first period? I am certain the City of Columbus street cleaners are probably happy. The vacuum created by all the air being let out of the arena was probably powerful enough to suck the dirt and trash off streets in a four block radius around Nationwide.

The lowest point of the evening had to be the attempted retaliation for the brutal hit by Stuart on R.J. Umberger. Commodore flops into the Detroit bench after trying to make a hit then proceeds to push a Redwing over and watch Cleary (a Redwing) handling the puck behind the goal. The Jacket player guarding the net leaves to play the puck and Cleary leaving Zetterberg wide open for the third goal as Rick Nash and Mike Commodore literally stand there and watch mere feet away from play. It would have been a sin if there was no retaliation for knocking R.J. into a game five (which is looking like it will never be played) but what the players on the ice did was even worse. They looked like staggered drunks trying to retaliate and let one more goal in while doing so.

Game four better feature one or more of the following:

a hard, legal hit that leaves a first or second line player on the Detroit side flat on the ice (extra points for the leg twitch)
a goal by Rick Nash, heck I would settle for a crushing blow by Nash on someone at this point
a good, legal check on a Redwing by at least six of the following players: Russell, Peca, Huselius, Chimera, Williams, Modin, Vermette, Nash, Voracek

The team needs to exceed the physicality shown toward it, not match it.

Spinning wheels

2009-04-06T16:15:00.001-04:00

Have you ever had one of those days where you are doomed to spinning your wheels working on a problem?

I have been working on improving one of our database triggers to do a single MERGE statement instead of opening two cursors, doing an insert and then doing an update of the same record. I kept getting a PL/SQL: ORA-00969: missing ON keyword compilation error. Can I not LEFT JOIN something to DUAL? Can I not use a :new.column variable within a function in that MERGE? I tried the usual reworking of the statement a half dozen times.

I tried a Google search.

Most arguments argued that it was just one record and to keep things simple and just do the update (or insert) and then handle the error to perform the other DML event. Yeah, it is only one record and the extra statement parse overhead is negligible in the grand scheme of things. Then again, I am an old mainframe system geek that remembers every little bit counts when dealing with high volume processing. Back in those days shortening an eye-catch in working storage by a couple bytes could end up saving 4K in a C.I.C.S. page. Do that for a couple dozen programs and you might end up increasing response time because of fewer space compressions. I see that methodology in practice; making things easy for the application developer at the expense of performance. It is a fight that is being lost to the get it done quickly, take credit, and make yourself and your resume look good crowd.

Then again, I could have used a code generator or some drag and drop development environment and it might have saved me a couple of hours of work by adding the required parenthesis around the condition on the ON clause before the merge update and merge insert clauses. I could have saved a couple hours of my valuable time. It is all about finding a good balance between doing what is best for performance and sustainability of hardware versus making our lives easier.

Jackets? Playoffs?

2009-04-02T12:22:00.001-04:00

The past two times I happened to be wearing my Columbus Blue Jackets shirt when going out to eat for lunch I have been asked if they are going to make the playoffs. They are definitely not in yet but are in fairly good shape. So I sat down during lunch to figure things out.

First, the win against Nashville was huge.

There are 5 games and 10 points left in the season. To be completely safe from the Anaheim Ducks and Nashville Predators the teams 7 of the remaining 10 points. At 6 points we would probably be edged by the Ducks because they (would) have more wins. To be completely safe from the St Louis Blues sitting at 9th, the team needs 6 of the 10 points. Columbus could probably do it with 5 points but would have to win more games because a tie in games won would give the edge to the Blue who have a better head to head record against Columbus. To be safe from the Wild and Oilers the teams needs 4 of the 10 points, but could do it with 3 points. This assumes these five teams win all of their remaining 5 games and get the full 10 points.

Here are our remaining games:

Apr 4 at Predators; loss
Apr 5 vs Blackhawks; win
Apr 8 at Blackhawks; loss
Apr 10 at Blues; loss
Apr 11 vs Wild; win

I think we will manage 4 points in the next 5 games. Sneaking out with a point in an overtime loss in any of the 3 away games would be great. The Blues have games against the ailing Coyotes, Stars and Avalanche and their last home game of the season against Columbus. They won't get points in Detroit unless the Wings have a brain fart like the last time Columbus visited. So 8 points to the Blues. The Predators will likely get 4 points at home and at least 1 in their 3 away games; so let's say 5 points. The Ducks have 2 games against the conference leading San Jose Sharks but I think they will get 1 point from the home/home melee. They will probably get 2 points against the Coyotes and Stars giving them 5 points.

So:

6th Jackets 88 + 4 = 92
7th Ducks 84 + 5 = 89
8th Predators 84 + 5 = 89
9th Blues 83 + 8 = 91

6th Jackets 92 points
7th Blues 91 points
8th Ducks 89 points
9th Predators 89 points, miss playoffs

We aren't in yet but I think the team will make the playoffs for the first time in franchise history.

ORA-01839 using an INTERVAL

2009-03-31T15:18:00.003-04:00

One of our application developers had a program that was been active and usually working for years but for some reason does not work issuing a cursor not open error every once in a while. It has been a source of anger for a while because the application will magically start working a day later without any changes to the application or host system. The source of the problem: an error occurred at cursor open due to date arithmetic using an INTERVAL and that error was not handled.

Shame on someone for not handling the error.

Regarding the INTERVAL logic, if you add or subtract a month interval from a date that results in the day being outside the number of days in the month (going from March with 31 to April with 30 for example) the statement will throw an error.

The developer is better off using the Oracle function ADD_MONTHS which does not throw an error. If the application needs a value of the first of the month if 31 days then the application should probably be subtracting days instead of months. If the warranty expires in 90 days then add 90 days. If the warranty expires in three months then unless there is some complex logic in the contract it should end on June 30 and not July 1 (just assuming).

SELECT SYSDATE FROM DUAL;
SYSDATE
---------
31-MAR-09

SELECT SYSDATE - INTERVAL '2' MONTH FROM DUAL;
SYSDATE-I
---------
31-JAN-09

SELECT SYSDATE - INTERVAL '4' MONTH FROM DUAL;
ORA-01839: date not valid for month specified

SELECT ADD_MONTHS(SYSDATE,-4) FROM DUAL;
ADD_MONTH
---------
30-NOV-08

The INTERVAL logic has been discussed elsewhere including a comment from Tom Kyte about the pure ANSI implementation. I document it here to spread the word and hopefully keep others from banging their heads into a frothy pulp over silly crap like this. Standards; gotta love them.

Recession garden

2009-03-30T06:16:00.001-04:00

I do not remember which station I heard this on but their news teaser mentioned something about what to do for your "recession garden".

A what?

So because the United States is in a recession I am supposed to grow my own food in my own personal recession garden?

A what?

Do these people know how hard it is to maintain a garden large enough to supplement the store bought food supply a family requires to survive? Do they know how expensive it is? Hmm, I just lost my job and I am tapping my cash reserves to stay inside my house and keep my family fed. Wait, I got it. I will make a recession garden.

First I have no problem whatsoever with home gardens. If you truly want fresh tomatoes and other vegetables right out of the ground or off the vine you really cannot beat a home garden. Well that is not true. There are plenty of local farmers with farm markets where all sorts of freshness are available. I would rather help keep them in business because they serve a greater need.

Second, do these people live in Ohio? Residential soil is better suited for making pottery than growing things. You will likely need to borrow or rent a machine to grind up and process the soil and more than likely you will need to purchase a decent amount of top soil to grow stuff in.

Lastly, seed and chemicals for growth and parasite control are not free. Sanitization will also be required. Are there stray cats in the neighborhood? If so, you just built them another litter box so be prepared for that.

What is next? Recession Chia herb gardens? Recession barn so we can raise our own meat?

ORA-01578 and related heart attacks

2009-02-25T16:00:00.004-05:00

Holy ****! We get this heart attack inducing error message in our production system:

ORA-01578: ORACLE data block corrupted (file # 4, block # 1510490)
ORA-01110: data file 4: '/u16/app/oracle/oradata/database/main_effin.dbf
ORA-26040: Data block was loaded using the NOLOGGING option

Within seconds we are hitting Metalink and Google and Oracle forums and Burleson Consulting's sites and opening up service requests. It's not looking good. We call friends. "I've never seen that one before!" F***! We're so f*****. We consider running dbv. We consider preparing for a full restore. We data pump the table into a file on a different file system. We drop the table. We recreate the table. S**!, error when trying to create the table from the import. Drop the table again. Import the table. Both of us have to pee so bad we could extinguish an active volcano.

The Oracle finally calls back.

No problem. We see this a number of times each month.

Here's the deal.

The table and index are set to full redo logging but the CLOB in that table is not. A couple months ago we had to do a full restore of our production database. This part gets confusing because it's being related second hand but the block got marked as LOGGING because of the restore even though the LOB options are set to not generate full REDO logging. When someone tried to update that table Oracle as a safety measure marked the block as read only until measures were taken to check it. The "corruption" in this case wasn't corruption as in your data is fooggegated and there is some crap lost somewhere. The best thing to do is data pump export, drop, data pump import (what we were trying to do while freaking out before calm, cool, collected Mr. Oracle called). He also explained that generating REDO for LOBs is painfully expensive so there is a case for NOT generating REDO for them if the data are insignificant and/or can be recreated.

So here I am, bladder emptied. It felt so good it feels like I'm still taking that leak. My heart rate has returned to close to normal.

I really need to win the lottery...

Project: Oracle 11g on Linux

2009-01-12T13:23:00.001-05:00

I recently found some inner motivation to go back an restart some personal computing projects at home. Maybe it was the time off from work today or I was tired of looking at high powered machines collecting cat litter dust. I'll keep this main post then add comments as I go. When I get something worth consolidating I'll repost

The Project

Install 64 bit Oracle 11g database on Linux. Get working two working installations: one with and then one without Automatic Storage Management (ASM). Learn ASM and Recovery Manager (RMAN).

The Hardware and Software

The project machine:

AMD 64 X2 3800+ CPU
4 x 1 GB DDR2 RAM (4 GB total)
4 x Seagate ST380013AS 80GB SATA150 drives
a Seagate ST3400633A 400GB ATA drive in an external USB enclosure.

I chose openSUSE 11.1 64bit as the operating system because SUSE Linux is one of the distributions supported by Oracle and their Oracle on openSUSE document and accompanying PDF files seemed the most solid place to start.

Configuration

Oracle requires and/or recommends swap space equal to physical memory for installations with 2GB to 8GB of memory. I figured 4 SATA disks with 1GB swap files would be good. I did some research and there are too many arguments for and against multiple small versus one large swap file; so I took the most painful route of four times the work. The rest of disk one is root which will contain /opt and /u01 for software and initial databases. For the first round I used the rest of the disk space for /u02, /u03 and /u04.

The Chinese buffet

2009-01-07T20:15:00.001-05:00

The girlfriend and I have made a few trips to a Chinese buffet recently. Those eateries are a wonderful example of what is wrong with people and why both of us stay hidden from society as a whole.

One of the easiest groups of people to get upset at are the wasteful slobs that pile a plate full of food, eat maybe half of it, then go back for another overflowing plate of mostly the same stuff still on the original plate. If the goal is to sample everything then take reasonable portions of stuff you are not sure if you will like, more of stuff you do like. The same ideals apply to those who want to feed until gorged and no longer mobile under their own locomotion. Americans waste an ungodly amount of food every year; enough to make world hunger probably go away or diminish to the level of "now what were you complaining about again?"

Then there are the food thieves. They are the ones that will secretly package and hide food in their garb or handbags during the consumption process. One for my gut, one for my purse. Two for my gut, one for my handbag. I understand it is about getting your money's worth but walking into a buffet with a Gucci wallet then walking out looking like you were about to go camping or were just enlisted into the military is another story.

For some strange reason a growing number of groups of people believe the buffet gives them a pass to exclude all manners and parenting from their public pie-hole stuffing. The other night there were two youths texting over their cell phones; so the missus and I had to endure twenty minutes of phone sound effects at the highest volume. Why? A group of loud, obnoxious cretins have waddled forth from their front porch couches to continue their "oh so important" 24 hour cell phone conversations. Yet if I were to go over, politely ask them to shut their noise holes and turn their ringer volumes down from a level that can be heard from space, I'd probably be shot. If I were to make Swiss cheese of their skulls, I'd go to jail after a thunderous round of applause. Also included in that group are the parents that just let their kids run feral like they were at a McDonalds. It's not a McDonalds. I understand the lack of oxygen due to not taking adequate breaths during your face stuffing might be part of the problem but when the floor looks like the badlands of a World War 1 trench war then you should be in jail and your kids should be in a compound somewhere.

Another group of people that aggravate me are the people that go to eat only crab legs. If you have seen them in action, you know who I am talking about. They usually travel in groups of two and ask to sit as close or with a direct line of sight to the pan containing the crab legs. The first thing these sloths do is empty that pan of crab legs. When the employees bring out a fresh pan of crab legs the stop what they are doing, swallow what is in or within the orbit around their mouths, then go back up to empty the pan of crab legs. Why by courteous and leave a few legs behind when you can shovel the entire catch onto your plate then shuffle back to the safety of the pile of shell carcasses and litter surrounding your table. I always pray that there will be two competing tribes of crab leg mooches so I can take humor from the situation as they jockey for position and circle the other buffet foods with an empty plate waiting for the crab legs to come out. It is like some sick game of musical chairs, just with food. It makes me feel a little better to think that a group of hardened men in Alaska didn't risk their lives in the dangerous, freezing oceans so two cellulite filled meat bags could gorge themselves on the cheap.

When I go to the Chinese buffet it is usually because I want a cheap variety of Oriental cuisine. I love steamed dumplings, green beans, and the California rolls with wasabi and ginger slices. I usually get a little bit of everything. I leave a clean plate. I eat quietly. If I see someone eyeing a dish I'm in the process of emptying I'll ask if they would like some or wait until a fresh batch comes out. I usually don't let the masses get to me but the last trip to the buffet just didn't sit right with me; too much rudeness and waste.

Oracle date goodies in SQL/PLSQL

2008-12-04T14:30:00.004-05:00

Originally published 03/12/08...

I have been searching through Google, Yahoo! and various forums to create a small library of useful functions and crud for Oracle PL/SQL and SQL. I was either unhappy with the solutions I found or simply thought I could do them better, differently or somewhat the same.

Leap Year

(3-EXTRACT(MONTH FROM TRUNC(date_column,'YEAR')+59)) -- returns 1 if a leap year, 2 if not; from DATE datatype
(3-EXTRACT(MONTH FROM ADD_MONTHS('01-JAN-0001',numeric_year_column*12)-307)) -- returns 1 if a leap year, 2 if not; from NUMBER datatype
DECODE(EXTRACT(MONTH FROM TRUNC(date_column,'YEAR')+59),2,'True','False') -- substitute VARCHAR2 values for the result

Holidays

CREATE OR REPLACE FUNCTION is_holiday(i_date DATE) RETURN VARCHAR2 IS
 v_date DATE;
 v_y NUMBER;
 v_q NUMBER;
BEGIN
 v_date := TRUNC(i_date,'Y');
 v_y := EXTRACT(YEAR FROM i_date);
 v_q := TRUNC(MOD(v_y / 38 * 1440,60) / 2);
 RETURN CASE i_date
  WHEN v_date THEN 'New Years'
  WHEN NEXT_DAY(ADD_MONTHS(v_date,1) - 1,'MON') + 14 THEN 'Presidents Day' --- Third Monday in February
  WHEN NEXT_DAY(ADD_MONTHS('01-JAN-0001',v_y * 12 - 8) + v_q + (CASE WHEN v_q < 5 THEN 17 ELSE -12 END),'SAT') - 34 + (CASE v_y WHEN 2079 THEN 7 ELSE 0 END) THEN 'Easter' --- valid 1900-2199 only
  WHEN NEXT_DAY(ADD_MONTHS(v_date,5) - 1,'MON') - 7 THEN 'Memorial Day' --- last Monday of May
  WHEN ADD_MONTHS(v_date,6) + 3 THEN 'Independance Day'
  WHEN NEXT_DAY(ADD_MONTHS(v_date,8) - 1,'MON') THEN 'Labor Day' --- first Monday in September
  WHEN ADD_MONTHS(v_date,10) + 10 THEN 'Veterans Day'
  WHEN NEXT_DAY(ADD_MONTHS(v_date,10) - 1,'THU') + 21 THEN 'Thanksgiving' --- fourth Thursday in November
  WHEN ADD_MONTHS(v_date,11) + 24 THEN 'Christmas'
  ELSE NULL
 END;
END;
/

The above function appears to be fairly accurate. I tested 2000-2008 for all holidays and 1900-2199 for Easter (finding only one glitch for 2079 manually corrected with a CASE statement). The above function could prove handy if you have a time dimension in your data warehouse (who doesn't) and use PL/SQL to generate your data.

Easter

Uses the Meeus/Jones/Butcher Gregorian algorithm

FUNCTION GET_EASTER2_FN(i_year NUMBER) RETURN DATE IS
 v_g  PLS_INTEGER; -- the Golden number; sequence in the 19-year lunar cycle
 v_c  PLS_INTEGER; -- the Christian era i.e. century
 v_y  PLS_INTEGER; -- the year number in the Christian era
 v_e  PLS_INTEGER; -- the epact; days in excess of the solar year over the lunar year
 v_l  PLS_INTEGER; -- the ?
BEGIN
 v_g := MOD(i_year,19);
 v_c := TRUNC(i_year / 100);
 v_y := MOD(i_year,100);
 v_e := MOD((19 * v_g + v_c - TRUNC(v_c / 4) - TRUNC((v_c - TRUNC((v_c + 8) / 25) + 1) / 3) + 15),30);
 v_l := MOD(32 + 2 * (MOD(v_c,4) + TRUNC(v_y / 4)) - v_e - MOD(v_y,4),7);
 RETURN ADD_MONTHS('22-MAR-0001',(i_year - 1) * 12) - TRUNC((v_g + 11 * v_e + 22 * v_l) / 451) * 7 + v_e + v_l;
END;

Format Independant TO_DATE

FUNCTION TO_DATE_FN( i_datechar VARCHAR2 ) RETURN DATE IS
  v_datechar VARCHAR2(32000) := UPPER(TRIM(i_datechar));
BEGIN
  RETURN CASE TRANSLATE(NVL(v_datechar,'N/U/L/L'),'0123456789/-ABCDEFGJLMNOPRSTUVY','##########/-^^^^^^^^^^^^^^^^^^^')
    WHEN '^/^/^/^'     THEN NULL
    WHEN '########'    THEN TO_DATE(v_datechar,'YYYYMMDD')
    WHEN '######'      THEN TO_DATE(v_datechar,'MMDDYY')
    WHEN '##/##/##'    THEN TO_DATE(v_datechar,'MM/DD/YY')
    WHEN '##/##/####'  THEN TO_DATE(v_datechar,'MM/DD/YYYY')
    WHEN '####-##-##'  THEN TO_DATE(v_datechar,'YYYY-MM-DD')
    WHEN '##-^^^-##'   THEN TO_DATE(v_datechar,'DD-MON-YY')
    WHEN '##-^^^-####' THEN TO_DATE(v_datechar,'DD-MON-YYYY')
    ELSE TO_DATE('01-JAN-2989','DD-MON-YYYY')
  END;
END TO_DATE_FN;

More to come...

MLS 2008 Champions

2008-11-30T23:11:00.001-05:00

Your 2008 champions of Major League Soccer: the Columbus Scruh, ... Crew.

What is more embarrassing? Having the commissioner of the league you just won a championship in come one letter short of calling you the "screw" on national television or having that same commissioner try to present the trophy to captain Guillermo Barros Schelotto when the captain was actually Frankie Hejduk; the guy strategically placed next to the cup for ease of transfer?

Suck a big one, Don.

Classic fail from the league head chimp aside, winning the 2008 MLS championship capped off a wonderful season that was a long time coming. There were a lot more fans that deserved this championship more than I. I can only offer praise to the groups that stayed with the team throughout the worst of the worst to finally get to greatness. I wasn't there for every game nor did I watch every game on television or at bar with a tab full of expensive finger foods and beer.

List of team accomplishments:

Took home the Trillium Cup; the league promoted distance rivalry between Columbus and Toronto, by beating them at home, then earning two draws on the road
Took control of the Supporter's Shield for having the best record at the end of the season
Defender of the Year for Chad Marshall
Coach of the Year for Sigi Schmid
Most Valuable Player for Guillermo Barros Schelotto
Took home the 2008 MLS Cup for defeating Kansas City on aggregate goals, the Chicago Fire 2-1 at home, and then the New York Red Bulls 3-1 in Carson City
MLS Cup MVP for Guillermo Barros Schelotto for directly assisting on all three goals

I made sure I picked up a copy of the Columbus Dispatch to tuck away somewhere as a memory for my later years. It would be nice to pick up a photoset, too.

Some consider the celebration short-lived. An expansion draft was held the next Thursday and starter Brad Evans went to Seattle. There is talk that defender of the year Chad Marshall is heading overseas (more cash and higher level of play) and that coach of the year, Sigi Schmid is heading to Seattle to be closer to family on the West Coast and to get a longer-term contract (three years) to make it easy to settle somewhere. A sizable chunk of cash will be required to sign Guillermo Barros Schelotto for another year or so. There are developmental players that will be waived. There will be veterans that will probably be asked to step down.

Regardless of these developments, 2008 was a triumphant year for the Columbus Crew. Nothing can take that away.

Web 2.blow, Oracle.com version

2008-11-20T13:11:00.005-05:00

Go to this Oracle University link with the various web browsers you have. Here is what you will see. In IE7 it renders well and you can get into the various tabs for schedules and details. In Firefox on Windows it operates but looks different. Unfortunately, it breaks on the sixth page of the checkout too but you won't know that unless you register, book a course, enter a purchase order number, enter names of five siblings you promise to sacrifice, etc. In Opera, well, it just pukes the whole mess up at the top of the screen and is totally unusable. If you set site options to mask as Netscape/Firefox it works.

Now I know I'm vastly in the minority here with Opera. That does not, however, excuse an if Netscape else if IE without an else; i.e. if the browser doesn't match anything previous just leave all objects null. Good programming would have at least put JavaScript compliant code in the final else and then hoped for the best. Most alternative browsers will try to be as standards compliant as possible.

Second, is all that JavaScript necessary? I find myself being left behind because I feel every web page doesn't need to look and perform like an iPhone. What's next; keyboards with motion sensors so. Whoops. Showing my age here. What's next; mice with horizontal and vertical sensors so... Ooops... Wrong again. Who uses mice anymore? Touch screens with voice recognition. Yeah. Every page is going to be LCD shake/touch/punch/slap and voice aware.

Actually I'm going off the deep end. I just want to be able to register for a $3,000 F'ing class so I can get a $270 certification then take another $3,000 F'ing class so I can get a $270 certification, so I can become a big-shot know-it-all Oracle guru about a month before we drop Oracle and go pure SQL Server. Now Oracle Identify Manager won't let me log into Oracle University via single sign-on (I can go to Metalink). When I "well F'you, I'll register" it says my sign-on is already in use. When I try to get my password, it says I don't exist. Sigh.

MLS conference finals

2008-11-16T16:50:00.000-05:00

I have some catching up to do here?

The Crew went on to defeat the Kansas City Wizards in Columbus which sent them to the finals. Chicago pounded a wounded New England three zip in Bridgeview, sending them to face us in the Eastern Conference finals. It was a match for the ages, everything it was supposed to be and then some.

There were so many back stories.

The biggest one was the return of Brian McBride. McBride was one of the original Crew players and face of the organization. To this day, people still associate McBride with Columbus. Brian left in 2003 to join English Premier League (top level) club Fulham. This season, he returned to America to end his career in the MLS but not for Columbus; he wanted to play for Chicago, his hometown (Arlington Heights). His first encounter with his former MLS team was in Chicago where he had the first and last goals of the two goal tie. Now he would make his return to Columbus with our elimination on his mind.

Another big story was the return of fan favorite Jon Busch. Jon was acquired in the 2002 SuperDraft and was starting for the Crew by 2003. Busch was between the pipes when Columbus won the Lamar Hunt U.S. Open Cup in 2002 and won goalkeeper of the year in 2004. He suffered season ending ACL injuries in both 2005 and 2006. He was waived in 2007, picked up by Toronto, then waived again and then picked up by Chicago. Busch has been, and always will be, a vocal person. The person who once stated that he could not see playing anywhere but Columbus was now displaying public anger towards Coach Sigi Schmid.

What else? The Fire had Kettering, Ohio native Chris Rolfe. Both teams played to two, two to two draws. Chicago was the only team Columbus did not defeat in 2008. The Crew lost to Chicago back in the June Open Cup match.

The first goal of the match was the worst possible scenario. Roughly 30 minutes into the game a defensive brain fart lets McBride get a head on a Mapp cross. One to zip, visitors and the travelling fans were jubilant. The Columbus fans never gave up though. The second half was a completely different story.

Roughly 50 minutes into the match, Crew central defender Chad Marshall out-duals the aerial powerhouse McBride and heads home a Guillermo Barros Schelotto free-kick. It was a perfect strike off the cross-bar; nothing anyone could have done to stop it. Five minutes later, Eddie Gaven buries a low, bouncing shot underneath Busch for the second goal. That one would end up the game winner.

About forty nervous minutes followed. I certain every single Crew fan was thinking the same thing: when was McBride going to tie this in the dying moments of regulation (just like he did in Chicago). Well, it didn’t happen. A few missed heartbeats later the Crew were going to their first MLS Cup with their first conference championship.

Massive.

The Crew will face the Western Conference champions - the New York Red Bulls??? Don't ask. Just destroy them.

Mojave part 3, Mojave fights back

2008-11-10T12:40:00.002-05:00

The machine I have been using Mojave on has had one or more folders in the \Users folders get corrupted and rendered inaccessible. The short version of this fix was to make a copy of the most previous version of the corrupt folder, shut down, boot with the Mojave install DVD, go to recovery, get to the command prompt, then run chkdsk C: /f, then pray a bit, then see if things got fixed and if you need to recover.

What a load of warthog feces.

The pain-in-the-ass is not being able to run the chkdsk from the Mojave GUI or when the computer is restarted (via prompt when you try to run the GUI chkdsk). You can tell it over and over and over again but it never seems to run chkdsk.

Fine...

This corruption has occurred after installing software and after installing updates. Apparently, others have run into this problem.

MLS semi-finals, leg 1

2008-11-03T21:14:00.000-05:00

The first leg of the home and away aggregate goal series went close to how I thought it would go. The Crew tied the Wizards with a late goal; so they will have to win the series at home. Wizard play maker Hercules Gomez will sit out that game because he was ejected after a violent foul in game one. New England was unable to score goals at home but luckily for them, the Fire didn’t score any either so their series is tied. The Red Bulls tied Houston on their home turf so they will probably get pounded when they travel to Houston. Real Salt Lake took a goal at home and hopefully they will survive their trip to California. Not surprising, there were three ties out of four games and not much scoring.

Mojave, part two - the revenge

2008-11-03T21:02:00.001-05:00

I have been using Windows Mojave for over a month now so I have decided to post some more thoughts.

Wireless networking, well, networking in general, is annoying at times. Compared to Windows XP, Mojave looks like it tries to do too much to make wireless networking dumb-ass proof. I usually end up doing a reboot after switching wireless networks because when I undock (from a wired connection) the wireless connection at work picks up if I’m lucky or when I get home the connection is strong but doesn’t always connect or connects for a moment or two then disconnects. It might be Dell because I’ve never had a problem with an IBM laptop.

Most of the problems with Windows Mojave have been with non Microsoft software. Symantec has an issue with correctly going into sleep mode, causing the battery to drain completely when you least expect it. Oracle’s Developer Tools for Visual Studio do not work out of the box. Then again, if Visual Studio was a 64 bit application, things might be different. Video and audio CODEC support in 64 bit mode is provided by someone outside of Microsoft and Media Player 32-bit is the default player, not the 64-bit one.

I think what annoys me the most about Mojave is that it just isn’t necessary. I was reading a magazine in the can the other day and one guy was suggesting that businesses start adopting Mojave because people are going to be wanting widgets for the side-bar. Really? That’s why I should upgrade hardware and operating systems, because people will be wanting sidebar widgets. How about some freakin’ software that works? How about 64 bit versions of some software? It has been how many years since 64 bit on the desktop was a reality? Some programs are not Mojave capable. How hard is it to make a program Mojave compatible? Some programs only work when run as administrator; why would a semi-thin client require administrator rights to work? Stupid.

My current impression of Mojave hasn’t changed much. It is a nice looking operating system and there are some things in the start menu that I like (like how you can type in the name of a program or control panel setting and it will filter the lists immediately for you, helps when you don’t have a mouse). Then again, everything I can do for this blog, my stories, and work can be done in Windows XP on a machine with a fourth the horsepower.

Crew vs. DC, playoff thoughts

2008-10-26T22:10:00.000-04:00

Today's match against D.C. United couldn't have been scripted any better. The same northwest goal post that denied two United attempts happily tucked a Brad Evans long-range shot into the back of the next for the only goal of the match. The massive supporters groups were able to enjoy the Supporter's Shield trophy and the Columbus Crew sent a hated rival with a long history of knocking us out of the playoffs, out of the playoffs. How sweet it is. Now it is on to the playoffs.

The first round of the MLS semi-finals consists of a home and away aggregate goal system. The lowest seed hosts the first game and the highest seed hosts the second game. In my opinion, this does not give the advantage to the team that did the best during the regular season. At best the two teams are equal or a slight advantage is given to the lower seeded team. Here is why.

The lower seeded team can come out hard; early and usually get away with it. New England did this to us in 2004 by delivering some hard fouls early in the match to our playmakers and midfield anchors (notable was a Shalrie Joseph tackle on Simon Elliot, worthy of ejection but went without even a caution and Simon wasn't the same player after that tackle; Joseph did the same thing to Jaime Moreno when they played DC, btw).

Using home field advantage for the first game, the lower seeded team has more control over the flow of the game as the visiting (higher seeded) team must usually sit back and feel out. The home and away series somewhat turns into a two-half match (one half home and away). So if the home team can squeeze out a victory by a goal (or more) they still control the flow of the game because the second leg forces the higher seeded team to score that many goals plus one (to avoid penalty kicks). The higher seeded team will need to press for goals opening them up for counter attack goals. I think this format benefits the "boring", defensive teams that rely upon the two or three highly skilled players up top typically running a quick counter attack three on three or four.

The first round will feature Columbus vs. Kansas City and Chicago vs. New England in the east, Houston vs. New York and Chivas USA vs. Salk Lake in the west. If I were New York I'd come out full throttle for the full 90 minutes and rack up as many goals as possible on that crap Astroturf surface. I still think Houston will take that one in the long run otherwise. I would like to see Real Salt Lake get by Chivas USA just to give Jason Kreis a pat on the back for his years of service to MLS, give the fans a pat on the back for being patient and getting a new stadium, and to keep California out of the playoffs. In the east I think New England is too volatile and weakened to go up against a surging Chicago team. I also think the Crew will prevail against Kansas City but we might have to do so on home turf.

Why SQL generators suck

2008-10-08T12:18:00.004-04:00

Assume the following table:

CREATE TABLE craps
( craps_id          NUMBER
, crap_size         NUMBER
, crap_color        VARCHAR2(16)
, crap_when_taken   DATE DEFAULT SYSDATE
, crap_when_flushed DATE
, crap_rating       NUMBER
, CONSTRAINT craps_pk PRIMARY KEY ( craps_id )
) storage_clauses, logging_clauses, santa_clauses ....

To get a list of craps and their size and color for a time interval by time it was flushed or if that was null (i.e. it was a proud poop that someone wanted everyone to see) the time it hit the bowl; the efficient SQL would be:

SELECT crap_size
, crap_color
, NVL(crap_when_flushed,crap_when_taken) crap_flushed_taken
FROM craps
WHERE NVL(crap_when_flushed,crap_when_taken) BETWEEN :1 and :2
ORDER BY crap_flushed_taken ASC NULLS FIRST;

Oracle would do a full table scan, efficient blocked I/O, and then poop out the results. Oh, wait. We're using a canned, record-based, RDBMS independant system that generates SQL that selects a list of primary keys and then reads each record by primary key. So we have something more like this:

SELECT craps_id
FROM craps
WHERE NVL(crap_when_flushed,crap_when_taken) BETWEEN :1 and :2
ORDER BY NVL(crap_when_flushed,crap_when_taken);

and for each selected primary key value:

SELECT crap_size
, crap_color
, NVL(crap_when_flushed,crap_when_taken) crap_flushed_taken
FROM craps
WHERE craps_id = :1;

Oh, wait. That NVL on those two fields is business logic that needs separated from the data layer; so we create a function:

CREATE OR REPLACE FUNCTION 
 crap_flushed_taken (i_crap_when_flushed DATE, i_crap_when_taken DATE)
 RETURN DATE AS
BEGIN
 RETURN NVL(i_crap_when_flushed,i_crap_when_taken);
END;

No, no, no, stupid. That layer shouldn't need to know to pass two dates in it should work off the record/object primary key identifier:

CREATE OR REPLACE FUNCTION 
 crap_flushed_taken (i_craps_id NUMBER)
 RETURN DATE
 AS
 v_crap_when_flushed DATE;
 v_crap_when_taken DATE;
BEGIN
 SELECT crap_when_flushed, crap_when_taken
 INTO v_crap_when_flushed, v_crap_when_taken
 FROM craps
 WHERE craps_id = i_craps_id; 
 RETURN NVL(v_crap_when_flushed,v_crap_when_taken);
END;

Actually, this is how they did it

CREATE OR REPLACE FUNCTION crap_flushed_taken (i_craps_id NUMBER)
 RETURN DATE
 AS as language java name 'com.crap.name.name1.CrapFlushedTaken(java.lang.Integer) return java.lang.Date';

That's a Java class that basically does the exact same thing that little slice of PL/SQL was doing. The final, generated SQL statement went something along the lines of this:

SELECT craps_id
FROM (
 SELECT q1.craps_id
 FROM craps q1
 WHERE NVL(crap_flushed_taken(q1.craps_id),TO_DATE(1,'YYYY')) >= TO_DATE('????????','YYYYMMDD')
 AND NVL(crap_flushed_taken(q1.craps_id),TO_DATE(1,'YYYY')) <= TO_DATE('????????','YYYYMMDD')
) t0
LEFT JOIN craps t1 ON t0.craps_id = t1.craps_id
ORDER BY WHERE crap_flushed_taken(t1.craps_id) ASC NULLS FIRST, t1.craps_id ASC NULLS FIRST;

Then the list program read each one of the selected primary key values and did this:

SELECT crap_size
, crap_color
, crap_when_flushed
, crap_when_taken
FROM craps
WHERE craps_id = :1;

Holy slang for feces! Oracle does an index fast full scan on the ID, which it would see as the most efficient way of going about this because in the optimizer's sane mind it would never think anyone would do a single read of the same table inside of a function. So for each primary key in that table (because we don't know the result of the function so no way to limit the working set) we are doing at least three (select, order by, list program), random (record location likely won't match the maintained order of the index), entire table reads using an index (read index block, then lookup corresponding block). Poop on me and call me a sundae...

I tried to simulate the above with inline select statements but the 10g optimizer was smart enough to figure it all out and come up with a plan five times the cost of the optimized plan for proper, hand-tuned SQL. I could try running the thing to get real statistics but unfortunately this thing runs for nine hours. My efficient SQL above runs in 30 seconds.

There are options available. First would be to rewrite the canned code and do the selection process with the optimized hand-written SQL. Another option would be to create an index on the function (haven't tried it yet) so theoretically Oracle will see the SELECT crap matches an index and use the index instead of reading the whole table one row at a time through the primary key index.

I know this is probably an extreme example (or one of many) but I think the same thoughts can be applied to code generators and to the object oriented purists that believe no business logic whatsoever belongs in the data tier.

Mojave, whoa oh oh oh

2008-09-30T06:11:00.000-04:00

Microsoft's new operating system, Mojave, was mandated for installation on the new hardware my employer recently purchased for me. Mojave looks an awful lot like Vista was supposed to look like but I heard Vista wasn't doing so well so I understand why Microsoft would want to replace it with Mojave. Anyways, I have decided to document my experiences here.

We installed the "Business" version of Mojave because the "Ultimate" version didn't have something that the "Business" version did have. We also installed the 64 bit version because the 32 bit version was either "a piece of crap" or "sucks donkey balls", I can't remember which. Luckily the hardware vendor provided a disk with all the appropriate drivers although we had to download a couple to get certain features to work. By installing the operating system ourselves we didn't have the 40 gigabytes of pre-loaded bull-feces to uninstall.

The second step was to install all the software we use on a daily, weekly, only-when-we-are-forced-to basis. As a systems administrator and developer I had double the amount of crap to install on the machine but was intelligent enough to download it all ahead of time to a portable drive. There was a lot of "Cancel or Allow" and "Something is attempting to do something" which led to a lot of clicking without reading because after a while you just get numb to it all. I could have been installing some Nazi Al Qaeda kitten snuff porn server hub and not known it because I had become conditioned to clicking these authorizations all the time. The only piece of software that isn't working at this writing is Eclipse. It throws a Java error when it starts up. Fudge.

The first noticeable difference between Mojave and XP is the Windows Sidebar. The sidebar, which by default hovers just above the clock in the explorer bar, features another clock, mini slide-show and news ticker. These things are called gadgets and there are about 400 more of them available ranging from just crap to useful eye-candy. Not wanting to be un-cool, I've loaded as many gadgets as I could into the sidebar. I don't need them but they look snazzy so who cares. I can't wait to have a themed slideshow so I can have some microscopic nudity in the sidebar for when I'm home.

Mojave includes a lot of eye-candy; i.e. things that look really sharp but might not affect your productivity.

I have found a few annoyances. The defragmenter does not show status, it just says this might take minutes or hours. To get around this run the command prompt as administrator then do a "defrag c: -a –v" to defragment drive "C:". I could not figure out how to enable hibernation (I have to create a power plan or something and still couldn't find the option to enable it) so I have to use "sleep" mode or shut down. The initial working set (i.e. just the applications that load at startup) is 1.4 GB. The machine has 4 GB so I will not pass judgment on Mojave being inefficient without looking at the internals. See my other blog posts on memory consumption; i.e. I'd rather have the machine using memory than hitting disk.

I am certain there will be more things to report as I use Mojave but at this time I'm not impressed. Everything I can do on Mojave I can do on Windows XP and even Linux if I so chose.

ORA-01008 on PL/SQL MERGE over link

2008-08-28T14:11:00.007-04:00

If you get an "ORA-01008: not all variables bound" error when trying to do a MERGE INTO a remote table using local PL/SQL variables and local data from local tables, what do you do besides beat your head against the wall until it is soft and mushy like a sponge?

DECLARE
  v_turd VARCHAR2 := 'Stinky';
BEGIN
MERGE INTO table@link m
USING (
  SELECT
    crap
  , poop
  , dood
  , loaf
  FROM local_table
  WHERE num = 2
) u
ON (m.loaf = u.loaf)
WHEN MATCHED THEN UPDATE SET m.turd = v_turd, m.poop = u.poop
WHEN NOT MATCHED THEN INSERT VALUES (u.load, v_turd, u.crap, u.poop, u.dood, u.loaf);
COMMIT;
END;

Thankfully, it only took a couple of Google searches to find a solution offered by Carsten Herbe at Oracle's forums. The INSERT and UPDATE statements in the MERGE must only use data defined in the USING or literals (or maybe other stuff too). So...

DECLARE
  v_turd VARCHAR2 := 'Stinky';
BEGIN
MERGE INTO table@link m
USING (
  SELECT
    crap
  , poop
  , dood
  , loaf
  , v_turd turd
  FROM local_table
  WHERE num = 2
) u
ON (m.loaf = u.loaf)
WHEN MATCHED THEN UPDATE SET m.turd = u.turd, m.poop = u.poop
WHEN NOT MATCHED THEN INSERT VALUES (u.load, u.turd, u.crap, u.poop, u.dood, u.loaf);
COMMIT;
END;

Sometimes I feel sorry for us computer nerds that have to deal with frustrating little things like a damned ORA-01008 that works perfectly in SQL when you are testing but as soon as you try to automate it with a variable you get your geek pee-pee whacked with a ruler. Bad geek, no work. Thankfully the clicker-web is littered with other suffering nerd-souls that have run into these problems on the smallest of free systems to multi-million dollar goliaths. Unlike other nagging problems this one didn't cause much suffering on my part. Hopefully by posting this here it will add to the Internet litter and maybe help others faster.

Oracle instant client, Ubuntu, revisited

2008-08-26T13:19:00.002-04:00

For those that want quick installation instructions assuming you have the installation files on the root of a mounted CDROM:

sudo -s
mkdir /opt/oracle
cd /opt/oracle
unzip /media/cdrom/instanclient-basic- (etc.version.blah.blah)
unzip /media/cdrom/instanclient-sdk- (etc.version.blah.blah)
unzip /media/cdrom/instanclient-sqlplus- (etc.version.blah.blah) optional
ln -s instantclient_11_1 client
cd client
ln -s libclntsh.so.11.1 libclntsh.so
ln -s libocci.so.11.1 libocci.so
ln -s libsqora.so.11.1 libsqora.so
tr -d '\r' < /media/cdrom/tnsnames.ora > /etc/tnsnames.ora
tr -d '\r' < /media/cdrom/sqlnet.ora > /etc/sqlnet.ora

To use SQLplus from a shell you need to add the ORACLE_HOME=/opt/oracle variable and add /opt/oracle/client to variable LD_LIBRARY_PATH to the /etc/bash.bashrc (for example).

Making fudged PII

2008-07-15T13:11:00.002-04:00

As I start dealing more with application development that sends data to external sources I think more about the security of personally identifiable data within that data. When testing web services and file transfers there is no reason to have real, personally identifiable information (PII) in that stream of test data. However, the destination for that data still needs values in those elements. There are a few ways to approach this:

Random data
Pre-generated test data, test cases, etc.
Use existing data but generate PII from a real primary key

The main problem I see with randomly generated data is "re-testing". For example: you send data for ID 1234567 to a service, randomly generating four columns of test data, then that service requests a re-test with the same data.

Pre-generated data would be a set of test cases, known trouble patterns and other data created before testing occurs. This scenario is feasible for new development on new data systems, to test min/max/null value scenarios that would otherwise never appear in live data, or to force a specific set of data. Where this scenario becomes cumbersome is when there is a tightly integrated system with numerous historical pre-cursor processes; triple the complexity if that system is another vendor's package and not your own. For example: create customer, purchase 14 months worth of product, run through aging and re-bill process, reconcile A/R, and then skip a month of purchases. There could be over a hundred tables touched by that set of processes and if one is skipped/missed then the data for another group is no longer valid.

Generating test data from the primary key is feasible when the system has a long history of identified test data handy but simply needs PII altered to protect the identity of the individual or organization. By using a unique, primary key the "fudged" PII data will always match back to the source primary key for instances where "re-testing" is required. For example: ID 1234567 always generates social security number 898-75-5309 (not a real number but will validate in some systems).

The example below was written for Oracle SQL to show how to convert a seven digit primary key identifier into a social security number, date of birth and gender:

SELECT t1.id  --- VARCHAR2(7)
, TO_CHAR(899-FLOOR(TO_NUMBER(t1.id)/989901),'000')
 ||TO_CHAR(MOD(FLOOR(TO_NUMBER(t1.id)/9999),99)-99,'S00')
 ||TO_CHAR(MOD(TO_NUMBER(t1.id),9999)-9999,'S0000') social_security_number
, TO_CHAR(TRUNC(TRUNC(SYSDATE-6574.5,'YEAR')-MOD(FLOOR(TO_NUMBER(t1.id)/10),2191.5)-CASE SUBSTR(t1.id,-1,1)
 WHEN '2' THEN 2191.5
 WHEN '3' THEN 2191.5
 WHEN '4' THEN 4383
 WHEN '5' THEN 6574.5
 WHEN '6' THEN 8766
 WHEN '7' THEN 10957.5
 WHEN '8' THEN 13149
 WHEN '9' THEN 15340.5
 ELSE 0 END),'MM/DD/YYYY') date_of_birth
, CASE
 WHEN SUBSTR(t1.id,-1,1) < '5' THEN 'Male'
 ELSE 'Female' END gender
, CASE SUBSTR(t1.id,-1,1)
 WHEN '0' THEN 'AI' --- American Indian/Alaskan
 WHEN '1' THEN 'AS' --- Asian/Pacific Islander
 WHEN '2' THEN 'BL' --- Black/Non-Hispanic
 WHEN '3' THEN 'HS' --- Hispanic
 WHEN '4' THEN 'NR' --- Non-Resident Alien
 WHEN '6' THEN 'AS'
 WHEN '7' THEN 'BL'
 WHEN '8' THEN 'HS'
 ELSE 'WH' END ethnic_code
FROM table_name t1 WHERE [selection criteria];

The social security number is pretty straight forward. If area numbers (first three digits) higher than 772 do not validate, then use area 267 (237-267 have all groups allocated within them).

The date of birth is a little complicated but the attempt was to use the ones digit to generate one of seven date ranges with the lower two in the range getting more hits because they are the primary age group dealt with. Starting with a base age of 18 years of age, subtract one of seven six year blocks, and then subtract zero to six years.

Gender was a simple test of the ones digit to determine male or female.

Ethnic background was a simple translation of the ones digit to a code taking into account there are more numbers in four of the six groups.

This was a very bare example meant only to suggest direction. It would be interesting to build a library (although someone probably already has).

No more Mr Cranky

2008-07-13T20:29:00.002-04:00

One of my favorite Friday morning web reads is calling it quits soon. Mr Cranky and his movie reviews are going away in August. I’m so, well the world isn’t going to come to an end but a good source of honest movie reviews and funny caption contests will be no more. I’ve thought about writing movie reviews here and have done so for the “Blog of the Dead” but they relate directly to zombie movies and not to movies in general.

In order to write movie reviews I would actually have to go to see movies in the theater. I’ll probably go see the Dark Night but I think the last movie I went out to see was Spider Man 3 and that was only to see how badly they fucked up the Venom character. I typically wait until the movies hit the rental queue on Netflix and I’m usually limited to horror movies and whatever movies my girlfriend rents. Theoretically I could still do reviews of rentals but they would be kind of late, wouldn’t they? I could always do mini-reviews of the rentals to see if my warped mind matches the rest of the critical world but I find myself with barely enough time to write for this blog and my story blog.

Maybe instead of doing something in absence of Mr Cranky I should just let it fade off and not do something in the same vein, that would be the best honor of all.

Update: Mr Cranky did not go away. Yay!

Web 2.blow

2008-07-10T08:15:00.000-04:00

Okay this whole web 2.0 lets throw everything and the kitchen sink into a web browser and use it as a business computing platform has given me a reason to get angry and blog again.

I usually have two physical machines active at work (well, at home too). I have the Windows XP based machine where I do most of my work and an older Ubuntu Linux desktop machine that I use for web browsing, music listening, scripted automation, etc. I have four desktops on the Linux machine: music player and maintenance, one ore more Opera web browser sessions for various documentation and manuals, Opera web browser for Google searches, and one empty just in case I need it. This configuration is very useful to me and it comes in handy sometimes because the Windows XP machine has about 300 megabytes of anti-virus, usage monitoring, remote management agents, network client, print client, database server, remote update agents, groupware notification agents, software update sleeper programs, and who the frak knows what else running on it before I launch the first bit of work. In fact, if I have to boot/reboot the Windows XP machine in the morning I can usually have the Linux machine booted with all the applications I use there loaded, my "Good Morning" Opera session loaded and all pages viewed, etc. just as the Windows XP machine finished its rituals. Occasionally, usually when I'm doing real intensive work, my employer schedules scanner software to make sure I don't have any viruses, spyware, malware, underwear, etc. and to inventory my machine for hardware and software, and to scan all directories for .avi, .mp3, .mpg, etc. to see how much hard disk real-estate I'm using for I'm assuming "non-work related" stuff.

Okay, I know, get to the point. As we use more web-based applications I keep running into more examples of where a) they don't work in a browser, b) do not have complete functionality in one or more browsers; c) have functionality (possibly) broken by a feature of a browser. Here is what I deal with:

one environment, since it is deeply integrated into IE6, does not work in IE7 (so we cannot upgrade to IE7 yet)
one application simply refuses to work in Opera but works in IE6 and Firefox
one application works in IE but not well in Firefox or Opera due to poor JavaScript and CSS (yes, it's a Microsoft Visual Studio project, how did you guess)
one application works in IE but occasionally loses functionality in Firefox and Opera
one application requests I upgrade from IE6 to IE7
one application works in IE and Opera, but not Firefox (could be fixed with a GreaseMonkey script?)
one application does not work in Opera because of how they implement(ed) display:none in CSS for images

There are some applications that do make sense in the Web 2.0 world. However, there are also things that are better off in a "thick" client that is forced to adhere to a strict window API/widget toolkit. People have been conditioned, for good reason, to not trust the Internet and that is the reason why all the browsers either include anti-phishing, anti-hacking, ad blocking, flash blocking, JavaScript blocking, fraud protection, content blocking, etc. via add-on or built-in. Combine that with the straight fact that CSS, JavaScript, DOM and default fonts are not the same and/or do not work the same way on IE6, IE7, Firefox, Opera, etc. and that, IMHO, is why web based applications that try to do too much, fail (and piss me off).

Oh, and don't get me started on how security and personal identity security has been handled in this new, networked and Internet world.

Lots, defined

2008-06-16T11:51:00.000-04:00

Do you know the drill when you are ordering Lleb ocaT? You drive up to the menu and wait for someone to say something along the lines of "how are you doing?" instead of something like "welcome to Lleb ocaT can I take your order please?" I can understand if they want to shorten it to "welcome, may I take your order?" but most times when I get that fake drive-thru ice breaker I sit there after my brief "fine" answer waiting for someone to ask me what I'd want to force down. You then order your "food" and/or beverages, proceed to one of perhaps two windows, pay, get a drink shoved in your face, get your change, and then they ask "any mild or hot sauce?" as they are ready to toss the food bag at you.

I always reply "lots of hot sauce please".

Unfortunately there appear to be very few Lleb ocaT franchises that understand what "lots" means. In my opinion, there are four responses to the "sauce" question: none, some, yes (enough or adequate), or lots.

"None" is self explanatory. The taste of the product is adequate, you can stomach it without the need for sauce, or you have more powerful chemical additives at home.
"Some" indicates you need at least a couple to either enhance the flavor of the food product, toys for the kids to play with, or would feel cheated if you didn't ask for some sauce.
"Yes" indicates you will need at least one sauce package per food item. This would most likely be the formula agreed to by management to equally appease customers flavorfully and parent company stockholders and franchise ownership fiscally; multiplied by 1.3275 rounding up.
"Lots" indicate that you either need to strongly mask the flavor of the food product or are so frakkin' hungry every ounce of sauce, food product, and wrapper scrapings are required to nourish you. This realistically means at least one per taco sized item and two to four per burrito depending upon burrito size and how much refried bean food product is in the burrito.

So today I order two burritos and three soft tacos, ask for "lots" of hot sauce and I'm given a whopping four plus no napkins to clean up with afterwards. Wow. Did you spit in the food as well?

I know how food service works. There are bean counters that know exactly what volume of soft drink to push, exactly how much product to deliver, exactly how many bags you should use and how many condiment packages you should put in with the order on request. They are looking at numbers under a microscope. Somewhere someone is saying "if we reduce the sauce packages by one per order we save 22/100 th of a cent in sauce and 1 and 13/100 th of a cent in manufacturing and 8 cents in advertisement per order. If you multiply times the average of 300 orders per day the franchise and stockholders can save over ten thousand dollars per year in just hot sauce packet count reduction. Do the same for "mild" and "fire" that would be a net savings of over thirty thousand dollars. If we assume our customers are slobs and reduce the napkin output per bag we can save the franchise and stockholders another twenty thousand dollars per year. Just in the United States alone, the estimated savings is a lot of money; probably over a trillion dollars. We could knock out the national debt in three years just by shafting the customer out of a hot sauce and napkin every single day."

Wow.

I think I'll just go to S'ydnew next time.

Quickie update

2008-06-10T05:56:00.001-04:00

Felt like a quick update.

Life in general

I usually try to keep my personal life out of the public only occasionally making reference to my job or home life. My personal life is just that, personal. I'm not some MySpace or YouTube attention whore that needs to pine endlessly about what Jason the boy stud did in study hall today. Things are getting better, though.

The Blog of the Dead

I am slowly attempting to catch up to the present times with my story blog. I have a large segment in December 2007 completed but need to fill in that piece with the story ending in October 2007. I usually have two or three different story pieces going at the same time depending on where I'm at when I get creative. There was one on the laptop and one on my main workstation. I use a USB drive to keep things in sync. I keep names, locations and an event timeline in an Excel spreadsheet.

Technology

Sometimes I wonder if I'm a glutton for punishment or just too stubborn to do anything differently.

I have two, high-powered machines at home: AMD X2 processors, one with 8GB of memory, one with 4GB of memory, both with infinitely more disk than they will ever need. I am installing Ubuntu 8.04 LTS server 64-bit on each of them. So why does this make me a glutton for punishment? Well, on one I am going to be installing VMWare and on the other I will be installing Oracle 10g database. VMWare is not guaranteed to work on the kernel Ubuntu 8.04 LTS server 64-bit is using and not really guaranteed to work on Ubuntu, period. Oracle 10g database is only supposed and to be used and installed on Red Hat Enterprise Linux or SUSE Linux Enterprise Server. Instead of using either of those two (they cost money, BTW), or using the free version of one (OpenSUSE), or one of the downstream distributions from Red Hat (Oracle Linux, Cent OS), I chose to use a different distribution and then scour the clicker-web looking for ways to force it to work with VMWare and Oracle database. Just by using the 64 bit version of Ubuntu is inviting pain, suffering and trouble. The X-Server 64 bit version does not work the same way as the 32 bit version on the same hardware. The 64 bit nVidia drivers are worse.

So why do I do this? Why not install Oracle database on Oracle Linux? Why not install VMWare on a kernel and distribution they support instead of searching far and wide looking for the patch and notes on how to install it?

I must be sick.

Chili recipes

2008-06-04T11:37:00.003-04:00

Just creating a blog post containing tasty chili recipes I've collected. If you stumble upon this and have one then leave a comment with a recipe or URL to one.

Turkey and Chicken Chili

1 lb boneless skinless chicken breast
1 lb turkey sausage (breakfast)
1 can chili beans
1 can diced tomatoes (Del Monte "zesty chili style")
1 small can tomato paste
1 pkg chili seasoning
1/2 red onion, diced
1/2 red bell pepper
2 tbsp chili powder
1 tbsp salt
1 tbsp pepper
1 tsp crushed red pepper
1 tbsp cilantro
1 tsp garlic powder

Pan fry the sausage with the red onion until brown. Boil the chicken until it's tender enough to be shredded. Combine the meat in a pan and add 1 cup of water and the seasoning packet. Reduce until water is almost completely gone. Add in everything else however you want.

Can be topped with shredded cheese, sour cream and/or chives and served with cornbread on the side.

Worth Quoting

2008-06-03T07:45:00.000-04:00

Dave Barry on the Economic Stimulus Payment

This year, taxpayers will receive an Economic Stimulus Payment. This is a very exciting new program that I will explain using the Q and A format:

Q. What is an Economic Stimulus Payment?
A. It is money that the federal government will send to taxpayers.

Q. Where will the government get this money?
A. From taxpayers.

Q. So the government is giving me back my own money?
A. Only a smidgen.

Q. What is the purpose of this payment?
A. The plan is that you will use the money to purchase a high-definition TV set, thus stimulating the economy.

Q. But isn't that stimulating the economies of China and Japan?
A. Shut up.

Computer hell

2008-05-24T22:06:00.003-04:00

Friday night was a damn crummy night for me and my computers.

The first pile of crap I had to deal with was the 64 bit server version of Ubuntu. I have a powerhouse AMD X2 machine with 8GB of memory and 900 gigabytes of disk (500, 200 and 200). This machine will be dedicated to running VMWare Server and various groups of virtual machines for developmental purposes. I had the new 8.04 LTS edition. I installed using LVM leaving extra space for expansion of logical volumes if necessary.

The first problem was GRUB wouldn’t do anything. Reboot, black screen, nothing. I altered the installation to install LILO and things worked this time. It was a minor annoyance but foreshadowed pain to come. I needed a desktop for VMWare so I decided to try KDE by installing the kubuntu-desktop package. There were four warnings issued during the installation. When I rebooted, LILO simply puked a bunch of 99’s on the screen then died. Great. I’ll try again later.

I just purchased a 24” wide screen LCD to replace the LCD one of our cats puked on and rendered non-operational. One of the problems with the large surface area of the desktop is that the styles that come with Windows XP are dull and look like crap. Some people at work have some really nice desktops so I figured I would find a nice them online, install it and bask in my coolness.

I went to a place called ThemeXP. I’m not going to provide a link, you’ll find out why later. I downloaded two themes in the form of executables. I assumed they were installers. When I tried installing them I was greeted by a pop-up window that explained these files had been “wrapped” and that you had to agree to services, etc. In short it was “f***-no” terminology.

I did some more investigation and found a link buried at the bottom that indicated some files were “wrapped” to defer operational cost through advertisements. Nice. We can really trust those advertisers. They have a stellar history of not trying to f*** over computer users. I cancel the install and look elsewhere. Then I noticed my hard drive just chattering away. Then AVG pops up a virus warning. I quickly do a process list and kill a msin.tmp process that was spawned by this “wrapper” program. More and more AVG pop-ups with files infected with the Win32/Gaelicum.A virus. My hell was just beginning.

I unplugged the network cable and booted a clean machine while scanning the infected one. The Win32/Gaelicum.A virus is a nasty little bastard that infects .exe files and is network aware. Grisoft had a cleaner utility to download and run in safe-mode. The problem, however, was that Grisoft AVG anti-virus had moved all infected executables to their virus vault; including the executable to manage the virus vault. So I can’t get the executables out of the vault to run the cleaner utility on them. Well that was just great.

I had to sleep on it. I was beyond furious.

So how did I fix it? In short: remove the infected drive from the computer and place it in a portable enclosure. Attach that drive via USB to a clean computer running the same software, copy AVG and Windows executables from the clean machine to the infected drive, copy vcleaner to the infected drive, detach the drive, install the drive back into the machine, boot, run the virus vault utility, take the infected executables out of the vault, reboot in safe mode, run vcleaner, reboot, scan again, test executables.

Whew.

Damage was minimal. Some things won’t uninstall due to corrupt binaries and I had to reinstall 7-zip.

I am a computer professional and I almost had my entire computer f***ed because I wanted a nicer looking desktop. I sent a mail message to themexp explaining what happened and haven’t heard back from them. Rot in hell. There is so much garbage out there that if you see a screen saver or theme or program somewhere just don’t install it. Don’t let your kids install screen savers or programs. If they do then punishment is no games and no Internet unless you need it for school. Don’t let you parents click on things unless you put them there to click on. If you are using a work computer just don’t install anything and if you employer blocks sites don’t bitch about it. Also, consider installing WOT (Web of Trust) or similar browser plug-ins.

Work and prison

2008-05-06T08:18:00.000-04:00

When you think about the differences between work and prison, maybe prison isn't so bad...

IN PRISON.......You spend the majority of your time in an 8x10 cell.
AT WORK........You spend most of your time in a 6x8 cubicle.

IN PRISON.......You get three meals a day.
AT WORK........You get a break for 1 meal and you have to pay for it.

IN PRISON.......You get time off for good behavior.
AT WORK........You get rewarded for good behavior with more work.

IN PRISON.......A guard locks and unlocks all the doors for you.
AT WORK........You must carry around a security card and unlock and open all the doors yourself.

IN PRISON........You can watch TV and play games.
AT WORK.........You get fired for watching TV and playing games.

IN PRISON.......You get your own toilet.
AT WORK........You have to share.

IN PRISON.......They allow your family and friends to visit.
AT WORK........You cannot even speak to your family and friends.

IN PRISON.......All expenses are paid by taxpayers with no work required.
AT WORK........You get to pay all the expenses to go to work and then they deduct taxes from your salary to pay for prisoners.

IN PRISON.......You spend most of your life looking through bars from inside wanting to get out.
AT WORK........You spend most of your time wanting to get out and go inside bars.

IN PRISON......There are wardens who are often sadistic.
AT WORK.......They are called supervisors.

IN PRISON.......You have unlimited time to read e-mail jokes.
AT WORK........You get fired if you get caught.

Hello I'm Hillarobamy Clibama

2008-03-02T14:47:00.002-05:00

"Hello, I'm Hillary Clinton"

"Hello, I'm Barack Obama"

Anybody else getting sick and damned tired of coming home and having to clear off at least one voice mail message every damned day? Anyone else frustrated at getting awakened during your lazy Sunday nap by the auto-dialers setup up by supporters of the Presidential hopefuls? I just did a Google search and evidently I'm not the only person sick of this crap.

Now that Texas and Ohio are suddenly important to Hillary and Obama, my home phone is getting at least one call a day. My girlfriend's dad was getting even more calls. Apparently, other places like New Hampshire have had the same problem. Most of the New Hampshire newspapers have printed letters from unhappy voters (and non-voters), many of whom simply said "I'm voting for this candidate" just to get the callers off their backs.

Guess what, dumb-asses. I can't stand any of the four remaining candidates for President. Clinton is all talk. Obama is another talker, just more aligned to future ideals that will be squashed by the remaining two branches. Huckabee is nut. McCain has some decent aspects but about as much charisma as tree bark; and "true conservatives" don't care for him anyways.

How did we end up with these four?

I'd consider libertarian again but the current crop of candidates for President and other offices makes me cringe. Again, is that the best you could find; what looks like a used car salesman, a Seinfeld extra, a crypt keeper, and a roller derby cross-dresser.

Anybody else out there just begging for consensus? Do you want to see something besides the conservative and liberal war parties fighting for this all-or-nothing cause when a democracy should build upon the commonalities between the people and place power of the government where it logically belongs? Anybody else believe that there is a place for big government, local government, and no-government?

A few jokes

2008-02-26T06:39:00.000-05:00

Just a few jokes for today...

HAVE YOU FORGIVEN YOUR ENEMIES?......

Toward the end of the church service, the minister asked, "How many of you have forgiven your enemies?" About 80% held up their hands. The minister then repeated the question and all responded by raising their hands except one small, elderly lady.

"Mrs. Jones? Are you not willing to forgive your enemies?", the minister asked.

"I don't have any", she replied, smiling sweetly.

"Mrs. Jones, that is indeed unusual. How old are you?"

"Ninety-eight" she replied.

"Mrs. Jones, would you come down front and tell the congregation how a person can live for ninety-eight years and not have an enemy in the world?"

The little lady tottered down the aisle, faced the congregation, smiled sweetly and said, "I outlived the bitches."

HOW TO POOP AT WORK

We've all been there but don't like to admit it. We've all kicked back in our cubicles and suddenly felt something brewing down below. As much as we try to convince ourselves otherwise, the WORK POOP is inevitable. For those who hate pooping at work, following is the Survival Guide for taking a dump at work.

CROP DUSTING: When farting, you walk briskly around the office so the smell is not in your area and everyone else gets a whiff but doesn't know where it came from. Be careful when you do this. Do not stop until the full fart has been expelled. Walk an extra 30 feet to make sure the smell has left your pants.
FLY BY: The act of scouting out a bathroom before pooping. Walk in and check for other poopers. If there are others in the bathroom, leave and come back again. Be careful not to become a FREQUENT FLYER. People may become suspicious if they catch you constantly going into the bathroom.
ESCAPEE: A fart that slips out while taking a leak at the urinal or forcing a poop in a stall. This is usually accompanied by a sudden wave of embarrassment. If you release an escapee, do not acknowledge it. Pretend it did not happen. If you are standing next to the farter in the urinal, pretend you did not hear it. No one likes an escapee. It is uncomfortable for all involved. Making a joke or laughing makes both parties feel uneasy.
JAILBREAK: When forcing a poop, several farts slip out at a machine gun pace. This is usually a side effect of diarrhea or a hangover. If this should happen, do not panic. Remain in the stall until everyone has left the bathroom to spare everyone the awkwardness of what just occurred.
COURTESY FLUSH: The act of flushing the toilet the instant the poop hits the water. This reduces the amount of air time the poop has to stink up the bathroom. This can help you avoid being caught doing the WALK OF SHAME.
WALK OF SHAME: Walking from the stall, to the sink, to the door after you have just stunk up the bathroom. This can be a very uncomfortable moment if someone walks in and busts you. As with farts, it is best to pretend that the smell does not exist. Can be avoided with the use of the COURTESY FLUSH.
OUT OF THE CLOSET POOPER: A colleague who poops at work and is damn proud of it. You will often see an Out Of The Closet Pooper enter the bathroom with a newspaper or magazine under his or her arm. Always look around the office for the Out Of The Closet Pooper before entering the bathroom.
THE POOPING FRIENDS NETWORK (P.F.N): A group of co-workers who band together to ensure emergency pooping goes off without incident. This group can help you to monitor the whereabouts of Out Of The Closet Poopers, and identify SAFE HAVENS.
SAFE HAVENS: A seldom used bathroom somewhere in the building where you can least expect visitors. Try floors that are predominantly of the opposite sex. This will reduce the odds of a pooper of your sex entering the bathroom.
TURD BURGLAR: Someone who does not realize that you are in the stall and tries to force the door open. This is one of the most shocking and vulnerable moments that can occur when taking a poop at work. If this occurs, remain in the stall until the Turd Burglar leaves. This way you will avoid all uncomfortable eye contact.
CAMO-COUGH: A phony cough that alerts all new entrants into the bathroom that you are in a stall. This can be used to cover-up a WATERMELON, or to alert potential Turd Burglars. Very effective when used in conjunction with an ASTAIRE.
ASTAIRE: A subtle toe-tap that is used to alert potential Turd Burglars that you are occupying a stall. This will remove all doubt that the stall is occupied. If you hear an Astaire, leave the bathroom immediately so the pooper can poop in peace.
WATERMELON: A poop that creates a loud splash when hitting the toilet water. This is also an embarrassing incident. If you feel a Watermelon coming on, create a diversion. See CAMO-COUGH.

Windows networking fun

2008-02-22T18:17:00.002-05:00

This is a partial topic, a work in progress so to speak...

For the server: in registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Browser\Parameters set the string values for IsDomainMaster = TRUE and MaintainServerList = Yes

For all workstations: in registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Browser\Parameters set the string values for IsDomainMaster = FALSE and MaintainServerList = No

A parable worth reading

2008-02-15T14:54:00.000-05:00

This came to my work mail address a while back...

A Japanese company (Toyota) and an American company (General Motors) decided to have a canoe race on the Missouri River. Both teams practiced long and hard to reach their peak performance before the race.

On the big day, the Japanese won by a mile. The Americans, very discouraged and depressed, decided to investigate the reason for the crushing defeat. A management team made up of senior management was formed to investigate and recommend appropriate action.

Their conclusion was the Japanese had 8 people rowing and 1 person steering, while the American team had 8 people steering and 1 person rowing. Feeling a deeper study was in order, American management hired a consulting company and paid them a large amount of money for a second opinion.

They advised, of course, that too many people were steering the boat, while not enough people were rowing. Not sure of how to utilize that information, but wanting to prevent another loss to the Japanese, the rowing team's management structure was totally reorganized to 4 steering supervisors, 3 area steering superintendents and 1 assistant superintendent steering manager. They also implemented a new performance system that would give the 1 person rowing the boat greater incentive to work harder.? It was called the 'Rowing Team Quality First Program,' with meetings, dinners and free pens for the rower. There was discussion of getting new paddles, canoes and other equipment, extra vacation days for practices and bonuses.

The next year the Japanese won by two miles. Humiliated, the American management laid off the rower for poor performance, halted development of a new canoe, sold the paddles, and canceled all capital investments for new equipment. The money saved was distributed to the senior executives as bonuses and the next year's racing team was outsourced to India.

ORA-22992 Oracle hell

2008-02-07T13:31:00.000-05:00

Welcome to my little corner of hell today. A third party vendor changed their tables structures recently, adding a CLOB field to some of their tables. Like most hard working technical folk we have had to write a lot of new code to provide functionality the third party vendor does not and will not provide. So some perfectly good ANSI standard SQL suddenly puked out a "ORA-22992: cannot use LOB locators selected from remote tables" error.

I'm not using any LOB's in the queries so why in the blue heck does Oracle want to their LOB locators? I did a search and thankfully found where someone on Oracle's forums just tried to use the WHERE clause to join instead of the JOIN statement and it worked. I tried it on and sure enough, it worked. It goes something like this:

table1
crap1 VARCHAR2(10)
crap2 DATE

table2
poop1 VARCHAR2(10)
poop2 DATE
poop3 CLOB

SELECT crap2,poop2
FROM table1@remote JOIN table2@remote ON crap1 = poop1;

ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables

SELECT crap2,poop2
FROM table1@remote, table2@remote WHERE crap1 = poop1;

data
glorious data

There is nothing we geeks hate more than code that should work but doesn't. Especially when the people we work for are paying five, six, seven or more figures for this software. The problem was originally noticed over two and a half years ago!

M*^!@f^((#&$

Customizable classes for objects

2007-11-06T12:07:00.000-05:00

Thoughts on object oriented programming, classes, objects and polymorphism

I am in the middle of a project that involves code from another organization. This organization is trying to make their code as flexible as possible through configuration settings, user only classes and templates. I also maintain code for another system from another organization that provides no flexibility for customization of code. I starting thinking, did some Google searches, and didn't find anything; so I'm putting my thoughts here.

The typical class design model I see in all media is the base class containing all the base functionality then you extend that class one or more times to provide additional logic. So myFoo extends Foo. Foo contains a lot of good methods and logic but my organization needs one additional property and method; myFoo is created. Here is where I start thinking this sucks. Any class that instantiates Foo, must now instantiate myFoo instead.

If the external organization delivers those classes we changed to use myFoo, now we must go back and make those changes again.

Wouldn't it make sense to deliver a Foo that extends a baseFoo, have all the classes that use Foo just use Foo? The developing organization makes changes to the baseFoo classes and the customizing organizations make customizations to the Foo classes.

This method of class design is just as vulnerable to catastrophic changes as the widely taught method. If you change the data schema you will still need to change the data objects, business objects, presentation objects, etc. Nothing will prevent that mess for occurring or, well, being a mess. If, however, a hundred classes use Foo would it make sense to change all (or ones that need) to use myFoo?

Blast from the past

2007-10-23T18:16:00.000-04:00

I came across some really old "vanity web page" crap I did back in 1998. It was just a start and never got published but, hey.

Bachelor Necessities

As a bachelor with many (too many) years of experience I feel it is my duty to document my experience somewhere. For those of you about ready to embark on the journey of bachelor life or those already on the trodden path, here are some things you might want to pick up along the way.

A Mini Fridge: This really isn't a necessity but something, for some strange mystical reason, a guy must have. Typical uses: beer. Typical placement: garage, basement, bedroom, television room - wherever you spend the most time. Estimated cost, $100.
George Foreman Grill: God bless you George "look at the lines, that's good eatin'" Foreman. This little bugger is so helpful and so easy to use any guy, no matter how dumb you are, can master one. Doesn't do a good job on steaks with bones in them but is perfect for frozen chicken breasts (don't bother thawing, just chuck'em on), burgers, fish, and just about all meats. Estimated cost, about $70.
An Outdoor Grill: You can go propane but you'll find you get the most use out of a small charcoal grill you can take with you in your car or let rust on your back porch/front step. Estimated cost, about $30.
Crock Pot: Another gift from the heavens to the bachelor. Just follow the recipes that come with the device. So easy, I was actually able to teach my dumb-ass complete waste of an ex-friend "L" how to cook. See my chili recipe. Estimated cost, about $40.
Microwave: Like, duh. Just buy a good one then treat it well and it will outlast you, all the women you are intimate with, and probably every dwelling you'll ever be in (even your casket). Estimated cost, who the hell knows - I bought mine ten years ago.
Big Screen TV: Even if you are stuck in the bedroom of your parent's house, you need a big screen TV. The big screen TV represents the following: a symbol of your manliness you can share with friends at football parties, a showcase for your best DVD movies and/or your porn collection, and a constant reminder of just how in debt you really are. Estimated cost, about $3600 (gotta get a good one, man).

Chili ala' Grouch

If you read my "bachelor necessities" above you should have noticed a crock-pot as one of the items there. This is what I use because a) I'm lazy, b) I'm a guy and need the flexibility of a crock-pot to hide my cooking inadequacies.

Ingredients

Two pounds of ground chuck or ground round (i.e. raw hamburger)
A can of chili beans
A can of tomato sauce (name brand, no generic)
A can of tomato paste
A can of diced tomatoes (or cut up a bunch of tomatoes, no seeds)
A can of green chilies (usually found in "Mexican" section)
One stalk of celery
One onion
Two green peppers
One jalapeno pepper
Two or more cloves of garlic (comes in bulbs, peel them to get cloves)
Chili powder (spice)
Pepper (the kind you shake)
One bay leaf (another spice) or Chinese 5-spice (yes, a it's a spice too)
Cumin (a spice)

Instructions

Brown the ground beef (this means you cook the hamburger until it turns brown, I had a family member that wondered why his chili had so much blood at the top - he was adding it raw). Here is how I do it: a) throw the wad on my George Foreman Grill, b) insert the sliced cloves of garlic into the meat, c) liberally add pepper and chili powder, d) grill, e) crumble into the crock pot.
While your biff is browning, cut up the veggies. Put the onion, celery, and jalapeno (and garlic) into the crock.
Open the cans. Dump in the green chilies.
Drain the grease from the beef (or don't, I don't care) and put it in the pot.
Add the bay leaf (or a couple shakes of 5-spice), a pinch of cumin, some pepper, some chili powder.
Dump in the tomato paste, then beans, then tomato sauce, then rinse cans with about a cup (measuring cup, i.e. a little bit) of water then dump into crock.
Start cookin (5-7 hours on high, 10-12 hours on low).
About one (high) or two (low) hours before the chili is done, stir in the green peppers. The reason I do this is because when I added the peppers at the beginning that's all I could taste in the chili - green peppers.
When done cooking, serve hot and enjoy with a beer. Don't ruin with crackers or I'll shoot you.

Variations: Tex/Mex". Replace can of chili beans, stalk of celery, and green peppers with black and/or pinto beans, a half a can of corn, a red pepper and a yellow pepper. Unlike the chili beans (which usually come in a sauce), you should drain and wash the black/pinto beans.

Dealing with Depression

If you are like me, and (according to some news sources) like over thirty percent of the population in the United States, you deal with depression on (sometimes) a daily basis. These feelings usually manifest themselves in the form of feelings of hopelessness or you might feel tired all the time.

What not to do.

Drugs are NOT the Answer.

When I say drugs I am describing anything from prescription drugs like Prozac to binge drinking. Drugs are only a temporary solution to the problem and usually make things worse. I have seen some co-workers on the drug Prozac and they are both psychos, one step away from full-out fruitcake. I have family members on different prescriptions and they aren't any better. One has been on various prescriptions for twenty (yes, 20) years. The physicians just keep giving him the line "let's try this one". These quacks have no idea what they are doing, they just continue to milk the system. One of my friends smoked marijuana (i.e. pot, reefer, weed) to escape his depression. That loser is still depressed and now half his tenth-grade brain is gone. The common thread is that all of these people are still taking drugs and they are all still just as depressed, if not more than they ever were.

Shrinks are NOT the Answer

If you a rock star or some wealthy Hollywood name, then a "shrink" might work for you. For the rest of us that need to work for a living, you might as well just stop someone on the street or sit next to someone at a fast food joint because you are likely to get the same answers. Basically you pay a "shrink" to listen and make observations but every single session boils down to a prescription (see above). They do not have the answers or if they do they are canned answers you could probably get from a box of cereal or Miss Cleo. I saw a psychiatrist once, and that was enough. There are no easy answers to anything, but there is something that can be prescribed.

Is God the answer?

Maybe… Part of the problem with religion or other social groups is that they remove your self-esteem by placing everything in someone else's hands. Instead of depending upon yourself, being responsible, and understanding your strengths and weaknesses, everything is now controlled by an external entity (i.e. God or insert religious icon here). It works for you if you can completely give yourself. For most people it works temporarily until that first bout of doubt sets in, then there is the possibility

My Solution?

Be depressed.

Seriously. Believe it or not, depression is a completely normal state of being just as happiness, sadness, ecstasy, and indifference are. It's okay to be depressed. If you are depressed, chances are good there is a reason why you are depressed. A couple of days later, you might still be depressed or you might do a one-hundred percent turn-around and be glowing with joy. It might even take a month or a year or your entire puberty. It's part of life. Depression makes happy days happier but can be overwhelming at times. Most people that "suffer" from depression have succumbed to it. Don't succumb to it. When the day is done, go over to the calendar and mark off the day because it's over.

Quick comments on CentOS 5

2007-10-01T12:15:00.000-04:00

I have installed CentOS 5 on three machines so far; here are some notes:

all three machines needed the boot time linux resolution:1024x768 to get a usable display on the anaconda installer and lvm is not available if you install in text mode
it took four hours to get a workable desktop install (including updates, not including additional software)
on an IBM Thinkcentre I've had to disable then enable the network interface each time to get Internet connectivity
the Gnome desktop looks nice, a little cleaner than Ubuntu (I like the blue and themeset)

Dick doodles

2007-09-19T07:06:00.000-04:00

Some of the greatest cartoons in the world started out in very perverse ways.

C#/.Net vs Java vs ?

2007-08-28T09:50:00.000-04:00

C#/.Net seems like a solid language and framework but you are locked into Microsoft for just about everything.

Java has so many frameworks and layers that it's hard to keep track of all of them. Then again, it has support from IBM, Oracle on the high-end and in the Open Source world and can be lightweight to almost overkill (EJB).

A few things to think about

2007-08-02T12:21:00.000-04:00

Can you cry under water?
How important does a person have to be before they are considered assassinated instead of just murdered?
Why do you have to "put your two cents in".. but it's only a "penny for your thoughts"? Where's that extra penny going to?
Once you're in heaven, do you get stuck wearing the clothes you were buried in for eternity?
Why does a round pizza come in a square box?
What disease did cured ham actually have?
How is it that we put man on the moon before we figured out it would be a good idea to put wheels on luggage?
Why is it that people say they "slept like a baby" when babies wake up like every two hours?
If a deaf person has to go to court, is it still called a hearing?
Why are you IN a movie, but you're ON TV?
Why do people pay to go up tall buildings and then put money in binoculars to look at things on the ground?
Why do doctors leave the room while you change? They're going to see you naked anyway.
Why is "bra" singular and "panties" plural?
Why do toasters always have a setting that burns the toast to a horrible crisp, which no decent human being would eat?
If Jimmy cracks corn and no one cares, why is there a stupid song about him?
Can a hearse carrying a corpse drive in the carpool lane?
If the professor on Gilligan's Island can make a radio out of a coconut, why can't he fix a hole in a boat?
Why does Goofy stand erect while Pluto remains on all fours? They're both dogs!
If Wiley E. Coyote had enough money to buy all that ACME crap, why didn't he just buy dinner?
If corn oil is made from corn, and vegetable oil is made from vegetables, what is baby oil made from?
If electricity comes from electrons, does morality come from morons?
Do the Alphabet song and Twinkle, Twinkle Little Star have the same tune?
Why did you just try singing the two songs above?
Why do they call it an asteroid when it's outside the hemisphere, but call it a hemorrhoid when it's in your butt?
Did you ever notice that when you blow in a dog's face, he gets mad at you, but when you take him for a car ride; he sticks his head out the window?

LVM on Linux

2007-07-27T14:38:00.000-04:00

I feel stupid for saying this but I played around with LVM on Ubuntu Linux for the first time today. I need to get out more. The benefits to LVM are flexibility. The major downside is critical failure of a physical volume will screw all logical volumes in that pool. Ubuntu Linux has LVM almost completely automated; just choose use the whole disk with LVM and it creates a non-LVM boot partition then a physical volume with the rest. Easy. Adding another physical disk was easy:

root@homer:~# fdisk -l

There will be a message similar to this one, "Disk /dev/sdb doesn't contain a valid partition table", if the disk is brand new. If not, the partition table might require some modifications.

root@homer:~# fdisk /dev/sdb

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1): 
Last cylinder or +size or +sizeM or +sizeK (1-130, default 130):
Using default value 130

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

The pvdisplay command can be used to determine the volume group name (VG Name) and display physical volumes. Now to create the physical volume, extend the volume group to include the new physical volue, then create a logical volume within the group.

root@homer:~# pvcreate /dev/sdb1
  Physical volume "/dev/sdb1" successfully created

root@homer:~# vgextend Ubuntu /dev/sdb1
  Volume group "Ubuntu" successfully extended

root@homer:~# lvcreate --name share_01 --size 1016M Ubuntu
  Logical volume "share_01" created

Once created, put the ext3 filesystem on the new logical volume

root@homer:~# mkfs.ext3 /dev/Ubuntu/share_01
mke2fs 1.38 (30-Jun-2005)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
130048 inodes, 260096 blocks
13004 blocks (5.00%) reserved for the super user
First data block=0
8 block groups
32768 blocks per group, 32768 fragments per group
16256 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376

Writing inode tables: done
Creating journal (4096 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 31 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.

Create a mount point then modify the fstab so this new volume mounts.

mkdir /share
root@homer:~# cp /etc/fstab /etc/fstab_20072707
root@homer:~# vi /etc/fstab

Note the backup of fstab made! Reboot the machine and the new logical volume should be available on the mount point

Adding another drive to the pool was just as easy:

root@homer:~# fdisk /dev/sdc

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-130, default 130):
Using default value 130

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
root@homer:~# pvcreate /dev/sdc1
  Physical volume "/dev/sdc1" successfully created
root@homer:~# vgextend Ubuntu /dev/sdc1
  Volume group "Ubuntu" successfully extended

To increase the size of the shared volume:

root@homer:~# umount /share
root@homer:~# lvextend -L2032M /dev/Ubuntu/share_01
  Extending logical volume share_01 to 1.98 GB
  Logical volume home_01 successfully resized
root@homer:~# e2fsck -f /dev/Ubuntu/share_01
e2fsck 1.38 (30-Jun-2005)
Pass 1: Checking inodes, blocks, and sizes
Pass 2: Checking directory structure
Pass 3: Checking directory connectivity
Pass 4: Checking reference counts
Pass 5: Checking group summary information
/dev/Ubuntu/share_01: 11/130048 files (0.0% non-contiguous), 8197/260096 blocks
root@homer:~# resize2fs /dev/Ubuntu/share_01
resize2fs 1.38 (30-Jun-2005)
Resizing the filesystem on /dev/Ubuntu/share_01 to 520192 (4k) blocks.
The filesystem on /dev/Ubuntu/share_01 is now 520192 blocks long.

root@homer:~# mount /dev/Ubuntu/share_01 /share_01

Too easy... Here are some links that were very helpful:

On through the Blog-o-sphere

2007-07-19T12:27:00.001-04:00

Occasionally, usually at work when my brain has numbed to the point of being able to be attached to a pole and used to mop floors, I'll use the Next Blog button at the top and just go through the Blog-o-sphere. Here is what I found:

1 epileptic seizure inducing
4 done by 18-21 year old that write like 8 year olds
1 well done, with good content
3 Orientals with nothing better to do than spend money and post pictures of what they ate that day
2 foreign language, living in America complaining about America
1 learning English, living in America, and complaining about America
9 foreign language, outside of America
1 horribly hip, teenage / college student with six zillion picture phone images
1 test / single nothing post
2 myspace wannabe, break stylesheet, l337 individual
1 foreign student in America
1 cool, but with a hundred embedded youtube objects
1 ugly with a hundred embedded youtube objects and link buttons
1 spam
1 American documenting her travels abroad
1 comic book related
1 real estate broker

The good news is the spam blogs are disappearing and almost gone. Also, the myspace wannabe and seizure inducing blogs are down as well. Hopefully they all went to myspace where it's okay to have six jillion youtube objects, pictures, horrifying templates and all sorts of cool but worthless crap. There are a lot of foreign blogs now, which I can't read. The blogs written in English are written at a kindergarden level. Oh well, I can't complain though. I'm certainly not perfect. I do think the blogging craze is starting to die down a little; maybe youtube and similar directions are now the cool thing and blogging is just, ick, words.

Overpriced on eBay

2007-07-19T12:03:00.000-04:00

Sometimes I just don't understand buyers on eBay; sellers, too.

I've been watching a specific Dell LCD for a little while now. It retails from Dell at around $320 brand spanking new shipping and tax included. Someone just bought a used one, no warranty, no return instructions from the seller for $321 after shipping on eBay; and they probably would have spent more. The other four almost identical options are shaping up to be similar bidding slap-fests that will end near or over the purchase price for a brand new item with warranty and vendor support.

One seller was selling SATA 150 drives as SATA II drives. When I asked a question about the model number not being the same as a spec from the auction it went unanswered. This seller usually opens up a lot of six to eight of these with very short open times, around a day or less.

I like Shuttle computers, the mini-xPC a.k.a. shoebox computers. A lot of them are being sold as "normally sell for $300 or more"; which is not the case. Not really true for these, though, more like $240 tops and for older technology. You can buy a newer model with current socket / chipset for around $190 plus the memory and processors are actually priced the same or cheaper for more capacity.

Blogger to mySQL

2007-07-17T09:00:00.000-04:00

The goal of this process was to get the Blog of the Dead (the story blog I work on) into a mySQL database at another location so new readers could start from the beginning then read the blog chronologically starting with the first gripping hours up through where I got bored with the whole thing. Blogger really wasn't designed to relive a story or sequential posts but it suited my purposes at the time.

The mySQL table uses the post date and time as the primary key and includes the author, Blogger permalink, post title and post body. I didn't save comments. Originally I looked at the Google API set but that only seemed to work with recent posts and you had to have feeds enabled.

Note: this only works for Bloggers using the classic templates (like me).

Step 1: Save your existing template

Copy the code for your existing template and save it somewhere locally, you will need to restore it later

Step 2: Replace your template

<Blogger><$BlogItemDateTime$>»<$BlogItemTitle$>»<$BlogItemAuthor$>»<$BlogItemPermalinkUrl$>»<$BlogItemBody$>
«</Blogger>

Step 3: In Settings | Formatting

set Show to 999 days on main page
set Timestamp format to MM/DD/YYYY HH:MM:SS AM|PM
set Convert line breaks to no

Step 4: In Settings | Archiving

set Archive Frequency to No Archive

Step 5: View your blog

Save the source for your blog somewhere locally

Step 6: Reset everything

Reset your formatting and archiving settings then template back to their original settings and content.

You should now have a text file that will need a little massaging to get into mySQL. I used UltraEdit to get the date/time into an ISO standard format and convert Windows CR/LF combinations to simple CR's. The resulting text file can be locally loaded with no column enclosures, no escape characters, \r plus the line terminator character as record ends, and the column separator character as your column separator. I'll revist this later with more details instructions with SED, etc.; I simply wanted to get my notes stored somewhere.

VMWare Server networking tips

2007-07-05T13:03:00.000-04:00

If you are installing a Linux operating system and using two virtual network interfaces: one bridged (i.e. can see the real interface and get to the world) and one host-only, define the host-only interface first then when finished with the virtual machine wizard add the bridged interface. The host only will be assigned eth0, the bridged eth1. On installation each will use DHCP and the nameservers for eth1 will by default override those for eth0.

Testing 1-2-3

2007-06-25T09:08:00.000-04:00

I want to see how labels are handled with classic templates.

<p>I want to see how labels are handled with classic templates.<p class="blogger-labels">Labels: <a rel='tag' href="http://grouchgeek.blogspot.com/search/label/testing">testing</a></p></p>

It looks like Blogger just inserts a paragraph for you at the end and from the looks of the documentation you have no control over that. Nice...

Crew cases, part two

2007-06-23T13:42:00.000-04:00

Just a quick update. The painting is done on the two cases. The yellow case; well the paint job didn't quite turn out perfect and I gave up trying to get a nice smooth gloss after three coats of color and two clear coats. It looks good from a distance or on casual notice but when you get close you notice the dull spots. Oh well.

Still left to do are the Crew logo, the "Columbus Crew" on one of the 5 1/4" drive panels, and possible the LH in a circle (for Lamar Hunt). The black case is fully operational with and AMD X2 processor, 4GB ram, 500GB and 80GB x 2 RAID-0 running Ubuntu Linux 64bit 7.04 desktop. This machine will serve as a VMWare Server server development system with images on the striped array; with management, squid cache, apache, tomcat, mysql and maybe Oracle images. Fun. The yellow will be a Windows 2003 Server based development machine and swap out drive sets with Linux based server development.

Informative links about computers

2007-06-14T10:03:00.000-04:00

Just putting this here for my own personal reference and might build on it in the future; or have other people build on it.Power supply buyer's guide

No more FOX in the mornings

2007-06-11T08:57:00.000-04:00

I am no longer going to wake up on the weekends to watch FOX and Friends on weekend mornings. This weekend on Bulls and Bears there were two blondes, one arguing and spouting off one-sided political vomit about another worthless blonde that deserves zero publicity. Ann Coulter discussing Paris Hilton. Good lord. One annoying stick figure she-male talking about a talentless socialite stick-figure.

That's it. No more.

I almost tuned out when they took Juliet Huddy off but then there was Kiran Chetry to look at but now Kiran is with CNN replaced by Gretchen Carlson and now Page Hopkins. I like Page but she's no Juliet or Kiran.

Ann Coulter discussing Paris Hilton was the last straw, though. Two days later and my stomach is still sore and unable to accept anything stronger than pudding or yogurt. (And I do realize that discussing this is actually "publicity" for both but if some lowly blog like this one is "publicity" then that actually makes me feel a little better about the rest of the world that thinks both need to be forever hidden from the public eye in a jail or sealed in lucite or carbonite or something).

Keeping CPUs busy

2007-06-06T13:12:00.000-04:00

I guess I either don't understand the finer points of computer performance or don't understand why some people don't understand what I consider to be simple knowledge about computer performance.

"The CPUs on the server are running at high utilization, 60 to 80 percent"

Okay. So?

Are you paging (swapping) heavily? No. Is the disk I/O rate normal, low or very heavy? It's about normal. Is there a looping or run-away process or service? No.

Then what is the problem? (this isn't happening with my current employer, by the way)

It seems to me that if you aren't paging then you are not using all of your server's memory, else you would be swapping and that could be bad. If there are no looping processes then the software and operating system are functioning normally. If the disk (or network) I/O are normal then the software is reading and writing data like it should be. Again, what is the problem because it sounds to me like the CPUs are doing what they are supposed to be doing, utilizing themselves and if your I/O rates aren't through the roof then you are probably getting a portion of the CPU utilization through application buffer and cache hits (a really good thing, by the way). You don't spend a dump-truck full of money on a bunch of processors just to have them sit there at 10 percent utilization most of the time; you want them doing work! Throw more memory at them to boost cache and buffer hit rates or if that doesn't seem to help (which sometimes it doesn't; there is a sweet spot when doing read caching just because you can only cover so much especially when data is scattered on disk) maybe consider moving temporary spaces to memory (or covering them heavily).

The point is: you want your server processors to be busy if there is work to be done. Maybe I'm wrong or just don't understand things correctly but it seems like putting a motorcycle engine on a scooter; if you're never going to have the thing above 2 miles per hour then why all the horsepower? I see the same thing on the workstation. If all you are going to be doing is surfing the Internet, typing memos and using central application software do you really need a 2.8Ghz P4 sitting there doing nothing? I got in an argument with a pro-Firefox guy recently. He said Opera can use over 160 MB of memory. So? Is any other application using it? No. Then what harm? I'd rather have Opera intelligently caching as much as possible while there is no effect on other applications. "Well it shouldn't use that much!" Sigh.

IBM has a technology called Capacity on Demand (CoD) "so companies don't have to pay for computing resources until they use them." They have on/off and reserve processing that can be activated in increments of time (like a day) for end-of-period processing or for sporadic work loads. The company I work for has already had some fun with it. There are other aspects of CoD and it is a decent idea but I have to believe that IBM isn't in it just to keep customers happy; they are making their money.

Subliminal ideas

2007-06-05T12:27:00.000-04:00

Ever been on hold with some company, usually a tech support line or any other cost center that earns them zero cash, and listened to that grainy music? Ever wish when you had a caller on the line that just talks and talks and talks and talks and talks forever about nothing and just wants to vent or occupy your time? Ever wish you could put that person on hold, press a button and have something subliminal play within the music while they were on hold? I think I'm onto something here.

Lets say I'm some cable company where ninety percent of calls are people saying the cable is out even though you have a message up front saying so. When this person actually gets through they could have on-hold music that subliminally repeats "hey lard-ass, the cable will be back shortly why don't you go for a walk or ride a bike for a while?" Maybe the on-hold subliminals for a sales line would be something like "buy this product and you sure to be getting oral pleasure from your spouse or spousal life acquaintance".

Of course, the applications are endless. And they don't necessarily have to be devious, do they? How about a calm, reassuring "your mind is calm, your life is happiness" while on hold? Maybe not at a hospital or funeral home but you get the idea.