topic Re: SAP SQL Connector generating Millions of entries in SAP table /QTQVC/JOB_SQL every day in Connectivity & Data Prep

SAP SQL Connector generating Millions of entries in SAP table /QTQVC/JOB_SQL every day

MichaelW — Fri, 10 Jun 2022 14:08:52 GMT

Hi,

we are using the SAP SQL Connector to send some very long SELECT statements (each 24kB long) into the SAP system several times per day. This fulfills our purpose and is reasonably fast.

We now found out the hard way that Qlik stores every SELECT statement into the table /QTQVC/JOB_SQL for reasons that are not apparent to me. Since our statements are very long, they produce 100s of table entries per statement, adding up to roughly 2 Million entries per day.

Is there a way to stop the Qlik Connector from writing into this table? It is fast becoming an issue of storage space. We are aware that there is a Qlik transaction to delete records from past years but we are already having issues with records from this year. It is not recommended to delete those while a Connector job is running and it is quite hard to find a windows of time where this is definitely not the case.

Any help is very appreciated!

Michael

Re: SAP SQL Connector generating Millions of entries in SAP table /QTQVC/JOB_SQL every day

Hakan_Ronningberg — Mon, 13 Jun 2022 06:02:29 GMT

Hi Michael,

Unfortunately the SQL connector needs to use the table /QTQVC/JOB_SQL.

It is possible to schedule a deletion job directly in SAP (transaction SM36) by using the Report /QTQVC/DELETE_TABLES. This task requires SAP Basis knowledge. Maybe you can schedule it at night time?

https://help.qlik.com/en-US/connectors/Subsystems/SAP_Connectors_Help/Content/Connectors_SAP/Important-general-information.htm

Regards,
Håkan

Re: SAP SQL Connector generating Millions of entries in SAP table /QTQVC/JOB_SQL every day

MichaelW — Tue, 14 Jun 2022 12:53:57 GMT

Hi Hakan,

thank you very much for your answer. Unfortunately, Qlik Jobs run around the clock in this case, so it is quite hard to fit in a regular deletion job.

As apparently there is no way to avoid writing into the table we will most likely move away from Qlik SAP Connector and pursue a different way of fetching data from SAP.

Thanks again and best regards,

Michael

Re: SAP SQL Connector generating Millions of entries in SAP table /QTQVC/JOB_SQL every day

Hakan_Ronningberg — Wed, 15 Jun 2022 04:40:44 GMT

Hi Michael,

We do not want you to move away from the Qlik SAP Connector. Would it be an option for you if Qlik can create a variant of the deletion program /QTQVC/DELETE_TABLES that do not delete records from the latest week? In that case the program can be executed anytime without affecting ongoing connector jobs. I assume your jobs does not run for more than a week. I can investigate if such a program can be developed.

Regards,
Håkan

Re: SAP SQL Connector generating Millions of entries in SAP table /QTQVC/JOB_SQL every day

MichaelW — Fri, 17 Jun 2022 07:41:00 GMT

Hi Hakan,

that would indeed solve the problem and I would be very thankful if you could investigate this possibility!

Best regards,

Michael

Re: SAP SQL Connector generating Millions of entries in SAP table /QTQVC/JOB_SQL every day

Hakan_Ronningberg — Fri, 17 Jun 2022 08:39:44 GMT

Hi Michael,

We have decided to create such a program. It will be part of the next SAP connector release (release date not decided yet). If you need it earlier we can arrange a patch for you via the Qlik Support. Hopefully it can be developed and tested before the summer vacations!

Regards,
Håkan

Re: SAP SQL Connector generating Millions of entries in SAP table /QTQVC/JOB_SQL every day

MichaelW — Tue, 21 Jun 2022 15:21:43 GMT

Dear Hakan,

thank you very much for your effort! It is great to hear that this functionality is being developed. I think we will probably wait for the official connector release.

Best regards,

Michael

Re: SAP SQL Connector generating Millions of entries in SAP table /QTQVC/JOB_SQL every day

PabloLabbeImaps — Thu, 17 Nov 2022 17:27:17 GMT

Hi Hakan,

We have a customer trying to schedule a job to run the program /QTQVC/DELETE_TABLES weekly but SAP says it´s not a valid ABAP program. With transaction he needs to use to schedule the execution of the deletion ?

Re: SAP SQL Connector generating Millions of entries in SAP table /QTQVC/JOB_SQL every day

Hakan_Ronningberg — Fri, 18 Nov 2022 07:08:09 GMT

Hi,

That is strange. /QTQVC/DELETE_TABLES is a valid ABAP program. Use transaction /N/QTQVC/DELETE_BGD to execute a deletion job with the program.

In the latest SAP connector release 8.0.2, there now also is another variant of the program. If there is a need to perform the deletion at the same time as connector jobs are running, use the transaction /N/QTQVC/DELETE_SAFE (program /QTQVC/DELETE_TABLES_SAFE). The program can be executed anytime without affecting ongoing connector jobs since it does not delete records from the latest week.

Regards,
Håkan

Re: SAP SQL Connector generating Millions of entries in SAP table /QTQVC/JOB_SQL every day

PabloLabbeImaps — Fri, 18 Nov 2022 16:42:16 GMT

Sorry, my mistake.

Customer is using a very old version of sap connector (5.4) so that is the reason we didn´t found the program .

Reviewing the documentation for that version, the transaction for delete log files is /N/QTQVC/DELETE. Do the abap program is /QTQVC/DELETE ?

Re: SAP SQL Connector generating Millions of entries in SAP table /QTQVC/JOB_SQL every day

Hakan_Ronningberg — Mon, 21 Nov 2022 19:05:37 GMT

Hi,

Yes you can use transaction /N/QTQVC/DELETE. However, it is not suitable for deleting big amounts of records. The reason is that there is a timeout after a while in SAP for jobs executed in a dialog process.
You can read more about the transaction here:

https://help.qlik.com/en-US/connectors/Subsystems/SAP_Connectors_Help/Content/Connectors_SAP/Important-general-information.htm

Regards,
Håkan