| A1: | C. Since no schema is explicitly specified, the schema will be the authorization ID for the connection. The authorization ID, also known as the connection ID, is the user ID used to connect to the database. Therefore, the table name will be foo.t1. |
| A2: | B. There is no object named IBMDEFAULTSPACE. When a database is created, DB2 creates the table spaces SYSCATSPACE, USERSPACE1, and TEMPSPACE1; the partition groups IBMCATGROUP, IBMTEMPGROUP, and IBMDEFAULTGROUP; and the buffer pool IBMDEFAULTBP. |
| A3: | C. An identity column ensures uniqueness, but only within a table. A sequence generates a unique value that is across the whole database and can therefore be used with multiple tables. |
| A4: | D. The statement alter table t1 activate not logged initially with empty table will delete all rows from the table and not log the deletions for the fastest response time. Table t1 must have been created with the activate not logged initially clause for the alter table to work. |
| A5: | B. While you can enforce this by specifying the T or F constraint by creating a view (if all inserts are done using a view), this will not prevent you from inserting a different value if you insert into the table directly. To ensure that no other values are entered into this column, you need to define a check constraint. |
| A6: | B. The CASCADE option will delete all referenced rows in the child tables. |
| A7: | B and D. Both a unique constraint and a primary key constraint can be referenced by a foreign key constraint. |
| A8: | C. The statement inserting a value of 300 into the productno column will fail as this is an identity column GENERATED ALWAYS, so there will be two rows successfully inserted into the table. |
| A9: | C. The default precision and scale are 5, 0 if not explicitly specified in the CREATE TABLE statement. |
| A10: | A. INBETWEEN is not a type of trigger. |
| A11: | C. Each database has its own set of logs and system catalogs, and a schema is created within a database. However, the registry variables are set for all instances or at the instance level and control all databases in the instance. |
| A12: | B. The CREATE TABLE statement creates the table/data object for the database. Since there is a primary key clause in this statement, an index will also be created to support the primary key. This statement creates two objects. |
| A13: | B. There are two server names in the db2nodes.cfg file; therefore, the database is partitioned across two servers. |
| A14: | A. Data objects can be created in regular table spaces only. |
| A15: | D. You need to connect to the database and create the buffer pool with a 16K page size before you can create a table space with a 16K page size. |
| A16: | A. Only a table can be altered using the ALTER TABLE statement. You must drop and recreate an index, view, or schema to change its definition. |
| A17: | A and B. A package is stored in a database and contains the executable SQL statements and the access path the DB2 optimizer will choose to retrieve the data. |
| A18: | D. Object names must be unique within a schema, but since the schema is the high-level qualifier for the object name, objects can have the same name as long as they are in different schemas. |
| A19: | B and C. seq1.next and seq1.nextValue are not valid options. |
| A20: | D. A user temporary table space created with the CREATE USER TEMPORARY TABLESPACE statement is needed before global temporary tables can be declared. Global temporary tables are created with the DECLARE GLOBAL TEMPORARY TABLE statement. |
