DB2 QUESTIONS
1. What What does does QMF QMF sta stands nds for ? a) b) c) d)
Query Query Mana Manager ger Fac Facili ility ty Query Query Mana Managem gement ent Fac Factor tor Query Query Mana Managem gement ent Fac Facili ility ty (X) None None of the the abo above ve
2. When DB2 is run with TSO TSO data can can be accessed accessed from a TSO terminal terminal in a) b) c) d)
Fore Foregr grou oun nd Batch Batch job job runni running ng in in backgr background ound Both a & b (X (X) None None of the the abo above ve
3. When DB2 is run in IMS environment environment an application application can access data in a) b) c) d)
Only Only DB2 DB2 data databa base se Only Only IMS IMS dat datab abas ase e Both Both DB2 DB2 & IMS IMS datab database ases s (X) (X) None None of the the abo above ve
4. DB2 application application programs programs can be written in which of the following languages? languages? a) b) c) d)
COBOL PL/I, PL/I, FORTRA FORTRAN, N, PASC PASCAL, AL, COBOL, COBOL, C PL/I, PL/I, FORTRA FORTRAN, N, C, COBOL, COBOL, ASSE ASSEMBL MBLER ER COBOL, COBOL, PL/I, PL/I, FORTRAN, FORTRAN, BASIC, BASIC, APL2, APL2, C & ASSEMBL ASSEMBLER ER (X)
5. DB2 main maintain tains s inform informatio ation n about about the the data data e) f) g) h)
In Table bles In a set set of tabl tables es know known n as DB2 DB2 cata catalog log (X) (X) In DB2 DB2 dat datab abas ase e None None of the the abo above ve
6. DB2 utilit utilities ies perfor perform m the follo following wing functi functions ons a) b) c) d)
Back Back up up and and reco recover ver data data Load Load and reorg reorgan anize ize dat data a Check Check and repair repair data data & monit monitor or perform performance ance All All the the abov above e (X) (X)
7. What What does does QBE QBE stan stands ds for for ? a) b) c) d)
Quer Query y By Exe Execu cuti tion on Query Query By Expl Explan anati ation on Quer Query y By By Exa Examp mple les s Query Query By Exam Example ple (X) (X)
8. QMF function functions s are are used used to to perfor perform m a) b) c) d)
Ad hoc hoc query query request requests s using using SQL Ad hoc query query usin using g QBE QBE & report report prep preparat aration ion Generati Generation on of graphic graphics s display displays s All All the the abov above e (X) (X)
9. Which operator operator is used to eliminate eliminate duplicate duplicate rows is QBE Query Query ? a) b) c) d)
DISTINCT UNQ (X) UNIQUE None None of the the abo above ve
10.Which 10. Which of the following following is valid in the case of QBE Query Query ? a) b) c) d)
PRIC PRICE E NOT NOT > 500. 500.00 00 PRIC PRICE E --| > 500. 500.00 00 NOT NOT PRIC PRICE E > 500 500.0 .00 0 (X) (X) None None of the the abo above ve
11.What 11. What does SQLCA stands stands for ? a) b) c) d)
SQL Commun Communicat ications ions Area Area (X) (X) SQL SQL Con Contr trol ol Area Area SQL SQL Conn Connec ecti tion on Are Area a SQL SQL Cont Contro roll Acce Access ss
12.When there is no data available to satisfy the FETCH condition DB2 places a return code a) +000 b) –001 c) +904
d) +100(X)
13.In processing SQL return codes which are the conditions that can be specified in a WHENEVER statement ? a) b) c) d)
SQLERROR, SQLERROR, NOT FOUND, FOUND, SQLWA SQLWARNING( RNING(X) X) SQLERR SQLERROR, OR, FOUND, FOUND, WARNIN WARNING G SQLE SQLERR RROR OR,, NOT NOT FOU FOUND ND None None of the the abo above ve
14.What 14. What does DCLGEN stands stands for ? a) b) c) d)
Declar Declarat ation ion Genera Generall Data Control Control Languag Language e Genera Generator tor DB2 DB2 Con Contr trol ol Regi Region on Declar Declarat ation ion Gene Generat rator or (X) (X)
15.What 15. What does DB2I stands stands for ? a) b) c) d)
DB2 DB2 Int Inter erch chan ange ge DB2 DB2 Inf Infor orma mati tion on DB2 DB2 Inte Intera ract ctiv ive e (X) (X) DB2 Init
16.The 16. The relational model is concerned concerned with a) b) c) d)
Data Data Stru Struct ctur ure e Data Data Inte Integr grit ity y Data Data Mani Manipu pula lati tion on All All the the abov above e (X) (X)
17. The relational equivalent of RECORD is a) b) c) d)
Relation Tuple ple (X) (X) Attrib tribut ute e Prim Primar ary y Key Key
18.The 18. The two types of DB2 tables tables are
a) b) c) d)
Base Base Table Tables s & Views Views (X) (X) Base Base Tabl Tables es & Catal Catalogs ogs Tabl Tables es & Vie Views ws None None of the the abo above ve
19.What 19. What does IRLM stands stands for ? a) b) c) d)
IMS Resource Resource Lock Lock Manage Managerr (X) (X) Informat Information ion Reso Resource urce Lock Manager Manager Informat Information ion Resour Resource ce Locat Location ion Manag Manager er Integrit Integrity y Resour Resource ce Lock Lock Manager Manager
20.Runtime Supervisor is resident in main memory when the application program is executing a) True (X) b) False c) Can’t Say
21.Which Database Services component manages the physical database ? a) b) c) d)
DBRM Buff Buffer er Mana Manage ger r Runt Runtim ime e Super Supervi viso sor r Data Data mana manage gerr (X) (X)
22.Bind 22. Bind component Produces Produces a) b) c) d)
Only Only Pack Packag age e Only Only Appl Applica icatio tion n Plan Plan Both a & b (X (X) None None of the the abo above ve
23.Directory 23. Directory cannot be accessed accessed by SQL statements statements a) True (X) b) False c) Can’t Say
24.What 24. What does CAF stands stands for ? a) b) c) d)
Call Attachm Attachment ent Facility Facility (X) Catalog Catalog Attachme Attachment nt Facility Facility Contro Controll Attachm Attachmen entt Facilit Facility y Catalog Catalog Allocati Allocation on Facilit Facility y
25.What 25. What are the valid DB2 numeric data types ? a) b) c) d)
NUMERIC, NUMERIC, INTEGER, INTEGER, SMALLINT SMALLINT,, FLOAT FLOAT LONG, LONG, SMAL SMALLIN LINT, T, FLOA FLOAT T INTEGER, INTEGER, SMALLINT SMALLINT,, DECIMAL DECIMAL,, FLOAT FLOAT (X) (X) INTEGER, INTEGER, TINYINT, TINYINT, FLOAT, FLOAT, DECIMAL DECIMAL
26.The 26. The statement statement START_DATE START_DATE + 1 YEAR YEAR + 6 MONTHS is correct correct a) True (X) b) False c) Can’t Say
27.The 27. The statement statement START_DATE + ( 1 YEAR + 6 MONTHS ) is valid a) True b) False lse (X) c) Can’t Say
28.The 28. The statement TIME( TIME(‘9:00 ‘9:00 AM’) + 120000 is valid a) True (X) b) False c) Can’t Say
29.The 29. The statement statement ‘9:00 AM’ + 120000 is valid d) True e) False lse (X) f) Can’t Say
30.The 30. The statement statement START_DATE + 6 HOURS is valid
g) True h) False lse (X) i) Can’t Say
31.DB2 31. DB2 Supports Supports Outer Join a) True b) False lse (X) c) Can’t Say
32.The use of IN or BETWEEN Operator in SQL gives better performance than LIKE Operator a) True (X) b) False c) Can’t Say
33.The maximum number of tables that can be used in DB2 SQL join is a) b) c) d)
12 15 (X) 5 10
34.Which utility allows the user to execute any operation that system supports a) b) c) d)
SYSOPR DBMAINT DBADM SYSADM (X) (X)
35.Which utility allows the user to execute maintenance functions on a database e) f) g) h)
SYSOPR DBMAINT (X (X) DBADM DBCTRL
36.Failure to use null indicator variable when referencing a nullable column will give the following SQLCODE a) b) c) d)
+150 –001 +276 +305 (X)
37.If 37. If the Singleton Singleton SELECT retrieves more than one one row the SQLCODE SQLCODE will contain the value a) b) c) d)
–255 –905 –001 –811 (X)
38.Which 38. Which SQLCODE is used to check for duplicate index index on a column a) b) c) d)
–811 –803 (X) –805 –855
39.A Plan is a) Is an executable executable module module containing containing access access path path logic provided provided by by the DB2 DB2 optimiser (X) b) In an non executable executable module containing containing access path logic logic provide provide by the DB2 DB2 optimiser c) Is just just a modu module le contain containing ing acce access ss path path to DB2 DB2 optimis optimiser er d) Is a module module conta containin ining g access access path
40.A Package is a) b) c) d)
A sing single, le, bound bound DBRM DBRM A Multi bound bound DBRM DBRM with optim optimised ised acces access s paths paths A Single Single bound bound DBRM with optim optimised ised access access paths paths (X) (X) A Mul Multi ti bou bound nd DBRM DBRM
41.Package 41. Package information information is stored in
a) b) c) d)
DB2 DB2 Dire Direct ctor ory y DB2 DB2 syst system em tabl tables es DB2 catal catalog og tables tables (X) None None of the the abo above ve
42.Package 42. Packages s can never be directly executed executed a) True (X) b) False c) Can’t Say
43.A Collection is a) b) c) d)
A A A A
syst system em define defined d nam name e user user defin defined ed name name for Plans Plans user user defin defined ed name name for Packa Packages ges (X) System System defined defined name name for Plans Plans
44.How 44. How many buffer pools are there is DB2 a) b) c) d)
4 (X) 3 15 8
45.Data 45. Data Pages can be of size size a) b) c) d)
4K 32K 4K & 32K 4K or 32K 32K (X) (X)
46.DB2 46. DB2 sorting takes takes place at a) DSNBD07 b) DSND DSNDB0 B07( 7(X) X) c) DSNBD10 d) DSNDB11
47.What 47. What is an image copy copy ? a) b) c) d)
It is an exact exact reprodu reproductio ction n of entire entire databa database se It is an exact exact reprodu reproductio ction n of partial partial databa database se It is an exact reproduct reproduction ion of all or part part of a tablespace tablespace (X) (X) It is an exact exact reprodu reproductio ction n of only only part of tables tablespace pace
48.What 48. What is meant by an index scan? a) b) c) d)
When an entire entire index index is scanned scanned to locate locate rows we call it as index scan scan (X) When only part of the the index is scanned scanned to locate locate rows we call call it as index index scan When all all the rows rows are are scanned scanned we call it as index index scan scan None None of the the abo above ve
49.What 49. What is a ‘nonleaf’ page page ? a) b) c) d)
It is a page page that that contains contains keys keys It is a page page that contains contains page page numbers numbers of other other pages in the index index It is a page page that that contain contains s the index index inform informatio ation n It is a page page that contains contains both both keys & page numbers numbers of other other pages in the index (X)
50.Non 50. Non leaf page never point to actual data a) TRUE (X) b) FALSE c) CAN’T SAY
51.Where 51. Where are plans stored stored ? a) b) c) d)
SYSIBM SYSIBM SYSIBM SYSIBM
TABLESPACES . CATALOGS . SYSPLAN(X) . SYS . PLAN .
52.What is a STORGROUP ? a) It is a collect collection ion of Packa Packages ges stored stored on on DASD volume volumes s b) It is a collect collection ion of Plans Plans stored stored on on DASD volum volumes es
c) It is a named collection collection of DASD DASD volumes volumes to be be used by tablespaces tablespaces d) It is a named named collection collection of DASD DASD volumes volumes to be used used by tablespaces tablespaces and index spaces of databases.(X)
53.What 53. What are the different types of locks that can be held ? a) b) c) d)
Exclus clusiv ive e Shared Update All All the the abov above e (X) (X)
54.What 54. What command command used by TSO TSO users to invoke invoke DB2 ? a) b) c) d)
RUN DSN DSN DSN RUN RUN (X) RUN None None of the the abo above ve
55.What 55. What are the different types of DB2 tablespaces tablespaces ? a) b) c) d)
Simple Segme gmented ted Part Partit itio ion ned All All the the abov above e (X) (X)
56.What is the maximum number of partitions allowed in a partitioned tablespace? a) b) c) d)
58 64 (X) 32 128
57.Can 57. Can a column be added after a table has been created in DB2 ? a) YES (X) b) NO c) CAN’T SAY 58.Can you cascade an update of a primary key?
a) b) c) d)
YES NO Only Only if the the row row has dep depend endan ants ts Only if the row has has no dependa dependants nts (X) (X)
59. SQLDA(SQL Descriptor Area ) can be specified in a) b) c) d)
COBO COBOL L prog progra ram m PL/I PL/I prog progra ram m C pr program None None of of the the abov above e (X) (X)
60.Which component of MVS is used for storing large volumes of trace data ? a) b) c) d)
GTF (X) RES SMF SRV
61.Which 61. Which SQLCODE denotes Successful Successful execution a) b) c) d)
+100 +000 (X) –000 +010
62.Which 62. Which SQLCODE denotes Unacceptable Unacceptable SQL statement? statement? a) b) c) d)
–082 –080 –019 –084 (X)
63.Which 63. Which SQLCODE denotes denotes Dead Lock situation ? a) b) c) d)
–010 –904 (X) –033 –343
64.UNION 64. UNION keyword causes duplicate rows to be removed from final result in the case of merger of more than 1 table a) TRUE (X) b) FALSE c) CAN’T SAY
65.Which operator denotes single unknown character when used with the LIKE keyword ? a) b) c) d)
? % _ (X) @
66.A 66. A Tablespace can be thought thought of a) b) c) d)
As a logical address space on secondary secondary storage (X) As a Physical Physical address address space space on on secondary secondary storage Can’t Say It is phys physical ical location location on on the hard hard disk
67.What 67. What does SPUFI SPUFI stands for ? a) b) c) d)
SQL Prog Programm ramming ing Using Using File File Input Input SQL Process Processing ing Using Using File File Inpu Inputt SQL Process Processor or Using Using File Input Input (X) (X) Sequence Sequence Process Processing ing Using Using File File Input Input
68.Which commands are used to access DB2 data through on-line TSO using a) b) c) d)
CLIST REX REXX EXEC Both a & b (X (X) None None of the the abo above ve
69.Using 69. Using which utility data can be loaded into DB2 tables ? a) LOAD b) QBE
c) DXT DXT (Da (Data ta Extr Extrac act) t) d) All All the the abov above e (X) (X)
70. A Program can explicitly define a commit point by executing a) b) c) d)
COMMIT ROL ROLLBACK Both a & b (X (X) None None of the the abo above ve
71.Does 71. Does DB2 supports Year 2000 2000 ? a) YES (X) b) NO c) CAN’T SAY 72.REPAIR utility a) b) c) d)
Restores Restores databas databases es to a current current state state Replaces Replaces invalid invalid data data with valid valid data data (X) Removes Removes unwante unwanted d imag image e copie copies s Determines Determines whether whether indexes indexes are consistent consistent with the the data in their corresponding tables
73.What 73. What does SAA stands stands for ? a) b) c) d)
System System Applic Applicatio ation n Archit Architectu ecture re Syste System m Admini Administr strato atorr Area System System Adminis Administrat trator or Account Account None None of the the abo above ve
74.Which are the SQL statements that can’t be executed dynamically ? a) b) c) d)
CLOSE DECLARE EXECUTE All All the the abov above e (X) (X)
75.DB2 75. DB2 utilities are generally generally run as MVS batch jobs a) TRUE (X)
b) FALSE c) CAN’T SAY
76.Which 76. Which are System Resource Privileges? a) b) c) d)
GRANT GRANT USE USE OF OF BUF BUFFE FERPO RPOOL OL GRANT GRANT USE USE OF OF STOG STOGROU ROUP P GRANT GRANT USE USE OF TABL TABLES ESPA PACE CE NONE NONE OF THE THE ABO ABOVE VE
77.The 77. The EXISTS keyword keyword in SQL will give boolean result a) TRUE (X) b) FALSE c) CAN’T SAY
78.The maximum number of characters that can be used to name a table in DB2 is a) b) c) d)
15 16 18 (X) No rest restri rict ctio ion n
79.Can 79. Can a UNIQUE column contain a null value? a) b) c) d)
NO Only a singl single e null is is allowed allowed for for that colum column n (X) Multiple Multiple nulls nulls are allowed allowed for for that that colum column n None None of the the abo above ve
80. The SELECT statements coded in COBOL should have which of the terminator ? a) b) c) d)
; ,
END-EXEC (X) *
81.The 81. The database langua language ge was introduced by a) b) c) d)
E.F. Cod Codd Chamb Chamberl erlin in and and Boyc Boyce e (X) Mullins None None of the the abo above ve
82.What is the first name of the prototype language introduced by IBM in 1974 ? a) b) c) d)
SEQUEL SEQU SEQUEL EL –XRM –XRM(X (X)) SQL None None of the the abo above ve
83.Which 83. Which was the first prototype of successful RDBMS? a) b) c) d)
DB2/2 XRM SYST SYSTEM EM-R -R (X) (X) None None of the the abo above ve
84.The 84. The number number of attributes attributes in a relation are are called a) b) c) d)
Deg Degree ree(X) (X) Number Domain None None of the the abo above ve
85.The 85. The number of tuples or rows in a relation are called a) b) c) d)
Rowc Rowcou oun nt Card Cardin inal alit ity y (X) (X) Degree None None of the the abo above ve
86.The primary key and foreign key should be defined on the same underlying domain
a) TRUE(X) b) FALSE c) CAN’T SAY
87.A key made up of more than one column is called a) b) c) d)
Com Complex plex Key Key Seco Second ndar ary y Key Key Comp Compos osit ite e Key Key(X (X)) Fore Foreig ign n Key
88.The 88. The operating system system on which DB2 run is a) b) c) d)
OS/2 UNIX XENIX MVS (X)
89.What are the functions of the system services component? a) b) c) d)
Logging Operat Operator or commu communi nicat cation ion Syst System em oper operat atio ion n All All the the abov above( e(X) X)
89.What 89. What is the function of DB2 pre-compiler? pre-compiler? a) Removes Removes all all the state statement ments s in source source code code b) Replaces all all the SQL statemen statements ts into correspond corresponding ing host language language CALL CALL statements(X) c) Replaces SQL statements statements into correspond corresponding ing host host language language statements statements d) None None of the the abo above ve
90.What 90. What does DBRM contain? contain? a) Edited form form of the SQL statements statements with with some additiona additionall information(X) information(X) b) Complied Complied versi version on of SQL state statemen ments ts c) Complied Complied version version of source source code
d) None None of the the abo above ve
91.The 91. The function of Bind id to convert the high-level high-level DBRMs into optimsed internal form a) TRUE(X) b) FALSE c) CAN’T SAY 92.Bind performs two major jobs syntax checking and optimization a) TRUE(X) b) FALSE c) CAN’T SAY
93.What 93. What is SQL2 or SQL/92? SQL/92? a) b) c) d)
The The secon second d relea release se of of SQL SQL The The secon second d vers version ion of SQL SQL The ANSI ANSI SQL SQL standar standard d introduc introduced ed in 1992(X 1992(X)) None None of the the abo above ve
94.The 94. The Data Definition statements statements of SQL are GRANT and REVOKE a) TRUE b) FALSE(X) (X) c) CAN’T SAY
95.What are the DB2 objects that can be altered using ALTER statement? a) b) c) d)
Views Indexes Tables bles((X) All All the the abov above e
96. When creating a table using LIKE command the Primary Key and foreign key definitions are not inherited a) TRUE(X) b) FALSE c) CAN’T SAY
97.Alternate key specifications can’t be modified using ALTER TABLE statement a) TRUE b) FALSE c) CAN’T SAY
98.What 98. What are the special special registers registers of DB2? a) b) c) d)
CURR CURREN ENT T SQL SQLID ID CURREN CURRENT T DATE DATE,, CURRE CURRENT NT TIME TIME CURR CURREN ENT T TIM TIMES ESTA TAMP MP All All the the abov above( e(X) X)
99.Whenever a base table is dropped the indexes for that table are not dropped a) TRUE b) FALSE(X) (X) c) CAN’T SAY
100.
The default default value value in SELE SELECT CT statem statement ent is is SELECT SELECT DISTINCT DISTINCT
a) TRUE b) FALSE(X) (X) c) CAN’T SAY
101. a) b) c) d)
What is the the clause clause for for gettin getting g the values values in particul particular ar order? order?
ORDE ORDER R BY(X BY(X)) GROUP BY ORDER GROUP
102. Is the the state statement ment “SELECT “SELECT * FROM FROM EMP EMP WHERE WHERE ENAME ENAME = NULL” NULL” correct ? a) TRUE b) FALSE(X) (X) c) CAN’T SAY
103. What is the the functio function n used used to to retriev retrieve e all all the rows with out elimi eliminati nating ng duplicate values? a) b) c) d)
COUNT COUNT AL ALL COUNT(&) COUNT(*)
104. The SQL stateme statement nt “SELECT “SELECT AVG(MIN( AVG(MIN(SALA SALARY)) RY)) AS AVERAGE AVERAGE FROM EMP” is legal a) TRUE b) FALSE(X) (X) c) CAN’T SAY
105. Which Which is the the aggrega aggregate te functi function on that that returns returns zero zero when when there there are no rows rows satisfying the search criteria? a) b) c) d)
MAX SUM AVG COUNT(X)
106. What will the aggrega aggregate te funct function ion SUM return return when when there there are are no rows satisfying the search criteria? a) b) c) d)
ZERO –1 NULL(X) 1
107. 107.
Subq Subquer ueries ies are are neste nested d SELECT SELECT sta statem temen ents ts
a) TRUE(X) b) FALSE c) CAN’T SAY
108.
If the the compar comparison ison oper operator ator in in a join join is “=” “=” then then that that join join is calle called d
a) b) c) d)
Out Outer Join Inne nner Joi Join n Equa Equali lity ty Join oin Equi Equijo join in(X (X))
109. When one of of the the identic identical al column column in a equijoin equijoin is elimi eliminate nated d then then that that join join is called a) b) c) d)
Equijo ijoin Natu Natura rall Joi Join( n(X) X) Out Outer Join Inne nner Joi Join n
110. For a view view to be be updatab updatable le the view view should should be be derived derived from from a singl single e base base table a) TRUE(X) b) FALSE c) CAN’T SAY
111. If the the column column of a view view is deriv derived ed from from an an aggreg aggregate ate funct function ion then then that that view is not updatable a) FALSE b) TRUE(X) c) CAN’T SAY
112. 112.
A view view defi defined ned on on a updat updatabl able e view view is updat updatabl able e
a) TRUE(X) b) FALSE c) CAN’T SAY
113.
Name Name the DB2 DB2 table table where where the the EXPLAI EXPLAIN N analysi analysis s results results can can be got
PLAN_TABLE(X) b) EXPL EXPLAI AIN_ N_TA TABL BLE E c) PACK PACKAG AGE_ E_TA TABL BLE E d) None None of the the abo above ve a)
114.
The BETWEEN BETWEEN predica predicate te is more efficien efficientt than than >= predica predicate te
a) TRUE(X) b) FALSE c) CAN’T SAY
115. 115. a) b) c) d)
What What are are the the table table privil privileg eges es? ?
ALTE ALTER, R, DELE DELETE TE INDEX, INDEX, INSER INSERT, T, SELECT, SELECT, UPDATE UPDATE ALL All All the the abov above( e(X) X)
116.What are the Database privileges? a) b) c) d)
CREATET CREATETAB, AB, CREATET CREATETS, S, DISPL DISPLAYDB AYDB DROP, DROP, IMAGE IMAGECOP COPY, Y, START STARTDB DB LOAD, LOAD, RECOV RECOVERD ERDB, B, REORG REORG All All the the abov above( e(X) X)
116.
CICS and DB2 can exist exist under under the same same region region in MVS MVS opera operating ting system system
a) TRUE b) FALSE(X) (X) c) CAN’T SAY
117. 117. a) b) c) d)
What What is the the faci facilit lity y that that conn connect ects s DB2 and and CICS? CICS?
SQL VSAM CICS CICS Attac Attachm hment ent Facil Facility ity(X (X)) DB2 Atta Attach chme ment nt Faci Facilit lity y
118. 118.
DB2 supp support orts s reco record rd level level locki locking ng
a) TRUE b) FALSE(X) (X) c) CAN’T SAY
119. 119. a) b) c) d)
LOCK LOCK Mana Manage ger r Reso Resour urce ce Man Manag ager er IRLM(X) None None of the the abov above e
120. a) b) c) d)
Which Which is is the the syste system m that that DB2 DB2 uses uses for for locki locking ng
DB2 tryin trying g to lock lock witho without ut the the help of IRLM IRLM then then that that locks locks are are called called
Locks Links Latc atches hes(X) (X) None None of the the abo above ve
121. 121.
Latch Latches es are are mor more e effic efficien ientt than than lock locks s
a) TRUE(X) b) FALSE c) CAN’T SAY
122. a) b) c) d)
What is the the utilit utility y used used to to gener generate ate host host variab variables? les?
QMF SPUFI DCL DCLGEN( GEN(X X) None None of the the abo above ve
123. When WHERE CURRENT CURRENT of claus clause e is used used the the cursor cursor shou should ld be decla declared red with a) b) c) d)
UPDA UPDATE TE cla claus use e FOR FOR UPDAT UPDATE E OF claus clause(X e(X)) MODI MODIFY FY cla clause use None None of the the abo above ve
124. 124.
What What are are the the two two typ types es of SQLs SQLs? ?
a) b) c) d)
Simpl Simple e and comple complex x Stati Static c and and Dyna Dynamic mic(X (X)) Re-en Re-entra trant nt and and Non re-e re-entr ntran antt Non Non of the the abov above e
125. 125. a) b) c) d)
What What are are the the Dyna Dynamic mic SQL statem statemen ents? ts?
DECLARE EXECUTE PREPARE PREPARE and EXECUTE EXECUTE IMMEDIAT IMMEDIATE E All All the the abov above( e(X) X)
126.
EXECUTE EXECUTE IMMEDIAT IMMEDIATE E dynam dynamic ic SQL SQL suppor supports ts SELE SELECT CT statem statement ent
a) TRUE b) FALSE(X) (X) c) CAN’T SAY 127. 127. a) b) c) d)
* ?(X) / ~
128. 128. a) b) c) d)
What What is is the the para parame mete terr mark marker er in DB2? DB2?
What What is is the the inpu inputt for for Bin Bind d oper operati ation? on?
Sour Source ce Code Code SQLs DBRMs(X) All All the the abov above e
129. 129.
What What is is the the outp output ut of of Bind Bind opera operatio tion? n?
a) Loa Load mod modul ule e b) Appli Applica catio tion n plan plan(X (X)) c) DBRMs
d) All All the the abov above e
130. a) b) c) d)
Accou Accounti nting ng and and Audit Audit trac trace e Globa Globall and and Monit Monitor or trac trace e Performa Performance nce and Statisti Statistics cs trace trace All All the the abov above( e(X) X)
131. 131. a) b) c) d)
What are the the diffe different rent types types of traces traces provi provided ded by by DB2? DB2?
What What are are the the main main back backup up util utiliti ities es of of DB2? DB2?
COPY COPY and and MER MERGE GE COP COPY Y RECOVER QUIESEC All All the the abov above( e(X) X)
132.
The LOAD LOAD utility utility is used used to accompl accomplish ish bulk bulk inser inserts ts into into DB2 DB2 tables tables
a) TRUE(X) b) FALSE c) CAN’T SAY
133.
The COMMIT COMMIT and and ROLLBA ROLLBACK CK statem statements ents are instr instructi uctions ons to DBMS DBMS
a) TRUE b) FALSE(X) (X) c) CAN’T SAY
134. 134.
COMMIT COMMIT and and COMMI COMMIT T WORK WORK does does the the same same func functio tion n
a) TRUE(X) b) FALSE c) CAN’T SAY
135. What is is the proces process s of copyin copying g content contents s of the activ active e log to the archiv archive e log? a) Load Load-s -she hedd ddin ing g b) OffOff-Lo Load adin ing( g(X) X)
c) Arch rchivi iving d) None None of the the abo above ve
136. A non-cor non-correla related ted subqu subquery ery is resol resolved ved in botto bottom m up manner manner where where as correlated subquery is resolved in top down manner. a) TRUE(X) b) FALSE c) CAN’T SAY
137. Which Which are the situa situation tions s in which which colum column n names names must must be be specifi specified ed explicitly for all columns of the view? a) b) c) d)
Any colum column n of the view view is derived derived form form a function function Any column column of the view view is derived derived form form an operational operational expression expression Two or more columns columns of the view would would otherwise otherwise have have the same name name All All the the abov above( e(X) X)
138. The ON ON DELETE DELETE CASCA CASCADE DE option option means means that that the the delete delete opera operation tion is cascaded, that is if a row in the parent table is deleted then all the matching rows in the child child table is automatically deleted. a) TRUE(X) b) FALSE c) CAN’T N’T SAY
139. The expression DATE('6/30/1977') - 1 MONTH yields the result '5/31/1977'. a) TRUE b) FALSE(X) (X) c) CAN’T SAY
139. Which Which is the the function function used used for for extract extracting ing the the hour hour portion portion of the the time time or Timestamp
a) b) c) d)
TIME HOUR( UR(X) TIME TIMEST STAM AMP P TO TO HOU HOUR R None None of the the abo above ve
140. A syncpoi syncpoint nt represe represents nts a bound boundary ary point point betwee between n two transa transactio ctions ns and it corresponds to the end of a logical unit of work and therefore a point in which the database is in a state of consistency. a) TRUE(X) b) FALSE c) CAN’T SAY
141. 141. a) b) c) d)
What What are are the func functio tions ns of of the CHEC CHECK K utili utility? ty?
Checks Checks the integ integrity rity of of data data structur structures es Checks Checks the refer referenti ential al integr integrity ity betwe between en 2 tables tables Checks Checks the the index indexes es for for consi consiste stency ncy All All the the abov above( e(X) X)
142. If the FROM FROM clause clause in the view view definit definition ion involve involves s multiple multiple range range variab variables, les, then it is not updatable. a) TRUE(X) b) FALSE c) CAN’T SAY
143. If NOT NOT NULL NULL WITH WITH DEFAU DEFAULT LT is specified specified during during an an ALTER ALTER TABLE TABLE command what will be the default values for the DATE, TIME and TIMESTAMP? a) b) c) d)
SPACES CURRENT CURRENT DATE, DATE, CURRENT CURRENT TIME and and CURRENT CURRENT TIMEST TIMESTAMP AMP 01/01/0001, 01/01/0001, 00:00 AM, 0001-01-0 0001-01-01-00.00. 1-00.00.00.00000 00.000000 0 (X ) None None of the the abo above ve
144. The total total colle collectio ction n of store stored d data data is divid divided ed into into a numb number er of user user databases and system databases, each of which is divided into a number of tablespaces and index spaces.
a) b) c)
TRUE(X) FALSE CAN’T SAY
145. What is the collection of data designed to be used by different people is called a) b) c) d)
File Data Databa base se (X) (X) Tuple Table
146. DB2 is
a) Database/Data communication system b) Database c) Relational Database Management system (X) d) Programming language
147. What are the characteristics of Database Management Systems? a) b) c) d)
NonNon-re redu dund ndan ancy cy Data Data Inde Indepen pende dence nce Incr Increa ease sed d Secur Securit ity y All All of the the abov above e (X) (X)
148. What are the aggregate functions supported by DB2 ? a) b) c) d)
SUM and and AV AVG SUM, SUM, MIN MIN and and MAX MAX SUM, SUM, MAX, MAX, MIN, MIN, AVG AVG and and COUNT COUNT (X) NONE
149. EXPLAIN allows the user to obtain information regarding the optimizer’s choice of access strategy for a given SQL statements. a) True (X) b) False
150. SELECT * FROM ………. will retrieve all columns of the table meeting the selection criteria . a) True (X) b) False 151. How does the defining of a stored procedure affect the size of a DB2 database? a) It increases the size of the database. b) It decreases the size of the database. c) It does not affect the size of the database. (X)
152. What is contained in the DB2 nodelock file? a) database names b) database user names c) license keys (X) d) server names
153. Which authority can be granted to a group of users using the GRANT statement? a) SYSCTRL b) SYSADM c) DBCTRL d) DBADM (X)
154. Which SQL command successfully removes uncommitted changes from a DB2 database? a) ROLL ROLLBA BACK CK (X) (X) b) DELETE c) DROP d) DECOMMIT 155. Where is a view definition stored when the view is created? a) in the database log files b) in the user tablespace
c) in the the system system catalog catalog tablespa tablespace ce (X) (X) d) in the the ind index ex tabl tables espac pace e
156. For which database object can a lock be obtained? a) a database directory b) a database tablespace (X) c) a database bufferpool d) a database column
157. Given the statement CREATE TABLE userid.test ( date_taken DATE NOT NULL, number SMALLINT NOT NULL, PRIMARY KEY (number)) Which will be the schema name for the table: a) date_taken b) number c) test d) userid (X)
158. The following tables exist in the database: db2.staff userid.staff An application issues the dynamic SQL call "select * from staff". What determines the table which will be accessed? a) the userid of the binder of the package b) the userid connected to the database (X) c) the userid of the precompiler of the package d) the userid specified in the db2cli.ini file e) the userid of the creator of the staff table
159. What is the purpose of the COLLECTION parameter of precompile/bind parameter? a) define the name of the package
b) provide an alias for the package c) define the schema of the package (X) d) define the database of the package
160. Which system catalog view can be used to determine when a bind operation was performed on a given package? a) SYSCAT.PRECOMPILE b) SYSCAT.PACKAGES (X) c) SYSCAT.BIND d) SYSCAT.TIMESTAMP e) SYSCAT.ACCESSPLAN
161. Which of the following programming methods will store an access plan within the database? a) Embedded Dynamic SQL b) Embedded Static SQL (X) c) Call Level Interface (CLI)
162. Which of the following programming method would you use if you want to restrict access to specific columns in your data? a) Embedded Dynamic SQL b) Embedded Static SQL (X) c) Call Level Interface (CLI)
163. What is the easiest method using DB2 to ensure that a columns values remain within a predetermined range? a) define a primary key on the column b) define a foreign key on the column c) define a table check constraint (X) d) define an insert constraint
164. How many rows can be retrieved using a SELECT INTO statement in an application? a) none
b) 1 (X) c) 2 d) unlimited
165. Which of the following statements uses embedded dynamic SQL? a) SELECT col1 INTO :hv from table1 b) DECLARE c1 CURSOR FOR s1 (X) c) DECLARE c1 CURSOR for SELECT col1 INTO :hv FROM table1 d) IMMEDIATE SELECT col1 INTO :hv FROM table1
166. Which type of cursor should be used so that its position will be maintained between transactions? a) FOR HOLD b) FOR UPDATE c) FOR FETCH d) WITH HOLD (X) 167. When can cursors be used to begin accessing data from the database? a) after the cursor has been declared b) after the cursor's host variable has been initialized c) after the cursor has been opened (X) d) after the first fetch has occurred
168. What occurs when you close a cursor? a) the transaction is rolled back b) the transaction is committed c) the cursor is available to be used in a subsequent fetch d) the cursor must be reopened to be used in a subsequent fetch(X)
169. How would you determine you are at the end of the result set? a) check the NULL indicator host variable after each fetch b) check the SQLCA after closing the cursor c) check the SQLCA after each fetch (X) d) check the SQLDA after each fetch
170. How should a host variable be defined to be used as a NULL indicator?
a) boolean b) integer (X) c) character d) binary large object
171.What must be done prior to capturing an EXPLAIN snapshot? A. Update the EXPLAIN directory. B. Create the EXPLAIN database. C. Rebind the EXPLAIN tool. D. Crea Create te the the EXP EXPLA LAIN IN tab table les. s. (X) (X)
172.If a query is to provide a sorted result how might DB2 perform the sorting operation? A. B. C. D.
DB2 will not sort the result DB2 will sort the data within the buffer pool DB2 will always sort the data on disk DB2 will attempt to perform the sort in memory (X)
173.Where would the SQL error code (SQLSTATE/SQLCODE) be reported? A. B. C. D.
in the SQLDA data structure in the SQLCA data structure (X) in the DB configuration file in the event monitor log
174. Given the following statements: Connect to sample grant select on table t1 to public Who can create a view based on the table t1? A. B. C.
nobody everybody(X) DBADM
D.
175. a) b) c) d)
SYSMAINT
What informat information ion is is used used as input input to the the bind bind proc process? ess?
SYSI SYSIBM. BM.SY SYSS SSTMT TMT(X) (X) SYSI SYSIBM BM.S .SYS YSCA CATL TL SYSI SYSIBM BM.S .SYS YSDT DTRG RG None None of the the abo above ve
176. 176.
What What is is the the func functio tion n of Buffer Buffer Manag Manager er? ?
a) DB2 component component responsible responsible for physically physically transferring transferring data data between between an external medium and virtual storage. b) It minimiz minimizes es the the amount amount of physi physical cal I/O. I/O. c) All All the the abov above e (X) (X)
177. 177. a) b) c) d)
1 2 3 4 (X)
178. 178. a) b) c) d)
The The valid valid colle collect ction ion of buf buffer fer pool pool is is
BP0, BP0, BP1 BP1,, BP2 BP2 and and BP32 BP32 (X) (X) BP0, BP0,BP BP2 2 and and BP33 BP33 BP0 and BP1 BP0, BP0, BP1, BP1, BP10 BP10
179. 179. a) b) c) d)
How many many buff buffer er poo pools ls are are the there re in in DB2? DB2?
What What is is the the func functio tion n of DSND DSNDB0 B07 7 data databas base? e?
Sort Sortin ing( g(X) X) Copying Merging None None of the the abo above ve
180. 180.
What What will will the the FREE FREE com comma mand nd do to a plan? plan?
a) b) c) d)
It will will drop drop the the exist existing ing pla plan n It will will delete delete the existing existing plan Both a & b(X) (X) None None of the the abo above ve
181. 181. a) b) c) d)
A data data model model is a way way of represe representin nting g entities entities A data data mode modell is a way of repres representi enting ng attri attribute butes s A data mode modell is a way of represe representin nting g relations relationships hips All All the the abov above( e(X) X)
182. 182. a) b) c) d)
What What is a dat data a mod model el? ?
How are are write write I/Os I/Os from from the the buffe bufferr pool pool execut executed? ed?
Async Asynchro hrono nous usly( ly(X) X) Sync Synchr hron onou ousl sly y Both a & b None None of the the abo above ve
183. Can one one execu execute te SQL SQL CREAT CREATE, E, ALTER ALTER or GRANT GRANT stateme statement nt on a remote system? a) YES b) NO(X) c) Can't say
184. 184. a) b) c) d)
Where Where is the the iso isolat lation ion level level speci specifie fied? d?
BIND REBIND Both a & b (X (X) None None of the the abo above ve
185.
Can a UNION UNION and a JOIN JOIN be used used in the the same same SELEC SELECT T statem statement? ent?
a) YES b) NO (X) c) Can't Say
186.
What excep exception tion condi conditio tions ns can be trappe trapped d by the WHENE WHENEVER VER statem statement? ent?
a) b) c) d)
NOT FO FOUND SQLERROR SQLW QLWARNIN RNING G All All the the abov above e (X) (X)