TERADATA 16.20 DBSCONTROL COMPRESSION FIELDS

Share via:
TERADATA 16.20 DBSCONTROL COMPRESSION FIELDS
Dear Readers,
In this article you will get to know the TERADATA 16.20 DBSCONTROL COMPRESSION FIELDS

DBS Control Record – Compression (Block Level) Fields:

1.  BlockLevelCompression                                     : ON

2.  (Reserved for future use)

3.  CompressSpoolDBs                                            :  IFNOTCACHED (ALWAYS, NEVER, IFNOTCACHED)

4.  (Reserved for future use)

5.  CompressMloadWorkDBs                                  :  UNLESSQBNO  (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)

6.  CompressPJDBs                                                 :   ALWAYS      (ALWAYS, NEVER)

7.  MinDBSectsToCompress                                    :   32

8.  MinPercentCompReduction                               :   20%

9.  CompressionAlgorithm                                       :   ZLIB        (ZLIB, ELZS_H, ELZS_S)

10. CompressionLevel                                              :   1           (1-9; Default value = 1)

11. (Reserved for future use)

12. UncompressReservedSpace                             :   5%

13. (Reserved for future use)

14. CompressPermPrimaryDBs                               :     UNLESSQBNO  (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)

15. CompressPermFallbackDBs                              :     UNLESSQBNO  (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)

16. CompressPermPrimaryCLOBDBs                     :     UNLESSQBNO  (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)

17. CompressPermFallbackCLOBDBs                    :     UNLESSQBNO  (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)

18. CompressGlobalTempPrimaryDBs                   :     UNLESSQBNO  (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)

19. CompressGlobalTempFallbackDBs                  :     UNLESSQBNO  (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)

20.CompressGlobalTempPrimaryCLOBDBs         :     UNLESSQBNO  (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)

21. CompressGlobalTempFallbackCLOBDBs        :     UNLESSQBNO  (ALWAYS, UNLESSQBNO, NEVER, ONLYIFQBYES)

22  – 31  (Reserved for future use)

32..EnableTempBLC                                                :     FALSE       (TRUE, FALSE)

33..DefaultTableMode                                             :     MANUAL      (AUTOTEMP, MANUAL, ALWAYS, NEVER)

34.TempBLCThresh                                                 :     COLD        (COLD, WARM, HOT)

35.TempBLCSpread                                                :      5%          (1-25%)

36.TempBLCInterval                                                :     10 minute(s) (1-120)

37.TempBLCIOThresh                                             :      1           (1-1000)

38.TempBLCPriority                                                 :      MEDIUM/DEFAULT (LOW, MEDIUM/DEFAULT, HIGH, RUSH/TOP)

39.TempBLCRescanPeriod                                     :      7 day(s)     (1-90)

40.CompressionZLIBMethod                                  :      IPPZLIB      (ZLIB, IPPZLIB)

41. OverrideARCBLC                                                :      FALSE       (TRUE, FALSE)

 

Detailed information about these fields:

DBS Control Record – Compression (Block Level) Fields:

1. BlockLevelCompression – This field controls whether block level compression isenabled globally or not.  Valid values are ON, to enable compression, or OFF, to disable compression.If this field is ON, compression is enabled or disabled for Individual table types as specified by their corresponding tunables.If this field is OFF, it supersedes all table type specific settings.Changes made to this setting take effect after the GDO is written.

2.This field is reserved for future use.

3.CompressSpoolDBs – This field is used by the File System to determine how to handle Spool DBs with respect to compression. This setting and all other compression attributes specified for the base table of this table type also applies to sort and index maintenance work subtablesand compressible LOB (JSON, XML, Character LOB, etc.) subtables corresponding to this table type.There are three values that are used, ALWAYS, NEVER and IFNOTCACHED.The default value is NEVER.ALWAYS means that the data blocks of a new Spool table will be compressed.NEVER means that the data blocks of a new Spool table will not be compressed.IFNOTCACHED means that the data blocks of a new Spool table will be compressedif the DBs are not being cached.

4.This field is reserved for future use.

5.CompressMloadWorkDBs – This field is used by the File System to determine how to handle Multiload sort and index maintenance work subtables and compressible LOB (JSON, XML, Character LOB, etc.) subtable swith respect to compression.There are four values that are used,

ALWAYS,  UNLESSQBNO, NEVER and ONLYIFQBYES.

The default value is NEVER.

ALWAYS means that the data blocks of a new Mloadwork table will be compressed regardless of what Query Band options are specified.

ONLYIFQBYES means that the datablocks of a new subtable will NOT becompressed unless the Query Band option specifies to compress them.

UNLESSQBNO means that the data blocks of a new Mloadwork tablewill becompressed unless the Query Band option specifies not to compress them.

NEVER means that the data blocks of a new Mloadwork table will not becompressed regardless of what Query Band options are specified.

6. CompressPJDBs – This field is used by the File System to determine how to handle Permanent Journal DBs with respect to compression. This setting and all other compression attributes specified for the base table of this table type also applies to sort and index maintenance work subtables and compressible LOB (JSON, XML, Character LOB, etc.) subtables corresponding to this table type.There are two values that are used, ALWAYS and NEVER. 

The default is NEVER.

ALWAYS means that the data blocks of a new PJ table will be compressed.

NEVER    means that the data blocks of a new PJ table will not be compressed.

Changes made to this setting take effect after the GDO is written.

7. MinDBSectsToCompress – A parameter to be used by the compression. algorithm to determine if enough sectors exist in the Data Block to warrant compression.If not enough sectors exist in the Data Block,the Data Block will not be saved in the compressed format.The allowable range is 32 to 2047.Changes made to this setting take effect after the GDO is written – but only for newly compressed data, existing compressed data stays as is until modified.

8. MinPercentCompressReduction – A parameter to be used by the Compression algorithm to determine if compression of the Data Block has reduced the size a sufficient amount.  If the size reduction is not adequate, the Data Block will not be saved in the compressed format.The allowable range is 0 to 99 with a default of 20. Changes made to its setting take effect only after the GDO is written.

9. CompressionAlgorithm – A parameter indicating which algorithm to use forcompression/decompression.  ‘ZLIB’, ‘ELZS_H’ and’ELZS_S’ are the algorithms supported. The default is ‘ZLIB’.  If ELZS_H is selected and the board is absent, ELZS_S will automatically be used.  ELZS_S is not are commended selection unless the hardware is suspect.  Before choosing ELZS_H or ELZS_S, please ensure all nodes have the requisite support installed. If some node lacks support, unanticipated behavior can be expected.

o Data qualifying for compression will not be compressed.

o Data already compressed previously via ELZS algorithms (perhaps present from a restore from a system that had support) will cause the task accessing the data to be aborted when trying to decompress the data.

Changes made to this setting take effect after the GDO is written – but only for newly compressed data, existing compressed data stays as is until modified.

10.CompressionLevel – A parameter used by the selected compression algorithm. For ZLIB, valid values are from 1 thru 9. A value of 1 indicates preference for maximum compression speed.Each higher values sacrifices more speed for better compression ratio.

The highest value of 9 optimizes for minimal compression size.

The default value is 1.

Changes made to its setting take effect only after the GDO is written -but only for newly compressed data, existing compressed data stays as is until modified.

11. (Reserved for future use)

12. UncompressReservedSpace – Specifies a percentage value indicating the minimum amount of free space that should be left in a database when performing an un compress operation.  If there is not sufficient space the uncompress operation will be terminated and the table left in a partially compressed state (but still usable).Allowable range is 0 to 90(percent) with a default of 5.Changes made to this setting take effect after the GDO is written.

13. This field is reserved for future use.

14. CompressPermPrimaryDBs – This field is used by the File System to determine how to handle primary sub-table data blocks in Permanent storage of tables using MANUAL compression. This field does not affect tables that have an effective block compression attribute of ALWAYS, AUTOTEMP or NEVER. System data,dictionary data, table headers, non-compressible LOBs,Snapshot Logs, Restore Logs,and all primary row secondary indexes are always excluded from compression. This setting and all other compression attributes specified for the base table of this table type also apply to sort worktables and index maintenance worktables corresponding to this table type.

ALWAYS means that the data blocks of a new subtable will be compressed regardless of what Query Band options are specified.

ONLYIFQBYES means that the data blocks of a new subtable will NOT be compressed unless a Query Band option is provided that specifies to compress them.

UNLESSQBNO means that the datablocks of a new subtable will be compressed unless a Query Band option is provided that specifies NOT to compress them.

NEVER means that the datablocks of a new subtable will NOT be compressed regardless of any specified Query Band option.

The default value is ONLYIFQBYES.

15. CompressPermFallbackDBs – This field is used by the File System to determine how to handle fallback subtable data blocks in Permanent storage of tables using MANUAL compression. This field does not affect tables that have an effective block compression attribute of ALWAYS, AUTOTEMP or NEVER. System data,dictionary data, table headers, non-compressible LOBs, Snapshot Logs, Restore Logs,and all primary row secondary indexes are always excluded from compression. This setting and all other compression attributes specified for the base table of this table type also apply to sort worktables and index maintenance  worktables corresponding to this table type.

ALWAYS means that the data blocks of a new subtable will be compressed regardless of what Query Band options are specified.

ONLYIFQBYES means that the datablocks of a new subtable will NOT be compressed unless a Query Band option is provided that specifies to compress them.

UNLESSQBNO means that the datablocks of a new subtable will be compressed unless a Query Band option is provided that specifies NOT to compress them.

NEVER means that the datablocks of a new subtable will NOT be compressed regardless of any specified Query Band option.

The default value is ONLYIFQBYES.

16. CompressPermPrimaryCLOBDBs – This field is used by the File System to determine how to handle primary Compressible LOB (JSON, XML, Character LOB, etc.) subtable data blocks in Permanent storage of tables using MANUAL compression.  This field does not affect tables that have an effective block compression attribute of ALWAYS, AUTOTEMP or NEVER.  System data,dictionary data, table headers, non-compressible LOBs, Snapshot Logs, Restore Logs,and all primary row secondary indexes are always excluded from compression. This setting and all other compression attributes specified for the base table of this table type also apply to sort worktables and index maintenance worktables corresponding to this table type.

ALWAYS means that the data blocks of a new subtable will be compressed regardless of what Query Band options are specified.

ONLYIFQBYES means that the data blocks of a new subtable will NOT be compressed unless a Query Band option is provided that specifies to compress them.

UNLESSQBNO means that the datablocks of a new subtable will be compressed unless a Query Band option is provided that specifies NOT to compress them.

NEVER means that the datablocks of a new subtable will NOT be compressed regardless of any specified Query Band option.

The default value is NEVER.

17. CompressPermFallbackCLOBDBs – This field is used by the File System to determine how to handle fallback Compressible LOB (JSON, XML, Character LOB, etc.) subtable datablocks in Permanent storage of tables using MANUAL compression.  This field does not affect tables that have an effective block compression attribute of ALWAYS, AUTOTEMP or NEVER. System data,dictionary data, table headers, non-compressible LOBs,Snapshot Logs, Restore Logs,and all primary row secondary indexes are always excluded from compression. This setting and all other compression attributes specified for the base table of this table type also apply to sort worktables and index maintenance worktables corresponding to this table type.

ALWAYS means that the data blocks of a new subtable will be compressed regardless of what Query Band options are specified.

ONLYIFQBYES means that the data blocks of a new subtable will NOT be compressed unless a Query Band option is provided that specifies to compress them.

UNLESSQBNO means that the datablocks of a new subtable will be compressed unless a Query Band option is provided that specifies NOT to compress them.

NEVER means that the datablocks of a new subtable will NOT be compressed regardless of any specified Query Band option.

The default value is NEVER.

18. CompressGlobalTempPrimaryDBs – This field is used by the File System to determine how to handle primary subtable data blocks in Global Temporary storage with respect to compression. System data, dictionary data, table headers, non-compressible LOBs, Snapshot Logs, Restore Logs, and all primary row secondary indexes are always excluded from compression. This setting and all other compression attributes specified for the base table of this table type also apply to sort worktables and index maintenance worktables corresponding to this table type.

ALWAYS means that the data blocks of a new subtable will be compressed regardless of what Query Band options are specified.

ONLYIFQBYES means that the datablocks of a new subtable will NOT be compressed unless a Query Band option is provided that specifies to compress them.

UNLESSQBNO means that the datablocks of a new subtable will be compressed unless a Query Band option is provided that specifies NOT to compress them.

NEVER means that the datablocks of a new subtable will NOT be compressed regardless of any specified Query Band option.

The default value is NEVER.

19. CompressGlobalTempFallbackDBs – This field is used by the File System to determine how to handle fallback subtable data blocks in Global Temporary storage with respect to compression.  System data, dictionary data, table headers,non-compressible LOBs, Snapshot Logs, Restore Logs, and all primary row secondary indexes are always excluded from compression. This setting and all other compression attributes specified for the base table of this table type also apply to sort worktables and index maintenance worktables corresponding to this table type.

ALWAYS means that the data blocks of a new subtable will be compressed regardless of what Query Band options are specified.

ONLYIFQBYES means that the data blocks of a new subtable will NOT be compressed unless a Query Band option is provided that specifies to compress them.

UNLESSQBNO means that the data blocks of a new subtable will be compressed unless a Query Band option is provided that specifies NOT to compress them.

NEVER means that the data blocks of a new subtable will NOT be compressed regardless of any specified Query Band option.

The default value is NEVER.

20. CompressGlobalTempPrimaryCLOBDBs – This field is used by the File System To determine how to handle primary Compressible LOB (JSON, XML, Character LOB, etc.) subtable datablocks in  Global Temporary storage with respect to compression.  System data, dictionary data, table headers,non compressible LOBs, Snapshot Logs, Restore Logs, and all primary row secondary indexes are always excluded from compression. This setting and all other compression attributes specified for the base table of this table type also apply to sort worktables and index maintenance worktables corresponding to this table type.

ALWAYS means that the data blocks of a new subtable will be compressed regardless of what Query Band options are specified.

ONLYIFQBYES means that the data blocks of a new subtable will NOT be compressed unless a Query Band option is provided that specifies to compress them.

UNLESSQBNO means that the datablocks of a new subtable will be compressed unless a Query Band option is provided that specifies NOT to compress

them.

NEVER means that the datablocks of a new subtable will NOT be compressed regardless of any specified Query Band option.

The default value is NEVER.

21. CompressGlobalTempFallbackCLOBDBs – This field is used by the File System to determine how to handle fallback Compressible LOB (JSON, XML,CharacterLOB, etc.) subtable data blocks in  Global Temporary storage with respect to compression.  System data, dictionary data, table headers,non-compressible LOBs, Snapshot Logs, Restore Logs, and all primary row secondary indexes are always excluded from compression. This setting and all other compression attributes specified for the base table of this table type also apply to sort worktables and index maintenance worktables corresponding to this table type.

ALWAYS means that the data blocks of a new subtable will be compressed regardless of what Query Band options are specified.

ONLYIFQBYES means that the data blocks of a new subtable will NOT be compressed unless a Query Band option is provided that specifies to compress them.

UNLESSQBNO means that the data blocks of a new subtable will be compressed unless a Query Band option is provided that specifies NOT to compress them.

NEVER means that the data blocks of a new subtable will NOT be compressed regardless of any specified Query Band option.

The default value is NEVER.

22 – 31.  (Reserved for future use)

32. EnableTempBLC – Controls all parts of temperature based BLC This includes the AutoTempComp background task responsible for compressing and un compressing data based on temperature, and any decisions to automatically compress COLD data as it is loaded.If this value is enabled, it is highly recommended that the tunableDisableAutoCylPack be set to FALSE and AutoCylPackColddata be set to TRUE.  If set to FALSE, the rules for compression revert to the inheritance model used by tradional BLC(if enabled).EnableTempBLC might be set to FALSE during a performance critical window.

Valid Values are TRUE and FALSE. The default is FALSE. Changes are effective immediately.

33. DefaultTableMode – Indicates whether the default for permanent tables with no specific table level attribute is to manage their compression manually, automatically, always, or never compress the table. If MANUAL is chosen, it is the user’s responsibility to compress or decompress the table. If AUTOTEMP is chosen, then data in the table will be compressed or decompressed automatically by the AutoTempComp background task based on the temperature of the data.

In this mode, parts of the table(such as partitions) may have a different compression state based on their frequency of access.

If ALWAYS is chosen, then data in the table will be compressed regardless of what Query Band options are specified.

If NEVER is chosen, then data in the table will never be compressed.

Valid values are MANUAL, AUTOTEMP, ALWAYS and NEVER.  The default is MANUAL. Changes are effective immediately.

34. TempBLCThresh – Indicates the temperature threshold at which data in tables using AUTOTEMP compression management are compressed. Any data at or colder than the specified temperature will be compressed. Temperatures can be specified by keyword or a floating point value representing a percentage of the coldest allocated user data (PERM TABLES) that should be compressed. The percentage is calculated based on the number of cylinders of PERM data used, without regard to how fully occupied the cylinders are. Valid Values are COLD, WARM, HOT, or a floating point value greater than 0 and less than or equal to 100. The default is COLD. Changes are effective immediately.

Note: The percentage setting is currently not supported.The only allowable values are COLD, WARM, and HOT.

35. TempBLCSpread – A percentage that is added to or subtracted from TempBLCThresh to determine if a cylinder should change compression state. For example, if the TempBLCThresh is defined as COLD and the TempBLCSpread is 5% then the data must be 5% colder than COLD to be compressed and 5% warmer than COLD to be uncompressed. This value can be used to prevent data whose natural temperature is close to the threshold from being repeatedly compressed and uncompressed. Valid values are from 1 to 25%.  The default is 5%.  Changes are effective immediately.

36. TempBLCInterval – The amount of time for AutoTempComp to wait after compressing or un compressing data before checking if other data needs to be processed. If the whole MI is scanned and no data is found to process then AutoTempComp will sleep for a duration indicated by TempBLCRescanPeriod.

Valid values are from 1 to 120 minutes. The default is 10 minutes. Changes are effective the next time AutoTempComp wakes up.

37. TempBLCIOThresh – The maximum number of outstanding I/O’s on a node above which AutoTempComp will sleep for a short interval before running again.  This setting limits the performance impact of AutoTempComp on foreground work loads. If the system remains busy, AutoTempComp will sleep according to the TempBLCInterval setting. Valid values are from 1 to 1000. The default is 1. Changes are effective immediatelly.

38. TempBLCPriority – The priority at which AutoTempComp runs. Priorities can be specified using their old or new names.

Valid values are: LOW, MEDIUM (or DEFAULT), HIGH, or RUSH(or TOP).  The default is MEDIUM (or DEFUALT). Changes are effective the next time AutoTempComp wakes up.

39. TempBLCRescanPeriod – The length of time in days to allow before revalidating the state of cylinders using automatic compression to see if they are properly compressed or uncompressed. This rescan is necessary as the temperature of a cylinder can change,even if the cylinder is not directly modified.  At least one scan is performed after each startup.Valid values are from 1 to 90 days. The default is 7 days.Changes are effective the next time AutoTempComp wakes up.

40. CompressionZLIBMethod – This field specifies specific ZLIB implementation package to use. If the package is not installed or error is encountered,

ZLIB is used instead.

ZLIB – Standard ZLIB is used for compression/uncompression

IPPZLIB – Intel’s IPP ZLIB implementation (default)

41. OverrideARCBLC – Determines whether the target system’s default compression status for tables is used for restore of archived data instead of the compression status when archived. Whether TRUE or FALSE, this flag is overridden if:

(1) a compression Query Band is set for restore.

(2) a BLOCKCOMPRESSION table-level attribute of a table is ALWAYS or NEVER. In addition to the above, when set FALSE, this flag is also over riden if:

(3) a table has a BLOCKCOMPRESSION table-level attribute of DEFAULT or does not specify one and DBS Control Compression field Default TableMode on the target system is set to ALWAYS or NEVER.

(4) a table has a BLOCKCOMPRESSION table-level attribute of MANUAL or DEFAULT or does not specify one, Default TableMode on the target system is set to MANUAL and one or more DBSControl CompressPermxxx or CompressGlobalTempxxx fields are set to ALWAYS or NEVER. In this case, only compression for the

specified subtable types is overridden.

Therefore, unless overridden by a restore Query Band or ALWAYS or NEVER attributes:

TRUE  – Compression status specified for the target system for the type of table or subtable being restored is used.

FALSE – Compression status from the archive is retained regardless of settings on the target system.

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment