Showing posts with label Fast Unload. Show all posts
Showing posts with label Fast Unload. Show all posts
Thursday, November 20, 2008
Wednesday, October 22, 2008
BMC UNLOAD PLUS Tips
BMC Unload Plus is a high-performance DB2 UNLOAD utility (just like CA/Platinum FAST UNLOAD utility). The performance of this utility can be controlled by proper use of the keyword "DIRECT".
DIRECT YES: UNLOAD PLUS reads data directly from the table space data set or image copy data sets to unload the data, using a SELECT-like syntaxfor data selection. SELECT functionality is a limited subset of thefunctionality that is normally available in DB2 SQL. The benefit of the DIRECT YES mode is maximum performance when unloading large volumes of data.
DIRECT NO: UNLOAD PLUS processes the SELECT statement and reads the data usingDB2 dynamic SQL. This mode is not a high-performance solution for unloading large volumes of data. The benefit of the DIRECT NO mode is a full range of DB2 SQL SELECTfunctionality, including joined tables, subqueries, and so on. This functionality includes many of the features that are availablein UNLOAD PLUS including DB2 parallelism, data type conversions, output formatting.
DIRECT YES: UNLOAD PLUS reads data directly from the table space data set or image copy data sets to unload the data, using a SELECT-like syntaxfor data selection. SELECT functionality is a limited subset of thefunctionality that is normally available in DB2 SQL. The benefit of the DIRECT YES mode is maximum performance when unloading large volumes of data.
DIRECT NO: UNLOAD PLUS processes the SELECT statement and reads the data usingDB2 dynamic SQL. This mode is not a high-performance solution for unloading large volumes of data. The benefit of the DIRECT NO mode is a full range of DB2 SQL SELECTfunctionality, including joined tables, subqueries, and so on. This functionality includes many of the features that are availablein UNLOAD PLUS including DB2 parallelism, data type conversions, output formatting.
Tuesday, September 30, 2008
CA/Platinum Fast Unload tips
Today I'm going to discuss about a vendor product - CA(previously Platinum)'s DB2 "Fast Unload". It is a high performance DB2 data unload utility. If you use Fast Unload at your shop, go ahead and read these tips:
Fast Unload is much faster than DB2's DSNTIAUL. One of the reasons is, it does not use DB2 SQL to fetch the data. Instead it fetches the data directly from the underlying VSAM dataset. But this does not happen always. Sometimes it may have to fetch the data from the table. This is controlled by the keyword "SQL-ACCESS".
SQL-ACCESS NONE:
When you specify "NONE" for SQL-ACCESS, it does not use SQL access. If the select statement
cannot be processed using VSAM or EXCP, an error message is issued & processing ends. This is the DEFAULT.
Note: You must specify NONE to unload data from Image Copies, Concurrent Copies & DSN1COPYs.
SQL-ACCESS ONLY:
When you specify this option, it uses only SQL access to unload the tablespaces. This option lets you use any valid SELECT statement. Only one SELECT statement is processed at a time. The statements using VSAM or EXCP are processed first, followed by the SQL ACCESS SELECT statements, which are processed in the order specified in the control statements. You cannot include an ORDER-CLUSTERED clause, or the PART or OBID parameters with this option. You must specify SQL-ACCESS ONLY to use TRIM with ASCII objects. With SQL-ACCESS ONLY, DB2 determines locking. To preserve data integrity, you must specify LOCKSIZE TABLE on the tablespace.
SQL-ACCESS EXTENSION:
When you specify "EXTENSION" for SQL-ACCESS, it uses EXCP or VSAM processing if possible; otherwise uses SQL access. If you specify SQL-ACCESS EXTENSION, only those SELECT statements that require SQL access use it; all other SELECT statements are multitasked & unloaded using VSAM or EXCP. If you specify SQL-ACCESS EXTENSION with the PART or OBID parameter & SQL-ACCESS is required, an error message is issued and processing terminates.
Fast Unload is much faster than DB2's DSNTIAUL. One of the reasons is, it does not use DB2 SQL to fetch the data. Instead it fetches the data directly from the underlying VSAM dataset. But this does not happen always. Sometimes it may have to fetch the data from the table. This is controlled by the keyword "SQL-ACCESS".
SQL-ACCESS NONE:
When you specify "NONE" for SQL-ACCESS, it does not use SQL access. If the select statement
cannot be processed using VSAM or EXCP, an error message is issued & processing ends. This is the DEFAULT.
Note: You must specify NONE to unload data from Image Copies, Concurrent Copies & DSN1COPYs.
SQL-ACCESS ONLY:
When you specify this option, it uses only SQL access to unload the tablespaces. This option lets you use any valid SELECT statement. Only one SELECT statement is processed at a time. The statements using VSAM or EXCP are processed first, followed by the SQL ACCESS SELECT statements, which are processed in the order specified in the control statements. You cannot include an ORDER-CLUSTERED clause, or the PART or OBID parameters with this option. You must specify SQL-ACCESS ONLY to use TRIM with ASCII objects. With SQL-ACCESS ONLY, DB2 determines locking. To preserve data integrity, you must specify LOCKSIZE TABLE on the tablespace.
SQL-ACCESS EXTENSION:
When you specify "EXTENSION" for SQL-ACCESS, it uses EXCP or VSAM processing if possible; otherwise uses SQL access. If you specify SQL-ACCESS EXTENSION, only those SELECT statements that require SQL access use it; all other SELECT statements are multitasked & unloaded using VSAM or EXCP. If you specify SQL-ACCESS EXTENSION with the PART or OBID parameter & SQL-ACCESS is required, an error message is issued and processing terminates.
Subscribe to:
Posts (Atom)