Converting Tidal File Events to Control-M File Watchers

We are converting from Tidal Scheduler to Control-M. One of the hurdles is how to create all the necessary files for the File Watchers to work. This worked for us.

SET NOCOUNT ON

USE dba

–DEALLOCATE c
IF OBJECT_ID(‘usp_TidalFileEventExport’) IS NULL
EXEC(‘CREATE PROC usp_TidalFileEventExport AS SELECT 1’)

DECLARE
@trgmst_id INT,
@trgmst_name VARCHAR(80),
@sql VARCHAR(8000)
DECLARE c CURSOR FAST_FORWARD FOR
SELECT trgmst_id FROM Admiral..trgmst WHERE trgmst_type=4 — file events
OPEN c
WHILE 1=1
BEGIN
FETCH NEXT FROM c INTO @trgmst_id

IF @@FETCH_STATUS<>0
BREAK

SELECT @sql=
‘INTERVAL <5> # Sleep interval between file search (seconds)
MIN_SIZE 0Kilo # Minimum file size
MIN_DETECT <3> # Number of times Control-M checks file size which have to all match for it to consider the size is static

ON_FILEWATCH ‘+REPLACE(REPLACE(trgmst_info.value(‘(evtmdef/SOURCE)[1]’,’varchar(4000)’),'<<prod file server>>’,'<<dev file server’),’PROD’,’DEV’)+’\’+
trgmst_info.value(‘(evtmdef/FILEMASK)[1]’,’varchar(4000)’)+’ CREATE
THEN
DO_CMD move %FILENAME% ‘+REPLACE(REPLACE(trgmst_info.value(‘(evtmdef/TARGET)[1]’,’varchar(4000)’),’prodĀ file server’,’dev file server’),’PROD’,’DEV’)+’
END_ON’,
@trgmst_name=trgmst_name

FROM (
SELECT CAST(trgmst_info AS XML) AS trgmst_info,
trgmst_name
FROM Admiral..trgmst
WHERE trgmst_type=4 — file events
AND trgmst_id=@trgmst_id
) a

SET @sql=’alter proc usp_TidalFileEventExport as select ”’+@sql+””
EXEC(@sql)

SET @sql=’bcp “exec dba..usp_TidalFileEventExport” queryout “d:\sqlserver\’+@trgmst_name+’_FW.txt” -c -S -T’
EXEC master..xp_cmdshell @sql

END
CLOSE c
DEALLOCATE c
DROP PROC usp_TidalFileEventExport

This entry was posted in Control-M, Tidal Enterprise Scheduler. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *