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

[MichaelW]

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

[Hakan_Ronningberg]

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/Importa...

Regards,
Håkan

[MichaelW]

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

[Hakan_Ronningberg · Accepted solution]

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

[MichaelW]

Hi Hakan,

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

Best regards,

Michael

[Hakan_Ronningberg · Accepted solution]

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

[MichaelW]

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

[PabloLabbeImaps]

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 ?

[Hakan_Ronningberg]

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.

You can read more in the online help: 
https://help.qlik.com/en-US/connectors/Subsystems/SAP_Connectors_Help/Content/Connectors_SAP/Importa...

Regards,
Håkan

[PabloLabbeImaps]

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 ?