Quick Reference Guide to XMDBTool commands

XMDBTool has evolved into a tool with lot of different scripts (batch files), each one built specifically for a unique feature. Each batch file has some parameters, which are used by XMDBTool. This document provides a brief overview of all the parameters. The idea behind this document, is to have a handy reference guide, the users can quickly go through.

These parameters must be specified in the xmdbtool.properties file (in the conf folder) or it can be passed to the script through the command line.

Server URL and user credentials

The server url is s=https://xm10.inforcloudsuite.com:443/inbox/login.jsp?tenant=<tenantID>

Replace the <tenantID> with the actual tenant ID provided by HOPS in the below mentioned server address.

Enter Login ID and Password as admin.

Configuration parameters for Importing Data [upload_data.bat]

These parameters are specified on the client side[upload_data.bat/sh] and as background process parameters for XMDBToolBkg.Import BKG process:

  • uploadTableName: It is the name of the table where you can send the data. For example; uploadTableName = alex_application_filter.
    Note:  Not required to be specified in the XMDBToolBKG.Import Bkg process.
  • uploadFileName: It is the location of the file that must be uploaded to the table specified in the uploadTableName. For example; uploadFileName = C:\\Docs\\data.txt.
    Note:  Not required to be specified in the XMDBToolBKG.Import Bkg process.
  • purgeData: If the purgeData parameter is set to 1, the data in the table specified in uploadTableName is purged (deleted) before the new data specified in the data file that is inserted into the table. To preserve the data in the table, set the purgeData to 0 or comment it out (include # in front of purgeData parameter). For example; purgeData = 1.
  • compress: It is used to compress. The default value is "true".
  • uploadModule: It is the name of the module to be uploaded. This parameter takes precedence over uploadTableName. For example; uploadModule = PSO_CONFIG.
    Note:  Only available for use with XMDBTool client upload_data.bat/sh.
  • uploadFromThisFolder: It is the name of the folder where all the data files are part of the uploadModule reside. For example; uploadFromThisFolder = C:\\Work\\UploadFromHere3.
    Note:  Only available for use with XMDBTool client upload_data.bat/sh.
  • logFileLocation: It is the name of the folder where the log files are directed after an upload run. Must be used for a single table upload or multiple tables module upload. This parameter is not mandatory. For example; logFileLocation = C:\\Work\\ClientInterfaceTool\\UploadFromHere.
    Note:  Only available for use with XMDBTool client upload_data.bat/sh.
  • chunkSize: This parameter allows uploading large number of files. Customers must set the chunk size (in number of lines). Default is 100000 lines.
  • im_logMerge: If it is set to true, the log files are merged in one file. Else for each run, there is separate log file generated, each log file is time-stamped. Default value is set to true.
  • im_delimiter: It is the delimiter used to import the files. By default, its bar (|).
  • debugInfo: This parameter is used to get additional logging information. Default value is false. Set it to true to get more information.
  • chunk - This parameter is used only with the XMDBToolBkg.Import background process. If this is set to True then the import file is chunked into small parts before being processed.

Configuration parameters for Exporting data [download_data.bat/sh]

These parameters are specified on the client side[download_data.bat/sh] and as background process parameters for XMDBToolBkg.Export BKG process:

  • downloadTableName: It is the name of the table from where the data is downloaded. It is the mandatory parameter. For example; downloadTableName = alex_application_filter.
  • downloadModule: It is the name of the module to download. This parameter take precedence over downloadTableName. Multiple modules are separated by colon (:). For example; downloadModule =ER.
  • autoVerify: It includes the module that must be AutoVerified in the autoVerify parameter. It is an optional parameter. Multiple modules are separated by colon (:). For example; autoVerify = ER.
  • downloadFolderName: It is the name of the folder where the downloaded data and log files are directed on the client side. It is an optional parameter. You must include double back slashes [\\] as folder separators. For example; downloadFolderName = C:\\Work\\ClientInterfaceTool\\code.
  • convertToCRLF: By setting convertToCRLF = 1, it converts the downloaded file to CRLF format. Default value is 0.
  • ex_delimiter: It is the delimiter used to export files. By default, its bar (|).
  • debugInfo: This parameter is used to get additional logging information. Default values is false. To get more information, Set it to true.
  • ex_logMerge: If it is set to true, the log files are merged into one file. Else for each run, there is separate log file generated and log file time-stamped. Default value is set to true.
  • colHeader: It includes the columns in the output (exported) file. It must be set to true. It is an optional parameter.

Configuration parameters for downloading full data from transactional database[download_all.bat/sh]

  • skipTableList: You can specify a list of tables that do not get unloaded. This list of table is a comma separated list. However, if this parameter is supplied through the command line then ensure that each table is separated with a dot (.) instead of comma. By default, skipTableList is set to almt_maxbobid.
  • printColumnHeaders: If set to true it prints column headers. By default, it is false.
  • downloadFolderName: This is the name of the folder in the client machine where the downloaded data is residing.
  • unloadToDataSets: This can be set to true or false. By default, unloadToDataSets is set to true. If set to true,download_all.bat downloads all the data files to specific directories. If it is set to false, all the data files are downloaded to a single folder (that specified in the downloadFolderName parameter).
  • numOfFiles: This parameter decides the number of files that are transferred from server to client in each call to the server. Default value is 15.
  • ignoreNeverUnloadTableList: You can specify a list of tables that do not get unloaded. These tables are never unloaded (unless you set ignoreNeverUnloadTableList=TRUE):
    • almt_load_info
    • almt_duplicate_bob_info
    • alco_flattened_group
    • almt_bobids

Configuration parameters for setting XMDBTool log levels

These parameters are used by all the XMDBTool Client Batch files and all XMDBTool Background process:

  • XMDBTool_logLevel: You can set the XMDBTool_logLevel to 1 (Serious), 2 (Minor), or 3 (Debug). By default, the XMDBTool_logLevel is set to 1.
  • XMDBTool_logTo: By default it is set to 3.
  • debugInfo: Set the debugInfo parameter to true, to get more information in the logs.

Configuration parameters for archive purge [archivepurge.bat/sh]

These parameters are specified on the client side[archivepurge.bat/sh] and as background process parameters for XMDBToolBkg.Archive BKG process:

  • archive: Specify true, if archive must be done. By default, it is set to false.
  • archive_location: It is used to specify the location where archived files must be stored.
  • activity_ids: It is used to specify the activity IDs that must be archived. IDs needs to be separated by dot (.). For example; activity_ids=72001.72051.
  • cutoff_date: It is used to specify the cut-off date. The format must be YYYYMMDD. This is mandatory parameter when date range is not in effect.
  • startDate: The start date of the archive process. When specifying from the command line, the date and time must be specified in the yyyy-MM-dd%HH:MM format. The percent is included to avoid the space between the date and the time.
  • endDate: The end date of the archive process. When specifying from the command line, the date and time format must be specified as yyyy-MM-dd%HH:MM. The percent is included to avoid the space between the date and the time.
  • incremental: This parameter is only specified when the archive purge is run in the bkg process. If true, start date is taken from the alex_xmdbtool_etl table.

Configuration parameters for uploading ADC [upload_adc.bat/sh]

  • adc: It is used to specify the ADC script location. For example; adc = C:\adc\sample_adc.xml.

Configuration parameters for uploading ETL scripts [upload_etl_script.bat/sh]

  • script: Script location must be provided with this parameter. For example; script=C:\Script\etl_script.xml.
  • type: Either ADC, ETL, CLEANUP, CONVERSION_SCRIPT script type must be provided with this parameter.

Configuration parameters for supporting PGP encryption [upload_key.bat/sh]

  • keyfile: It is used to specify the Key File Location. For example; keyfile = C:\keys\mykey.asc.
  • keytype: Key type must be provided. Specify pri for private key or pub for public key. Specify pub_verifysign for signature verification.
  • passphrase: Key Passphrase must be provided.

Configuration parameters for supporting background process [run_bkg.BAT/SH]

  • op: It is used to either start or stop a bkg process. Default value is start.
  • bkgName: It is used to specify the bkg process to run.
  • bkgWaitTime: It is an optional parameter. It is used to set the waiting time before the server is queried for the bkg status. It is specified in seconds. If not set, default value is set to 60 seconds.
  • newLog: It is an optional parameter. If new log is required for each run, either leave it null or set it to true. If false, then no new log is created. Log is appended. By default, if value is null set it to true.
  • bkglogReqd: It is an optional parameter.- If bkg log is required to be downloaded, either leave it null or set it to true. If false, then no new log is created. Log is appended. By default, if value is null set it to true.

Configuration parameters for supporting import of cc files [upload_cc_feed.bat/sh]

These parameters are specified on the client side[upload_cc_feed.bat/sh] and as background process parameters for XMDBToolBkg.CC BKG process:

  • cc.uploadFileName: It is used to specify the location of the CC File.
  • cc.uploadGetLogFileOnly: Set to true, if only log file is required. By default it is set to false.
  • cc.uploadImportFormat: It is used to specify the import format.
  • cc.uploadOverwrite: It is an Optional parameter. By default it is set to false. It is used to overwrite the cc file on the server.
  • cc.uploadCurrencyCode: It is used to specify the currency code. By default it is USD.
  • debugInfo: It is used to get more log info. Default value is false. If it is not null and not true it is set to false.
  • cc.uploadDeleteAfterImport: It is an optional parameter. By default it is true. The CC file is deleted once the import is done.
  • cc.importThoseBilledCurrencyMatched: It is an optional parameter.- By default it is false.
  • cc.archiveLogInOneFile: It is an optional parameter. It is only used by the XMDBToolBKG.CC. By default the value is false. If true, it archives the log files (take the backup with the timestamp) and write the current log to a new file.
  • cc.logFileSameAsSrc: It is an optional parameter. If set to true, the log file name is exactly same as source file (without the timestamp). Default value is set to false.

These commands are only supported in BKG for CC Import [XMDBToolBKG.CC]:

  • rerun: It is used only when the XMDBToolBkg.CC_RERUN is run. When rerun=true, it picks up the files from the rerun folder.
  • runFile: It is used only when the XMDBToolBkg.CC_RERUN is run. It is used to specify which specific file needs to be run. This file is picked up from the archived or the rerun folder, depending on how the file name is specified. For example, if runFile=visa.dat then the file is searched in the archive folder, but if the runFile=rerun/visa.dat then the file is searched for in the rerun folder. This parameter is always used in conjunction with the runFile paramter.
  • cc.filepat: It picks up only the files (from the sftp server) containing the pattern specified in this parameter. Optional parameter. Only used in the XMDBToolBkg.CC background process.
    Note: BKG for CC Import [XMDBToolBkg.CC] now accepts signed PGP CC encrypted feeds. You can upload the public key for signature verification using the update_key.bat/.sh tool.

Configuration parameters for supporting export using templates [run_export.bat/sh]

  • profileFile: It is used to specify the location of the profile xml file. Either profileFile or profileName is used.
  • profileName: It is the name of the profile specified.
  • logFile: The name and location of the log file. Default value is run_export.log.

Configuration parameters for running ETL [run_etl.bat/sh]

These parameters are specified on the client side[run_etl.bat/sh] and as background process parameters for XMDBToolBkg.ETL BKG process:

  • etlChunk: It is used to chunk the full etl file and get the etl data file as number of small individual files. You need to set it to true for chunking the files. By default, its false. it is used only on the client side.
  • etlChunkSize: By default 10000 lines. This suggests the number of lines in each chunked file. It is used only on the client side.
  • deploy_tables: It must be set to Y. This is required parameter along with the mapping_classes parameter for running full etl. By default it is null.
  • deploy_specified_tables: It is used to specify the tables where the etl data needs to be deployed. Multiple tables can be specified separated by a colon. When this parameter is used only specified tables are mapped. For example; deploy_specified_tables EXPENSE_REPORT:EXPENSE_REPORT_LINE_ITEM.
  • mapping_classes: It must be set to USER_HIERARCHY. This is required parameter for running full etl. By default it is null.
  • fulletlrerun: It is used to run the full etl again in case of any errors. The beauty is, the original full etl is still in place (since it is deleted after the last chunk file has been downloaded), so no etl is generated again. The old file is chunked again and sent out. This parameter needs to be set to true if the rerun is required.
  • incremental: Set this parameter to true to run incremental etl. Default value is null.
  • manual_lock_rdb: It is used to unlock database. Set to N to unlock the database.
  • startDate: The start date that must be specified for running etl in a date range or in full etl with date range. Format must be MM-DD-YYYYY HH:MM:SS. When dates are specified in the command line, add a '%' between the data and the time part. For example, MM-DD-YYYYY%HH:MM:SS.
  • endDate: The end date that must be specified for running etl in a date range or in full etl with date range. Format must be MM-DD-YYYYY HH:MM:SS. When dates are specified in the command line, add a '%' between the data and the time part. For example, MM-DD-YYYYY%HH:MM:SS. Default date for full etl is current date.
  • db_type: It is used to specify the database type. By default it is mssql.
  • sql_end_delimiter: It is used to specify the delimiter. By default it is GO.
  • to_file_begin_tx: It is used to specify the beginning of the transaction.
  • to_file_commit_cmd: It is used to specify the commit.
  • toFile: It is used to specify the etl data file location. On Client, the full location is required and on the server only filename is required. The etl run's db-to-db if toFile is not specified.
  • splitETLFile: It is set to true, if we need the etl file to be generated as individual files (each file with the table name). By default it is false. It is used only on the server side.
  • etl_sqlTrace: If set to true, the etl_sqlTrace file is generated in the log folder on the client side. This file is generated only when ETL db - to - db is run. This parameter can be used on the client side and as bkg parameters to the XMDBToolBKG.ETL bkg process.

Configuration Parameters related to Setting up of SFTP

  • sftp: If this is set to true, the system tries to connect to SFTP server using the provided credentials. Default value is false.
  • sftpHost: The SFTP Host address.
  • sftpUser: The SFTP user ID.
  • sftpPassword: The SFTP user password.
  • sftpHostImport: This is the location of the folder on the SFTP server, where the data files that must be imported to the Expense Management system (cc and nonCC), reside. Generally, this location is a relative path starting from the user folder. For example, sftpHostImport=/import/.
  • sftpHostExport: This is the location of the folder on the SFTP server, where the XMDBTool exportbkg places all the exported files from the Expense Management system. Generally, this location is a relative pathstarting from the user folder. For example, sftpHostImport=/export/.
  • sftpHostLog: This is the location of the folder on the SFTP server, where the XMDBTool background process places all the log files from the Expense Management system. Generally, this location is a relative path starting from the user folder. For example, sftpHostImport=/log/.
  • sftpHostForLog: The host SFTP url where the logs files are to be sent. This is only required if the log files must be sent to a totally different SFTP server.
  • sftpUserForLog: The user id of the sftp server where the logs files are to be sent. This is only required if the log files must be sent to a totally different SFTP server.
  • sftpPasswordForLog: The password of the sftp server where the logs files are to be sent. This is only required if the log files must be sent to a totally different SFTP server.
  • sftpHostExportPass: Optional parameter. Used only in case when exported files must be sent to a separate folder on the GXS Server. Use this parameter to specify the sftp folder location where the exported files (other than the files that require the detokenise process) must be routed.
  • detokenFiles: Optional Parameter. Sometimes CC files, that are exported [generally er_expense_export_line.txt], include token numbers instead of CC numbers. To detokenise, these files are sent to the GXS server, where tokens are replaced with the actual card numbers. So all files, that must be detokenized are specified in this parameter. Use this parameter to specify the prefix of the files that must be routed to the export folder. The default value is er_expense_export_line. Any number of files can be specified and are separated by comma.
  • rm: If this parameter is set to true, The files in the SFTP server are deleted after it has been read by Expense Management. By default, this value is set to false.

Configuration Paramters for Setting up Trondent

  • sftpHostTrondent: The SFTP Host address.
  • sftpUserTrondent: The SFTP user ID.
  • sftpPasswordTrondent: The SFTP user password.
  • sftpHostImportTrondent: The folder location on the SFTP for importing Trondent files.

Important Application Parameters used by XMDBTool

  • importFileLocation: This is the base location, where the imported data files are residing, and from where the bkg picks it up for processing. This base location must be specified. Default value is ../temp/sftp.
  • xmdbtool.archiveDays: Specify the number of days to archive the imported files. Default is 1 day.
  • xmdbtool.archiveDaysTrondent: Specify the number of days to archive the trondent files. Default is 1 day.
  • xmdbtool.archiveDaysCC: Specify the number of days to archive the imported Credit Card files. Default is 1 day.
  • xmdbtool.backup: Specify the number of days to backup the exported files after the XMDBTool export bkg is run. Default is 1 day.
  • archive.dir: The location where the archived files are stored. Default value is ../../xmshare/archive.
  • exportFileLocation: This is the base location, where the exported data files are residing. This base location must be specified. Default value is ../temp/sftp.