Free Cheat-test Samples and Demo Questions Download
Adobe exams Adobe
Apple exams Apple
Avaya exams Avaya
Check Point exams Check Point
Cisco exams Cisco
Citrix exams Citrix
CIW exams CIW
CompTIA exams CompTIA
CWNP exams CWNP
EC-Council exams EC-Council
EMC exams EMC
Exin exams Exin
Fortinet exams Fortinet
GIAC exams GIAC
Hitachi exams Hitachi
HP exams HP
IBM exams IBM
Isaca exams Isaca
ISC exams ISC
ISEB exams ISEB
Juniper exams Juniper
LPI exams LPI
McAfee exams McAfee
Microsoft exams Microsoft
Oracle exams Oracle
PMI exams PMI
Riverbed exams Riverbed
SNIA exams SAP
Sun exams SAS
Symantec exams Symantec
VMware exams VMware
All certification exams

IBM 000-516 Exam - Cheat-Test.com

Free 000-516 Sample Questions:

1. Which of the following SQL clauses CANNOT be used in a CREATE VIEW statement?
A. GROUP BY
B. ORDER BY
C. Subselect
D. Outer join
Answer: B

2. Given the following partitioned table space index definition:
CREATE INDEX OWN1.IX1
ON OWN1.TB1 (COL_KEY ASC)
USING STOGROUP DSN8G710
PRIQTY 7200
SECQTY 720
CLUSTER
(
PART 1 VALUES(25),
PART 2 VALUES(50),
PART 3 VALUES(75),
PART 4 VALUES(99),
)
BUFFERPOOL BP1;
Which of the following partitions will a data row with a COL_KEY value of 55 fall into?
A. Partiton 1
B. Partition 2
C. Partition 3
D. Partition 4
Answer: C

3. Which of the following is NOT a valid option on a CREATE INDEX statement for a TYPE 2 index?
A. CLOSE
B. DEFER
C. SUBPAGE
D. PIECESIZE
Answer: C

4. Which of the following statements is NOT true for a segmented table space?
A. It is more efficient for table spaces greater than 4 GB
B. It makes free pages available immediately after a commit
C. It generally skips empty pages when a table scan is being processed
D. It has more efficient space map processing than a simple table space
Answer: A

5. When copying a table using CREATE TABLE...LIKE, which of the following is TRUE of the new table?
A. Table check constraints are copied from the original table
B. WITH RESTRICT ON DROP will be copied from the original table
C. Column null attributes will be copied from the original table
D. Primary and foreign keys will be copied from the original table
E. Database and table space information is the same as the original table
Answer: C

6. Given the following CREATE TABLE statement:
CREATE TABLE STAFF
(
EMPID CHAR(6) NOT NULL,
NAME VARCHAR(15) NOT NULL,
SALARY DECIMAL(9,2)
)
IN DATABASE HUMANRES;
Where will DB2 put the STAFF table?
A. In an implicit table space
B. In the default table space
C. In an existing table space
D. In a temporary table space
Answer: A

7. Which of the following tasks CANNOT be accomplished using the DB2 Catalog or Directory?
A. Determine plan/package dependencies
B. Determine referential constraints upon a table
C. Determine the characteristics of indexes on a table
D. Determine table spaces that do not have image copies
E. Determine active/archive log data set characteristics
Answer: E

8. Which TWO of the following system catalog tables contains the correlation between the name of an index and the physical dataset name?
A. SYSIBM.SYSKEYS
B. SYSIBM.SYSINDEXES
C. SYSIBM.SYSKEYSPART
D. SYSIBM.SYSINDEXPART
E. SYSIBM.SYSINDEXSTAT
Answer: BD

9. RUNSTATS with the KEYCARD parameter has been run for all the tables in the payroll application. Which two of the following columns will tell the DBA the estimated number of distinct values in a column of any table?
A. SYSTABLES.CARDF
B. SYSCOLDIST.CARDF
C. SYSTABLEPART.CARDF
D. SYSINDEXPART.CARDF
E. SYSCOLUMNS.COLCARDF
F. SYSCOLSTATS.COLCARD
Answer: BE

10. Which DB2 catalog table can be used to determine if a user has the authorization to bind a new plan?
A. SYSDBAUTH
B. SYSTABAUTH
C. SYSUSERAUTH
D. SYSPLANAUTH
Answer: C

11. Which of the following statements will create a partitioned table space TS1 in database DB1 with 5 partitions with selective partition locking?
A. CREATE TABLESPACE ts1 IN db1 LOCKPART YES MAXROWS 5
B. CREATE TABLESPACE ts1 IN db1 NUMPARTS 5 LOCKPART YES
C. CREATE TABLESPACE ts1 IN db1 PARTITIONS 5 LOCKING YES
D. CREATE TABLESPACE ts1 IN db1 NUMPARTS 5 SELPARTLOCK YES
Answer: B

12. Which of the following ALTER TABLESPACE statements causes DB2 to record data page changes in the space map pages of a segmented table space DSN8D71A.DSN8S71E?
A. ALTER TABLESPACE dsn8d71a.dsn8s71e LOG YES;
B. ALTER TABLESPACE dsn8d71a.dsn8s71e CLOSE YES;
C. ALTER TABLESPACE dsn8d71a.dsn8s71e TRACKMOD YES;
D. ALTER TABLESPACE dsn8d71a.dsn8s71e COMPRESS YES;
E. ALTER TABLESPACE dsn8d71a.dsn8s71e CHANGEMOD YES;
Answer: C

13. Which of the following statements will create a table space TS1 in database DB1 with 3 partitions?
A. CREATE LOB TABLESPACE ts1 IN db1 ...
B. CREATE TABLESPACE ts1 IN db1 NUMPARTS 3 ...
C. CREATE SEGMENTED TABLESPACE ts1 IN db1 NUMPARTS 3 ...
D. CREATE PARTITIONED TABLESPACE ts1 IN db1 NUMPARTS 3 ...
Answer: B

14. Which of the following attributes of a table space CANNOT be changed with the ALTER TABLESPACE statement?
A. ERASE
B. CLOSE
C. SEGSIZE
D. LOCKSIZE
E. COMPRESS
Answer: C

15. Table space DB1.TS1 was created by user id SMITH. The user id of an agent wishing to DROP this table space is JONES. JONES has DBADM authority to database DB1. Which of the following SQL statements could JONES use to DROP table space DB1.TS1 and all associated tables and indexes?
A. DROP TABLESPACE db1.ts1;
B. DROP TABLESPACE smith.ts1;
C. DROP TABLESPACE smith.DB1.TS1;
D. SET CURRENT USER = 'SMITH'; DROP TABLESPACE db1.ts1 CASCADE YES;
E. SET CURRENT SQLID = 'SMITH'; DROP TABLESPACE db1.ts1 CASCADE YES;
Answer: A

16. In order to remove the WITH RESTRICT ON DROP specification from a table, which of the following SQL statements can be used?
A. DROP
B. ALTER
C. DELETE
D. REVOKE
Answer: B

17. Which of the following CANNOT be done with the ALTER TABLE statement?
A. Change the CACHE value for an identity column
B. Turn on auditing for the table
C. Add a primary key to the table
D. Add a column to the end of a table row
Answer: A

18. Which of the following clauses of the ALTER TABLESPACE command require the table space or partition to be in a stopped state before execution?
A. USING VCAT
B. CLOSE YES
C. LOCKSIZE ROW
D. COMPRESS YES
Answer: A

19. Which of the following best describes the difference between an alias and a synonym?
A. An alias may be used to refer to a view; a synonym may not be used to refer to a view.
B. An alias may only be defined with CREATETAB authorization; a synonym may be created with no authorization.
C. An alias may be used in either a dynamic or a static SQL statement; a synonym can only be used in a dynamic SQL statement.
D. A synonym can be used to refer to a table in a remote subsystem; an alias can only be used to refer to a table in the same subsystem in which the alias is defined.
E. An alias can be used to refer to a table in a remote subsystem; a synonym can only be used to refer to a table in the same subsystem in which the synonym is defined.
Answer: E

20. Which of the following is NOT true when comparing an alias to a synonym?
A. A synonym can only be used by its owner; an alias can be referenced by others.
B. Synonyms are recorded in SYSIBM.SYSSYNONYMS; aliases are recorded in SYSIBM.SYSALIASES.
C. A synonym is dropped along with its table; an alias is not dropped when its table is dropped.
D. A synonym is referenced by only a simple name; an alias can be referenced by a high-level qualifier.
Answer: B

21. Which of the following are required for calling a stored procedure?
A. Owner ID, procedure name, and data type parameters
B. Procedure name, and number and type of input and output parameters
C. Procedure name, work load manager (WLM) environment name, and number of parameters
D. Run time execution ID, procedure name, and number and type of input and output parameters
Answer: D

22. The DB2 Stored Procedure Builder (SPB) Wizards assist the creation of new DB2 stored procedures. Which of the following is NOT one of the OS/390 options included in the Build component of the SPB?
A. Bind
B. Compile
C. Prelink
D. Authorization
E. SQL Precompile
Answer: D

23. Which of the following catalog tables are updated after the following DDL is executed?
CREATE PROCEDURE SYSPROC.MYPROC(IN INT, OUT INT, OUT DECIMAL(7,2))
LANGUAGE COBOL
EXTERNAL NAME MYMODULE
PARAMETER STYLE DB2SQL
WLM ENVIRONMENT PARTSA
RESULT SETS 1
RUN OPTIONS
'HEAP(,,ANY),BELOW(4K,,),ALL31(ON),STACK(,,ANY,)';
A. SYSROUTINES, SYSPARMS
B. SYSPROCEDURES, SYSPARMS
C. SYSPROCEDURES, SYSROUTINES
D. SYSROUTINES, SYSROUTINEAUTH
Answer: A

24. A changed stored procedure program needs to be implemented, ACME1, that has changed both the source code and added a new parm field. Which of the following statements will activate this changed program? It is defined in WLM application environment ACME_REP.
A. 1. Issue OS/390 command: VARY wlm,APPLENV=acme_rep,REFRESH
B. 1. Issue DB2 command: -STO PROCEDURE (acme1) 2. Issue DB2 command: -STA PROCEDURE (acme1)
C. 1. Issue DB2 command: -STO PROCEDURE (acme1) 2. Issue DB2 command: -STA PROCEDURE (acme1) 3. Issue OS/390 command: VARY wlm,APPLENV=acme_rep,START
D. 1. Issue DB2 command: -STO PROCEDURE (acme1) 2. Issue DB2 command: -STA PROCEDURE (acme1) 3. Issue OS/390 command: VARY wlm,APPLENV=acme_rep,REFRESH
Answer: D

25. Which of the following is NOT true when adding primary or foreign keys to an existing table?
A. When adding a foreign key, a unique parent key index on the parent table must exist.
B. Adding a foreign key causes DB2 to immediately validate the data values used in the relationship.
C. When adding a primary key, the table must already have a unique index on the key columns.
D. When adding a primary key, the PRIMARY KEY clause must be used in an ALTER TABLE statement.
Answer: B

26. The DEPARTMENT table is a parent of the EMPLOYEE and DEPARTMENT_INVENTORY tables. The primary key of DEPARTMENT is DEPTNO. The primary key of EMPLOYEE is EMPNO and the foreign key is WORKDEPT. The primary key of DEPARTMENT_INVENTORY is PARTNO,PARTDEPT and the foreign key is PARTDEPT. The delete rule of EMPLOYEE is CASCADE and the delete rule of DEPARTMENT_INVENTORY is SET NULL.
Given the following data, which of the following will occur if DEPTNO 01 is deleted?
DEPARTMENT
DEPTNO DEPTNAME
01 SALES
02 MARKETING
03 HUMAN RESOURCES
EMPLOYEE
EMPNO WORKDEPT
10 01
20 02
30 02
DEPARTMENT_INVENTORY
PARTNO PARTDEPT
AAA 02
BBB 02
CCC 03
A. No action is taken
B. EMPLOYEE.WORKDEPT is set to NULL where WORKDEPT = 01
C. The row from DEPARTMENT where DEPTNO = 01 is deleted; EMPLOYEE.WORKDEPT 01 is set to NULL
D. The row from DEPARTMENT where DEPTNO = 01 is deleted; the row from EMPLOYEE where EMPNO=10 row is deleted
Answer: D

27. Given the following DDL statements,
CREATE LOB TABLESPACE moviets...;
CREATE AUX TABLE movies IN moviets
STORES literature COLUMN lobmovie;
CREATE INDEX mix ON movies...;
Which of the following indexes will be created?
A. Unique index
B. Non-unique index
C. Partitioning index
D. Unique where not null index
Answer: A

28. Which of the following statements is NOT true after the following statement is executed? This is an active procedure in the DB2 system.
ALTER PROCEDURE tst1.proc1 STAY RESIDENT YES
A. The SYSIBM.SYSROUTINES table is updated
B. The program will immediately start to stay resident
C. The stored procedure can stay active when this statement is executed
D. The user needs to be the owner of the stored procedure, or have ALTERIN privilege for the schema or all schemas, or have SYSADM or SYSCTRL authority
Answer: B

29. Which of the following procedures can be used to increase the size of the primary allocation in order to remove the extents in a user-defined table space?
A. Run REORG with the REUSE option
B. Alter PRIQTY, then REORG the table space
C. Run REORG with the UNLOAD PAUSE option, use Access Method Services to redefine the dataset, then run REORG CONTINUE
D. Run REORG with the UNLOAD PAUSE option, use Access Method Services to redefine the dataset, then restart REORG with RESTART(PHASE)
Answer: D

30. Which of the following is TRUE about a DB2 storage group?
A. It must be used with SMS
B. It must only contain one volume
C. To add/remove volumes, it must be dropped and recreated
D. A defined table space or index can specify PRIQTY
Answer: D


© 2014 Cheat-Test.com, All Rights Reserved