CALL VECTORWISE
Valid in: SQL, ESQL
The CALL VECTORWISE statement calls a Vector system command.
Note: This statement requires the SECURITY privilege or DB_ADMIN database permission.
This statement has the following format:
CALL VECTORWISE( COMMAND ['PARAMETERS'])
where:
COMMAND
Specifies the name of the system command. Commands include:
• CLEANUP_UNUSED_FILES
• CONDENSE_LOG
• CSVEXPORT
• FORCE_TRANSACTION_DISALLOW_COMMIT
• SETCONF
• TERMINATE
• TOTAL_DB_SIZE
• VWLOG_RELOAD
• VWLOG_ROTATE
'PARAMETERS'
Specifies one or more parameters specific to the called system.
If parameters is a null, empty, or blank string, the statement transfers the user to the operating system and the user can execute any operating system command. Exiting or logging out of the operating system returns the user to the application.
CLEANUP_UNUSED_FILES Command
The CLEANUP_UNUSED_FILES command scans all locations recursively to find files that do not belong to any tables. It renames such files by adding “unused_” prefix. This system command can be executed on a working system.
You should remove these unused files only after making sure that the system is running correctly.
This command has the following format:
CALL VECTORWISE(CLEANUP_UNUSED_FILES)
Note: CLEANUP_UNUSED_FILES is designed to be used in cases where files are not deleted due to a crash or bug. It does not mark unreleased files that may still be needed by a transaction. So using this system call may not help you to free space if there are long-running or hanging transactions in the system.
CONDENSE_LOG Command
The CONDENSE_LOG command condenses the main.wal file by dropping unused information. For more information, see WAL Condensation in the User Guide.
This command has the following format:
CALL VECTORWISE(CONDENSE_LOG)
CSVEXPORT Command
The CSVEXPORT command writes a table to a local file system. The result is either a single CSV file or a collection of CSV files, depending on whether the query is run in parallel. The number of files produced cannot be specified, but can be indirectly influenced by setting [engine] max_parallelism_level.
This command has the following format:
CALL VECTORWISE (CSVEXPORT 'table_name, file_name [, null_value]')
where
table_name
Specifies the name of the table to unload.
file_name
Specifies the output location, which can be either a local file system path or a HDFS location URL (only on HDFS installations). If multiple files are created they use the file name with suffixes in the form '.nnn' where nnn is the file number.
null_value
Specifies the text to use for the NULL valued attributes. The default is an empty string.
When a parameter contains commas or spaces, double quotation marks must be used.
Examples:
1. Export data in the table MY TABLE to a file named “my table”, and insert the text THIS IS NULL for null values:
CALL VECTORWISE (CSVEXPORT '"MY TABLE", "/tmp/my table", "THIS IS NULL"').
2. Export data in the supplier table to a file named supplier.csv in HDFS location, and insert the text NULL for null values:
CALL VECTORWISE(CSVEXPORT 'supplier, hdfs://your.domain.com:8020/tmp/supplier.csv, NULL')
FORCE_TRANSACTION_DISALLOW_COMMIT Command
The FORCE_TRANSACTION_DISALLOW_COMMIT command marks a transaction as not allowed to commit. By doing so, overlapping transactions that commit do not have to be preserved for conflict resolution and do not accumulate in committed_transaction memory. The transaction is otherwise unaffected; that is, if a query is running it will continue to run and use resources.
This command can be used to:
• Reduce committed transactions memory use
• Remove a session with uncommitted transactions when it cannot be removed otherwise (with IPM or through its application). In such situations, marking the transaction as not allowed to commit prevents committed transaction memory accumulating indefinitely, so that the server can continue to operate. Because such a situation is an error state, however, we advise that you schedule a server restart for a convenient time.
Caution! This command should be used in emergency situations only. You should first try to remove the session with the hanging transaction. Manually removing sessions, interrupting transactions, and preventing a transaction from committing changes are emergency solutions. It is preferable to avoid this situation by transaction control in the application and query time-outs.
This command has the following format:
CALL VECTORWISE (FORCE_TRANSACTION_DISALLOW_COMMIT '''x100_tx_id''')
where x100_tx_id is the transaction ID (obtained from the vwinfo ‑o output).
If the transaction was a write transaction and attempts to commit, it will receive error: “E_QE0087 Error committing a transaction.” The vectorwise.log will show:
UPDATE_MGR:Attempting to commit transaction TXID x100_tx_id that was forcibly disallowed to commit.
X100SERVER:handle_request: Error committing transaction, aborted!
If the transaction did not make any changes, it will commit without an error because it does not require conflict resolution.
SETCONF Command
The SETCONF command dynamically modifies the setting of the specified configuration parameter to the specified value. The setting remains in effect for the duration of the session.
This command has the following format:
CALL VECTORWISE( SETCONF 'parameter_section, parameter_name, ''parameter_value''[, GLOBAL]')
When the GLOBAL parameter is used, the setting remains in effect until server shutdown; when omitted, the setting remains in effect for the duration of the session.
For example, the following command enables profiling for all the queries that will be executed in the current session:
CALL VECTORWISE(SETCONF 'server, profiling, ''true''')
Note: For some parameters, the modified setting will not be effective. For example, dynamically modifying the value of [cbm] bufferpool_size parameter will not change the actual bufferpool size.
TERMINATE Command
The TERMINATE command stops Vector and rolls back transactions in progress.
This command has the following format:
CALL VECTORWISE(TERMINATE)
TOTAL_DB_SIZE Command
The TOTAL_DB_SIZE command displays the estimated database size in bytes in the vectorwise.log, based on decompressing a sample range of tuples for all user table columns in the database.
This command has the following format:
CALL VECTORWISE(TOTAL_DB_SIZE)
VWLOG_RELOAD Command
The VWLOG_RELOAD command loads a specific Vector error log configuration file (vwlog.conf). The rules for log output specified in the file are used for all subsequent log events. If the log-configuration file is not parsed successfully, the current log rules remain active.
VWLOG_RELOAD can be used to change the error logging rules without the need to restart Vector. In particular, it can be used to increase the log level verbosity while debugging a process, and to otherwise decrease the log level for a more concise log file.
This command has the following format:
CALL VECTORWISE( VWLOG_RELOAD ['''log_config_file''']);
where:
log_config_file
Specifies the name of a error log configuration file. It must be an absolute path and enclosed in triple quotes.
The file name (on keyword filename =) specified in the log_config_file must end with the string “vectorwise.log”. This restriction on the log configuration file is a security precaution that only applies when using the VWLOG_RELOAD command.
Windows: The backslashes in the path name must be escaped, as shown in the example.
Examples:
1. Read and parse a custom vwlog.conf file:
CALL VECTORWISE(VWLOG_RELOAD '''C:\\Documents and Settings\\John\\My Documents\\my_vwlog.conf''');
2. Read and parse the default error log configuration file:
CALL VECTORWISE(VWLOG_RELOAD);
VWLOG_ROTATE Command
The VWLOG_ROTATE command performs the following actions on the Vector error log file:
1. Closes the current log file.
2. Renames the current log file to a new file.
3. Reopens the old log file (which is now empty) and continues to append incoming log messages.
This command makes it easier to transfer, manage, and analyze the error log file. It can be used if the error log file gets too large. It can be used daily or weekly to better organize log files, such as compressing them, and then archiving them to another storage location.
This command has the following format:
CALL VECTORWISE( VWLOG_ROTATE ['''new_filename''']);
where:
new_filename
Specifies the name of the new error log file. The new_filename can be an absolute path and must be enclosed in triple quotes.
If new_filename does not specify an absolute path, then the error log file is moved to the installation’s files subdirectory (or II_LOG location on Windows). If new_filename is an existing file, VWLOG_ROTATE does not overwrite it and raises an error message. The new_filename must specify a file on the same partition as the existing error log file. It can be moved to another partition later.
Examples:
1. Move the error log file to new_file. Write subsequent log lines to a new (empty) file that has the same name as the old log file:
CALL VECTORWISE(VWLOG_ROTATE '''/home/John/new_file''');
2. Move the error log file to a file whose name is the concatenation of the current log file name and a timestamp string of the form YYYYMMDDTHHMMSS, in the same directory as the current log file. Write subsequent log lines to a new (empty) file that has the same name as the old log file:
CALL VECTORWISE(VWLOG_ROTATE);