Stored procedures are programs too – forget that, and the results can be hazardous to your organization’s Y2K health, according to ServerLogic Corp.
That’s why the Bellevue, Wash.-based software vendor has developed SP/2000, a tool designed to root out date-related problems in stored procedures.
Found in most databases, stored procedures are SQL programs activated by client devices or database triggers. They tell the database what to do when handling data for specific transactions. However, many stored procedures perform financial calculations, and may be written to show dates in a two-digit format – meaning 00 could be interpreted as 1900.
Richard Allen, marketing manager with ServerLogic, said stored procedures are easy for Y2K project teams to overlook.
“The primary reason for that is you have front end developers who are responsible for their programs, and then you have the DBAs that are responsible for databases – but the stored procedures falls between the cracks; kind of a no man’s land.”
If corrupt data from within the database finds its way to an employee’s desktop, problems can result, Allen said.
Anyone familiar with year 2000 software will have little difficulty using SP/2000, according to ServerLogic. Because stored procedures are often written in their own script, SP/2000 first exports them to ASCII files. It then generates a report, based on user preferences, detailing the number of lines of code that contain potential problems.
SP/2000 looks specifically for embedded two digit dates, improper date string manipulation, database-specific date formats and incorrect date function usage.
Once the analysis is complete, a developer can make the required fixes within SP/2000 with the click of a mouse, Allen said. “And after you do the remediation, it’s done. You now have this text file that’s clean of Y2K issues, and you then export it back into the database.”
As part of a Y2K tool arsenal, SP/2000 is suitable as a primary scanner, and should be used as such, given the lack of awareness around stored procedures, said Jim Duggan, analyst with Stamford, Conn.-based Gartner Group Inc.
“People tend to be less familiar with stored procedures than they are with some of their other code,” Duggan said. “It’s an area that has been neglected in part because people don’t understand that a stored procedure is a program. They think of their database as something that comes in a box, and (wonder) how it could possibly have calculations in it.”
Duggan adds that SP/2000 is designed for use by programmers, and not for the average end-user.
“We did not expect to find problems with our stored procedures as a result of Y2K and international date formats,” said Ty Brown, a systems analyst with Guilford Mills, a Greensboro, N.C.-based textile manufacturer. “SP/2000 quickly searched more than 3,200 stored procedures and triggers. It found three stored procedures requiring changes for Y2K, as well as 86 stored procedures requiring changes for international date formats.”
Besides SP/2000, ServerLogic is also offering PB/2000, a year 2000 tool for PowerBuilder applications.
SP/2000 runs on Windows 9x or NT, and requires a PC or workstation with a Pentium 100MHz CPU, 16MB of RAM and 10MB of available hard drive space. It works with most major database products, including those from Oracle, Informix, Microsoft and Sybase.
SP/2000 sells for US$3,000, and a evaluation copy can be downloaded from the ServerLogic Web site at www.serverlogic.com/sp2000.htm.
ServerLogic Inc. in Bellevue, Wash., is at (425) 803-0378.
— With files from IDG News Service