Hello @Heinvandenheuvel and @DesmondWOO , thanks for your Help! With this internal Parameter:
parameter: $info.query_syntax.create_index
value: CREATE UNIQUE INDEX ${QO}${TABLE_OWNER}${QC}.${QO}${CONSTRAINT_NAME}${QC} ON ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ( ${COLUMN_LIST} )
my Task is working.
I also searched with the Command my Syntax for Db2LUW:
command getprovidersyntax response:
{
"provider_syntax": {
"name": "DB2",
"query_syntax": {
"truncate_table": "ALTER TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} activate not logged initially with empty table",
"modify_column": "ALTER TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ALTER COLUMN ${QO}${COLUMN_NAME}${QC} SET DATA TYPE ${COLUMN_TYPE}",
"rename_table": "RENAME TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} TO ${QO}${NEW_TABLE_NAME}${QC}",
"reorg": "CALL ADMIN_CMD('REORG TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC}')",
"error_code_constraint_violation": "-803",
"error_code_data_failure": "-461,-121,-206",
"drop_index": "DROP INDEX ${QO}${CONSTRAINT_NAME}${QC}",
"timestamp_to_date": "DATE(${COLUMN_NAME})",
"string_to_timestamp": "TIMESTAMP('${COLUMN_NAME}')",
"row_number_function": "ROW_NUMBER() OVER()",
"mod_function": "MOD(${COLUMN_NAME1}, ${COLUMN_NAME2})",
"pseudo_source": "SYSIBM.SYSDUMMY1"
},
"data_type_mapping": [{
"rep_type": "kAR_DATA_TYPE_BLOB",
"provider_data_type": "BLOB(2G) NOT LOGGED",
"casting_data_type": "BLOB(2G)"
}, {
"rep_type": "kAR_DATA_TYPE_CLOB",
"provider_data_type": "CLOB(2G) NOT LOGGED",
"casting_data_type": "CLOB(2G)"
}],
"odbc_col_attr": [{
"sql_type": -99,
"bind_db_type": -8,
"rep_type": "kAR_DATA_TYPE_CLOB",
"buffer_length": "${LOB_SIZE}*2",
"bind_len": "${LOB_SIZE}*2+2",
"is_lob": true,
"attributes": 4114
}, {
"sql_type": -98,
"bind_db_type": -2,
"rep_type": "kAR_DATA_TYPE_BLOB",
"buffer_length": "${LOB_SIZE}+2",
"bind_len": "${LOB_SIZE}+2",
"is_lob": true,
"attributes": 4096
}]
}
}
[getprovidersyntax command] Succeeded
And here is the "create_index" missing. so i seached for other Syntaxes and only found the GenericWithoutSchema:
command getprovidersyntax response:
{
"provider_syntax": {
"name": "GenericWithoutSchema",
"query_syntax": {
"create_schema": "",
"create_table": "CREATE TABLE ${QO}${TABLE_NAME}${QC} ( ${COLUMN_LIST} )",
"create_index": "CREATE UNIQUE INDEX ${QO}${CONSTRAINT_NAME}${QC} ON ${QO}${TABLE_NAME}${QC} ( ${COLUMN_LIST} )",
"create_primary_key": "ALTER TABLE ${QO}${TABLE_NAME}${QC} ADD CONSTRAINT ${QO}${CONSTRAINT_NAME}${QC} PRIMARY KEY ( ${COLUMN_LIST} )",
"drop_table": "DROP TABLE ${QO}${TABLE_NAME}${QC}",
"truncate_table": "TRUNCATE TABLE ${QO}${TABLE_NAME}${QC}",
"add_column": "ALTER TABLE ${QO}${TABLE_NAME}${QC} ADD COLUMN ${QO}${COLUMN_NAME}${QC} ${COLUMN_TYPE}",
"drop_column": "ALTER TABLE ${QO}${TABLE_NAME}${QC} DROP COLUMN ${QO}${COLUMN_NAME}${QC}",
"rename_column": "ALTER TABLE ${QO}${TABLE_NAME}${QC} RENAME COLUMN ${QO}${COLUMN_NAME}${QC} TO ${QO}${NEW_COLUMN_NAME}${QC}",
"modify_column": "ALTER TABLE ${QO}${TABLE_NAME}${QC} ALTER COLUMN ${QO}${COLUMN_NAME}${QC} ${COLUMN_TYPE}",
"rename_table": "ALTER TABLE ${QO}${TABLE_NAME}${QC} RENAME TO ${QO}${NEW_TABLE_NAME}${QC}",
"use_owner_name": false
}
}
}
[getprovidersyntax command] Succeeded
So i think there is a combination with the 2 syntaxes, but the Table got the Schemaname from the Tasksettings.
Here is also the requested Endpointdefinition with the Qlikversion:
"databases": [{
"name": "DB2ZOS-S-DBTS",
"description": "HOST DBT24",
"role": "SOURCE",
"is_licensed": true,
"type_id": "DB2ZOS_NATIVE_COMPONENT_TYPE",
"db_settings": {
"$type": "Db2zosSettings",
"additionalConnectionProperties": "enableACR=\"true\";enableWLB=\"false\"",
"username": "TUDBTCDC",
"password": "xxxxx",
"databaseAlias": "DBT24SSL",
"provider": "IBM DB2 ODBC DRIVER - DB2COPY1",
"SetDataCaptureChanges": false,
"ifi306SpName": "QLIKCDC.QLIKUDFT"
},
"override_properties": {
}
}, {
"name": "DB2LUW-T-DTSSCHAD",
"description": "DB DTSSCHAD",
"role": "TARGET",
"is_licensed": true,
"type_id": "ODBC_COMPONENT_TYPE",
"db_settings": {
"$type": "OdbcSettings",
"additionalConnectionProperties": "Driver={IBM DB2 ODBC DRIVER - DB2COPY1};DBALIAS=STSSCHAD;",
"syntax": "DB2",
"username": "svcdb2cdcb",
"password": "xxx"
},
"override_properties": {
}
}]
},
"_version": {
"version": "2024.5.0.247",
"version_major": 2024,
"version_minor": 5,
"version_revision": 247,
"fips": 0
I will open a case to extend the default DB2 Syntaxprovider.
PS: Sorry for the delayed answer, i was on Holiday.
