Introduction To Sybase, Part 3
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.
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.
Jay Sissom (firstname.lastname@example.org) 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.
Fast/Flexible Linux OS Recovery
On Demand Now
In this live one-hour webinar, learn how to enhance your existing backup strategies for complete disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible full-system recovery solution for UNIX and Linux systems.
Join Linux Journal's Shawn Powers and David Huffman, President/CEO, Storix, Inc.
Free to Linux Journal readers.Register Now!
- Sony Settles in Linux Battle
- Download "Linux Management with Red Hat Satellite: Measuring Business Impact and ROI"
- Libarchive Security Flaw Discovered
- Profiles and RC Files
- Maru OS Brings Debian to Your Phone
- Understanding Ceph and Its Place in the Market
- Snappy Moves to New Platforms
- The Giant Zero, Part 0.x
- Git 2.9 Released
- Astronomy for KDE
With all the industry talk about the benefits of Linux on Power and all the performance advantages offered by its open architecture, you may be considering a move in that direction. If you are thinking about analytics, big data and cloud computing, you would be right to evaluate Power. The idea of using commodity x86 hardware and replacing it every three years is an outdated cost model. It doesn’t consider the total cost of ownership, and it doesn’t consider the advantage of real processing power, high-availability and multithreading like a demon.
This ebook takes a look at some of the practical applications of the Linux on Power platform and ways you might bring all the performance power of this open architecture to bear for your organization. There are no smoke and mirrors here—just hard, cold, empirical evidence provided by independent sources. I also consider some innovative ways Linux on Power will be used in the future.Get the Guide