Elance DB2 Programming Test Answers 2015
·
Which of the following is true about locking
All of the above
Locking prevents concurrent users from accessing
inconsistent data.
Locking is a process that is used to ensure data
integrity.
The data (row) is locked until a commit is executed
to release the updated data.
When a user has a SELECT authorization on a
certain base table, and he creates a view on that table alone, then which of
the following is true?
He/She can only execute an UPDATE as long as the
definition of the view is not exceeded.
He/She also has DELETE authorization on that view
because he/she is the creator of the view.
He/She will face an authorization error when trying
to create the view
He/She only has a SELECT authorization on that
view.
What is a lightweight web application created
from multiple sources?
A smashup.
A mashup.
A crackup.
A mixup.
A role is a database entity that groups
together one or more privileges. Which of the following is true for a
"role"
A role cannot be a primary authorization ID.
A role cannot be set by using a SET CURRENT SQLID
statement.
All of the above
A role can be the schema qualifier of an object.
However, when it is used as a schema qualifier, a role is considered to be a
character string and does not add any implicit schema privileges (ALTERIN,
CREATEIN, or DROPIN) to this role.
What are the three primary development
environments for DB2?
PhotoData Studio, Microsoft Visual Studio, and IBM
Optim Development Studio.
WebSphere Studio, Adobe Acrobat Studio, and IBM Optim
Development Studio.
Silverlight Studio, Microsoft Visual Studio, and
IBM Optim Development Studio.
WebSphere Studio, Microsoft Visual Studio, and IBM
Optim Development Studio.
What is a stored procedure?
An SQL statement.
An SQL statement to execute a program.
A method for using wrappers to encode SQL.
A program to execute SQL statements.
What would the “SQL CONNECT” statement be used
for when coding stored procedures?
It connects DB2 with Java.
It connects DB2 with the database.
It allows an application to communicate with DB2.
It connects DB2 with Active Directory.
Which of the following is a DB2 mechanism that
ensures data integrity between tables related by Primary & Foreign Keys
Referential integrity
Data Integrity
Pre-compilation
B & C
When are dynamic SQL applications prepared?
When the program is not running.
While the program is running.
When the system is turned off.
When the program is sandboxed.
Given the following DDL statement: CREATE
TABLE newsmart1 LIKE smart1 Which of the following would occur as a result of
the statement execution?
newsmart1 has same triggers as smart1
newsmart1 has the same primary key as smart1
newsmart1 columns have same attributes as smart1
newsmart1 is populated with smart1 data
Which of the following occurs if a DB2
procedure or application ends abnormally during an active unit of work?
The unit of work moves to CHECK_PENDING
The unit of work is rolled back
The unit of wok moves to pending state
The unit of work remains active
What must happen before using the EXPLAIN
statement?
A stored procedure must be made to process it.
A Java wrapper must be written to use it.
A planned procedure must be made to execute it.
A plan table must be created to hold the results of
EXPLAIN.
What code is used to start the CICS attachment
facility?
CICSgo
CICSstart
EXEC CICS LINK PROGRAM('DSNyCOM ')
CICS
A declared temporary table is used for which
of the following purposes?
To provide an area for database manager sorts
To create a backup copy of a database or table
space
To share result sets between applications
To store intermediate results
Which of the following is an example of a
programming method not used in DB2 programming?
Hadoop
OBDC
Dynamic SQL.
Static SQL
Which of the following statements eliminates
all but one of each set of repeated rows inthe final result table?
SELECT * FROM DISTINCT T1
SELECT DISTINCT * FROM t1
SELECT UNIQUE * FROM t1
SELECT UNIQUE (*) FROM t1
What is a host structure?
A variable group of hosts that an SQL statement can
refer to by using any name.
A group of host variables that an SQL statement can
refer to by using a single name.
A group of host variables that an SQL statement can
process.
A single host variable that an SQL statement can
refer to by using a single name.
Which of the following DB2 objects allows
multiple users to access data in a table with each only being able to access
certain subsets of the data?
Views
Aliases
Indexes
Mirrored Tables
What type of SQL operator would you use to
combine the results of 2 Select statements while retaining the duplicates
Union All
Intersect
Union
Inner Join
What is SQLCA used for?
It checks the code of SQL statements.
It checks the locking of SQL statements.
It checks the execution of SQL statements.
It parses stored procedures.
What are three examples of languages you can
use to program in DB2?
Perl, Python, and PL/SQL.
COBOL, Fortran, and Perl.
HTML, MSDOS, Python.
PHP, Ruby on Rails, and Haskell.
What technique can DB2 use to more effectively
interpret data from EXPLAIN tables?
Quadratic processing.
Networked processing.
Unilateral processing.
Parallel processing.
When coding in a language that requires a host
variable declaration, what must precede it?
;hostchar
;iostream.h
;hostvar
;varchar
Which of the following is the correct syntax
for an input variable of a PARMLIST string?
DCL SINTAR BIN FAXED(15);
DCL SINvTAR BIN FIXED(15);
(15)DCL SINTVAR BIN FIXED;
DCL SINTVAR BIN FIXED(15);
Before executing SQL statements, you want to
know whether the CICS attachment facility is available. How do you check?
You shut down DB2 and check in the CICS kernal.
You check in the CICS configuration file.
You check in the CICS configuration menu.
Use the EXTRACT EXIT command in your application,
or INQUIRE EXITPROGRAM in version 4.0.
What are two examples of steps that must be
taken before coding an application with embedded static or dynamic SQL
statements?
Pretranscribe, and bind.
Transcribe, and bind.
Compile, and bind.
Precompile, and bind.
Why would you receive a command response of
NORMAL when the attachment facility is not available?
The exit was disabled.
The exit was not enabled.
The exit was not ENABLE STARTED.
The database was disabled.
What is the name of the effect when a system
continues to receive work, but is down?
Hutchins effect.
Auger effect.
Stormdrain effect.
Faraday effect.
Which statement about an index is NOT true?
An index always has a balanced tree structure.
A clustering index influences the physical sequence
of data in the tablespace.
An index has its own INDEX SPACE, automatically
created
The name of an index can be mentioned in a Select
statement, to improve the performance of the query.
Which of the following can be accomplished
with a single UPDATE statement?
Updating multiple tables based on a WHERE clause
Updating a view consisting of joined tables
Updating multiple tables
Updating a table based on a sub-select using joined
tables
What must be done to a DB2 application before
it can run, and why?
It has to be compressed first so it can recognize
SQL statements.
It has to be binded first so it can recognize SQL
statements.
It has to be encrypted first so it can recognize
SQL statements.
It has to be compiled first so it can recognize SQL
statements.
What is an example of a benefit of using the
Java programming language?
Once you write the program, it can be used with any
other program or software.
Once you write the program, it acts like a stored
procedure.
Once you develop an application, it can be run anywhere.
There is no benefit to programming with Java.
When you don't know the format of an SQL
statement within a program you're writing, what is a good option?
T-SQL.
NoSQL.
Dynamic SQL.
Static SQL.
When is it necessary to precompile DB2 REXX procedures
before running them?
Every time because they use static SQL.
Only when you're using a Java wrapper.
Never because they use dynamic SQL.
Only sometimes, because they vary from static to
dynamic SQL.
Consider the following: DECLARE MYCURS CURSOR
FOR SELECT * FROM MYTABLE WHERE COL1 > :NUM Which of the following embedded
SQL statements will NOT generate an error?
SELECT MYCURS INTO :HOSTMYTABLE:IND
FETCH * INTO :HOSTMYTABLE:IND FROM MYTABLE
FETCH MYCURS INTO :HOSTMYTABLE:IND
SELECT * INTO :HOSTMYTABLE:IND FROM MYCURS
Can static SQL statements be changed without
altering the program itself?
Yes, but the program has to be altered.
Yes, but the statements have to be changed as well.
Yes. DB2 is a fully dynamic program, accepting all
forms of SQL.
No.
Given the statement: CREATE TABLE t1 (c1
INTEGER NOT NULL,c2 INTEGER,PRIMARY KEY(c1),FOREIGN KEY(c2) REFERENCES t2) How
many non-unique indexes are defined for table t1?
2
1
3
0
What is a benefit of concurrency in SQL
application programming?
Increasing data access points.
Increase of data conflicts.
Minimization of data access conflicts.
Multi-threaded compiling.
How are ODBC calls binded?
They are not binded because they use standard
functions to execute SQL.
They are binded at compile time.
They are pre-process binded.
They are binded using standard functions in SQL.
SQL statements embedded into an application is
called what?
PL/SQL
Dynamic SQL.
T-SQL.
Static SQL.
Which of the following is not one of the types
of authorizations associated with a DB2 user?
Current SQLID
Secondary Authorization ID
SQL Authorization ID
Primary Authorization ID
What's the output of a DB2 Bind
DB2 Application plan
Database request module
DB2 Catalog
Load Module
How does one prepare a Java program that
contains JDBC methods?
Use the “DB2java” command.
Use the “javac” command.
Use the “JBDC” command.
Use the “javago” command.
When declaring a foreign key on a table,
referencing an existing primary key with complete definition, what might differ
between foreign key and primary key?
B & E
The nullability of one of the included columns.
The data type of the included columns.
The order of the included columns.
Which of the following is true about the
EXPLAIN command
EXPLAIN is used to display the access path as
determined by the optimizer for a SQL statement
C,D & E
It can be used in SPUFI(for single SQL statement)
It can be used in BIND step(for embedded SQL).
In what catalog table must stored procedures
be defined?
CONFPROC
SYSPROCEDURES
CONFPROCEDURES
STOPROCEDURES
SQLSTATE is a standard set of error messages
and warnings in which the first two characters defines the class and the last
three defines the subclass of the error. Which of the following SQLSTATE codes
is interpreted as "No data returned"?
22xxx
01xxx
02xxx
00xxx
What must a SELECT statement be coded within?
A DECLARE CURSOR.
A DEFINE CURSOR
A SELECT CURSOR
A STATIC CURSOR
What is an example of an underlying cause for
regressions caused by changes in DB2?
Regression analysis.
Accurate statistics.
Inadequate statistics.
Redundant statistics.
Which of the following is a correct
syntactical example of written SQL code?
TYPE INTO //:book_type FROM BOOK_TYPES WHERE//
EXEC SQL SELECT TYPE INTO :book_type FROM
BOOK_TYPES WHERE
EXEC: SELECT INTO :book_type FROM BOOKTYPES WHERE
SELECT TYPE INTO :book_type FROM BOOK_TYPES WHERE
Which of the following is correct SQLJ syntax?
#sql [myConnCtxt] { UPDATE EMP
#sql [myConnCtxt] :: UPDATE//EMP
///#sql [myConnCtxt]}}{{||\\ { UPDATE EMP///
#//sql [myConnCtXt] { update EMP
How many indexes will be created by the
following statement? Create table Smarterer { Col1 int not null primary key,
Col2 char (64), Col3 char (32), Col4 int not null, Constraint c4 unique
(Col4,Col1) }
1
2
0
3
Which of the following is not a DB2 object
Storage Group
Table
Synonym
Column
What are the maximum number of tables that can
be joined in DB2
15
16
12
10
What happens when the execution of a utility
is terminated by the TERM command?
The execution ends abnormally, the corresponding
row in the SYSUTIL table is not removed, all resources are freed.
The execution ends normally, the corresponding row
in the SYSUTIL table is not removed, all resources are freed.
The execution ends abnormally, the corresponding
row in the SYSUTIL table is removed, all resources are freed.
The execution ends normally, the corresponding row
in the SYSUTIL table is removed, all resources are freed.
Can the SQL procedural language be used for
advanced functions?
Yes, but with a supplementary set of Java wrappers.
Yes, but they must be precompiled first.
Yes, but with some supplementary coding.
No.
What happens to SQL statements if there are no
stored procedures?
They are disregarded.
They are embedded.
They are locked.
They are corrupted.
Given the following DDL statements: CREATE
TABLE Smart1 (a INT, b INT, c INT) CREATE VIEW View1 AS SELECT a,b,c FROM
Smart1 WHERE a > 250 WITH CHECK OPTION Which of the following INSERT is
correct
INSERT INTO Smart1 VALUES (200, 2, 3)
INSERT INTO View1 VALUES (250, 2, 3)
INSERT INTO View1 VALUES (300, 2, 3)
INSERT INTO Smart1 VALUES (350, 2, 3)
Why must the DB2 precompiler be used to
execute SQL statements?
It isn't necessary to use the DB2 precompiler.
The DB2 precompiler wraps the SQL statements in
Java for easier use.
The DB2 precompiler executes a HIVE database for SQL
operation.
The compiler does not recognize SQL statements.
What is the Java data access platform used in
DB2?
pureQuery.
aQuery.
dQuery.
jQuery.
Consider the following SQL statement, executed
by user S001: CREATE VIEW BOSTON_TEAMS AS SELECT * FROM TEAMS WHERE STATE =
'MA' User S002 has INSERT authority on this view, what would happen if he tries
to insert a row into this view, where the STATE field of that line contains a
value of ‘IN’?
You can’t insert into a view, because views don’t
contain actual data; they’re just virtual windows on base tables.
The row won’t be inserted because the field STATE
doesn’t have the value ‘MA’.
The row will be inserted in the table S001.TEAMS,
but it will never show up in a SELECT on this view.
The row will be inserted only if the user has an
authority to insert on table S001.TEAMS, but it will never show up in a SELECT
on this view.
When should we execute a REBIND rather than a
BIND
C & E
When the embedded SQL of the application is
changed.
When an index is added to one of the tables used by
the embedded SQL.
After the execution of a RUNSTATS.
Which statement about tablespaces is true?
When creating a tablespace, one must indicate
either the storage group or the bufferpool to be used by this tablespace. If
neither is defined, the creation of the tablespace will fail.
A tablespace is divided in units called pages ,
which hold one or more rows of a table. If a row of 8000 bytes is placed in a
4K page, th e row will automatically be split over two pages.
Dropping a tablespace will not only remove all
tables of the tablespace itself, but also all indexes created on these tables,
even tho ugh they are stored separately in their own indexspace.
A tablespace is divided in units called pages ,
which hold one or more rows of a table. If a row of 8000 bytes is placed in a
4K page, the page will automatically be resized to 8K.
What four languages can host variable arrays
be specified in?
C, C++, COBOL, or PL/I.
C, C++, Fortran, or PL/I.
Javascript, C++, COBOL, or PL/I.
C, C++, Perl, or PL/I.
Which of the following represents a function
that is performed for each row in a DB2 table
Aggregate Function
Group by Having
Group by
Scalar function
When a column has an extension of WITH DEFAULT
NULL and a unique index is created on this column, what will be the effects on
the possible null values in that column?
There is still a single null allowed, since it is
unique as such.
It has no influence on the possible null values;
DB2 doesn’t consider nulls when it comes to an index.
C & F
Nulls are no longer allowed, since nulls aren’t
unique.
Which is an example of a column that would
contain statistics necessary for programming?
CRDF
CARDF
CRDX
CARDY
Which of the following is not a type of
dynamic SQL?
Deferred embedded SQL.
Interactive SQL.
Embedded dynamic SQL.
Enhanced PL/SQL
Which of the following statements concerning
locking on TABLESPACE level is correct?
When a TABLESPACE is S-locked by another user, a U
lock can be placed. However, an X lock is not compatible. A IX lock (Intent to
eXclusively use) will be placed to indicate that a X lock is waiting to be
placed.
When a TABLESPACE is S-locked by another user,
neither a U lock or an X lock is com- patible on this level. In both cases an I
lock (IU lock, IX lock) will be placed to indicate that a U lock or X lock is
waiting to be placed.
When a TABLESPACE is S-locked by another user, a
U-lock can be placed. However, an X-lock is not compatible and will have to
wait until the S-lock is released.
When a TABLESPACE is S-locked (Shared use) by
another user, a U lock (Update use) can be placed. However, an X lock
(eXclusive use) on this level will force the S lock to be dropped, its
transaction to be rolled-back to be able to place the X lock.
Given the following cursor declaration:
DECLARE CLASSUPDATE CURSOR FOR SELECT CSTITLE FROM T001.CLASSS FOR UPDATE OF
CDUR Which of the following embedded SQL statements will use this cursor
correctly?
UPDATE T001.CLASSS SET CDUR = 5 WHERE CURRENT OF
CLASSUPDATE
UPDATE T001.CLASSS C SET C.CSTITLE = 'New Title'
WHERE CURRENT OF CLASSUPDATE
UPDATE SET CDUR = 5 WHERE CURRENT OF CLASSUPDATE
UPDATE T001.CLASSS SET CDUR = 5
If the SQLCA is included in the program, which
of the following host-variables is NOT known and as such unavailable for the
program?
SQLCODE
SQLSTATE
SQLWARN
SQLNUM
Which of the following is not a DB2 datatype
Vargraphic
Long Graphic
Long Vargraphic
Graphic
Which subquery operator compares a single
value to every member of set of value.
Between
In
All
Any
Any database needs to go through a
normalization process to make sure that data is represented only once. This
will eliminate problems with creating or destroying data in the database. The
normalization process is done usually in three steps which results in first,
second and third normal forms. Which best describes the process to obtain the
third normal form?
Each table should have related columns.
Each separate table should have a primary key.
If a table has columns not dependent on the primary
keys, they need to be moved in a separate table.
We have a table with multi-valued key. All columns
that are dependent on only one or on some of the keys should be moved in a
different table.
When using JBDC, do you have to recode your
Java application if you want to change between drivers?
No, however the new drivers need wrappers.
Yes, however there is a tool that must be used to
change to a new driver.
No.
No, however a special code must be written to use a
new driver.
In what form is data physically stored in DB2
VSAM ESDS
Flat Files
VSAM LDS
Tables
SELECT empname , paygrade , salary FROM emp,
salgrade WHERE salary BETWEEN lowsal AND highsal ORDER BY paygrade The above is
an example of a
Non equi join
Inner join
None of the above
Outer join
Consider the following embedded SQL statement:
SELECT CLASSNAME INTO :CLASSNAME:IND FROM CLASS WHERE STUDNAME = :STUDNAME In
which order should the following program variables be evaluated by the part of
the program just after this SQL statement?
IND, STUDNAME, CLASSNAME
SQLCODE, STUDNAME, CLASSNAME
SQLCODE, CLASSNAME, IND
SQLCODE, IND, CLASSNAME
Besides on TABLE and TABLESPACE level, on what
level can implicit locks be placed by DB2?
On ROW,PAGE and PARTITION level
On PAGE , SEGMENT and PARTITION level.
On PAGE , SEGMENT and PARTITION level.
On ROW , PAGE and SEGMENT level.
When can you not drop a database
Before all indexes connected to tables of this
database are dropped.
If there is still a user connected to the database.
If there is a referential constraint pointing to a
table of this database from a table of another database.
When a DB2 utility has control of any part of the
database.
Subscribe to:
Posts (Atom)
