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.
In IBM DB2, a sophisticated and robust relational database management system, SQL aggregate and built-in functions play an integral role in facilitating complex data analysis, summarization, and manipulation. These functions allow for the execution of operations on a set of rows to yield a single value, enhancing the efficiency and effectiveness of data management tasks. The following delineation provides an overview of five pivotal SQL aggregate and built-in functions available in DB2, each chosen for its utility and prevalence in database administration and querying tasks.
1. COUNT: The `COUNT` function is pivotal in SQL query operations, providing the means to ascertain the total number of entries within a dataset that meet specified criteria. Its versatility allows for implementation in various scenarios, including counting all rows in a specific table (`COUNT(*)`) or counting distinct occurrences of a particular column value (`COUNT(DISTINCT column_name)`). This function is indispensable for data analysis, ensuring administrators can quantify dataset attributes efficiently.
2. SUM: The `SUM` function serves a critical role in numerical data aggregation, enabling the summation of values within a specified column. This function is particularly beneficial in financial and statistical applications where totalizing numeric values is a frequent requirement. By employing `SUM(column_name)`, database administrators can swiftly calculate total figures, streamlining data analysis processes.
3. AVG: The `AVG` function is essential for computing the average value of a numerical column, facilitating balanced and meaningful insights into data sets. Utilizing `AVG(column_name)`, it allows for the determination of the mean value, providing a central tendency measure that is crucial in various analytical contexts, from business intelligence to scientific research.
4. MAX: The `MAX` function is instrumental in identifying the maximum value within a column, applicable to both numeric and date/time data types. By executing `MAX(column_name)`, users can pinpoint the highest value, a capability that proves invaluable in scenarios requiring the identification of peak figures, such as sales data analysis or performance metrics evaluation.
5. MIN: Conversely, the `MIN` function enables the determination of the minimum value present in a column, applicable across diverse data types including numeric and date/time. The utilization of `MIN(column_name)` facilitates the identification of the lowest value in a dataset, essential for analyses where understanding the lower bounds or least figures is critical, such as in inventory management or performance assessment.
These SQL aggregate and built-in functions in DB2 are foundational tools that empower database administrators and users to conduct sophisticated data aggregation, analysis, and summary operations. Their integration into SQL queries enhances the capability to derive meaningful insights from vast datasets, thereby bolstering the decision-making processes and operational efficiency within organizations leveraging DB2 as their database management system.

Does the SUBSTR function in DB2 have the same syntax as the SUBSTR function as in Oracle?

Answer:
The `SUBSTR` function, a commonly used string manipulation function in SQL, is present in both DB2 and Oracle databases with similar purposes but slight differences in syntax and parameters. This function is designed to extract a substring from a given string, starting at a specified position and, optionally, for a specified length.
DB2 Syntax:
In DB2, the `SUBSTR` function's basic syntax is as follows:
```
SUBSTR(string, start, length)
```
- `string` is the source string from which the substring will be extracted.
- `start` is the position in the string where extraction will begin. The first position in the string is 1.
- `length` is an optional parameter that specifies the number of characters to be extracted. If omitted, the substring from the start position to the end of the string is returned.
Oracle Syntax:
In Oracle, the `SUBSTR` function shares a similar syntax:
```
SUBSTR(string, start, length)
```
- `string` refers to the input string from which the substring will be extracted.
- `start` indicates the position in the string from which the extraction should begin. A positive value starts the extraction at the specified position counting from the beginning of the string, while a negative value counts from the end of the string.
- `length` is also an optional parameter in Oracle and specifies the number of characters to extract. If not specified, the substring from the start position to the end of the string is extracted.
Key Differences:
While the basic syntax and functionality of the `SUBSTR` function are consistent between DB2 and Oracle, there are nuances and extended features that may differ:
1. Negative Start Position: Oracle's `SUBSTR` function uniquely allows the `start` parameter to be negative, which means the substring extraction can begin from the end of the string, moving backwards. This feature is not standard in DB2's implementation of `SUBSTR`.
2. Function Overloading and Variants: Both DB2 and Oracle might offer variations and overloads of the `SUBSTR` function, catering to specific data types or offering additional functionality. These variants can include differences in handling different character sets, bytes versus characters for multibyte character languages, and specific versions tailored for binary data.
3. Error Handling and Behavior: The way errors or edge cases are handled might differ slightly between the two databases. For example, how each database handles cases where the start position is greater than the string length, or the specified length extends beyond the end of the string, may vary.
In conclusion, while the core functionality and basic syntax of the `SUBSTR` function in DB2 and Oracle are largely similar, it is essential for users to be mindful of database-specific behaviors, especially concerning negative start positions and function variants. Understanding these subtleties ensures the accurate and effective use of the `SUBSTR` function in database operations and string manipulation tasks within each respective environment.
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.

How many DB2 1) date and 2) time 'data types' and their associated functions exist?
In IBM DB2, date and time data types, along with their associated functions, are fundamental components that facilitate the handling of temporal data. These data types and functions enable the storage, retrieval, and manipulation of dates and times, catering to a wide range of application requirements from simple date storage to complex temporal calculations.
Date and Time Data Types in DB2:
1. Date Data Types:
  - DATE: The `DATE` data type is used to store calendar dates, including year, month, and day, without time of day information. The format typically adheres to `YYYY-MM-DD`.
2. Time Data Types:
  - TIME: The `TIME` data type is designated for storing time information in hours, minutes, and seconds, without date information. The standard format is `HH:MM:SS`.
  - TIMESTAMP: The `TIMESTAMP` data type combines date and time into a single data type, capturing both calendar date and time of day with optional fractional seconds. The format is generally `YYYY-MM-DD-HH.MM.SS.SSSSSS`.
Associated Functions:
DB2 provides a rich set of functions to work with these date and time data types, enabling operations such as extraction, formatting, and arithmetic. Key functions include:
1. Date Functions:
  - `CURRENT DATE`: Returns the current date.
  - `DATE()`: Converts a string or a timestamp to a date.
  - `DAY()`, `MONTH()`, `YEAR()`: Extracts the respective component from a date.
  - `DAYS()`: Returns the number of days since a specific base date, useful for date arithmetic.
  - `LAST_DAY()`: Returns the last day of the month for a given date.
2. Time Functions:
  - `CURRENT TIME`: Retrieves the current time.
  - `TIME()`: Converts a string or a timestamp to a time.
  - `HOUR()`, `MINUTE()`, `SECOND()`: Extracts the respective component from a time or timestamp.
  - `CURRENT TIMESTAMP`: Returns the current date and time with fractional seconds.
  - `TIMESTAMP()`: Constructs a timestamp from a date and a time, or converts a string to a timestamp.
3. Timestamp Functions:
  - `TIMESTAMPDIFF()`: Calculates the difference between two timestamps.
  - `TIMESTAMP_FORMAT()`: Converts a string to a timestamp based on a specified format.
  - `TIMESTAMP_ISO()`: Converts a timestamp to ISO format.
Considerations:
While the above list encapsulates the primary date and time data types and a selection of associated functions in DB2, it is important to note that the actual number of functions can expand when considering variations, overloads, and additional utility functions that DB2 provides for specific use cases, including locale-specific formatting and timezone-aware operations. In conclusion, DB2's support for date and time data types, coupled with a comprehensive suite of functions, empowers users to perform intricate temporal data manipulation and querying, catering to diverse application needs and ensuring precise temporal data management.
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.