SQL concepts

Edit online

Db2 for i 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 IBM i 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 i licensed program is not installed.

  • SQL precompilers
    SQL precompilers support precompiling embedded SQL statements in host languages. The following languages are supported:
    • ILE C
    • ILE C++
    • ILE COBOL
    • COBOL
    • PL/I
    • RPG III (part of RPG)
    • ILE RPG

    The SQL host language precompilers prepare an application program that contains 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 IBM Db2 Query Manager and SQL Development Kit for i licensed program.

  • Run SQL Scripts in IBM® i Access Client Solutions (ACS)

    For information about ACS, see https://www.ibm.com/support/pages/ibm-i-access-client-solutions

  • Run SQL Statements (RUNSQLSTM) CL command

    The RUNSQLSTM command can be used to run a series of SQL statements that are stored in a source file or a source stream file. For more information about the RUNSQLSTM command, see Using the SQL statement processor.

  • Run SQL (RUNSQL) CL command

    The RUNSQL command can be used to run a single SQL statements. For more information about the RUNSQL command, see Using the RUNSQL CL command.

  • DB2® Query Manager

    DB2 Query Manager 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 IBM Db2 Query Manager and SQL Development Kit for i licensed program. For more information, see Query Manager UseLink to PDF.

  • 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.

  • Call Level Interface (SQL CLI)

    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 IBM i 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 (option 27) enables the use of SQL partitioned tables, which allows an SQL table to contain more than one partition (member) and therefore be much larger in size. For more information, see Db2 Multisystem.

  • DB2 Symmetric Multiprocessing

    This feature (Option 26) 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.