Statement Packing
To provide optimal performance, the Stream operator packs individual DML statements into a larger multistatement request based on the rate specified by the Pack attribute. This type of processing requires less overhead than multiple individual requests.
The Stream operator submits these multistatement requests using macros which it creates to hold the requests. The macros are then executed instead of running each individual DML statement.
The macros are automatically removed after the job is complete. The use of macros in place of lengthy requests helps to minimize both network and parsing overhead.
Specifying the Pack Rate
The Pack attribute specifies the number of statements in a multistatement request. Specifying a Pack rate improves network/mainframe efficiency by reducing the number of sends and receives between Teradata PT and the Teradata Database. A maximum of 2400 statements can be specified.
Trial and error might be required to determine the best Pack rate for a Stream job. As the Pack rate is increased, the throughput improvement is usually great at first, then falls off. In other words, going from a Pack rate of 1 to 2 could provide huge performance gains, and going from 2 to 4 could be just as beneficial, but moving from 8 to 16 might cause a performance drop.
If the PackMaximum attribute is set to 'Yes', the Stream operator determines the maximum pack for the job, and then reports it.
Two factors to consider are:
These two factors might not be equal. The maximum rate lets you know the upper limit, but performance might improve at a smaller rate. For this reason, it is recommended that PACKMAXIMUM not be used for production jobs until you determine the optimal Pack factor.
Tuning the Pack Factor
Packing multiple statement requests improves network/mainframe efficiency by reducing the number of sends and receives between the application and the Teradata Database.
To determine the ideal pack factor to specify in the Pack attribute, first use the PackMaximum attribute by setting it to 'Yes'. Setting this attribute to Yes on the first job run sets up iterative interactions with the Teradata Database to heuristically determine the maximum possible pack factor. At the end of the run, this value is displayed in the Stream operator’s logged output. Specify that determined value in the Pack attribute on subsequent runs. Set the PackMaximum to 'No'.
Alternatively, the Stream Driver TD_Evt_PackFactor event returns the current pack factor when queried. This value is available after a connection is initiated and before it is terminated.