Oracle9i Enterprise Edition User's Guide
Release 2 (184.108.40.206.0) for OS/390
Part No. A97312-01
Oracle9i Enterprise Edition is a high-performance, secure, high-availability database management system that can be deployed across multiple operating systems and hardware platforms, including OS/390. This architecture, unique to Oracle, offers customers a database server with a consistent code base on a variety of platforms, from desktop to UNIX to mainframe. In essence, this frees customers from viewing the hardware and operating system as the platform and in turn view Oracle® Enterprise Edition as the platform. Oracle software on UNIX is the same product as Oracle software on OS/390.
In addition to integrating Oracle9i into a distributed environment, Oracle for OS/390 supports traditional transaction processing such as CICS and IMS_TM workloads, as well as Internet computing, where client applications can access Oracle data on OS/390 from any number of distributed sources. This allows customers to exploit the security, reliability, and availability of the mainframe where it counts most, for the data, and use a less premium, commodity environment for Web server applications. Oracle9i Enterprise Edition for OS/390 gives customers that flexibility.
The complete name for this product is Oracle9i Enterprise Edition for OS/390. This product runs on IBM's z/OS as well as on OS/390. This document uses the term OS/390 to indicate both operating systems. To maintain readability and conciseness in this document, the Oracle9i Enterprise Edition is referred to as Oracle9i for OS/390. In all cases, the referenced product remains Oracle9i Enterprise Edition for OS/390.
The following sections are discussed in this chapter:
This release of Oracle9i for OS/390 is based on the execution environment known as OSDI which has replaced MPM. OSDI architecture is the S/390-specific execution environment for all Oracle9i for OS/390 products. The same Oracle products are supported, and their generic behavior with respect to applications is unchanged. Some of the benefits of using OSDI are: virtual storage constraint relief, enhanced client workload management, improved performance and throughput, and improved reliability, availability, and serviceability. The supported products include the Oracle9i Enterprise Edition RDBMS and Oracle Net.
Existing Oracle for OS/390 users should refer to Chapter 11, "Migration Considerations", for further details on migration issues and downward compatibility considerations.
Oracle9i Real Application Clusters is a revolutionary leap beyond Oracle Parallel Server. A separately licensed server option, Real Application Clusters provides your applications with out-of-the-box, near-linear scaling without the need for parallel-aware design changes. Configuring an Oracle instance to use Oracle9i Real Application Clusters is simple, and you can easily and quickly add nodes to your cluster as your needs change. Every node can always access all of your data, so if one node needs to be taken off-line, or happens to fail, the remaining nodes will seamlessly pick up and back out any transactions that may need recovery. This kind of continuous availability is required for today's demanding, 24x7 business applications.
On OS/390, this scalability and availability is accomplished without the need for specialized coupling hardware requiring difficult sizing exercises or complex management tasks. Oracle9i uses the native, high-speed interconnect available in all supported S/390 and zSeries environments. If coupling hardware is present Oracle9i Real Application Clusters will transparently exploit it, but Real Application Clusters does not require coupling hardwareor even a Parallel Sysplexto bring you the benefits of greater availability and scalability. When combined with the multi-address space capabilities of Oracle9i, Real Application Clusters will let you scale your user populations up to the levels required for Internet applications.
Oracle Label Security is but one of many layers of security protection that Oracle9i provides to its OS/390 customers. Oracle Label Security builds on the Oracle Virtual Private Database facilities available in Oracle9i by attaching access control information directly to your data. With this sophisticated, flexible security option in place in place each row of your data is separately secured. These techniques, used by government and defense organizations to protect sensitive information and provide data separation, can now be applied to enterprises as diverse as application hosting and health care to help meet security requirements in the Internet Age. For example, in application hosting, a subscriber label can be used to separate data among subscribers in the same application. More importantly, your policies are enforced within the database, providing security even if the application is bypassed. Oracle Label Security also includes a sophisticated policy management tool to manage policies, labels, and user label authorizations. Oracle Label Security is a separately licensed option for the Oracle9i server.
Note:Oracle Label Security is not supported with this release of Oracle9i for OS/390.
If you decide to license the Oracle Spatial server option, it will provide you with an integrated set of functions and procedures that facilitate analysis based on the spatial relationships of associated data. For example, in an Internet-based customer service application Oracle Spatial could help you decide the most cost-effective way to route deliveries and pickups to your customers. Because it's fully integrated with the Oracle9i database, Oracle Spatial allows you to seamlessly integrate this spatial data with the rest of your enterprise applications.
Note:Oracle Spatial is not supported with this release of Oracle9i for OS/390.
Oracle Partitioning, a separately licensed option of the Oracle9i server, allows you to divide large tables and indexes into pieces that can be separately managed, instead of managing each table as one large object. Partitioning is a technique that provides scalable performance with a large amount of data. Partitioning decreases the time required to perform administrative operations by applying the operations to smaller units of storage, improves performance through increased parallelism, and improves availability by containing the impact of failures.
Database administrators (DBAs) can specify storage attributes for each partition and the placement of the partition within the host file system, increasing the granularity of control for very large databases. Partitions can be individually taken off-line or brought online, backed up, recovered, exported and imported, and loaded, thereby eliminating the time required for management operations. An individual partition can be built for one table partition, bounding the time required for index maintenance operations. Various local and global index strategies are provided. Partition operations can be performed in parallel. Partitions increase availability by containing media and application failures, that is, applications not requiring data from a down or off-line partition continue to run without any impact.
Partitioning is transparent to applications and standard data manipulation language (DML) statements that run against partitioned tables. The Oracle9i optimizer is aware of the partitions; and partitions that do not contain any data for query are eliminated from the search.
The separately licensed Oracle Advanced Security server option provides a comprehensive suite of security features to protect an enterprise's networks and to securely extend corporate networks to the Internet. The Oracle Advanced Security option (formerly known as Advanced Networking Option) provides a single source of integration with network encryption and authentication solutions, single sign-on services, and security protocols.
With other database systems, applications can have contention problems with performance limited by transactions that lock data at the page level or escalate lower-level locks, regardless of available CPU power or I/O bandwidth. Many traditional mainframe shops must have separate windows of time for online workloads and batch workloads to handle this contention. The Oracle9i database server uses full, unrestricted row-level locking for data and indexes, and never escalates locks. In addition, Oracle read operations do not prevent concurrent write operations, ensuring maximum concurrent access to data. The Oracle9i database server's high-performance, scalable sequence number generator eliminates application contention to obtain unique numeric key values, a common requirement in transaction processing applications. Reverse-key indexes reverse the bytes in index entries, spreading the inserts of consecutive keys among different blocks to eliminate insertion hot spots.
The Oracle9i database server can be accessed by the Oracle9i Application Server, which can fully integrate your existing Oracle9i business applications with Web technology, and safely deploy them inside or outside of your corporate firewall.
The Oracle9i Internet Application Server (iAS) enables stored procedures to be invoked by clients using Web browsers to generate dynamic Web documents. This means that Web pages are no longer limited to displaying information from static files.
The Oracle9i database server is 100 percent American National Standards Institute and International Standardization Organization (ANSI/ISO) SQL92 entry-level compliant SQL. This implementation ensures a fully-open application development environment. In addition, the Oracle9i database server offers a number of robust SQL extensions that allow complex operations to be expressed in SQL, improving developer productivity by reducing the need for procedural code. Application performance and scalability are enhanced by performing complex data manipulation operations within the Oracle9i SQL engine.
PL/SQL, the Oracle procedural extension to SQL, is an advanced fourth-generation programming language (4GL). It offers modern features such as data encapsulation, overloading, collection types, exception handling, and information hiding. PL/SQL also offers seamless SQL access, tight integration with the Oracle database server and tools, portability, and security.
Oracle9i stored procedures and triggers improve application development scalability and productivity by allowing common procedures to be developed once and maintained in a central location, instead of in every application. Stored procedures and triggers improve application performance and scalability by allowing application logic to be invoked with a single call. This minimizes network traffic and isolates application processing on the server.
Oracle9i stored procedure implementation also supports automatic dependency tracking for scalable application development. Oracle9i stored procedures can dynamically define and run SQL statements, permitting powerful and flexible procedures. User-defined functions referenced from SQL statements provide productivity by enhancing the power of SQL, and by enhancing performance by running application-specific logic within the server. PL/SQL cursor variables provide a link to a SQL query, allowing stored procedures to encapsulate statically-defined or dynamically-defined queries, and return one or more multirow result sets to the calling application.
The Oracle9i database server includes application development features necessary to construct a new generation of sophisticated applications at low cost. Declarative facilities ensure scalable, reliable enforcement of data integrity while minimizing development, maintenance, and administration costs. PL/SQL, an advanced procedural 4GL that is tightly integrated with the Oracle9i database server, provides the power to easily express complex business rules as stored procedural code. The Oracle9i programmatic interfaces enable third-generation language (3GL) programs to access and manipulate Oracle9i data and schema. The productive development features of the Oracle9i database server improve application performance, scalability, and security to support hundreds of applications and thousands of users.
The Oracle9i database server enforces constraints, either for database integrity purposes or business rules, at the database level, allowing the greatest amount of security and business-rule enforcement. Oracle9i achieves this through the use of declarative integrity constraints, database procedures, and database triggers. Deferred constraint checking optionally shifts integrity constraints checking from the end-of-statement operation to the end-of-transaction operation. This simplifies the coding of certain operations involving integrity constraints. Also, many kinds of constraints can be enabled without stopping update activity on the table.
With PL/SQL stored procedures, functions, packages, and database triggers, you can enforce complex business rules at the server level. This improves application performance, scalability, security, and reduces development costs. Procedures and functions can accept arguments from calling client applications, and return one or more result values. Packages group together definitions of related procedures, functions, variables, cursors, and other database objects to improve development productivity.
When rows are inserted, updated, or deleted from the Oracle9i tables, database triggers are run automatically, either once per row or once per statement. Database triggers can be used to enforce complex integrity rules within the server. The Oracle9i database server trigger implementation is modeled on the draft ANSI and SQL3 specification.
The Oracle9i database server supports deployment of heterogeneous Internet and distributed database configurations by automatically and transparently performing any necessary character set conversions. Oracle9i National Language Support (NLS) ensures error messages, sort order, date format, and other conventions automatically adapt to the native language. Parameter settings at the Oracle9i database server and system levels determine the behavior of individual conventions.
Separate national calendars, including Japanese Imperial, ROC Official, Thai Buddha, Persian, and Arabic Hijrah, are supported. Arabic and Hebrew display character set support is also available. NLS also supports user-defined characters and calendar years.
The benefits of relational technology cannot be achieved without transparent integration of new and existing systems. The Oracle9i database server provides distributed database facilities that make the integration of enterprise data practical. Data can be replicated for direct and highly available local access. Data can be accessed remotely using both SQL and PL/SQL procedure calls in a transparent manner, as if the data were local. Also, data can be stored in both Oracle9i database servers and non-Oracle servers. No external servers are required, which can complicate management procedures such as recovery.
The Oracle9i database offers basic and advanced replication facilities. For information dissemination throughout an enterprise, Oracle9i basic replication supports a simple primary site model of replication where one replica is updatable and all others are read-only. For more sophisticated distributed operation systems, including rollover configurations and mass deployment applications such as sales force automation, Oracle9i advanced replication supports bidirectional replication and sophisticated conflict detection, resolution, and management tool capabilities that you require.
Both basic and advanced replication are fully integrated into the Oracle9i database server. Updates to replicas are captured using internal triggers that run within the server operation for maximum performance.
Oracle9i advanced queuing provides direct support in the database for high-performance queuing operations. This capability provides asynchronous operations and eliminates the dependency on external systems for applications requiring high scalability. The enqueue and dequeue operations can shift processing from within a transaction to a background process, improving the transaction response time.
Oracle Objects, the Oracle9i object-relational paradigm, allows developers to directly define their business objects, such as purchase orders, inventory items, and data warehouse information within the Oracle9i database server. This allows developers of mainstream commercial applications to better manage their business objects.
The Oracle9i database server allows you to define custom object types. An object type is typically defined to correspond to some business object, such as a purchase order. The object type might contain multiple fields or attributes, and it might be nested within other object types. More complex objects that include a variable number of items are supported through variable length arrays and nested tables. This allows, for example, a purchase order object type, which can accommodate a variable number of line items.
Oracle9i database server development products, including Oracle Call Interface (OCI), Pro*COBOL, and Pro*C, support objects. Client-side caching, complex object retrieval, and navigational access provide client-side processing and minimize the number of network roundtrips between the client and the server. The object type translator utility generates C programming language header files for use in Pro*C and OCI applications.
The Oracle9i database server eases the evolution from relational to object-oriented functionality. The Oracle9i object-relational capabilities are built on the same solid foundation as the relational functionality.
Objects are completely integrated throughout the Oracle9i database server in all levels of the server, and are supported in both SQL and PL/SQL. The strengths of the Oracle9i database server are provided with objects, including the sophisticated Oracle9i database server concurrency model, industry leading performance, scalability, reliability, manageability, and availability.
OCI is an application programming interface (API) that allows you to create applications that use the native function calls of the C programming language to access an Oracle9i database server and control all phases of SQL statement execution.
Oracle9i for OS/390 provides the following support for Java development under UNIX System Services environment.
Java Database Connectivity (JDBC)
SQL embedded in Java (SQLJ)
Oracle9i for OS/390 provides a JDBC thin driver. It is a Type IV driver that is targeted for applet developers. The driver is 100 percent pure Java and complies with the JDBC 1.2.2 standard.
For communicating with the database, the driver includes an equivalent implementation of the Oracle TTC presentation protocol and Oracle Net session protocol in Java. Both of these protocols are lightweight implementation versions of their counterparts on the server. To use this driver, it is not necessary to install any Oracle-specific software on the client.
SQLJ allows application programmers to embed static SQL operations in Java code in a way that is compatible with the Java design philosophy. A SQLJ program is a Java program containing embedded static SQL statements that comply with the ANSI-standard SQLJ Language Reference syntax.
The Oracle SQLJ translator is conceptually similar to other Oracle Precompilers and performs the following checks:
Checks the syntax of the embedded SQL.
Checks data types to ensure the data exchanged between Java and SQL has compatible types and proper type conversions.
Checks SQL constructs against the database schema to ensure consistency.
The SQL methodology of embedding SQL operations directly in Java code is more convenient and concise than the JDBC methodology. In this way, SQLJ reduces development and maintenance costs in Java programs that require database connectivity. When dynamic SQL is required, however, SQLJ supports interoperability with JDBC such that you can intermix SQLJ code and JDBC code in the same source file.
Oracle9i for OS/390 provides support for Java (JDK 1.2) from within the Oracle9i database.
Java applications can range from the simple standalone application to large, enterprise solutions. This support allows Java stored procedures running inside the Oracle address space to access Oracle databases using the server-side JDBC driver. The entire JVM operates in the same address space as the database and the SQL engine. This enhances the performance of Java programs and is much faster than executing a remote call to access Oracle.
The server side JDBC driver supports the same features, APIs, and Oracle extensions as the client-side drivers. This makes application partitioning very straightforward. For example, a Java application that is data-intensive, can easily be moved into the Oracle server for better performance, without having to modify the application-specific calls.
Oracle9i for OS/390 provides support for several XML related tools. They include the XML Parser for Java, the XML Class Generator for Java, and the XML Parser for PL/SQL. They are all implemented in Java.
The XML Parser for Java and the XML Parser for PL/SQL are stand-alone XML components that parse an XML document so that it can be processed by an application.
XML Class Generator for Java creates Java source files from an XML DTD.
Oracle9i for OS/390 contains numerous, easy-to-use utilities for DBAs to use, providing a fully-integrated data management system. Refer to the appropriate Oracle documentation for product-specific functions and use of the Oracle utilities.
The following utilities are discussed in this section:
The Oracle9i Recovery Manager utility uses information about the database to automatically locate, backup, restore, and recover datafiles, control files, and archived redo logs.
Recovery Manager allows database and tablespace point-in-time recovery. It also allows you to simplify normal restore and recover operations. Instead of requiring analysis of logs to perform point-in-time recovery, Oracle9i Recovery Manager allows timestamp specification to simplify this procedure.
Export creates an operating system (OS) sequential data set containing data and definitions from the Oracle9i database. The data is created in an internal Oracle format. Export helps migrate data from one system to another in conjunction with the companion utility, Import. Export is also used to provide backups of the Oracle tables and database. Several types of Exports are available to you, depending upon authority level within the Oracle9i database server.
Import reads the OS-sequential data set created by the Export utility and builds tables and related data in the target Oracle9i database.
This release of Oracle9i for OS/390 supports direct upgrade from any Oracle8 release; a migration utility is not required to upgrade from Oracle8 to Oracle9i. A migration utility is provided to upgrade from Oracle V7.3.3 to Oracle9i.
SQL*Loader facilitates the transfer of data from OS/390 data sets into tables in an Oracle9i database. A control file is used to describe the data location, data format, and target tables. You can direct data that does not satisfy certain conditions specified in the control file to special files.
When combined with Oracle Net, SQL*Loader allows you to propagate existing data to remote Oracle9i database servers. SQL*Loader supports a wide variety of input file formats, including virtual storage access method (VSAM) files and partitioned data sets (PDSs), and accepts DB2 Load Utility/DXT control file syntax.
With the server-side Oracle Enterprise Manager Intelligent Agent, DBAs can manage the Oracle for OS/390 database from any OEM console. Starting and stopping the database, as well as managing backups with Recovery Manager, can be controlled through the graphical user interface (GUI) on any OEM console.
Oracle9i includes several tools and utilities designed to run in the OS/390 UNIX System Services (USS) environment. OS/390 UNIX System Services is similar to other UNIX environments, and the Oracle9i tools built for OS/390 USS behave like their UNIX counterparts with every few, if any, differences.
Chapter 4, " Accessing Oracle9i Under USS", describes the Oracle OS/390 UNIX System Services environment. This chapter includes information on the Oracle java utilities available under OS/390 USS. Furthermore, additional information on specific USS compatibility is included in the relevant chapters of this guide.
A number of additional products are distributed on the Oracle9i for OS/390 server distribution tape. They include the following:
Oracle Access Managers enable CICS and IMS_TM applications to directly access local OS/390 data or remote Oracle systems. Usage of one of these is included with the Oracle9i for OS/390 license.
Oracle Access Manager for CICS provides support for CICS transactions to access Oracle data. The Oracle data can be local on the same OS/390 system or remote on any Oracle-supported platform. If the Oracle data is remote, then Oracle Access Manager for CICS routes the transaction's Oracle requests through Oracle Net to the remote system. The distinction between local and remote Oracle9i database server access is transparent to both the transaction program and the user.
CICS transactions written in COBOL or C programming language can be modified to access the Oracle9i database server by embedding industry-standard SQL statements in the source code. This does not affect a program's ability to access non-Oracle data stores on the enterprise server. Both the Oracle9i database server data and non-Oracle data can be accessed and updated in a single transaction. Oracle Access Manager for CICS provides the appropriate interfaces so that CICS fulfills its normal role as commit, rollback, and recovery coordinator.
Data available on an Oracle platform can be accessed simultaneously by both CICS transactions and the Oracle tools or programs. This provides complete, reliable control over data consistency and integrity. You can begin to leverage the full range of Oracle products and facilities while still utilizing your traditional applications.
Data available on an Oracle platform can be accessed simultaneously by both CICS transactions and other Oracle tools or programs. This provides complete and reliable control over data consistency and integrity. You can begin to leverage the full range of Oracle products and facilities.
Oracle Access Manager for IMS_TM provides support for IMS_TM transactions to access Oracle data. The Oracle data can be local on the same OS/390 system, or remote on any Oracle-supported platform. If the Oracle data is remote, then Oracle Access Manager for IMS_TM routes the transaction's Oracle requests through Oracle Net to the remote system. The distinction between local and remote Oracle9i database server access is transparent to both the transaction program and to the user.
IMS transaction programs written in COBOL or C programming language can be modified to access the Oracle9i database server by embedding industry-standard SQL statements in the source code. This does not affect a program's ability to access non-Oracle data stored on the enterprise server. Both Oracle9i database server data and non-Oracle data can be accessed and updated in a single transaction. Oracle Access Manager for IMS_TM provides the appropriate interfaces so that IMS_TM fulfills its role as commit, rollback, and recovery coordinator.
Data moved to another platform can be accessed simultaneously by both IMS_TM transactions and other Oracle tools or programs available in the new environment. This provides complete and reliable control over data consistency and integrity. You can leverage the full range of Oracle products and facilities.
Oracle Precompilers support SQL high-level source programs. The Oracle Precompilers do the following:
Accept the source program as input.
Translate the embedded SQL statements into standard Oracle runtime calls.
Generate a modified source program you can compile, link, and run.
Oracle Precompilers supported on Oracle9i for OS/390 include:
Oracle Net provides network communications between Oracle client applications and the Oracle9i database server or Oracle gateways. It operates across different processors and operating systems. Users of one OS/390 system can access:
An Oracle9i for OS/390 instance on the same processor.
An Oracle9i for OS/390 instance on a different processor.
An Oracle9i instance on a non-OS/390 platform.
Enter, edit, store, retrieve, and run SQL commands
Format, perform calculations on, store, and print query results in report form
List column definitions for any table
Access and copy data between databases
Send messages to and accept responses from a user
Manage Oracle9i database servers
There are two Oracle Transparent Gateways available for Oracle9i for OS/390:
Oracle Transparent Gateways are available on separate distribution media. To obtain them, contact your local Oracle representative.
Oracle Transparent Gateway for DB2 is tightly integrated with Oracle9i for OS/390 and provides Oracle applications with read and write access to DB2 tables. The gateway runs as a started task under OS/390 and uses the DB2 standard Call Attach Facility (CAF) to access tables stored in DB2.
Oracle Net provides network support between the client and the Oracle9i database server for any protocol supported on both platforms, and supports TCP/IP between a remote Oracle9i database server and the gateway on OS/390. The Oracle9i database server can reside on any Oracle-supported platform, including OS/390.
Oracle Transparent Gateway for DB2 is Security Authorization Facility (SAF) compliant and provides a user exit facility to give gateway installations a mechanism for passing control to installation-written code at user logon time. Security environments using the SAF interface include ACF2, RACF, and TOP SECRET.
In addition, Oracle Transparent Gateway for DB2 can be configured to pass DB2 statistics to the Oracle optimizer for improved query performance. Oracle Transparent Gateway for DB2 also provides customers with the ability to take advantage of DB2 parallel query operations.
Oracle Transparent Gateway for EDA/SQL combines technology from Oracle Corporation and Information Builders. Oracle Transparent Gateway for EDA/SQL enables access for Oracle applications to the non-Oracle databases and file systems supported by the EDA/SQL Server. These include the following: