Given a non-partitioned table space TS1 is built with LOCKSIZE ROW, LOCKMAX 1000 and SEGSIZE 64.
If a unit of work acquires 1001 locks against table space TS1, the lock will be escalated to which of the following levels?
- A . PAGE
- B . TABLE
- C . PARTITION
- D . TABLESPACE
Which of the following are contained in the PLAN_TABLE? (Choose two.)
- A . RUNSTATS recommendations
- B . Access to LOB values, which are stored separately from the base table
- C . Access to parent or dependent tables needed to enforce referential constraints
- D . Whether an index access or table space scan is used for each access to a table
- E . The join methods and types that are used, and the order in which DB2 joins the tables
Which privilege will user smith be able to grant to another user on table emp when the following SQL is executed?
GRANT ALL ON TABLE emp TO smith WITH GRANT OPTION;
- A . BIND
- B . REORG
- C . TRIGGER
- D . EXECUTE
Which of the following utilities CANNOT be executed from the DB2 utilities panel in DB2I?
- A . COPY
- B . REORG
- C . STOSPACE
- D . BACKUP SYSTEM
D
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/ugref/src/tpc/ db2z_useutilitiespaneldb2i.html
Which of the following locking scenarios requires the least amount of real storage in the IRLM address space?
- A . 10 row locks
- B . 200 page locks
- C . A single table space lock
- D . 6 partition locks (given a table space with a total of 10 partitions)
Which of the following is NOT TRUE?
- A . An index can be created on XML columns
- B . Length must be specified for an XML column
- C . XML columns can be added to existing tables
- D . Triggers can be created on tables with XML columns
Given the following CREATE TABLE statement, and a unique index on column EMPID, which set of SQL statements is valid?
- A .
- B .
- C .
- D .
The DB2 subsystem DSN has been correctly configured to access a remote DB2 location MYREMOTE.
Which of the following processes can be used on subsystem DSN to access MYUSER.EMP table at MYREMOTE location?
- A . SELECT * FROM MYUSER.EMP;
- B . CONNECT TO MYREMOTE; SELECT * FROM MYUSER.EMP;
- C . SELECT * FROM MYUSER.MYREMOTE.EMP;
- D . CREATE ALIAS EMP_ALIAS FOR LOCATION.MYREMOTE.MYUSER.EMP;
SELECT * FROM MYUSER.EMP_ALIAS;
Which of the following elements is affected by a ROLLBACK statement?
- A . LOB table spaces
- B . Changes in cursor positioning
- C . Acquisition and release of locks
- D . Caching of rolled back statements
A
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/ db2z_sql_rollback.html
Which statement is TRUE regarding ROLLBACK processing?
- A . It only reads active logs
- B . It always needs archive logs
- C . It can never be processed just from memory
- D . It uses output buffers, active and archive logs
Which of the following SQL constructs does NOT provide information about the success or failure of the SQL statement that was just executed?
- A . SQLCA
- B . SQLDA
- C . SQLCODE
- D . SQLSTATE
Your current authorization ID is DBA. Only one of your secondary authorization IDs, DB2, has the privilege to create an index DB2.AAA on a table. The appropriate SQL statement starts with “CREATE INDEX AAA ON…”.
Which of the following statements must precede the CREATE statement so that you can create the index?
- A . SET CURRENT SQLID = ‘DB2’;
- B . SET CURRENT RULES = ‘DB2’;
- C . No preceding statement is needed
- D . SET CURRENT SCHEMA = ‘DB2’;
The metadata for which of the following objects is NOT stored in the DB2 catalog table SYSIBM.SYSTABLES?
- A . A view
- B . A temporal table
- C . A created global temporary table
- D . A declared global temporary table
In a unique referential constraint, which type of key does a foreign key reference?
- A . Child key
- B . Collation key
- C . Parent key
- D . Secondary key
C
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/ com.ibm.db2.luw.admin.dbobj.doc/doc/c0020166.html
The following definition for COL1 is given by this statement:
CREATE TABLE… (COL1 BINARY (05))
Which of the following statements will successfully insert data into the table?
- A . INSERT INTO… (COL1) VALUES(HEX(‘12345’));
- B . INSERT INTO… (COL1) VALUES(BLOB(‘12345’));
- C . INSERT INTO… (COL1) VALUES(CHAR(‘12345’));
- D . INSERT INTO… (COL1) VALUES(BINARY(‘123456’));
If a scrollable cursor is INSENSITIVE, which of the following applies?
- A . The cursor is read only
- B . WITH ROWSET POSITIONING is not allowed
- C . The select-statement must not contain an SQL data change statement
- D . The cursor has sensitivity to changes that are made to the database after the result table is materialized
A
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/java/src/tpc/ imjcc_tjvsjscr.html
You want to reduce lock contention that results from uncommitted insert and delete operations by enabling transactions that read data to access only the currently committed data rather than waiting for the uncommitted changes to be resolved.
How can you accomplish this?
- A . Bind your application with ISOLATION(UR)
- B . Bind your application with ISOLATION(CS)
- C . Bind your application with CONCURRENTACCESSRESOLUTION (WAITFOROUTCOME)
- D . Bind your application with CONCURRENTACCESSRESOLUTION (USECURRENTLYCOMMITTED)
D
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/perf/src/tpc/ db2z_accesscommitteddata.html
Given the following CREATE VIEW statement and sample data; your login id is “ROGER”.
What will be the result for this query?
- A . 6
- B . 5
- C . 4
- D . 2
Assume DELETE triggers exist on the employee table.
Which of the following statements will delete all the rows in the employee table and ignore any DELETE triggers?
- A . TRUNCATE employee;
- B . DELETE FROM employee;
- C . DELETE FROM employee WHERE…;
- D . TRUNCATE employee RESTRICT WHEN DELETE TRIGGERS;
B
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/ db2z_sql_delete.html
Which of the following is a characteristic of a stand-alone utility?
- A . It can only be executed using JCL
- B . It can only be executed by a user with SYSADM
- C . It can only be executed if no other utilities are running
- D . It can only be executed in a non-data sharing environment
A
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/ugref/src/tpc/ db2z_invokestandaloneutl.html
You are designing a payroll table that will contain a salary column.
Which of the following is the appropriate built-in data type for a salary column?
- A . REAL
- B . BIGINT
- C . DECIMAL
- D . INTEGER
C
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SS6NHC/ com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0000791.html
Which package level privileges does the user smith have when the following statement is executed?
GRANT ALL ON PACKAGE clct1.pkg1 TO smith;
- A . GRANT, COPY, BIND, RUN
- B . BIND, EXECUTE, COPY, RUN
- C . CREATE, COPY, RUN, EXECUTE
- D . COPY, BINDADD, SQL, EXECUTE
B
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/ db2z_sql_grantpackageprivileges.html
Which of the following can activate a trigger?
- A . A row is inserted
- B . A table or view is created
- C . A check constraint is defined
- D . A referential constraint is violated
D
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/intro/src/tpc/ db2z_triggers.html
In which address space would a native SQL stored procedure run?
- A . WLM
- B . DIST
- C . DBM1
- D . The address space of the calling program
C
Explanation:
Reference: http://www.ibmbigdatahub.com/blog/db2-native-sql-procedures-future-computing
You want to see all messages that were generated by one specific DB2 subsystem such as log switches and timeout messages.
Which job log would show all of these?
- A . MSTR
- B . DBM1
- C . DIST
- D . IRLM
Which of the following statements about a table with a LOB column is TRUE?
- A . It doesn’t need an auxiliary table if it has an inline LOB
- B . It must also have a ROWID column and an auxiliary table
- C . It doesn’t need a ROWID column (implicit or explicit) if a LOB locator will be used
- D . It must be in a partitioned by growth (PBG) universal table space with a ROWID column
Which catalog table provides information about the validity of a bound package?
- A . SYSIBM.SYSPACKLIST
- B . SYSIBM.SYSPACKAGE
- C . SYSIBM.SYSPACKDEP
- D . SYSIBM.SYSPACKAUTH
A
Explanation:
Reference: https://books.google.com.pk/books?id=QH3JAgAAQBAJ&pg=PA256&lpg=PA256&dq=db2+11 +catalog+table+provides+information+about+the+validity+of+a+bound +package&source=bl&ots=4G_RwWpnUF&sig=Lm-LaNpt_SRiZcdhGxiX7ehVIfY&hl=en&sa=X&ved=0ahUKEwjLmPz66drZAhUO6aQKHW_2C9UQ6AEITjAF# v=onepage&q=db2%2011%20catalog%20table%20provides%20information%20about%20the%20validity %20of%20a%20bound%20package&f=false
Which of the following statements concerning a bi-temporal table is TRUE?
- A . It provides business history, but is not useful for audit purposes
- B . It is defined with both an application period and a system period
- C . It is useful for audit purposes, but is not useful for business history
- D . It contains data reflecting two different times C current time and one previous time
B
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/intro/src/tpc/ db2z_creationoftableswithdataversioning.html
What is the result XQuery FLWOR expression when running the following scenario?
- A . Joe Smith
- B . <name> Joe Smith </name>
- C . <employeeinfo><name> Joe Smith </name></employeeinfo>
- D . <name> Joe Smith </name><department name>DB2</department name>
Given the following table and index definitions, what type of index will be created?
- A . Non-partitioned index
- B . Non-partitioned secondary index
- C . Data-partitioned secondary index
- D . Data-partitioned partitioning index
While DSN is running, which of the following commands CANNOT be successfully executed?
- A . CSTOP DB2
- B . CSTART DB2
- C . CTERM UTILITY
- D . CDISPLAY UTILITY
Which of the following is TRUE for using LOAD on a partition-by-growth table space?
- A . Data can only be loaded at the partition level
- B . Data can only be loaded at the table space level
- C . Data can only be loaded using a clustering index
- D . Data can be loaded at either the table space level or the partition level
A
Explanation:
Reference: https://www.google.com/url? sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwjXpIn74tzZAhVRPsAKHRYG BGUQFggnMAA&url=https%3A%2F%2Fwww.ibm.com%2Fsupport%2Fknowledgecenter%2Fen% 2FSSEPEK_11.0.0%2Fintro%2Fsrc%2Ftpc% 2Fdb2z_partitionbygrowthtablespaces.html&usg=AOvVaw0DCKeDA6MfIUE5UQWhFe_G
Which of the following can be defined on the basis of a query?
- A . An alias
- B . A base table
- C . An accelerator-only table
- D . A materialized query table
D
Explanation:
Reference: https://www.ibm.com/developerworks/data/library/techarticle/dm-0509melnyk/
Assuming column hire date is a DATE data type, which SQL statement will retrieve the maximum salary (salary) and minimum salary(salary) for each job(job) from EMPLOYEE table for those hired(hiredate) before 1965?
- A .
- B .
- C .
- D .
In order to run a DB2 program, the user issues the following commands:
TSO prompt: READY
USER enters: DSN SYS(SSTR)
USER enters: RUN PROGRAM(MYPROG)
USER enters: END
What is the result?
- A . DB2 does not allow these commands
- B . DSN command can not be used to run a program
- C . Start a DSN session with DB2 subsystem SSTR, run a program and end the DSN session
- D . Start a DSN session with system parameters SSTR, run a program and end the DSN session
C
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/comref/src/tpc/ db2z_cmd_dsn.html
Which of the following describes OLTP workload rather than an analytics workload?
- A . Extreme data volume
- B . Temporary or intermediate staging of data
- C . Set-oriented processing and bulk operations
- D . A large number of short, discrete, atomic transactions
D
Explanation:
Reference: http://www.ibmbigdatahub.com/sites/default/files/document/IBM-Netezza-Understanding-Analytic-Workloads-eBook.pdf
Given the tables:
Which SQL statement would join the tables on product number (prod#) and provide a table of what products (product), if any, the parts (part) are used? Limit the result to only parts where suppliers (supplier) do not start with the letter A.
- A .
- B .
- C .
- D .
Which of the following are advantages of DB2 for z/OS data sharing over a standalone DB2 subsystem implementation? (Choose two.)
- A . Reduced active logs
- B . Reduced storage use
- C . Higher transaction rates
- D . Reduced lock contention
- E . Increased data availability
The maximum size for each partition of a partition-by-range table space, with fewer than 255 partitions, is determined by which combination of options? (Choose two.)
- A . DSSIZE
- B . PRIQTY
- C . NUMPARTS
- D . BUFFERPOOL
- E . MAXPARTITIONS
AC
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/ db2z_sql_createtablespace.html
Which of the following statements about a system-period temporal table is TRUE?
- A . Rows can be archived into a history table which is always read only
- B . Every row has a pair of timestamps which is set and managed by the application
- C . AN SQL query for auditing can be run against current data or history data, but not both
- D . The table contains only the current active rows
B
Explanation:
Explanation In a system-period temporal table, every row has a pair of timestamps, which are set by the DB2 system and recorded in the begin_column and end_column columns. DB2 uses the SYSTEM_TIME period to audit the data changes. It provides the application with the database managed row begin and row end timestamp maintenance, providing non-overlapping time periods with no gaps in time.
Reference: https://www.ibm.com/developerworks/data/library/techarticle/dm-1410temporal-tables-db2zos/ index.html
Which types of user-defined data types are supported? (Choose two.)
- A . CHAR
- B . ARRAY
- C . NUMERIC
- D . DISTINCT
- E . TIMESTAMP
BD
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/ db2z_userdefineddatatypes.html
Which BIND option determines when DB2 will do authorization checking?
- A . DEFER
- B . DEPLOY
- C . VALIDATE
- D . CURRENTDATA
C
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/comref/src/tpc/ db2z_bindoptvalidate.html
COL1 is defined as CHAR(05) and contains CHAR(‘12345’).
Which of the following expressions returns the digits 12345 for further calculation?
- A . HEX(COL1)
- B . DEC(COL1)
- C . PACK(COL1)
- D . DIGITS(COL1)
Which DB2 address space manages DB2 locking?
- A . IRLM
- B . DIST
- C . DBM1
- D . MSTR
A
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/inst/src/tpc/ db2z_irlmaddrspcstgreq.html
What are two major encoding schemes? (Choose two.)
- A . UNIX
- B . Linux
- C . CCSID
- D . Unicode
- E . EBCDIC
DE
Explanation:
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/intro/src/tpc/ db2z_introcodepage.html
We want to increase the salary for each employee (EMP) in the department (WORKDEPT) D11 and we want to see the new salaries (SALARY) for each of them.
Which single SQL statement will do this?
- A .
- B .
- C .
- D .
You want to limit the access of a table to allow users to only see certain columns and/or rows.
Which of the following options CANNOT be used to accomplish this?
- A . A view
- B . Multilevel security
- C . An MQT (Materialized Query Table)
- D . A combination of row permissions and column masks
Which of the following tables can be used to set the default of the special register CURRENT APPLICATION COMPATIBILITY for remote users?
- A . The resource limit facility table authid.DSNRLMTxx
- B . The DB2 catalog table SYSIBM.SYSENVIRONMENT
- C . The profile table SYSIBM.DSN_PROFILE_ATTRIBUTES
- D . The communications database table SYSIBM.USERNAMES