 Smart codeSo, the other day a developer asked me to come up with a table to tally votes. Basically, there are six choices, and customers can vote once per day. I asked if there were any other constraints, and there were none. Here's what I did. [Planet MySQL] The Rosetta Stone for DB2 Address Spaces Need a translation guide for address space terms between DB2 for z/OS and DB2 for LUW? Read on... Free SQL Server 2005 Training Microsoft's giving away free SQL Server 2005 courses ! MySQL 5: Transferring database from MySQL 3.23.XX Earlier, when I wrote about upgrading to MySQL 5, I didn't transfer any databases from MySQL 3.23.56 to MySQL 5 because I was going to use the new MySQL 5 installation as a client and load all old tables to MySQL 5 server on a separate machine. Today I needed to transfer some tables from MySQL 3.23.56 to new MySQL 5 (on the same machine) so here I am writing this post. [Planet MySQL]  Sun and PostgreSQLDespite what I thought was a pretty clear annoucement, there seems to still be a lot of confusion about what "Sun Postgres" means. So I'm presenting a little Q&A; here, and encourage readers to post comments with additional questions. Wireless, Battery Powered MySQL Cluster Pictured here is an image of a wireless, battery powered MySQL cluster. Yes, it works. The setup is a 12" PowerBook and 15" PowerBook and a 15" iBook. [Planet MySQL] Stored Routine Debugger with Breakpoints for MySQL 5.0 (Part 1: How to implement a basic first version) There was some discussion recently in the forums on how to debug your stored procedures and functions in MySQL 5.0. A lot of developers use SELECT statements to echo debugging output from within stored procedures, some use temporary tables and INSERTs to log it -- but you can even take one big leap forward and implement a Stored Routine Debugger that supports breakpoints and allows to inspect and even change tables and local variables while your routines are halted. [Planet MySQL]  Formating Output of SQLI mentioned a few posts ago about Oracle's SQL*Plus client program and how great it was at formatting output in different and novel ways, providing you know the commands to do it. One of the biggest problems is that the default linesize (the width of the screen buffer used to display rows of data in a table) is small and only actually displays something if you have small amount of columns. The answer of course is to change the size of the linesize parameter... [Planet MySQL] DBA Best Practices? What are the things that you do, or want to do, on a daily basis to manage your database infrastructure? How to do MySQL replication with 669 Mbit/s over a distance of 226 kilometers Today I moved our MySQL replication slave all across Switzerland, from our main office in Zurich to our branch office in Lausanne. We just thought it was not the best idea to have the master and the slave do their work next to each other (for backup reasons). [Planet MySQL] Using Perl in PostgreSQLPerl's DBI module makes it easy to use a database. That's not the only way to interact with a database, though. If your PostgreSQL database doesn't do exactly what you want, you can write server-side extensions--in Perl. Andrew Dunstan shows how to enable PL/Perl and how to write triggers with it. [Databases] Stored Procedures in MySQL 5.0 MySQL's stored procedures do not provide for direct support of passing and using object names in the procedure, but it's pretty easy to work around this limitation. [Planet MySQL] Export Data To Excel Last month we explored the process of importing data from Microsoft Excel. This month, we look at how to get your data OUT of Access and back into Excel.  db4free optimizationsI'm currently preparing for the MySQL professional exam, and I could use the new skills from learning for this exams immediately for re-configuring the db4free.net server to offer better performence to its users... but there are more benefits from learning for MySQL certification exams than that. [Planet MySQL] How DB2 for LUW Stripes Data To what extent do you understand DB2 extents? Stored Function Overhead in MySQL 5.0 not that bad after all? In my post on how to implement ADD_TO_SET() and REMOVE_FROM_SET() I came to the conclusion that the use of stored functions generates tremendous overhead. In a reaction MySQL developer Per-Erik Martin pointed out to me that this might not be true but just a consequence of a very strange behaviour of MySQL's built-in BENCHMARK() function. [Planet MySQL] SAP's Agassi responds: I love open source Shai Agassi responded on his blog and in the media to my post (and presumably others) last week regarding his comments about open source. I took his advice and went back and listened to the ZD podcast to make sure I wasn't overreacting. I was able to find all of the quotes I used from the VNUnet article and they weren't out of context enough to alter their meaning, contrary to what Agassi stated. [Planet MySQL] Oracle Database 10G Express Edition It looks like I am going to have to deviate from my intended topic of discussion for one blog. Oracle has recently announced 10G Express Edition with great fanfare. I thought that I would provide you with a quick overview of the product. So how robust is Ingres? CA is spinning off Ingres, more or less, to an investment fund led by Terry Garnett, who will also be interim of CEO. Now, I've given Terry a lot of grief over the decades. It started by accident, when I bashed his presentation of Lightyear at a 1984 party in Rosann Stach's... [Planet MySQL] Finally, a real database market We heard yesterday that CA is spinning off Ingres to form an independent company. My friend and alter-ego, Dave, asks, "Who cares?" ... The market has been clamoring for competition across the software industry. This move, as well as the launch of EnterpriseDB, offers database customers real choice again. [Planet MySQL] Starting a New Article Series Several months ago I finished a series of articles on Visual Basic.NET and MySQL. I have decided to start a new series that will focus more on new features of MySQL 5 and Visual Basic 2005. [Planet MySQL] Using a SP to split up a table Lets assume that we have one big table with forum posts from quite a long period. We want to split this big table up in several smaller tables for every month. Here's a stored procedure for that. [Planet MySQL] Marten Mickos on Innovation There are two good essays by MySQL CEO Marten Mickos this week, both in the form of a question. Marten has a curious way of provoking people which is to ask the hard questions and let people draw their own conclusions. You may agree or disagree with Marten's views, but you'll certainly end up thinking about the issues. Always-On: Will they all go open source? European Business Forum: Is europe losing its edge? [Planet MySQL] Add the missing ADD_TO_SET() AND REMOVE_FROM_SET() for MySQL's SET type MySQL has a datatype for the storage of sets in one simple column. It provides a FIND_IN_SET() function to deal with this in SELECT queries. However it still lacks an ADD_TO_SET() and REMOVE_FROM_SET() to use in UPDATEs. The usual workarounds you find on the web generate warnings or even wrong results with some special cases. So with MySQL 5.0 it's definitely the time to build a clean emulation for these two functions using stored routines. [Planet MySQL] SQL Server 2005 - SQL Server Integration Services - Part 12 - Logging Continuing his series on SQL Server Integration Services, Marcin Policht takes a closer look into the various characteristics of SSIS logging and their corresponding configuration options. Writing SQL Results to a FileMany of us old skool Oracle developers still prefer to use the good old SQL*Plus command line client, on of the reasons being that no matter where we are, be it on our desk top, on a clients site or even remotely via an ssh client we can be pretty sure a version is available. [Planet MySQL] Data quality is no walk in the park. Data quality is not an automatic happening! Data quality requires the correct processes, measures and responsibilities followed by both IT and Business. Feedback on yesterday's entry indicated that people do not understand the data life cycle. A common understanding of the data life cycle is important to both Business and IT. Oracle's purchase of InnoDB, their release of Oracle Express, and the effect on MySQL As most of you will know, the big news recently in the MySQL world, besides the launch of the long-awaited MySQL 5, involves Oracle. Oracle has done two things that will catch the attention of MySQL users. First was the purchase of Finnish company Innobase OY and secondly Oracle's release of their free Express edition database software. The advantages for Oracle are clear, and they have a wide scope to fine-tune their strategy. MySQL has more of a tightrope to walk, and the MySQL user community will be watching with interest, and some concern. Installing MySQL MaxDB Synchronization Manager This series of articles describes how to install and configure the MySQL MaxDB Synchronization Manager. In a step by step approach the author guides you through the process of obtaining, installing, testing, configuring and running MaxDB and the Synchronization Manager. [Planet MySQL] Toad for DB2 is here! In my job I get to talk to a lot of database developers. Many of these folks have significant experience building applications that use Oracle databases. I always ask "what tools do you use?"  What's an Instance? And where is my Subsystem?In this posting from my series on DB2 LUW for a z/OS DBA I'll fill you in on what an instance is and how databases are slightly different on the two platforms. Data on demand for data warehouses Data on Demand is a paradigm of storing data for a data warehouse with huge storage requirements. There are cases where creating statistical tables is not enough, and the detail data should be kept online for long periods. This technique has been applied in cases of MySQL DW associated with industrial production control, saving up to 80% storage space and improving performance and scalability. [Planet MySQL] Database Security and Patches – Part 1 Part 1 of this series provides a review of database security and looks at a well-known security hole in Oracle 9.2.0.1. Powerful MySQL Aggregate Functions When I started exploring the MySQL SQL Dialect, one of the first things that struck me was the quite support for some quite exotic aggregate functions. Some of the are really useful too, and frankly, I'm at a loss why products like Microsoft SQL Server and Oracle don't support them too. [Planet MySQL] Administering Terabyte Tables - An Interview with Data Warehouse DBA Jim Dojonovic My unit is responsible for administering several large data warehouses. The entire database is multiple terabytes and rapidly growing at hundreds (and hundreds) of MEGs per month. We estimate that within 2 years, the database will be close to 10 terabytes in size. I thought it might be interesting to interview our "big data" DBA, Jim Dojonovic.  Ralph Kimball - Real-time Data Warehouse Design ChallengesI "attend" webinars on a somewhat frequent basis. I can usually catch them around lunch time (I'm on the east coast), and since I don't have to travel it sometimes works out better than a live, in person seminar... They all have a bit of marketing, though. That's what pays for it, I guess. Inherent data quality You can describe inherent data quality in terms of four processes: data capture, definition, sourcing and usage. This entry describes how to measure inherent data quality. Replication Monitor preview posted I posted the source to my replication monitor (rep_mon.pl) over at sourceforge, along with a screen shot of it in action. It doesn't look all that fancy, but when you've got a dozen MySQL servers, it sure helps to have one script that checks them all at once! [Planet MySQL] The Wizard revisited: Dynamic Crosstabs using MySQL Stored Procedures Since I read the excellent article MySQL Wizardry by Giuseppe Maxia some years ago I wanted to implement crosstabs in pure MySQL -- and had to wait a long time until 5.0 made it possible... After Roland Bouman blogged on this same topic yesterday, it was definitely the right moment to try out the real thing: Fully dynamic crosstabs in a pure MySQL stored procedure. [Planet MySQL] Of Railroad Barons and Crippleware Oracle XE, like the others, is what's known in the software world as "crippleware".  Load balancing across multiple MySQL serversI have been experimenting with many different ways to load balance connections across multiple MySQL database servers. I have come across many different possible solutions. For the longest time, I was convinced that the hardware method was the way to go, until I suffered sticker shock. A good quality load balancer can cost upwards of $25K - $75K!!! Yikes!! [Planet MySQL] Upgrading MySQL - Upgrade MySQL to 5.0 This guide will show you how to upgrade to MySQL 5.0 on a Linux Box. Also see MySQL 5.0 article. [Planet MySQL] Creating Crosstabs in MySQL In some cases, it would be very nice to be able to get a crosstab result from a query. You can't really do that in MySQL, at least not directly; as matter of fact, you can't do it in most rdbms-es. If you really need a crosstab, here are some techniques that might be of use. [Planet MySQL] mysqlsniffer v1.1 released mysqlsniffer (a tcpdump clone for watching MySQL traffic on a network) v1.1 has been released. [Planet MySQL] What DBAs Don't want Managers to Know Do you keep your management in the dark? Day Three: Performance TuningToday is the MySQL performance tuning workshop. Here's a good quote: Performance costs money, whatever road you take. [Planet MySQL] MySQL UC Call for Papers Nov 7 Zack Urlocker announced that the last call for papers at the 2006 MySQL Users Conference. is coming, Get your submissions in by November 7! [Planet MySQL] MySQL 5.0's Pluggable Storage Engine Architecture, Part 2: A Quick Test Drive MySQL.com shows how simple it is to use MySQL's pluggable storage engine architecture and see the impact different storage engines can have on performance. [Planet MySQL] sodoku, or... now we can do everything in stored procedures! Stored Procedures are a useful -- but much abused -- feature. Why abused? Well, sometimes it just doesn't make any sense to put certain functionality into the database server. Arjen Lentz considers this idea. [Planet MySQL] Upgrading from older versions to MySQL 5.0 I sometimes read the posts in the MySQL Newsgroup... There are often questions that give me interesting material for this weblog. Such as how to update to the latest MySQL 5.0 version -- not only from 4.1, but also from older versions like 3.23. Here's how I would do it. [Planet MySQL] Oracle 10gR2 RAC on VMWare - A First LookYesterday I wrote about finding VMWare Player, a free virtual machine for running pre-built environments. I installed it and played with the browser appliance while I downloaded the pre-built Oracle 10gR2 RAC on Red Hat environment. I now have the Oracle and Red Hat downloads installed. Compress SQL Server backups using WinZip – Part II Part two of this series discusses how to delete the original backup file, after zipping the backup file using WinZip, and how to uncompress the compressed backup files using the WinZip command line wzunzip.exe. Fun With Triggers I was trying to come up something funny|cute that used new features in MySQL 5.0 and settled on a quick little bit with a coffee pot and mug. [Planet MySQL] Using an updatable VIEW as a constraint "... You can do detailed server-side data validation with a view that has the CASCADED CHECK OPTION enabled. Of course, this only works because nearly all MySQL views are actually updatable (where possible). That means that you can do INSERTs, UPDATEs and DELETEs on a view! Think about the fab implications of that for a moment." [Planet MySQL] A MySQL MantraAndrew Gilfrin on Planet MySQL . . . "If you can do it in a single sql statement - do so. If you cannot, do it in as little PLSQL as you can. If you cannot, because plsql cannot do it, try some java (eg: sending an email with an attachment) If you cannot do it in java (too slow, or you have some existing 3gl code already) do it in C as an extproc. This is great advice, at least for Oracle developers, in that it's making the most of each method at the appropriate stage." [Planet MySQL] Denormalization and (lack of) constraints Data Management Blogs . . . Just back from a week of insane workload: sorry. However, a few points you may not have been considering: Do you really think you can make your database perform better without sacrificing something? Nesting MySQL Cursor Loops Rolan Bouman on Planet MySQL. . . Handling cursor loops in MySQL keeps puzzling people. Single Cursor Loops The common case is to have a simple cursor loop. Each record is fetched from the cursor until there are now more records, and for each record some work is performed. [Planet MySQL] Database Performance and some Christmas CheerJames F. Koopmann in Data Management Blogs . . . "Performance can be defined as the ability of a system to deliver the results based on the request of the users, while keeping them satisfied. The key point to remember is satisfaction. If current database key indicators reveal that the database is running optimally, but users are not satisfied with the response times, is there a need for tuning? The answer is yes." UDF registry Arjen Lentz on Planet MySQL . . . "We're working on something comprehensive for this, but meanwhile you may be interested to have a look at http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/ which catalogs a nice little collection of User Defined Functions for MySQL." [Planet MySQL] ANTs Benefits as Oracle Acquires InnoDB Planet MySQL . . . "ANTs Software Inc is offering its ANTs Data Server as an alternative for MySQL customers and prospects dismayed by this acquisition. ANTs Data Server (check my earlier post about ANTs Data Server) is a full-featured, standards-compliant relational database management system based on a breakthrough, high-performance SQL query execution engine that incorporates innovative, lock-free operations. " [Planet MySQL] Reporting on your database with a single SQL command (or why I love UNION ALL) Data Management Blogs . . ."Want to see the mother of all SQL statements? Here is a single statement you can use to report on many of the key status elements of your database." Let's begin our Compatibility Mode discussionsEnterprise Back Office Blogs . . . "You have completed all of the suggested planning, and the tapes have arrived. It is finally time to start your migration from DB2 Version 7 to Version 8. The most important thing you want to remember now is that this is just a migration. Sure, V8 is probably the most significant new release of DB2 since Version 1. However, it is still just a migration." Oracle's Open Source Database Endorsement Zack Urlocker on Planet MySQL . . . "Oracle's acquisition of InnoBase, the guys who developed the InnoDB storage engine, is interesting because it shows how significant open source database technology has become in the industry. Oracle, along with IBM, helped legitimize Linux in the enterprise, and are now endorsing open source database technology." [Planet MySQL] InnoDB and the Compromise of Dual Licensing Data Management Blogs . . . Less than 48 hours ago, Oracle bought Innobase. For me, it has also thrown the issues with dual-licensed, single-company open source projects into sharp relief. Oracle Collections: A Definition in Plain English Part 2Data Management Blogs . . . In part 1 of this series I covered Associative Arrays and Record types. Today I will be covering Varrays and Nested Tables. A MySQL sniffer - mysqlsniffer v1.0 release Hack MySQL . . . It all started because Perl DBD-mysql dies after calling a stored procedure. Someone wrote a patch for this, but it didn't work for me. Wanting to know why, and the error message being "Lost connection to MySQL," I decided to write a MySQL sniffer and see if I could figure it out (and I did). I learned a whole lot about the MySQL protocol, and I like learning such things, but why anyone else would have use for a MySQL network protocol sniffer, I don't know. Never the less, here is a MySQL protocol sniffer. No documentation yet; it's just like any other sniffer: compile, run as root, tell it which network interface to listen on. [Planet MySQL] Little has changed, we're still Data processing Development and Integration Blogs . . . A few weeks ago one of our IT Managers asked me how best to communicate Architecture and Design issues to our Business users. The premise was that although we had produced wonderful UML diagrams, pretty as they were,they weren't really telling them anything.  Continuent: clustering software for databasesAndy Oram . . . Now there's an open-source, database-independent solution for simple database clustering needs. [Databases] Doing INTERSECT and MINUS in MySQL Carsten Pedersen on Planet MySQL . . . Recently, I was asked to contribute a series of entries for the MySQL Network Knowledge Base (to which you get access only if you're a MySQL Network subscriber). I thought the following ones were kind of fun, so I asked the maintainer if I would be allowed to post them here - and maybe someone could tell me if I'm completely off-track or have suggestions for improvements. [Planet MySQL] 
|
|
Sponsored by:
|
|
