Sunday, July 6, 2008
Process
I read The Daily WTF, well, daily. On Thursday last week, there was a good one on process. Essentially, the entire process had to be followed when an error occurred at boot. F1 would have solved the problem immediately...
My first job I never really got to put anything into production, so I wasn't real familiar with it. My second job, I was the lone ranger, so I did everything myself (though I did not do development in production). My last job however, was full of "The Process."
Rightfully so, especially in a large environment (i.e. more than 1 developer), though I think it was a bit overdone. And up until one of my failed deployments, the deployment itself was done through the Change Request (CR). What I mean by that, is that the code was attached to the CR itself. Since I attached a newer version, which had not been QA'd, well, you get the picture. We finally moved to a system whereby the DBAs actually deployed from our source control system...thankfully.
Now I'm in an environment that's a mix between the last job and the second to last. Everything is QA'd, but there isn't this whole process surrounding deployments...yet. Fortunately we're small enough to deal with it.
What's the point? I'm not sure.
Perhaps it's that I've learned more what not to do from The Daily WTF...
My first job I never really got to put anything into production, so I wasn't real familiar with it. My second job, I was the lone ranger, so I did everything myself (though I did not do development in production). My last job however, was full of "The Process."
Rightfully so, especially in a large environment (i.e. more than 1 developer), though I think it was a bit overdone. And up until one of my failed deployments, the deployment itself was done through the Change Request (CR). What I mean by that, is that the code was attached to the CR itself. Since I attached a newer version, which had not been QA'd, well, you get the picture. We finally moved to a system whereby the DBAs actually deployed from our source control system...thankfully.
Now I'm in an environment that's a mix between the last job and the second to last. Everything is QA'd, but there isn't this whole process surrounding deployments...yet. Fortunately we're small enough to deal with it.
What's the point? I'm not sure.
Perhaps it's that I've learned more what not to do from The Daily WTF...
Labels: development, process, work
¶ 7/06/2008 11:18:00 PM 0 Comments Links to this post
Tuesday, July 1, 2008
Lookup Tables
Also known as reference, crosswalk and a few other names.
I love 'em.
I'm not afraid to use them. It certainly makes that table count go up, but you know what you're getting.
I have ADDRESS_TYPES, PHONE_TYPES, PERSON_TYPES (in an intersection table of course) and any other kind of TYPE you can imagine.
I could use CHECK constraints I guess, but if it's anything other than Y or N, I typically create a lookup table to go with the table.
Let's take an ADDRESS table. ADDRESSTYPECODE becomes an attribute of an address. It gets a Foreign Key to the ADDRESS_TYPES table and also (many seem to leave this one out), a NOT NULL constraint. Every address has to have an type.
To make it somewhat easier, I use codes (as opposed to IDs which I tend to associate with numbers) so a join isn't absolutely necessary. If 'HOME' is the ADDRESSTYPECODE, you would rarely need to join as it's self evident what that means. If the lookup table is large, I'd typically use ID (or numbers) for the key.
Like I said, it bumps up that table count and makes things look a bit "messy," but you know exactly what belongs in what column. And if you're using ApEx, administrative screens are a snap!
Just don't ask Duke Ganote whether type is a good name or not!
I love 'em.
I'm not afraid to use them. It certainly makes that table count go up, but you know what you're getting.
I have ADDRESS_TYPES, PHONE_TYPES, PERSON_TYPES (in an intersection table of course) and any other kind of TYPE you can imagine.
I could use CHECK constraints I guess, but if it's anything other than Y or N, I typically create a lookup table to go with the table.
Let's take an ADDRESS table. ADDRESSTYPECODE becomes an attribute of an address. It gets a Foreign Key to the ADDRESS_TYPES table and also (many seem to leave this one out), a NOT NULL constraint. Every address has to have an type.
To make it somewhat easier, I use codes (as opposed to IDs which I tend to associate with numbers) so a join isn't absolutely necessary. If 'HOME' is the ADDRESSTYPECODE, you would rarely need to join as it's self evident what that means. If the lookup table is large, I'd typically use ID (or numbers) for the key.
Like I said, it bumps up that table count and makes things look a bit "messy," but you know exactly what belongs in what column. And if you're using ApEx, administrative screens are a snap!
Just don't ask Duke Ganote whether type is a good name or not!
Labels: apex, constraints, database, design, development
¶ 7/01/2008 09:52:00 PM 3 Comments Links to this post
Thursday, June 5, 2008
BULK COLLECT and FORALL
I have a task to tune a little bit of SQL. It's very ETL like, but the target is not (yet) a star schema, it's pretty much a table flattened out for reporting purposes.
As I reviewed it, I noticed it went row by row, with a COMMIT inside the LOOP. That's gotta go.
Can I do this in one SQL statement? No, there's other processing that needs to be done (UPDATE two other tables before and after). Hmmm...could I just return the appropriate records into a collection? I'll have to look at that to see if it's possible.
For now though, I am going to try and use BULK COLLECT with the LIMIT clause and FORALL for the processes that occur before and after.
Let's create some data:
So I can bulk bind, but I have to INSERT into the table as a whole. I can't be selective.
I updated my code to this:
BULK COLLECT and FORALL are great tools if you can't do it in a single SQL statement and if you want to avoid the row by row processing.
As I reviewed it, I noticed it went row by row, with a COMMIT inside the LOOP. That's gotta go.
Can I do this in one SQL statement? No, there's other processing that needs to be done (UPDATE two other tables before and after). Hmmm...could I just return the appropriate records into a collection? I'll have to look at that to see if it's possible.
For now though, I am going to try and use BULK COLLECT with the LIMIT clause and FORALL for the processes that occur before and after.
Let's create some data:
OK, now let's create an anonymous block, BULK COLLECTing the data from T into a PL/SQL table and then populated another table with that data:
CREATE TABLE t( x NUMBER, y NUMBER );
INSERT INTO t( x, y )
SELECT
TRUNC( dbms_random.value( 1, 99999999 ) ),
TRUNC( dbms_random.value( 1, 100000 ) )
FROM dual
CONNECT BY level < 1001;
And then I run it and I get the following:
DECLARE
TYPE t_record IS TABLE OF T%ROWTYPE;
l_table T_RECORD;
CURSOR c
IS
SELECT x, y
FROM t;
BEGIN
OPEN c;
LOOP
FETCH c
BULK COLLECT INTO l_table
LIMIT 100;
FORALL i IN 1..l_table.COUNT
INSERT INTO s ( x, y )
VALUES ( l_table(i).x, l_table(i).y );
EXIT WHEN C%NOTFOUND;
END LOOP;
CLOSE c;
END;
/
A quick google search and I end up here .
ERROR at line 18:
ORA-06550: line 18, column 16:
PLS-00436: implementation restriction: cannot reference
fields of BULK In-BIND table of records
ORA-06550: line 18, column 16:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 30:
PLS-00436: implementation restriction: cannot reference
fields of BULK In-BIND table of records
ORA-06550: line 18, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 16:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 17, column 7:
PL/SQL: SQL Statement ignored
So I can bulk bind, but I have to INSERT into the table as a whole. I can't be selective.
I updated my code to this:
I run it and it completes successfully. This is all on XE, so I wonder (hope) that 11g will allow me to do what I want (I'll be working on an 11g RAC system). I scroll down the list of google results and I find this one which then takes me to AskTom. The first post demonstrates that my first attempt will work on 11g.
DECLARE
TYPE t_record IS TABLE OF T%ROWTYPE;
l_table T_RECORD;
CURSOR c
IS
SELECT x, y
FROM t;
BEGIN
OPEN c;
LOOP
FETCH c
BULK COLLECT INTO l_table
LIMIT 100;
FORALL i IN 1..l_table.COUNT
INSERT INTO s
VALUES l_table(i);
EXIT WHEN C%NOTFOUND;
END LOOP;
CLOSE c;
END;
/
BULK COLLECT and FORALL are great tools if you can't do it in a single SQL statement and if you want to avoid the row by row processing.
Labels: development, howto, oracle, plsql, sql
¶ 6/05/2008 10:14:00 PM 7 Comments Links to this post
Monday, May 12, 2008
ApEx: What is it?
Surprisingly it seems, very few Oracle professionals know about ApEx. At WellCare, no one knew about it. Many others I have talked to have no idea what it is.
How can this be?
I'm hard-pressed to believe I am an early adopter. I like to think of myself as such, but perception and reality are two totally different things.
In a nutshell, ApEx (or Application Express, aka HTMLDB) is a rapid application tool that allows you to quickly build web based applications on top of an Oracle database. If I remember correctly, it was initially billed as the Microsoft Access killer.
It is also the front end for Oracle XE.
I've personally built 3 (professional, i.e. paid for) ApEx applications. One for my former day job with 350 pages (1.5 years to build and maintain), one for my fellow baseball alumni at the University of Florida, and one for my now defunct business, CABEZE.
I believe the question I get most often is: "Can you format it?"
Which I take to mean can you make it pretty or design it anyway you want?
The answer to that is a resounding "Yes!"
So, what follows is a list of ApEx applications for you to evaluate on style and design (borrowed from the unofficial ApEx wiki housed at shellprompt):
In summary, you can do with ApEx what you can do with any other web tool/environment. Want AJAX? Done. CSS? Done.
You're only limited by your imagination.
(Polls are fun...I'll stop apologizing for it soon)
How can this be?
I'm hard-pressed to believe I am an early adopter. I like to think of myself as such, but perception and reality are two totally different things.
In a nutshell, ApEx (or Application Express, aka HTMLDB) is a rapid application tool that allows you to quickly build web based applications on top of an Oracle database. If I remember correctly, it was initially billed as the Microsoft Access killer.
It is also the front end for Oracle XE.
I've personally built 3 (professional, i.e. paid for) ApEx applications. One for my former day job with 350 pages (1.5 years to build and maintain), one for my fellow baseball alumni at the University of Florida, and one for my now defunct business, CABEZE.
I believe the question I get most often is: "Can you format it?"
Which I take to mean can you make it pretty or design it anyway you want?
The answer to that is a resounding "Yes!"
So, what follows is a list of ApEx applications for you to evaluate on style and design (borrowed from the unofficial ApEx wiki housed at shellprompt):
- Dance Tunes - arguably the best designed ApEx application out there.
- AskTom - We all know, or should know, this site. 40,000 page hits a day on a 4 CPU box.
- Metalink - In my opinion not the best implementation of ApEx, but it's a busy site.
- Shellprompt - host your applications here for a very reasonable price.
- View other examples here
In summary, you can do with ApEx what you can do with any other web tool/environment. Want AJAX? Done. CSS? Done.
You're only limited by your imagination.
(Polls are fun...I'll stop apologizing for it soon)
Labels: apex, development, oracle
¶ 5/12/2008 07:34:00 PM 12 Comments Links to this post
Thursday, April 17, 2008
Failed Deployments: An Index
Since there seem to be so many now, I'm creating this index page to track them. Enjoy!
The Countdown Timer - a brief not on the origin of the countdown timer adorning my site.
Good Day to Worse Day - This is the day that the countdown timer was first started.
DELETEng an entire production table.
Blowing up the Rate Application.
The Countdown Timer - a brief not on the origin of the countdown timer adorning my site.
Good Day to Worse Day - This is the day that the countdown timer was first started.
DELETEng an entire production table.
Blowing up the Rate Application.
Labels: development, failed, humility, work
¶ 4/17/2008 09:01:00 PM 0 Comments Links to this post
Tuesday, April 15, 2008
How Do You Audit?
I'm not necessarily talking about system auditing, which I understand to be pretty much like throwing a switch, I'm talking about table level auditing.
Given the following table:
I typically do something like this:
I know there are other ways, but I'd like to hear some of your ideas/methods.
Updated
I realized that I should not have put ID as the primary key, I should have used a unique key (SSN) instead but generated a surrogate key (ID). I've updated it to use SSN as the unique key, so the primary key can and will change (it's a surrogate), but the SSN cannot.
Updated II
Then it would fail when you create a new record because the SSN would no longer be unique. Oy, I usually read and re-read anything technically related.
So I'll remove the UNIQUE constraint from SSN and that should do the trick. SSN will be used to find the record of the appropriate person, along with END_DATE IS NOT NULL (a "current" row).
Given the following table:
The requirement is to track any changes to everything but the SSN. ID is just a surrogate key.
CREATE TABLE t
(
id NUMBER(15) PRIMARY KEY,
ssn VARCHAR2(9)UNIQUE,
last_name VARCHAR2(40),
first_name VARCHAR2(30),
dob DATE
);
I typically do something like this:
I added the create_ and end_ columns to see who did what when. If someone comes in and decides to change the record, the end_date and end_user are populated (thereby "terminating" the record) and a new record is created with the updated values. That then becomes the "current" record.
CREATE TABLE t
(
id NUMBER(15) PRIMARY KEY,
ssn VARCHAR2(9)UNIQUE,
last_name VARCHAR2(40),
first_name VARCHAR2(30),
dob DATE,
create_date DATE,
create_user VARCHAR2(30),
end_date DATE,
end_user VARCHAR2(30)
);
I know there are other ways, but I'd like to hear some of your ideas/methods.
Updated
I realized that I should not have put ID as the primary key, I should have used a unique key (SSN) instead but generated a surrogate key (ID). I've updated it to use SSN as the unique key, so the primary key can and will change (it's a surrogate), but the SSN cannot.
Updated II
Then it would fail when you create a new record because the SSN would no longer be unique. Oy, I usually read and re-read anything technically related.
So I'll remove the UNIQUE constraint from SSN and that should do the trick. SSN will be used to find the record of the appropriate person, along with END_DATE IS NOT NULL (a "current" row).
Labels: database, design, development
¶ 4/15/2008 07:44:00 PM 5 Comments Links to this post
Tuesday, March 25, 2008
Use the [Oracle] Database dammit!
Dom Brooks recently posted an article about the Dea(r)th of the Oracle RDBMS. It seemed to struck a chord.
I've written about MySQL Friday or Application Developers vs. Database Developerswhich were similar in thought; the database is a bucket.
Ultimately, my take is that application developers don't know and don't want to learn how to use a database. PL/SQL specifically, is a platform in and of itself. You can do so much in the database now that you essentially need an application only for display, to determine the row color if you will.
The usual caveat follows:
If you are building applications that are supposed to be database independent, then the logic belongs in the application. The database is a bucket.
If you are building business applications specific to Oracle though, use the damn thing. Application/web developers are then forced to work on the design and user interface, not application/transaction logic.
Easy steps to actually utilize your database:
1. Use as many constraints as humanly possible - This will reduce the amount of code you have to write and you'll have the security of knowing the data will be what you constrain it to be.
2. DEFAULT columns in table definitions - create_date or load_date can be default to SYSDATE and thus left out of any application code. I've gone so far as to use SYS_CONTEXT( 'MY_CONTEXT', 'USERID' ) as the DEFAULT value for the create_user column. That along with a NOT NULL (or CHECK) constraint, makes life that much easier.
3. Did I mention constraints? Primary Key and Foreign Key constraints are very important to maintain data integrity (ensure you have the data you expect). Don't forget to index those foreign keys.
4. Security - VPD (Virtual Private Database) or Fine Grained Access Control. No longer do you need to maintain two separate schemas (or databases), just add a column and only allow those with the value set see that data. If you are using ApEx, this is incredibly easy to do.
5. Security (Roles and Privileges) - No more table based authorization, let the database do it through roles and privileges. GRANT EXECUTE ON my_package TO some_user
That's my short list for today. Like Dom, this makes me angry. If there were some rational logic behind it, great, convince me. I haven't seen it yet though.
I've written about MySQL Friday or Application Developers vs. Database Developerswhich were similar in thought; the database is a bucket.
Ultimately, my take is that application developers don't know and don't want to learn how to use a database. PL/SQL specifically, is a platform in and of itself. You can do so much in the database now that you essentially need an application only for display, to determine the row color if you will.
The usual caveat follows:
If you are building applications that are supposed to be database independent, then the logic belongs in the application. The database is a bucket.
If you are building business applications specific to Oracle though, use the damn thing. Application/web developers are then forced to work on the design and user interface, not application/transaction logic.
Easy steps to actually utilize your database:
1. Use as many constraints as humanly possible - This will reduce the amount of code you have to write and you'll have the security of knowing the data will be what you constrain it to be.
2. DEFAULT columns in table definitions - create_date or load_date can be default to SYSDATE and thus left out of any application code. I've gone so far as to use SYS_CONTEXT( 'MY_CONTEXT', 'USERID' ) as the DEFAULT value for the create_user column. That along with a NOT NULL (or CHECK) constraint, makes life that much easier.
3. Did I mention constraints? Primary Key and Foreign Key constraints are very important to maintain data integrity (ensure you have the data you expect). Don't forget to index those foreign keys.
4. Security - VPD (Virtual Private Database) or Fine Grained Access Control. No longer do you need to maintain two separate schemas (or databases), just add a column and only allow those with the value set see that data. If you are using ApEx, this is incredibly easy to do.
5. Security (Roles and Privileges) - No more table based authorization, let the database do it through roles and privileges. GRANT EXECUTE ON my_package TO some_user
That's my short list for today. Like Dom, this makes me angry. If there were some rational logic behind it, great, convince me. I haven't seen it yet though.
Labels: constraints, database, development, oracle, work
¶ 3/25/2008 03:16:00 PM 2 Comments Links to this post
Wednesday, February 20, 2008
Application Developers vs Database Developers
It started innocently enough with this article. I sent it out to about 20 colleagues.
The best line from the article:
"Jerry: "Yeah, databases cause lots of headaches. They crash all the time, corrupt data, etc. Using text files is better."
One of my more recently arrived colleagues (I'll call him Mr. M) replied to everyone with this statement:
"Kind of funny actually, databases are less and less important at the large investment banks, where they basically load everything up into a data grid across a several hundred node cluster. Writing to the db is way too slow."
This started a day long exchange of emails. What follows is the entire thread (up until my last post tonight).
Me:
"I would just argue that they don’t necessarily know how to write to databases. I would however love to see benchmarking done on both methods. Would be an interesting test..."
Mr. M:
"Well, my understanding is they just can’t scale out the db enough. Even something like Oracle RAC won’t work. And outside of the military, these are probably the top 1% of programmers in the world building this stuff."
Me:
"A benchmark would be the only way I would believe it.
If you said the top 1% of database developers tried it and failed, I would be more likely to agree.
My experience is that application developers != database developers. Different type of thinking involved."
Mr. M:
"'A benchmark would be the only way I would believe it.'
Do you need a benchmark before you would believe in-memory retrieval is faster than disk retrieval? Essentially, this is what we’re talking about.
'If you said the top 1% of database developers tried it and failed, I would be more likely to agree. My experience is that application developers != database developers. Different type of thinking involved.'
Why? It’s an issue to do with application performance not simply database performance. Database concerns are a subset of application concerns, essentially a specialization, requiring less encompassing knowledge. ;)
From the article you linked to (http://www.watersonline.com/public/showPage.html?page=432587)
"Better data management is the answer, says Lewis Foti, manager of high-performance computing and grid at The Royal Bank of Scotland (RBS) global banking and markets. "For very large compute arrays, the key issue is data starvation and saturation. This problem requires data grids with high bandwidth and scalable, parallel access,
...
Banks are learning that data management in a distributed grid environment is very different from online transaction processing. "With so many data sources, distribution channels, demands for aggregation and analytics, surges in data volumes and complex dynamics between the flows, we need to manage 'data in motion' and give up the notion that data is somehow stored. It's dynamic, not static," says Michael Di Stefano, vice president and architect for financial services at GemStone Systems
...
There is even some debate over how small a unit of work can be put on today's grids. Di Stefano at GemStone, for example, says, "One client has gone from 200 trades per second in a program trading application to more than 6,000 trades per second. This shows what the technology can do."
Yep, the writing is on the wall. Oracle knows it too.
http://www.google.com/search?hl=en&q=oracle+buys+tangosol&btnG=Google+Search"
Me:
"Good points. If it is in-memory it would be faster. I have not had the pleasure to work on such a system.
I do disagree with the database concerns being a subset of application concerns. The data drives the app. We’re probably getting religious at this point (or am I)."
Mr. M:
"‘The data drives the app.”
Exactly, but who’s to say where the data comes from or in what format? My application data may reside completely in xml files, or maybe I get it from some third party web services a la the en vogue “mashup.” Heck, I may not even need to worry about a database anymore…. http://www.amazon.com/gp/browse.html?node=16427261 The database is only one particular concern of the overall application. And it’s the application that matters. Data is useless if it just sits on a disk somewhere. It’s the ways in which the application lets the users view and manipulate the data that adds value to the business.
Yep, definitely a different type of thinking between application developers and database developers."
Me:
"Definitely religious now.
Applications come and go, data stays the same. Think Green Screens, EJBs, Ruby…what’s next?"
Mr. M:
"'Applications come and go'
Exactly. Businesses are not static, nor are the markets they compete in. Changing applications are a function of changing business processes and changing markets.
'data stays the same.'
Nonsense. Otherwise UPDATE would not be an SQL reserved word. If you mean database technology stays the same, well, I’m more inclined to agree with that.
'Think Green Screens, EJBs, Ruby...what’s next?'
Whatever comes along to let the business more effectively respond to current market realities. Application platforms have evolved much faster than database platforms have. They’ve had to, their sphere of operation is much broader than that of databases, this is only natural, they deal with much broader concerns than do databases. Databases in the internet era function in essentially the same role they did in the era of dumb terminals. Clearly application platforms have evolved orders of magnitude more. Hence the statement, database concerns are a subset of application concerns.
Here’s a simple test….if I take some business application and I’m forced to throw away one or the other, either the database or the appl- wait a second, it doesn’t even make sense to finish it, does it? The business can live without the database. I could do all kinds of things with the data, I could stick it anywhere. The business can’t live without the application though. Another way to look at is, what do the business users look at, test, approve, and use? The database? Of course not, they look at the application. They could care less whether the data sits on disk in an RDBMS, xml, or flat files."
Me:
"We obviously violently disagree.
Without the database (and I use database and data interchangebly), the business could no longer function. The app is meaningless. How would you contact your customer? You couldn’t find it.
'Exactly. Businesses are not static, nor are the markets they compete in. Changing applications are a function of changing business processes and changing markets.'
Poorly designed applications…that is all."
A Feisty Colleague:
"Using data and database interchangeably is incorrect. A database is a mechanism for data storage. XML data sets and flat files are mechanisms for data storage, too. So is a file cabinet, because, the data doesn’t have to be electronic, it could be … gasp! … on paper, and the application to use that data would be hands for holding the paper and a pencil to update and add data to the page."
Me:
"No it isn’t. I take into account xml files, flat files, web services (but not paper, unless it’s scanned) and all that. It would be consumed by the database and then accessed by the application via SQL.
(that’s for Mr. M and the feisty one)"
At which point someone forwarded the home page for Oracle's TimesTen In-Memory Database.
Me:
"A database on/in the mid-tier...Perfect!"
Mr. M:
"Implicit acknowledgment that disk IO operations that come with traditional database access simply can’t match the performance of in-memory data access (a point which you previously were unconvinced of but now seem perfectly accepting of the idea once you see it’s got Oracle’s imprimatur on it).
Of course, why any application developer would want to program against an SQL interface if they weren’t forced to is beyond me. It is orthogonal to the programming model of most application platform languages.
Surely Oracle recognize this fact too or they wouldn’t be buying Tangosol and other data grid technologies. Of course, most of those products are far more technically advanced than TimesTen or anything Oracle has in that space.
Incidentally, it’s illustrative to note that Coherence and other products like it were for the most part designed and built by application programmers. The development of all these products is pretty much driven by the needs of the large investment banks on Wall Street. These trading applications simply had too many concurrent transactions to use an RDBMS (a problem quite a number of public domains now share, most famously google.com, nope, no RDBMS there, yet miraculously there is still data). The database just simply would not scale to such a degree. So the application developers, by necessity, came up with an alternate solution that did work, a fully transactional cache of data replicated across a cluster with node numbers in the thousands, and no relational model whatsoever to speak of. A perfect example of how database concerns are only one, sometimes small, concern amongst many that application developers must be aware of and ready to solve."
Me:
"Like you said initially, the top 1%.
Many of us will never touch a system like this.
I will certainly concede that it is faster (still would love to see benchmarking though), but that still leaves 99% of the applications out there that do not require that kind of performance."
Me (again):
"And don’t forget, I use data and database interchangeably. Applications are nothing without the data right?
As to the object/relational impedance mismatch...well, more people that don’t know how to work in sets. Looping is what they understand. I understand the application side more than you seem to give me credit for.
I’m not saying applications aren’t important, they are. Data (databases) and applications go hand in hand. If the application went away though, they could still access their data via SELECT statements (yes, via an application client tool), however painful that may be. Applications make retrieving data that much easier for our users.
If anyone wants to unsubscribe from this mailing list, just let us know. This is fun for me (I’m guessing Mr. M too)."
Needless to say it was a fun day. It didn't get [too] personal. More than anything I'm happy to have an equally passionate colleague.
Besides, he claims he was just fracking around with me. ;)
The best line from the article:
"Jerry: "Yeah, databases cause lots of headaches. They crash all the time, corrupt data, etc. Using text files is better."
One of my more recently arrived colleagues (I'll call him Mr. M) replied to everyone with this statement:
"Kind of funny actually, databases are less and less important at the large investment banks, where they basically load everything up into a data grid across a several hundred node cluster. Writing to the db is way too slow."
This started a day long exchange of emails. What follows is the entire thread (up until my last post tonight).
Me:
"I would just argue that they don’t necessarily know how to write to databases. I would however love to see benchmarking done on both methods. Would be an interesting test..."
Mr. M:
"Well, my understanding is they just can’t scale out the db enough. Even something like Oracle RAC won’t work. And outside of the military, these are probably the top 1% of programmers in the world building this stuff."
Me:
"A benchmark would be the only way I would believe it.
If you said the top 1% of database developers tried it and failed, I would be more likely to agree.
My experience is that application developers != database developers. Different type of thinking involved."
Mr. M:
"'A benchmark would be the only way I would believe it.'
Do you need a benchmark before you would believe in-memory retrieval is faster than disk retrieval? Essentially, this is what we’re talking about.
'If you said the top 1% of database developers tried it and failed, I would be more likely to agree. My experience is that application developers != database developers. Different type of thinking involved.'
Why? It’s an issue to do with application performance not simply database performance. Database concerns are a subset of application concerns, essentially a specialization, requiring less encompassing knowledge. ;)
From the article you linked to (http://www.watersonline.com/public/showPage.html?page=432587)
"Better data management is the answer, says Lewis Foti, manager of high-performance computing and grid at The Royal Bank of Scotland (RBS) global banking and markets. "For very large compute arrays, the key issue is data starvation and saturation. This problem requires data grids with high bandwidth and scalable, parallel access,
...
Banks are learning that data management in a distributed grid environment is very different from online transaction processing. "With so many data sources, distribution channels, demands for aggregation and analytics, surges in data volumes and complex dynamics between the flows, we need to manage 'data in motion' and give up the notion that data is somehow stored. It's dynamic, not static," says Michael Di Stefano, vice president and architect for financial services at GemStone Systems
...
There is even some debate over how small a unit of work can be put on today's grids. Di Stefano at GemStone, for example, says, "One client has gone from 200 trades per second in a program trading application to more than 6,000 trades per second. This shows what the technology can do."
Yep, the writing is on the wall. Oracle knows it too.
http://www.google.com/search?hl=en&q=oracle+buys+tangosol&btnG=Google+Search"
Me:
"Good points. If it is in-memory it would be faster. I have not had the pleasure to work on such a system.
I do disagree with the database concerns being a subset of application concerns. The data drives the app. We’re probably getting religious at this point (or am I)."
Mr. M:
"‘The data drives the app.”
Exactly, but who’s to say where the data comes from or in what format? My application data may reside completely in xml files, or maybe I get it from some third party web services a la the en vogue “mashup.” Heck, I may not even need to worry about a database anymore…. http://www.amazon.com/gp/browse.html?node=16427261 The database is only one particular concern of the overall application. And it’s the application that matters. Data is useless if it just sits on a disk somewhere. It’s the ways in which the application lets the users view and manipulate the data that adds value to the business.
Yep, definitely a different type of thinking between application developers and database developers."
Me:
"Definitely religious now.
Applications come and go, data stays the same. Think Green Screens, EJBs, Ruby…what’s next?"
Mr. M:
"'Applications come and go'
Exactly. Businesses are not static, nor are the markets they compete in. Changing applications are a function of changing business processes and changing markets.
'data stays the same.'
Nonsense. Otherwise UPDATE would not be an SQL reserved word. If you mean database technology stays the same, well, I’m more inclined to agree with that.
'Think Green Screens, EJBs, Ruby...what’s next?'
Whatever comes along to let the business more effectively respond to current market realities. Application platforms have evolved much faster than database platforms have. They’ve had to, their sphere of operation is much broader than that of databases, this is only natural, they deal with much broader concerns than do databases. Databases in the internet era function in essentially the same role they did in the era of dumb terminals. Clearly application platforms have evolved orders of magnitude more. Hence the statement, database concerns are a subset of application concerns.
Here’s a simple test….if I take some business application and I’m forced to throw away one or the other, either the database or the appl- wait a second, it doesn’t even make sense to finish it, does it? The business can live without the database. I could do all kinds of things with the data, I could stick it anywhere. The business can’t live without the application though. Another way to look at is, what do the business users look at, test, approve, and use? The database? Of course not, they look at the application. They could care less whether the data sits on disk in an RDBMS, xml, or flat files."
Me:
"We obviously violently disagree.
Without the database (and I use database and data interchangebly), the business could no longer function. The app is meaningless. How would you contact your customer? You couldn’t find it.
'Exactly. Businesses are not static, nor are the markets they compete in. Changing applications are a function of changing business processes and changing markets.'
Poorly designed applications…that is all."
A Feisty Colleague:
"Using data and database interchangeably is incorrect. A database is a mechanism for data storage. XML data sets and flat files are mechanisms for data storage, too. So is a file cabinet, because, the data doesn’t have to be electronic, it could be … gasp! … on paper, and the application to use that data would be hands for holding the paper and a pencil to update and add data to the page."
Me:
"No it isn’t. I take into account xml files, flat files, web services (but not paper, unless it’s scanned) and all that. It would be consumed by the database and then accessed by the application via SQL.
(that’s for Mr. M and the feisty one)"
At which point someone forwarded the home page for Oracle's TimesTen In-Memory Database.
Me:
"A database on/in the mid-tier...Perfect!"
Mr. M:
"Implicit acknowledgment that disk IO operations that come with traditional database access simply can’t match the performance of in-memory data access (a point which you previously were unconvinced of but now seem perfectly accepting of the idea once you see it’s got Oracle’s imprimatur on it).
Of course, why any application developer would want to program against an SQL interface if they weren’t forced to is beyond me. It is orthogonal to the programming model of most application platform languages.
Surely Oracle recognize this fact too or they wouldn’t be buying Tangosol and other data grid technologies. Of course, most of those products are far more technically advanced than TimesTen or anything Oracle has in that space.
Incidentally, it’s illustrative to note that Coherence and other products like it were for the most part designed and built by application programmers. The development of all these products is pretty much driven by the needs of the large investment banks on Wall Street. These trading applications simply had too many concurrent transactions to use an RDBMS (a problem quite a number of public domains now share, most famously google.com, nope, no RDBMS there, yet miraculously there is still data). The database just simply would not scale to such a degree. So the application developers, by necessity, came up with an alternate solution that did work, a fully transactional cache of data replicated across a cluster with node numbers in the thousands, and no relational model whatsoever to speak of. A perfect example of how database concerns are only one, sometimes small, concern amongst many that application developers must be aware of and ready to solve."
Me:
"Like you said initially, the top 1%.
Many of us will never touch a system like this.
I will certainly concede that it is faster (still would love to see benchmarking though), but that still leaves 99% of the applications out there that do not require that kind of performance."
Me (again):
"And don’t forget, I use data and database interchangeably. Applications are nothing without the data right?
As to the object/relational impedance mismatch...well, more people that don’t know how to work in sets. Looping is what they understand. I understand the application side more than you seem to give me credit for.
I’m not saying applications aren’t important, they are. Data (databases) and applications go hand in hand. If the application went away though, they could still access their data via SELECT statements (yes, via an application client tool), however painful that may be. Applications make retrieving data that much easier for our users.
If anyone wants to unsubscribe from this mailing list, just let us know. This is fun for me (I’m guessing Mr. M too)."
Needless to say it was a fun day. It didn't get [too] personal. More than anything I'm happy to have an equally passionate colleague.
Besides, he claims he was just fracking around with me. ;)
Labels: database, development, funny, partition, work
¶ 2/20/2008 10:29:00 PM 5 Comments Links to this post
Thursday, January 31, 2008
Love Your DBA
I consider myself a Developer/DBA.
That said, you've probably either read about or experienced the typical riff between the developer and the DBA.
At my current employer, I am finally surrounded by true Production DBAs. Initially, I found it difficult to work them. When I would ask "Why can't I do that?" I would rarely get a response.
Over time though, things have changed...for the better.
I believe it's called trust.
Trust that I am trying to do the right thing.
Trust that I want to learn.
Trust that I will listen to their suggestions.
Trust that I won't hack their DEV/QA instances using CREATE ANY PROCEDURE and EXECUTE ANY PROCEDURE.
Trust that I want to build a scalable and robust application.
So love your DBA. Give them time to get to know you. Give them time to learn your style, your methodology. Maybe someday they'll love you back and your job will get infinitely easier.
That said, you've probably either read about or experienced the typical riff between the developer and the DBA.
At my current employer, I am finally surrounded by true Production DBAs. Initially, I found it difficult to work them. When I would ask "Why can't I do that?" I would rarely get a response.
Over time though, things have changed...for the better.
I believe it's called trust.
Trust that I am trying to do the right thing.
Trust that I want to learn.
Trust that I will listen to their suggestions.
Trust that I won't hack their DEV/QA instances using CREATE ANY PROCEDURE and EXECUTE ANY PROCEDURE.
Trust that I want to build a scalable and robust application.
So love your DBA. Give them time to get to know you. Give them time to learn your style, your methodology. Maybe someday they'll love you back and your job will get infinitely easier.
Labels: database, dba, development, work
¶ 1/31/2008 12:15:00 AM 6 Comments Links to this post
Tuesday, January 8, 2008
Asynchronous Distributed HotLog - CDC Part III
This is the third installment (one and two)of my attempts to configure Oracle's CDC from a 9.2.0.6 source (highest release for windows) to a 10.2.0.1 (ditto) target.
After being appropriately set up with privileges (DBA's trusted me with the DBA role!) on our actual environments, 9.2.0.7 source and 10.2.0.3 target (Sun Solaris), I've moved back to trying to just get a simple proof of concept working on my machine.
That's had it's own difficulties, though I can safely say that I can manually create 9i and 10g databases manually.
Getting the databases configured properly has been my biggest challenge. I had eliminated most possibilities down to either Net Services or something to do with the JVM. I used my local listener and ruled out Net Services. It was down to the JVM. I kept getting this strange error:
Installing InterMedia was a pain. I kept getting a failure of a particular jar (ORA-23542?), could not resolve the class.
I googled one of the classes that failed to see if anyone had run across this before. There was only one record found...and it was mine from about a year and half ago! I found that very amusing.
So I went into the\ord\im directory and found a readme.txt. As I am scanning through it I see CLASSPATH. So I add the environment variable and voila! Perfect install. That would have solved my problem from before too. I followed the docs on the install to a T. Nothing in there about CLASSPATH. Perhaps I should write them and ask for it to be added?
Anyway, thinking the best, since I had a perfectly clean installation, I tried again:
At least the ORA-0600 is gone, but I am back to where I started.
Another good thing is that I am really learning how to pour through trace files and tkprof files.
All the examples I have found make it seem so easy...why can't I get this dang thing to work?!
After being appropriately set up with privileges (DBA's trusted me with the DBA role!) on our actual environments, 9.2.0.7 source and 10.2.0.3 target (Sun Solaris), I've moved back to trying to just get a simple proof of concept working on my machine.
That's had it's own difficulties, though I can safely say that I can manually create 9i and 10g databases manually.
Getting the databases configured properly has been my biggest challenge. I had eliminated most possibilities down to either Net Services or something to do with the JVM. I used my local listener and ruled out Net Services. It was down to the JVM. I kept getting this strange error:
A trace file was generated (with the above error) but it didn't really tell me anything. I googled the phrase, nothing. I went to Metalink, nothing. It looked like a Java call though, so I tore the database down and rebuilt everything. Just in case, I added XDB, Data Mining and InterMedia in case there was some reference there.
ERROR at line 1:
ORA-00600: internal error code, arguments: [qccsrc_createHLSource-3], [], [],
[], [], [], [], []
ORA-06512: at "SYS.DBMS_CDC_IPUBLISH", line 133
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 226
ORA-06512: at line 2
Installing InterMedia was a pain. I kept getting a failure of a particular jar (ORA-23542?), could not resolve the class.
I googled one of the classes that failed to see if anyone had run across this before. There was only one record found...and it was mine from about a year and half ago! I found that very amusing.
So I went into the
Anyway, thinking the best, since I had a perfectly clean installation, I tried again:
And I waited for an agonizing minute thinking I had it this time...
SQL> BEGIN
2 dbms_cdc_publish.create_hotlog_change_source
3 ( change_source_name => 'T',
4 description => 'Test',
5 source_database => 'ORANINE' );
6 END;
7 /
Barnacles!
BEGIN
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at "SYS.DBMS_CDC_IPUBLISH", line 133
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 226
ORA-06512: at line 2
At least the ORA-0600 is gone, but I am back to where I started.
Another good thing is that I am really learning how to pour through trace files and tkprof files.
All the examples I have found make it seem so easy...why can't I get this dang thing to work?!
Labels: cdc, dbms_cdc_publish, development, howto, work
¶ 1/08/2008 12:11:00 AM 0 Comments Links to this post
Thursday, December 27, 2007
Asynchronous Distributed HotLog - Change Data Capture
I am currently researching the feasibility of getting Asynchronous Distributed HotLog CDC working for a Proof of Concept in our organization. The source database is a 9.2.0.7 on Sun Solaris and our target database is a 10gR2 on the same OS.
I have been trying, since I arrived, to get them to use more features provided by Oracle. From what I have found so far, all of our Oracle databases are severely under utilized.
I will post my findings here along with a full How To when I have completed it and hopefully the results of whether it was implemented or not.
I did want to post some of the sources I have used so far though:
Oracle Documentation - This is where I started. Most of the other links reference the docs.
Mark Rittman - 10g to 10g. Great example but from what I can tell so far, it's almost identical to the documentation.
Oracle How To - I just found this one and am very interested in reading through it.
If you have any examples, feel free to post the link.
I have been trying, since I arrived, to get them to use more features provided by Oracle. From what I have found so far, all of our Oracle databases are severely under utilized.
I will post my findings here along with a full How To when I have completed it and hopefully the results of whether it was implemented or not.
I did want to post some of the sources I have used so far though:
Oracle Documentation - This is where I started. Most of the other links reference the docs.
Mark Rittman - 10g to 10g. Great example but from what I can tell so far, it's almost identical to the documentation.
Oracle How To - I just found this one and am very interested in reading through it.
If you have any examples, feel free to post the link.
Labels: cdc, database, development, oracle
¶ 12/27/2007 01:01:00 AM 0 Comments Links to this post
Wednesday, December 5, 2007
Instrumentation: DEBUG/LOGGING
In my previous entry on instrumenting code I detailed the way in which you could use the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure to help monitor your long running code.
In this one, I will detail my home grown version of Tom Kyte's debug routine. I do know that others have similar code but can't seem to find them right now.
You can find the source code here.
Contents:
2 tables
2 sequences
1 package
1 procedure
1 build file
debug_tab allows you to turn the debugging on and off. debug_details_tab will store each line that you write to the debug routine when turned on.
Here's an example of it in practice:
I mentioned in the previous article that I had had difficulty grasping this concept initially. I think once I related it to DBMS_OUTPUT.PUT_LINE it became much more clear to me.
This simple debug routine has helped me tremendously in the last year or two that I have used it. Especially when you get nested levels of logic. It gets very hard to keep track of where you are, but with this you can run your procedure or function and then issue a SELECT against the debug_details_tab and see what was happening when.
I even began using this in my APEX applications. I would preface each line with "APEX: " and then write whatever was necessary so that I could step through the various pieces of code. It became crucial when I was doing validation on a table of records in a collection...oh so much easier.
On large systems this will generate a lot of data. I definitely would not put this inside a loop doing some sort of batch processing, but it is helpful to find where things fail out.
It can certainly be improved on. I basically took the idea of DBMS_OUTPUT.PUT_LINE and wrote it to a table, nothing fancy there. Mr. Kyte mentions writing it to the file system as well. Since I don't have ready access to the database file system, this was the easiest.
Make your life easier and use a debug/logging routine in your code. No longer will you have to comment, the debug statements should do it for you!
In this one, I will detail my home grown version of Tom Kyte's debug routine. I do know that others have similar code but can't seem to find them right now.
You can find the source code here.
Contents:
2 tables
2 sequences
1 package
1 procedure
1 build file
debug_tab allows you to turn the debugging on and off. debug_details_tab will store each line that you write to the debug routine when turned on.
Here's an example of it in practice:
CREATE OR REPLACE
FUNCTION get_age_in_months( p_id IN NUMBER ) RETURN NUMBER
IS
l_age_in_months INTEGER;
BEGIN
--instrumentation calls
debug( 'GET_AGE_IN_MONTHS' );
debug( 'P_ID: ' || p_id );
debug( 'select value into variable' );
SELECT age_in_months
INTO l_age_in_months
FROM people_tab
WHERE id = p_id;
debug( 'L_AGE_IN_MONTHS: ' || l_age_in_months );
RETURN l_age_in_months;
EXCEPTION
WHEN no_data_found THEN
debug( 'no data found' );
RETURN NULL;
END get_age_in_months;
/
show errors
I mentioned in the previous article that I had had difficulty grasping this concept initially. I think once I related it to DBMS_OUTPUT.PUT_LINE it became much more clear to me.
This simple debug routine has helped me tremendously in the last year or two that I have used it. Especially when you get nested levels of logic. It gets very hard to keep track of where you are, but with this you can run your procedure or function and then issue a SELECT against the debug_details_tab and see what was happening when.
I even began using this in my APEX applications. I would preface each line with "APEX: " and then write whatever was necessary so that I could step through the various pieces of code. It became crucial when I was doing validation on a table of records in a collection...oh so much easier.
On large systems this will generate a lot of data. I definitely would not put this inside a loop doing some sort of batch processing, but it is helpful to find where things fail out.
It can certainly be improved on. I basically took the idea of DBMS_OUTPUT.PUT_LINE and wrote it to a table, nothing fancy there. Mr. Kyte mentions writing it to the file system as well. Since I don't have ready access to the database file system, this was the easiest.
Make your life easier and use a debug/logging routine in your code. No longer will you have to comment, the debug statements should do it for you!
Labels: code, development, howto, instrumentation, plsql, tools, utilities, work
¶ 12/05/2007 09:03:00 PM 1 Comments Links to this post
Sunday, December 2, 2007
Instrumentation: DBMS_APPLICATION_INFO
Instrumentation has something that I have come to rely on fairly heavily. I believe I first read about it on asktom, but the one that really spurred me on was this post on instrumentation on his personal blog.
Initially, I couldn't really wrap my head around instrumentation. I don't know why it was so difficult; I had a similar problem with sessions when I first started my career. I look back now and it just seems so obvious.
Now that I am doing datawarehouse work, nothing is fast. Fast to me is now one hour to load 30 or 40 million records. No more split second queries for me.
We currently use no tools. It's straight PL/SQL. Instrumentation of the code is ideal. Actually, it's more instrumentation to aid monitoring. The tool most easily used is provided by Oracle in the DBMS_APPLICATION_INFO package.
There are three subprograms that I use most, SET_MODULE, SET_ACTION and most importantly SET_SESSION_LONGOPS. I hadn't started using it until this year, I mainly stuck to the first two. SET_SESSION_LONGOPS is now part of my procedure/function template I've created in JDeveloper.
What it allows you to do is set a row in the v$session_longops view (I know it's not actually putting the row in the view...it's the underlying table, but I digress). You can then monitor how your job is doing.
Here's an example:
g_index and g_slno are global variables in the package. l_table is a PL/SQL TABLE OF VARCHAR2.
Now you can monitor the progress of your job in v$session_longops!
Here's the query I use:
Now you too can sit for hours and watch your job move incrementally forward!
But seriously, it does help tremendously to know where a job is at. You can further use the SET_MODULE and SET_ACTION calls to see a specific point in the processing (inside a loop).
Here's the code in context:
Initially, I couldn't really wrap my head around instrumentation. I don't know why it was so difficult; I had a similar problem with sessions when I first started my career. I look back now and it just seems so obvious.
Now that I am doing datawarehouse work, nothing is fast. Fast to me is now one hour to load 30 or 40 million records. No more split second queries for me.
We currently use no tools. It's straight PL/SQL. Instrumentation of the code is ideal. Actually, it's more instrumentation to aid monitoring. The tool most easily used is provided by Oracle in the DBMS_APPLICATION_INFO package.
There are three subprograms that I use most, SET_MODULE, SET_ACTION and most importantly SET_SESSION_LONGOPS. I hadn't started using it until this year, I mainly stuck to the first two. SET_SESSION_LONGOPS is now part of my procedure/function template I've created in JDeveloper.
What it allows you to do is set a row in the v$session_longops view (I know it's not actually putting the row in the view...it's the underlying table, but I digress). You can then monitor how your job is doing.
Here's an example:
dbms_application_info.set_session_longops
( rindex => g_index,
slno => g_slno,
op_name => 'GETTING MEMBER DATA',
sofar => 0,
totalwork => l_table.COUNT + 1,
target_desc => 'GETTING MEMBER DATA' );
g_index and g_slno are global variables in the package. l_table is a PL/SQL TABLE OF VARCHAR2.
Now you can monitor the progress of your job in v$session_longops!
Here's the query I use:
SELECT
username,
sid,
serial#,
TO_CHAR( start_time, 'MM/DD/YYYY HH24:MI:SS' ) start_ti,
time_remaining rem,
elapsed_seconds ela,
ROUND( ( sofar / REPLACE( totalwork, 0, 1 ) ) * 100, 2 ) per,
sofar,
totalwork work,
message,
target_desc
FROM v$session_longops
WHERE start_time >= SYSDATE - 1
ORDER BY start_time DESC
Now you too can sit for hours and watch your job move incrementally forward!
But seriously, it does help tremendously to know where a job is at. You can further use the SET_MODULE and SET_ACTION calls to see a specific point in the processing (inside a loop).
Here's the code in context:
PROCEDURE get_member_data
IS
l_exists INTEGER;
TYPE table_of_lobs IS TABLE OF VARCHAR2(3);
l_table TABLE_OF_LOBS := TABLE_OF_LOBS( 'COM', 'ORG' );
l_count INTEGER := 0;
BEGIN
--check to see if there is enrollment data, if not, move on
SELECT COUNT(*)
INTO l_exists
FROM members
WHERE rownum < 2;
IF l_exists = 1 THEN--data exists, truncate and reload
g_index := dbms_application_info.set_session_longops_nohint;
EXECUTE IMMEDIATE 'TRUNCATE TABLE member_stg';
g_audit_key := p_audit.begin_load
( p_targettable => 'MEMBER_STG',
p_loadsource => 'MEMBER_SOURCE',
p_loadstatus => 'PRE',
p_loadprogram => 'GET_MEMBER_DATA',
p_commenttext => 'INSERT' );
dbms_application_info.set_session_longops
( rindex => g_index,
slno => g_slno,
op_name => 'GETTING MEMBERS',
sofar => 0,
totalwork => l_table.COUNT + 1,
target_desc => 'GETTING MEMBERS' );
FOR i IN 1..l_table.COUNT LOOP
l_count := l_count + 1;
INSERT INTO member_stg
SELECT *
FROM members;
g_total_rows_affected := g_total_rows_affected + sql%rowcount;
COMMIT;
dbms_application_info.set_session_longops
( rindex => g_index,
slno => g_slno,
op_name => 'GETTING MEMBERS',
sofar => l_count,
totalwork => l_table.COUNT + 1,
target_desc => 'GETTING MEMBERS' );
END LOOP;
p_audit.end_load
( p_auditkey => g_audit_key,
p_loadstatus => 'SUC',
p_rowsuccess => g_total_rows_affected );
gather_table_stats
( p_tablename => 'MEMBER_STG',
p_schemaname => 'MYHOME' );
dbms_application_info.set_session_longops
( rindex => g_index,
slno => g_slno,
op_name => 'GETTING MEMBERS',
sofar => l_count + 1,
totalwork => l_table.COUNT + 1,
target_desc => 'GETTING MEMBERS' );
END IF;
EXCEPTION
WHEN others THEN
p_audit.failed_load
( p_auditkey => g_audit_key,
p_comments => SQLCODE || ' ' || SQLERRM );
RAISE;
END get_member_data;
Labels: dbms_application_info, development, instrumentation, plsql, sql, work
¶ 12/02/2007 10:04:00 PM 0 Comments Links to this post
Friday, November 30, 2007
Ling Chi II
or Death By a Thousand Cuts. I wrote about it before.
Today was one of the worst days I've had at this job.
I got home last night knowing I had to run and test a new line of business in our test environment. This was a fairly small subset so it should run fairly fast, two or three hours. Around 1:30 AM, it completed. I did my quick sanity checks and realized that it just wasn't working right.
Crap, I forgot to load our new rates. That has to be it.
So I reran the rates and then reran our process.
It's now 3:00 AM. I run my sanity check and it's exactly the same as the last one.
I quickly realize that some of the rates hadn't been updated like I assumed they had. This test was null and void. I went to sleep...mad.
I woke up around 8:15 with my son telling me something about Transformers. What? Oh, OK, I'm in his bed. I take him to school and get to work around 9:15.
I'm cranky because the other developer didn't do his part, but probably more mad at myself for not making sure they completed their piece.
I throw a piece of candy. Better.
Of course my colleague isn't in today, so I have to make sure the DBAs deploy this to our test environment.
Finally I get the process working and it finishes around noon. I send out a note to the business and QA letting them know that they can begin.
I get a call from the business around 2:30, it's not working the way they expected. One of our rates overlaps another causing invalid results to return.
Thanks for telling us that sooner. The day before deployment and were getting new requirements. Awesome!
I walk over to my boss' desk with my badge in hand, ready to quit, kind of, to tell him the news. He talks me off the ledge.
We then head over to their building to discuss and indeed it's something they didn't realize. OK, fair enough. Had they seemed to appreciate me/us a little bit more over the past eight months I probably wouldn't have been so mad. It was just one more thing though.
My boss decides how this will be remedied. I argue (gently) that this is not an application issue but a data issue. He agrees. We'll just put one job on hold and make the necessary changes to the [driving] data.
Two IT VPs, one business VP and the CIO need to sign off on two change requests, one for the driving data and one for the application.
We're slated to deploy tomorrow pending UAT signoff, though that shouldn't be a problem.
Ten to twenty percent of my time is spent writing code, the rest is paperwork. I believed I had mastered the administrative stuff only to get this requirement change at the last minute.
One more thing...just one more thing.
Ling Chi indeed.
Today was one of the worst days I've had at this job.
I got home last night knowing I had to run and test a new line of business in our test environment. This was a fairly small subset so it should run fairly fast, two or three hours. Around 1:30 AM, it completed. I did my quick sanity checks and realized that it just wasn't working right.
Crap, I forgot to load our new rates. That has to be it.
So I reran the rates and then reran our process.
It's now 3:00 AM. I run my sanity check and it's exactly the same as the last one.
I quickly realize that some of the rates hadn't been updated like I assumed they had. This test was null and void. I went to sleep...mad.
I woke up around 8:15 with my son telling me something about Transformers. What? Oh, OK, I'm in his bed. I take him to school and get to work around 9:15.
I'm cranky because the other developer didn't do his part, but probably more mad at myself for not making sure they completed their piece.
I throw a piece of candy. Better.
Of course my colleague isn't in today, so I have to make sure the DBAs deploy this to our test environment.
Finally I get the process working and it finishes around noon. I send out a note to the business and QA letting them know that they can begin.
I get a call from the business around 2:30, it's not working the way they expected. One of our rates overlaps another causing invalid results to return.
Thanks for telling us that sooner. The day before deployment and were getting new requirements. Awesome!
I walk over to my boss' desk with my badge in hand, ready to quit, kind of, to tell him the news. He talks me off the ledge.
We then head over to their building to discuss and indeed it's something they didn't realize. OK, fair enough. Had they seemed to appreciate me/us a little bit more over the past eight months I probably wouldn't have been so mad. It was just one more thing though.
My boss decides how this will be remedied. I argue (gently) that this is not an application issue but a data issue. He agrees. We'll just put one job on hold and make the necessary changes to the [driving] data.
Two IT VPs, one business VP and the CIO need to sign off on two change requests, one for the driving data and one for the application.
We're slated to deploy tomorrow pending UAT signoff, though that shouldn't be a problem.
Ten to twenty percent of my time is spent writing code, the rest is paperwork. I believed I had mastered the administrative stuff only to get this requirement change at the last minute.
One more thing...just one more thing.
Ling Chi indeed.
Labels: development, sql, work
¶ 11/30/2007 10:03:00 PM 0 Comments Links to this post
Thursday, November 29, 2007
Keeping it Simple
One of my all time favorite articles is The Complicator's Gloves on Worse Than Failure (formerly the DailyWTF). It identifies the tendency of software developers in particular to come up with overly complex solutions, usually when there is a much simpler one available.
This was the context of my latest rant to my CIO. Actually, this theme seems to play out in all my rants. Funny how that works.
While web services and the like have their place, many times they are used just because they are the cool new thing, not because of a pressing need. I know I am not the first to mention that nor will I be the last.
Whether it was years of reading asktom (for pleasure no less) or the influence of my first boss, I have striven (sp?) to build applications that are scalable yet easy to maintain.
One of my proudest accomplishments as a developer was at my previous job. A small state-contracted agency where I was the lone developer. I, thanks to a very trusting boss, was allowed to install Oracle and soon after found Application Express (APEX). In 18 months I was able to create some 350 pages of forms and reports for the organization. One person, 350 pages. I once found a job ad for a web developer to help maintain a 100 page website on a team of six. What? Six people? Really? Must be java or something. ;)
I continued to work for them on a contract basis for about six months after I left. Mostly until the new guy got comfortable. Unfortunately for me, they didn't require my services a whole lot. Yes, I could be deluding myself, I realize that...but I just don't believe it. They WOULD tell me.
Back to my point. At our organization we seem to have quite a few architects. They talk of Ruby on Rails, Java, JBoss, etc. MySQL gets a brief mention on occasion.
We have a hard enough time writing good SQL or PL/SQL, so now we're going to introduce new languages and a new database platform?
If we were a company that made software, I will probably be [mostly] on board, but we are not. We store and manage data for the business to do their job.
I do hope I am wrong about them and that they do talk about the importance of data in our organization. I just haven't see it yet.
So, put it in the database, use APEX when appropriate (95% of the time) and keep it simple.
This was the context of my latest rant to my CIO. Actually, this theme seems to play out in all my rants. Funny how that works.
While web services and the like have their place, many times they are used just because they are the cool new thing, not because of a pressing need. I know I am not the first to mention that nor will I be the last.
Whether it was years of reading asktom (for pleasure no less) or the influence of my first boss, I have striven (sp?) to build applications that are scalable yet easy to maintain.
One of my proudest accomplishments as a developer was at my previous job. A small state-contracted agency where I was the lone developer. I, thanks to a very trusting boss, was allowed to install Oracle and soon after found Application Express (APEX). In 18 months I was able to create some 350 pages of forms and reports for the organization. One person, 350 pages. I once found a job ad for a web developer to help maintain a 100 page website on a team of six. What? Six people? Really? Must be java or something. ;)
I continued to work for them on a contract basis for about six months after I left. Mostly until the new guy got comfortable. Unfortunately for me, they didn't require my services a whole lot. Yes, I could be deluding myself, I realize that...but I just don't believe it. They WOULD tell me.
Back to my point. At our organization we seem to have quite a few architects. They talk of Ruby on Rails, Java, JBoss, etc. MySQL gets a brief mention on occasion.
We have a hard enough time writing good SQL or PL/SQL, so now we're going to introduce new languages and a new database platform?
If we were a company that made software, I will probably be [mostly] on board, but we are not. We store and manage data for the business to do their job.
I do hope I am wrong about them and that they do talk about the importance of data in our organization. I just haven't see it yet.
So, put it in the database, use APEX when appropriate (95% of the time) and keep it simple.
Labels: apex, database, development, work
¶ 11/29/2007 01:00:00 AM 1 Comments Links to this post
Saturday, November 24, 2007
Parallel Processing using DBMS_JOB
I found this article through the OraNA feed by ProdLife which talked about running a report that was based on multiple queries. It reminded me of something I did awhile back.
We have this multi-step process which loads data into 2 tables that the business would use to reconcile our money in the door and our membership. Membership is on a month granularity (member month) and our money is transactional (they may have multiple transactions within a given month).
One table stores the transactions joined with our members. Not the correct grain that the business needs but useful for research. The other table summarizes the transactions to the month level and then is joined with our membership so that both are at the same granularity. Currently we're pulling across about 27 million records for members and the same for their transactions.
On the right is a basic diagram of the process.
The process initially took 8 hours to complete. Part of it was the fact that it runs
sequentially. However, not all parts of this process are dependent on one another. It isn't until the final 2 steps (Target Table 1 and Target Table 2, in yellow) that they need to run sequentially.
I wanted to speed this up and began thinking about the ways to do this (assuming as much tuning as possible had already completed).
1. I could use our scheduler or unix shell scripts.
2. Use a table based approach as ProdLife did.
3. Utilize PL/SQL and DBMS_JOB.
I chose number 3 initially and that's the focus of this post. I'll detail why I didn't use this method at the end.
The first thing I had to figure out was how to get PL/SQL to wait. Having read a few
posts on AskTom I remembered the SLEEP procedure. After a quick scan of the site, I found that it was part of the DBMS_LOCK package. I asked the DBAs to give me access so that I could being testing.
I figured that if I could wait long enough, it would be easy to "poll" the USER_JOBS
view to see when it had finished. I'm just going to show code snippets as the whole thing can get quite long.
I first determined that the error returned from Oracle for a job not there is -23241.
That will let me know when it is complete. Next, I declared variables for each job to run.
First thing I do in the body is create the jobs using DBMS_JOB.SUBMIT.
Make sure you issue the COMMIT statement after the jobs have been submitted.
Here's the fun part. I created a loop that would call DBMS_LOCK.SLEEP and wait for 60 seconds. After the wait has ended, I check to see whether that job remains in the USER_JOBS table. This allows the jobs to complete in 100 minutes.
The next step is to determine when to exit the loop. Hopefully, the jobs will finish in time and move on to the next, but if not, you want to exit gracefully. Well, semi-gracefully anyway.
That's all there is to it.
In the end though, I was convinced not to use this method as restartability would be difficult. Perhaps this method combined with the table-based approach would be the ideal. I'll leave that for another day though.
We have this multi-step process which loads data into 2 tables that the business would use to reconcile our money in the door and our membership. Membership is on a month granularity (member month) and our money is transactional (they may have multiple transactions within a given month).
One table stores the transactions joined with our members. Not the correct grain that the business needs but useful for research. The other table summarizes the transactions to the month level and then is joined with our membership so that both are at the same granularity. Currently we're pulling across about 27 million records for members and the same for their transactions.
On the right is a basic diagram of the process.
The process initially took 8 hours to complete. Part of it was the fact that it runs
sequentially. However, not all parts of this process are dependent on one another. It isn't until the final 2 steps (Target Table 1 and Target Table 2, in yellow) that they need to run sequentially.
I wanted to speed this up and began thinking about the ways to do this (assuming as much tuning as possible had already completed).
1. I could use our scheduler or unix shell scripts.
2. Use a table based approach as ProdLife did.
3. Utilize PL/SQL and DBMS_JOB.
I chose number 3 initially and that's the focus of this post. I'll detail why I didn't use this method at the end.
The first thing I had to figure out was how to get PL/SQL to wait. Having read a few
posts on AskTom I remembered the SLEEP procedure. After a quick scan of the site, I found that it was part of the DBMS_LOCK package. I asked the DBAs to give me access so that I could being testing.
I figured that if I could wait long enough, it would be easy to "poll" the USER_JOBS
view to see when it had finished. I'm just going to show code snippets as the whole thing can get quite long.
I first determined that the error returned from Oracle for a job not there is -23241.
That will let me know when it is complete. Next, I declared variables for each job to run.
DECLARE
no_job EXCEPTION;
PRAGMA EXCEPTION_INIT( no_job, -23421 );
l_exists NUMBER;
l_dollars_job NUMBER;
l_members_job NUMBER;
First thing I do in the body is create the jobs using DBMS_JOB.SUBMIT.
BEGIN
dbms_job.submit
( job => l_dollars_job,
what => 'BEGIN p_mypackage.get_dollars; COMMIT; END;',
next_date => SYSDATE );
dbms_job.submit
( job => l_members_job,
what => 'BEGIN p_mypackage.get_members; COMMIT; END;',
next_date => SYSDATE );
COMMIT;
Make sure you issue the COMMIT statement after the jobs have been submitted.
Here's the fun part. I created a loop that would call DBMS_LOCK.SLEEP and wait for 60 seconds. After the wait has ended, I check to see whether that job remains in the USER_JOBS table. This allows the jobs to complete in 100 minutes.
FOR i IN 1..100 LOOP
dbms_lock.sleep( 60 );
IF l_dollars_job IS NOT NULL THEN
BEGIN
SELECT 1
INTO l_exists
FROM user_jobs
WHERE job = l_dollars_job;
l_exists := NULL;
EXCEPTION
WHEN no_data_found THEN
l_dollars_job := NULL;--job is finished
END;
END IF;
IF l_members_job IS NOT NULL THEN
BEGIN
SELECT 1
INTO l_exists
FROM user_jobs
WHERE job = l_members_job;
l_exists := NULL;
EXCEPTION
WHEN no_data_found THEN
l_members_job := NULL;--job is finished
END;
END IF;
The next step is to determine when to exit the loop. Hopefully, the jobs will finish in time and move on to the next, but if not, you want to exit gracefully. Well, semi-gracefully anyway.
IF l_dollars_job IS NULL
AND l_members_job IS NULL
THEN
EXIT;
ELSIF i = 100 THEN
BEGIN
dbms_job.remove( l_dollars_job );
EXCEPTION
WHEN no_job THEN
NULL;
END;
BEGIN
dbms_job.remove( l_members_job );
EXCEPTION
WHEN no_job THEN
NULL;
END;
--abort run, taking too long
raise_application_error( -20001, 'DOLLARS/MEMBERS data from not loaded timely...' );
END IF;
END LOOP;
END;
That's all there is to it.
In the end though, I was convinced not to use this method as restartability would be difficult. Perhaps this method combined with the table-based approach would be the ideal. I'll leave that for another day though.
Labels: dbms_job, development, howto, plsql
¶ 11/24/2007 12:44:00 AM 0 Comments Links to this post
Tuesday, November 13, 2007
Death By a Thousand Cuts
In case I haven't mentioned it before, I work for a fairly immature IT organization. We're heading in the right direction mind you, but defined and documented processes don't really exist.
Yesterday I went back and forth with our QA department about two truncate table statements. We had performed this in production before and we didn't change the code. It needed to be tested and I needed to do a unit test plan. Today I needed UAT signoff. What? There's no business user.
Our EDI group is overtaxed right now, so I wrote a process to delete data in "their" database on tables that they created. Part of that process was to drop the constraints and then recreate them with the ON DELETE CASCADE rule. Amusingly, they created most of them with the SET NULL rule and then had NOT NULL constraints on those columms. Sweet. I needed to do all this and finally delete the data, of course none of the environments (DEV/TEST/PROD) are the same, so what worked in DEV didn't work in TEST. I'm guessing that as soon as it is deployed in PROD, I will have missed something else. I'll look like the idiot too. Did I mention that I have to create indexes for all those foreign keys? No, well, I did. Otherwise the process would take days to complete. There are some 13 tables in total. Three child tables of the parent table and then multiple child tables of those child tables. Awesome!
I'm on my fifth day of trying to complete a "simple" delete now.
Since the FBI raided, QA has become more strict. OK, understandable, but there was never a notice saying so, nor what the new rules would be.
I finally get my final approval and the work orders are created. Release Management gives me the all clear (the files were staged for the DBAs). I ran over to let them know and he wants to go through the instructions before I leave to make sure everythings clear. We then go into the staging folder and only two of the eight files are there. Deep breath. I call the Release Management group and let them know. It will be an hour before they get home.
I know that was an honest mistake. When I went back to make sure I filled out all the forms correctly I realized that I had left one file off of my build list, so I originally only had seven.
DBA just called and he was slammed with other critical deployments/production problems and wasn't able to get to it tonight. It would happen in the morning.
A "simple" delete will have taken seven days to complete.
Slow slicing indeed...
Yesterday I went back and forth with our QA department about two truncate table statements. We had performed this in production before and we didn't change the code. It needed to be tested and I needed to do a unit test plan. Today I needed UAT signoff. What? There's no business user.
Our EDI group is overtaxed right now, so I wrote a process to delete data in "their" database on tables that they created. Part of that process was to drop the constraints and then recreate them with the ON DELETE CASCADE rule. Amusingly, they created most of them with the SET NULL rule and then had NOT NULL constraints on those columms. Sweet. I needed to do all this and finally delete the data, of course none of the environments (DEV/TEST/PROD) are the same, so what worked in DEV didn't work in TEST. I'm guessing that as soon as it is deployed in PROD, I will have missed something else. I'll look like the idiot too. Did I mention that I have to create indexes for all those foreign keys? No, well, I did. Otherwise the process would take days to complete. There are some 13 tables in total. Three child tables of the parent table and then multiple child tables of those child tables. Awesome!
I'm on my fifth day of trying to complete a "simple" delete now.
Since the FBI raided, QA has become more strict. OK, understandable, but there was never a notice saying so, nor what the new rules would be.
I finally get my final approval and the work orders are created. Release Management gives me the all clear (the files were staged for the DBAs). I ran over to let them know and he wants to go through the instructions before I leave to make sure everythings clear. We then go into the staging folder and only two of the eight files are there. Deep breath. I call the Release Management group and let them know. It will be an hour before they get home.
I know that was an honest mistake. When I went back to make sure I filled out all the forms correctly I realized that I had left one file off of my build list, so I originally only had seven.
DBA just called and he was slammed with other critical deployments/production problems and wasn't able to get to it tonight. It would happen in the morning.
A "simple" delete will have taken seven days to complete.
Slow slicing indeed...
Labels: development, work
¶ 11/13/2007 09:31:00 PM 0 Comments Links to this post
Monday, November 5, 2007
I Want to Be Better Than Tom Kyte
OK, that got your attention. Somehow I knew it would.
I believe Mr. Kyte to be one of the foremost experts at Oracle development. His solutions are usually simple and concise. His philosophy is simple and concise; logic belongs with the data (in the database), don't reinvent the wheel if we've already created it (using supplied packages) and keep it simple. Of course Mr. Kyte may have objections to some of that, but that's the general idea I have gleaned over that past 5 years.
2. I grew up playing baseball, I liked being better than most of the other kids. I still believe that if I hadn't drank away my opportunity in college, I'd still be playing.
3. I'm an only child, I'm used to the attention and crave it. How do I get it now? By being better than everyone else.
Perhaps I should start small...be the best Oracle developer at WellCare, then Tampa, Florida, the U.S, North America, Northern Hemisphere and finally the World!
I am probably not the best Oracle developer at WellCare, so I have a ways to go. That's what drives me though. Trying to be the best. I'm surrounded by a lot of smart people which is a good thing. No...a great thing. I've been the lone wolf developer for too long. Now I have the opportunity to learn directly (as opposed to just reading) from others. There is give and take. Sometimes my solution is the best and sometimes it is not.
I don't believe my competitive nature interferes with my interpersonal relationships (I hope not anyway). It is more of an internal thing to me. Once upon a time I was skinny and in shape and I did triathlons. I wanted to be a pro (laughable). Each time though I tried to outdo my previous performance. Did I want to win? Sure I did, but it was more important for me to improve.
I believe that I am strong enough to take criticism from others. I can admit when I'm wrong (see countdown timer above).
I do want to be the best. I'll probably never have the opportunity (nor the time) to do what Mr. Kyte has done. I'm not going to strive to be mediocre though. Whether I realize that goal or not is mainly irrelevant, but that is my goal...to be better than Mr. Kyte.
I believe Mr. Kyte to be one of the foremost experts at Oracle development. His solutions are usually simple and concise. His philosophy is simple and concise; logic belongs with the data (in the database), don't reinvent the wheel if we've already created it (using supplied packages) and keep it simple. Of course Mr. Kyte may have objections to some of that, but that's the general idea I have gleaned over that past 5 years.
I am very competitive
1. One of the reasons I got into IT in the first place was that I didn't like this whole group of people knowing more than I did.2. I grew up playing baseball, I liked being better than most of the other kids. I still believe that if I hadn't drank away my opportunity in college, I'd still be playing.
3. I'm an only child, I'm used to the attention and crave it. How do I get it now? By being better than everyone else.
My goal is to be the best developer in the world
Will I ever achieve that? Could it even be measured? Is there some sort of Oracle developer competition out there?Perhaps I should start small...be the best Oracle developer at WellCare, then Tampa, Florida, the U.S, North America, Northern Hemisphere and finally the World!
I am probably not the best Oracle developer at WellCare, so I have a ways to go. That's what drives me though. Trying to be the best. I'm surrounded by a lot of smart people which is a good thing. No...a great thing. I've been the lone wolf developer for too long. Now I have the opportunity to learn directly (as opposed to just reading) from others. There is give and take. Sometimes my solution is the best and sometimes it is not.
I don't believe my competitive nature interferes with my interpersonal relationships (I hope not anyway). It is more of an internal thing to me. Once upon a time I was skinny and in shape and I did triathlons. I wanted to be a pro (laughable). Each time though I tried to outdo my previous performance. Did I want to win? Sure I did, but it was more important for me to improve.
I believe that I am strong enough to take criticism from others. I can admit when I'm wrong (see countdown timer above).
I do want to be the best. I'll probably never have the opportunity (nor the time) to do what Mr. Kyte has done. I'm not going to strive to be mediocre though. Whether I realize that goal or not is mainly irrelevant, but that is my goal...to be better than Mr. Kyte.
Labels: database, development, nerd, oracle, work
¶ 11/05/2007 08:48:00 PM 4 Comments Links to this post
Wednesday, October 31, 2007
Code Style: Functions
Functions
Functions are used to return a value. This value can take the form of a set of records (ref cursor), a collection, or a single value (NUMBER, VARCHAR2, etc.).
Functions (or procedures for that matter) should be logically grouped within a package. Only on the rare occasion should you store them individually.
That said, here is the syntax to create a function in Oracle:
The "debug" call is just instrumentation of the code. It will allow you to step through the code more easily.
In the above function, you would also need to be aware of too_many_rows, but since I know that "id" is the primary key on the table, I have ommitted it.
I tend to name variables thusly (I think I got that from Mr. Kyte):
Functions are used to return a value. This value can take the form of a set of records (ref cursor), a collection, or a single value (NUMBER, VARCHAR2, etc.).
Functions (or procedures for that matter) should be logically grouped within a package. Only on the rare occasion should you store them individually.
That said, here is the syntax to create a function in Oracle:
CREATE OR REPLACE
FUNCTION get_age_in_months( p_id IN NUMBER ) RETURN NUMBER
IS
l_age_in_months INTEGER;
BEGIN
--instrumentation calls
debug( 'GET_AGE_IN_MONTHS' );
debug( 'P_ID: ' || p_id );
debug( 'select value into variable' );
SELECT age_in_months
INTO l_age_in_months
FROM people_tab
WHERE id = p_id;
debug( 'L_AGE_IN_MONTHS: ' || l_age_in_months );
RETURN l_age_in_months;
EXCEPTION
WHEN no_data_found THEN
debug( 'no data found' );
--here you need to decide what exactly you want to do. If this is
--used in a SQL statement then you probably don't want to halt
--processing, so returning NULL will work just fine. However, if
--this function is called by another function or procedure and you
--need this value to continue processing, you WOULD want to know
--that the value is not there and you would issue a RAISE after you've
--logged the error
RETURN NULL;
END get_age_in_months;
/
show errors
The "debug" call is just instrumentation of the code. It will allow you to step through the code more easily.
In the above function, you would also need to be aware of too_many_rows, but since I know that "id" is the primary key on the table, I have ommitted it.
- Instrument your code.
- Name the function something descriptive. There is a 30 character limit so don't be lazy.
- Name your functions something descriptive. I typically use get_ then whatever it is I'm doing. get_calculated_amount, get_as_of_date, etc.
- Name your variables something descriptive. There is a 30 character limit so don't be lazy. Please just spell out "no". Is it "No" or "Number?" Why make the next person think, just spell it out. They'll thank you for it.
- If there are more than 1 input parameters, start at the next line.
- CREATE OR REPLACE goes on the top line, nothing else
- Use spaces liberally
- Comment where necessary. If you name things descriptively though, you'll find you won't need a lot of comments.
I tend to name variables thusly (I think I got that from Mr. Kyte):
- p_ = parameters passed (though you could use i_ for in and o_ for out as well)
- l_ = local variables
- g_ = global variables
Labels: code, development, style
¶ 10/31/2007 10:59:00 PM 1 Comments Links to this post
Thursday, October 25, 2007
Looking for an Oracle Developer?
So I am on vacation (well, I wouldn't necessarily call being a pack mule vacation) at Disney World this week.
Yesterday I received a text message from one of my friends, our company was being overtaken by armed FBI agents, lots of 'em.
FBI raid shutters Medicare insurer
FBI Raids Tampa WellCare
I've never seen any of these types of activities, though the scope of duties has been fairly limited (I've been there less than a year). I find it hard to believe as everyone I have worked with on the IT side of things I KNOW is on the up and up. I've never been asked to do anything nefarious...My hope, that if true, it is only a select few individuals.
I thoroughly enjoy working there. I am surrounding by some great people, both personally and professionally, but I do have a family to feed so...
So if you are in the Tampa area and are looking for a hard-working Oracle developer (APEX, PL/SQL), either email me or post a comment (I won't post the comment, but I will receive it via email and I can contact you from there). I'll also work on getting my resume updated and online.
Yesterday I received a text message from one of my friends, our company was being overtaken by armed FBI agents, lots of 'em.
FBI raid shutters Medicare insurer
FBI Raids Tampa WellCare
I've never seen any of these types of activities, though the scope of duties has been fairly limited (I've been there less than a year). I find it hard to believe as everyone I have worked with on the IT side of things I KNOW is on the up and up. I've never been asked to do anything nefarious...My hope, that if true, it is only a select few individuals.
I thoroughly enjoy working there. I am surrounding by some great people, both personally and professionally, but I do have a family to feed so...
So if you are in the Tampa area and are looking for a hard-working Oracle developer (APEX, PL/SQL), either email me or post a comment (I won't post the comment, but I will receive it via email and I can contact you from there). I'll also work on getting my resume updated and online.
Labels: apex, development, oracle, work
¶ 10/25/2007 11:41:00 AM 0 Comments Links to this post
Thursday, October 11, 2007
Code Style: Package Specification
Package Specification
CREATE OR REPLACE
PACKAGE p_foo
AS
FUNCTION get_something
( p_id IN NUMBER,
p_thing_im_looking_for VARCHAR2 ) RETURN NUMBER;
PROCEDURE do_something( p_id IN NUMBER );
PROCEDURE insert_something
( p_first_name IN PEOPLE_TAB.FIRST_NAME%TYPE,
p_last_name IN PEOPLE_TAB.LAST_NAME%TYPE,
p_email_address IN PEOPLE_TAB.EMAIL_ADDRESS%TYPE,
p_middle_initial IN PEOPLE_TAB.MIDDLE_INITIAL%TYPE DEFAULT NULL,
p_prefix IN PEOPLE_TAB.PREFIX%TYPE DEFAULT NULL );
PROCEDURE update_something
( p_id IN NUMBER,
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2 );
PROCEDURE delete_something( p_id IN NUMBER );
END p_foo;
/
show errors
- Name your package something descriptive. There is a 30 character limit so don't be lazy.
- Name your procedures and functions something descriptive. There is a 30 character limit so don't be lazy. UPDATE this or INSERT that. Try to give a small clue as to what it does. If it's a function, I typically use get_ then whatever it is I'm doing. get_calculated_amount, get_as_of_date, etc.
- Name your variables something descriptive. There is a 30 character limit so don't be lazy. Please just spell out "no". Is it "No" or "Number?" Why make the next person think, just spell it out. They'll thank you for it.
- Use the package name after the END statement.
- If there are more than 1 input parameter, start at the next line.
- CREATE OR REPLACE goes on the top line, nothing else
- Use spaces liberally
- Comment where necessary. If you name things descriptively though, you'll find you won't need a lot of comments.
- Declare parameters using %TYPE. If the parameter doesn't map to a table column declare SUBTYPEs somewhere.
Labels: code, development, style
¶ 10/11/2007 10:16:00 PM 2 Comments Links to this post
Code Style: Index
My ongoing contribution to...pretty much myself. Perhaps you can find this useful, or amusing, whichever.
Labels: code, development, style
¶ 10/11/2007 01:00:00 AM 0 Comments Links to this post
Code Style: Tables
Tables are easy.
For the datawarehouse, you'll need to think about constraints a bit more as it may slow down load times. I'm still all for constraints, but I would never say always use them.
For child tables:
This would be helpful if someone up and decided to change the NUMBER(10,0) to a VARCHAR2(10) or something (please don't ever do that!).
As for STORAGE or other table options, I typically leave that up to the DBA or work with them to add them. They may have a particular setup for certain tables that you can't possibly know (if you don't talk to them).
To recap:
Remember to always name your constraints. While I am at, use constraints as much as humanly possible, at least in your OLTP systems. You'll be able to reduce the amount of code you need to write and actually let the database do it's job. I'd much rather let the database do it than rely on code to maintain my data integrity.
CREATE TABLE t
(
col1 NUMBER(10,0)
CONSTRAINT pk_col1 PRIMARY KEY,
col2 VARCHAR2(32)
CONSTRAINT nn_col2_t NOT NULL
CONSTRAINT uq_col2_t UNIQUE,
col3 VARCHAR2(400),
col4 VARCHAR2(1) DEFAULT 'N'
CONSTRAINT ck_yorn_col4_t CHECK ( col4 IN ( 'Y', 'N' ) )
CONSTRAINT nn_col4_t NOT NULL
);
For the datawarehouse, you'll need to think about constraints a bit more as it may slow down load times. I'm still all for constraints, but I would never say always use them.
For child tables:
For Foreign Key constraints, you do not have to declare the type as it will be inherited from the parent table.
CREATE TABLE s
(
col5 NUMBER(10,0)
CONSTRAINT pk_col5 PRIMARY KEY,
col1
CONSTRAINT fk_col1_s REFERENCES t( col1 )
CONSTRAINT nn_col1_s NOT NULL,
col6 VARCHAR2(30)
);
This would be helpful if someone up and decided to change the NUMBER(10,0) to a VARCHAR2(10) or something (please don't ever do that!).
As for STORAGE or other table options, I typically leave that up to the DBA or work with them to add them. They may have a particular setup for certain tables that you can't possibly know (if you don't talk to them).
To recap:
- Use constraints as much as possible
- Always name your constraints
- Work with your DBA for table options
- Always name your constraints
Labels: code, constraints, development, discipline, style
¶ 10/11/2007 12:37:00 AM 0 Comments Links to this post
Tuesday, October 9, 2007
Code Style: General Principles
I have always wanted to put together my personal code style guide. Now that I have a blog, I can do so rather easily.
This will be the first among many that details my approach to coding style. Yes, it is very particular to me. No, I don't expect anyone in their right mind to follow it. Though perhaps someone can use it as a general guideline.
I am fairly fanatical about style.
Parenthesis go on separate lines, always (I'll eventually contradict myself I'm sure). None of this my line starts with a comma crap. My only concession is that it makes commenting out lines easier. Otherwise, I can't stand it. It's ugly to me and I don't like ugly.
Tabs = 2 spaces
CREATE OR REPLACE is the top line followed by the particular object on the second line (I don't know why, it's my style though).
Always put "show errors" at the bottom of procedure/function/package scripts.
Always end your procedure or function with the name after END.
Align SELECT statements left, not right:
Evil
Correct
I believe but can't confirm that Toad Formatter is the main perpetrator of this travesty.
Instrumentation. Use lots of it. Home grown or Mr. Kyte's debug routine. You can also use the Oracle supplied package DBMS_APPLICATION_INFO. I have gotten into the habit of using set_session_longops and it helps tremendously in monitoring those pesky long running datawarehouse programs.
Be liberal in your use of spaces: ADD_MONTHS( TRUNC( SYSDATE, 'MONTH' ), 2 )
Capitalize keywords like SELECT, INSERT, UPDATE and DELETE.
Standard functions should also be capitalized.
I'm sure I've got more and I'll add them here as time goes on.
This will be the first among many that details my approach to coding style. Yes, it is very particular to me. No, I don't expect anyone in their right mind to follow it. Though perhaps someone can use it as a general guideline.
I am fairly fanatical about style.
Parenthesis go on separate lines, always (I'll eventually contradict myself I'm sure). None of this my line starts with a comma crap. My only concession is that it makes commenting out lines easier. Otherwise, I can't stand it. It's ugly to me and I don't like ugly.
Tabs = 2 spaces
CREATE OR REPLACE is the top line followed by the particular object on the second line (I don't know why, it's my style though).
CREATE OR REPLACE
PACKAGE p_package
IS
...
Always put "show errors" at the bottom of procedure/function/package scripts.
Always end your procedure or function with the name after END.
CREATE OR REPLACE
PACKAGE p_package
IS
...
END p_package;
/
show errors
Align SELECT statements left, not right:
Evil
SELECT blah, t.id, s.x
FROM t, s
WHERE t.id = s.id
AND t.x = s.x;
Correct
SELECT
blah,
t.id,
s.x
FROM
t,
s
WHERE t.id = s.id
AND t.x = s.x;
I believe but can't confirm that Toad Formatter is the main perpetrator of this travesty.
Instrumentation. Use lots of it. Home grown or Mr. Kyte's debug routine. You can also use the Oracle supplied package DBMS_APPLICATION_INFO. I have gotten into the habit of using set_session_longops and it helps tremendously in monitoring those pesky long running datawarehouse programs.
Be liberal in your use of spaces: ADD_MONTHS( TRUNC( SYSDATE, 'MONTH' ), 2 )
Capitalize keywords like SELECT, INSERT, UPDATE and DELETE.
Standard functions should also be capitalized.
I'm sure I've got more and I'll add them here as time goes on.
Labels: development, discipline, nerd, style
¶ 10/09/2007 09:07:00 PM 5 Comments Links to this post
Wednesday, September 5, 2007
Working with the Business
I was once one of those annoying Business users who constantly pestered the IT department.
Probably one of the biggest reasons I got into IT was because I didn't like the control that IT (seemingly) had over me. I wanted that kind of control, but I also wanted to share it.
I have tried to carry my end user mentality with me to IT. I try and go out of my way to learn the business; from reading procedures to just sitting down with them and discussing what they do. This was fairly easy to do in the smaller companies I've worked with, but now that I am in a 3,000+ person company, it has gotten a bit more difficult.
I read an article by Steve Jones about getting closer to your business. I couldn’t agree more.
I would love (and have asked) to be able to spend more time with the Business folks. Perhaps shadowing them for a week, or just writing reports so I can get a better feel for what they do with the data/information we provide them. Better yet, make it part of the on-boarding for new employees. Spend 6 weeks with various departments or something like that. Some companies have management programs that do just that.
Anyway, I believe time spent with the Business makes me a better developer and hopefully builds up a good working relationship (trust) between IT and the Business.
Probably one of the biggest reasons I got into IT was because I didn't like the control that IT (seemingly) had over me. I wanted that kind of control, but I also wanted to share it.
I have tried to carry my end user mentality with me to IT. I try and go out of my way to learn the business; from reading procedures to just sitting down with them and discussing what they do. This was fairly easy to do in the smaller companies I've worked with, but now that I am in a 3,000+ person company, it has gotten a bit more difficult.
I read an article by Steve Jones about getting closer to your business. I couldn’t agree more.
I would love (and have asked) to be able to spend more time with the Business folks. Perhaps shadowing them for a week, or just writing reports so I can get a better feel for what they do with the data/information we provide them. Better yet, make it part of the on-boarding for new employees. Spend 6 weeks with various departments or something like that. Some companies have management programs that do just that.
Anyway, I believe time spent with the Business makes me a better developer and hopefully builds up a good working relationship (trust) between IT and the Business.
Labels: development, work
¶ 9/05/2007 10:09:00 PM 2 Comments Links to this post
Business Logic: In the Database or in the Application
When I started in the Data Warehouse, I began subscribing to as many BI/Data Warehousing blogs that I could find (there don't seem to be lot of them). Intelligent Enterprise (Roger Kimball) and Bill Inmon seem to be the most popular or well known. I also read Mark Rittman (and Mr. Mead), David Aldridge, Lewis Cunningham and Dratz who all seem to have more of an Oracle lean.
Today I read a post from Dan Linstedt titled "How Data Models can Impact Business." I followed the rather lengthy, but descriptive post, until I got to this part:
Today I read a post from Dan Linstedt titled "How Data Models can Impact Business." I followed the rather lengthy, but descriptive post, until I got to this part:
"Ok, I kind-of buy it, but what about Referential Integrity, when should that be enforced?
In two places:
1) When the data is captured within the application capturing it - it would clean up a LOT of these source systems, and put much more rigorous business logic (and cleaner data) into the source systems to begin with.
2) When the data is "released" for users, to reports, to screens, to output. This is when reusable common services / routines for getting data out are helpful. They implement the referential integrity in the application layer."
Now, I haven't been in IT all that long (5+ years now), but I put as much business logic into the database as I possibly can. I use the front end (APEX, woohoo!), to handle row color or something along those lines.
If you start with a good, flexible model, have a strong database team (DBAs, Developers) and you build a good API to your physical model (no INSERT, UPDATE or DELETE to any users, the only entry point to your tables being your API), flexibility and maintainability should not be a problem. Making changes should not entail a monumental effort. I suppose if the application in Mr. Linstedt's article is the only point of entry into the tables, I probably wouldn't really disagree (he's just moved it from my database API to his application), but then you have a giant bucket. Why not just use what you have paid for and build it in the database? That way, more than one application can use the same API over and over.
Labels: database, development, oracle, work
¶ 9/05/2007 09:43:00 PM 3 Comments Links to this post
