DB2 Java Stored Procedures

Learning by Example

By Maria Sueli Almeida, Kirk Condon, Michael Fischer and Julian Stuhler

Stored procedures can provide major benefits in the areas of application performance, code re-use, security, and integrity. The DB2 Family of products has offered support for stored procedures for some time, with each release offering significant enhancements over the last.

In the meantime, Java has been making steady inroads as the standard application development language for many companies large and small. Its inherent portability and openness, combined with the good availability of skilled programming resource, has made it an increasingly attractive choice, and the central plank in many organization’s e-business strategy.

Until recently, DB2 did not support stored procedures written in Java, so the advantages of the two technologies could not be combined. The latest releases of DB2 have changed all that, opening up new possibilities for efficient, secure, highly portable application development.

This IBM Redbook aims to give the reader an in-depth understanding of the techniques and issues associated with the development of DB2 stored procedures written in Java and using SQLJ and/or JDBC for their SQL operations. The extensive collection of sample code presented in this book and included on the accompanying CD-ROM was designed to run against DB2 UDB Server across the OS/390, Windows, and UNIX platforms.

We have assumed that the reader has at least a basic understanding of Java terminology (such as classes, methods and packages) in addition to exposure to general DB2 application development concepts.

Click to Read More

IBM DB2 Application Development Guide

From IBM

This book discusses how to design and code application programs that access DB2 databases and application servers. It presents detailed information on the use of Structured Query Language (SQL) in supported host language programs. For information unique to your specific operating system, see the Application Building Guide.

You can access data with:

1. SQL statements embedded in a host language, including embedded SQL for Java (SQLJ)
2. dynamic APIs including Java Database Connectivity (JDBC), Perl DBI, and DB2 Call Level Interface (DB2 CLI)

This book discusses all these ways to access data except DB2 CLI, which is discussed in the CLI Guide and Reference. JDBC, SQLJ, and DB2 CLI provide some data access capabilities that are not available through embedded SQL. These capabilities include scrollable cursors and stored procedures that return multiple result sets. See the discussion in Access to Data to help you decide which data access method to use.

To effectively utilize the information in this book to design, write, and test your DB2 application programs, you need to refer to the SQL Reference along with this book. If you are using the DB2 Call Level Interface (CLI) or Open Database Connectivity (ODBC) interface in your applications to access DB2 databases, refer to the CLI Guide and Reference. To perform database manager administration functions using the DB2 adminstration APIs in your application programs, refer to the Administrative API Reference.

You can also develop applications where one part of the application runs on the client and another part runs on the server. Version 6 of DB2 introduces support for stored procedures with enhanced portability and scalability across platforms. Stored procedures are discussed in Stored Procedures...........

Click to Read More

Information Modeling

By David Edmond

This PDF version of book covers Introduction (brief intro to Z and to SQL), Specific facts (relations and functions in Z), Sets (set extension, comprehension, power sets, product sets), Relations (database relations - Z style) , Introducing SQL, SQL retrieval (select from where), SQL modularization (group by, views), Facts and relations (conceptual schema modelling), Uncovering facts (brief methodology), Fact-based analysis (case-study), Entity-relationship modeling, Knowledge (predicate calculus, quantification), The knowledge base (intro to schemas in Z), From specification to implementation (specifying in Z, implementing in SQL), Database definition in SQL (create table, index, view), Database manipulation in SQL (insert, update, delete), Application programming, Case studies, and Refinement.

Click to Read More/Download

Introduction to Databases for web developers

extropia.com

Types of Database

These days, when you talk about databases in the wild, you are primarily talking about two types:

• Analytical databases
• Operational databases.

Let's examine each type

Analytic databases (a.k.a. OLAP- On Line Analytical Processing) are primarily static, read-only databases which store archived, historical data used for analysis. For example, a company might store sales records over the last ten years in an analytic database and use that database to analyze marketing strategies in relationship to demographics.

On the web, you will often see analytic databases in the form of inventory catalogs such as the one shown on the previous page from Amazon.com. An inventory catalog analytical database usually holds descriptive information about all available products in the inventory.

Web pages are generated dynamically by querying the list of available products in the inventory against some search parameters. The dynamically-generated page will display the information about each item (such as title, author, ISBN) which is stored in the database.

Click to Read More

Sybase SQL Server 11 Unleashed

by Jeff Garbus

This Unleashedbook is meant for anyone who is responsible for designing, building, administering and tuning systems that rely on SQL Server. Among other things, this book contains performance information you may not find anywhere else, including tuning methods and advanced SQL techniques that are undocumented or not well described.

• System Administrators will learn how to install and administer SQL Server. You will also learn about important standards and protocols to ensure that SQL Server applications can be maintained and supported for the long haul.
• Database Administrators will learn to make the best use of SQL Server objects and datatypes and will learn to write effective stored procedures and triggers. The “Performance And Tuning” section of the book will help you understand what’s going on under the hood of the server, including a detailed analysis of the query optimizer and the physical storage mechanisms used by SQL Server. You can start to understand what performance you can reasonably expect with SQL Server so that you can focus on problems that you can really fix and on practical solutions.
• Programmers will learn how to write code that runs well on SQL Server and will acquire a complete understanding of how the server interprets SQL statements when running. We also provide a solid foundation for your work in C and Visual Basic with DB-Library, ct-Library, and ODBC in the “Introduction to Open Client Programming” section of this book.

Click to Read More

Database Programming with Perl

By Kirrily Robert

Types of databases
There are many different types of databases, including:

• Flat-file text databases
• Associative flat-file databases such as Berkeley DB
• Relational databases
• Object databases
• Network databases
• Hierarchical databases such as LDAP

Relational databases are by far the most useful type commonly available, and this training module focusses largely on them, after looking briefly at flat file text databases.

Click to Read More

Building a Database-Driven Web Site Using PHP and MySQL

by Kevin Yank

On the Web today, content is king. After you've mastered HTML and learned a few neat tricks in JavaScript and Dynamic HTML, you can probably build a pretty impressive-looking Web site design. But then comes the time to fill that fancy page layout with some real information. Any site that successfully attracts repeat visitors has to have fresh and constantly updated content. In the world of traditional site building, that means HTML files--and lots of 'em.

The problem is that, more often than not, the people providing the content for a site are not the same people handling its design. Oftentimes, the content provider doesn't even know HTML. How, then, is the content to get from the provider onto the Web site? Not every company can afford to staff a full-time Webmaster, and most Webmasters have better things to do than copying Word files into HTML templates anyway........

Click to Read More

Introduction to Structured Query Language

Version 4.11

By James Hoffman

This page is a tutorial of the Structured Query Language (also known as SQL) and is a pioneering effort on the World Wide Web, as this is the first comprehensive SQL tutorial available on the Internet. SQL allows users to access data in relational database management systems, such as Oracle, Sybase, Informix, Microsoft SQL Server, Access, and others, by allowing users to describe the data the user wishes to see. SQL also allows users to define the data in a database, and manipulate that data.

This page will describe how to use SQL, and give examples. The SQL used in this document is "ANSI", or standard SQL, and no SQL features of specific database management systems will be discussed until the "Nonstandard SQL" section. It is recommended that you print this page, so that you can easily refer back to previous examples.

Click to Read More

Structured Query Language(SQL)

A Practical Introduction

By Akeel I Din

Introduction
The Structured Query Language, SQL is a query language which is used with relational databases. This chapter starts by describing some of the terms used in data processing and how they relate to SQL. The later part of this chapter describes relational databases and how SQL is used to query them.

"A Collection of Related Data": Databases and Database Management Systems.
Let's start from basics. What is a database? In very general terms, a database is a collection of related data. Notice the word related, this implies that the collection of letters on this page do not by themselves constitute a database. But if we think of them as a collection of letters arranged to form words, then they can be conceptualised as data in a database. Using similar reasoning, we can also say that a tome such as a telephone directory is also a database. It is a database first, because it is a collection of letters that form words and second, because it is an alphabetical listing of people's names, their addresses and their telephone numbers. How we think of a database depends on what use we want to make of the information that it contains.

Click to Read More/Download

Data Transfer Strategies

Transferring data between XML documents and relational databases

by Ronald Bourret

In this paper we will discuss strategies for transferring data between XML documents and relational databases according to two mappings (a table-based mapping and an object-based mapping) commonly used to map DTDs to relational databases. Although the discussion largely focuses on the difference between using SAX- and DOM-based tools to transfer data, it also discusses a number of strategies for traversing both the XML and database hierarchies and the tradeoffs among them.

Click to Read More

Oracle™ Unleashed

First Edition

Sams Publishing

Introduction

When I first started using Oracle many years ago, it was possible to know the database and the tools available. With the rash of recent releases of different options for the database and the spate of new tools, only people who wear their underpants over their trousers will be able to know everything there is to know about the Oracle products. If you were an experienced Oracle developer three years ago and since then have been locked away—not able to see the new Oracle versions of the database and tools—there will be very little that you will recognize today. Along with changes in other computing technologies, the rate of change with Oracle is constantly increasing, which is all the more interesting for us who get turned on by the software. You'll get a running start with the pages that follow.

Oracle Unleashed is one of the most comprehensive books on Oracle and its tools available today. It's written by authors who have real-life experience using the Oracle tools (most of whom are members of the Oracle Business Alliance Program). We will show you what's important, the pitfalls, tips from real-life experiences, and code examples—The kind of information that can give you the cumulative experience of many years of expertise.

Click to Read More

Concurrency Control and Recovery in Database Systems

By Philip A. Bernstein, Vassos Hadzilacos and Nathan Goodman

For over 20 years, businesses have been moving their data processing activities on-line. Many businesses, such as airlines and banks, are no longer able to function when their on-line computer systems are down. Their on-line databases must be up-to-date and correct at all times.

In part, the requirement for correctness and reliability is the burden of the application programming staff. They write the application programs that perform the business’s basic functions: make a deposit or withdrawal, reserve a seat or purchase a ticket, buy or sell a security, etc. Each of these programs is designed and tested to perform its function correctly. However, even the most carefully implemented application program is vulnerable to certain errors that are beyond its control. These potential errors arise from two sources: concurrency and failures.

Multiprogramming is essential for attaining high performance. Its effect is to allow many programs to interleave their executions. That is, they execute concwrently. When such programs interleave their accesses to the database, they can interfere. Avoiding this interference is called the concurrency control problem.

Click to Read More/Download

Concurrency Control and Recovery in Database Systems

SQL Server 2000 Administrator's Pocket Consultant

By William R. Stanek

As you get started with Microsoft SQL Server 2000, you should concentrate on these areas:

• How SQL Server 2000 works with your hardware
• What versions and editions of SQL Server 2000 are available
• How SQL Server 2000 works with Microsoft Windows-based operating systems
• What administration tools are available

SQL Server 2000 and Your Hardware
Successful database server administration depends on three things:

• Good database administrators
• Strong database architecture
• Appropriate hardware

The first two ingredients are covered: you're the administrator, you're smart enough to buy this book to help you through the rough spots, and you've enlisted SQL Server 2000 to provide your high-performance relational database management system (RDBMS) needs. This brings us to the issue of hardware. SQL Server 2000 should run on a system with adequate memory, processing speed, and disk space. You also need an appropriate data and system protection plan at the hardware level.

Click to Read More

Microsoft SQL Server(TM) 2000 Administrator's Pocket Consultant (It-Administrator's Pocket Consultant)

Practical PostgreSQL

by John C. Worsley and Joshua D. Drake

Command Prompt Inc

PostgreSQL is one of the most successful open source databases available. It is arguably also the most advanced, with a wide range of features that challenge even many closed-source databases.

This book is intended to be a practical guide to PostgreSQL v7.1.x, though most of the book should also apply to earlier and future releases of PostgreSQL. The content is focused on getting you comfortable with PostgreSQL in the most expedient fashion possible. Although we will touch on some academic database subjects, such discussion will be kept brief. Our core focus is to provide the reader with enough of an understanding of PostgreSQL to manage a fully operational PostgreSQL database. Our hope is that by introducing this book to the community we will provide a better understanding of PostgreSQL and its functionality.

This book is for anyone interested in utilizing the PostgreSQL object-relational database-management system (ORDBMS). The reader should be familiar with Linux- and UNIX-based systems, but is not expected to be a database guru. Although the test operating system for this book is Red Hat Linux, the tasks in this book that apply to Linux should apply to most UNIX variants without much modification.

Click to Read More

Data Mining Desktop Survival Guide

by Graham Williams

Knowledge leads to wisdom and better understanding. Data mining builds knowledge from information, adding value to the tremendous stores of data that abound today--stores that are ever increasing in size and availability. Emerging from the database community in the late 1980's the discipline of data mining grew quickly to encompass researchers from Machine Learning, High Performance Computing, Visualisation, and Statistics, recognising the growing opportunity to add value to data. Today, this multi-disciplinary effort continues to deliver new techniques and tools for the analysis of very large collections of data. Searching through databases measuring in gigabytes and terabytes data mining delivers discoveries that can change the way an organisation does business. It can enable companies to remain competitive in this modern data rich, knowledge hungry, wisdom scarce world. Data mining delivers knowledge to drive wisdom.

For a long time, Statisticians and more recently Machine Learning researchers, have sought to add value to data by building models from data samples. From a statistics point of view, the aim is generally to build accurate models. From a machine learning point of view, the aim is generally to gain understanding that can be turned into actionable knowledge. Irrespective the models can help better understand the general behaviour of systems and even predict outcomes for new cases.

Statistical and symbolic techniques have often been hamstrung by their computational and memory requirements, leading to long waits for models to be built over very large datasets. Alternatively sampling of the data is required in order to generate models in a reasonable time. Traditionally we might also characterise the statistical approach as apriori hypothesis testing rather than data exploration.

Data mining strives to discover new knowledge (new hypotheses) from data, effectively letting the data speak for itself. Previously unknown patterns in very large databases are searched for, presenting discoveries in a human accessible form.

Click to Read More

SQL Fundamentals

By Mike Chapple

Introduction
The Structured Query Language (SQL) comprises one of the fundamental building blocks of modern database architecture. SQL defines the methods used to create and manipulate relational databases on all major platforms. At first glance, the language may seem intimidating and complex but it's really not all that bad. In a series of articles over the next few weeks we'll explore the inner workings of SQL together. By the time we're through, you'll have the fundamental knowledge you need to go out there and start working with databases!

This week, our first article in the SQL series provides an introduction to the basic concepts behind SQL and we'll take a brief look at some of the main commands used to create and modify databases. Throughout this article, please keep our goal in mind: we're trying to get the "big picture" of SQL -- not a mastery of the individual commands.......

Click to Read More

SQL for Web Nerds

by Philip Greenspun

After writing a preface lampooning academic eggheads who waste a lot of ink placing the relational database management system (RDBMS) in the context of 50 years of database management software, how does this book start? With a chapter placing the RDBMS in the context of other database management software.

Why? You ought to know why you're paying the huge performance, financial, and administration cost of an RDBMS. This chapter doesn't dwell on mainframe systems that people stopped using in the 1970s, but it does cover the alternative approaches to data management taken by Web sites that you've certainly visited and perhaps built.

The architect of any new information system must decide how much responsibility for data management the new custom software should take and how much should be left to packaged software and the operating system. This chapter explains what kind of packaged data management software is available, covering files, flat file database management systems, the RDBMS, object-relational database management systems, and object databases. This chapter also introduces the SQL language.

Click to Read More

SQL - Structured Query Language

By Welland Barn

The objective of this chapter is to introduce the main concepts of data storage and retrieval in the context of database information systems.

In view of their prominence this booklet concentrates on the general characteristics of Relational Database Management Systems (RDBMS) and the Structured Query Language SQL and does not consider any of the numerous other types of databases. No prior knowledge of SQL is assumed.

It is intended that the SQL presented in this booklet be followed interactively and that the you should try all the given examples in the order in which they are presented. At the end of the booklet you should have attained a thorough knowledge of SQL and its capabilities as an interactive statement language.

In the main the SQL covered complies with the standard definition for SQL were proprietary SQL features are referred to this will be made clear. On this basis the skills obtained from this unit should be transferable across a wide range of RDBMS's which support SQL.

To enable you to make effective use of your local facilities a number of RDBMS specific appendices containing access instructions and other supplementary information specific to your environment have been included.

Find it from here

JCC's SQL Standards Page

This page is designed to be a central source of information about the SQL standards process and its current state. It also contains pointers to other sources of information about the SQL standard. The information available here is:

• Current Status
• Parts of the SQL Standard and Timeline
• Definition of the Parts
• Timeline
• Number of Pages Devoted to Each Part
• How to Get Copies of the Standards
• The SQL Standards Process
• SQL/MM

Click to Read More

XML Query Language (XQL)

Authors: Jonathan Robie, Joe Lapp and David Schach

The XML Query Language (XQL) is a notation for addressing and filtering the elements and text of XML documents. XQL is a natural extension to the XSL pattern syntax. It provides a concise, understandable notation for pointing to specific elements and for searching for nodes with particular characteristics. This proposal was provided in September 1998 to the XSL Working Group (http://www.w3.org/Style/XSL/Group/1998/09/XQL-proposal.html) as input when considering extensions to the XSL pattern syntax.

The companion document Querying and Transforming XML describes the benefits of basing query and transformation languages for XML on the XSL transformation language and the extensions to the pattern language proposed here.

Introduction
The XSL pattern language ( http://www.w3.org/TR/WD-xsl, section 2.6) provides an extremely understandable way to describe a class of nodes to process. It is declarative rather than procedural. One simply describes the types of nodes to look for using a simple pattern modeled after directory notation. For example, book/author means find author elements contained in book elements.

XQL (XML Query Language) provides a natural extension to the XSL pattern language. It builds upon the capabilities XSL provides for identifying classes of nodes, by adding Boolean logic, filters, indexing into collections of nodes, and more.

XQL is designed specifically for XML documents. It is a general purpose query language, providing a single syntax that can be used for queries, addressing, and patterns. XQL is concise, simple, and powerful.......

Click to Read More

SQL Tutorial By SQLCourse2.com

Welcome to SQLCourse2.com! This unique SQL Tutorial is the "sequel" to the highly successful SQLCourse.com site and will provide you with more advanced easy-to-follow SQL Instruction and the ability to practice what you learn on-line with immediate feedback! You will receive immediate results on a web page after submitting your SQL Commands.

This continuation course will provide you with critical need-to-know advanced features and clauses of the SELECT statement that weren't supported in the previous SQLCourse.com site. Everything you learn here will be ANSI SQL compliant and should work with most SQL databases such as Oracle, SQL Server, mySQL, MS Access, Informix, Sybase, or any other ANSI SQL compliant database.

If you're already familar with the basics of SQL, you can still use this as a refresher, and practice some SQL statements.

Overview

This Intermediate/Advanced SQL Tutorial will cover the SELECT statement in great detail. The SELECT statement is the core of SQL, and it is likely that the vast majority of your SQL commands will be SELECT statements. Due to the enormous amount of options available for the SELECT statement, this entire tutorial has been dedicated to it.

When constructing SQL Queries (with the SELECT statement), it is very useful to know all of the possible options and the best or more efficient way to do things. This Tutorial will help provide you with those skills.

Although it is recommended that you go through SQLCourse.com (the original site) or at least the SELECT statement on SQLCourse.com, it isn't required. YOu can start with this site, and then proceed to SQLCourse.com when you are finished. SQLCourse.com covers four other SQL Commands and allows you to practice those as well.

Click to Read More

Gradiance SQL Tutorial

Gradiance.com

Our approach to teaching SQL is to use a combination of coding exercises (writing queries) and quizzes, to emphasize the "learning by programming" paradigm, along with providing a set of slide sets covering the relevant material. The programming exercises are on-line and feature immediate constructive feedback about the correctness of the submitted queries. Test cases, comments and hints are provided to illustrate the errors and help you debug the queries on-line.

The Gradiance SQL tutorial covers the following core topics:

• Basics - the SELECT-FROM-WHERE framework for SQL queries
• Null Values and Aggregation - dealing with NULL values in SQL tables; aggregating column values
• Grouping and Ordering - GROUP BY and ORDER BY clauses
• Join queries - multi-table queries involving join operations
• Aggregation and NULL values in join queries - grouping and aggregation across multiple tables; NULL values and outer-join operations
• Subqueries - notion of queries embedded in other queries; IN, NOT IN, ANY, ALL and EXISTS operations

The estimated duration for the tutorial is from 30 to 60 hours, spread over a total of 150 slides, 30 problems to solve (in the quizzes) and 50 programming exercises to work on (in the lab projects). The material is organized into six lesson packages, with associated quizzes and lab projects, so that each item can be handled in a short session...........

Click to Read More

Using Oracle 8

Macmillan Computer Publishing

Introduction
Welcome to Using Oracle8! This book identifies the many functions an Oracle DBA needs to perform on an Oracle8 database and explains how to do them as efficiently and effectively as possible. You learn about the key functions of database administration, including installing the product, designing and creating a database and its tablespaces, designing and creating the tables and other objects that make up an Oracle database, designing and executing a good backup strategy with a recovery methodology, and monitoring and tuning performance. You also learn about creating and maintaining users and performing an upgrade to Oracle8, as well as other tasks that you may need in your position as DBA. You also learn when and how to use the various tools Oracle8 provides to assist you in database management, performance monitoring and tuning, data loading, backup and recovery, and data export and import.

The book is designed to let you read about a topic at length when you have the time and the inclination, or to use as a quick reference guide when you need an answer to a pressing technical question or an example to follow when performing a specific task.

Using Oracle8 contains cross-references to related topics so that you can look at all aspects of a topic, even if they're covered in different chapters. These cross-references also enable you to read the book in any order you choose. If you run across a subject you don't fully understand, you can easily switch your attention to the area(s) identified and carry on your reading there. Where applicable, the book also references the Oracle documentation materials, so you can find even more detail if you need it.

Don't forget to keep this book handy at work, just in case you need to check something in a hurry that you haven't read about yet or is a new topic to you. Be sure also to use the tear-out card inside the book's cover. It contains some of the most common, but difficult to remember, information you'll need.

Click to Read More

Developing Client/Server Applications with Oracle

by Paul Hipsley

Sams, Macmillan Computer Publishing

Oracle Developer/2000 is the next generation of development tools for Microsoft Windows applications development from Oracle Corporation. Oracle Developer/2000 leverages the power of Oracle7 and Microsoft Windows. Oracle7 is the most popular Relational Database Management System (RDBMS) in the world and Microsoft Windows is the most popular Graphical User Interface (GUI) in the world. The previous release of Oracle Developer/2000 was know as the Oracle Cooperative Development Environment (CDE). At times you may see or hear references to CDE2, which is now known as Oracle Developer/2000.

This book is intended to be of value to many different types of readers, including managers of Oracle client/server projects, individuals evaluating development tools, programmers who are new to the Oracle environment, experienced Oracle programmers who are new to the Windows development environment, and students interested in investing in their future careers in the Oracle application development job market........

Click to Read More

Sams Oracle Unleashed

SAMS Publishing

Introduction
When I first started using Oracle many years ago, it was possible to know the database and the tools available. With the rash of recent releases of different options for the database and the spate of new tools, only people who wear their underpants over their trousers will be able to know everything there is to know about the Oracle products. If you were an experienced Oracle developer three years ago and since then have been locked away—not able to see the new Oracle versions of the database and tools—there will be very little that you will recognize today. Along with changes in other computing technologies, the rate of change with Oracle is constantly increasing, which is all the more interesting for us who get turned on by the software. You'll get a running start with the pages that follow.

Oracle Unleashed is one of the most comprehensive books on Oracle and its tools available today. It's written by authors who have real-life experience using the Oracle tools (most of whom are members of the Oracle Business Alliance Program). We will show you what's important, the pitfalls, tips from real-life experiences, and code examples—The kind of information that can give you the cumulative experience of many years of expertise.

Click to Read More

Oracler Programming with Visual Basicr

Publisher: Sybex, Inc.

Author(s): Nick Snowdon

Why a Book on Oracle and Visual Basic?
This is an unusual book as far as computer literature goes in that it covers two quite separate areas of information technology. Many good books are available on Oracle, although nearly every one is designed as a source of information for database administrators. There are probably more books on Visual Basic than any other topic except perhaps the Web, but you will rarely see the word “Oracle” in the indexes. The lack of information is what I found myself dealing with a few years ago. Even with my strong background in Visual Basic (version 3 at the time), I found getting myself connected to an Oracle database and making it perform efficiently and effectively posed a problem that needed an answer.

This book is a personal one because it provides the resource I would have liked to have had a few years ago. It combines an understanding of the basic concepts about Oracle without which you will have a hard time communicating with database administrators or carrying out the administration role yourself if you are caught in that situation. (Oracle is not the simplest of databases to administer, which is something that is not always appreciated by project managers.)

Most Oracle texts are generally too complicated for the novice who has a hard time trying to decide which things are important enough to learn and which can be safely discarded until more time is available for training. Visual Basic books, on the other hand, are either introductory or deal exclusively with Microsoft SQL Server. So many questions are left unanswered for Oracle developers, even if it is just seeing an example of how something works.

The paucity of books for Oracle developers and the absence of any coverage of Oracle in the Visual Basic literature has led to the need for such coverage; this book will provide you with the information to proceed with your VB front ends to Oracle.

Click to Read More

Oracle Performance Tuning and Optimization

by Edward Whalen

Sams, Macmillan Computer Publishing

Introduction
Database management software and the manipulation of data has evolved to where it touches every aspect of our lives. A day doesn’t go by in which we don’t access a database. Whether we are withdrawing money from an ATM machine, opening a checking account, or purchasing groceries, every aspect of our lives is affected by databases. Hand in hand with the new power of information comes the frustration of having to wait for data to be retrieved. I’m sure there isn’t a person today who hasn’t had to wait for a credit card to be approved. Although the speed of computers has been increasing every year, so has the amount of data being manipulated. Amounts of data that several years ago were unheard of are now a daily part of many companies. In years past, databases were used strictly in the realm of big business because large mainframes cost millions of dollars; today, gigabytes of data are being manipulated on the same types of computer you may have in your own home.

No matter how fast new generations of computers get, applications will always be written to take advantage of them. As the cost of storage continues to drop, the amount of data stored will continue to increase. A perfect example of this is the CD-ROM. The advent of the CD-ROM allowed large amounts of data to be inexpensively stored; predictably, many new applications have arisen to take advantage of that technology. These applications are now augmenting written text with video and audio clips. The same type of information revolution is also happening in the database industry.

Oracle already has the capability to store video, documents, and large binary objects in the database and allow quick access to this data. Oracle databases can store hundreds of gigabytes of data and can easily retrieve it; Oracle has the potential of storing terabytes of data in a single database in the near future.

Click to Read More

Oracle 8 Black Book

Publisher: The Coriolis Group

Author(s): Michael R. Ault

Chapter 1—Provides an overview of relational and object-oriented terminology and technology. The Practical Guide provides concrete examples of how to apply these to database design. Actual conversion examples are given.

Chapter 2—Provides an overview of database structured design methodology. The Practical Guide demonstrates real-world application of the structured design concepts and provides a template for use in object-oriented diagramming.

Chapter 3—Discusses Oracle8 and how triggers are used to provide encapsulation and data hiding. Example triggers are shown, and the dreaded mutating table is slain.

Chapter 4—Discusses Oracle object/relational structures and insights into the use of the user-defined types. Comparisons to Java are utilized to demonstrate Oracle8’s object-oriented capabilities.

Chapter 5—Demonstrates Oracle8 data storage features. Partitioned tables, nested tables, VARRAYs, index-only tables, and LOB storage examples are given.

Chapter 6—Examines the new LOB features of Oracle8. Techniques for reading, inserting, and using LOB datatypes in the database are demonstrated including the DBMS_LOB package.

Chapter 7—Examines the PL/SQL enhancements specifically dealing with collection data types such as VARRAY and nested tables. The Practical Guide demonstrates the use of collection methods and data types.

Chapter 8—Deals with the new parallel and distributed options of Oracle8. Techniques for use of parallel insert, update, and delete are discussed and demonstrated. Advanced queuing is also discussed.

Chapter 9—Demonstrates how the Oracle8 data dictionary has been altered for the new features of Oracle8. The book’s companion appendix provides complete definitions of the major sets of data dictionary items such as the dollar ($), GV_$, and DBA_ views and tables. The Practical Guide shows how these tables and views are used to find information about Oracle8 objects.

Chapter 10—Provides information on application tuning with practical demonstrations of the use of Oracle provided tuning tools and scripts. All scripts discussed in Chapter 10 are provided on the com-panion CD-ROM, which also includes the Precise/SQL SQL-tuning tool from Precise Software Solutions.

Chapter 11—Provides information on tuning Oracle internals. The Practical Guide provides scripts, techniques, and guidelines for Oracle internal tunings. All scripts shown are provided on the companion CD-ROM along with the Q Diagnostic program from Savant Corporation.

Chapter 12—Covers the Oracle-supplied tuning and utility scripts, and demonstrates their use.

Chapter 13—Covers Oracle and Java topics. The Practical Guide shows examples of the use of Java with Oracle, including queries against the Oracle database using JDBC and JSQL, and the retrieval and display of objects (such as images) from the database. The companion CD-ROM contains the JDK1.1.4, JDBC, and JSQL development tool sets for use with Oracle8.

Appendix—Provides a reference for DBA_ and V$ views and ($) dollar tables.

Click to Read More

Developing Personal Oracle7ª for Windows[rm]95 Applications, Second Edition

By David Lockman

Copyright © 1997 by Sams Publishing

What This Book Is About

This book is about a the version of the Oracle relational database management system (RDBMS) called Personal Oracle7. Personal Oracle7 is a version of the Oracle product that runs on top of Microsoft Windows 95 and Windows 3.11. Even though Personal Oracle7 is a PC-based version of the Oracle RDBMS, Personal Oracle7 provides almost the same features that exist in the versions of the Oracle RDBMS for large computer systems. As a result, Personal Oracle7 is an excellent tool for developers who intend to design applications for the Oracle RDBMS.

Click to Read More

Special Edition Using Microsoft SQL Server 6.5

Second Edition

By Stephen Wynkoop

Introduction
All of data processing is involved with the operations of storing and retrieving data. A database, such as Microsoft SQL Server, is designed as the central repository for all the data of an organization. The crucial nature of data to any organization underlines the importance of the method used to store it and enable its later retrieval.

Microsoft SQL Server uses features similar to those found in other databases and some features that are unique. Most of these additional features are made possible by SQL Server's tight integration with the Windows NT operating system. SQL Server contains the data storage options and the capability to store and process the same volume of data as a mainframe or minicomputer.

Like most mainframe or minicomputer databases, SQL Server is a database that has seen an evolution from its introduction in the mid-1960s until today. Microsoft's SQL Server is founded in the mature and powerful relational model, currently the preferred model for data storage and retrieval.

Unlike mainframe and minicomputer databases, a server database is accessed by users--called clients--from other computer systems rather than from input/output devices, such as terminals. Mechanisms must be in place for SQL Server to solve problems that arise from the access of data from perhaps hundreds of computer systems, each of which can process portions of the database independently from the data on the server. Within the framework of a client/server database, a server database also requires integration with communication components of the server in order to enable connections with client systems. Microsoft SQL Server's client/server connectivity uses the built-in network components of Windows NT.

Unlike a stand-alone PC database or a traditional mainframe or minicomputer database, a server database, such as Microsoft SQL Server, adds service-specific middleware components--such as Open Database Connectivity (ODBC)--on top of the network components. ODBC enables the interconnection of different client applications without requiring changes to the server database or other existing client applications.

SQL Server also contains many of the front-end tools of PC databases that traditionally haven't been available as part of either mainframe or minicomputer databases. In addition to using a dialect of Structured Query Language (SQL), GUI applications can be used for the storage, retrieval, and administration of the database.

Now, with the addition of new database-aware components, you can also use your SQL Server with your Internet-based applications. Tools such as the Internet Database Connector, or IDC, and the Advanced Data Connector, or ADC, are available that will help you integrate SQL Server database information into your Web pages. Depending on the tool or approach you select, you'll have access that ranges from static Web pages to dynamic, Visual Basic-enhanced Web pages. These exciting tools are making Web-based applications a reality..............

Click to Read More

Teach Yourself SQL in 21 Days, Second Edition

Authors: : Ryan K. Stephens, Ronald R. Plew, Bryan Morgan, and Jeff Perkins

© Copyright, Macmillan Computer Publishing.

Overview
The first 14 days of this book show you how to use SQL to incorporate the power of modern relational databases into your code. By the end of Week 1, you will be able to use basic SQL commands to retrieve selected data.

NOTE: If you are familiar with the basics and history of SQL, we suggest you skim the first week's chapters and begin in earnest with Day 8, "Manipulating Data."

At the end of Week 2, you will be able to use the more advanced features of SQL, such as stored procedures and triggers, to make your programs more powerful. Week 3 teaches you how to streamline SQL code; use the data dictionary; use SQL to generate more SQL code; work with PL/SQL, Transact-SQL, and SQL*Plus; and handle common SQL mistakes and errors.

The syntax of SQL is explained and then brought to life in examples using Personal Oracle7, Microsoft Query, and other database tools. You don't need access to any of these products to use this book--it can stand alone as an SQL syntax reference. However, using one of these platforms and walking though the examples will help you understand the nuances.

Click to Read More

Interactive SQL tutorial

sqlzoo.net

What is Sql?

SQL stands for "Structured Query Language". This language allows us to pose complex questions of a database. It also provides a means of creating databases. SQL very widely used. Many database products support SQL, this means that if learn how to use SQL you can apply this knowledge to MS Access or SQL Server or to Oracle or Ingres and countless other databases.

SQL works with relational databases. A relational database stores data in tables (relations). A database is a collection of tables. A table consists a list of records - each record in a table has the same structure, each has a fixed number of "fields" of a given type.......

Click to Read More

The Rise of Relational Databases

Large-scale computer applications require rapid access to large amounts of data. A computerized checkout system in a supermarket must track the entire product line of the market. Airline reservation systems are used at many locations simultaneously to place passengers on numerous flights on different dates. Library computers store millions of entries and access citations from hundreds of publications. Transaction processing systems in banks and brokerage houses keep the accounts that generate international flows of capital. World Wide Web search engines scan thousands of Web pages to produce quantitative responses to queries almost instantly. Thousands of small businesses and organizations use databases to track everything from inventory and personnel to DNA sequences and pottery shards from archaeological digs.

Thus, databases not only represent significant infrastructure for computer applications, but they also process the transactions and exchanges that drive the U.S. economy..........

Click to Read More

Relational Databases

by Anthony and Hala Awtrey

One of the most powerful tools computers give us is the ability to store and search data. Early applications stored data for programs in files and used indexes to search the files for particular bits of data. These programs didn't fare very well on networks because there are problems with more than one computer trying to update data in a file simultaneously. This fact and the lack of standard database structure and command syntax encouraged the creation of the networked relational database.

When you write a program and want to be able to manipulate and search data you often have to construct the data files and the code to perform the manipulation and searches. This is a time consuming task and if you are developing many different applications you have to reinvent the data handling routines for every job. By removing the database functions such as file management, indexing, searches and simultaneous data access, you speed up development of applications and allow for these specialized database programs to become highly optimized and scalable.

What is a database
A database is simply organized data. A database contains tables which are basically descriptions of types of data. Tables in turn contain records which is the actual data......

Click to Read More

RDBMS - Relational Database Management Systems

By Christopher Browne

Database Classifications

There are several ways of classifying the database systems available for Linux:

• Based on "freeness." - There are commercial packages (sold for a price), and there are free software database systems (that do not cost anything to acquire). This is fairly closely correlated to availability of usable source code, which is another legitimate interpretation of "freeness."

• Compatibility/Means of Functioning -

1. xBASE - This is traditionally a "PC" oriented system, of which the first version was known as Vulcan, and ran under CP/M . It was later renamed dBase, and later versions and competitors have followed.
   

   The system model generally involves data structured in a dual fashion similar to ISAM databases with "data" files containing data, and "index" files containing index information. Applications access data directly by reading the files. Newer versions have a network locking system to manage contention for files and/or records if multiple users try to access data simultaneously, but there is still contention inherent in that many programs are accessing the same files simultaneously.
   

   More modern systems use "extent-based" allocation systems to better support the handling of tuples of varying sizes.

2. SQL - Structured Query Language - Ingres was the progenitor of the modern "query language," with its QUEL query language; a similar query language was then designed that we now know as SQL. SQL is arguably inferior to QUEL, as QUEL had a syntax that is simultaneously simpler and more powerful than that of SQL.
   

   The system model typically involves there being a central database manager "engine" or "process;" application programs do not have direct access to the data. This allows data to be relatively protected from corruption/misuse by rogue processes.

Click to Read More

Dilip's Brief Introduction to Relational Databases

cs.unc.edu

Lesson Overview

As part of the spring 1998 Advanced Java Course at UNC, I am giving the first seminar intended to introduce the audience to relational databases and give a tutorial on Microsoft Access. This lecture precedes one by Wen Zhang and Ganesh Srivinas about connecting to a database from java, and a final database lecture by Will Sexton and Rich Thornett about the "JDBC".

We start off quickly looking at the notion of "database" and consider the simple relational database model of tables with tuples (rows) and attributes (columns). A popular way to design a database is through ER diagrams, and we look at a sample diagram. I hope that you won't get bogged down when we discuss the relational algebra, set theoretic notions that the relational model is based upon. Though it is important to have some basic understanding of the relational algebra, in practice most users take advantage of a higher level language for retrieving information from a database, and we look at SQL (Structured Query Language) as a common example. Finally, we motivate the use of Microsoft Access, a popular relational database system, and follow-on with a detailed tutorial on how to use Access.

Click to Read More

Using Oracle PL/SQL

infolab.stanford.edu

This document was written originally by Yu-May Chang and Jeff Ullman for CS145, Autumn 1997; revised by Jun Yang for Prof. Jennifer Widom's CS145 class in Spring, 1998; additional material by Jeff Ullman, Autumn 1998; further revisions by Jun Yang, Spring 1999; minor revisions by Jennifer Widom, Spring 2000.

Basic Structure of PL/SQL

PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in he program. A block has the following structure:

DECLARE
/* Declarative section: variables, types, and local subprograms. */

BEGIN
/* Executable section: procedural and SQL statements go here. */
/* This is the only section of the block that is required. */

EXCEPTION
/* Exception handling section: error handling statements go here. */

END;

Click to Read More

MySQL 5.1 Reference Manual

Copyright 1997-2006 MySQL AB

This is the Reference Manual for the MySQL Database System, version 5.1, through release 5.1.12-beta. It is not intended for use with older versions of the MySQL software due to the many functional and other differences between MySQL 5.1 and previous versions. If you are using an earlier release of the MySQL software, please refer to the MySQL 5.0 Reference Manual, which covers the 5.0 series of MySQL software releases, or to MySQL 3.23, 4.0, 4.1 Reference Manual, which covers the 3.23, 4.0, and 4.1 series of MySQL software releases. Differences between minor versions of MySQL 5.1 are noted in the present text with reference to release numbers (5.1.x).

Because this manual serves as a reference, it does not provide general instruction on SQL or relational database concepts. It also does not teach you how to use your operating system or command-line interpreter.

The MySQL Database Software is under constant development, and the Reference Manual is updated frequently as well. The most recent version of the manual is available online in searchable form at http://dev.mysql.com/doc/. Other formats also are available there, including HTML, PDF, and Windows CHM versions.........

Click to Read More

Getting Started with MySQL

© 1995-2006 MySQL AB

dev.mysql.com

This article is the first in an educational series offered by MySQL AB aimed towards providing the reader with valuable insight into the MySQL database server. Although future articles will delve into some of the more complicated topics surrounding MySQL, including replication, ODBC and optimization, it was thought to be prudent if the first tutorial started, well, at the beginning. Therefore the goal of this article is to thoroughly acquaint the reader with various topics surrounding the basic functioning of MySQL. A synopsis of what is covered is shown in the Table of Contents, listed below. You can go to any topic listed in the Table of Contents simply by clicking on its title.

Click to Read More