Hi,
do you know if there is a sintax to clear all tables all in once, something like:
INFOR.CLEAR.TABLES ('all tables', true)
Thanks in advance.
Alberto
Hi Alberto,
I'd recommend submitting an enhancement request via https://ers.infor.com/ers to help improve visibility for the request for other clients out there. We've recently heard additional folks directly asking for such a feature to help improve usability at scale but additional votes help bubble feature requests higher on our roadmaps
No, there isn't such option. The function parameter can be one table name.
A simply Python script
or using this SQL you can build statements to execute.
/* script the build for a CLEAR TABLES. * Note: 1) Compass can only run 90 queries at a time so these have to be manually split using the CUT indication * 2) Remove last union of result to run */WITH DLTables (RowID, TableName, TableN) as ( select row_number() over( order by t.Table_Name) as RowID , t.table_name as TableName , concat('/*', upper(t.table_name), '*/') as TableN from information_schema.tables t where t.table_name like 'hcm%' -- <<-- put various filters here -- and upper(t.table_name) IN ('CMNPSU', 'FPLEDX', 'FFRDEF', 'FFASDM', 'FPLEDG') order by TableName)select row_number() over() as RowNbr, RowID, TableN, TableName , concat('EXEC INFOR.CLEAR_TABLE(''', TableName, ''', ''True'') ' , CASE WHEN RowID % 80 = 0 THEN ' /* CUT */ ' ELSE ' Union ' END) as SQLQuery from DLTables order by TableName, RowID
The file format used to export / import data in M3 - Administration Tools - Business Engine Data Management is public ?
The Infor Partner's have access to it?
Hi Kevin,
thanks for your script.
I try to remove the ELSE condition as by comment but i'm not sure if it's executing (SQL query field return NULL).
Please see below my full query.
This might be a **** question but have you tried using the Purge function? Data Fabric > Data Lake > Purge.
You can select the Purge Criteria, select Purge by Filter. You can then select Data Objects, there is a Select All option. Select All objects, select Logical ID, add filters by accounting entity/location if needed, click Purge, sit and wait for a few minutes.
I've run this recently on a cloud MT environment and it works perfectly
I wouldn't recommend to use Purge unless you wish to delete the data objects in Data Lake. Though it is indeed correct what you are describing, that after purging data objects also the Compass cache will be cleared.
You need to take the SQLQuery column and copy paste that into another SQL window then execute it. This first SQL just builds all the CLEAR statements needed based on your filter.
You need to cut/separate them at the CUT mark per the comments.
WE had issues that even after we purged, we had to clear cache because there was data in cache that the purge did not touch.
