The difference between a user and a database is that a user has a password
and a database does not
TABLES
SET Table - no duplicate records
MUTLISET Table - duplicate reocrds.(Does not check if you have UPI)
Each Table should have Primary Index (PI) either NUPI or UPI. If not first column is used as Primary Index
Datatypes
BIGINT, INTEGER, SMALLINT, BYTEINT, REAL, FLOAT, DECIMAL, NUMERIC
CHAR, VARCHAR, LONG VARCHAR, CLOB
DATE, TIME, TIMESTAMP
BYTE, VARBYTE, BLOB
Primary Key
- Cannot be Null, Must be Unique to identify each row
- Used to maintain referential integrity, not required by physical table defn
- Does not imply access path
Primary Index
- Used to distribute and retrieve data.
- Can be unique, nonunique, null, values can change
- Defines the most common access path
- 1 AMP operation
- Cannot be changed without recreating the table
When a row is inserted into a table, rdb manager stores 32-bit row hash value of the primary index with it. rdb manager generates unique 32 bit value (also called Uniqueness value) and appends to the Row Hash Value to form a Unique ROWID.
Primary Index - unique or nonunique, partitioned or non partitioned
Secondary Index
- uses subtable which has row-hash, values and rowid
- 2 AMP operation
Value-Ordered Indexes (ORDER BY VALUES)
- good for range queires
Single Table Join Index
Sparse Join Index
Value Ordered Sparse Single Table Join Index
Hash Index
Locking
3 Levels of Locking granularity - Database, Table and Row hash
4 Locking modes -
- Exclusive – deleting entire database
- Write – writing to a table
- Read – select without locking
- Access – Select with locking
Placed in response to a user-defined LOCKING FOR ACCESS modifier or by setting the session default isolation level to READ UNCOMMITTED using the SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL statement .
Permits the user to access for READ an object that may be already locked for READ
or WRITE. An ACCESS lock does not restrict access by another user except when
an EXCLUSIVE lock is required.
MERGE
The MERGE statement merges a source row into a target table based on whether any target rows satisfy a specified matching condition with the source row. The MERGE statement is a single SQL statement that includes both UPDATE and INSERT functionality.
Zero-table SELECT statements return data but do not access tables.
To update rows in a multirow result table in embedded SQL, declare a cursor for the SELECT statement and use it to fetch individual result rows for processing, then use a WHERE CURRENT OF clause in a positioned UPDATE statement to update the selected rows.
and a database does not
TABLES
SET Table - no duplicate records
MUTLISET Table - duplicate reocrds.(Does not check if you have UPI)
Each Table should have Primary Index (PI) either NUPI or UPI. If not first column is used as Primary Index
Datatypes
BIGINT, INTEGER, SMALLINT, BYTEINT, REAL, FLOAT, DECIMAL, NUMERIC
CHAR, VARCHAR, LONG VARCHAR, CLOB
DATE, TIME, TIMESTAMP
BYTE, VARBYTE, BLOB
Primary Key
- Cannot be Null, Must be Unique to identify each row
- Used to maintain referential integrity, not required by physical table defn
- Does not imply access path
Primary Index
- Used to distribute and retrieve data.
- Can be unique, nonunique, null, values can change
- Defines the most common access path
- 1 AMP operation
- Cannot be changed without recreating the table
When a row is inserted into a table, rdb manager stores 32-bit row hash value of the primary index with it. rdb manager generates unique 32 bit value (also called Uniqueness value) and appends to the Row Hash Value to form a Unique ROWID.
Primary Index - unique or nonunique, partitioned or non partitioned
Secondary Index
- uses subtable which has row-hash, values and rowid
- 2 AMP operation
Value-Ordered Indexes (ORDER BY VALUES)
- good for range queires
Single Table Join Index
Sparse Join Index
Value Ordered Sparse Single Table Join Index
Hash Index
Locking
3 Levels of Locking granularity - Database, Table and Row hash
4 Locking modes -
- Exclusive – deleting entire database
- Write – writing to a table
- Read – select without locking
- Access – Select with locking
Placed in response to a user-defined LOCKING FOR ACCESS modifier or by setting the session default isolation level to READ UNCOMMITTED using the SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL statement .
Permits the user to access for READ an object that may be already locked for READ
or WRITE. An ACCESS lock does not restrict access by another user except when
an EXCLUSIVE lock is required.
MERGE
The MERGE statement merges a source row into a target table based on whether any target rows satisfy a specified matching condition with the source row. The MERGE statement is a single SQL statement that includes both UPDATE and INSERT functionality.
Zero-table SELECT statements return data but do not access tables.
To update rows in a multirow result table in embedded SQL, declare a cursor for the SELECT statement and use it to fetch individual result rows for processing, then use a WHERE CURRENT OF clause in a positioned UPDATE statement to update the selected rows.
Comments