Wednesday, December 4, 2013

DB2 Architecture -What is Table Space, storage groups,OBID,PSID,index and Index spaces in DB2

When DB2 gets installed on Z/OS there are several other components that runs in parallel . Like all other Z/OS components, DB2 also runs as a formal subsytem and in different address spaces.Let's see the list of system objects which comprises the core DB2 architecture in z/os.
All System objects that DB2 works with in Z/Os architecture.
1. So,What is a DB2 DIRECTORY ?:
This is one of  the DB2 system objects which is  being  used during normal operation (like to gather information ,etc ) and are totally internal to DB2.We cannot access the Directory tables. There are  system databases like DSNDB01, DSNDB06, DSNDB07 on which all these system objects (as shown above)resides on.
DB2 Directory resides in DSNDB01 database and this database contains 5 main Table spaces as below:
SCT02
SPT01
DBD01
SYSUTILX
SYSLGRNGX .
SCT02: contains the skeleton cursor table for plans.When we do a DB2 bind for the SQL statements onto a PLAN, a db2 structure is in SCT02 structure.FREE PLAN removes the removes the table structure.
SPT01: Similarly when we execute a package Bind, db2 causes a structure to be created in SPT01 Table space..When we issue a FREE PACKAGE command, the SKT is removed from the SPT01.
DBD01 (DBD):Contains information about all the DB2 objects defined in the database.DB2 creates and updates DBDs whenever their corresponding databases are created or updated
SYSUTILX (Active Utilities): We run online DB2 utilites as part of normal day work. As each step progresses, its status gets updated and stored in
When the utility finishes normally or is terminated, all information about that utility is purged from SYSUTILX.
SYSLGRNGX (Logs):Tracks the opening and closing of table spaces, indexes, or partitions. This tracking information is associated with RBA.When a DB2 recovery is requested,the required logs are located and used for quick recovery.

Just For information:
DB2 Catalog resides in the DSNDB06 database.
DB2 Directory resides in the DSNDB01 database.
Work files reside in the DSNDB07 database.

2. What is DB2 CATALOG:
When we hear the term catalog,the thing that comes to our mind is 'information being stored/registered' in the z/os system. Exactly the same fact goes for DB2 catalog.
DB2 catalog is nothing but set of  system tables like SYSIBM.SYSTABLES, SYSIBM.SYSTABAUTH,SYSIBM.SYSTABAUTH....and lot more of other tables whose main function is to keep information of the DB2 objects that we create like Tables,columns,indexes, primary key,foreign keysViews,Indexes.Lets check an example and have an idea about :
How the Catalog and system tables used in DB2?
Whenever a DB2 object is created, dropped or altered by any DDL statement or whenever control over the object is changed by any DCL statement of sql, an entry is made in the appropriate catalog table.
We can query the CATALOG TABLES to get information, but can not modify these CATALOG tables.
Example: When we trigger a SQL query, db2 checks in catalog tables if the table exists or not. Then it will look for SYSCOLUMNS to check if the column exists or not. Further more it checks the SYSTABAUTH to check the authority of the person firing the SQL statement.
Similarly when we create a table into one table space, every details about the table, ie the Table name, Table space name, name of the database it is residing on, name of the owner is captured and a new row gets inserted into the corresponding tables.

3. What is a DB2 LOG:
As the name suggests, log is something which keeps track of all the changes done to the database.So every transaction (insert, update, delete) performed on the data is logged in the log files.These information comes useful in crash recoveries. A log file has large number of log records in it.A log sequence number identifies the log record in the log file. DB2 writes each log record on to a file and is known as Active log. Once the space is full, these logs are migrated to tape and becomes Archive Log ( so that they can be referred later when needed).
4. What is a Boot strap data set(BSDS):
Contains information which are critical to  DB2 , such as name of the logs.DB2 uses these information in doing system restarts.
5. What is a Buffer pool: 
We all know about the concept of cache memory.A buffer pool is nothing but cached database page in memory.We can view it as a a virtual storage space for table space or indexes,but this is temporary.When DB2 access the rows of a table,that data page containing that requested  row is written to the buffer pool.So next time when the data page is accessed, DB2 eliminates the need to go back to the disk to fetch the row, there by reducing the physical input/output access to the disk.
" For information:::The maximum size of a buffer pool is 1 TB  "

6. What is a Resource Limit Facility:
The name is self explanatory."Resource  Limit  Facility" which means DB2 has some kind of governing system which  helps to limit/control the use of DB2 resource. RLF limits the resource usage like bind operations in certain times of the day,to avoid contention; Set warning or error threshold when processing limit might be exceeded for particular dynamic SQL statement and inform the user
7. Work Database:
Simply these are the work files storage, used by DB2 to process SQL statements.( like we see SORTWK1...in dfsort /syncsort). The database for the workfiles is DSNDB07.


Now lets see brief about user defined Table spaces and other DB2 objects!!!
---------------------------------------------------------------------------------------------------
The data structure in db2 consists of Storage group, database, tablespace, tables, index, and index spaces. Among these only the storage group , Table space and index have the physical existence. Rest all are logical concept. Storage group is a physical DASD (direct Access storage device). A table space or an index is associated with one or more of these physical datasets.
Table and database have no actual physical representation.We can imagine a database to be big unit which contain tablespace and indexes. The tablespace in turn contains one or multiple table.
Since a Tablespace has physical existence ,we need to  specify the PRIQTY  and SECQTY while creating the table space because they actually allocate primary and secondary space in the DASD devices.

What are Object Identifiers(OBID),PSID,DBID in DB2?
When we create a object in DB2 database, those objects are assigned with a identifier and stored in DB2 catalog.These object identifiers are called OBID. Going further a table space or index space are assigned with Page Set Id know as PSID. A database is assigned with Database identifier known as DBID.
DBD thus stores information about database whereas OBD is used to store information about the DB2 objets like TS,Table,Index etc....


What is a page in Db2: Db2 internally continues read/write operation in terms of equal size units known as pages.
The size can vary from 4k,8k,16l,32k.  The default page size is 4K.

How many types of table spaces are there in DB2?
Fundamentally the table space is the storage  unit for recovery and re-organization purposes; i.e. it is the unit that can be recovered  via the recover utility . If  the table space is very large , however , recovery and re-organization could take a very long time.
There can be Simple, Segmented, partioned, universal, XML, Large Object Table spaces in DB2.

Simple Table Space are obsolete now a day. It can contain rows  of one or more tables. When many tables are defined on a simple table space, one single page contains all the rows from the tables.A simple  TS can be created by issuing the CREATE TABLESPACE statement without specifying either the SEGSIZE or NUMPARTS clause.
One reason for having more than one is that stored records for different stored tables can be clustered together in such a way as to improve access times to logically related data.In  particular , certain join queries would then be handled more efficiently since the I/O operations would be reduced
Disadvantage of a simple table space is that it unnecessarily locks  rows of other tables which are there in that page even when operation is being performed on some other table.
Command to Create a Tablespace
CREATE DATABASE "DBTEST1"
   STOGROUP   STTEST1
   BUFFERPOOL BT0;

COMMIT;

CREATE TABLESPACE "REOT001" IN "DBTEST1"
   USING  STOGROUP   STTEST1
          PRIQTY     144000
          SECQTY     144000
          ERASE      NO
          BUFFERPOOL BP0
          LOCKSIZE   TABLESPACE
          CLOSE      NO
COMMIT;


Segmented Table space: Preferred way to build a table space.A table space that is segmented is useful for storing more than one table, especially relatively small tables. The pages hold segments, and each segment holds records from only one table. Segmented table spaces hold a maximum of 64 GB of data and can contain one or more VSAM data sets. The Basic Difference between simple table space and segmented Table spaces is ,segmented table spaces do not support any kind of cross table clustering- that is they do not allow records for different stored tables to be interleaved on a single page. Instead , they keep the tables  physically separated.
A segmented table space can be built using the  SEGSIZE clause. The value indicates the pagesize
in each segment.

CREATE TABLESPACE "REOT001" IN "DBTEST1"
   USING  STOGROUP   STTEST1
          PRIQTY     144000
          SECQTY     144000
          ERASE      NO
          BUFFERPOOL BP0
          LOCKSIZE   TABLESPACE
          CLOSE      NO
      SEGSIZE    24;

COMMIT;
We can keep some free pages in segment using the FREEPAGE clause with a value less than that of
SEGSIZE.Defining small tables in a table space with a large segment size could result in wasted DASD space.
If we specify the FREEPAGE to be greater than SEGSIZE, the FREEPAGE will be calculated as
SEGSIZE -1.

Partitioned tablespace: A table space that is partitioned stores a single table.
Partitioned table spaces are intended for stored tables that are sufficiently large. A partitioned table space thus contains exactly one stored table, partitioned in accordance with value ranges of a partitioning columns or column combination. Individual partitions of a partitioned table space are independent of each other in the sense that they can be independently recovered or reorganized.
They can also be associated with different storage groups i.e. it is possible to store different partitions on different devices and thereby spread the table space I/O load.

CREATE LARGE TABLESPACE test01TS  IN TestDB
  USING STOGROUP TestEX4
  PRIQTY 504000 SECQTY 504000
   FREEPAGE 0 PCTFREE 0
  GBPCACHE CHANGED
  TRACKMOD YES
  NUMPARTS  40
  BUFFERPOOL BPX
  LOCKSIZE ANY
  CCSID    EBCDIC
  MAXROWS 255

use of keyword  NUMPARTS indicates the partitioned tablespace.

INDEX SPACE : index space is to an index what a table space is to a stored table. However, since the correspondence between indexes  and index spaces is always one to one, there are no data definition statements for index spaces; instead, the necessary index space parameters
are specified on the corresponding index definition statements. Thus for e.g. there is no create
index space; instead the index space is created automatically when the corresponding index is created. Like Table spaces ,index spaces can be reorganized and recovered independently.

Friday, November 8, 2013

DB2 In Built Functions - Column function and Scalar Function

Column functions
1.Column functions can be executed in SELECT Statement
2. Each Column function returns only one value for the set of rows
3.If we apply a column function to one column in a select Statement,we must apply column functions to all the columns specified in the same select statement, unless the GROUP BY clause is used.
4. Result of the column function will have the same data type like the column on which it was applied.
5. Column function will never return a SQLCODE +100. It returns NULL value if the where clause does not fetches any data. 
But COUNT and COUNT_BIG function returns value, not NULL value. 
So lets look into the below query which theoretically is doing the same thing, but results will be different.
Thus AVG(SALARY)   and   SUM(SALARY)/ COUNT(*)  will be different.
The reason being, count function takes all the rows into account, whereas SUM function ignores the rows that has null values.

The COUNT function and COUNT_BIG function: The COUNT_BIG function is same as COUNT function except that it returns a decimal value.
Ex: select COUNT(*) from EMP_TBL   or Select COUNT_BIG(*) from EMP_TBL

The MAX and MIN functions: MAX will give the largest value in the expression and MIN will give the smallest one.The Result is of the same data type on which it is applied.
It can not be applied on data types like CLOB, BLOB, DBCLOB

STDDEV function returns the standard deviation of  a set of number
ex:  select stddev(salary)  from emp where dept='cse'

SUM function gives the total of values specified in the expression.
ex:  select SUM(SALARY+BONUS) FROM EMP  where month='Oct'

Scalar  functions :
Scalar functions operate on single value as compared to column function.
Below are the Queries along with their output for few scalar functions.

SELECT LEFT('THIS IS VALUE',2) FROM SYSIBM.SYSDUMMY1;
TH
                                                           
SELECT (RAND()*100) FROM SYSIBM.SYSDUMMY1;
+0.3388811137242620E+02

SELECT LOCATE('S','INDIA IS GREAT') FROM SYSIBM.SYSDUMMY1;
 8  
Returns the position of first occurrence of 'S' in the string

SELECT NEXT_DAY(CURRENT DATE,'FRIDAY') FROM SYSIBM.SYSDUMMY1;
2013-11-15-00.00.00.000000

SELECT REPLACE('BATATA','TA','NA')  FROM SYSIBM.SYSDUMMY1;
BANANA

SELECT DATE('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
2013-11-15

SELECT WEEK('2013-12-25-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
52
SELECT YEAR('2013-12-25-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
2013

SELECT DAY('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
15 

SELECT LAST_DAY('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
2013-11-30


SELECT MONTH('2013-11-15-00.00.00.889924') FROM SYSIBM.SYSDUMMY1;
11

SELECT TIME('2013-11-15-00.00.00.889924') FROM SYSIBM.SYSDUMMY1;
00.00.00

SELECT HOUR('2013-11-15-23.22.21.056788') FROM SYSIBM.SYSDUMMY1;
23

SELECT SECOND('2013-11-11-00.00.13.0000') FROM  SYSIBM.SYSDUMMY1
13

SELECT MICROSECOND('2013-11-15-00.00.00.889924') FROM SYSIBM.SYSDUMMY1;
889924 

SELECT JULIAN_DAY('2013-11-15') FROM SYSIBM.SYSDUMMY1;
2456612

SELECT DAYOFWEEK('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;

Represents the day of the week.Like 1 for Sunday,2 Monday

SELECT DAYOFYEAR('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
319 

SELECT GRAPHIC('319') FROM SYSIBM.SYSDUMMY1;
.â3â1â9.

SELECT LCASE('JIM') FROM SYSIBM.SYSDUMMY1;
jim

SELECT LTRIM('     JIM') FROM SYSIBM.SYSDUMMY1;
JIM
Trims the space from the string.

SELECT HEX('RI5') FROM SYSIBM.SYSDUMMY1;
D9C9F5

SELECT INT(123.90) FROM SYSIBM.SYSDUMMY1;
123 

SELECT CHAR(CURRENT DATE,USA) FROM SYSIBM.SYSDUMMY1
11/11/2013

Displays  the current date in the USA date format 

SELECT CONCAT('CON','CAT') FROM SYSIBM.SYSDUMMY1
CONCAT


SELECT INSERT('JIM  PRAT', 5, 0 ,'NORM') FROM SYSIBM.SYSDUMMY1 
JIM NORM PRAT 
Accepts 4 arguments. Returns the string which contains the 4th argument inserted into the 1st argument at the position specified by the 2rd argument. 3rd arg specifies the number of bytes to delete

SELECT QUARTER('2013-11-11-00.00.00.0000') FROM  SYSIBM.SYSDUMMY1
4

Vaue of 1=1s quarter,2=2nd quarter,3=3rd quarter,4=4th quarter

Tuesday, November 5, 2013

Lock Escalation and Lock Promotion in DB2

Lock escalation:
 Locks are data structures that indicate which part of the system (row, page, table, tablespace, database, ...) is protected in some way.  DB2 remembers this information in memory. Blocking the whole database all the time when an application accesses a small part of it doesn't make much sense. So if you have an application that deals with a lot of data, it potentially has to have many locks. Those locks accumulate and need space in memory.

So when the list is almost full DB2 does a "lock escalation". That means, many row-level locks held by one transaction on a table are escalated to a single table-level lock, for example. Instead of having 1000s or millions of locks (per row), it is better to have lock on that table , there by relieving the pressure on the lock list.

When lock Escalation Happens:
As the number of locks held by any one application reaches near to MAXLOCKS percentage on the create tablespace statement.(If we remeber we use this paramter on table space DDL statement). Database manager will trigger lock escalation and will continue until the percentage of the lock list held is below the value of MAXLOCKS. Lock escalation also occurs if the LOCKLIST runs out of space.  

How DB2 Handles this scenario:
The database manager determines which locks to escalate by looking through the lock list for the application and finding the table with the most row locks.
Row locks will be replacing with a single table lock. It will keep repeating till MAXLOCKS value is no longer exceeded.

Summary  :
Lock escalation can occur for objects defined with LOCKSIZE ANY, PAGE, or ROW. Value ANY just gives DB2 the choice of what lock to take initially.
If we use LOCKSIZE ANY, LOCKMAX SYSTEM , it means means it uses the number set by the NUMLKTS DSNZPARM.We can turn off lock escalation by setting LOCKMAX to zero.If we choose this approach, be sure the applications that access the objects are committing frequently, and adjust DSNZPARM NUMLKTS to permit more locks to be taken. Otherwise, we risk hitting negative SQL codes when you reach the maximum number of locks.
Lock escalation is thus a way to protect from taking excessive system resources, so if we turn it off, we need to control it.
If we remember the locking modes and the lock sizes, this is how it works:
Page lock and Row lock escalate to Table level and then to Tables space level for Segmented tables space.
But for partioned tables spaces, the page/row lock escalates straight to the Tables space level.
A page and Row lock occurs at the same level.They can not be escalated to each other, ie ROW<-->PAGE

Lock promotion:
Lock promotion is the action of exchanging one lock on a resource for a more restrictive lock on the same resource, held by the same application process. We can say, it is the process of changing the lock mode or lock size to a more restrictive level. See the below example .


Example:An application reads data, which requires an IS lock on a table space. Based on further calculation, the application updates the same data, which requires an IX lock on the table space. The application is said to promote the table space lock from mode IS to mode IX.(ie Intent share to intent Exclusive)
Note:  LOCKSIZE ANY will cause DB2 to do page level locking
Want to refresh about the lock modes and intent lock? check out Locking  again.

Inportant information about DB2 LOCK
1. DSNZPARM starts up the parameters for DB2.
2.The LOCKMAX parameter of the create  or ALTER TABLESPACE can be seen in the
MAXROWS column of the SYSIBM.SYSTABLESPACE.
3. If a single user accumulates more page locks than allowed by DSNZPARM, we get a SQLCODE of -904.(resource unavailable)
4.All the page locks held for data in segmented table space are escalated to table locks.
 All the page locks held for data in partitioned table space are escalated to table space locks.


5. Why do we get SQLCODE -904 ?
NUMLKUS parameter in DSNZPARM defines the threshold for the total number of page locks across all table space that can be held concurrently by a single DB2 application.When any other application tries to attempt lock that cause the application to exceed the NUMLKUS threshold,the application receives SQLCODE -904.

Monday, October 28, 2013

CHAPTER 6 (CONCURRENCY) DB2 9 Exam 730 certification Dumps


730 CERTIFICATION GUIDE

Q. Which of the following isolation levels will lock all rows scanned to build a result data set?
A. Uncommitted Read
B. Cursor Stability
C. Read Stability
D. Repeatable Read


Q.Application A holds an Exclusive lock on table TAB1 and needs to acquire an Exclusive lock on table TAB2. Application B holds an Exclusive lock on table TAB2 and needs to acquire an Exclusive lock on table TAB1. If lock timeout is set to -1 and both applications are using the Read Stability isolation level, which of the following will occur?
A. Applications A and B will cause a deadlock situation
B. Application B will read the copy of table TAB1 that was loaded into memory when Application A first read it
C. Application B will read the data in table TAB1 and see uncommitted changes made by Application A
D. Application B will be placed in a lock-wait state until Application A releases its lock


Q.A transaction using the Read Stability isolation level scans the same table multiple times before it terminates. Which of the following can occur within this transaction's processing?
A. Uncommitted changes made by other transactions can be seen from one scan to the next.
B. Rows removed by other transactions that appeared in one scan will no longer appear in subsequent scans.
C. Rows added by other transactions that did not appear in one scan can be seen in subsequent scans.
D. Rows that have been updated can be changed by other transactions from one scan to the next.


Q.Two applications have created a deadlock cycle in the locking subsystem. If lock timeout is set to 30 and both applications were started at the same time, what action will the deadlock detector take when it "wakes up" and discovers the deadlock?
A. It will randomly pick an application and rollback its current transaction
B. It will rollback the current transactions of both applications
C. It will wait 30 seconds, then rollback the current transactions of both applications if the deadlock has not been resolved
D. It will go back to sleep for 30 seconds, then if the deadlock still exists, it will randomly pick an application and rollback its current transaction


Q.Application A is running under the Repeatable Read isolation level and holds an Update lock on table TAB1. Application B wants to query table TAB1 and cannot wait for Application A to release its lock. Which isolation level should Application B run under to achieve this objective?
A. Repeatable Read
B. Read Stability
C. Cursor Stability
D. Uncommitted Read


Q.Which of the following DB2 UDB isolation levels will only lock rows during read processing if another transaction tries to drop the table the rows are being read from?
A. Repeatable Read
B. Read Stability
C. Cursor Stability
D. Uncommitted Read


Q.Application A holds a lock on a row in table TAB1. If lock timeout is set to 20, what will happen when Application B attempts to acquire a compatible lock on the same row?
A. Application B will acquire the lock it needs
B. Application A will be rolled back if it still holds its lock after 20 seconds have elapsed
C. Application B will be rolled back if Application A still holds its lock after 20 seconds have elapsed
D. Both applications will be rolled back if Application A still holds its lock after 20 seconds have elapsed


Q.To which of the following resources can a lock NOT be applied?
A. Table spaces
B. Buffer pools
C. Tables
D. Rows
 

Q.Which of the following causes a lock that is being held by an application using the Cursor Stability isolation level to be released?
A. The cursor is moved to another row
B. The row the cursor is on is deleted by the application
C. The row the cursor is on is deleted by another application
D. The row the cursor is on needs to be updated by another application

 
Q.Which of the following modes, when used with the LOCK TABLE statement, will cause the DB2 Database Manager to acquire a table-level lock that prevents other concurrent transactions from accessing data stored in the table while the owning transaction is active?
A. SHARE MODE
B. ISOLATED MODE
C. EXCLUSIVE MODE
D. RESTRICT MODE


Q.An application has acquired a Share lock on a row in a table and now wishes to update the row. Which of the following statements is true?
A. The application must release the row-level Share lock it holds and acquire an Update lock on the row
B. The application must release the row-level Share lock it holds and acquire an Update lock on the table
C. The row-level Share lock will automatically be converted to a row-level Update lock
D. The row-level Share lock will automatically be escalated to a table-level Update lock







Q.Application A wants to read a subset of rows from table TAB1 multiple times. Which of the following isolation levels should Application A use to prevent other users from making modifications and additions to table TAB1 that will affect the subset of rows read?
A. Repeatable Read
B. Read Stability
C. Cursor Stability
D. Uncommitted Read


Q.Which of the following resources can be explicitly locked?
A.Row
B.Page
C.Table
D.Column

Q.Application A issues the following SQL statements within in a single transaction using the Uncommitted Read isolation level:
SELECT * FROM employee WHERE deptno='A00';
UPDATE employee SET salary = salary * 1.1 WHERE deptno='A00';
As long as the transaction has not been committed, which of the following is true for all other applications not running under the Uncommitted Read isolation level?

A.They can read and make changes to data for any employee in department A00.
B.They can read but not make changes to data for any employee in department A00.
C.They must wait until the transaction is committed before accessing data of any employee in department A00.
D.They must wait until application A disconnects before accessing data of any employee in department A00.

Q. Which of the following best describes how locks are used in DB2?
A.To maintain control of updated rows for commit processing
B.To ensure only committed changes are altered by another application
C.To allow two applications to update the same row of data simultaneously
D.To prevent multiple applications from accessing the same data simultaneously

Q. If an application issues the same query more than once in the same Unit of Work, which isolation level will not permit this application to see additional rows inserted by other applications?

 A.Read Stability (RS)
B.Repeatable Read (RR)
C.Uncommitted Read (UR)
C.Cursor Stability (CS)

Q. If DB2 detects a deadlock between application A and application B (each waiting for a lock held by the other one), what action will DB2 take?

A.Rollback the transaction in both applications.
B.Rollback the transaction in one of the two applications.
C.Decrease the lock timeout value for both applications, thereby causing a lock timeout situation.
D.Send a message to let the administrator know of the situation and decide which application or applications should be terminated.

Q. How does DB2 protect the integrity of indexes when data is updated?
A.Locks are acquired on the data.
B.Locks are acquired on index keys.
C.Locks are acquired on index pages.
D.Locks are acquired on index pointers.

Q. Application A currently holds an exclusive lock on a single row and application B tries to access that row. If lock timeout is set to -1 and both applications are using isolation level RS, which of the following will occur?

A.Applications A and B will cause a deadlock situation.
B.Application B will read the previous version of the row.
C.Application B will read the row and see uncommitted changes made by application A.
D.Application B will be placed in a lock-wait state until application A releases its lock.


Q. A table contains a list of all seats available at a football stadium. A seat consists of a section number, a seat number, and whether or not the seat has been assigned. A ticket agent working at the box office generates a list of all unassigned seats. When the agent refreshes the list, it should only change if another agent assigns one or more unassigned seats. Which of the following is the best isolation level to use for this application?
A. Repeatable Read
B. Read Stability
C. Cursor Stability
D. Uncommitted Read

CHAPTER 5 (WORKING WITH DB2 TABLE,VIEWS,AND INDEXES) DB2 9 Exam 730 certification Dumps

Q.Which of the following is a valid DB2 data type?
A. NUMBER
B. INTERVAL
C. BYTE
D. NUM

Q.Which of the following DB2 data types does NOT have a fixed length?
A. INT
B. CHAR
C. XML
D. DOUBLE



Q.Which of the following is the best statement to use to create a user-defined data type that can be used to store currency values?
A. CREATE DISTINCT TYPE currency AS NUMERIC(7,2)
B. CREATE DISTINCT TYPE currency AS SMALLINT
C. CREATE DISTINCT TYPE currency AS BIGINT
D. CREATE DISTINCT TYPE currency AS DOUBLE

Q.Which of the following DB2 data types can be used to store 1000 MB of single-byte character data?
A. BLOB
B. CLOB
C. DBCLOB
D. GRAPHIC

Q.Which of the following DB2 data types can NOT be used to create an identity column?
A. SMALLINT
B. INTEGER
C. NUMERIC
D. DOUBLE


Q.Which of the following strings can NOT be inserted into an XML column using XMLPARSE()?
A. "<employee />"
B. "<name>John Doe</name>"
C. "<?xml version='1.0' encoding='UTF-8' ?>"
D. "<p></p>"

 

Q.Given the requirements to store employee names, employee numbers, and when employees were hired, which of the following built-in data types CANNOT be used to store the day an employee was hired?
A. Character Large Object
B. Time
C. Varying-Length Character String
D. Timestamp
 

Q.Given the requirements to store customer names, billing addresses, and telephone numbers, which of the following would be the best way to define the telephone number column for a table if all customers were located in the same country?
PHONE    CHAR(15)
PHONE    VARCHAR(15)
PHONE    LONG VARCHAR
PHONE    CLOB(1K)


Q. Which of the following strings can NOT be inserted into an XML column using XMLPARSE()?
A. "<employee />"
B. "<name>John Doe</name>"
C. "<?xml version='1.0' encoding='UTF-8' ?>"
D. "<p></p>"


Q. Which two of the following are optional and do not have to be specified when creating a table?
A. Table name
B. Column name
C. Default constraint
D. Column data type
E. NOT NULL constraint


Q.Which of the following is a NOT a valid reason for defining a view on a table?
A. Restrict users' access to a subset of table data
B. Ensure that rows inserted remain within the scope of a definition
C. Produce an action as a result of a change to a table
D. Provide users with an alternate view of table data


Q.Given the following CREATE TABLE statement:
CREATE TABLE table2 LIKE table1
Which two of the following will NOT occur when the statement is executed?
A. TABLE2 will have the same column names and column data types as TABLE1
B. TABLE2 will have the same column defaults as TABLE1
C. TABLE2 will have the same nullability characteristics as TABLE1
D. TABLE2 will have the same indexes as TABLE1.
E. TABLE2 will have the same referential constraints as TABLE1


Q.  If the following SQL statements are executed:
CREATE TABLE tab1 (id SMALLINT NOT NULL PRIMARY KEY,
                   name  VARCHAR(25));

CREATE TABLE tab2 (empid   SMALLINT,
                   weekno  SMALLINT,
                   payamt  DECIMAL(6,2),
    CONSTRAINT const1 FOREIGN KEY (empid)
        REFERENCES taba(id) ON UPDATE NO ACTION);


Which of the following statements is true?
A. Only values that exist in the ID column of table TAB1 are allowed to be inserted in the EMPID column of table TAB2
B. The updating of values in the ID column of table TAB1 is not allowed
C. Only values that do not already exist in the ID column of table TAB1 are allowed to be inserted in the EMPID column of table TAB2
D. When values that exist in the ID column of table TAB1 are updated, corresponding values in the EMPID column of table TAB2 are updated as well

CHAPTER 4 (WORKING WITH DB2 USING SQL AND XQUERY) DB2 9 Exam 730 certification Dumps

730 CERTIFICATION GUIDE

Q.Given the following two tables:
         NAMES
-----------------------------
NAME                 NUMBER
----------                -------
Wayne Gretzky        99
Jaromir Jagr             68
Bobby Orr                 4
Bobby Hull              23
Mario Lemieux        66

        POINTS
-----------------------------
NAME                 POINTS
----------                 ------
Wayne Gretzky        244
Bobby Orr               129
Brett Hull                 121
Mario Lemieux        189
Joe Sakic                    94
How many rows would be returned using the following statement?
SELECT name FROM names, points
A. 0
B. 5
C. 10
D. 25

Q.Given the following CREATE TABLE statement:
CREATE TABLE EMPLOYEE
 (EMPNO       CHAR(3) NOT NULL,
  FIRSTNAME   CHAR(20) NOT NULL,
  MIDINIT     CHAR(1),
  LASTNAME    CHAR(20) NOT NULL,
  SALARY      DECIMAL(10, 2))
Which of the following will retrieve the rows that have a missing value in the MIDINIT column?
A. SELECT * FROM employee WHERE midinit = ' '
B. SELECT * FROM employee WHERE midinit = NULL
C. SELECT * FROM employee WHERE midinit = " "
D. SELECT * FROM employee WHERE midinit IS NULL

Q.Given the following two tables:
       TAB1
----------------------
COL_1         COL_2
-----         -----
A             10
B             12
C             14

       TAB2
----------------------
COL_A         COL_B
-----         -----
A             21
C             23
D             25
Assuming the following results are desired:
COL_1            COL_2            COL_A            COL_B
A                    10                        A                21
B                    12                         -                -
C                    14                        C                23
-                -                                D                25
Which of the following joins will produce the desired results?
A. SELECT * FROM tab1 INNER JOIN tab2 ON col_1 = col_a
B. SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON col_1 = col_a
C. SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON col_1 = col_a
D. SELECT * FROM tab1 FULL OUTER JOIN tab2 ON col_1 = col_a

Q.If the following SQL statements are executed in the order shown:
CREATE TABLE table1 (c1 INTEGER, c2 INTEGER);
INSERT INTO table1 VALUES (123, 456);
UPDATE table1 SET c1 = NULL;
What will be the result of the following statement?
SELECT * FROM table1;
A.
C1    C2
---   ---
123   456
1 record(s) selected.
B.
C1    C2
---   ---
NULL  456
1 record(s) selected.
C.   
C1    C2
---   ---
-     456
1 record(s) selected.

D.
C1    C2
---   ---
0     456
1 record(s) selected.
Q.Given the following table:
TAB1

COL1         COL2
-----        -----
A            10
B            20
C            30
A            10
D            40
C            30
Assuming the following results are desired:
TAB1

COL1         COL2
-----        -----
A            10
B            20
C            30
D            40
Which of the following statements will produce the desired results?
A. SELECT UNIQUE * FROM tab1
B. SELECT DISTINCT * FROM tab1
C. SELECT UNIQUE(*) FROM tab1
D. SELECT DISTINCT(*) FROM tab1

Q.Assuming table TAB1 contains 100 rows, which of the following queries will return only half of the rows available?
A. SELECT * FROM tab1 FIND FIRST 50 ROWS
B. SELECT * FROM tab1 FETCH FIRST 50 ROWS ONLY
C. SELECT * FROM tab1 WHILE ROW_NUM < 50
D. SELECT * FROM tab1 MAXROWS 50

Q.Given the following two tables:
EMPLOYEE

ID NAME           DEPTID
-- ---------------- ---
01 Mick Jagger     10
02 Keith Richards  20
03 Ronnie Wood     20
04 Charlie Watts   20
05 Bill Wyman      30
06 Brian Jones    -

DEPARTMENT

ID DEPTNAME
-- ------------------
10 Executive Staff
20 Sales
30 Marketing
40 Engineering
50 Human Resources
Which two of the following queries will display the employee name and department name for all employees that are in Sales?
A.    SELECT e.name, d.deptname
FROM employee e, department d
WHERE e.deptid = d.id AND d.id = '20'

B.    SELECT e.name, d.deptname
FROM employee e FULL OUTER JOIN department d
ON e.deptid = d.id
WHERE d.id = '20'
C.    SELECT e.name, d.deptname
FROM employee e RIGHT OUTER JOIN department d
ON e.deptid = d.id
WHERE d.id = '20'
D.    SELECT e.name, d.deptname
FROM employee e LEFT OUTER JOIN department d
ON e.deptid = d.id
WHERE d.id = '20'
E.    SELECT e.name, d.deptname
FROM employee e INNER JOIN department d
ON e.deptid = d.id
WHERE d.id = '20'


Q.Given the following queries:
SELECT c1 FROM tab1;
SELECT c1 FROM tab2;
Which of the following set operators can be used to produce a result data set that contains only records that are not found in the result data set produced by each query after duplicate rows have been eliminated?
A. UNION
B. INTERSECT
C. EXCEPT
D. MERGE

Q.Given the following two tables:
NAMES

NAME                   NUMBER
----------                  -------
Wayne Gretzky          99
Jaromir Jagr               68
Bobby Orr                  4
Bobby Hull               23
Brett Hull                  16
Mario Lemieux         66
Mark Messier           11

POINTS

NAME                   POINTS
----------                  ------
Wayne Gretzky       244
Jaromir Jagr           168
Bobby Orr              129
Brett Hull               121
Mario Lemieux      189
Joe Sakic                 94
Which of the following statements will display the player name, number, and points for all players that have scored points?
A. SELECT p.name, n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name
B. SELECT p.name, n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name
C. SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name
D. SELECT p.name, n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name

Q.Which of the following is a valid wildcard character in a LIKE clause of a SELECT statement?
A. %
B. *
C. ?
D. \

Q.Given the following tables:
YEAR_2006

EMPID  NAME
-----  ---------------
1      Jagger, Mick
2      Richards, Keith
3      Wood, Ronnie
4      Watts, Charlie
5      Jones, Darryl
6      Leavell, Chuck

YEAR_1962

EMPID  NAME
-----  ---------------
1      Jagger, Mick
2      Richards, Keith
3      Jones, Brian
4      Wyman, Bill
5      Watts, Charlie
6      Stewart, Ian
If the following SQL statement is executed, how many rows will be returned?
SELECT name FROM year_2007
UNION ALL
SELECT name FROM year_1962
A. 6
B. 9
C. 10
D. 12

Q.Given the following table definition:
SALES
--------------------------------------
INVOICE_NO          CHAR(20) NOT NULL
SALES_DATE          DATE
SALES_PERSON        VARCHAR(25)
REGION              CHAR(20)
SALES_AMT           DECIMAL(9,2)
Which of the following queries will return SALES information, sorted by SALES_PERSON, from A to Z, and SALES_DATE, from most recent to earliest?
A. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person, sales_date DESC
B. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person DESC, sales_date
C. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person, sales_date DESC
D. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person DESC, sales_date

Q.Given the following statement:
SELECT hyear, AVG(salary)
FROM (SELECT YEAR(hiredate) AS hyear, salary
              FROM employee WHERE salary > 30000)
GROUP BY hyear
Which of the following describes the result if this statement is executed?
A. The statement will return the year and average salary for all employees that have a salary greater than $30,000, sorted by year.
B. The statement will return the year and average salary for all employees hired within a given year that have a salary greater than $30,000.C. The statement will return the year and average salary for all years that every employee hired had a salary greater than $30,000.
D. The statement will return the year and average salary for all years that any employee had a salary greater than $30,000.

Q.Which two of the following statements are true about the HAVING clause?
A. The HAVING clause is used in place of the WHERE clause.
B. The HAVING clause uses the same syntax as the WHERE clause.
C. The HAVING clause can only be used with the GROUP BY clause.
D. The HAVING clause accepts wildcards.
E. The HAVING clause uses the same syntax as the IN clause.

Q.Given the following table definitions:
DEPARTMENT
--------------------------
DEPTNO       CHAR(3)
DEPTNAME     CHAR(30)
MGRNO        INTEGER
ADMRDEPT     CHAR(3)

EMPLOYEE
--------------------------
EMPNO        INTEGER
FIRSTNAME    CHAR(30)
MIDINIT      CHAR
LASTNAME     CHAR(30)
WORKDEPT     CHAR(3)
Which of the following statements will list every employee number and last name, along with the employee number and last name of their manager, including employees that have not been assigned to a manager?
A. SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e LEFT INNER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno
B. SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e LEFT OUTER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno
C. SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e RIGHT OUTER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno
D. SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e RIGHT INNER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno

Q.Given the following table:
EMPLOYEE

EMPID     NAME            INSTRUMENT
---       ---------------         -------------------
1         Jagger, Mick           01
2         Richards, Keith      02
3         Wood, Ronnie        02
4         Watts, Charlie        03
5         Jones, Darryl         04
6         Leavell, Chuck      05
If the following query is executed:
SELECT name,
   CASE WHEN instrument = '01' THEN 'HARMONICA'
     WHEN instrument = '02' THEN 'GUITAR'
     WHEN instrument = '03' THEN 'DRUMS'
     ELSE 'UNKNOWN'
   END AS instrument
FROM employee
What will be the results?
A.
NAME                INSTRUMENT
---------------        --------
Jagger, Mick       HARMONICA
Richards, Keith   GUITAR
Wood, Ronnie     GUITAR
Watts, Charlie     DRUMS
Jones, Darryl       ERROR
Leavell, Chuck    ERROR
B.
NAME                 INSTRUMENT
------------------     --------
Jagger, Mick        HARMONICA
Richards, Keith    GUITAR
Wood, Ronnie      GUITAR
Watts, Charlie     DRUMS
Jones, Darryl      04
Leavell, Chuck   05
C.   
NAME               INSTRUMENT
------------------ --------
Jagger, Mick       HARMONICA
Richards, Keith    GUITAR
Wood, Ronnie       GUITAR
Watts, Charlie     DRUMS
Jones, Darryl      UNKNOWN
Leavell, Chuck     UNKNOWN

D.   
NAME               INSTRUMENT
------------------ --------
Jagger, Mick       HARMONICA
Richards, Keith    GUITAR
Wood, Ronnie       GUITAR
Watts, Charlie     DRUMS
Jones, Darryl      -
Leavell, Chuck     -

Q.Given the following UPDATE statement:
UPDATE employees SET workdept =
   (SELECT deptno FROM department WHERE deptno = 'A01')
    WHERE workdept IS NULL
Which of the following describes the result if this statement is executed?
A. The statement will fail because an UPDATE statement cannot contain a subquery.
B. The statement will only succeed if the data retrieved by the subquery does not contain multiple records.
C. The statement will succeed; if the data retrieved by the subquery contains multiple records, only the first record will be used to perform the update.
D. The statement will only succeed if every record in the EMPLOYEES table has a null value in the WORKDEPT column.

Q.Given the following table:
CURRENT_EMPLOYEES
--------------------------
EMPID        INTEGER NOT NULL
NAME         CHAR(20)
SALARY       DECIMAL(10,2)

PAST_EMPLOYEES
--------------------------
EMPID        INTEGER NOT NULL
NAME         CHAR(20)
SALARY       DECIMAL(10,2)
Assuming both tables contain data, which of the following statements will NOT successfully add data to table CURRENT_EMPLOYEES?
A. INSERT INTO current_employees (empid) VALUES (10)
B. INSERT INTO current_employees VALUES (10, 'JAGGER', 85000.00)
C. INSERT INTO current_employees SELECT empid, name, salary FROM past_employees WHERE empid = 20
D. INSERT INTO current_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20)

Q.Given the following table definition:
SALES
--------------------------
SALES_DATE       DATE
SALES_PERSON     CHAR(20)
REGION           CHAR(20)
SALES            INTEGER
Which of the following SQL statements will remove all rows that had a SALES_DATE in the year 1995?
A. DELETE * FROM sales WHERE YEAR(sales_date) = 1995
B. DELETE FROM sales WHERE YEAR(sales_date) = 1995
C. DROP * FROM sales WHERE YEAR(sales_date) = 1995
D. DROP FROM sales WHERE YEAR(sales_date) = 1995

Q.Given the following table definition:
EMPLOYESS
--------------------------
EMP ID             INTEGER
NAME              CHAR(20)
DEPT               CHAR(10)
SALARY          DECIMAL (10, 2)
COMMISSION DECIMAL (8, 2)
Assuming the DEPT column contains the values ‘ADMIN’, ‘PRODUCTION’, and ‘SALES’, which of the following statements will produce a result data set in which all ADMIN department employees are grouped together, all PRODUCTION department employees are grouped together, and all SALES department employees are grouped together?
A. SELECT name, dept FROM employees ORDER BY dept
B. SELECT name, dept FROM employees GROUP BY dept
C. SELECT name, dept FROM employees GROUP BY ROLLUP (dept)
D. SELECT name, dept FROM employees GROUP BY CUBE (dept)

Q.The following SQL statement:
DELETE FROM tab1 WHERE CURRENT OF csr1 WITH RR
Is used to perform which type of delete operation?
A. Positioned
B. Searched
C. Embedded
D. Dynamic 


Q.Given the following data:
TAB1

C1    C2
--      ---
200  abc
250  abc
150  def
300  ghi
175  def
If the following query is executed:
WITH subset (col1, col2) AS
    (SELECT c1, c2 FROM tab1 WHERE c1 > 150)
SELECT col2, SUM(col1) AS col1_sum
  FROM subset
  GROUP BY col2
  ORDER BY col2
Which of the following result data sets will be produced?
A.  
COL2      COL1_SUM
----          --------
abc         200
abc         250
def         175
ghi         300
4 record(s) selected.
B.  
COL2     COL1_SUM
----         --------
abc         450
def         175
ghi         300
3 record(s) selected.

C.  
COL2     COL1_SUM
----          --------
abc         450
def         325
ghi         300
3 record(s) selected.
D.  
COL2     COL1_SUM
----         --------
abc         450
abc         450
def         175
def         175
ghi         300
5 record(s) selected.


Q.Given the following table definitions:
TABLE1
-----------------------------------
ID                   INT
NAME           CHAR(30)
PERSON       INT
CITIES          INT


TABLE2
-----------------------------------
ID                      INT
LASTNAME    CHAR(30)


Which of the following statements will remove all rows in table TABLE1 that have matching PERSONs in table TABLE2?
A. DELETE FROM table1 WHERE id IN (SELECT id FROM table2)
B. DELETE FROM table1 WHERE id IN (SELECT person FROM table2)
C. DELETE FROM table1 WHERE person IN (SELECT id FROM table2)
D. DELETE FROM table1 WHERE person IN (SELECT person FROM table2)


Q.Given the following two tables:
NAMES


NAME                NUMBER
-----------             --------------

Wayne Gretzky     99
Jaromir Jagr          68
Bobby Orr              4
Bobby Hull           23
Brett Hull             16
Mario Lemieux    66
Mark Messier      11

POINTS

NAME                   POINTS
-----------                --------------
Wayne Gretzky      244
Jaromir Jagr           168
Bobby Orr              129
Brett Hull               121
Mario Lemieux      189
Joe Sakic                94

Which of the following statements will display the player name, number, and points for all players that have scored points?
A. SELECT p.name, n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name
B. SELECT p.name, n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name
C. SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name
D. SELECT p.name, n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name


Q.Given the following table definitions:
EMPLOYEES
------------------------------------------------
EMPID                          INTEGER
NAME                          CHAR(20)
DEPTID                        CHAR(3)
SALARY                      DECIMAL(10,2)
COMMISSION             DECIMAL(8,2)
DEPARTMENTS
------------------------------------------------
DEPTNO                      INTEGER
DEPTNAME                CHAR(20)
Which of the following statements will produce a result data set that satisfies all of these conditions:
> Displays the total number of employees in each department
>> Displays the corresponding department name for each department ID
>> Sorted by department employee count, from greatest to least


A. SELECT *, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC
B. SELECT deptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC
C. SELECT deptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 ASC
D. SELECT deptname, COUNT(*) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2


Q.Given the following table:
CURRENT_EMPLOYEES
--------------------------------------
EMPID INTEGER NOT NULL
NAME CHAR(20)
SALARY DECIMAL(10,2)
PAST_EMPLOYEES
--------------------------------------
EMPID INTEGER NOT NULL
NAME CHAR(20)
SALARY DECIMAL(10,2)
Assuming both tables contain data, which of the following statements will NOT successfully add data to table CURRENT_EMPLOYEES?
A. INSERT INTO current_employees (empid) VALUES (10)
B. INSERT INTO current_employees VALUES (10, ‘JAGGER’, 85000.00)
C. INSERT INTO current_employees SELECT empid, name, salary FROM past_employees WHERE empid = 20
D. INSERT INTO current_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20)


Q.Given the following table:
STOCK
--------------------------
CATEGORY        CHAR(1)
PARTNO              CHAR(12)
DESCRIPTION   VARCHAR(40)
QUANTITY        INTEGER
PRICE                 DEC(7,2)
If items are indicated to be out of stock by setting DESCRIPTION to NULL and QUANTITY and PRICE to zero, which of the following statements updates the STOCK table to indicate that all items except those with CATEGORY of ‘S’ are temporarily out of stock?
A. UPDATE stock SET description = ’NULL’, quantity = 0, price = 0 WHERE category ‘S’
B. UPDATE stock SET description = NULL, SET quantity = 0, SET price = 0 WHERE category ‘S’
C. UPDATE stock SET (description, quantity, price) = (‘null’, 0, 0) WHERE category ‘S’
D. UPDATE stock SET (description, quantity, price) = (NULL, 0, 0) WHERE category ‘S’


Q.Given the following SQL statements:
CREATE TABLE tab1 (col1 INTEGER)
INSERT INTO tab1 VALUES (NULL)
INSERT INTO tab1 VALUES (1)
CREATE TABLE tab2 (col2 INTEGER)
INSERT INTO tab2 VALUES (NULL)
INSERT INTO tab2 VALUES (1)
INSERT INTO tab2 VALUES (2)
What will be the result when the following statement is executed?
SELECT * FROM tab1 WHERE col1 IN (SELECT col2 FROM tab2)
A.
COL1
----
1
1 record(s) selected.


B.
COL1
----
NULL
1
2 record(s) selected.

C.
COL1
----
-
1
2 record(s) selected.

D.
COL1
----
-
1 record(s) selected.


Q.Given the following table definition:
SALES
---------------------------------------------
INVOICE_NO           CHAR(20) NOT NULL
SALES_DATE          DATE
SALES_PERSON     CHAR(20)
REGION                   CHAR(20)
SALES                      INTEGER
If the following SELECT statement is executed, which of the following describes the order of the rows in the result data set produced?
SELECT * FROM sales
A. The rows are sorted by INVOICE_NO in ascending order.
B. The rows are sorted by INVOICE_NO in descending order.
C. The rows are ordered based on when they were inserted into the table.
D. The rows are not sorted in any particular order.


Q.Given the following tables:
YEAR_2006
EMPID NAME
---------------------------------
1                Jagger, Mick
2                Richards, Keith
3                Wood, Ronnie
4                Watts, Charlie
5                Jones, Darryl
6                Leavell, Chuck
YEAR_1962
EMPID NAME
---------------------------------
1                Jagger, Mick
2                Richards, Keith
3                Jones, Brian
4                Wyman, Bill
5                Chapman, Tony
6                Stewart, Ian
If the following SQL statement is executed, how many rows will be returned?
SELECT name FROM year_2006
UNION
SELECT name FROM year_1962
A. 0
B. 6
C. 10
D. 12


Q.Which of the following best describes a unit of work?
A. It is a recoverable sequence of operations whose point of consistency is established when a connection to a database has been established or when a mechanism known as a savepoint is created.
B. It is a recoverable sequence of operations whose current point of consistency can be determined by querying the system catalog tables.
C. It is a recoverable sequence of operations whose point of consistency is established when an executable SQL statement is processed after a connection to a database has been established or a previous transaction has been terminated.
D. It is a recoverable sequence of operations whose point of consistency is only established if a mechanism known as a savepoint is created.


Q.Given the following set of statements:
CREATE TABLE tab1 (col1 INTEGER, col2 CHAR(20));
COMMIT;
INSERT INTO tab1 VALUES (123, 'Red');
INSERT INTO tab1 VALUES (456, 'Yellow');
SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS;
DELETE FROM tab1 WHERE col1 = 123;
INSERT INTO tab1 VALUES (789, 'Blue');
ROLLBACK TO SAVEPOINT s1;
INSERT INTO tab1 VALUES (789, 'Green');
UPDATE tab1 SET col2 = NULL WHERE col1 = 789;
COMMIT;
Which of the following records would be returned by the following statement?
SELECT * FROM tab1
A.
COL1     COL2
----     -------
123      Red
456      Yellow
2 record(s) selected.
B.
COL1     COL2
----     ------
456      Yellow
1 record(s) selected.

C.
COL1     COL2
----     -----
123      Red
456      Yellow
789      -
3 record(s) selected.


D.
COL1     COL2
----     ------
123      Red
456      Yellow
789      Green
3 record(s) selected.


Q. 
Given the following table:
TAB1

COL1         COL2
-----        -----
A            10
B            20
C            30
D            40
E            50
And the following SQL statements:
DECLARE c1 CURSOR WITH HOLD FOR
SELECT * FROM tab1 ORDER BY col_1;
OPEN c1;
FETCH c1;
FETCH c1;
FETCH c1;
COMMIT;
FETCH c1;
CLOSE c1;
FETCH c1;
Which of the following is the last value obtained for COL_2?
A. 20
B. 30
C. 40
D. 50


Q.A stored procedure has been created with the following statement:
CREATE PROCEDURE proc1 (IN var1 VARCHAR(10), OUT rc INTEGER)
SPECIFIC myproc LANGUAGE SQL …
What is the correct way to invoke this procedure from the command line processor (CLP)?
A. CALL proc1 ('SALES', ?)
B. CALL myproc ('SALES', ?)
C. CALL proc1 (SALES, ?)
D. RUN proc1 (SALES, ?)


Q.Given the following table:
TEMP_DATA

TEMP     DATE
-----         -----
45           12/25/2006
51           12/26/2006
67           12/27/2006
72           12/28/2006
34           12/29/2006
42           12/30/2006
And the following SQL statement:
CREATE FUNCTION degf_to_c (temp INTEGER)
   RETURNS INTEGER
   LANGUAGE SQL
   CONTAINS SQL
   NO EXTERNAL ACTION
   DETERMINISTIC
   BEGIN ATOMIC
      DECLARE newtemp INTEGER;
      SET newtemp = temp - 32;
      SET newtemp = newtemp * 5;
      RETURN newtemp / 9;
   END
Which two of the following SQL statements illustrate the proper way to invoke the scalar function DEGF_TO_C?
A. VALUES degf_to_c(32)
B. SELECT date, degf_to_c(temp) AS temp_c FROM temp_data
C. CALL degf_to_c(32)
D. SELECT * FROM TABLE(degf_to_c(temp)) AS temp_c
E. VALUES degf_to_c(32) AS temp_c

CHAPTER 3 (WORKING WITH DATABASE&DATABASE OBJECT) DB2 9 Exam 730 certification Dumps

Q.While attempting to connect to a database stored on an iSeries server from a Windows client, the following message was displayed:
SQL1013N The database alias name or database name "TEST_DB" could not be found.

Which of the following actions can be used to help determine why this message was displayed?
A. Execute the LIST REMOTE DATABASES command on the server; look for an entry for the TEST_DB database
B. Execute the LIST DCS DIRECTORY command on the server; look for an entry for the TEST_DB database
C. Execute the LIST REMOTE DATABASES command on the client; look for an entry for the TEST_DB database
D. Execute the LIST DCS DIRECTORY command on the client; look for an entry for the TEST_DB database

Q.A database named TEST_DB resides on a z/OS system and listens on port 446. The TCP/IP address for this system is 192.168.10.20 and the TCP/IP host name is MYHOST. Which of the following commands is required to make this database accessible to a Linux client?
A. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 192.168.10.20;
CATALOG DATABASE zos_db AS test_db AT NODE zos_srvr;
CATALOG DCS DATABASE zos_db AS test_db;
B. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 192.168.10.20;
CATALOG DCS DATABASE zos_db AS test_db AT NODE zos_srvr;
C. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 446;
CATALOG DCS DATABASE zos_db AS test_db AT NODE zos_srvr;
D. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 446;
CATALOG DATABASE zos_db AS test_db AT NODE zos_srvr;
CATALOG DCS DATABASE zos_db AS test_db;


Q.Which of the following statements will catalog the database MYDB on the node MYNODE and assign it the alias MYNEWDB?
A. CATALOG DATABASE mynewdb AT NODE mynode
B. CATALOG DATABASE mynewdb AS mydb AT NODE mynode
C. CATALOG DATABASE mydb AT NODE mynode
D. CATALOG DATABASE mydb AS mynewdb AT NODE mynode

Q.Which of the following tools can NOT be used to catalog a database?
A. Control Center
B. SQL Assist
C. Configuration Assistant
D. Command Line Processor

Q.Which of the following tools can NOT be used to catalog a database?
A. Visual Explain
B. Alert Center
C. Journal
D. Configuration Assistant

Q.In which of the following scenarios would a stored procedure be beneficial?
A. An application running on a remote client needs to be able to convert degrees Celsius to degrees Fahrenheit and vice versa
B. An application running on a remote client needs to collect three input values, perform a calculation using the values provided, and store the input data, along with the results of the calculation in two different base tables
C. An application running on a remote client needs to track every modification made to a table that contains sensitive data
D. An application running on a remote client needs to ensure that every new employee that joins the company is assigned a unique, sequential employee number

Q.CREATE TABLE orders
     (order_num      INTEGER NOT NULL,
       buyer_name    VARCHAR(35),
       amount        NUMERIC(5,2));

CREATE UNIQUE INDEX idx_orderno ON orders(order_num);
Which of the following describes the resulting behavior?
A. Every ORDER_NUM value entered must be unique; whenever the ORDERS table is queried rows should be displayed in order of increasing ORDER_NUM values
B. Every ORDER_NUM value entered must be unique; whenever the ORDERS table is queried rows will be displayed in no particular order
C. Duplicate ORDER_NUM values are allowed; no other index can be created for the ORDERS table that reference the ORDER_NUM column
D. Every ORDER_NUM value entered must be unique; no other index can be created for the ORDERS table that reference the ORDER_NUM column

Q.An alias can be an alternate name for which two of the following DB2 objects?
A. Sequence
B. Trigger
C. View
D. Schema
E. Table

Q.Which of the following events will NOT cause a trigger to be activated?
A. A select operation
B. An insert operation
C. An update operation
D. A delete operation

Q.If a view named V1 is created in such a way that it references every column in a table named EMPLOYEE except a column named SALARY, which of the following is NOT an accurate statement?
A. View V1 can be used in the same context as the EMPLOYEE table for all data retrieval operations that do not acquire SALARY information
B. View V1 can be used as a data source for other views
C. View V1 does not have to reside in the same schema as the EMPLOYEE table
D. All data, except SALARY data that is stored in the EMPLOYEE table is copied to the physical location associated with view V1
 
Q.Which of the following would NOT provide access to data stored in table TABLE1 using the name T1?
A. An alias named T1 that references table TABLE1
B. A view named T1 that references table TABLE1
C. A schema named T1 that references table TABLE1
D. An alias named T1 that references a view named V1 that references table TABLE1

Q.Which of the following DB2 objects can be referenced by an INSERT statement to generate values for a column?
A. Sequence
B. Identity column
C. Trigger
D. Table function

Q.A sequence was created with the DDL statement shown below:
CREATE SEQUENCE my_seq START WITH 10 INCREMENT BY 10 CACHE 10
User USER1 successfully executes the following statements in the order shown:
VALUES NEXT VALUE FOR my_seq INTO :hvar;
VALUES NEXT VALUE FOR my_seq INTO :hvar;
User USER2 successfully executes the following statements in the order shown:
ALTER SEQUENCE my_seq RESTART WITH 5 INCREMENT BY 5 CACHE 5;
VALUES NEXT VALUE FOR my_seq INTO :hvar;
After users USER1 and USER2 are finished, user USER3 executes the following query:
SELECT NEXT VALUE FOR my_seq FROM sysibm.sysdummy1
What value will be returned by the query?
A. 5
B. 10
C. 20
D. 30

Q.Given the following statements:
CREATE TABLE tab1 (c1 INTEGER, c2 CHAR(5));
CREATE VIEW view1 AS SELECT c1, c2 FROM tab1 WHERE c1 < 100;
CREATE VIEW view2 AS SELECT c1, c2 FROM view1
        WITH CASCADED CHECK OPTION;
Which of the following INSERT statements will fail to execute?
A. INSERT INTO view2 VALUES(50, 'abc')
B. INSERT INTO view1 VALUES (100, 'abc')
C. INSERT INTO view2 VALUES(150, 'abc')
D. INSERT INTO view1 VALUES(100, 'abc')

Q.Given the following statements:
CREATE TABLE t1 (c1 INTEGER, c2 CHAR(5));
CREATE TABLE t1audit (user VARCHAR(20), date DATE, action
VARCHAR(20));

CREATE TRIGGER trig1 AFTER INSERT ON t1
FOR EACH ROW
MODE DB2SQL
INSERT INTO t1audit VALUES (CURRENT USER, CURRENT DATE,
'Insert');
If user USER1 executes the following statements:
INSERT INTO t1 VALUES (1, 'abc');
INSERT INTO t1 (c1) VALUES (2);
UPDATE t1 SET c2 = 'ghi' WHERE c1 = 1;
How many new records will be written to the database?
A. 0
B. 2
C. 3
D. 4

Q.Which of the following is NOT an attribute of Declared Global Temporary Tables (DGTTs)?
A. Each application that defines a DGTT has its own instance of the DGTT
B. Two different applications cannot create DGTTs that have the same name
C. DGTTs can only be used by the application that creates them, and only for the life of the application
D. Data stored in a DGTT can exist across transaction boundaries

Q.Which of the following is an accurate statement about packages?
A. Packages provide a logical grouping of database objects.
B. Packages contain control structures that are considered the bound form for SQL statements
C. Packages describe the objects in a DB2 database and their relationship to each other
D. Packages may be used during query optimization to improve the performance for a subset of SELECT queries

Q.Given the following information:
Protocol: TCP/IP
Port Number: 5000
Host Name: DB_SERVER
Database Name: TEST_DB
Database Server Platform: Linux
Which of the following will allow a client to access the database stored on the server?
A. CATALOG DATABASE test_db AS test_db REMOTE TCPIP SERVER db_server PORT 5000 OSTYPE LINUX;
B. CATALOG TCPIP NODE 5000 REMOTE SERVER db_server OSTYPE LINUX;
CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;
C. CATALOG TCPIP NODE db_server REMOTE db_server SERVER 5000 OSTYPE LINUX;
CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;

D. CATALOG TCPIP NODE db_server REMOTE db_server PORT 5000 OSTYPE LINUX;
CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;

Q.A declared temporary table is used for which of the following purposes?
A. Backup purposes
B. Storing intermediate results
C. Staging area for load operations
D. Sharing result data sets between applications

Q.Which of the following DB2 objects is NOT considered executable using SQL?
A. Routine
B. Function
C. Procedure
D. Trigger

Q.Which of the following is NOT an accurate statement about views?
A. Views are publicly referenced names and no special authority or privilege is needed to use them.
B. Views can be used to restrict access to columns in a base table that contain sensitive data
C. Views can be used to store queries that multiple applications execute on a regular basis in a database
D. Views support INSTEAD OF triggers

Q.Which of the following SQL statements can be used to create a DB2 object to store numerical data as EURO data?
A. CREATE NICKNAME euro FOR DECIMAL (9,3)
B. CREATE ALIAS euro FOR DECIMAL (9,3)
C. CREATE DISTINCT TYPE euro AS DECIMAL (9,3)
D. CREATE DATA TYPE euro AS DECIMAL (9,3)