Planning
Security
Working with Databases and Database Objects
| The correct answer is D. In order to access a remote database from a client workstation, the database must be cataloged in the system database directory of both the client and the server and the server workstation must be cataloged in the client's node directory. (The entry in the node directory tells the DB2 Database Manager how to connect to the server to get access to the database stored there.) Because the information needed to connect to DRDA host databases is different from the information used to connect to LAN-based databases, information about remote host or iSeries databases is kept in a special directory known as the Database Connection Services (DCS) directory. If an entry in the DCS directory has a database name that corresponds to the name of a database stored in the system database directory, the specified Application Requester (which in most cases is DB2 Connect) can forward SQL requests to the database that resides on a remote DRDA server. The contents of the DCS directory file can be viewed by executing the LIST DCS DIRECTORY command. If there is no record for a zSeries or iSeries database in the DCS directory, no database connection can be established. | |||||||||||||
| The correct answer is D. In order to access a remote database on a z/OS server, the database must be cataloged in the system database directory of the client, the server must be cataloged in the client's node directory, and an entry for the database must exist in the DCS directory. Answer D illustrates the proper way to catalog the server, the DCS database, and create a corresponding entry for the DCS database in the system database directory. | |||||||||||||
| The correct answer is D. The correct syntax for the CATALOG DATABASE command is: CATALOG [DATABASE | DB] [DatabaseName]<AS [Alias]><ON [Path] | AT NODE [NodeName]><AUTHENTICATION [AuthenticationType]><WITH "[Description]">where:
| |||||||||||||
| The correct answer is A. The system catalog tables are used to keep track of information like database object definitions, database object dependencies, database object privileges, column data types, and table constraints. In most cases, the complete characteristics of a database object are stored in one or more system catalog tables when the object is created. However in some cases, such as when triggers and constraints are defined, the actual SQL used to create the object is stored instead. Characteristics of views, not the SQL used to create them, are stored in the system catalog tables. | |||||||||||||
| The correct answer is B. The Control Center and the Configuration Assistant provide visual tools for cataloging databases while the CATALOG DATABASE command can be executed from the Command Line Processor. Since the CATALOG DATABASE command is a command and not an SQL statement, it cannot be issued from the SQL Assist utility. | |||||||||||||
| The correct answer is B. A scalar user-defined function would be the best option for the requirements outlined in answer A; an UPDATE trigger and a DELETE trigger that inserts records into an activity table every time update and delete operations are performed on a table containing sensitive data would be the best way to accomplish the requirements outlined in answer C; and an identity column or sequence could be used to address the requirements shown in answer D. | |||||||||||||
| The correct answer is A. If the UNIQUE clause is specified when the CREATE INDEX statement is executed, rows in the table associated with the index will not have two or more occurrences of the same values in the set of columns that make up the index key. Furthermore, the creation of an index provides a logical ordering of the rows of a table so in this example, rows inserted into the ORDERS table will be ordered ORDER_NUM values, in ascending order. | |||||||||||||
| The correct answers are C and E. An alias is simply an alternate name for a table or view. (Aliases can also be created for nicknames that refer to tables or views found on federated systems.) Once created, an alias can be referenced the same way the table or view the alias refers to can be referenced. | |||||||||||||
| The correct answer is A. A trigger can be activated whenever an insert, update, or delete operation is performed against the subject table that is associated with the trigger. | |||||||||||||
| The correct answer is D. Although a view looks like a base table, it does not exist as a table in physical storage; therefore, it does not contain data. Instead, a view refers to data that is stored in other base tables so data stored in the EMPLOYEE table would not be copied to view V1. | |||||||||||||
| The correct answer is C. An alias is an alternate name for a table or view; therefore, it is possible to create an alias named T1 for the table TABLE1, and it is possible to create an alias named T1 for a view named V1. Views are used to provide a different way of looking at the data stored in one or more base tables. Schemas, on the other hand, are used to logically group data; therefore, a schema named T1 could be used to group aliases and views that reference table TABLE1, but it could not be used to provide access to the data stored in table TABLE1. | |||||||||||||
| The correct answer is A. Sequences, identity columns, and triggers can be used to automatically generate values for columns. However, only sequences can be referenced in an INSERT statement. | |||||||||||||
| | | ||||||||||||
| The correct answer is D. The first VALUES statement executed by user USER1 will return the value 10; the second will return the value 20. The ALTER statement changes the behavior of the sequence and the VALUES statement executed by user USER2 will return the value 5. When user USER3 executes queries the database to obtain the next sequence number, the value 30 is returned. Why? Because when user USER2 obtained a value from the sequence, four more values were generated and cached since a cache value of 5 was specified for the sequence. Since an increment value of 5 was also used, the numbers cached were: 10, 15, 20, and 25. But none of the cached values were used-they were discarded when user USER2 terminated his database connection. Then, when user USER3 queried the sequence for the next number available, he received the number 30 because that was, in fact, the next number in the sequence. | |||||||||||||
| The correct answer is C. The statement "INSERT INTO view2 VALUES(150, 'abc')" will fail because the value 150 is greater than 100; because view VIEW2 was created with the WITH CASCADED CHECK OPTION specified, the "WHERE c1 < 100" clause used to create view VIEW1 became a constraint that is used to validate insert and update operations that are performed against view VIEW2 to ensure that all rows inserted into or updated in the base table the view refers to conform to the view's definition. | |||||||||||||
| The correct answer is D. Each time a record is inserted into table T1, trigger TRIG1 is fired and a record is written to the table T1AUDIT. If both tables were queried after the update operation completes, the results would look something like this: SELECT * FROM t1C1 C2----------------1 ghi2 - 2 record(s) selected. SELECT * FROM t1auditUSER DATE ACTION---------------------------------------------------RSANDERS 01/20/2007 InsertRSANDERS 01/20/2007 Insert 2 record(s) selected.In order to track update and delete operations performed against table T1, similar UPDATE and DELETE triggers would need to be created. | |||||||||||||
| The correct answer is B. Unlike base tables, whose descriptions and constraints are stored in the system catalog tables of the database to which they belong, declared temporary tables are not persistent and can only be used by the application that creates them-and only for the life of the application. When the application that creates a declared temporary table terminates, the rows of the table are deleted, and the description of the table is dropped. (However, data stored in a temporary table can exist across transaction boundaries.) Another significant difference focuses on naming conventions: Base table names must be unique within a schema, but because each application that defines a declared temporary table has its own instance of that table, it is possible for many applications to create declared temporary tables that have the same name. | |||||||||||||
| The correct answer is B. A package is an object that contains the information needed to process SQL statements associated with a source code file of an application program. When an Embedded SQL source code file is precompiled, a corresponding package that contains, among other things, the access plans that will be used to process each SQL statement embedded in the source code file is produced. (Access plans contain optimized information that the DB2 Database Manager uses to execute SQL statements.) This package must reside in a DB2 database that contains the data objects referenced by the package before the corresponding application can be executed against that database. The process of creating and storing a package in a DB2 database is known as "binding," and by default, packages are automatically bound to a database during the precompile process. Schemas provide a logical grouping of database objects; the system catalog describes the objects in a DB2 database and their relationship to each other; and Multi-dimensional Clustering Tables (MCDs) may be used during query optimization to improve the performance for a subset of SELECT queries. | |||||||||||||
| The correct answer is C. In this case, both the node and the database must be cataloged by executing the CATALOG … NODE and CATALOG DATABASE commands. Answer C is the only answer that uses the correct syntax for these two commands. | |||||||||||||
| The correct answer is B. Declared temporary tables are used to hold temporary data on behalf of a single application and are automatically destroyed when the application that declared them disconnects from the database. Declared temporary tables are not used for backup purposes, to save data for load operations, or to share result data sets between applications. | |||||||||||||
| The correct answer is D. Routines are a type of database object that you can use to encapsulate logic that can be invoked like a programming sub-routine. There are many different types of routines available; routines can be grouped in different ways, but are primarily grouped by their system or user definitions, by their functionality, and by their implementation. The supported routine definitions are: · System-defined routines · User-defined routines The supported functional types of routines are: · Procedures (also called stored procedures) · Functions · Methods The supported routine implementations are: · Built-in routines · Sourced routines · SQL routines · External routines | |||||||||||||
| The correct answer is A. Views can be used to restrict access to columns in a base table that contain sensitive data, views can be used to store queries that multiple applications execute on a regular basis in a database, and views support INSTEAD OF triggers. Aliases are publicly referenced names that require no special authority or privilege to use. | |||||||||||||
| The correct answer is C. A distinct data type is a user-defined data type that is derived from one of the built-in data types available with DB2. Although a distinct data type shares a common internal representation with a built-in data type, it is considered a separate data type that is distinct from any other data type (hence, the "distinct" in the name). Distinct user-defined data types can be created by executing the CREATE DISTINCT TYPE SQL statement. The basic syntax for this statement is: CREATE DISTINCT TYPE [TypeName]AS [SourceDataType]<WITH COMPARISONS>where:
Thus, if you wanted to create a distinct data type to store EURO data, you could do so by executing an SQL statement like that in answer C. |
Working with DB2 Data Using SQL and XQuery
| The correct answer is D. When a SELECT statement such as the one shown is executed, the result data set produced will contain all possible combinations of the rows found in each table specified (otherwise known as the Cartesian product). Every row in the result data set produced is a row from the first table referenced concatenated with a row from the second table referenced, concatenated in turn with a row from the third table referenced, and so on. The total number of rows found in the result data set produced is the product of the number of rows in all the individual table-references; in this case, 5 x 5 = 25. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is D. The proper way to test for a missing value (or null) is by using the NULL predicate with a WHERE clause, and answer D shows the correct way to construct such a WHERE clause. Keep in mind that NULL, zero (0), and blank ("") are not the same value. NULL is a special marker used to represent missing information, while zero and blank (empty string) are actual values that can be stored in a column to indicate a specific value (or lack thereof). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is D. When a full outer join operation is performed, rows that would have been returned by an inner join operation, together with all rows stored in both tables of the join operation that would have been eliminated by the inner join operation are returned in the result data set produced. An inner join can be thought of as the cross product of two tables, in which every row in one table that has a corresponding row in another table is combined with that row to produce a new record. When a left outer join operation is performed, rows that would have been returned by an inner join operation, together with all rows stored in the leftmost table of the join operation (i.e., the table listed first in the OUTER JOIN clause) that would have been eliminated by the inner join operation, are returned in the result data set produced. When a right outer join operation is performed, rows that would have been returned by an inner join operation, together with all rows stored in the rightmost table of the join operation (i.e., the table listed last in the OUTER JOIN clause) that would have been eliminated by the inner join operation, are returned in the result data set produced. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. The UPDATE statement assigns a NULL value to column C1 and NULL values are displayed as a dash (-). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is B. If the DISTINCT clause is specified with a SELECT statement, duplicate rows are removed from the final result data set returned. Two rows are considered to be duplicates of one another if the value of every column of the first row is identical to the value of the corresponding column of the second row. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is B. The FETCH FIRST clause is used to limit the number of rows that are returned to the result data set produced in response to a query. When used, the FETCH FIRST clause is followed by a positive integer value and the words ROWS ONLY. This tells the DB2 Database Manager that the user/application executing the query does not want to see more than n number of rows, regardless of how many rows might exist in the result data set that would be produced were the FETCH FIRST clause not specified. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answers are A and E. An inner join can be thought of as the cross product of two tables, in which every row in one table that has a corresponding row in another table is combined with that row to produce a new record. The syntax for a SELECT statement that performs an inner join operation is: SELECT[* | [Expression] <<AS> [NewColumnName]> ,...]FROM [[TableName] <<AS> [CorrelationName]> ,...][JoinCondition]where:
The following syntax can also be used to create a SELECT statement that performs an inner join operation: SELECT[* | [Expression] <<AS> [NewColumnName]> ,...]FROM [[TableName1] <<AS> [CorrelationName1]>]<INNER> JOIN[[TableName2] <<AS> [CorrelationName2]>]ON [JoinCondition]where:
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. When the EXCEPT set operator is used, the result data sets produced by each individual query are combined, all duplicate rows found are eliminated, and all records found in the first result data set that have a corresponding record in the second result data set are eliminated, leaving just the records that are not found in both result data sets. When the UNION set operator is used, the result data sets produced by each individual query are combined and all duplicate rows are eliminated; when the INTERSECT set operator is used, the result data sets produced by each individual query are combined, all duplicate rows found are eliminated, and all records found in the first result data set that do not have a corresponding record in the second result data set are eliminated, leaving just the records that are found in both result data sets; and MERGE is not a set operator. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. When a right outer join operation is performed, rows that would have been returned by an inner join operation, together with all rows stored in the rightmost table of the join operation (i.e., the table listed last in the OUTER JOIN clause) that would have been eliminated by the inner join operation are returned in the result data set produced. In this case, we want to see all records found in the POINTS table, along with any corresponding records found in the NAMES table, so a right outer join is the appropriate join operation to use. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is A. The pattern of characters specified with the LIKE clause of a SELECT statement can consist of regular alphanumeric characters and/or special metacharacters that are interpreted as follows: · The underscore character (_) is treated as a wild card character that stands for any single alphanumeric character. · The percent character (%) is treated as a wild card character that stands for any sequence of alphanumeric characters. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is D. When the UNION ALL set operator is used, the result data sets produced by each individual query are combined; all duplicate rows found are retained. Thus with this example, the results of both tables are combined (6 rows + 6 rows = 12 rows) and the duplicate rows for "Jagger, Mick", "Richards, Keith", and "Watts, Charlie" are retained. Had the UNION set operator been used instead, the result data sets produced by each individual query would have been combined, all duplicate rows would have been eliminated, and the answer would have been 9 (12 - 3 = 9 rows). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. Data is stored in a table in no particular order, and unless otherwise specified, a query only returns data in the order in which it is found. The ORDER BY clause is used to tell the DB2 Database Manager how to sort and order the rows that are to be returned in a result data set produced in response to a query. When specified, the ORDER BY clause is followed by the name of one or more column(s) whose data values are to be sorted and a keyword that indicates the desired sort order. If the keyword ASC follows the column's name, ascending order is used, and if the keyword DESC follows the column name, descending order is used. If no keyword is specified, ascending order is used by default. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is B. The subselect produces a result data set that contains hire year and salary information for each employee whose salary is greater than $30,000.00. The GROUP BY clause is used to tell the DB2 Database Manager how to organize rows of data returned in the result data set produced in response to a query. (The GROUP BY clause specifies an intermediate result table consisting of a group of rows.) In this example, the GROUP BY clause tells the outer SELECT to calculate and group average salary information by hire year. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answers are B and C. The HAVING clause is used to apply further selection criteria to columns that are referenced in a GROUP BY clause. This clause behaves like the WHERE clause, except that it refers to data that has already been grouped by a GROUP BY clause (the HAVING clause is used to tell the DB2 Database Manager how to select the rows that are to be returned in a result data set from rows that have already been grouped.) And like the WHERE clause, the HAVING clause is followed by a search condition that acts as a simple test that, when applied to a row of data, will evaluate to TRUE, FALSE, or Unknown. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is B. There is no such thing as a RIGHT INNER JOIN or a LEFT INNER JOIN so the statements shown in Answers A and D are invalid. Because we want to get employee records for employees who do not have a manager and because the EMPLOYEE table is listed before the OUTER JOIN clause, the LEFT OUTER JOIN is the correct join to use. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. One efficient and concise way to display coded values in a readable format is to use one or more CASE expressions in the selection list of a query. Each CASE operation evaluates a specified expression and supplies a different value, depending on whether a certain condition is met. A CASE expression can take one of two forms: simple or searched. The CASE statement presented in the question is a searched CASE expression; in this example, if the INSTRUMENT column contains the value '01', the word 'HARMONICA' is returned, if the INSTRUMENT column contains the value '02', the word 'GUITAR' is returned, if the INSTRUMENT column contains the value '03', the word 'DRUMS' is returned, and if the INSTRUMENT column contains any other value, the word 'UNKNOWN' is returned. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is B . When the results of a query, or subselect, are used to provide values for one or more columns identified in the column name list provided for an UPDATE statement, the values retrieved from one base table or view are used to modify values stored in another. The number of values returned by the subselect must match the number of columns provided in the column name list specified, and only one record can be returned. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is D. Because the EMPID column in each table was defined in such a way that it does not allow null values, a non-null value must be provided for this column any time data is inserted into either table. The INSERT statement shown in answer D does not provide a value for the EMPID column of the CURRENT_EMPLOYEES table, so the statement will fail. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is B. The DELETE statement is used to remove specific records from a table (the DROP statement completely destroys the table object), and the correct syntax for the DELETE statement is DELETE FROM [TableName] … | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is A. The ORDER BY clause is used to tell the DB2 Database Manager how to sort and order the rows that are to be returned in a result data set produced in response to a query. In this example, all rows containing the value “ADMIN” in the DEPT column would be listed first, followed by all rows containing the value “PRODUCTION”, followed by all rows containing the value “SALES”. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is A. Delete operations can be conducted in one of two ways: as searched delete operations or as positioned delete operations. To perform a positioned delete, a cursor must first be created, opened, and positioned on the row to be deleted. Then, the DELETE statement used to remove the row must contain a WHERE CURRENT OF [CursorName] clause (CursorName identifies the cursor being used). Because of their added complexity, positioned delete operations are typically performed by embedded SQL applications. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is B. Common table expressions are mechanisms that are used to construct local temporary tables that reside in memory and only exist for the life of the SQL statement that defines them. The syntax used to construct a common table expression is: WITH [TableName]<([ColumnName] ,...])>AS ([SELECTStatement])where:
So in this example, all of the data stored in table TAB1, with the exception of the record "150 - def" is copied to a common table named SUBSET, and then a query is run against this common table. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. Since we are looking for values in the PERSON column of TABLE1 that have a matching value in the ID column of TABLE2, the statement shown in Answer C is the only statement that is correct. (The SQL statements shown in Answers B and D are incorrect because there is no PERSON column in TABLE2; the statement shown in Answer A is incorrect because it is looking for values that match those in the ID column in TABLE1, not the PERSON column.) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. When a right outer join operation is performed, rows that would have been returned by an inner join operation, together with all rows stored in the rightmost table of the join operation (i.e., the table listed last in the OUTER JOIN clause) that would have been eliminated by the inner join operation are returned in the result data set produced. In this case, we want to see all records found in the POINTS table, along with any corresponding records found in the NAMES table, so a right outer join is the appropriate join operation to use. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is B. COUNT(empno) together with GROUP BY deptname displays the total number of employees in each department; SELECT deptname displays the corresponding department name for each department ID, and ORDER BY 2 DESC sorts the data by employee count (which is column 2) from greatest to least. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is D. Because the EMPID column was defined in such a way that it does not allow null values, a non-null value must be provided for this column anytime data is inserted into either table. The INSERT statement shown in Answer D does not provide a value for the EMPID column of the CURRENT_EMPLOYEES table, so the statement will fail. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is D. Because ‘NULL’ is treated as a string instead of a NULL value, the SQL statements shown in Answers A and C would not set the STATUS to NULL; the statement shown in Answer B is invalid because the SET keyword is only used once in the UPDATE statement. Therefore, statement D is the only UPDATE statement shown that will accomplish the desired task. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is A. The IN predicate is used to define a comparison relationship in which a value is checked to see whether or not it matches a value in a finite set of values. This finite set of values can consist of one or more literal values coded directly in the SELECT statement, or it can be composed of the non-null values found in the result data set generated by a subquery. So in this example, the non-null values that appear in the result data set produced by the subquery are the values 1 and 2, and the only row in TAB1 that has a matching value in COL1 is the row with the value 1 in it. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is D. Data is stored in a table in no particular order, and unless otherwise specified (with an ORDER BY clause), a query only returns data in the order in which it is found. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. When the UNION set operator is used, the result data sets produced by each individual query are combined and all duplicate rows are eliminated. Thus with this example, the results of both tables are combined (6 rows + 6 rows = 12 rows) and the duplicate rows for Jagger, Mick and Richards, Keith are removed (12 – 2 = 10 rows). So 10 rows are returned. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. A transaction (also known as a unit of work) is a sequence of one or more SQL operations grouped together as a single unit, usually within an application process. A given transaction can perform any number of SQL operations-from a single operation to many hundreds or even thousands, depending on what is considered a "single step" within your business logic. The initiation and termination of a single transaction defines points of data consistency within a database; either the effects of all operations performed within a transaction are applied to the database and made permanent (committed), or the effects of all operations performed are backed out (rolled back) and the database is returned to the state it was in before the transaction was initiated. In most cases, transactions are initiated the first time an executable SQL statement is executed after a connection to a database has been made or immediately after a preexisting transaction has been terminated. Once initiated, transactions can be implicitly terminated using a feature known as "automatic commit" (in this case, each executable SQL statement is treated as a single transaction, and any changes made by that statement are applied to the database if the statement executes successfully or discarded if the statement fails) or they can be explicitly terminated by executing the COMMIT or the ROLLBACK SQL statement. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. DB2 uses a mechanism known as a savepoint to allow an application to break the work being performed by a single large transaction into one or more subsets. Once created, a savepoint can be used in conjunction with a special form of the ROLLBACK SQL statement to return a database to the state it was in at the point in time a particular savepoint was created. The syntax for this form of the ROLLBACK statement is: ROLLBACK <WORK> TO SAVEPOINT <[ SavepointName]>where:
So, in this example, every operation performed between the time savepoint S1 was created and the ROLLBACK TO SAVEPOINT statement was executed was undone. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. When a cursor that has been declared with the WITH HOLD option specified (as in the example shown) is opened, it will remain open across transaction boundaries until it is explicitly closed; otherwise, it will be implicitly closed when the transaction that opens it is terminated. In this example, the cursor is opened, the first three rows are fetched from it, the transaction is committed (but the cursor is not closed), another row is fetched from it, and then the cursor is closed. Thus, the last value obtained will be: TAB1---------------COL1 COL2---- ---D 40 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is A. The CALL statement is used to invoke a stored procedure, so answer D is wrong; because a stored procedure cannot be invoked using its specific name, answer B is wrong; and since SALES is a character string value that is being passed to the procedure, it must be enclosed in single quotes. Therefore, answer C is wrong. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answers are A and B. How a user-defined function is invoked depends a lot on what it has been designed to do; scalar user-defined functions can be invoked as an expression in the select list of a query while table and row functions must be referenced by the FROM clause. In because the user-defined function used in this example is a scalar function that only returns a single value, answer B is the correct way to call it. A scalar function can also be invoked by executing a VALUES statement that looks something like the one shown in answer A. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is A. In XQuery, expressions are the main building blocks of a query. Expressions can be nested and form the body of a query. A query can also have a prolog that contains a series of declarations that define the processing environment for the query. Thus, if you wanted to retrieve customer names for all customers who reside in North Carolina from XML documents stored in the CUSTINFO column of a table named CUSTOMER (assuming this table has been populated with the INSERT statement we looked at earlier), you could do so by executing an XQuery expression that looks something like this: XQUERY declare default element namespace "http://custrecord.dat";for $info in db2-fn:xmlcolumn('CUSTOMER.CUSTINFO')/customerinfowhere $info/addr/state-prov="North Carolina" return $info/nameAnd when this XQuery expression is executed from the Command Line Processor, it should return information that looks like this (again, assuming this table has been populated with the INSERT statement we looked at earlier): 1------------------------------<name xmlns="http://custrecord.dat">John Doe</name> If you wanted to remove the XML tags and just return the customer name, you could do so by executing an XQuery expression that looks like this instead: XQUERY declare default element namespace "http://custrecord.dat"; for $info in db2-fn:xmlcolumn('CUSTOMER.CUSTINFO')/customerinfowhere $info/addr/state-prov="North Carolina" return$info/name/text()Now, when the XQuery expression is executed from the Command Line Processor, it should return information that looks like this: 1-------John Doe | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is D. NUMBER, INTERVAL, and BYTE are not valid DB2 data types. The terms DECIMAL, DEC, NUMERIC, and NUM are used to denote the decimal data type. The decimal data type is used to store numeric values that contain both whole and fractional parts, separated by a decimal point. The exact location of the decimal point is determined by the precision and the scale of the value (the scale is the number of digits used by the fractional part). The maximum precision allowed for decimal values is 31 digits, and the corresponding scale must be a positive number less than the precision of the number. If no precision or scale is specified, a scale of 5 and a precision of 0 is used by default - DECIMAL(5,0). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. The XML data type is used to store XML documents in their native format. The amount of storage space set aside to store an XML document varies and is determined in part, by the size and characteristics of the XML document being stored. The integer data type is used to store numeric values that have a precision of 10 digits. The range for integer values is -2,147,483,648 to 2,147,483,647, and 4 bytes of storage space is required for every integer value stored. The terms INTEGER and INT are used to denote the integer data type. The fixed-length character string data type is used to store character string values that are between 1 and 254 characters in length. The amount of storage space needed to store a fixed-length character string value can be determined by solving the following equation: (Number of characters x 1) = Bytes required. (A fixed amount of storage space is allocated, even if all of the space allocated is not needed-short strings are padded with blanks.) The terms CHARACTER and CHAR are used to denote the fixed-length character string data type. The double-precision floating-point data type is used to store a 64-bit approximation of a real number. This number can be zero, or it can fall within the range -1.79769E+308 to -2.225E-307 or 2.225E-307 to 1.79769E+308. Each double-precision floating-point value can be up to 53 digits in length, and 8 bytes of storage space is required for every value stored. The terms DOUBLE, DOUBLE PRECISION, and FLOAT are used to denote the double-precision floating-point data type. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is A. The decimal data type is used to store numeric values that contain both whole and fractional parts, separated by a decimal point. The terms DECIMAL, DEC, NUMERIC, and NUM are used to denote the decimal data type. Since currency values contain both whole and fractional parts, the decimal data type is the best choice to base a userdefined data type on. And to create a distinct data type named CURRENCY that can be used to store numeric data you would execute a CREATE DISTINCT TYPE SQL statement that looks like the one shown in Answer A. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is B. The character large object (CLOB) data type is used to store single-byte character data. The binary large object (BLOB) data type is used to store binary data; the double-byte character large object (DBCLOB) data type is used to store double-byte character data; and the fixed-length double-byte character string (GRAPHIC) data type is used to store double-byte character data strings. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is D. The data type assigned to an identity column must be a numeric data type with a scale of 0; therefore, the only data types that can be assigned to an identity column are: SMALLINT, INTEGER, BIGINT, DECIMAL/NUMERIC, or a user-defined data type that is based on one of these data types. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. The XMLPARSE function parses a character string and returns an XML value; the character string expression to be parsed must evaluate to a well-formed XML document that conforms to XML 1.0, as modified by the XML Namespaces recommendation. Answers A, B, and D are character strings that are comprised of a starting tag, an optional value, and a corresponding ending tag. As a result, these strings can be converted into a well-formed, but small, XML documents. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answers are C and E. At a minimum, when a new table is created, a table name, one or more column names, and corresponding column data types must be specified. Primary keys, constraints (NOT NULL, default, check, unique, referential integrity, and informational), and table space information is optional and is not required. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is D. A unique index, a check constraint, and a referential constraint place restrictions on what can and cannot be stored in the column(s) they are associated with. A default constraint, however, is used to provide a default value for a particular column if no data is provided for that column when data is inserted into a table; if a value is provided for the column, the default value is ignored. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answers are D and E. When the CREATE TABLE … LIKE … statement is executed, each column of the table that is created will have exactly the same name, data type and nullability characteristic as the columns of the source table used to create the new table. Furthermore, if the EXCLUDING COLUMN DEFAULTS option is not specified (which is the case in this example), all column defaults will be copied as well. However, the new table will not contain any unique constraints, foreign key constraints, triggers, or indexes that exist in the original. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is A. The Insert Rule for a referential constraint guarantees that a value can never be inserted into the foreign key of a child table unless a matching value can be found in the corresponding parent key of the associated parent table. Any attempt to insert records into a child table that violates this rule will result in an error, and the insert operation will fail. The Insert Rule for a referential constraint is implicitly created when the referential constraint itself is created. In this example, the EMPID column of table TAB2 is a foreign key (in a child table) that references the ID column (the parent key) of table TAB1 (the parent table). Therefore, because of the Insert Rule, values cannot be added to the EMPID column of table TAB2 that do not already exist in the ID column of table TAB1. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is B. A unique constraint can be used to ensure that the value(s) assigned to one or more columns when a record is added to a base table are always unique; once a unique constraint has been defined for one or more columns, any operation that attempts toplace duplicate values in those columns will fail. Although a unique, system-required index is used to enforce a unique constraint, there is a distinction between defining a unique constraint and creating a unique index; even though both enforce uniqueness, a unique index allows NULL values and generally cannot be used in a referential constraint. A unique constraint on the other hand, does not allow NULL values and can be referenced in a foreign key specification. (The value "NULL" means a column's value is undefined and distinct from any other value, including other NULL values). A check constraint (also known as a table check constraint) can be used to ensure that a particular column in a base table is never assigned an unacceptable value-once a check constraint has been defined for a column, any operation that attempts to place a value in that column that does not meet specific criteria will fail. The default constraint can be used to ensure that a particular column in a base table is assigned a predefined value (unless that value is overridden) each time a record is added to the table. The predefined value provided could be null (if the NOT NULL constraint has not been defined for the column), a user-supplied value compatible with the column's data type, or a value furnished by the DB2 Database Manager. Unlike other constraints, informational constraints are not enforced during insert and update processing. However, the DB2 SQL optimizer will evaluate information provided by an informational constraint when considering the best access plan to use to resolve a query. As a result, an informational constraint may result in better query performance even though the constraint itself will not be used to validate data entry/modification. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. A check constraint is used to ensure that a particular column in a base table is never assigned an unacceptable value-once a check constraint has been defined for a column, any operation that attempts to place a value in that column that does not meet specific criteria will fail. Check constraints are comprised of one or more predicates that collectively are known as the check condition. This check condition is compared with the data value provided and the result of this comparison is returned as the value TRUE, FALSE, or Unknown. If the check constraint returns the value TRUE, the value is acceptable, so it is added to the database. If, on the other hand, the check constraint returns the value FALSE or Unknown, the operation attempting to place the value in the database fails, and all changes made by that operation are backed out. In this example, the check constraint CST1 defined for table TAB1 only allows the values 1, 2, or 3 to be entered into column COL1. The INSERT statement shown in Answer C is the only INSERT statement that has a valid value specified for column COL1. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is A. The ON DELETE RESTRICT ensures that whenever a delete operation is performed on the parent table of a referential constraint, the value for the foreign key of each row in the child table will have the same matching value in the parent key of the parent table that it had before the delete operation was performed. Therefore, in this example no row will be deleted from the MAKE because two rows exists in the MODEL table that references the row the DELETE statement is trying to remove. Had the ON DELETE CASCADE definition been used instead, the delete operation would have succeeded and the tables would have looked like this:
On the other hand, if the ON DELETE SET NULL definition had been used, the delete operation would have succeeded and the tables would have looked like this:
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is D. A unique index allows one and only one NULL value; the value "NULL" means a column's value is undefined and distinct from any other value. The remaining characteristics are true for unique indexes. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is D. In this example, the statement "INSERT INTO employee VALUES (DEFAULT, 'Smith', 10, '11/18/2004', 60250.00)" will fail because the hire date and the salary specified violates check constraint CST1 - YEAR(hiredate) > 2006 OR salary > 60500) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is B. To create a referential constraint, you define a primary key, using one or more columns in the parent table, and you define a foreign key for one or more corresponding columns in the child table that reference the parent table's primary key. (The list of column names in the foreign key clause must be identical to the list of column names specified in the primary key OR a unique constraint for the columns in the parent table that are referenced by the foreign key in the child must exist in order for a referential constraint to be successfully created.) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is B. The value assigned to the CURRENT SCHEMA special register is not persistent across database restarts. Therefore, if you assign a value to the CURRENT SCHEMA special register, disconnect from the database, and reconnect, the CURRENT SCHEMA special register will contain your authentication ID - not the value you assigned it earlier. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is D. A view is a named specification of a result table that is populated whenever the view is referenced in an SQL statement. (Each time a view is referenced, a query is executed and the results are retrieved from the underlying table and returned in a table-like format.) Like base tables, views can be thought of as having columns and rows. And in most cases, data can be retrieved from a view the same way it can be retrieved from a table. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. If the WITH LOCAL CHECK OPTION clause of with the CREATE VIEW SQL statement is specified, insert and update operations performed against the view that is created are validated to ensure that all rows being inserted into or updated in the base table the view refers to conform to the view's definition (otherwise, the insert/update operation will fail). So what exactly does this mean? Suppose a view was created using the following CREATE VIEW statement: CREATE VIEW priority_ordersAS SELECT * FROM orders WHERE response_time < 4WITH LOCAL CHECK OPTIONNow, suppose a user tries to insert a record into this view that has a RESPONSE_TIME value of 6. The insert operation will fail because the record violates the view's definition. Had the view not been created with the WITH LOCAL CHECK OPTION clause, the insert operation would have been successful, even though the new record would not be visible to the view that was used to add it. Because VIEW1 was created using a SELECT statement that only references rows that have a value less than 100 in COL1 and because VIEW1 was created with the WITH LOCAL CHECK OPTION specified, each value inserted into COL1 (using VIEW1) must be less than 100. In addition, because COL2 was defined using a character data type, all values inserted into COL2 must be enclosed in single quotes. The INSERT statements shown in Answers B and D will fail because the value to be assigned to COL1 exceeds 100; the INSERT statement shown in Answer A will fail because the value "abc" is not enclosed in single quotation marks. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. Whenever an insert operation, an update operation, or a delete operation is performed against the subject table or view, a trigger can be activated (fired). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is D. Triggers are only fired if the trigger event they have been designed to watch for takes place against the subject table they are designed to interact with. In this example, no UPDATE trigger was defined; therefore, no triggers are fired when the sales table is updated. Trigger TRIGGER_C will be fired anytime a delete operation is performed against the SALES table and triggers TRIGGER_A and TRIGGER_B will be fired in the order they were created whenever an insert operation is performed against the SALES table. Trigger TRIGGER_A is designed to calculate a sales commission for an invoice based on the sale amount; trigger TRIGGER_B is designed to assign a value to the BILL_DATE column that is 30 days from today; and trigger TRIGGER_C is designed to display an error message whenever anyone tries to delete records from the SALES table. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is A. The XML data type can be used to store well-formed XML documents in their native format. A table can contain any number of XML columns; however each XML column used has the following restrictions: · It cannot be part of any index except an XML index. · It cannot be included as a column of a primary key or unique constraint. · It cannot be a foreign key of a referential constraint. · It cannot have a specified default value or a WITH DEFAULT clause-if the column is nullable, the default value for the column is the null value. · It cannot be used in a table with a distribution key. · It cannot be used in range-clustered or range-partitioned tables. In addition, XML columns can only be referenced in a check constraint if the check constraint contains the VALIDATED predicate. (The VALIDATED predicate checks to see if an XML value has been validated using the XMLVALIDATE() function. The XMLVALIDATE() function returns a copy of the input XML value, augmented with information obtained from XML schema validation, including default values and type annotations. If the value of the column is null, the result of the VALIDATED predicate is unknown; otherwise, the result is either TRUE or FALSE.) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is B. The SALES table in the example is partitioned such that each quarter's data is stored in a different data partition, and each partition resides in a different table space. Advantages of using table partitioning include: · Easy roll-in and roll-out of data. Rolling in partitioned table data allows a new range to be easily incorporated into a partitioned table as an additional data partition. Rolling out partitioned table data allows you to easily separate ranges of data from a partitioned table for subsequent purging or archiving. Data can be quickly rolled in and out by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement. · Easier administration of large tables. Table level administration becomes more flexible because administrative tasks can be performed on individual data partitions. Such tasks include: detaching and reattaching of a data partition, backing up and restoring individual data partitions, and reorganizing individual indexes. In addition, time consuming maintenance operations can be shortened by breaking them down into a series of smaller operations. For example, backup operations can be performed at the data partition level when the each data partition is placed in separate table space. Thus, it is possible to backup one data partition of a partitioned table at a time. · Flexible index placement. With table partitioning, indexes can be placed in different table spaces allowing for more granular control of index placement. · Better query processing. When resolving queries, one or more data partitions may be automatically eliminated, based on the query predicates used. This functionality, known as Data Partition Elimination, improves the performance of many decision support queries because less data has to be analyzed before a result data set can be returned. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| The correct answer is C. Unlike base tables, whose descriptions and constraints are stored in the system catalog tables of the database to which they belong, declared temporary tables are not persistent and can only be used by the application that creates them-and only for the life of the application. (Once the application that created the global temporary table is terminated, any records in the table are deleted and the table itself is destroyed.) When the application that creates a declared temporary table terminates, the rows of the table are deleted, and the definition of the table is dropped. (However, data stored in a temporary table can exist across transaction boundaries.) Another significant difference between the two centers around naming conventions: base table names must be unique within a schema, but because each application that defines a declared temporary table has its own instance of that table, it is possible for many applications to create declared temporary tables that have the same name. And where base tables are created with the CREATE TABLE SQL statement, declared temporary tables are created with the DECLARE GLOBAL TEMPORARY TABLE statement. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Working with DB2 Tables, Views, and Indexes
| The correct answer is D. NUMBER, INTERVAL, and BYTE are not valid DB2 data types. The terms DECIMAL, DEC, NUMERIC, and NUM are used to denote the decimal data type. The decimal data type is used to store numeric values that contain both whole and fractional parts, separated by a decimal point. The exact location of the decimal point is determined by the precision and the scale of the value (the scale is the number of digits used by the fractional part). The maximum precision allowed for decimal values is 31 digits, and the corresponding scale must be a positive number less than the precision of the number. If no precision or scale is specified, a scale of 5 and a precision of 0 is used by default - DECIMAL(5,0). | |
| The correct answer is C. The XML data type is used to store XML documents in their native format. The amount of storage space set aside to store an XML document varies and is determined in part, by the size and characteristics of the XML document being stored. The integer data type is used to store numeric values that have a precision of 10 digits. The range for integer values is -2,147,483,648 to 2,147,483,647, and 4 bytes of storage space is required for every integer value stored. The terms INTEGER and INT are used to denote the integer data type. The fixed-length character string data type is used to store character string values that are between 1 and 254 characters in length. The amount of storage space needed to store a fixed-length character string value can be determined by solving the following equation: (Number of characters x 1) = Bytes required. (A fixed amount of storage space is allocated, even if all of the space allocated is not needed-short strings are padded with blanks.) The terms CHARACTER and CHAR are used to denote the fixed-length character string data type. The double-precision floating-point data type is used to store a 64-bit approximation of a real number. This number can be zero, or it can fall within the range -1.79769E+308 to -2.225E-307 or 2.225E-307 to 1.79769E+308. Each double-precision floating-point value can be up to 53 digits in length, and 8 bytes of storage space is required for every value stored. The terms DOUBLE, DOUBLE PRECISION, and FLOAT are used to denote the double-precision floating-point data type. | |
| | |
| The correct answer is A. The decimal data type is used to store numeric values that contain both whole and fractional parts, separated by a decimal point. The terms DECIMAL, DEC, NUMERIC, and NUM are used to denote the decimal data type. Since currency values contain both whole and fractional parts, the decimal data type is the best choice to base a user-defined data type on. And to create a distinct data type named CURRENCY that can be used to store numeric data you would execute a CREATE DISTINCT TYPE SQL statement that looks like the one shown in Answer A. | |
| The correct answer is B. The character large object (CLOB) data type is used to store single-byte character data. The binary large object (BLOB) data type is used to store binary data; the double-byte character large object (DBCLOB) data type is used to store double-byte character data; and the fixed-length double-byte character string (GRAPHIC) data type is used to store double-byte character data strings. | |
| The correct answer is D. The data type assigned to an identity column must be a numeric data type with a scale of 0; therefore, the only data types that can be assigned to an identity column are: SMALLINT, INTEGER, BIGINT, DECIMAL/NUMERIC, or a user-defined data type that is based on one of these data types. | |
| The correct answer is B. A date value can be stored using a date (DATE), timestamp (TIMESTAMP), or character string (CHAR) data type. The time (TIME) data type, on the other hand, can only be used to store a time value. | |
| The correct answer is A. Although each data type specified is valid, the CHAR(15) data type will only require 16 bytes of storage whereas the VARCHAR(15) data type will need 20 bytes of storage, the LONG VARCHAR data type will need 40 bytes, and the CLOB(1K) data type will require over 1024 bytes of storage space. | |
| The correct answer is C. The XMLPARSE function parses a character string and returns an XML value; the character string expression to be parsed must evaluate to a well-formed XML document that conforms to XML 1.0, as modified by the XML Namespaces recommendation. Answers A, B, and D are character strings that are comprised of a starting tag, an optional value, and a corresponding ending tag. As a result, these strings can be converted into a well-formed, but small, XML documents. | |
| The correct answers are C and E. At a minimum, when a new table is created, a table name, one or more column names, and corresponding column data types must be specified. Primary keys, constraints (NOT NULL, default, check, unique, referential integrity, and informational), and table space information is optional and is not required. | |
| The correct answer is C. A trigger is used to produce an action as a result of a change to a table. Views provide users with alternate ways to see table data. And because a view can reference the data stored in any number of columns found in the base table it refers to, views can be used, together with view privileges, to control what data a user can and cannot see. Furthermore, if a view is created with the WITH [LOCAL | CASCADED] CHECK OPTION specified, it can be used to ensure that all rows added to a table through it conform to its definition. | |
| The correct answers are D and E. When the CREATE TABLE … LIKE … statement is executed, each column of the table that is created will have exactly the same name, data type and nullability characteristic as the columns of the source table used to create the new table. Furthermore, if the EXCLUDING COLUMN DEFAULTS option is not specified (which is the case in this example), all column defaults will be copied as well. However, the new table will not contain any unique constraints, foreign key constraints, triggers, or indexes that exist in the original. | |
| The correct answer is A. The Insert Rule for a referential constraint guarantees that a value can never be inserted into the foreign key of a child table unless a matching value can be found in the corresponding parent key of the associated parent table. Any attempt to insert records into a child table that violates this rule will result in an error, and the insert operation will fail. The Insert Rule for a referential constraint is implicitly created when the referential constraint itself is created. In this example, the EMPID column of table TAB2 is a foreign key (in a child table) that references the ID column (the parent key) of table TAB1 (the parent table). Therefore, because of the Insert Rule, values cannot be added to the EMPID column of table TAB2 that do not already exist in the ID column of table TAB1. | |
| The correct answer is D. A unique index, a check constraint, and a referential constraint place restrictions on what can and cannot be stored in the column(s) they are associated with. A default constraint, however, is used to provide a default value for a particular column if no data is provided for that column when data is inserted into a table; if a value is provided for the column, the default value is ignored. | |
| The correct answer is C. A check constraint is used to ensure that a particular column in a base table is never assigned an unacceptable value-once a check constraint has been defined for a column, any operation that attempts to place a value in that column that does not meet specific criteria will fail. Check constraints are comprised of one or more predicates that collectively are known as the check condition. This check condition is compared with the data value provided and the result of this comparison is returned as the value TRUE, FALSE, or Unknown. If the check constraint returns the value TRUE, the value is acceptable, so it is added to the database. If, on the other hand, the check constraint returns the value FALSE or Unknown, the operation attempting to place the value in the database fails, and all changes made by that operation are backed out. In this example, the check constraint CST1 defined for table TAB1 only allows the values 1, 2, or 3 to be entered into column COL1. The INSERT statement shown in Answer C is the only INSERT statement that has a valid value specified for column COL1. | |
| The correct answers are C and E. When a unique index is created for a column, every value found in that column must be unique, and one of the column's unique values can be the null value. | |
| The correct answer is A. The ON DELETE RESTRICT ensures that whenever a delete operation is performed on the parent table of a referential constraint, the value for the foreign key of each row in the child table will have the same matching value in the parent key of the parent table that it had before the delete operation was performed. Therefore, in this example no row will be deleted from the MAKE because two rows exist in the MODEL table that references the row the DELETE statement is trying to remove. Had the ON DELETE CASCADE definition been used instead, the delete operation would have succeeded and the tables would have looked like this: MAKEMAKEID MAKE------ ----------2 Chevrolet3 ToyotaMODELMODELID MODEL MAKEID----- ------- -------3 Malibu 24 Camry 3On the other hand, if the ON DELETE SET NULL definition had been used, the delete operation would have succeeded and the tables would have looked like this: MAKEMAKEID MAKE---- ----------2 Chevrolet3 Toyota MODELMODELID MODEL MAKEID------ -------- ------1 Mustang _2 Escort _3 Malibu 24 Camry 3 | |
| The correct answer is C. The check constraint (CONST1) for TABLEA will only allow the values 1, 2, or 3 to be entered into column COL1. The NOT NULL constraint prohibits null values, the value 0 is not a valid value, and the value '1' is a character value (the column COL1 was defined using a numeric data type). | |
| The correct answer is D. The ON DELETE RESTRICT delete rule and the ON DELETE NO ACTION delete rule prevent the deletion of parent rows in a parent table if dependent rows that reference the primary row being deleted exist in the corresponding child table, and the ON DELETE SET NO VALUE delete rule is an invalid rule. On the other hand, the ON DELETE CASCADE delete rule will allow rows in the parent table to be deleted; if dependent rows that reference the primary row being deleted exist in the corresponding child table, they will be deleted as well. | |
| The correct answer is D. A unique index allows one and only one NULL value; the value "NULL" means a column's value is undefined and distinct from any other value. The remaining characteristics are true for unique indexes. | |
| The correct answer is D. In this example, the statement "INSERT INTO employee VALUES (DEFAULT, 'Smith', 10, '11/18/2004', 60250.00)" will fail because the hire date and the salary specified violates check constraint CST1 - YEAR(hiredate) > 2006 OR salary > 60500). | |
| The correct answer is B. A unique constraint can be used to ensure that the value(s) assigned to one or more columns when a record is added to a base table are always unique; once a unique constraint has been defined for one or more columns, any operation that attempts to place duplicate values in those columns will fail. Although a unique, system-required index is used to enforce a unique constraint, there is a distinction between defining a unique constraint and creating a unique index; even though both enforce uniqueness, a unique index allows NULL values and generally cannot be used in a referential constraint. A unique constraint on the other hand, does not allow NULL values and can be referenced in a foreign key specification. (The value "NULL" means a column's value is undefined and distinct from any other value, including other NULL values). A check constraint (also known as a table check constraint) can be used to ensure that a particular column in a base table is never assigned an unacceptable value-once a check constraint has been defined for a column, any operation that attempts to place a value in that column that does not meet specific criteria will fail. The default constraint can be used to ensure that a particular column in a base table is assigned a predefined value (unless that value is overridden) each time a record is added to the table. The predefined value provided could be null (if the NOT NULL constraint has not been defined for the column), a user-supplied value compatible with the column's data type, or a value furnished by the DB2 Database Manager. Unlike other constraints, informational constraints are not enforced during insert and update processing. However, the DB2 SQL Optimizer will evaluate information provided by an informational constraint when considering the best access plan to use to resolve a query. As a result, an informational constraint may result in better query performance even though the constraint itself will not be used to validate data entry/modification. | |
| The correct answer is D. The decimal (DECIMAL or NUMERIC) data type is used to hold the number-the precision is 10 because 10 numbers will be displayed and the scale is 3 because the number contains three decimal places. | |
| The correct answer is B. Because column COL2 was defined using a varying-length character string (VARCHAR) data type, the default value provided for the default constraint must be enclosed in single quotes. Had the value 'NONE' been provided instead of the value NONE, the column COL2 would have been created. Instead, because column COL2 could not be created, the table TAB1 was not created. | |
| The correct answer is C. To create a referential constraint, you define a primary key, using one or more columns in the parent table, and you define a 5foreign key for one or more corresponding columns in the child table that reference the parent table's primary key. (The list of column names in the foreign key clause must be identical to the list of column names specified in the primary key OR a unique constraint for the columns in the parent table that are referenced by the foreign key in the child must exist in order for a referential constraint to be successfully created.) | |
| The correct answer is B. The value assigned to the CURRENT SCHEMA special register is not persistent across database restarts. Therefore, if you assign a value to the CURRENT SCHEMA special register, disconnect from the database, and reconnect, the CURRENT SCHEMA special register will contain your authentication ID - not the value you assigned it earlier. | |
| The correct answer is A. Since no unique or primary constraints were included in the table definition, no unique indexes are created. | |
| The correct answer is D. A view is a named specification of a result table that is populated whenever the view is referenced in an SQL statement. (Each time a view is referenced, a query is executed and the results are retrieved from the underlying table and returned in a table-like format.) Like base tables, views can be thought of as having columns and rows. And in most cases, data can be retrieved from a view the same way it can be retrieved from a table. | |
| The correct answer is C. If the WITH LOCAL CHECK OPTION clause of with the CREATE VIEW SQL statement is specified, insert and update operations performed against the view that is created are validated to ensure that all rows being inserted into or updated in the base table the view refers to conform to the view's definition (otherwise, the insert/update operation will fail). So what exactly does this mean? Suppose a view was created using the following CREATE VIEW statement: CREATE VIEW priority_ordersAS SELECT * FROM orders WHERE response_time < 4WITH LOCAL CHECK OPTIONNow, suppose a user tries to insert a record into this view that has a RESPONSE_TIME value of 6. The insert operation will fail because the record violates the view's definition. Had the view not been created with the WITH LOCAL CHECK OPTION clause, the insert operation would have been successful, even though the new record would not be visible to the view that was used to add it. Because VIEW1 was created using a SELECT statement that only references rows that have a value less than 100 in COL1 and because VIEW1 was created with the WITH LOCAL CHECK OPTION specified, each value inserted into COL1 (using VIEW1) must be less than 100. In addition, because COL2 was defined using a character data type, all values inserted into COL2 must be enclosed in single quotes. The INSERT statements shown in Answers B and D will fail because the value to be assigned to COL1 exceeds 100; the INSERT statement shown in Answer A will fail because the value "abc" is not enclosed in single quotation marks. | |
| The correct answer is A. The ON DELETE NO ACTION definition ensures that whenever a delete operation is performed on the parent table in a referential constraint, the value for the foreign key of each row in the child table will have a matching value in the parent key of the parent table (after all other referential constraints have been applied). Therefore, no row will be deleted from TABLEA because a row exists in TABLEB that references the row the DELETE statement is trying to remove. And because the ON DELETE CASCADE definition was not used, no row will be deleted from TABLEB. | |
| The correct answer is C. Whenever an insert operation, an update operation, or a delete operation is performed against the subject table or view, a trigger can be activated (fired). | |
| The correct answer is D. Triggers are only fired if the trigger event they have been designed to watch for takes place against the subject table they are designed to interact with. In this example, no UPDATE trigger was defined; therefore, no triggers are fired when the sales table is updated. Trigger TRIGGER_C will be fired anytime a delete operation is performed against the SALES table and triggers TRIGGER_A and TRIGGER_B will be fired in the order they were created whenever an insert operation is performed against the SALES table. Trigger TRIGGER_A is designed to calculate a sales commission for an invoice based on the sale amount; trigger TRIGGER_B is designed to assign a value to the BILL_DATE column that is 30 days from today; and trigger TRIGGER_C is designed to display an error message whenever anyone tries to delete records from the SALES table. | |
| The correct answer is B. Both primary keys and unique indexes can be defined over one or more columns in a table. | |
| The correct answer is A. The XML data type can be used to store well-formed XML documents in their native format. A table can contain any number of XML columns; however each XML column used has the following restrictions: · It cannot be part of any index except an XML index. · It cannot be included as a column of a primary key or unique constraint. · It cannot be a foreign key of a referential constraint. · It cannot have a specified default value or a WITH DEFAULT clause-if the column is nullable, the default value for the column is the null value. · It cannot be used in a table with a distribution key. · It cannot be used in range-clustered or range-partitioned tables. In addition, XML columns can only be referenced in a check constraint if the check constraint contains the VALIDATED predicate. (The VALIDATED predicate checks to see if an XML value has been validated using the XMLVALIDATE() function. The XMLVALIDATE() function returns a copy of the input XML value, augmented with information obtained from XML schema validation, including default values and type annotations. If the value of the column is null, the result of the VALIDATED predicate is unknown; otherwise, the result is either TRUE or FALSE.) | |
| The correct answer is B. The SALES table in the example is partitioned such that each quarter's data is stored in a different data partition, and each partition resides in a different table space. Advantages of using table partitioning include: · Easy roll-in and roll-out of data. Rolling in partitioned table data allows a new range to be easily incorporated into a partitioned table as an additional data partition. Rolling out partitioned table data allows you to easily separate ranges of data from a partitioned table for subsequent purging or archiving. Data can be quickly rolled in and out by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement. · Easier administration of large tables. Table level administration becomes more flexible because administrative tasks can be performed on individual data partitions. Such tasks include: detaching and reattaching of a data partition, backing up and restoring individual data partitions, and reorganizing individual indexes. In addition, time consuming maintenance operations can be shortened by breaking them down into a series of smaller operations. For example, backup operations can be performed at the data partition level when the each data partition is placed in separate table space. Thus, it is possible to backup one data partition of a partitioned table at a time. · Flexible index placement. With table partitioning, indexes can be placed in different table spaces allowing for more granular control of index placement. · Better query processing. When resolving queries, one or more data partitions may be automatically eliminated, based on the query predicates used. This functionality, known as Data Partition Elimination, improves the performance of many decision support queries because less data has to be analyzed before a result data set can be returned. | |
| The correct answer is C. Unlike base tables, whose descriptions and constraints are stored in the system catalog tables of the database to which they belong, declared temporary tables are not persistent and can only be used by the application that creates them-and only for the life of the application. (Once the application that created the global temporary table is terminated, any records in the table are deleted and the table itself is destroyed.) When the application that creates a declared temporary table terminates, the rows of the table are deleted, and the definition of the table is dropped. (However, data stored in a temporary table can exist across transaction boundaries.) Another significant difference between the two centers around naming conventions: Base table names must be unique within a schema, but because each application that defines a declared temporary table has its own instance of that table, it is possible for many applications to create declared temporary tables that have the same name. And where base tables are created with the CREATE TABLE SQL statement, declared temporary tables are created with the DECLARE GLOBAL TEMPORARY TABLE statement. |
Data Concurrency
| The correct answer is D. The Repeatable Read isolation level will lock all rows scanned in response to a query. (The Read Stability isolation level will only lock the rows returned in the result data set; the Cursor Stability isolation level will only lock the row in the result data set that the cursor is currently pointing to; and the Uncommitted Read isolation level will not lock any rows during normal read processing.) | |
| The correct answer is A. If Application B did not already have an Exclusive lock on table TAB2, Application B would be placed in a lock-wait state until Application A released its locks. However, because Application B holds an Exclusive lock on table TAB2, when Application A tries to acquire an Exclusive lock on table TAB2 and Application B tries to acquire an Exclusive lock on table TAB1, a deadlock will occur - processing by both transactions will be suspended until their second lock request is granted. Because neither lock request can be granted until one of the owning transactions releases the lock it currently holds (by performing a commit or rollback operation), and because neither transaction can perform a commit or rollback operation because they both have been suspended (and are waiting on locks), a deadlock has occurred. | |
| The correct answer is C. When the Read Stability isolation level is used by a transaction that executes a query, locks are acquired on all rows returned to the result data set produced, and other transactions cannot modify or delete the locked rows; however, they can add new rows to the table that meet the query's search criteria. If that happens, and the query is run again, these new rows will appear in the new result data set produced. | |
| The correct answer is A. When a deadlock cycle occurs, all transactions involved will wait indefinitely for a lock to be released unless some outside agent steps in and breaks the cycle. With DB2, this agent is a background process, known as the deadlock detector, and its sole responsibility is to locate and resolve any deadlocks found in the locking subsystem. Each database has its own deadlock detector, which is activated as part of the database initialization process. Once activated, the deadlock detector stays "asleep" most of the time but "wakes up" at preset intervals and examines the locking subsystem to determine whether a deadlock situation exists. If the deadlock detector discovers a deadlock cycle, it randomly selects one of the transactions involved to roll back and terminate; the transaction chosen (referred to as the victim process) is then sent an SQL error code, and every lock it had acquired is released. The remaining transaction(s) can then proceed, because the deadlock cycle has been broken. | |
| The correct answer D. Typically, locks are not acquired during processing when the Uncommitted Read isolation level is used. Therefore, if Application B runs under this isolation level, it will be able to retrieve data from table TAB1 immediately - lock compatibility is not an issue that will cause Application B to wait for a lock. | |
| The correct answer is D. Usually locks are not acquired during processing when the Uncommitted Read isolation level is used. However, rows that are retrieved by a transaction using the Uncommitted Read isolation level will be locked if another transaction attempts to drop or alter the table from which the rows were retrieved. | |
| The correct answer is A. Any time one transaction holds a lock on a data resource and another transaction attempts to acquire a lock on the same resource, the DB2 Database Manager will examine each lock's state and determine whether they are compatible. If the state of a lock placed on a data resource by one transaction is such that another lock can be placed on the same resource by another transaction before the first lock acquired is released, the locks are said to be compatible and the second lock will be acquired. However, if the locks are not compatible, the transaction requesting the incompatible lock must wait until the transaction holding the first lock is terminated before it can acquire the lock it needs. If the requested lock is not acquired before the time interval specified in the locktimeout configuration parameter has elapsed, the waiting transaction receives an error message and is rolled back. | |
| The correct answer is B. Locks can only be acquired for table spaces, tables, and rows. | |
| The correct answer is A. If a row level lock is held by a application using the Cursor Stability isolation level, that lock remains in effect until either the cursor is moved to a new row (at which time the lock for the old row is released - if possible, and a new lock for the current row is acquired) or the transaction holding the lock is terminated. | |
| The correct answer is C. The LOCK TABLE statement allows a transaction to explicitly acquire a table-level lock on a particular table in one of two modes: SHARE and EXCLUSIVE. If a table is locked using the SHARE mode, a table-level Share (S) lock is acquired on behalf of the transaction, and other concurrent transactions are allowed to read, but not change, the data stored in the locked table. If a table is locked using the EXCLUSIVE mode, a table-level Exclusive (X) lock is acquired, and other concurrent transactions can neither access nor modify data stored in the locked table. | |
| The correct answer is C. If a transaction holding a lock on a resource needs to acquire a more restrictive lock on the same resource, the DB2 Database Manager will attempt to change the state of the existing lock to the more restrictive state. The action of changing the state of an existing lock to a more restrictive state is known as lock conversion. Lock conversion occurs because a transaction can hold only one lock on a specific data resource at any given time. In most cases, lock conversion is performed on row-level locks, and the conversion process is fairly straightforward. For example, if an Update (U) lock is held and an Exclusive (X) lock is needed, the Update (U) lock will be converted to an Exclusive (X) lock. | |
| The correct answer is A. When the Repeatable Read isolation level is used, the effects of one transaction are completely isolated from the effects of other concurrent transactions; when this isolation level is used, every row that's referenced in any manner by the owning transaction is locked for the duration of that transaction. As a result, if the same SELECT SQL statement is issued multiple times within the same transaction, the result data sets produced are guaranteed to be the identical. Other transaction are prohibited from performing insert, update, or delete operations that would affect any row that has been accessed by the owning transaction as long as that transaction remains active. | |
| The correct answer is D. When the Uncommitted Read isolation level is used, rows retrieved by a transaction are only locked if the transaction modifies data associated with one or more rows retrieved or if another transaction attempts to drop or alter the table the rows were retrieved from.) As the name implies, transactions running under the uncommitted read isolation level can see changes made to rows by other transactions before those changes have been committed. On the other hand, transactions running under the Repeatable Read, Read Stability, or Cursor Stability isolation level are prohibited from seeing uncommitted data. Therefore, applications running under the Uncommitted Read isolation level can read the row Application A updated while applications running under a different isolation level cannot. Because no locks are needed in order for Application A to read data stored in other tables, it can do so - even if a restrictive lock is held on that table. However, there is nothing that prohibits Application A from performing an insert operation from within the open transaction. | |
| The correct answer is C. If the Repeatable Read isolation level is used, other agents will be unable to assign seats as long as the transaction that generated the list remains active; therefore, the list will not change when it is refreshed. If the Read Stability isolation level is used, other agents will be able to unassign currently assigned seats (and these unassigned seats will show up when the list is refreshed), but they will not be able to assign any seat that appears in the list as long as the transaction that generated the list remains active. If the Uncommitted Read isolation level is used, other agents will be able to unassign currently assigned seats, as well as assign unassigned seats; however, uncommitted seat unassignments/assignments will show up when the list is refreshed, and the agent may make an inappropriate change based on this data. Therefore, the best isolation level to use for this particular application is the Cursor Stability isolation level. |
No comments:
Post a Comment