Continue in 2 seconds

Modern Database Administration, Part 1

  • September 01 2001, 1:00am EDT

As database management systems change and become more complex, the role of database administration must adapt. Database management systems are taking on more functionality and being used in more situations than ever before. No longer do databases store only traditional simple structured data types such as characters, numbers and dates; they also store more complex unstructured data types such as audio, video, images and compound documents. As a result, databases are growing in size. However, this is not the only factor causing database growth. The business intelligence needs of today's organizations have caused data warehouses to grow large and out of control.

Furthermore, today's databases do not just store data. They also store processes that act upon that data. Stored procedures, triggers and user-defined functions managed by the database management system (DBMS) place new requirements on database administrators. Additionally, databases are being placed on more diverse platforms than ever before, including mainframes, midranges, workstations, PCs and even PDAs. Databases are increasingly being connected to the Internet to enable e-business applications which further complicates the way in which databases are managed. All of these new technologies and trends impact the job of the database administrator (DBA).

Becoming an E-DBA

In this day and age, there is no doubt that Internet usage is pervasive. Every business is being impacted by the rapid adoption of Internet technologies. Indeed, a new term, e-business, has been coined to describe the changing nature of business when it is conducted online.

An e-business is always online; it never closes. Regardless of the time or date, customers expect e-businesses to be available to serve their current needs. Full functionality and nonstop availability is expected.

An e-business must be available and operational 24 hours a day, every day of the year. An e-business must be prepared to engage with customers at any time or risk losing business to a company whose Web site is more accessible. System outages, whether planned (for maintenance and tuning) or unplanned (due to hardware failure, bugs or viruses), are the enemy of the successful e-business.

A key component of modern Web-based applications is access to and storage of mission-critical data. The most reliable, time-tested method of storing persistent data is inside a database. The traditional DBA is required to implement databases, optimize performance and ensure high availability. This role, however, is greatly expanded and becomes more difficult for e-businesses. With additional responsibilities, let's call this new role the e-DBA.

Minimizing downtime is the first duty of the e-DBA. Industry analysts estimate that as much as 80 percent of application downtime is due to application software failures and human error (see Figure 1). Hardware failures and operating system crashes are not as common as they once were. Instead, downtime more frequently results from problems such as improperly entered transactions, improperly timed batch runs or running a program using the wrong input files or parameters. It is possible for DBAs to reverse the effects of application failures using the database log and solutions available from vendors. With a high-speed transaction recovery solution in place, it may be possible to recover from an application failure without an outage.

Figure 1: Causes of Application Downtime (Source: Gartner)

Another interesting aspect of downtime is whether it is planned or unplanned. Planned outages occur more frequently and, therefore, can have a greater impact on overall availability than unplanned outages (see Figure 2). Planned outages include software upgrades, database changes and regularly scheduled maintenance tasks such as reorganization. What does this mean for the e-DBA? A shift in thinking is required. Although it is important to plan for recovery from unplanned outages, it is even more important to minimize downtime resulting from planned outages because they occur more frequently than unplanned outages.

Figure 2: Downtime versus Availability

Whenever possible, avoid downtime altogether by managing databases while they are online. One example is concurrent database reorganization. Although traditional reorganization scripts and utilities require the database objects to be offline, new and more efficient reorganization utilities can reorganize data to a mirror copy, swapping the copies when the reorg process is complete. Because the database can stay online during the process, downtime is eliminated. While these techniques require more disk space, they will not disrupt online businesses. New and emerging technologies are becoming available to perform other utility tasks such as backing up and loading databases while the databases remain online for read and update.

Another example of online database administration is tweaking system parameters. Every DBMS product provides system parameters that control the functionality and operation of the DBMS (for example, the DSNZPARMs in DB2 or the init.ora parms in Oracle). Usually, the DBMS must be restarted to make changes to these parameters. In an e-business environment, this downtime may be unacceptable. There are products that enable DBMS system parameters to be modified without recycling the DBMS address spaces. Depending upon the e- business applications impacted, the affected system parameters and the severity of the problem, a single instance where parameters can be changed without an outage can justify the investment in this type of tool.

Another way to minimize downtime is to automate routine maintenance tasks. For example, changing the structure of a table can be an arduous task. The structure of relational databases can be modified using the ALTER statement, but ALTER cannot be used for every type of change. Database change management tools are available that allow you to make any desired change to a relational database using a simple online interface. The tool automatically generates scripts that understand the correct way to invoke database changes. When errors are avoided because of automation, downtime is diminished, resulting in greater online availability.

Sometimes, downtime cannot be avoided. If this is the case, you should strive to minimize downtime by performing tasks more quickly. Use the fastest and least error-prone technology and methods available to you. For example, if a third-party recover, load or reorg utility can run in one- half to one-quarter of the time of a traditional database utility, consider migrating to the faster technology. In many cases, the faster technology will pay for itself much more quickly in an e-business because of the increased availability requirements.

Not all outages can be eliminated. Consider a faulty memory chip or disk drive. No one can predict when hardware will fail. Certain fail-safe methods can be implemented with redundant storage or automatic propagation of changes to a mirrored system, but at some point a problem can still occur and cause an outage. If this is the case, the e-DBA needs to be prepared to recover as rapidly as possible to reduce the duration of the outage. Using recovery tools, e-DBAs can quickly recover databases after unplanned outages. The length of the outage can be reduced because these tools automatically build recovery scripts that use faster recovery methods than traditional utilities.

Downtime is not the only issue faced by the e- DBA. E-DBAs also need to be prepared to work with Java. Java's main benefit is portability. It enables developers to write a program once and run it on any platform, regardless of hardware or operating system. Additionally, Java is suitable for enabling animation for and interaction with Web pages. Using HTML, developers can run Java programs, called applets, over the Web. Java applets are automatically downloaded and executed by users as they surf the Web. Even though Web interaction is one of its most touted features, Java is a fully functional programming language that can be used for developing general-purpose programs, independent from the Web.

As organizations move to the Web, Java will gain popularity. Indeed, the growth of Java in recent years almost mirrors the growth of e-business. Java will be used to write Web applications, and those applications will need to access data which is invariably stored in a relational database. When programs access relational databases, performance problems can occur. If Java is used to develop Web-based applications that access relational data, e-DBAs will need to understand Java, at least at a rudimentary level.

Most DBAs participate in application tuning, debugging or designing. Wise shops ensure that all application code is submitted to a performance review before it is promoted to the production environment. The performance review is conducted to ensure programs are efficient, effective and properly coded. Most experts agree that 70 to 80 percent of poor relational performance is caused by poorly written SQL and application logic. It is intelligent to review programs before they are moved to production. If the e-DBA does not understand Java, he or she cannot possibly understand how to tune the program to work optimally with a relational database.

Another reason Java is popular for Web-based applications is its ability to enhance application availability. Availability is the name of the game for e-businesses. Java is a late-binding language. After a Java program is written, it is compiled. However, the compiler output is not pure executable code. Instead, the compiler produces Java bytecodes. The Java bytecodes are interpreted by a Java virtual machine (JVM). Each platform has its own JVM.

Java code can be deployed as components while the application is running. Therefore, you do not need to stop the application in order to introduce code changes. The modified code can be downloaded over the Web as it is needed. In this way, Java can enhance availability. Additionally, Java simplifies complicated turnover procedures and the distribution and management of dynamic link library (DLL) files required of client/server applications.

One of the traditional roles of the DBA is to monitor and manage the performance of database access. With Java, performance can be a problem. Because it is interpreted at run time, a Java program is usually slower than an equivalent traditional, compiled program.

Just-in-time (JIT) compiler technology can be used to speed Java program execution. Using a JIT compiler, bytecodes are interpreted into machine language just before they are executed on the platform of choice. This can enhance the performance of a Java program, but a JIT compiler does not deliver the speed of a compiled program. The JIT compiler is still an interpretive process and performance can still be problematic. Another approach is a high performance Java (HPJ) compiler. A HPJ compiler turns bytecodes into true load modules. It avoids the overhead of interpreting Java bytecodes at runtime. However, not all Java implementations support JIT or HPJ compilers. E-DBAs need to be aware of the different compilation options and provide Java development guidelines for the programming staff.

Additionally, e-DBAs need to know how to access databases using Java. There are two options: Java database connectivity (JDBC) and structured query language for Java (SQLJ).

JDBC is an application programming interface (API) that enables Java to access relational databases. Similar to ODBC, JDBC consists of a set of classes and interfaces that can be used to access relational data. Anyone familiar with application programming and ODBC (or any call-level interface) can get up and running with JDBC fairly quickly. JDBC provides dynamic SQL access to relational databases. Using JDBC, theoretically at least, you should be able to write an application for one platform and deploy it on other platforms. The application should be portable if you use the correct JDBC drivers for the database platform.

SQLJ provides embedded static SQL for Java. With SQLJ, a translator must process the Java program. For those who are DB2 literate, this is just like precompiling a COBOL program. All database vendors plan to use the same generic translator. The translator strips the SQL from the Java code so that it can be optimized into a database request module. It also adds Java code to the Java program, replacing the SQL calls. Now the entire program can be compiled into bytecodes, and a bind can be run to create a package for the SQL.

Should you use JDBC or SQLJ? It depends. SQLJ potentially can enhance performance using static SQL. This can be important for Java because of its reputation for being slow. SQLJ is similar to embedded SQL programs; JDBC is similar to call-level interface programs. The familiarity of developers with either approach could make one method easier to use than the other. So, before moving forward with Java development, be sure that the DBAs have been trained in Java and understand the differences between JDBC and SQLJ.

Overall, though, the biggest challenge for the e-DBA is data availability. If data is not available, the e-business is not functioning. This will impact sales, profitability and ultimately stock price and valuation. Being prepared to eliminate and reduce planned and unplanned outages is the biggest job of the e-DBA.

Database management is becoming more complex which, in turn, is driving up the level of skills required to successfully implement database administration. This article covered the changes facing DBAs as they implement and manage databases on the Internet. However, there are many more challenges faced by modern DBAs. The next two installments of this series will expore additional challenges and describe how to deploy intelligent automation to overcome these challenges.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access