Ejemplo_1_3

Question 1 of 138

1

If the following command is executed: CREATE DATABASE test What is the page size (in kilobytes) of
the database?

Select one or more of the following:

4
8
16
32

Explanation

Question 2 of 138

1

Given the following DDL statement: CREATE TABLE sales (item CHAR(20)); If a DBA wishes to
increase the size of the ITEM column, which SQL statement needs to be used?

Select one or more of the following:

ALTER TABLE sales ADD COLUMN item CHAR(40);
ALTER TABLE sales ALTER COLUMN item CHAR(40);
ALTER TABLE sales MODIFY COLUMN item CHAR(40);
ALTER TABLE sales ALTER COLUMN item SET DATA TYPE CHAR(40);

Explanation

Question 3 of 138

1

Which DB2 object can be used to improve the execution performance of qualified SELECT statements?

Select one or more of the following:

Trigger
SQL Procedure
Sequence Object
Materialized Query Table

Explanation

Question 4 of 138

1

When is it appropriate to use a sequence?

Select one or more of the following:

When you want to control the order in which triggers are fired.
When you want to control the order in which stored procedures can be invoked.
When you want to automatically generate a numeric value that is not tied to any specific column or table.
When you want to automatically generate a numeric value for each row that is added to a specific table.

Explanation

Question 5 of 138

1

5.Which clause should be included in a ALTER TABLE statement to update a table definition with a new
column?

Select one or more of the following:

ADD COLUMN
ALTER COLUMN
APPEND COLUMN
RENAME COLUMN

Explanation

Question 6 of 138

1

Which object is a stored procedure defined into?

Select one or more of the following:

Table
Schema
Package
Database

Explanation

Question 7 of 138

1

Which action needs to be performed in order to complete the definition of an application-period temporal
table?

Select one or more of the following:

A transaction-start-id column must be defined for the table.
A history table must be defined and associated with the base table.
A BUSINESS_TIME period must be specified in a CREATE or ALTER of the table.
A unique index must be created that prevents overlapping of the BUSINESS_TIME period of the table.

Explanation

Question 8 of 138

1

What functionality allows users to perform a UNION operation between a DB2 table and an Oracle
view?

Select one or more of the following:

Oracle connect
Trusted context
Oracle federation
Distributed request

Explanation

Question 9 of 138

1

You have a business need to query DB2 10 and DB2 9 databases and you want to write an application
that can run on most platforms unchanged. Which interface would you use to achieve your goal?

Select one or more of the following:

CLI
XML
JDBC
RUBY

Explanation

Question 10 of 138

1

Which DB2 object is used to provide a logical grouping of other database objects?

Select one or more of the following:

Alias
Index
Schema
Collection

Explanation

Question 11 of 138

1

What are two valid objects that can be created in DB2? (Choose two.)

Select one or more of the following:

Tree
Node
Trigger
Contexts
Sequence

Explanation

Question 12 of 138

1

Which type of temporal table can be used to store only time-sensitive data?

Select one or more of the following:

Bitemporal
Time-period
System-period
Application-period

Explanation

Question 13 of 138

1

When an index is created for a table, where is the metadata for that index stored?

Select one or more of the following:

In the table definition
In the system catalog.
In the schema that the index resides in.
In the metadata of the table the index was created for.

Explanation

Question 14 of 138

1

Which product is used to customize execution environments for the purpose of controlling system
resources so that one department or service class does not overwhelm the system?

Select one or more of the following:

pureScale
Workload manager
Data partitioning feature
Self-tuning memory manager

Explanation

Question 15 of 138

1

What type of mechanism is a simple token value that is used to refer to a much bigger large object
(LOB)?

Select one or more of the following:

locator
pointer
address
reference

Explanation

Question 16 of 138

1

What type of large object (LOB) is used to store LOB data together with the formatted rows on data
pages, instead of in a separate LOB storage object?

Select one or more of the following:

inline
binary
internal
partitioned

Explanation

Question 17 of 138

1

What is the purpose of the Query Tuner?

Select one or more of the following:

To automatically capture and stop rogue queries.
To provide recommendations and analysis for tuning a single query.
To provide recommendations and analysis for tuning up to 100 queries.
To recommend indexes and to guide DBAs through the process of creating new indexes.

Explanation

Question 18 of 138

1

Which tool allows users to connect to a DB2 database using a wizard?

Select one or more of the following:

Control Center
IBM Data Studio
Universal Connection Expert
DB2 Connection Pool Manager

Explanation

Question 19 of 138

1

Which two activities indicate the need for a data warehouse? (Choose two.)

Select one or more of the following:

Confirm product inventory.
Monitor product availability.
Summarize sales by region.
Identify patterns for products sold in the last five years.
Associate one or more products with a purchase order.

Explanation

Question 20 of 138

1

What is the primary function of an Online Transaction Processing (OLTP) workload?

Select one or more of the following:

To combine data from multiple sources.
To discover hidden relationships in data.
To analyze large amounts of data to find patterns.
To make changes to a small number of records within a single transaction.

Explanation

Question 21 of 138

1

Which isolation level offers the greatest protection of data but provides the least amount of
concurrency?

Select one or more of the following:

Read Stability (RS)
Cursor Stability (CS)
Repeatable Read (RR)
Uncommitted Read (UR)

Explanation

Question 22 of 138

1

What factor influences lock escalation?

Select one or more of the following:

Table size
Buffer space
Number of locks
Available real storage

Explanation

Question 23 of 138

1

Which type of lock allows the lock owner and all concurrent applications to read, but not update, the
locked data?

Select one or more of the following:

Share (S)
Update (U)
Exclusive (X)
Intent Exclusive (IX)

Explanation

Question 24 of 138

1

Application APP_A is performing updates to table TAB1 using the cursor stability (CS) isolation level. If
application APP_B wants to retrieve all rows from table TAB1 without waiting for application APP_A to
finish making updates, what isolation level must application APP_B use?

Select one or more of the following:

Read Stability (RS)
Repeatable Read (RR)
Uncommitted Read (UR)
Cursor Stability (CS)

Explanation

Question 25 of 138

1

Which statement will prevent concurrent application processes from performing anything other than
read-only operations against a table named TAB1?

Select one or more of the following:

LOCK TABLE tab1 IN READ MODE
LOCK TABLE tab1 IN SHARE MODE
LOCK TABLE tab1 IN EXCLUSIVE MODE
LOCK TABLE tab1 IN READ-ONLY MODE

Explanation

Question 26 of 138

1

When is an INTENT EXCLUSIVE (IX) lock required?

Select one or more of the following:

When a transaction intends to read or change data.
When a transaction intends to change but not read data.
When a transaction intends to read but not change data.
When a transaction intends to change the system catalog.

Explanation

Question 27 of 138

1

What is the act of exchanging one lock an application holds on a resource for a more restrictive lock on
the same resource known as?

Select one or more of the following:

Lock escalation
Lock substitution
Lock switch/exchange
Lock conversion/promotion

Explanation

Question 28 of 138

1

What isolation level prevents dirty reads, nonrepeatable reads, and phantoms?

Select one or more of the following:

Read stability (RS)
Cursor stability (CS)
Repeatable read (RR)
Uncommitted read (UR)

Explanation

Question 29 of 138

1

Which operation normally does NOT require an exclusive lock?

Select one or more of the following:

BIND
DROP
GRANT
SELECT

Explanation

Question 30 of 138

1

When a COMMIT statement is executed, what happens?

Select one or more of the following:

All locks held on the database are automatically released.
Data stored in global temporary tables is automatically deleted.
Open cursors defined WITH HOLD are closed, but their data is retained.
The current transaction is terminated and a new transaction boundary is started.

Explanation

Question 31 of 138

1

Which command is used to back out a subset of database changes that have been made within a unit
of work?

Select one or more of the following:

COMMIT
ROLLBACK
COMMIT TO SAVEPOINT
ROLLBACK TO SAVEPOINT

Explanation

Question 32 of 138

1

An SQL function designed to convert temperatures from Fahrenheit to Celsius was created as follows:
CREATE FUNCTION conv_temp (IN temp_f FLOAT
RETURNS INTEGER
NO EXTERNALACTION
SPECIFIC convert_ftoc
RETURN INT ((temp_f -32)/1.8)
How can this function be used to convert average temperature (AVG_TEMP) data stored in a table called
CLIMATE_INFO?

Select one or more of the following:

CALL conv_temp(climate_info.avg_temp);
CALL convert_ftoc(climate_info.avg_temp);
SELECT conv_temp(avg_temp) FROM climate_info;
SELECT convert_ftoc(avg_temp) FROM climate_info;

Explanation

Question 33 of 138

1

If a table named MY_TAB contains 100 rows and the following statement is executed:
DELETE FROM (SELECT * FROM my_tab ORDER BY col1 DESC
FETCH FIRST 5 ROWS ONLY) AS tmp;

What will happen?

Select one or more of the following:

The last 5 rows in the table will be deleted.
The first 5 rows in the table will be deleted.
The statement will fail because a subquery cannot be used with a DELETE statement.
The statement will fail because a table name was not specified with the DELETE statement.

Explanation

Question 34 of 138

1

Given an EMPLOYEES table and a SALES table, a user wants to produce a list of all employees and
their associated revenue, even if no revenue exists. Which SQL statement will produce the desired list?

Select one or more of the following:

SELECT employees.name, sales.revenue FROM employees INNER JOIN sales ON employees.id =
sales.emp_id
SELECT employees.name, sales.revenue FROM employees INNER JOIN sales ON sales.emp_id =
employees.id
SELECT employees.name, sales.revenue FROM sales LEFT OUTER JOIN employees ON
employees.id = sales.emp_id
SELECT employees.name, sales.revenue FROM sales RIGHT OUTER JOIN employees ON
employees.id = sales.emp_id

Explanation

Question 35 of 138

1

Which two operations are allowed in the body of an SQL scalar user-defined function? (Choose two.)

Select one or more of the following:

CALL statements.
External file access.
Use of a scratch pad.
COMMIT statements.
SQL control statements.

Explanation

Question 36 of 138

1

Which command will delete all rows from a table without generating log records?

Select one or more of the following:

TRIM
DROP
DELETE
TRUNCATE

Explanation

Question 37 of 138

1

Which function can be used to obtain values from XML documents that are to be inserted into one or
more tables?

Select one or more of the following:

XMLTABLE
XMLPARSE
XMLEXISTS
XMLATTRIBUTES

Explanation

Question 38 of 138

1

User USER1 wants to retrieve records from a table named EMPLOYEE that satisfy at least one of the
following criteria:
-The employee's hire date (HIREDATE) is before 1999 and the employee's salary (SALARY) is less than
$40,000.00 a year. -The employee has attended university
Which SQL statement will accomplish this?

Select one or more of the following:

SELECT * FROM employee WHERE (hiredate < '1999-01-01' AND salary < 40000) OR (education ='University')
SELECT * FROM employee WHERE (hiredate < '1999-01-01') OR (salary < 40000) OR (education ='University')
SELECT * FROM employee WHERE (hiredate < '1999-01-01' OR (salary < 40000 AND (education ='University')
SELECT * FROM employee WHERE (hiredate < '1999-01-01' AND salary < 40000 AND (education ='University')

Explanation

Question 39 of 138

1

Which SQL statement will retrieve the employee number (EMPNO), hire date (HIREDATE), and salary
(SALARY) for each employee from a table named EMPLOYEE who was hired before 1998 and earns a
salary of less than $35,000.00 per year?

Select one or more of the following:

SELECT empno, hiredate, salary FROM employee FOR hiredate < '1998-01-01' AND salary < 35000
SELECT empno, hiredate, salary FROM employee WHERE hiredate < '1998-01-01' AND salary <
35000
SELECT empno, hiredate, salary FROM employee WHERE hiredate < '1998-01-01' OR salary <
35000
SELECT empno, hiredate, salary FROM employee FOR hiredate < '1998-01-01' OR salary < 35000

Explanation

Question 40 of 138

1

If the following result set is desired:

Which SQL statement must be executed?

Select one or more of the following:

SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 FETCH FIRST 5 ROWS
ONLY
SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 DESC FETCH FIRST 5
ROWS ONLY
SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 FETCH FIRST 5 ROWS
SELECT lastname, firstnme, salary, job FROM employee ORDER BY 3 DESC FETCH FIRST 5
ROWS

Explanation

Question 41 of 138

1

Which statement about INSERT operations is true?

Select one or more of the following:

The INSERT statement is used to insert rows into a table, view, or table function.
Inserted values must satisfy the conditions of any check constraints defined on the table.
If an INSERT statement omits any column from the inserted row that is defined as NULL or NOT NULL
WITH DEFAULT, the statement will fail.
If the underlying table of a view being referenced by an INSERT statement has one or more unique
indexes, each row inserted does not have to conform to the constraints imposed by those indexes.

Explanation

Question 42 of 138

1

Which statement about application-period temporal tables is true?

Select one or more of the following:

Consists of explicitly-supplied timestamps and a separate associated history table.
Is based on explicitly-supplied timestamps that define the time periods during which data is valid.
Is useful when one wants to keep both user-based period information and system-based historical
information.
Consists of a pair of columns with database-manager maintained values that indicate the period when
a row is current.

Explanation

Question 43 of 138

1

Which SQL statement should be used to select the minimum and maximum salaries (SALARY), by job
code (JOB), from a table EMPLOYEE?

Select one or more of the following:

SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY job;
SELECT job, MIN(salary), MAX(salary) FROM employee ORDER BY job;
SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY job, MIN(salary), MAX(salary);
SELECT JOB, MIN(salary), MAX(salary) FROM employee ORDER BY job, MIN(salary), MAX(salary);

Explanation

Question 44 of 138

1

In a trusted context environment, if a switch request is made with an authorization ID that is not
allowed on a trusted connection, what state is the connection placed in?

Select one or more of the following:

Locked
Waiting
Pending
Unconnected

Explanation

Question 45 of 138

1

If a user has been granted SYSCTRL authority, which activities can they perform?

Select one or more of the following:

Load operations
Backup/copy table spaces
Create and drop databases
Monitor database performance

Explanation

Question 46 of 138

1

What is the purpose of a role?

Select one or more of the following:

To define exactly what a specific user can do within a particular database once they have been
authenticated.
To group a collection of privileges together so that they can be simultaneously granted to and revoked
from multiple users.
To establish a credible relationship between DB2 and an external entity, such as a database
administrator or a middleware server.
To group a collection of users together so that they can be simultaneously granted and revoked
specific authorities and privileges.

Explanation

Question 47 of 138

1

Which SQL statement will give user USER1 the ability to assign a comment to a sequence named
MYSEQ?

Select one or more of the following:

GRANT ALTER ON SEQUENCE myseq TO user1
GRANT USAGE ON SEQUENCE myseq TO user1
GRANT SELECT ON SEQUENCE myseq TO user1
GRANT COMMENT ON SEQUENCE myseq TO user1

Explanation

Question 48 of 138

1

A column mask that is to be used for row and column access control (RCAC) was created with the
DISABLE option specified. What must be done if this mask is to be used to restrict access to data stored
in a table named EMPLOYEE?

Select one or more of the following:

The column mask must be enabled; the EMPLOYEE table must be altered to activate column access
control.
The column mask must be enabled (column access control for the EMPLOYEE table will be activated
automatically).
The EMPLOYEE table must be altered to activate column access control (the column mask will be
enabled automatically).
The EMPLOYEE table must be altered to activate column access control; row permission must be
granted to everyone who needs to use thecolumn mask.

Explanation

Question 49 of 138

1

User USER1 holds both DBADM and SECADM authority and you want to separate database
administration activities from security administration tasks. What authority must you have in order to
revoke SECADM authority from user USER1 and assign it to someone else?

Select one or more of the following:

DBADM
DBCTRL
SECADM
DBMAINT

Explanation

Question 50 of 138

1

A table named EMPLOYEE was created as follows:

Which action will prevent unauthorized users from accessing SSN data?

Select one or more of the following:

Assign the SSN column to a restricted role that only authorized users can access.
Only grant ACCESSCTRL authority for the SSN column to users who need to access SSN data.
Alter the table definition so that SSN data is stored in a separate schema that only authorized users
can access.
Create a view for the EMPLOYEE table that does not contain the SSN column and require
unauthorized users to use the view.

Explanation

Question 51 of 138

1

Which SQL statement will allow user USER1 to create a view on a table named EMPLOYEE?

Select one or more of the following:

GRANT CREATETAB ON DATABASE TO user1
GRANT SELECT ON TABLE employee TO user1
GRANT CREATEVIEW ON DATABASE TO user1
GRANT REFERENCES ON TABLE employee TO user1

Explanation

Question 52 of 138

1

A new user named USER1 needs to retrieve information from a database named MYDB. What
authority must be granted to user USER1?

Select one or more of the following:

DBCTRL authority
SQLADM authority
DATAACCESS authority
ACCESSCTRL authority

Explanation

Question 53 of 138

1

Which privilege is required to invoke a user defined function?

Select one or more of the following:

CALL
USAGE
EXECUTE
REFERENCES

Explanation

Question 54 of 138

1

Which statement is true about an index that is used to support a UNIQUE constraint?

Select one or more of the following:

It must not contain more than one column.
It cannot be used in a referential constraint.
It must be defined with the UNIQUE attribute.
It must be defined as UNIQUE WHERE NOT NULL.

Explanation

Question 55 of 138

1

Which statement regarding triggers is true?

Select one or more of the following:

Only one INSERT, UPDATE, or DELETE trigger can be defined per table.
Triggers cannot be modified, they must be dropped and recreated with new definitions.
Adding a trigger to a table that already has rows in it will cause triggered actions to be fired.
Triggers can only be fired one time per statement irrespective of the number of rows affected.

Explanation

Question 56 of 138

1

A table named STATES was created as follows:

CREATE TABLE states
(state_cd CHAR(2) NOT NULL,
state_desc CHAR(20));
CREATE UNIQUE INDEX indx1 ON states (state_cd);

if the following ALTER statement is executed:

ALTER TABLE states ADD PRIMARY KEY (state_cd);

What will happen?

Select one or more of the following:

An error will be returned and the operation will fail.
A warning will be returned and the unique index INDX1 will become the index for the primary key.
The primary key will be successfully added and the unique index INDX1 will be dropped and recreated
as the primary key index.
The primary key will be successfully added, the unique index INDX1 will be dropped, and a new index
will be created for the primary key.

Explanation

Question 57 of 138

1

When an application using a temporary table terminates or disconnects from the database
unexpectedly, what happens to the temporary table and any data stored in it?

Select one or more of the following:

The data in the table is deleted and the table persists.
The data in the table is deleted and the table is implicitly dropped.
The temporary table is converted to a base table and the data is deleted.
The temporary table is converted to a base table and the data is persistent.

Explanation

Question 58 of 138

1

When a user-defined function (UDF) is created and no schema is specified, what schema is used to
store the UDF?

Select one or more of the following:

The SYSIBM schema.
The SYSFUN schema.
A schema with the name of the UDF.
A schema with the userid of the UDF definer.

Explanation

Question 59 of 138

1

Which statement about triggers is true?

Select one or more of the following:

A trigger is designed to return specific types of values and contains executable code.
A trigger is designed to return specific types of values but contains no executable code.
A trigger performs actions to capture details about an activity like a join operation against multiple
tables.
A trigger performs actions in response to an event like an INSERT, UPDATE or DELETE operation on
a table.

Explanation

Question 60 of 138

1

Which two statements are true about foreign key constraints? (Choose two.)

Select one or more of the following:

The foreign key constraint columns must be supported by an index.
The foreign key constraint columns must be defined as NOT NULL.
The number and data types of foreign key constraint columns must match the parent key.
The columns of one foreign key constraint cannot be used in another foreign key constraint.
To define a foreign key constraint there must be an associated primary key or unique key in the same
or different table.

Explanation

Question 61 of 138

1

What is the primary purpose of a view?

Select one or more of the following:

To enable uniqueness.
To ensure data clustering.
To combine data from multiple tables.
To optimize queries ran against multiple tables.

Explanation

Question 62 of 138

1

By default, a column defined as a TIMESTAMP includes which attributes?

Select one or more of the following:

No fractional digits or time zone.
6 fractional digits and no time zone.
12 fractional digits plus a time zone.
12 fractional digits and no time zone.

Explanation

Question 63 of 138

1

Which is NOT a valid reason for creating an index?

Select one or more of the following:

To allow queries to run more efficiently.
To enforce constraints such as uniqueness on index keys.
To order the columns of a table in ascending or descending sequence according to values in a row.
To order the rows of a table in ascending or descending sequence according to the values in a column.

Explanation

Question 64 of 138

1

To order the rows of a table in ascending or descending sequence according to the values in a column.

Select one or more of the following:

Unique constraint
Primary key constraint
Informational constraint
Column default constraint

Explanation

Question 65 of 138

1

Which statement is true regarding constraints?

Select one or more of the following:

A table can only have one unique key constraint.
A table can have multiple primary key constraints.
Informational constraints tell DB2 what rules the data conforms to, but the rules are not enforced.
Foreign key constraints are enforced on the values within the rows of a table, or between the rows of
two tables, by a unique index on a foreignkey.

Explanation

Question 66 of 138

1

By default, where is the data stored for a column defined with an XML data type?

Select one or more of the following:

In an XML index.
In line with the rest of the data for the table.
In an XML storage object that is separate from the table.
In line with the rest of the data for the table, for XML columns less than 32KB.

Explanation

Question 67 of 138

1

Which statement about BEFORE triggers is FALSE?

Select one or more of the following:

A BEFORE trigger can be used to perform validation of input data.
A BEFORE trigger can be used to automatically generate values for newly inserted rows.
A BEFORE trigger is fired for each row in the set of affected rows before the trigger event executes.
A BEFORE trigger is fired for each row in the set of affected rows instead of executing the trigger
event.

Explanation

Question 68 of 138

1

What is used in conjunction with a foreign key to define a relationship between two tables?

Select one or more of the following:

Primary key
Partitioning key
Check constraint
Unique constraint

Explanation

Question 69 of 138

1

Which case will require a sequence to be dropped and recreated instead of being modified by the
ALTER SEQUENCE statement?

Select one or more of the following:

Change the data type of the sequence.
Reset the sequence to its starting value.
Establish new minimum or maximum values.
Change the increment between future values.

Explanation

Question 70 of 138

1

Given the following statement:
GRANT DELETE ON TABLE sales TO User1 WITH GRANT OPTION;
Which statement is correct?

Select one or more of the following:

USER1 can only remove the SALES table.
USER1 can only remove records from the SALES table.
USER1 can remove the SALES table and grant the privilege to remove the SALES table to other
users.
USER1 can remove records from the SALES table and grant the privilege to remove records from the
SALES table to other users.

Explanation

Question 71 of 138

1

Which method for restricting data access relies on a SQL search condition that describes what set of
rows a user has access to?

Select one or more of the following:

Authentication
Authorities and privileges
Label-based access control
Row and column access control

Explanation

Question 72 of 138

1

Which SQL statement will take the ability to create tables in a table space named MYTBSP away from
user USER1?

Select one or more of the following:

REVOKE USE OF TABLESPACE mytbsp FROM user1
REVOKE ALTER ON TABLESPACE mytbsp FROM user1
REVOKE UPDATE OF TABLESPACE mytbsp FROM user1
REVOKE CREATETAB ON TABLESPACE mytbsp FROM user1

Explanation

Question 73 of 138

1

In a trusted context environment, if a switch request is made with an authorization ID that is not
allowed on a trusted connection, what state is the connection placed in?

Select one or more of the following:

Locked
Waiting
Pending
Unconnected

Explanation

Question 74 of 138

1

When is a mask used to limit access to data in a table?

Select one or more of the following:

When mandatory access control (MAC) is used to protect the table.
When discretionary access control (DAC) is used to protect the table.
When label-based access control (LBAC) is used to protect the table.
When row and column access control (RCAC) is used to protect the table.

Explanation

Question 75 of 138

1

What privilege is required to create a view on a table that you are not the owner of?

Select one or more of the following:

INSERT
SELECT
CREATE
TRIGGER

Explanation

Question 76 of 138

1

After creating several new indexes and executing RUNSTATS, user USER1 wants to re-create a
package that is stored in the database so it will take advantage of the new indexes. Which
authority/privilege must user USER1 be granted in order to re-create the package?

Select one of the following:

BIND
REBIND
BINDADD
BIND_REBIND

Explanation

Question 77 of 138

1

Which SQL statement will give a user named USER1 the ability to run an embedded SQL application
named BENEFITS that calls a package named CORPDATA.PKGA?

Select one of the following:

GRANT EXECUTE ON APPLICATION benefits TO user1
GRANT EXECUTE ON PACKAGE corpdata.pkga TO user1
GRANT EXECUTE ON APPLICATION PACKAGE corpdata.pkga TO user1
GRANT EXECUTE ON APPLICATION benefits USING PACKAGE corpdata.pkga TO user1

Explanation

Question 78 of 138

1

Which of the following privileges allow a user to update the comment on a sequence?

Select one of the following:

ALTER
USAGE
UPDATE
COMMENT

Explanation

Question 79 of 138

1

Which statement is valid about the Row and Column Access Control (RCAC) feature?

Select one of the following:

A row mask's access control rule is defined by an SQL CASE expression; a column permission's
access control rule is defined by an SQLsearch condition.
A row permission's access control rule is defined by an SQL CASE expression; a column mask's
access control rule is defined by an SQLsearch condition.
A row mask's access control rule is defined by an SQL search condition; a column permission's access
control rule is defined by an SQL CASEexpression.
A row permission's access control rule is defined by an SQL search condition; a column mask's access
control rule is defined by an SQL CASEexpression.

Explanation

Question 80 of 138

1

Which type of table should you use if you want to define specific time periods when data is valid?

Select one of the following:

Materialized query table
System-period temporal table
Declared global temporary table
Application-period temporal table

Explanation

Question 81 of 138

1

Which SQL statement will create a DB2 object that can be used to store numerical data as Canadian
currency?

Select one of the following:

CREATE DISTINCT ALIAS canadian_dollar AS DECIMAL (9,3) WITH COMPARISONS
CREATE DISTINCT TYPE canadian_dollar AS DECIMAL (9,3) WITH COMPARISONS
CREATE DISTINCT PROCEDURE canadian_dollar AS DECIMAL (9,3) WITH COMPARISONS
CREATE DISTINCT FUNCTION canadian_dollar AS DECIMAL (9,3) WITH COMPARISONS

Explanation

Question 82 of 138

1

Which object can be enabled for compression?

Select one of the following:

View
Buffer pool
Storage group
Temporary table

Explanation

Question 83 of 138

1

What is the minimum product required for DB2 connectivity across all platforms?

Select one of the following:

DB2 Personal Edition
DB2 Development Edition
DB2 Connect Personal Edition
DB2 Development Unlimited Edition

Explanation

Question 84 of 138

1

Given the SQL statement: CREATE ALIAS total_sales FOR sales Which statement is valid?

Select one of the following:

SALES can be the name of a schema.
SALES can be the name of a package.
SALES can be the name of an existing alias.
SALES can be the name of a declared global temporary table.

Explanation

Question 85 of 138

1

Which DB2 object limits the user's ability to retrieve data from a table by defining a SQL statement in
the object?

Select one of the following:

View
Index
Trigger
Check constraint

Explanation

Question 86 of 138

1

A table named EMPLOYEES was created as follows, What will be the results?

Select one of the following:

0
1
2
3

Explanation

Question 87 of 138

1

Which two DB2 objects can a view be derived from? (Choose two.)

Select one or more of the following:

View
Index
Table
Trigger
Procedure

Explanation

Question 88 of 138

1

Which protocol provides applications the ability to connect to and update multiple DB2 databases
within the same transaction?

Select one of the following:

Type-1 connect
Type-2 connect
Type-3 connect
Type-4 connect

Explanation

Question 89 of 138

1

Which statement about bitemporal tables is valid?

Select one of the following:

Bitemporal tables are system tables and can only be queried by the schema owner.
Creating a bitemporal table is similar to creating a regular table except users must specify a system
time period column.
When data in a bitemporal table is updated, a row is added to it's associated history table.
Querying a bitemporal table will produce results for a complete timeline; a view must be created to
obtain results for a specific time period.

Explanation

Question 90 of 138

1

Which object can a buffer pool be assigned to?

Select one of the following:

Schema
Table space
Storage group
Stored procedure

Explanation

Question 91 of 138

1

Which DB2 object is associated with PREVIOUS VALUE and NEXT VALUE expressions?

Select one of the following:

View
Table
Sequence
Identity Column

Explanation

Question 92 of 138

1

What needs to be defined in order to track changes made to a system-period temporal table over
time?

Select one of the following:

Once the row-begin, row-end, and transaction-start-id columns are created, all changes are tracked.
A history table must be created with identical columns to the base table and a unique index must be
defined on the transaction-start-id column.
A history table must be created as a clone table of the base table after the row-begin, row-end, and
transaction-start-id columns have beendefined.
A history table must be created with identical columns to the base table and then the base table altered
with the ADD VERSIONING clause torelate it to the history table.

Explanation

Question 93 of 138

1

Which object is used to cache table and index data as it is read from disk?

Select one of the following:

Database
Buffer pool
Table space
Package cache

Explanation

Question 94 of 138

1

Which statement about large object (LOB) locators is true?

Select one of the following:

A LOB locator is a data type that is used to store LOB data in binary files.
A LOB locator is a data type that is used to store LOB data in a database.
A LOB locator represents a value for a LOB resource that is stored in a database.
A LOB locator represents a value for a LOB resource that is stored in a binary file.

Explanation

Question 95 of 138

1

Which DB2 product is a database cluster solution for non-mainframe platforms and is best suited for
Online Transaction Processing (OLTP) workloads?

Select one of the following:

DB2 Connect
DB2 pureScale
Workload Manager
High Availability Disaster Recovery

Explanation

Question 96 of 138

1

What type of large object (LOB) is used to store LOB data together with the formatted rows on data
pages, instead of in a separate LOB storage object?

Select one or more of the following:

inline
binary
internal
partitioned

Explanation

Question 97 of 138

1

Which product replaced the Control Center used in versions prior to DB2 v10?

Select one or more of the following:

IBM Data Studio
IBM Administrator
IBM Data Manager
IBM Command Center

Explanation

Question 98 of 138

1

Which DB2 product is NOT suitable for very large data warehouse applications?

Select one or more of the following:

DB2 for i
DB2 for AIX
DB2 for z/OS
DB2 for Linux

Explanation

Question 99 of 138

1

Which platform supports DB2 pureScale environments?

Select one or more of the following:

AIX
iOS
z/OS
Windows

Explanation

Question 100 of 138

1

Which tool allows users to connect to a DB2 database using a wizard?

Select one or more of the following:

Control Center
IBM Data Studio
Universal Connection Expert
DB2 Connection Pool Manager

Explanation

Question 101 of 138

1

A table named SALES contains a record for every sales transaction a company processes. A user
wishes to see the number of transactions that are made by each salesman. Which SQL statement will
produce the desired results?

Select one or more of the following:

SELECT name, COUNT(*) AS transactions FROM sales
SELECT name, COUNT(DISTINCT name) AS transactions FROM sales
SELECT name, COUNT(*) AS transactions FROM sales GROUP BY name
SELECT DISTINCT name, COUNT(*) AS transactions FROM sales GROUP BY transactions

Explanation

Question 102 of 138

1

Which statement about roll back operations is correct?

Select one or more of the following:

When a ROLLBACK statement is executed, all locks held by the terminating transaction are released.
When a ROLLBACK TO SAVEPOINT statement is executed, all locks acquired up to the savepoint are
released.
When a ROLLBACK TO SAVEPOINT statement is executed, all locks acquired after the savepoint are
released.
When a ROLLBACK statement is executed, all locks acquired for open cursors that were declared
WITH HOLD, are held.

Explanation

Question 103 of 138

1

When a COMMIT statement is executed, what happens?

Select one or more of the following:

All locks held on the database are automatically released.
Data stored in global temporary tables is automatically deleted.
Open cursors defined WITH HOLD are closed, but their data is retained.
The current transaction is terminated and a new transaction boundary is started.

Explanation

Question 104 of 138

1

Which SQL statement would be used to retrieve only salaries that are greater than $100,000.00 from
a table named EMPLOYEE?

Select one or more of the following:

SELECT * FROM employee WHERE salary < 100000
SELECT salary FROM employee WHERE salary > 100000
SELECT salary FROM employee WHERE salary > 100,000.00
SELECT * FROM employee WHERE salary GREATER THAN 100000

Explanation

Question 105 of 138

1

Which SQL statement should be used to retrieve the minimum and maximum salaries (SALARY) for
each job code (JOB), sorted by job code, from a table named EMPLOYEE?

Select one or more of the following:

SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY job ORDER BY job;
SELECT job, MIN(salary), MAX(salary) FROM employee ORDER BY job;
SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY SALARY ORDER BY job;
SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY MIN(salary), MAX(salary) ORDER BY job;

Explanation

Question 106 of 138

1

If a table named MY_TAB contains 100 rows and the following statement is executed:

Select one or more of the following:

0
1
10
99

Explanation

Question 107 of 138

1

An SQL function named DEPT_INFO was created as follows:
What is the correct way to use this function in a query?

Select one or more of the following:

SELECT dept_info(dept_id, dept_name)
SELECT dept_id, dept_name FROM dept_info()
SELECT TABLE dept_id, dept_name FROM dept_info()
SELECT dept_id, dept_name FROM TABLE(dept_info()) AS results

Explanation

Question 108 of 138

1

Which type of stored procedure is not associated with an external application program?

Select one or more of the following:

A Java stored procedure.
A REXX stored procedure.
A native SQL stored procedure.
An external SQL stored procedure.

Explanation

Question 109 of 138

1

Which SQL statement will retrieve the department number (WORKDEPT) and average departmental
salary (SALARY) for all departments in a table named EMPLOYEE and arrange the result set in
ascending order by average departmental salary?

Select one or more of the following:

SELECT workdept, AVG(salary) FROM employee ORDER BY workdept GROUP BY 2
SELECT workdept, AVG(salary) FROM employee ORDER BY workdept ARRANGE BY 2
SELECT workdept, AVERAGE(salary) FROM employee GROUP BY workdept ORDER BY 2
SELECT workdept, AVG(salary) FROM employee GROUP BY workdept ORDER BY 2

Explanation

Question 110 of 138

1

Which statement about system-period temporal tables is true?

Select one or more of the following:

They store user-based period information.
They do not have a separate history table.
They can be queried without a time period specification.
They manage data based on time criteria specified by users or applications.

Explanation

Question 111 of 138

1

A table named DEPARTMENT contains the following data:
What will happen?

Select one or more of the following:

The statement will fail because a subquery cannot be used in an UPDATE statement.
The statement will fail because the result set produced by the subquery will contain more than one row.
The statement will succeed; the last record retrieved by the subquery will be used to provide a WORKDEPT value for the update operation.
The statement will succeed; the first record retrieved by the subquery will be used to provide a WORKDEPT value for the update operation

Explanation

Question 112 of 138

1

Which statement about UPDATE processing is FALSE?

Select one or more of the following:

The UPDATE statement logs the before and after value of every column it modifies.
A searched UPDATE is used to update one or more rows and a positioned UPDATE is used to update exactly one row.
When the UPDATE statement modifies parent key columns, the value of corresponding foreign key columns are also modified.
The UPDATE statement modifies the values of specified columns in the rows of a table, view, or underlying table(s) of a specified fullselect.

Explanation

Question 113 of 138

1

Given the following statements:
What will be the result?

Select one or more of the following:

Joe Smith
<name>Joe Smith</name>
<name>Joe Smith></name><name>DB2</name>
<employeeinfo><name>Joe Smith</name></employeeinfo>

Explanation

Question 114 of 138

1

A table named STATES has the following columns: STATE_CD, REGION_CD, and STATE_NAME.
Which SQL statement will return the number of states in each region, ordered by number of states?

Select one or more of the following:

SELECT state_cd, COUNT(*) FROM states ORDER BY COUNT(*);
SELECT state_cd, COUNT(*) FROM states GROUP BY state_cd ORDER BY state_cd;
SELECT region_cd, COUNT(*) FROM states GROUP BY state_cd ORDER BY COUNT(*);
SELECT region_cd, COUNT(*) FROM states GROUP BY region_cd ORDER BY COUNT(*);

Explanation

Question 115 of 138

1

When a user-defined function (UDF) is created and no schema is specified, what schema is used to
store the UDF?

Select one or more of the following:

The SYSIBM schema.
The SYSFUN schema.
A schema with the name of the UDF.
A schema with the userid of the UDF definer.

Explanation

Question 116 of 138

1

What is a function of a table check constraint?

Select one or more of the following:

To provide a method of avoiding duplicate entries for a column
To provide a method of ensuring a column's value are within a specific range
To ensure that XML columns are properly populated in an associated XML table space
To enforce relationships between tables by checking the existence of rows in one table against another.

Explanation

Question 117 of 138

1

which statement about foreign keys is true?

Select one or more of the following:

A table can have only one foreign key
Foreign keys can only be defined when a table is created
A foreign key can be defined on a column or a set of columns in a table
Foreign keys are enforced only during the execution of INSERT statements

Explanation

Question 118 of 138

1

which type of table is best suited for recording results from application data manipulation that is not required to be persistent?

Select one or more of the following:

Base table
Temporal table
Transition table
Temporary table

Explanation

Question 119 of 138

1

Which statement regarding referential integrity constraints is true?

Select one or more of the following:

Multiple primary keys can be defined for a table
A foreign key can reference multiple primary keys
A primary key can be referenced by only one foreign key
Primary keys and foreign keys are used to define relationships

Explanation

Question 120 of 138

1

Which database object provides an alternative way of describing data that exists in one or more base tables?

Select one or more of the following:

Alias
View
Index
Sequence

Explanation

Question 121 of 138

1

The WITH CHECK OPTION clause of the CREATE VIEW statement allows you to do which of the following?

Select one or more of the following:

Define a view on one or more tables or views
Revalidate all invalid objects that are referenced on a view's fullselect
Ensure that any inserted or updated rows conform to the definition of the view
Define a view that only consists of columns that have check constraints defined

Explanation

Question 122 of 138

1

Which type of constraint can be used to tell the DB2 Optimizer that a parent-child relationship exists between two tables, but that the relationship is not enforced?

Select one or more of the following:

Check
Unique
Referential
Informational

Explanation

Question 123 of 138

1

Which event will NOT cause a trigger to be fired?

Select one or more of the following:

Execution of an INSERT statement
Execution of LOAD REPLACE operation
Execution of an INSERT operation of a MERGE statement
Execution of an UPDATE operation of a MERGE statement

Explanation

Question 124 of 138

1

What is the primary purpose for using the INCLUDE clause with a CREATE UNIQUE INDEX statement?

Select one or more of the following:

To override the limit on index key columns
To further enforce the uniqueness of index values
To combine two or more indexes to create a single unique index
To improve performance of some queries by allowing index-only access

Explanation

Question 125 of 138

1

What are INSTEAD OF triggers used for?

Select one or more of the following:

To update data in other tables
To perform insert, update and delete operations against complex views
To check or modify values before they are actually stored in a database
To run non-database operations that are coded in user-defined functions

Explanation

Question 126 of 138

1

User USER1 wants to store part numbers and part descriptions in a table named PARTS; user
USER1 also wants to ensure that any comparisons of part descriptions that are made will be done so in binary, regardless of the database collating sequence used. Which statement should user USER1
execute to create the desired table?

Select one or more of the following:

CREATE TABLE parts (part_no INTEGER, desc CLOB))
CREATE TABLE parts (part_no INTEGER, desc VARCHAR(200))
CREATE TABLE parts (part_no INTEGER, desc CLOB FOR BIT DATA))
CREATE TABLE parts (part_no INTEGER, desc VARCHAR(200) FOR BIT DATA)

Explanation

Question 127 of 138

1

What mechanism is typically used to automatically update other tables, generate or transform values
for inserted or updated rows, and perform tasks such as issuing alerts?

Select one or more of the following:

Trigger
Procedure
Table function
Scalar function

Explanation

Question 128 of 138

1

Which data type should be used to store data in a column that represents money and accurately
returns a two position scale?

Select one or more of the following:

BIGINT
BINARY
DOUBLE
DECIMAL

Explanation

Question 129 of 138

1

User USER1 wants to define a required relationship between two tables named TAB1 and TAB2 in
such a way that whenever a record is deleted from table TAB1, any related records are deleted from table TAB2. What must user USER1 do to accomplish this?

Select one or more of the following:

1) Create a primary key on table TAB1; 2) Create a foreign key on table TAB2 that references the primary key on table TAB1 and adheres to the ON DELETE CASCADE rule.
Create a primary key on table TAB2; 2) Create a foreign key on table TAB1 that references the primary key on table TAB2 and adheres to the ON DELETE CASCADE rule.
1) Create a primary key on table TAB1; 2) Create a foreign key on table TAB2 that references the primary key on table TAB1 and adheres to the ON DELETE RESTRICT rule.
1) Create a primary key on table TAB2; 2) Create a foreign key on table TAB1 that references the primary key on table TAB2 and adheres to the ON DELETE RESTRICT rule.

Explanation

Question 130 of 138

1

Which statement about a deadlock is true?

Select one or more of the following:

The victim will be rolled back.
The victim will read through the lock.
Both victim and holder are rolled back.
The victim must wait until the holder releases the lock.

Explanation

Question 131 of 138

1

What takes place when a process accesses a data object on which it already holds a lock, and the
access mode requires a more restrictive lock than the one currently held?

Select one or more of the following:

Lock wait
Lock timeout
Lock escalation
Lock conversion

Explanation

Question 132 of 138

1

What is the act of releasing a large number of row-level locks that an application holds on a single
table to acquire a table-level lock known as?

Select one or more of the following:

Lock exchange
Lock promotion
Lock escalation
Lock conversion

Explanation

Question 133 of 138

1

When is an INTENT EXCLUSIVE (IX) lock required?

Select one or more of the following:

When a transaction intends to read or change data.
When a transaction intends to change but not read data.
When a transaction intends to read but not change data.
When a transaction intends to change the system catalog.

Explanation

Question 134 of 138

1

The EXCLUSIVE MODE option of the LOCK TABLE statement is used to prevent which of the
following?

Select one or more of the following:

Concurrent application processes from performing any operations on the table.
Concurrent application processes from performing any read-only operations on the table.
Concurrent applications processes that are running under the cursor stability (CS) isolation level from performing read-only operations on thetable.
Concurrent applications processes that are running under the uncommitted read (UR) isolation level from performing read-only operations onthe table.

Explanation

Question 135 of 138

1

What factor influences lock conversion/promotion?

Select one or more of the following:

Lock size
Number of locks
Lock mode needed
Available real storage

Explanation

Question 136 of 138

1

Application APP_A is performing updates to table TAB1 using the read stability (RS) isolation level.
Which isolation level will allow application APP_B to retrieve all rows from table TAB1 immediately, rather than have to wait until application APP_A has finished making updates?

	Created by Mª Carmen Ruiz Garcia over 9 years ago

	Copied by ismael mesa domi over 9 years ago

	Copied by renanvkas over 9 years ago

	Copied by mmorenonf over 9 years ago

	Copied by alejandrogarciah over 9 years ago

	Copied by rosamarozzi over 9 years ago

	Copied by alejandro lopez 8876 over 9 years ago

Quiz on Ejemplo_1_3, created by alejandro lopez 8876 on 19/06/2015.

Ejemplo_1_3

Ejemplo_1_3

Question 1 of 138

If the following command is executed: CREATE DATABASE test What is the page size (in kilobytes) of the database?

Select one or more of the following:

Explanation

Question 2 of 138

Given the following DDL statement: CREATE TABLE sales (item CHAR(20)); If a DBA wishes to increase the size of the ITEM column, which SQL statement needs to be used?

Select one or more of the following:

Explanation

Question 3 of 138

Which DB2 object can be used to improve the execution performance of qualified SELECT statements?

Select one or more of the following:

Explanation

Question 4 of 138

When is it appropriate to use a sequence?

Select one or more of the following:

Explanation

Question 5 of 138

5.Which clause should be included in a ALTER TABLE statement to update a table definition with a new column?

Select one or more of the following:

Explanation

Question 6 of 138

Which object is a stored procedure defined into?

Select one or more of the following:

Explanation

Question 7 of 138

Which action needs to be performed in order to complete the definition of an application-period temporal table?

Select one or more of the following:

Explanation

Question 8 of 138

What functionality allows users to perform a UNION operation between a DB2 table and an Oracle view?

Select one or more of the following:

Explanation

Question 9 of 138

You have a business need to query DB2 10 and DB2 9 databases and you want to write an application that can run on most platforms unchanged. Which interface would you use to achieve your goal?

Select one or more of the following:

Explanation

Question 10 of 138

Which DB2 object is used to provide a logical grouping of other database objects?

Select one or more of the following:

Explanation

Question 11 of 138

What are two valid objects that can be created in DB2? (Choose two.)

Select one or more of the following:

Explanation

Question 12 of 138

Which type of temporal table can be used to store only time-sensitive data?

Select one or more of the following:

Explanation

Question 13 of 138

When an index is created for a table, where is the metadata for that index stored?

Select one or more of the following:

Explanation

Question 14 of 138

Which product is used to customize execution environments for the purpose of controlling system resources so that one department or service class does not overwhelm the system?

Select one or more of the following:

Explanation

Question 15 of 138

What type of mechanism is a simple token value that is used to refer to a much bigger large object (LOB)?

Select one or more of the following:

Explanation

Question 16 of 138

What type of large object (LOB) is used to store LOB data together with the formatted rows on data pages, instead of in a separate LOB storage object?

Select one or more of the following:

Explanation

Question 17 of 138

What is the purpose of the Query Tuner?

Select one or more of the following:

Explanation

Question 18 of 138

Which tool allows users to connect to a DB2 database using a wizard?

Select one or more of the following:

Explanation

Question 19 of 138

Which two activities indicate the need for a data warehouse? (Choose two.)

Select one or more of the following:

Explanation

Question 20 of 138

If the following command is executed: CREATE DATABASE test What is the page size (in kilobytes) of
the database?

Given the following DDL statement: CREATE TABLE sales (item CHAR(20)); If a DBA wishes to
increase the size of the ITEM column, which SQL statement needs to be used?

5.Which clause should be included in a ALTER TABLE statement to update a table definition with a new
column?

Which action needs to be performed in order to complete the definition of an application-period temporal
table?

What functionality allows users to perform a UNION operation between a DB2 table and an Oracle
view?

You have a business need to query DB2 10 and DB2 9 databases and you want to write an application
that can run on most platforms unchanged. Which interface would you use to achieve your goal?

Which product is used to customize execution environments for the purpose of controlling system
resources so that one department or service class does not overwhelm the system?

What type of mechanism is a simple token value that is used to refer to a much bigger large object
(LOB)?

What type of large object (LOB) is used to store LOB data together with the formatted rows on data
pages, instead of in a separate LOB storage object?

Which isolation level offers the greatest protection of data but provides the least amount of
concurrency?

Which type of lock allows the lock owner and all concurrent applications to read, but not update, the
locked data?

Application APP_A is performing updates to table TAB1 using the cursor stability (CS) isolation level. If
application APP_B wants to retrieve all rows from table TAB1 without waiting for application APP_A to
finish making updates, what isolation level must application APP_B use?

Which statement will prevent concurrent application processes from performing anything other than
read-only operations against a table named TAB1?

What is the act of exchanging one lock an application holds on a resource for a more restrictive lock on
the same resource known as?

Which command is used to back out a subset of database changes that have been made within a unit
of work?

If a table named MY_TAB contains 100 rows and the following statement is executed:
DELETE FROM (SELECT * FROM my_tab ORDER BY col1 DESC
FETCH FIRST 5 ROWS ONLY) AS tmp;

What will happen?

Given an EMPLOYEES table and a SALES table, a user wants to produce a list of all employees and
their associated revenue, even if no revenue exists. Which SQL statement will produce the desired list?

Which function can be used to obtain values from XML documents that are to be inserted into one or
more tables?