Question:* Which of the following is true about locking
Answer: • All of the above
Question:* Which of the following is true about the EXPLAIN command
Answer: • C,D & E
Question:* What are two examples of steps that must be taken before coding an application with embedded static or dynamic SQL statements?
Answer: • Precompile, and bind.
Question:* Which of the following statements eliminates all but one of each set of repeated rows inthe final result table?
Answer: • SELECT DISTINCT * FROM t1
Question:* Given the following DDL statement: CREATE TABLE newsmart1 LIKE smart1 Which of the following would occur as a result of the statement execution?
Answer: • newsmart1 columns have same attributes as smart1
Question:* What must happen before using the EXPLAIN statement?
Answer: • A plan table must be created to hold the results of EXPLAIN.
Question:* When are dynamic SQL applications prepared?
Answer: • While the program is running.
Question:* What code is used to start the CICS attachment facility?
Answer: • EXEC CICS LINK PROGRAM('DSNyCOM ')
Question:* Which of the following occurs if a DB2 procedure or application ends abnormally during an active unit of work?
Answer: • The unit of work is rolled back
Question:* A declared temporary table is used for which of the following purposes?
Answer: • To store intermediate results
Question:* What is SQLCA used for?
Answer: • It checks the execution of SQL statements.
Question:* Which of the following is a DB2 mechanism that ensures data integrity between tables related by Primary & Foreign Keys
Answer: • Referential integrity
Question:* What would the “SQL CONNECT” statement be used for when coding stored procedures?
Answer: • It allows an application to communicate with DB2.
Question:* What is a stored procedure?
Answer: • A program to execute SQL statements.
Question:* A role is a database entity that groups together one or more privileges. Which of the following is true for a "role"
Answer: • All of the above
Question:* What is a host structure?
Answer: • A group of host variables that an SQL statement can refer to by using a single name.
Question:* What are three examples of languages you can use to program in DB2?
Answer: • COBOL, Fortran, and Perl.
Question:* Which of the following is an example of a programming method not used in DB2 programming?
Answer: • Hadoop
Question:* 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?
Answer: • Views
Question:* What type of SQL operator would you use to combine the results of 2 Select statements while retaining the duplicates
Answer: • Union All
Question:* What technique can DB2 use to more effectively interpret data from EXPLAIN tables?
Answer: • Parallel processing.
Question:* What are the three primary development environments for DB2?
Answer: • WebSphere Studio, Microsoft Visual Studio, and IBM Optim Development Studio.
Question:* What is the name of the effect when a system continues to receive work, but is down?
Answer: • Stormdrain effect.
Question:* When is it necessary to precompile DB2 REXX procedures before running them?
Answer: • Never because they use dynamic SQL.
Question:* What is a lightweight web application created from multiple sources?
Answer: • A mashup.
Question:* Before executing SQL statements, you want to know whether the CICS attachment facility is available. How do you check?
Answer: • Use the EXTRACT EXIT command in your application, or INQUIRE EXITPROGRAM in version 4.0.
Question:* When coding in a language that requires a host variable declaration, what must precede it?
Answer: • ;hostvar
Question:* Which of the following is the correct syntax for an input variable of a PARMLIST string?
Answer: • DCL SINTVAR BIN FIXED(15);
Question:* Which of the following can be accomplished with a single UPDATE statement?
Answer: • Updating a table based on a sub-select using joined tables
Question:* What is an example of a benefit of using the Java programming language?
Answer: • Once you develop an application, it can be run anywhere.
Question:* What must be done to a DB2 application before it can run, and why?
Answer: • It has to be binded first so it can recognize SQL statements.
Question:* Can static SQL statements be changed without altering the program itself?
Answer: • No.
Question:* SQL statements embedded into an application is called what?
Answer: • Static SQL.
Question:* Why would you receive a command response of NORMAL when the attachment facility is not available?
Answer: • The exit was not ENABLE STARTED.
Question:* Which statement about an index is NOT true?
Answer: • The name of an index can be mentioned in a Select statement, to improve the performance of the query.
Question:* 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?
Answer: • FETCH MYCURS INTO :HOSTMYTABLE:IND
Question:* 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?
Answer: • 0
Question:* When you don't know the format of an SQL statement within a program you're writing, what is a good option?
Answer: • Dynamic SQL.
Question:* What's the output of a DB2 Bind
Answer: • DB2 Application plan
Question:* What is a benefit of concurrency in SQL application programming?
Answer: • Minimization of data access conflicts.
Question:* How does one prepare a Java program that contains JDBC methods?
Answer: • Use the “javac” command.
Question:* 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) }
Answer: • 2
Question:* 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?
Answer: • He/She only has a SELECT authorization on that view.
Question:* How are ODBC calls binded?
Answer: • They are not binded because they use standard functions to execute SQL.
Question:* Which of the following is not a DB2 object
Answer: • Column
Question:* Which of the following is not one of the types of authorizations associated with a DB2 user?
Answer: • SQL Authorization ID
Question:* 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
Answer: • INSERT INTO View1 VALUES (300, 2, 3)
Question:* What four languages can host variable arrays be specified in?
Answer: • C, C++, COBOL, or PL/I.
Question:* 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"?
Answer: • 02xxx
Question:* In what catalog table must stored procedures be defined?
Answer: • SYSPROCEDURES
Question:* What is an example of an underlying cause for regressions caused by changes in DB2?
Answer: • Inadequate statistics.
Question:* What is the Java data access platform used in DB2?
Answer: • pureQuery.
Question:* Why must the DB2 precompiler be used to execute SQL statements?
Answer: • The compiler does not recognize SQL statements.
Question:* Can the SQL procedural language be used for advanced functions?
Answer: • No.
Question:* 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?
Answer: • The nullability of one of the included columns.
Question:* 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’?
Answer: • The row will be inserted in the table S001.TEAMS, but it will never show up in a SELECT on this view.
Question:* Which of the following represents a function that is performed for each row in a DB2 table
Answer: • Scalar function
Question:* 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?
Answer: • There is still a single null allowed, since it is unique as such.
Question:* Which of the following statements concerning locking on TABLESPACE level is correct?
Answer: • 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.
Question:* Which of the following is a correct syntactical example of written SQL code?
Answer: • EXEC SQL SELECT TYPE INTO :book_type FROM BOOK_TYPES WHERE
Question:* What happens when the execution of a utility is terminated by the TERM command?
Answer: • The execution ends normally, the corresponding row in the SYSUTIL table is removed, all resources are freed.
Question:* Which statement about tablespaces is true?
Answer: • 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.
Question:* If the SQLCA is included in the program, which of the following host-variables is NOT known and as such unavailable for the program?
Answer: • SQLNUM
Question:* What are the maximum number of tables that can be joined in DB2
Answer: • 15
Question:* Which of the following is not a type of dynamic SQL?
Answer: • Enhanced PL/SQL
Question:* Which subquery operator compares a single value to every member of set of value.
Answer: • Any
Question:* Which is an example of a column that would contain statistics necessary for programming?
Answer: • CARDF
Question:* What happens to SQL statements if there are no stored procedures?
Answer: • They are embedded.
Question:* 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?
Answer: • UPDATE T001.CLASSS SET CDUR = 5 WHERE CURRENT OF CLASSUPDATE
Question:* What must a SELECT statement be coded within?
Answer: • A DECLARE CURSOR.
Question:* When should we execute a REBIND rather than a BIND
Answer: • C & E
Question:* Which of the following is not a DB2 datatype
Answer: • Long Graphic
Question:* In what form is data physically stored in DB2
Answer: • VSAM LDS
Question:* 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?
Answer: • If a table has columns not dependent on the primary keys, they need to be moved in a separate table.
Question:* 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?
Answer: • SQLCODE, IND, CLASSNAME
Question:* SELECT empname , paygrade , salary FROM emp, salgrade WHERE salary BETWEEN lowsal AND highsal ORDER BY paygrade The above is an example of a
Answer: • Non equi join
Question:* Besides on TABLE and TABLESPACE level, on what level can implicit locks be placed by DB2?
Answer: • On ROW,PAGE and PARTITION level
Question:* When using JBDC, do you have to recode your Java application if you want to change between drivers?
Answer: • No.
Question:* When can you not drop a database
Answer: • When a DB2 utility has control of any part of the database.
Question:* Which of the following is correct SQLJ syntax?
Answer: • #sql [myConnCtxt] { UPDATE EMP
No comments:
Post a Comment