SQL concepts
DB2® UDB for iSeries™ SQL consists of several main parts, such as SQL runtime support, precompilers, and interactive SQL.
- SQL runtime support
SQL run time parses SQL statements and runs any SQL statements. This support is part of the i5/OS® licensed program, which allows applications that contain SQL statements to be run on systems where the IBM® DB2 Query Manager and SQL Development Kit for iSeries licensed program is not installed.
- SQL precompilers SQL precompilers support precompiling embedded SQL statements in host languages. The following languages are supported:
The SQL host language precompilers prepare an application program containing SQL statements. The host language compilers then compile the precompiled host source programs. For more information about precompiling, see Preparing and running a program with SQL statements in the Embedded SQL programming information. The precompiler support is part of the DB2 Query Manager and SQL Development Kit for iSeries licensed program. - SQL interactive interface
SQL interactive interface allows you to create and run SQL statements. For more information about interactive SQL, see Using interactive SQL. Interactive SQL is part of the DB2 Query Manager and SQL Development Kit for iSeries licensed program.
- Run SQL Scripts
The Run SQL Scripts window in iSeries Navigator allows you to create, edit, run, and troubleshoot scripts of SQL statements. Run SQL Scripts is a part of iSeries Navigator.
- Run SQL Statements (RUNSQLSTM) CL command
The RUNSQLSTM command allows you to run a series of SQL statements that are stored in a source file. For more information about the RUNSQLSTM command, see Using the SQL statement processor.
- DB2 Query Manager for iSeries
DB2 Query Manager for iSeries provides a prompt-driven interactive interface that allows you to create data, add data, maintain data, and run reports on the databases. Query Manager is part of the DB2 Query Manager and SQL Development Kit for iSeries licensed program. For more information, see Query Manager Use
.
- SQL REXX interface
The SQL REXX interface allows you to run SQL statements in a REXX procedure. For more information about using SQL statements in REXX procedures, see Coding SQL statements in REXX applications in the Embedded SQL Programming information.
- SQL call level interface
DB2 UDB for iSeries supports the SQL call level interface. This allows users of any of the ILE languages to access SQL functions directly through bound calls to a service program that is provided by the system. Using the SQL call level interface, you can perform all the SQL functions without the need to precompile. This is a standard set of procedure calls to prepare SQL statements, run SQL statements, fetch rows of data, and even perform advanced functions, such as accessing the catalogs and binding program variables to output columns.
For a complete description of all the available functions and their syntax, see SQL call level interface in the Database section of the i5/OS Information Center.
- Process Extended Dynamic SQL (QSQPRCED) API
This application programming interface (API) provides an extended dynamic SQL capability. You can prepare SQL statements into an SQL package and run them by using this API. Statements that are prepared into a package by this API persist until the package or statement is explicitly dropped. For more information about the QSQPRCED API, see Process Extended Dynamic SQL (QSQPRCED) API. For general information about APIs, see Application programming interfaces.
- Syntax Check SQL Statement (QSQCHKS) API
This API syntax checks SQL statements. For more information about the QSQCHKS API, see Syntax Check SQL Statement (QSQCHKS) API. For general information about APIs, see Application programming interfaces.
- DB2 Multisystem
This feature of the operating system allows your data to be distributed across multiple systems. For more information, see DB2 Multisystem.
- DB2 Symmetric Multiprocessing
This feature of the operating system provides the query optimizer with additional methods for retrieving data that include parallel processing. Symmetric multiprocessing (SMP) is a form of parallelism achieved on a single system where multiple processors (CPU and I/O processors) that share memory and disk resource work simultaneously toward achieving a single end result. This parallel processing means that the database manager can have more than one (or all) of the system processors working on a single query simultaneously. For more information, see Controlling parallel processing for queries in the Database Performance and Query Optimization topic collection.
- SQL relational database and system terminology
In the relational model of data, all data is perceived as existing in tables. DB2 UDB for iSeries objects are created and maintained as system objects. - SQL and system naming conventions
You can use either the system (*SYS) or the SQL (*SQL) naming convention in DB2 UDB for iSeries programming. - Types of SQL statements
There are several basic types of SQL statements. They are listed here according to their functions. - SQL communication area
The SQL communication area (SQLCA) is a set of variables that provides an application program with information about its execution of SQL statements. The SQLCA is updated at the end of the execution of every SQL statement. - SQL diagnostics area
The SQL diagnostics area maintained by the database manager provides information about the SQL statement that is most recently run. Your application program can access the SQL diagnostics area using the GET DIAGNOSTICS statement.