topic Re: Settings for Index Schema? in Qlik Replicate

Settings for Index Schema?

micpage — Mon, 26 Aug 2024 11:04:10 GMT

Hello Qlik Community,

i have a problem during "Reload Table" - "CREATE TABLE" with the Index Creation on Db2 LUW. The Schema is missing on the CREATE INDEX Command and so is will be use the Username for Index Schema, but this is not the right.

CREATE UNIQUE INDEX "TA_DH_CDWH_TATXT08K_TA_DH_CDWH_TATXT08K_PK" ON "POC_CDC_QLIK"."TA_DH_CDWH_TATXT08K" ( "TXT08K_ID" )

is mapped by Db2 to Usersname

CREATE UNIQUE INDEX "SVCDB2CDCB"."TA_DH_CDWH_TATXT08K_TA_DH_CDWH_TATXT08K_PK" ON "POC_CDC_QLIK"."TA_DH_CDWH_TATXT08K" ( "TXT08K_ID" )

but it schould the same like the Table:

CREATE UNIQUE INDEX "POC_CDC_QLIK"."TA_DH_CDWH_TATXT08K_TA_DH_CDWH_TATXT08K_PK" ON "POC_CDC_QLIK"."TA_DH_CDWH_TATXT08K" ( "TXT08K_ID" )

I want to create the Index in the same Schema from the Table. Are there are hidden "More Options" to set the Index Schema?

i tested some options, but the do not work:

I have set the Schema with "Task Settings" - "Metadata" - "Target Metadata" - "Target table schema".

I also defined a "Global Rule" to rename schema.

i also set it on the "Table Settings" - "General" - "Map to target table" - "Table Schema"

Here is the Errormessage:

00001012: 2024-08-26T12:43:28 [TASK_MANAGER ]I: Start loading table 'TS'.'TATXT08K' (Id = 12) by subtask 1. Start load timestamp 00062093CB8471C0 (replicationtask_util.c:761)

00004928: 2024-08-26T12:43:30 [TARGET_LOAD ]E: Failed (retcode -1) to execute statement: CREATE UNIQUE INDEX "TA_DH_CDWH_TATXT08K_TA_DH_CDWH_TATXT08K_PK" ON "POC_CDC_QLIK"."TA_DH_CDWH_TATXT08K" ( "TXT08K_ID" ) [1022502] (ar_odbc_stmt.c:5082)

00004928: 2024-08-26T12:43:30 [TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: 42502 NativeError: -552 Message: [IBM][CLI Driver][DB2/LINUXX8664] SQL0552N "SVCDB2CDCB" verfügt nicht über die Berechtigung, die Operation "IMPLICIT CREATE SCHEMA" auszuführen. SQLSTATE=42502 [1022502] (ar_odbc_stmt.c:5090)

00004928: 2024-08-26T12:43:30 [TARGET_LOAD ]E: execute create primary key failed, statement CREATE UNIQUE INDEX "TA_DH_CDWH_TATXT08K_TA_DH_CDWH_TATXT08K_PK" ON "POC_CDC_QLIK"."TA_DH_CDWH_TATXT08K" ( "TXT08K_ID" ) [1022502] (odbc_endpoint_imp.c:6814)

00004928: 2024-08-26T12:43:30 [TARGET_LOAD ]E: Handling new table 'POC_CDC_QLIK'.'TA_DH_CDWH_TATXT08K' failed [1022502] (endpointshell.c:3001)

Best Regards

Michael

Re: Settings for Index Schema?

Heinvandenheuvel — Thu, 29 Aug 2024 21:28:54 GMT

On the one hand, this feels like a bug. On the other hand, it feels like too big a bug - that is this would never work for non-default target schemas. What version are you using? What was the target DB type chosen? Can you share the 'databases' part of the task export?

The naming suggest this is 'just' a POC and indeed the attempted configuration is not supported. Is someone (Qlik) guiding the POC? Let them figure it out? Get paid for assistance if need be... it's worth it! Is DB2-LUW even supported as target?

Is just using a default schema perhaps a workaround worth testing? Make the task use "POC_CDC_QLIK" as username instead of "SVCDB2CDCB"

You can potentially use REPCTL to 'see' (and replace) the syntax used. For example:

C:\scripts>repctl -d \Replicate\data getprovidersyntax syntax_name=db2zos command getprovidersyntax response: { "provider_syntax": { "name": "db2zos", "query_syntax": { "create_schema": "", "create_index": "CREATE UNIQUE INDEX ${QO}${TABLE_OWNER}_${CONSTRAINT_NAME}${QC} ON ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ( ${COLUMN_LIST} )", "create_primary_key": "ALTER TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ADD PRIMARY KEY ( ${COLUMN_LIST} )", "drop_column": "ALTER TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} DROP COLUMN ${QO}${COLUMN_NAME}${QC} RESTRICT",

This however does no look exactly like: CREATE UNIQUE INDEX "TA_DH_CDWH_TATXT08K_TA_DH_CDWH_TATXT08K_PK" ON...

That's an awfully long constraint name. It looks more like that was generated with:

"CREATE UNIQUE INDEX ${QO}${TABLE_NAME}_${CONSTRAINT_NAME}${QC} ON...

This does not match the syntax export I showed above:

"CREATE UNIQUE INDEX ${QO}${TABLE_OWNER}_${CONSTRAINT_NAME}${QC} ON...

And you seem to suggest you need that underscore replace by close-quote, period, open-quote.

"CREATE UNIQUE INDEX ${QO}${TABLE_OWNER}${QC}.${QO}${CONSTRAINT_NAME}${QC} ON...

You'll probably need to create a support case. Be sure to provide all I suggested in the opening.

Hein

Re: Settings for Index Schema?

DesmondWOO — Fri, 30 Aug 2024 10:00:07 GMT

Hi @micpage ,

In your DB2LUW target endpoint, go to "Advanced" setting and click "Internal Parameters". Then please add following information:

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} )

Example:

Hope this helps.

Regards,
Desmond

Re: Settings for Index Schema?

micpage — Mon, 02 Sep 2024 15:31:09 GMT

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.