DB2 SQL Aggregate | SUBSTR, Transactions, Commits and Rollbacks
What are some Sql Aggregates and other built-in functions?
Answer: The common aggregate, built-in functions are AVG, SUM, MIN, MAX, COUNT and DISTINCT.
How is the SUBSTR keyword used in sql?
Answer: SUBSTR is used for string manipulation with column name, first position and string length used as arguments. For example
SUBSTR (NAME, 1 3)
refers to the first three characters in the column NAME.
What are the three DB2 date and time data types and their associated functions?
Answer:
The three data types are DATE, TIME and TIMESTAMP. CHAR can be used to specify the format of each type.
The DAYS function calculates the number of days between two dates. (It's Y2K compliant).
Explain transactions, commits and rollbacks in DB2.
Answer: In DB2 a transaction typically requires a series of updates, insertions and deletions that represent a logical unit of work. A transaction puts an implicit lock on the DB2 data. Programmers can use the COMMIT WORK statement to terminate the transaction creating smaller units for recovery. If the transaction fails DB2 uses the log to roll back values to the start of the transaction or to the preceding commit point.
What is deadlock?
Answer: Deadlock occurs when transactions executing at the same time lock each other out of data that they need to complete their logical units of work.
What are the four lockable units for DB2?
Answer: DB2 imposes locks of four differing sizes: pages, tables, tablespace and for indexes subpage.
What are the three lock types?
Answer: The three types are shared, update and exclusive. Shared locks allow two or more programs to read simultaneously but not change the locked space. An exclusive lock bars all other users from accessing the space. An update lock is less restrictive; it allows other transactions to read or acquire shared locks on the space.
What is isolation level?
Answer: SQL statements may return any number of rows, but most host languages deal with one row at a time by declaring a cursor that presents each row at a unique isolation level.
What is an intent lock?
Answer: An intent lock is at the table level for a segmented tablespace or at the tablespace level for a nonsegmented tablespace. They indicate at the table or tablespace level the kinds of locks at lower levels.
What is the difference between static and dynamic sql?
Answer: Static sql is hard-coded in a program when the programmer knows the statements to be executed. For dynamic sql the program must dynamically allocate memory to receive the query results.
