Jun 06

The first issue of “My Databases” coming next week!

ASE, Databases, IBM DB2, Microsoft, My Databases, MySQL, Oracle, Postgres, SQL Server, SQLite, Sybase 3 Comments »

As many of you know, I’ve been working on a free magazine regarding various database systems (dbms) called My Databases.  I hope to have multiple authors in future issues covering all sorts of open source and proprietary databases.

I should have the first issue done Sunday night.  I’m using OpenOffice, Scribus, Gimp, and Inkscape.

Listen to this podcast Listen to this podcast
May 01

Wordpress, MySQL and a bit of corruption caused the tags & categories to disappear

Databases, MySQL, Wordpress No Comments »

Sometime early this afternoon, the categories & tags of my blog disappeared. After a little bit of investigation (I was working quite heavily at the time), I discovered that the wp_term_taxonomy table in MySQL contained minor corruption. I ran repair table ‘wp_term_taxonomy’ and it was corrected.

Listen to this podcast Listen to this podcast
Apr 23

Is Sybase’s ASE SQL92 (ANSI SQL2) or SQL99 (ANSI SQL3) compliant?

ASE, Databases, IBM DB2, Microsoft, MySQL, Oracle, Postgres, SQL Server, SQLite, Sybase No Comments »

ASE implements a subset of SQL 92 and isn’t 100% compliant with the SQL 92 standard (no DBMS on the planet is btw).

SQL99 compliance isn’t seriously being looked at by the major commercial DBMS vendors. Disregarding the fact the the SQL standards aren’t all they are cracked up to be, the vendors have too much invested in their own proprietary SQL variants (and other components) to be 100% compliant. If they were 100% compliant with the SQL92/99/whatever standard, then wholesale migrations from one vendor to another would take place.

I believe as time goes forward the opensource DBMSs (PostgreSQL, MySQL, etc) may become far more compliant with the standards than the commercial vendors as vendor lock in doesn’t mean as much to them.

Look into what the vendors (Oracle, MS, IBM,etc) are saying what constitutes as “compliance”. Ask each vendor what parts of the SQL99 standard they will be implementing and which parts they won’t be. If any vendor says that they are 100% compliant with the SQL92 or SQL99 ANSI standard, then that particular person is lying to you. Granted, that person may have been told their DBMS was 100% compliant and believes it. An honest vendor says that they comply with features X,Y and Z of the SQL 92 or SQL 99 standards.

Personally, I have found no significant movement by any of the commercial DBMS vendors to implement the SQL99 standard. So far it has just been lip service IMHO.

Listen to this podcast Listen to this podcast
Oct 10

Porting MySQL’s date_format function to Sybase ASE 15.0.2

ASE, Databases, How To, MySQL, Sybase 1 Comment »

Did you ever wish Sybase ASE’s date formating capabilities weren’t so antique and unwieldy? Did you ever look at MySQL’s date_format() function and wish Sybase would do something like that? Yup, I’m still waiting for Sybase to fix this 15 year issue too. :(

In the meantime, using ASE 15.0.2’s user defined SQL function feature, I’ve been able to port the MySQL date_format() function to an user defined function:

Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week
%u Week (00..53), where Monday is the first day of the week
%V Week (01..53), where Sunday is the first day of the week; used with %X
%v Week (01..53), where Monday is the first day of the week; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal “%” character

Usage:

SELECT DATE_FORMAT(GETDATE(), ‘%T’)

OUTPUT:

07:50:24
SELECT DATE_FORMAT(GETDATE(), ‘%m/%d/%y %a %h:%i:%s %p %f’)

OUTPUT:

10/10/07 Wed 08:04:24 AM 730000

Code: udf_date_format.sql

CREATE FUNCTION DATE_FORMAT (@now DATETIME, @format VARCHAR(2000))
RETURNS VARCHAR(2000) AS
BEGIN
 SELECT @format = str_replace(@format, ‘%r’, ‘%h:%i:%s %p’)
 SELECT @format = str_replace(@format, ‘%T’, ‘%H:%i:%s’)
 SELECT @format = str_replace(@format, ‘%b’, SUBSTRING(DATENAME(mm, @now), 1, 3) )
 SELECT @format = str_replace(@format, ‘%M’, DATENAME(mm, @now))
 SELECT @format = str_replace(@format, ‘%a’, SUBSTRING(DATENAME(dw, @now), 1, 3) )
 SELECT @format = str_replace(@format, ‘%W’, DATENAME(dw, @now))
 SELECT @format = str_replace(@format, ‘%w’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(dw, @now) - 1), 2) )
 SELECT @format = str_replace(@format, ‘%U’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(wk, @now) - 1), 2) )
 SELECT @format = str_replace(@format, ‘%V’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(wk, @now)), 2) )

 SELECT @format =
  CASE
   WHEN DATEPART(dy, @now) = 1
    THEN str_replace(@format, ‘%u’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(wk, @now) - 1), 2))
    ELSE str_replace(@format, ‘%u’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(wk, DATEADD(dw, -1, @now) ) - 1 ), 2) )
  END

 SELECT @format =
  CASE
   WHEN DATEPART(dy, @now) = 1
    THEN str_replace(@format, ‘%v’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(wk, @now)), 2))
    ELSE str_replace(@format, ‘%v’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(wk, DATEADD(dw, -1, @now) )), 2) )
  END

 SELECT @format = str_replace(@format, ‘%c’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(mm, @now)), 2))
 SELECT @format = str_replace(@format, ‘%m’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(mm, @now)), 2))
 SELECT @format = str_replace(@format, ‘%d’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(dd, @now)), 2))
 SELECT @format = str_replace(@format, ‘%e’, CONVERT(VARCHAR(2), DATEPART(dd, @now)))
 SELECT @format = str_replace(@format, ‘%y’, SUBSTRING( CONVERT(VARCHAR(4), DATEPART(yy, @now)), 3, 2))
 SELECT @format = str_replace(@format, ‘%Y’, CONVERT(VARCHAR(4), DATEPART(yy, @now)))
 SELECT @format = str_replace(@format, ‘%X’, CONVERT(VARCHAR(4), DATEPART(yy, @now)))
 SELECT @format = str_replace(@format, ‘%x’, CONVERT(VARCHAR(4), DATEPART(yy, @now)))
 SELECT @format = str_replace(@format, ‘%H’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(hh, @now)), 2))

 SELECT @format =
  CASE
   WHEN DATEPART(hh, @now) > 12
    THEN str_replace(@format, ‘%h’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(hh, @now) - 12), 2))
    ELSE str_replace(@format, ‘%h’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(hh, @now)), 2))
  END

 SELECT @format =
  CASE
   WHEN DATEPART(hh, @now) > 12
    THEN str_replace(@format, ‘%I’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(hh, @now) - 12), 2))
    ELSE str_replace(@format, ‘%I’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(hh, @now)), 2))
  END

 SELECT @format = str_replace(@format, ‘%k’, CONVERT(VARCHAR(2), DATEPART(hh, @now)))

 SELECT @format =
  CASE
   WHEN DATEPART(hh, @now) > 12
    THEN str_replace(@format, ‘%l’, CONVERT(VARCHAR(2), DATEPART(hh, @now) - 12))
    ELSE str_replace(@format, ‘%l’, CONVERT(VARCHAR(2), DATEPART(hh, @now)))
  END

 SELECT @format =
  CASE
   WHEN DATEPART(hh, @now) > 12
    THEN str_replace(@format, ‘%p’, ‘PM’)
    ELSE str_replace(@format, ‘%p’, ‘AM’)
  END

 SELECT @format = str_replace(@format, ‘%i’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(mi, @now)), 2))
 SELECT @format = str_replace(@format, ‘%s’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(ss, @now)), 2))
 SELECT @format = str_replace(@format, ‘%S’, RIGHT(REPLICATE(‘0′, 2) + CONVERT(VARCHAR(2), DATEPART(ss, @now)), 2))
 SELECT @format = str_replace(@format, ‘%f’, RIGHT(REPLICATE(‘0′, 6) + CONVERT(VARCHAR(6), DATEPART(ms, @now) * 1000), 6))
 SELECT @format = str_replace(@format, ‘%j’, RIGHT(REPLICATE(‘0′, 3) + CONVERT(VARCHAR(3), DATEPART(dy, @now)), 3))
 SELECT @format = str_replace(@format, ‘%%’, ‘%’)

 RETURN @format
END

Listen to this podcast Listen to this podcast
Sep 28

Decision Support Systems… how far we’ve come :)

ASE, Databases, Microsoft, MySQL, Oracle, Postgres, SQL Server, Sybase No Comments »

At the dawn of Data Warehouse thinking, primitive decision support systems were poking their collective heads out of the murky depths of the early 1980s. Twenty three years later we have real data warehouses such as Sybase’s IQ. An episode of the Computer Chronicles on the Internet Archive provides a time machine:

Get the Flash Player to see this player.

Decision Support Systems (2/2/1984)

Expert systems software and artificial intelligence applications.
Host: Stewart Cheifet
Guests: Gary Kildall, DRI; Mike Thoma, Mgmt Decisn Sys; Steve Weyl, Syntelligence; Jim Chapman, Human Edge SW
Products/Demos: Strategic Simulations Epidemic, Prospector, CompuStat, Easy Scan, Sales Edge
This item is part of the collection: Computer Chronicles
Keywords: Episode year: 1984

Listen to this podcast Listen to this podcast
Aug 11

Just installed Sybase’s PowerBuilder 11 and Created a .NET application accessing a Sybase ASE server, a MySQL server and a PostgreSQL server

ASE, DBI, Databases, Events, MySQL, News, OS, Perl, Postgres, Powerbuilder, Sybase, TechWave, Windows 1 Comment »

Because the of the excellent sessions and a keynote at Sybase TechWave 2007, I’m picking up PowerBuilder 11. Thought it was dead? We all heard it over and over again from the VB, C++, .NET folk. Think again and check this out!

SPECIAL PLENARY SESSION — Advancing the Boundaries of Development: How IT developers will face the future.
Tuesday, 1-2:15 p.m. PDT. | View the recorded webcast

Using the PowerBuilder Application Server Plugin
Wednesday, 1 - 2 p.m. PDT. Speaker: Evan Ireland | View the recorded webcast

Using .NET Classes in PowerBuilder
Wednesday, 2:15 - 3:45 p.m. Speaker: Xue-song Wu | View the recorded webcast

Best Practices of PowerBuilder 11.0 WinForm/Smart Client Application Development
Wednesday, 4 - 5:30 p.m. Speaker: Harry Zhang | View the recorded webcast

The DataWindow in PowerBuilder WebForm Targets
Thursday, 1 - 2 p.m. Speaker: Frederick Koh | View the recorded webcast

PowerBuilder: Service Creation
Thursday, 2:15 - 3:15 p.m. Speaker: Sheila Wood | View the recorded webcast

Did you know that you can build .NET applications easily with PowerBuilder? I did, but I didn’t realize that it was very very easy! Say good bye to C# and VB.net for any database application, because PowerBuilder is back and BETTER than anything that Microsoft has to offer!

Most importantly, the video of the key note, Future Development Will Rely on Current Core Technologies, should be out in the next few days. I’ll post the link to it…. it ultimately was the final motivator to make me pick up PowerBuilder 11. Thanks to Jason Fentor for dragging me to the keynote!

UPDATE:   Future Development Will Rely on Current Core Technologies video is here!  When you open the video click down to “PowerBuilder 11: Accelerate your development”

It took me 10 minutes from knowing absolutely no PowerBuilder to building a working client/server .NET application connecting three wildly different databases (Sybase ASE, MySQL and PostgreSQL) and retrieving/updating data to them! TEN MINUTES! I expected an hour or two but ten minutes… I’m just completely floored!

Sorry, I’m not usually this impressed and try to take everything with a grain of salt, but… Sybase, with all your faults, dang! You did good!

For comparison: The same application took me 30 minutes to write in Perl, which I’m pretty good at.

Listen to this podcast Listen to this podcast
Aug 08

FW: Scanning your iPhone Backup Files

ASE, DBI, Databases, Gadgets, How To, Mac OSX, Microsoft, MySQL, OS, Perl, SQL Server, Sybase No Comments »

Erica Sadun wrote an application, written in Perl, to convert your iPhone backup files into a SQLite3 database:


Here’s a nice way to recover notes from your iPhone without having to mail them to yourself–although it’s not for the faint of heart. Read more at The Unofficial Apple Weblog…

It would be a simple matter to have the data imported in to your favorite DBMS if you prefer Sybase ASE, MS SQL Server, dBASE / xBase, etc.

Listen to this podcast Listen to this podcast
Apr 25

FW (Slashdot): Google Releases MySQL Enhancements

Databases, MySQL No Comments »

Developers: Google Releases MySQL Enhancements

Posted by CmdrTaco on Wednesday April 25, @11:19AM
from the what-an-exciting-morning-this-isn’t dept.
Google
An anonymous reader noted that “Google has released its internally developed enhancements to MySQL to the open source community this week. Changes include improvements in replication, high availability configuration, and performance.” It’ll be interesting to see if the changes they made are of interest to other places using MySQL.

It’s about time! :) MySQL has improved a great deal over the past year. Performance has gone way up and many of the design issues have been rectified. Google releasing their modifed MySQL code back to the community is a very very good thing indeed. Way to go Google!

Listen to this podcast Listen to this podcast
Apr 04

Virtualization and Databases

ASE, Databases, IBM DB2, Microsoft, MySQL, Oracle, Postgres, SQL Server, Sybase No Comments »

In Chris Brown’s Virtualization and ASE blog post, he brings up the question of whether Sybase’s ASE can be used in a virtual environment (VMWare, Xen, etc) but doesn’t answer it. I’ve been using various databases in virtual environments for several years, here is what I found out:

Running ASE, ASIQ, or SQL Anywhere under virtualization software such as Xen, VMWare, Parallels, etc is very useful under a number of situations:

  1. development of new applications - each developer group can have its own “db server” on the same machine
  2. testing new ebfs/releases with your applications
  3. reproducing problems either in the sybase software or in the application code - a ‘virgin’ instance that can be duplicated at will
  4. trying out new operating systems (moving from Windows to Linux or Windows to Solaris x86?) without investing in new hardware

The main caveat is that the performance stinks - databases typically require high disk i/o, memory i/o and cpu responsiveness. The virtualization software currently available, even with hardware help (newer Intel,AMD chips), are not up to the task of running a *production* database.  In a couple years… possibly.

Listen to this podcast Listen to this podcast
Mar 22

Monitoring Databases - what’s wrong with that?

ASE, Databases, IBM DB2, Microsoft, MySQL, Oracle, Postgres, SQL Server, Sybase No Comments »

Most database monitoring systems aren’t from the database vendors as you might think, but a hodge-podge of 3rd party vendors that seem to want to charge more than I make in a lifetime for database monitoring software — try finding low cost monitoring software for DB2 on the mainframe.

They typically use standardized, and often deprecated, monitor counters that when used for their product, interfere with any other monitoring products you might use.  For example, if the Operations Department is using Nimbus to monitor the network, VoIP, hosts, tape archival systems, and the database servers to ensure that they are running, what happens when the DBAs want to use DBA Expert? The two products (keep in mind that I chose the products for the example at random) will trip over each other - neither will provide reliable metrics of the databases.

The front ends for the monitoring products always seem to show a fancy GUI full of bright colors, dials, graphs, and the latest and greatest designer kitchen sink. They are very rarely willing to provide any documented API or mechanism for you to obtain the data from their product without a nasty NDA. The premise is that you will use their front end to display and analyze the monitoring metrics.

The database vendors, themselves, are largely to blame. The monitoring APIs that they offer assume that you will only be using a single monitoring system.  For example, in Sybase’s ASE, the new API is to use their MDA tables to obtain performance metrics but the problem comes in when the monitoring software would use multiple methods to obtain additional information that may not be (easily) obtainable from the MDA tables.  sp_sysmon will reset several monitoring counters unless you call it with the ‘noclear’ option.  Unfortunately, the ‘noclear’ is not widely known and rarely used in the monitoring software.  Of course, this is just an example of multiple monitoring APIs from a database vendor.

You know what? I don’t care about the vendors’ fancy front ends. Give me a web service that I can access and use the monitoring metrics in another application, a PDA, etc. A few vendors have tried to offer an API but they are often so damned complicated that you would have had to work at the company to understand the API.  Don’t even get me started on vendors keeping their APIs updated.

Update:  Thanks goes to Peter Dorfman of Sybase to helping clarifying that the MDA tables in ASE ‘clear’ only on a connection basis.  That means if you look at monDeadlocks on connection #1 twice, the first select might show 5 rows and the second 0 rows.  If you ran the select on connection #2 sometime later, you would see the 5 rows plus any other deadlocks that might have occurred since then.  I wasn’t very clear on that as I was (in my head) also including sp_sysmon and other monitoring options that would conflict with the MDA tables.

Listen to this podcast Listen to this podcast
Mar 10

TeamSybase Annual Meeting: RepServer & MySQL

Annual Meeting, Databases, MySQL, Replication Server, Sybase, TeamSybase No Comments »

The replication capabilities of MySQL are relatively primitive when compared to the more mature Replication Server from Sybase. Sybase expects to support full featured replication using Sybase’s Replication Server in late 2008 with MySQL. This would be an add-on (RepServer Options) to the standard Replication Server product. Keep in mind that this is a tentative time frame and it can arrive sooner or later than 2008.

Listen to this podcast Listen to this podcast
Nov 03

Sybase ASE 15 vs MySQL 5

ASE, MySQL, Sybase No Comments »

Dhimant Chokshi at Sybase’s Server Performance and Engineering Development Group (aka Sybase ASE Engineering), did an internal benchmark of ASE 15 and MySQL 5. 

Adaptive Server® Enterprise (ASE) is a highly scalable, mission critical database server that provides a portable, multi-platform system for high performance data management. The new features of ASE 15.0 deliver an operational advantage with lower cost and risk, and they achieve higher performance on mixed workload systems.

Testing with ASE 15.0 has shown an advantage in overall performance for transaction processing over MySQL 5.0, and many complex queries show significant improvements.

The new query optimizer and execution engine greatly enhance the performance of complex queries. ASE 15.0 features patented query-processing technology that increases query performance and, when coupled with features such as Computed Columns and Function Indexes, significantly reduces reporting time on mission critical reports.

Sybase ASE 15.0 provides new features and functionality that can handle severe user demands for performance and economy. This paper will discuss how ASE 15.0 delivers advanced DSS performance on data sets of all sizes—from small to the VERY large—while controlling your costs. Some of the features that improve query performance are hashbased algorithms, merge joins, and N-ary joins.

Take his benchmark with a great big chunk of salt because you have to remember how companies do benchmarks:

  • optimize the stuffing out of YOUR product
  • default or minimal optimizing of the competing company

The benchmark doesn’t coincide with my experience but whether one DBMS is faster than another depends on alot of things. 

Listen to this podcast Listen to this podcast
Aug 18

FW: Sybase vs. Oracle: Users speak out

ASE, Microsoft, MySQL, Oracle, Postgres, SQL Server, Sybase No Comments »

By Elisa Gabbert, Assistant Editor
15 Aug 2006 | SearchOracle.com

A recent column by Mich Talebzedah touting the merits of Sybase on Linux over Oracle as a database management system (DBMS) has our readers riled up.

Anil Mahadev is a DBA and tech writer whose company, based in India, manages Oracle, SQL Server, DB2 and Sybase databases. Having worked with both Oracle and Sybase systems, Mahadev finds that Sybase is "definitely easier to manage and use." He cited seven reasons to opt for Sybase, including programming advantages, startup time and ease of installation. He also concurs with Talebzedah’s claim that switching from Sybase to Oracle is rarely worth the trouble.  Read more….

 

I have to agree with both sides of the discussion.  Sybase’s (and Microsoft’s for that matter) Transact SQL is very limiting for a developer.  Oracle’s PL/SQL is a far more mature SQL dialect.

Compared to other SQL dialects, TSQL appears to be archaic in both functionality and extendability.  Both vendors have made attempts to developer usability with .NET (Microsoft) and Java (Sybase) with varied success.  When calling in an issue involving either a .NET assembly or a java class, Microsoft’s support staff tend to be better trained than Sybase’s support staff, in so much as Microsoft’s support staff are able to determine whom internally they need to contact for help. 

Sybase’s support of Java in the database is limited to a very small number of tech support people.  There isn’t a whole lot of demand for Sybase’s java in the database due to the high cost of this add on and ongoing stability and performance issues with the JVM being run from within ASE itself.

The extendability of the open source DBMSs MySQL and Postgres far exceed their proprietary counterparts (Oracle, MS SQL Server, Sybase ASE).  Not only are there many interpretors that can be used in lieu of SQL, but you can create your own with little trouble.  The commercial companies should take note of this.

Listen to this podcast Listen to this podcast