Tables and Views
Data accessed through SQL is perceived as tables made up of rows and columns. A table is a base table.
An application program accesses an SQL-defined database by issuing SQL statements that refer to one or more base tables, or to a predefined view of one or more base tables.
Schema and Area
A schema is a named collection of tables and views. The rows of a table are stored in the area that is specified in the CREATE TABLE statement or, if not specified, in the default area for the schema.
Concurrent access to data can be controlled at the area level and the table row level.
SELECT Statement
A SELECT statement requests the DBMS to retrieve data. The table of values returned to the program on a select is a result table. Typically, a result table is a subset of the row and column values in one or more base tables.
Cursor
A cursor is an SQL programming construct that is used to process data in a result table. The cursor defines the result table, and the program can retrieve each row of the result table one at a time with a FETCH statement.
The cursor row whose values are available to the program represents the cursor position. Each FETCH statement advances the cursor position to the next row of the result table.
Updateable Cursor
If the cursor definition meets certain requirements, it is an updateable cursor. The program can update or delete the row on which an updateable cursor is positioned, (that is, the row most recently fetched).
INSERT, UPDATE, and DELETE
The SQL statement to add a row to a table is INSERT and to delete a row is DELETE. The statement to modify one or more column values in a row is UPDATE.
Host Variables
A host variable is a program variable that is referenced in an SQL statement. Host variables are used to receive data retrieved from the database and to supply data to be added to the database.
Local Variables
A local variable of an SQL routine is a program variable declared in a compound statement of an SQL routine. Local variables can be used to receive data retrieved from the database and to supply data to be added to the database.
Routine Parameter
A routine parameter of an SQL routine is a program variable declared in the parameter definition of an SQL routine. Routine parameters provide for the mechanism of passing data between an SQL routine and its invoker, but they can also be used to receive data retrieved from the database and to supply data to be added to the database.
CALL
The CALL procedure is the SQL statement that invokes an external procedure's program or an SQL procedure using a remote procedure paradigm. Input values are passed from CA IDMS to the program or SQL procedure. The output values are returned into the host variables of the program or into the local variables or routine parameters of the SQL procedure specified in the procedure reference.
Bulk Processing
Bulk processing is a CA IDMS extension to the SQL standard that allows the program to select, fetch, or insert a group of rows using a host variable array.
Temporary Table
An application program can create a temporary table, populate it, and manipulate the data in it. A temporary table exists only for the duration of the SQL transaction in which it is created.
Prepared Statement
A program can prepare, or compile, certain SQL statements at runtime. This allows the program to execute an SQL statement that is not known until runtime.
|
Copyright © 2013 CA.
All rights reserved.
|
|