| A1: | The load utility supports CURSOR as the input. The cursor must be already declared before the load utility is invoked. The entire result of the query associated with the specified cursor will be processed by the load utility. |
| A2: | When the load utility is invoked in INSERT mode, one needs INSERT privileges on the target table. If REPLACE mode is used, INSERT and DELETE privileges on the target table are also required. |
| A3: | Rows that do not comply with the table definition will not be loaded and placed in the dump file. Therefore stockdump.dmp contains: 20, "BBB", - 40, "EEE", x
|
| A4: | Rows that violated the unique constraint will be deleted and inserted into the exception table. Therefore stockexp has the following row: 30, "DDD", 4
|
| A5: | The table will be in CHECK PENDING state because only unique constraints are validated during the load operation. If a constraint is defined in the table like the CHECK constraint in the example, the utility will place the table in CHECK PENDING statement. You need to issue the SET INTEGRITY command to validate the data before the table is available for further processing. |
| A6: | The table will be accessible after the import command is successfully executed. No other command is required because data is already validated during the import operation. |
| A7: | The command will generate the DDL for all objects in the database department, the UPDATE statements to replicate the statistics on all tables and indexes in the database, the GRANT authorization statements, the UPDATE statements for the Database Manager Configuration and database configuration parameters, and the db2set statements for the registry variables. The output will be stored in the file db2look.sql. |
| A8: | The step that Bob missed is to REBIND the packages. Packages for static and SQL stored procedures are created at compile time. When the packages are bound to the database, data access plans are determined. Since the large amount of data is inserted into the database and database statistics have been updated, data access plans for these packages are still based on the outdated statistics. A REBIND of all packages that already exist will ensure that the latest statistics are used, hence more optimal data access paths. |
| A9: | He can either increase the size of the log files (or the number of logs) sufficiently large enough to hold all the changes made during the import. Alternatively, he could include the commitcount option: import from largeinputfile.ixf of ixf commitcount 1000 messages import.out create into newtable in datats index in indexts
|
| A10: | The option insert_update means that the utility will add imported data to the target table or update existing rows with matching primary keys. Therefore, the table must already exist with primary keys. Otherwise, the import will fail. |
| A11: | C and E. The load and import tools can read ASCII data from an input file and then insert them in a table in your database. |
| A12: | E. Only the import tool can insert records from an input file into a view. The load tool can only add data to a table, not a view. |
| A13: | D. DB2 does not support the XLS format. For column delimited data, use the WSF format. |
| A14: | D. The default column delimiter is a comma, so you need to modify the column delimiter as in answer D to import this data successfully. |
| A15: | C. While you can export from a view and import into a view, you cannot load into a view. You must load into the base table(s) directly. |
| A16: | B. db2relocatedb can be used to rename a database. |
| A17: | C. The load utility can capture statistics on all data added to the table during a load operation. |
| A18: | B and D. By default, the load utility locks the target table for exclusive access until the load completes. If the allow read access option is specified, it allows read access to data already existing in the table before the load was run. However, you cannot see the newly loaded data until the load completes. |
| A19: | B. The import utility can create the table if it does not exist and if the input file is in the IXF format. |
| A20: | B. PCTFREE = 1 means PCTFREE value for a table page is 0. With this setting, the import utility will not leave free space on the data pages. However, the load utility can do so by specifying the options INDEXFREESPACE, PAGEFREESPACE, and TOTALFREESPACE. The REORG utility will leave free space on the data and/or index pages based on the value of PCTFREE of the tables and indexes. This is especially important for tables with a clustering index. |
