Automating the Manager features - 7.3

Talend Change Data Capture User Guide
Version
7.3
Language
English
Product
Talend Change Data Capture
Module
Talend Change Data Capture
Content
Data Governance > Third-party systems > Database components (Integration) > Change Data Capture
Data Quality and Preparation > Third-party systems > Database components (Integration) > Change Data Capture
Design and Development > Third-party systems > Database components (Integration) > Change Data Capture
Last publication date
2023-08-08

All actions usually performed in the Manager user interface (until the distribution of a model), can be automated in a scripting file. All sources and targets supported by the Manager are also supported in the script file.

About this task

The actions to be automated in the script file are the following:

  • starting and stopping database journalization,
  • creating and deleting an environment,
  • creating and deleting a model,
  • adding and deleting tables,
  • creating and deleting a target,
  • starting, stopping and terminating the distribution of a model,
  • performing a load recovery on the distribution.

Procedure

  1. To create the script file, open a .txt file.
  2. Feed it according to the following commands and their syntax:
    Note: You can add as many commands as you like in the same script file (for example, connecting to various source databases and creating various distributions is possible). You can also create one script file for each source type. It depends on your needs.
    Command Description Details and Syntax
    : comment line   
    :this is a comment line
    or
    : this is a comment line
    connect source To connect the source database Type entry is one among the databases supported as source (AS400, MS SQL Server, Oracle, OpenEdge, PostgreSQL, MySQL, MariaDB).
    Three different commands are used:
    • For AS400 IBMi source: 
      connect IpAddress/AS400/
    • For MS SQL Server and Oracle sources: 
      connect IpAddress/Type/Instance
    • For MariaDB, MySQL, PostgreSQL and OpenEdge sources:
      connect IpAddress/Type/DB_Name
    start journalization   
    start-journalisation
Database = <database_name>
StoragePath = <storage_path>
end-journalisation
    where storage_path is the folder located on the same machine as the engine, used for the storage of .JRN files.
    select-env To select an environment

    Use this command only if you want to use an environment that is already created in the Manager.

    select-env envid
    create env To create and select an environment

    Default value is used if no parameter is specified    		 
    create-env envid
	EnvType	:=	2
				:0 = Production	'default
				:1 = Test
				:2 = Training
				:3 = Model
				:4 = Deployment
	Description    :=	SFEnvirt Training 01
	Hold	    :=	0
				:0 = False	     :default
				:1 = True
	SrcId	    :=	S01
	Tempo	    :=	1
	Retry	    :=	5
end-create
    create metadata To create metadata for this environment Mainly for the JSON variables used for streaming sources (Kafka, Kinesis, Snowflake). The JSON script is then included in the create model command.
    select-env envid
create-metadata
	#variable1#	:=	metavalue1
	#variable2#	:=	metavalue2
	#variable3#       :=      metavalue3
end-create
    select model To select a model 
    select-env envid
select-model grpid
    create model To create and select a model

    Default value is used if no parameter is specified    		 AS400 source 
    select-env envid
create-model grpid

GrpType         :=   0  
			:0 = Journal
			:1 = Extract
Description     :=   SFo-Replication a l'identique
iASPgroup       :=   *NONE
LibJrn          :=   DD_TRAING
JrnName         :=   JOURNAL
ManualDistrib   :=   0  
			:0 = False		:default
			:1 = True
AllMembers      :=   0  
			:0 = False		:default
			:1 = True
AccesMode       :=   3  
			:0 = Auto		 :default
			:1 = RRN
			:2 = Index Key
			:3 = SQL
			:4 = Backup
OrpheanMgt      :=   0  
			:0 = False		:default
			:1 = True
ContError       :=   0  
			:0 = False		:default
			:1 = True
Repair          :=   1  
			:0 = No
			:1 = Yes		  :default
			:2 = Skip
			:3 = Yes with trace
DDLchange       :=   1  
			:0 = No		
			:1 = Ignore
			:2 = Yes		
			:3 = Yes Alter no Drop	:default
			:4 = Yes Alter with Drop
Truncate        :=   1  
			:0 = No
			:1 = Yes		  :default
			:2 = Ignore
RGZPFM          :=   2  
			:0 = No
			:1 = Yes
			:2 = Ignore		:default
ADDPFM          :=   2  
			:0 = No
			:1 = Yes
			:2 = Ignore		:default
RMVMBR          :=   2  
			:0 = No
			:1 = Yes
			:2 = Ignore		:default
DLTF            :=   0  
			:0 = No		    :default
			:1 = Yes
NullProcess     :=   0  
			:0 = No		    :default
			:1 = Yes
DateAschar      :=   0
			:0 = No		    :default
			:1 = Yes
MetaScript      :=   Begin
script
MetaScript      :=   End

end-create
    Other sources 
    create-model grpid
GrpType         :=   0  
			:0 = Journal
			:1 = Extract
Description     :=   SFo-Replication a l'identique
DataBase        :=   DD_TRAINING
ManualDistrib   :=   0  
			:0 = False		:default
			:1 = True
AllMembers      :=   0  
			:0 = False		:default
			:1 = True
AccesMode       :=   3  
			:0 = Auto		 :default
			:1 = RRN
			:2 = Index Key
			:3 = SQL
			:4 = Backup
OrpheanMgt      :=   0  
			:0 = False		:default
			:1 = True
ContError       :=   0  
			:0 = False		:default
			:1 = True
Repair          :=   1  
			:0 = No
			:1 = Yes		  :default
			:2 = Skip
			:3 = Yes with trace
DDLchange       :=   1  
			:0 = No		
			:1 = Ignore
			:2 = Yes		
			:3 = Yes Alter no Drop	:default
			:4 = Yes Alter with Drop
Truncate        :=   1  
			:0 = No
			:1 = Yes		   :default
			:2 = Ignore
NullProcess     :=   0  
			:0 = No		    :default
			:1 = Yes
DateAschar      :=   0
			:0 = No		    :default
			:1 = Yes
MetaScript      :=   Begin
script
MetaScript      :=   End
end-create
    select-table To select a table 
    select-env envid
select-model grpid
select-table tableid
    add-table

    To add a new table to the model selected

    The Id is the unique identifier (numeric from 1 to 9999999999) of the table in this model.

    Thanks to this Id, a table can be added more than one time but with different identifiers.

    AS400 source

    select-env envid
select-model grpid
add-table Library FileName [TargetTableName] [Id:nn]
    Other sources
    select-env envid
select-model grpid
add-table TableName [TargetTableName][Id:nn]
    select target   
    select-env envid
select-target tgtid
    create target To create and select a target 
    select-env envid
create-target tgtid
Description	  :=   My DD machine
NewSourceId	  :=   
				: blank is default
TargetType	   :=   1
				: 0 = IBM I 
				: 1 = Windows		:default	
	Hold	   :=  0
				:0 = False		   :default
				:1 = True
	TargetName    :=  MyVM
	TargetAdress  :=  192.168.0.0
end-create
    distribute model To distribute the model to the target 
    select-env envid
select-model grpid
select-target tgtid
distribute
	Hold            :=	0
				:0 = False		:default
				:1 = True
	scheduled       :=	0
				:0 = False		:default
				:1 = True
	TgtBDType       :=	0
				:0 =	IBMi
				:1 =	SQL Server
'2 = Oracle ODBC
				:5 = ODBC
				:8 = PostgreSQL 
				:9 = Text
				:A = MySQL
				:B = DB2 LUW
				:C = XML
				:D = SyBase
				:E = Oracle OCI
				:F = SQL Server Bi-Directional
				:G = OpenEdge
				:H = SQLite
				:J = MySQL Latin
				:K = MySQL Native
				:L = PostgreSQL Native
				:M = SQLite NAtive
				:N = GreenPlum
				:O = Netezza
				:P = Vectowise MicroBatch
				:Q = VectorWise
				:R = NuoDB
				:S = Altibase
				:T = Iseries Access
				:V = Kafka
				:k = Kinesis	
				:W = Hana
				:X = Teradata	
				:Y = SalesForce
				:Z = MariaDB	
				:b = MariaDB Latin	
				:c = MariaDB Native
				:d = Azure SQL	
				:e = Azure Event Hub
				:g = Azure Service Bus
				:i = SnowFlake
	UseLibName      :=	0
				:0 = FileName		:default
				:1 = BDName_FileName
				:2 = BDName..FileName or BD.FileName
	TargetDB        :=	MyDatabase
	LoadByFlatFile  :=   0
				:0 = False		:default
				:1 = True
	PathForFlatFile := 
#if Kinesis or kafka or servicebus or eventhub Target
	Format           :=  J			:default
'J = Json
'A = Avro
'F = Fixed
'D = Delimited
	FieldNameOn1st   :=  0
				:0 = False		:default
				:1 = True
       FieldSep         :=  ;
				:tab = tabulation
				:; = semicolon		:default
				:, = colon
				: … 1 character
 	TextSep         :=  "
				:1 character
	LineSep          :=  2
				:1 = <CR>
				:2 = <CR><LF>		'default
				:3 = <LF>
				:4 = ;
				:5 = ,
				:6 = tabulation
				:7 = nothing
	DateFormat       :=  2
'0 = DMY
'1 = MDY
'2 = YMD			'default
'3 = YDM
'4 = DYM
'5 = MYD
	YearIn4Digits    :=  1
				:0 = False		
				:1 = True		:default

	DateDelimiter    :=  /			:default
				1 character
	TimeDelimiter    :=  :			:default
				1 character
	DecimalSep       :=  .			:default
				1 character
	kdsConnectString    :=  region=eu-west-3;
				accesskey={0B733EDD1CF71};
				secretkey={0B733EDD1CF71}
#elseif SnowFlake Target
	sfAccount         :=  AB65262.west-europe.azure
	sfConnectString   :=  DSN=SnowFlakes;UID=gammadev;
				PWD={0B733EDD1CF71};
				CodePage=e297UTF8.cpg;
	sfPathForTextFile :=  D:\DD_FILE\AS400\M3P
	sfCommandFile     :=  LoadData.cmd %s %s 1
	sfMaxRecord       :=  0
	sfMaxTime         :=  5
 #else
	ConnectString     :=   DSN=…
#endif
#endif
	Planning          :=   000..000
				sequence of 168 digits (0 or 1) 
				for the 168 hours of a week with 
				0 = inactive, 1=active.
The first digit represents Monday from 0 AM to 1 AM
	RecoveryType      :=   
				:Blank is nothing
				:C = Load
				:P = Sequence
	CaseSensitive     :=    0 
				:0 = False		:default
				:1 = True
	CacheSize         :=    0
				:0 = 32Ko		:default
				:1 = 128K0
				:2 = 256Ko = 128*2
				:3 = 512Ko = 256*2
				..
				:7 = 8Mo
#if AS400 source
	RecoveryLib        :=
#endif
	RecoveryReceiver   :=
       Sequence           :=
	end-distribute
    change-distributed-table To change the properties of the distributed table

    First, use the selection commands:

    select-model grpid
select-target tgtid
    Then, change the properties of the distributed table:
    change-distributed-table ficid TargetDBName TargetTableName

    In case you want to change only the target table name, enter:

    change-distributed-table ficid default TargetTableName
    add query to table To add the query to the selected table
    Note: Only for NT sources.
    First, use the selection commands:
    select-env envid
select-model grpid
select-table tableid

    Then add the query to the table:

    begin-script-where
    :Some query after where clause
	SALESNUM = '1' AND PROMOKEY > 10
end-script-where
    add transformation to the table To add the transformation to the selected table First, use the selection commands:
    select-env envid
select-model grpid
select-table tableid
    Then add the transformation to the table:
    begin-transformation
     DCL O.SALESNUM Char 20 NULL Rtrim KEYORDER 1
     DCL O.PROMOKEY Numeric 10,0 NULL Same
     
     if I.SALESNUM = '1' Then
          O.PROMOKEY = 1
          O.SALESNUM = I.SALESNUM
     Endif
     end-transformation
    DCL lines are the default ones. They will appear in the list if no other declaration is entered.

    If you want to add another DCL than the default ones, you can write it in the transformation block. The default DCL lines are automatically removed from the list and only your new lines are taken into account.
    Note: Between begin-transformation and end-transformation, comments are marked with /* only.
    stop distribution To stop the distribution of the model 
    select-env envid
select-target tgtid
stop-distribution grpid
    kill distribution To terminate the distribution of a model 
    select-env envid
select-target tgtid
kill-distribution grpid
    delete distribution To delete the distribution of a model 
    select-env envid
select-target tgtid
delete-distribution grpid
    delete target To delete a target 
    select-env envid
delete-target tgtid
    delete model To delete a model 
    select-env envid
delete-model grpid
    delete table To delete a table 
    select-env envid
select-model grpid
delete-table tableid
    delete environment To delete an environment 
    delete-env envid
    stop journalization To stop journalizing a database 
    dejournalise dbid
  3. Save the file.
  4. Launch a command prompt and enter the following commands:
    >cd TalendChangeDataCaptureDestinationPath. Press Enter.
    DDmanager -cicd "ScriptFilePath". Press Enter.

Results

Distributions are created as they were declared in the script file.

If you want to view the result: launch the Manager and subscribe to the source, then connect to it. For more information, see Managing sources subscription and connection. To start the replication process, follow Operating and supervising a replication section.

To check the logs after the start of the script, open DDMGRAUTO.log file located in DestinationPath repository (the same repository as the Manager .exe file).

To plan when to launch all replications, you can configure the Scheduler (see Starting Replications Automatically).