Ejemplo_1

Frage 1 von 55

1

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

Wähle eine oder mehr der folgenden:

4
8
16
32

Erklärung

Frage 2 von 55

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?

Wähle eine oder mehr der folgenden:

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);

Erklärung

Frage 3 von 55

1

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

Wähle eine oder mehr der folgenden:

Trigger
SQL Procedure
Sequence Object
Materialized Query Table

Erklärung

Frage 4 von 55

1

When is it appropriate to use a sequence?

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 5 von 55

1

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

Wähle eine oder mehr der folgenden:

ADD COLUMN
ALTER COLUMN
APPEND COLUMN
RENAME COLUMN

Erklärung

Frage 6 von 55

1

Which object is a stored procedure defined into?

Wähle eine oder mehr der folgenden:

Table
Schema
Package
Database

Erklärung

Frage 7 von 55

1

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

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 8 von 55

1

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

Wähle eine oder mehr der folgenden:

Oracle connect
Trusted context
Oracle federation
Distributed request

Erklärung

Frage 9 von 55

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?

Wähle eine oder mehr der folgenden:

CLI
XML
JDBC
RUBY

Erklärung

Frage 10 von 55

1

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

Wähle eine oder mehr der folgenden:

Alias
Index
Schema
Collection

Erklärung

Frage 11 von 55

1

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

Wähle eine oder mehr der folgenden:

Tree
Node
Trigger
Contexts
Sequence

Erklärung

Frage 12 von 55

1

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

Wähle eine oder mehr der folgenden:

Bitemporal
Time-period
System-period
Application-period

Erklärung

Frage 13 von 55

1

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

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 14 von 55

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?

Wähle eine oder mehr der folgenden:

pureScale
Workload manager
Data partitioning feature
Self-tuning memory manager

Erklärung

Frage 15 von 55

1

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

Wähle eine oder mehr der folgenden:

locator
pointer
address
reference

Erklärung

Frage 16 von 55

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?

Wähle eine oder mehr der folgenden:

inline
binary
internal
partitioned

Erklärung

Frage 17 von 55

1

What is the purpose of the Query Tuner?

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 18 von 55

1

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

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 19 von 55

1

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

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 20 von 55

1

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

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 21 von 55

1

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

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 22 von 55

1

What factor influences lock escalation?

Wähle eine oder mehr der folgenden:

Table size
Buffer space
Number of locks
Available real storage

Erklärung

Frage 23 von 55

1

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

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 24 von 55

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?

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 25 von 55

1

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

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 26 von 55

1

When is an INTENT EXCLUSIVE (IX) lock required?

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 27 von 55

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?

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 28 von 55

1

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

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 29 von 55

1

Which operation normally does NOT require an exclusive lock?

Wähle eine oder mehr der folgenden:

BIND
DROP
GRANT
SELECT

Erklärung

Frage 30 von 55

1

When a COMMIT statement is executed, what happens?

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 31 von 55

1

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

Wähle eine oder mehr der folgenden:

COMMIT
ROLLBACK
COMMIT TO SAVEPOINT
ROLLBACK TO SAVEPOINT

Erklärung

Frage 32 von 55

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?

Wähle eine oder mehr der folgenden:

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;

Erklärung

Frage 33 von 55

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?

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 34 von 55

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?

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 35 von 55

1

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

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 36 von 55

1

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

Wähle eine oder mehr der folgenden:

TRIM
DROP
DELETE
TRUNCATE

Erklärung

Frage 37 von 55

1

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

Wähle eine oder mehr der folgenden:

XMLTABLE
XMLPARSE
XMLEXISTS
XMLATTRIBUTES

Erklärung

Frage 38 von 55

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?

Wähle eine oder mehr der folgenden:

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')

Erklärung

Frage 39 von 55

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?

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 40 von 55

1

If the following result set is desired:

Which SQL statement must be executed?

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 41 von 55

1

Which statement about INSERT operations is true?

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 42 von 55

1

Which statement about application-period temporal tables is true?

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 43 von 55

1

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

Wähle eine oder mehr der folgenden:

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);

Erklärung

Frage 44 von 55

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?

Wähle eine oder mehr der folgenden:

Locked
Waiting
Pending
Unconnected

Erklärung

Frage 45 von 55

1

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

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 46 von 55

1

What is the purpose of a role?

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 47 von 55

1

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

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 48 von 55

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?

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 49 von 55

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?

Wähle eine oder mehr der folgenden:

DBADM
DBCTRL
SECADM
DBMAINT

Erklärung

Frage 50 von 55

1

A table named EMPLOYEE was created as follows:

Which action will prevent unauthorized users from accessing SSN data?

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 51 von 55

1

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

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 52 von 55

1

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

Wähle eine oder mehr der folgenden:

DBCTRL authority
SQLADM authority
DATAACCESS authority
ACCESSCTRL authority

Erklärung

Frage 53 von 55

1

Which privilege is required to invoke a user defined function?

Wähle eine oder mehr der folgenden:

CALL
USAGE
EXECUTE
REFERENCES

Erklärung

Frage 54 von 55

1

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

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 55 von 55

1

Which statement regarding triggers is true?

Wähle eine oder mehr der folgenden:

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.

	Erstellt von Mª Carmen Ruiz Garcia vor mehr als 11 Jahre

	Kopiert von ismael mesa domi vor mehr als 11 Jahre

	Kopiert von renanvkas vor mehr als 11 Jahre

Quiz am Ejemplo_1, erstellt von renanvkas am 17/06/2015.

Ejemplo_1

Frage 1 von 55

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

Wähle eine oder mehr der folgenden:

Erklärung

Frage 2 von 55

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?

Wähle eine oder mehr der folgenden:

Erklärung

Frage 3 von 55

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

Wähle eine oder mehr der folgenden:

Erklärung

Frage 4 von 55

When is it appropriate to use a sequence?

Wähle eine oder mehr der folgenden:

Erklärung

Frage 5 von 55

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

Wähle eine oder mehr der folgenden:

Erklärung

Frage 6 von 55

Which object is a stored procedure defined into?

Wähle eine oder mehr der folgenden:

Erklärung

Frage 7 von 55

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

Wähle eine oder mehr der folgenden:

Erklärung

Frage 8 von 55

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

Wähle eine oder mehr der folgenden:

Erklärung

Frage 9 von 55

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?

Wähle eine oder mehr der folgenden:

Erklärung

Frage 10 von 55

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

Wähle eine oder mehr der folgenden:

Erklärung

Frage 11 von 55

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

Wähle eine oder mehr der folgenden:

Erklärung

Frage 12 von 55

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

Wähle eine oder mehr der folgenden:

Erklärung

Frage 13 von 55

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

Wähle eine oder mehr der folgenden:

Erklärung

Frage 14 von 55

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?

Wähle eine oder mehr der folgenden:

Erklärung

Frage 15 von 55

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

Wähle eine oder mehr der folgenden:

Erklärung

Frage 16 von 55

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?

Wähle eine oder mehr der folgenden:

Erklärung

Frage 17 von 55

What is the purpose of the Query Tuner?

Wähle eine oder mehr der folgenden:

Erklärung

Frage 18 von 55

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

Wähle eine oder mehr der folgenden:

Erklärung

Frage 19 von 55

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

Wähle eine oder mehr der folgenden:

Erklärung

Frage 20 von 55

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?