Database Mock Test

Explanation
The correct answer is SYSIBM.SYSPL. This is because the question is asking where all the PLANS are stored, and the SYSIBM.SYSPL directory is the specific directory where these PLANS are stored. The other options, SY.SYSPL and SYPSL.SB, are not valid directories for storing PLANS.

Explanation
The count(*) function in SQL returns the number of rows in a table. If the target table is empty, there are no rows, so the count(*) function will return 0.

Explanation
ENTITY INTEGRITY is a state where a column that is part of a primary key cannot have null values. This ensures that each record in a table is uniquely identified and prevents duplicate or missing values in the primary key column. Therefore, the statement that a column that is part of a primary key can have null values is incorrect.

Explanation
An index is an ordered set of pointers to rows of a base table. It allows for efficient retrieval of data by providing a quick way to locate specific rows based on the values in one or more columns. The index stores the values of the indexed columns along with the pointers to the corresponding rows, making it easier and faster to search for specific data in the table.

Explanation
SQLCODE -922 is indeed for authorization failure. In SQL, authorization failure occurs when a user attempts to access a database or perform a specific action without the necessary permissions or privileges. When this happens, the SQLCODE -922 is returned to indicate the authorization failure. This can happen if the user does not have the required privileges to execute a certain SQL statement or access a particular table or view.

Explanation
In some database systems, it is possible to use the MAX function on a CHAR column. The MAX function is used to retrieve the maximum value from a column, and it can be applied to numerical as well as character data types. When used on a CHAR column, the MAX function will return the highest value based on the alphabetical order of the characters. However, it is important to note that this behavior may vary depending on the specific database system being used.

Explanation
The statement "Every SQL statement is executable" is false. Not every SQL statement is executable as there are certain types of SQL statements that are used for defining database structures or performing administrative tasks rather than executing a specific action on the data. Examples of such statements include CREATE TABLE, ALTER TABLE, and GRANT. These statements do not directly manipulate the data but rather modify the structure or access rights of the database. Therefore, not all SQL statements are executable.

Explanation
The function SUBSTR(CUSTNAME,1,3) is used to extract the first three characters from the CUSTNAME string. Therefore, the given statement is true.

Explanation
Views in DB2 allow multiple users to access data in a table, with each user only being able to access certain portions of the data. Views provide a way to present a subset of the data from one or more tables, allowing users to see only the specific columns or rows that they are authorized to view. This can be useful for security purposes, as it allows for fine-grained control over the data that each user can access.

Explanation
The final result of a DB2 BIND is the DB2 (application) Plan. This plan is created during the BIND process and contains the optimized access paths for executing SQL statements in the application. It is used by the DB2 optimizer to determine the most efficient way to access data and execute queries. The plan is stored in the DB2 catalog and is loaded into memory when the application is executed.

Explanation
A column is not a DB2 object because it is a component of a table. In a relational database like DB2, a table is made up of rows and columns, where columns define the structure and data type of the data stored in the table. Therefore, while a storage group, table, and synonym are all examples of DB2 objects, a column is not considered a separate object but rather a part of a table object.

Explanation
A positive value of SQL code typically indicates a warning. This means that the SQL statement executed successfully, but there may be some potential issues or non-critical errors that occurred during the execution. It serves as a notification to the user that there might be something worth investigating or addressing, but it does not indicate a critical failure or error in the execution of the SQL code.

Explanation
When a new index is created, it triggers the process of automatic recompilation. This process is necessary to update the database's query execution plans and optimize performance based on the new index. Dropping an existing index does not require automatic recompilation as it simply removes the index from the database. Therefore, the correct answer is that automatic recompilation occurs when a new index is created.

Explanation
The correct answer is PACKAGES. Database services do not consist of packages. Packages are a way to organize and group related database objects together. They are not a part of the core database services but rather a feature or tool that can be used within the database system.

Explanation
A package is a single bound DBRM with an optimized access path. It is a collection of related DBRMs that are stored together in a single package. The package contains information about the access path, which is the most efficient way to retrieve data from the database. By storing multiple DBRMs in a package, the access path can be optimized for the specific requirements of the application, resulting in improved performance. Therefore, a package is the correct answer as it provides an optimized access path for the DBRM.