IBM i SQL
DB2 SQL CURSOR : COMMITMENT CONTROL
When you use COMMIT or ROLLBACK statements with CURSOR, the behavior of the CURSOR depends on whether or not it is declared using the WITH HOLD clause If the CURSOR is declared using the WITHOUT HOLD clause, all of its resources (cursor, locks, and large-object datatype, or LOB, locators) are released upon either COMMIT or [...]
DB2 SQL CURSOR : POSITIONED UPDATE & DELETE
When processing a CURSOR, DB2 SQL give option to update or delete data in base table based on current position of CURSOR in result table. This is called POSITIONED UPDATE or POSITIONED DELETE. Let say there is a table EXAMPLETABLE like CREATE TABLE EXAMPLETABLE (id numeric, name char(10), processedFlag char(1));
DB2 SQL CURSOR : SCROLLABLE CURSOR
A scrollable cursor is one defined with the SCROLL keyword. A scrollable cursor can move in both forward and backward directions. For a scrollable cursor, each row of the result table can be fetched multiple times per OPEN of the cursor. When the cursor is opened, it is positioned before the first row in the result table. Example
DB2 SQL CURSOR : SERIAL CURSOR
SERIAL CURSOR : A serial cursor is one defined without the SCROLL keyword. A serial cursor can only more in forward direction. For a serial cursor, each row of the result table can be fetched only once per OPEN of the cursor. To use a serial cursor again, you must first close the cursor and then re-issue the OPEN statement. When the [...]
DB2 SQL CURSOR : BASIC
SQL CURSORS Result Set of a SELECT Statement which returns more than one row is easy to view on SQL interface like STRSQL. But How we can handle this result set pragmatically in Embedded SQL or SQL/PL code? Answer is SQL CURSOR. CURSOR provides a simple way to access Result Set returned by SELECT Statement. You can think of CURSOR as a [...]
DB2 SQL GLOBAL VARIABLES
GLOBAL VARIABLES GLOBAL VARIABLES are database objects to save a single value at a time(like DTAARA object) They can be accessed and modified using SQL statements. Their definition (only definition, not actual value) is stored in the database catalogs. The reason for this is that global variables have a session scope. This means that [...]
DB2 SQL TABLE EXPRESSION : COMMON TABLE EXPRESSION
COMMON TABLE EXPRESSION (CTE) Unlike NESTED TABLE EXPRESSION CTE is not specified in FROM clause of the query. But CTE is specified before the full-select in a SELECT statement using WITH keyword. A single query can contain multiple CTEs but keyword WITH is used only once. Inside query CTE table is treated as any other normal table. By [...]
DB2 SQL TABLE EXPRESSION : NESTED TABLE EXPRESSION
Nested table expressions(NTE) (also called derived tables) Basic syntax of SQL SELECT statement is like SELECT * FROM table-name Nested table expressions gives you option to replace “table-name”(after FROM clause) with “(SELECT-statement)” which act as Temporary table. To use this Temporary table in the query we [...]
DB2 SQL TABLE EXPRESSION : BASIC
SQL TABLE EXPRESSION SQL TABLE EXPRESSION gives you power to create a temporary table inside a query. Life of this temporary table is only inside that query. As soon as query is completed temporary table will be vanished. This is a very useful and powerful concept and can help you solve very complex issue with very simple queries. There [...]
DB2 SQL GLOBAL TEMPORARY TABLE
Global Temporary Table DECLARE GLOBAL TEMPORARY TABLE gives you a handy option to create table in QTEMP lib with some very useful options. As DECLARE GLOBAL TEMPORARY TABLE creates table in QTEMP lib so It cannot be shared by other sessions. It does not appear in the system catalog. When you end your session, the rows of the table are [...]
DB2 SQL MATERIALIZED QUERY TABLE
Materialized Query Table(MQT) A materialized query table is a table whose structure and definition is based on the result of a given query. Materialized query table get its data from same query. And MQT actually contains that data. That query may contain more than 1 table.
DB2 SQL DB GENERATED SEQUENCE : SEQUENCE OBJECT
SQL SEQUENCE OBJECT SEQUENCE OBJECT are similar to IDENTITY COLUMN in the way that they both generate unique values and use almost same attributes. However, sequences are objects are independent of any tables. Sequences are not tied to a column in a table and are accessed separately. Additionally, they are not treated as any part of a [...]
DB2 SQL DB GENERATED SEQUENCE : ROWID
Using ROWID is another way to have the system assign a unique value to a column. ROWID is similar to IDENTITY COLUMNS. But rather than being an attribute of a numeric column, it is a separate data type. Same as IDENTITY COLUMNS there are 2 options for GENERATED clause. ALWAYS BY DEFAULT
DB2 SQL DB GENERATED SEQUENCE : IDENTITY COLUMN
IDENTITY COLUMN An IDENTITY COLUMN is a numeric column defined in a table for which the column values can be generated automatically by DB2. Only columns of type SMALLINT, INTEGER, BIGINT, DECIMAL, or NUMERIC are allowed for identity columns. Maximum one identity column per table is allowed IDENTITY COLUMN is implicitly NOT NULL.
DB2 SQL DB GENERATED SEQUENCE : BASIC
Let say you add a column ID in your table and you want an incremented value for this column every time a new record is inserted in Table or later you want to re-sequence the same ID column starting from 1. There can be multiple tricks to achieve this but SQL provides multiple very simple and handy option to do all these tasks where a [...]