Business Justification
The main reason we adopted the Qlik replicate solution is to replace custom Mainframe data pipelines with a sound replication solution. Qlik delimiter limitation and not having an official support around using control characters as delimiter is limiting the adoption.
Why use Control characters like “0x1e” as column delimiter?
- Over the years of managing mainframe data we found that the delimiter “0x1e” is a sound delimiter that does not show up within character dataset and does not cause load jobs to fail on distributed side.
What is the request?
- Phase1: Asking Qlik to officially support use of control characters using method mentioned below.
- Phase2: Enhance the Qlik product to use decimal number for the control character with replicate UI.
Specifying Control characters as column delimiter to csv load files.
- Currently, there are two steps to specify a delimiter when loading into Netezza. 1) set the delimiter at $info.query_syntax.csv_delimiter internal parameter 2) specify the delimiter in provider syntax file.
- For 1) above, replicate do not allow to specify “control” characters like 0x1E via GUI. We found a crude way to do it, but Qlik SME/CF/CSE team confirmed this is unsupported and should go via ideation. Below details on the workaround (but unsupported) we are currently using
- Add internal parameter $info.query_syntax.csv_delimiter in Netezza target endpoint and set the value to desired delimiter created using Notepad++
- Control character creation using Notepad++
- Open a new notepad++ document, hold “Alt” key and enter “030” using Numeric keypad and release “Alt” key to see control character in the document.
- Copy control character from notepad++ document and past into $info.query_syntax.csv_delimter value.
- Update Provider syntax file override for Netezza load statement.
"load_data_exe_params": "-quotedValue DOUBLE -host ${SERVER} -db ${DATABASE} -nullvalue attN -u ${USER_NAME} -pw ${PASSWORD} -delim 030 -t \\\"${TABLE_NAME}\\\" -df \"${FILENAME}\" -escapeChar \\ -outputDir \"${OUTPUT_DIR}\" ",
