Home ›

Introduction To Sybase, Part 3

Issue 64

From Issue #64
August 1999

Jul 31, 1999  By Jay Sissom
 in
Writing a Sybase web application.
The Application Database

In our application, we use two databases. The first is the pubs2 example database. We use the titles table as the list of books. This table is read-only. We just view it; the application does not make changes to it. The second database is called book_d and it is created on two devices. The log segment is on device02 and has 10MB allocated to it. The data segment is on device01 and has 20MB allocated to it. There are five tables in this database.

  • inventory_t: this table is a one-to-one relationship to the titles table in the pubs2 database. There is one row in this table for each row in the titles table. This table contains the number of books in stock and the number on order.

  • orders_t: this table is a list of customer orders.

  • order_nbr_t: this table has one row in it. It is used to guarantee that the order number is always unique.

  • types_t: this table is a list of the types of books. It is used to populate the drop-down box on the search screen.

  • user_t: this table is a list of customers.

To provide access to the data, we use stored procedures in all cases except one. Stored procedures are SQL code stored in the database. This allows us to encapsulate procedures in the database so it doesn't have to be replicated in all your applications. It also provides a performance benefit. When the stored procedure is loaded, the SQL is precompiled. When the application runs, the server doesn't have to precompile the SQL, so the application should run faster. We used SQL in the search1 procedure, because it would change dramatically depending on the parameters given.

In the scripts, we have also created a user to access the database. Each time the CGI script runs, it logs in as this user. The end users of your application do not need to know this, and in fact, shouldn't know it. Even though this user has the minimum rights necessary to run the application, you should protect this user name and password.

This is a very small application. A few more things must be done if you put this application into production.

  • Make it look nicer. This application would have looked fine in the early 90's, but in today's world, it would need to look much better before people would use it.

  • Prepare and schedule automatic backups. Use cron to back up your data at regular intervals.

  • Prepare and schedule database consistency checks. These should be run regularly, again using cron to schedule these checks.

  • Create and tune table indexes. These tables have no defined indexes. An index is extra data about the table that will allow the database server to access the data faster.

Explaining how the database server uses indexes is beyond the scope of this article—I have a complete book on the subject. Before you put an application into production, you should load example data in all the tables and test queries to be run in your application. Based on the queries, you can make good guesses as to which indexes will be needed. There is a command in SQL that can help tune indexes. It is set showplan on. Entire book chapters are devoted to explaining this command and its output.

Summary

As you can see, using a Sybase database as the basis for your client-server applications is not a trivial task. The Sybase database server is an industrial-strength database, capable of handling hundreds of users and many gigabytes of data. Many databases are available for Linux. Of the free ones, I believe the Sybase database server is the most powerful. If you don't need a powerful database, there may be better choices. If, however, you need a high-power database to manage a large amount of data or many users, a Sybase database server would be a solid foundation.

Resources

Jay Sissom (jsissom@indiana.edu) is responsible for the web-based front end to the financial decision support data at Indiana University. He has installed and supported Sybase databases on many operating systems and has written database clients for the web. When not programming, he enjoys amateur radio and playing his bass guitar and keyboards.

______________________

Webcast
More Resources
How to Build an Optimal Hadoop Cluster to Store and Maintain Unlimited Amounts of Data Using Microservers

Realizing the promise of Apache® Hadoop® requires the effective deployment of compute, memory, storage and networking to achieve optimal results. With its flexibility and multitude of options, it is easy to over or under provision the server infrastructure, resulting in poor performance and high TCO. Join us for an in depth, technical discussion with industry experts from leading Hadoop and server companies who will provide insights into the key considerations for designing and deploying an optimal Hadoop cluster.

Learn More

Sponsored by AMD

White Paper
More Resources
Red Hat White Paper: Using an Open Source Framework to Catch the Bad Guy

Built-in forensics, incident response, and security with Red Hat Enterprise Linux 6

Every security policy provides guidance and requirements for ensuring adequate protection of information and data, as well as high-level technical and administrative security requirements for a system in a given environment. Traditionally, providing security for a system focuses on the confidentiality of the information on it. However, protecting the data integrity and system and data availability is just as important. For example, when processing United States intelligence information, there are three attributes that require protection: confidentiality, integrity, and availability.

Learn more about catching the bad guy in this free white paper.

Learn More

Sponsored by DLT Solutions