Automating the Manager features

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.

1. To create the script file, open a .txt file.
2. Feed it according to the following commands and their syntax:
   Information noteNote: 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 Information noteNote: 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. Information noteNote: 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.