SQL Plus is the most basic Oracle Database utility, with a basic command-line interface , commonly used by users, administrators, and programmers.
40-460: SQL Plus understands five categories of text: Scripts can include all of these components. An Oracle programmer in the appropriately configured software environment can launch SQL Plus, for example, by entering: where the Oracle user scott has the password tiger . SQL Plus then presents a prompt with the default form of: Interactive use can then start by entering a SQL statement (terminated by
80-692: A SQL Plus mode of operation. Relevant products include TOAD from Quest Software . SQL Plus-internal variables, accessible within an SQL Plus session, include: The WHENEVER command specifies an action to perform in the event of the system detecting an SQL error or an operating-system error while running a subsequent SQL Plus command. SQL*Plus needs to be installed and configured on the machine where you intend to use it. This usually involves setting up Oracle client software and configuring network connections to Oracle databases. Oracle Database Oracle Database (commonly referred to as Oracle DBMS , Oracle Autonomous Database , or simply as Oracle )
120-609: A consultancy called Software Development Laboratories (SDL) in 1977. SDL developed the original version of the Oracle software. The name Oracle comes from the code-name of a CIA -funded project Ellison had worked on while formerly employed by Ampex . Oracle products follow a custom release-numbering and -naming convention. The "ai" in the current release, Oracle Database 23ai, stands for "Artificial Intelligence". Previous releases (e.g. Oracle Database 19c, 10g, and Oracle9i Database) have used suffixes of "c", "g", and "i" which stand for "Cloud", "Grid", and "Internet" respectively. Prior to
160-409: A full-function DBMS, was exclusively available on IBM mainframes . Later, IBM brought DB2 to other platforms, including OS/2 , UNIX , and MS Windows servers, and then Linux (including Linux on IBM Z ) and PDAs . This process occurred through the 1990s. An implementation of DB2 is also available for z/VSE and z/VM . An earlier version of the code that would become DB2 LUW (Linux, Unix, Windows)
200-515: A query once and data returns from multiple sources quickly and efficiently. Db2 on Cloud: Formerly named "dashDB for Transactions", Db2 on Cloud is a fully managed, cloud SQL database with a high-availability option featuring a 99.99 percent uptime SLA . Db2 on Cloud offers independent scaling of storage and compute, and rolling security updates. Db2 on Cloud is deployable on both IBM Cloud and Amazon Web Services (AWS). Key features include: Db2 Hosted: Formally named "DB2 on Cloud", Db2 Hosted
240-424: A replacement of SQL*DBA — is obsolete and SQL Plus 8i and later allows the user to issue statements like STARTUP and SHUTDOWN when connected as SYSDBA. Server Manager 7.1 introduced the command CONNECT / AS SYSDBA to replace CONNECT INTERNAL . SQL Plus 8i and later allows the use of CONNECT / AS SYSDBA Other vendors have made their software somewhat compatible with SQL Plus script commands or offer
280-548: A result, they use different drivers . DB2 traces its roots back to the beginning of the 1970s, when Edgar F. Codd , a researcher working for IBM, described the theory of relational databases, and in June 1970, published the model for data manipulation. In 1974, the IBM San Jose Research Center developed a related Database Management System (DBMS) called System R , to implement Codd's concepts. A key development of
320-449: A semicolon), a PL/SQL block, or another command. For example: The first version of SQL Plus was called UFI ("User Friendly Interface"). UFI appeared in Oracle database releases up to Version 4. After Oracle programmers had added new features to UFI, its name became Advanced UFI . The name "Advanced UFI" changed to "SQL Plus" with the release of the version 5 of Oracle. Graphical interfaces from Oracle or third parties have diminished
360-683: Is a proprietary multi-model database management system produced and marketed by Oracle Corporation . It is a database commonly used for running online transaction processing (OLTP), data warehousing (DW) and mixed (OLTP & DW) database workloads. Oracle Database is available by several service providers on-premises , on-cloud , or as a hybrid cloud installation. It may be run on third party servers as well as on Oracle hardware ( Exadata on-premises, on Oracle Cloud or at Cloud at Customer). Oracle Database uses SQL for database updating and retrieval. Larry Ellison and his two friends and former co-workers, Bob Miner and Ed Oates , started
400-508: Is an enterprise-grade, hybrid ANSI-compliant SQL on the Hadoop engine delivering massively parallel processing (MPP) and advanced data query. Additional benefits include low latency, high performance, security, SQL compatibility and federation capabilities. Big SQL offers a single database connection or query for disparate sources such as HDFS, RDMS, NoSQL databases, object stores and WebHDFS. Exploit Hive, Or to exploit Hbase and Spark and whether on
440-459: Is an unmanaged, hosted version of Db2 on Cloud's transactional, SQL cloud database. Key features: Formerly named "dashDB for Analytics", Db2 Warehouse on Cloud is a fully managed, elastic, cloud data warehouse built for high-performance analytics and machine learning workloads. Key features include: In 2018, the IBM SQL product was renamed and is now known as IBM Db2 Big SQL (Big SQL). Big SQL
SECTION 10
#1733085548134480-534: Is designed to deliver high performance, actionable insights, data availability and reliability, and it is supported across Linux, Unix and Windows operating systems. The Db2 database software includes advanced features such as in-memory technology (IBM BLU Acceleration), advanced management and development tools, storage optimization, workload management, actionable compression and continuous data availability (IBM pureScale). In 1995, GA (general availability) of V4. It introduced "data sharing": several DB2 engines access
520-513: The MySQL interface. On IBM i and its predecessor OS/400, DB2 is tightly integrated into the operating system, and comes as part of the operating system. It provides journaling, triggers and other features. In early 2012, IBM announced the next version of DB2, DB2 10.1 (code name Galileo) for Linux, UNIX, and Windows. DB2 10.1 contained a number of new data management capabilities including row and column access control which enables 'fine-grained' control of
560-569: The QMF feature of DB2 produced real SQL, and brought the same "QBE" look and feel to DB2. The inspiration for the mainframe version of DB2's architecture came in part from IBM IMS , a hierarchical database , and its dedicated database-manipulation language, IBM DL/I . The name DB2 (IBM Database 2), was first given to the Database Management System or DBMS in 1983 when IBM released DB2 on its MVS mainframe platform. For some years DB2, as
600-881: The SQL vocabularies between z/OS and distributed platforms. In October 2007, IBM announced "Viper 2", the codename for DB2 9.5 on the distributed platforms . There were three key themes for the release , Simplified Management, Business Critical Reliability, and Agile XML development. In June 2009, IBM announced "Cobra", the codename for DB2 9.7 for LUW. DB2 9.7 added data compression for database indexes, temporary tables, and large objects. DB2 9.7 also supported native XML data in hash partitioning (database partitioning), range partitioning (table partitioning), and multi-dimensional clustering. These native XML features allow users to directly work with XML in data warehouse environments. DB2 9.7 also added several features that make it easier for Oracle Database users to work with DB2. These include support for
640-517: The System R project was the Structured Query Language ( SQL ). To apply the relational model, Codd needed a relational-database language he named DSL/Alpha . At the time, IBM didn't believe in the potential of Codd's ideas, leaving the implementation to a group of programmers not under Codd's supervision. This led to an inexact interpretation of Codd's relational model that matched only part of
680-705: The ability to let utilities run on lists of tablespaces. Furthermore real-time statistics, scrollable cursors, and initial Unicode support. In 2004, GA of V8. It added, e.g., 64-bit support. New index types (notably DPSI), recursive SQL. Internal catalog is converted to Unicode. In 2007, GA of V9. It added, e.g., Trusted Context (a security feature), and "native XML" support. In 2010, GA of V10. It added, e.g., Temporal Tables (e.g., row history), security features like separation of system and security administrators, and RCAC (row column access control). In 2013, GA of V11. It added, e.g., JSON support. In 2016, GA of V12. It added, e.g., RESTful services; and usage of AI to optimize
720-617: The cloud, Oracle Database competes against the database services of AWS, Microsoft Azure, and Google Cloud Platform. Increasingly, the Oracle database products compete against open-source software relational and non-relational database systems such as PostgreSQL , MongoDB , Couchbase , Neo4j , ArangoDB and others. Oracle acquired Innobase , supplier of the InnoDB codebase to MySQL , in part to compete better against open source alternatives, and acquired Sun Microsystems , owner of MySQL, in 2010. Database products licensed as open-source are, by
760-778: The current Oracle Database releases and their patching end dates. Prior to Oracle Database 18c, Oracle Corporation released Critical Patch Updates (CPUs) and Security Patch Updates (SPUs) and Security Alerts to close security vulnerabilities. These releases are issued quarterly; some of these releases have updates issued prior to the next quarterly release. Starting with Oracle Database 18c, Oracle Corporation releases Release Updates (RUs) and Release Update Revisions (RURs). RUs usually contain security, regression (bug), optimizer, and functional fixes which may include feature extensions as well. RURs include all fixes from their corresponding RU but only add new security and regression fixes. However, no new optimizer or functional fixes are included. In
800-457: The database and multi-temperature data management that moves data to cost effective storage based on how "hot" or "cold" (how frequently the data is accessed) the data is. IBM also introduced "adaptive compression" capability in DB2 10.1, a new approach to compressing data tables. In June 2013, IBM released DB2 10.5 (code name "Kepler"). On 12 April 2016, IBM announced DB2 LUW 11.1, and in June 2016, it
840-624: The design of DB2 pureScale on the Parallel Sysplex implementation of DB2 data sharing on the mainframe. DB2 pureScale provides a fault-tolerant architecture and shared-disk storage. A DB2 pureScale system can grow to 128 database servers, and provides continuous availability and automatic load balancing. In 2009, it was announced that DB2 can be an engine in MySQL . This allows users on the IBM i platform and users on other platforms to access these files through
SECTION 20
#1733085548134880-739: The following years incorporated Informix technology into the DB2 product suite. DB2 can technically be considered to be an object–SQL DBMS . In mid-2006, IBM announced "Viper", the codename for DB2 9 on both distributed platforms and z/OS. DB2 9 for z/OS was announced in early 2007. IBM claimed that the new DB2 was the first relational database to store XML "natively". Other enhancements include OLTP -related improvements for distributed platforms, business intelligence / data warehousing -related improvements for z/OS, more self-tuning and self-managing features, additional 64-bit exploitation (especially for virtual storage on z/OS), stored procedure performance enhancements for z/OS, and continued convergence of
920-683: The insight business leaders needed to make data-informed decisions. A new approach was needed to aggregate and analyze data from multiple transactional sources to deliver new insights, uncover patterns, and find hidden relationships among the data. Db2 Warehouse, with its capabilities to normalize data from multiple sources, performs sophisticated analytic and statistical modeling, provides businesses these features at speed and scale. Increases in computational power resulted in an explosion of data inside businesses generally and data warehouses specifically. Warehouses grew from being measured in GBs to TBs and PBs. As both
960-466: The legal terms of the Open Source Definition , free to distribute and free of royalty or other licensing fees. IBM Db2 Db2 is a family of data management products, including database servers , developed by IBM . It initially supported the relational model , but was extended to support object–relational features and non-relational structures like JSON and XML . The brand name
1000-432: The mainframe and the server-based products was named DB2 Universal Database (or DB2 UDB). In the mid-1990s, IBM released a clustered DB2 implementation called DB2 Parallel Edition, which initially ran on AIX. This edition allowed scalability by providing a shared-nothing architecture , in which a single large database is partitioned across multiple DB2 servers that communicate over a high-speed interconnect. This DB2 edition
1040-852: The market for relational databases, Oracle Database competes against commercial products such as IBM Db2 and Microsoft SQL Server . Oracle and IBM tend to battle for the mid-range database market on Unix and Linux platforms, while Microsoft dominates the mid-range database market on Microsoft Windows platforms. However, since they share many of the same customers, Oracle and IBM tend to support each other's products in many middleware and application categories (for example: WebSphere , PeopleSoft , and Siebel Systems CRM ), and IBM's hardware divisions work closely with Oracle on performance-optimizing server-technologies (for example, Linux on IBM Z ). Niche commercial competitors include Teradata (in data warehousing and business intelligence), Software AG's ADABAS , Sybase , and IBM's Informix , among many others. In
1080-554: The mid-80s. In parallel with the development of SQL, IBM also developed Query by Example (QBE), the first graphical query language. IBM's first commercial relational-database product, SQL/DS , was released for the DOS/VSE and VM/CMS operating systems in 1981. In 1976, IBM released Query by Example for the VM platform where the table-oriented front-end produced a linear-syntax language that drove transactions to its relational database. Later,
1120-485: The most commonly used SQL syntax, PL/SQL syntax, scripting syntax, and data types from Oracle Database. DB2 9.7 also enhanced its concurrency model to exhibit behavior that is familiar to users of Oracle Database and Microsoft SQL Server. In October 2009, IBM introduced its second major release of the year when it announced DB2 pureScale . DB2 pureScale is a cluster database for non-mainframe platforms, suitable for online transaction processing (OLTP) workloads. IBM based
1160-651: The prescriptions of the theory; the result was Structured English QUEry Language or SEQUEL . When IBM released its first relational-database product, they wanted to have a commercial-quality sublanguage as well, so they overhauled SEQUEL and renamed the revised language Structured Query Language (SQL) to differentiate it from SEQUEL, and also because the acronym "SEQUEL" was a trademark of the UK-based Hawker Siddeley aircraft company. IBM bought Metaphor Computer Systems to utilize their GUI interface and encapsulating SQL platform that had already been in use since
1200-588: The proportion of Oracle database end-users who depend on the SQL Plus environment. Oracle shops typically continue to use SQL Plus scripts for batch updating or simple reports. Oracle Corporation's wrappers/gui-fications/replacements for SQL Plus include: Starting from Oracle database 11g, iSqlplus (web based) and sqlplus GUI no longer ship with Oracle database software. The command-line SQL Plus interface continues in use, mostly for non-interactive scripting or for administrative purposes. The Server Manager Command Line —
1240-460: The release of Oracle8i Database, no suffixes featured in Oracle Database naming conventions. There was no v1 of Oracle Database, as co-founder Larry Ellison "knew no one would want to buy version 1". For some database releases, Oracle also provides an Express Edition (XE) that is free to use. Oracle Database release numbering has used the following codes: Oracle Database 23c (23.2 and 23.3)
SQL Plus - Misplaced Pages Continue
1280-584: The same data. Advantages: performance and availability (if one DB2 engine fails or is migrated to the next version). In 1997, GA of V5. It added, e.g., online reorganization of tablespaces. In 1999, GA of V6. It added object-relational support. "Objects" here mean data items longer than 32K (up to then the maximal length of a table row, more precisely a table record), such as images, videos, or text. DB2 could now store and handle such objects. Furthermore, it added trigger support. In 2001, GA of V7. It added, e.g., dynamic allocation of data sets (~files on z/OS), and
1320-467: The selection of the access path to the data, thus enhancing performance. On May 31, 2022, IBM released Db2 13 for z/OS. "Data warehousing" was first mentioned in a 1988 IBM Systems Journal article entitled, "An Architecture for Business Information Systems." This article illustrated the first use-case for data warehousing in a business setting as well as the results of its application. Traditional transaction processing databases were not able to provide
1360-603: The software completely. The new version of Database Manager were called DB2/2 and DB2/6000 respectively. Other versions of DB2, with different code bases, followed the same '/' naming convention and became DB2/400 (for the AS/400), DB2/VSE (for the DOS/VSE environment), and DB2/VM (for the VM operating system). IBM lawyers stopped this handy naming convention from being used and decided that all products needed to be called "product FOR platform" (for example, DB2 for OS/390). The next iteration of
1400-708: The volume and variety of data grew, Db2 Warehouse adapted as well. Initially purposed for star and snowflake schemas, Db2 Warehouse now includes support for the following data types and analytical models, among others: Db2 Warehouse uses Docker containers to run in multiple environments: on-premise, private cloud and a variety of public clouds, both managed and unmanaged. Db2 Warehouse can be deployed as software only, as an appliance and in Intel x86, Linux and mainframe platforms. Built upon IBM's Common SQL engine, Db2 Warehouse queries data from multiple sources—Oracle, Microsoft SQL Server, Teradata, open source, Netezza and others. Users write
1440-652: Was eventually ported to all Linux, UNIX, and Windows (LUW) platforms, and was renamed to DB2 Extended Enterprise Edition (EEE). IBM now refers to this product as the Database Partitioning Feature (DPF) and bundles it with their flagship DB2 Enterprise product. When Informix Corporation acquired Illustra and made their database engine an object-SQL DBMS by introducing their Universal Server, both Oracle Corporation and IBM followed suit by changing their database engines to be capable of object–relational extensions. In 2001, IBM bought Informix Software , and in
1480-535: Was originally styled as DB2 until 2017, when it changed to its present form. (In the early days, it was sometimes wrongly styled as DB/2 - in a false derivation from the operating system OS/2 .) Unlike other database vendors, IBM previously produced a platform-specific Db2 product for each of its major operating systems. In the 1990s IBM began producing a more unified Db2 with a mostly common code base for Linux, Unix and Windows. DB2 for System z and DB2 for IBM i , however, remained independent implementations and as
1520-498: Was part of an Extended Edition component of OS/2 called Database Manager. IBM extended the functionality of Database Manager a number of times, including the addition of distributed database functionality by means of Distributed Relational Database Architecture (DRDA) that allowed shared access to a database in a remote location on a LAN . (Note that DRDA is based on objects and protocols defined by Distributed Data Management Architecture (DDM).) Eventually, IBM decided to rewrite
1560-511: Was released in 2023: April 2023 (Linux) Oracle Database Free - Developer Release September 2023 Oracle Database on Base Database Service August 2021 (Linux) April 2019 (Linux) June 2019 (cloud) July 2018 (other) March 2017 (on-premises) The Introduction to Oracle Database includes a brief history on some of the key innovations introduced with each major release of Oracle Database. See My Oracle Support (MOS) note Release Schedule of Current Database Releases (Doc ID 742060.1) for
1600-546: Was released. In mid-2017, IBM re-branded its DB2 and dashDB product offerings and amended their names to "Db2". On June 27, 2019, IBM released Db2 11.5, the AI Database. It added AI functionality to improve query performance as well as capabilities to facilitate AI application development. Db2 (now short for the former "Db2 for LUW") is a relational database that delivers advanced data management and analytics capabilities for transactional workloads. This operational database
#133866