An application needs a table for each connection that tracks the ID and Name of all items
previously ordered and committed within the connection. The table also needs to be cleaned up
and automatically removed each time a connection is ended. Assuming the ITEMS table was
created with the following SQL statement:
CREATE TABLE items
item_no INT,
item_name CHAR(5),
item_qty INT)
Which of the following SQL statements will provide the table definition that meets the specified
requirements?
A.
DECLARE GLOBAL TEMPORARY TABLE tracker
AS (SELECT item_no, item_name FROM items) WITH NO DATA
ON COMMIT PRESERVE ROWS
ON DISCONNECT DROP TABLE
B.
DECLARE GLOBAL TEMPORARY TABLE tracker
AS (SELECT item_no, item_name FROM items) WITH NO DATA
ON COMMIT PRESERVE ROWS
C.
CREATE TABLEsystmp.tracker
AS (SELECT item_num, item_name FROM items) WITH NO DATA
ON COMMIT PRESERVE ROWS
D.
CREATE TABLE tracker
AS (SELECT item_num, item_name FROM items)
ON COMMIT PRESERVE ROWS
ON DISCONNECT DROP TABLE
The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current session.
ON COMMIT PRESERVE ROWS – is DB2 syntax.
“ON DISCONNECT DROP TABLE” no such syntax.
0
0
DECLARE GLOBAL TEMPORARY TABLE
The DECLARE GLOBAL TEMPORARY TABLE statement defines a declared temporary table for the current application process. The declared temporary table resides in the work file database and its description does not appear in the system catalog. It is not persistent and cannot be shared with other application processes. Each application process that defines a declared temporary table of the same name has its own unique description and instance of the temporary table. When the application process terminates, the temporary table is dropped.
0
0
ON COMMIT
Specifies what happens to the table for a commit operation. The default is ON COMMIT DELETE ROWS.
DELETE ROWS
Specifies that all of the rows of the table are deleted if there is no open cursor that is defined as WITH HOLD that references the table.
PRESERVE ROWS
Specifies that all of the rows of the table are preserved. Thread reuse capability is not available to any application process or thread that contains, at its most recent commit, an active declared temporary table that was defined with the ON COMMIT PRESERVE ROWS clause.
DROP TABLE
Specifies that the table is implicitly dropped at commit if there is no open cursor that is defined as WITH HOLD that references the table. If there is an open cursor defined as WITH HOLD on the table at commit, the rows are preserved.
0
0