bulk_insert

1. Description

bulk_insert is used to load the data present in the data_file into the table present in ORACLE/MSSQL specified in the input.

2. Program location

<installation_path>/bulk_insert

3. Input

3.1 Mandatory Parameters

#	Parameters	Description	Example
1	db_name	Database name	ORACLE/MSSQL
2	connection_string	Database connection string	userid|password|ip:port/sid(for oracle) and Server|db|user|pssword(for mssql)
3	schema_name	Schema name	dbo/RUNCTROL_ORADB3
4	data_file	Path to the data file to be loaded.	test-bed/a-summary.txt
5	log_file	Path to write logs	test-bed/log.txt
6	diagnostics_log_file	Path to write diagnostics logs	test-bed/diag_log.txt
7	table_name	Table name into which data is to be loaded.	tblProductTotals

Note: In case of MSSQL provide full path of data_file.

3.2 Non Mandatory Parameters

#	Parameters	Description	Default value	Example
1	log_level	Level of diagnostics written to the log file.	info	error/warn/info/debug/trace/none
2	diagnostics_flag	The flag that decides whether performance diagnostics will be written to the diagnostics log file.	false	true or false
3	terminator	Separator used in between columns.Given only in case of MSSQL, for oracle provide in CTL file.	|
4	rowterminator	Terminator used in between rows.Given only in case of MSSQL, for oracle provide in CTL file.	0x0a
5	headercount	Number of rows to be skipped at the start.Given only in case of MSSQL, for oracle provide in CTL file.	0	1
6	ctl_file	Path to the control file.Required for ORACLE only.	""	test-bed/tblProductTotals.ctl
7	option	Additional parameters to be used for sqlcmd,sqlldr & sqlplus.The option parameters should be pipe seperated,containing the identification key and its value.	""	m,2|l,10

4. Output

4.1 Healthcheck report

The health check report gives summary about the number of rows loaded into the table and number of rows discarded.🔗.

5. Process configuration in streamdef table:

  .........
    "processName": "Bulk Insert",
    "processId": "1",
    "processBinary": "{SH_PROGRAMS}/bulk_insert",
    "processArguments": [
        "--connection-string",
        "{CONN_STR}",
        "--schema-name",
        "{SCHEMA}",
        "--db-name",
        "{DB}",
        "--data-file",
        "{SH_SUMMARYDATA}/IND/BALM/{ddmmyyyy}/a-summary.txt",
        "--log-file",
        "{SH_LOGS}/IND/BALM/{ddmmyyyy}/log.txt",
        "--diagnostics-log-file",
        "{SH_LOGS}/IND/BALM/{ddmmyyyy}/diag.txt",
        "--table-name",
        "tblProductTotals"
    ],
    "processDependencies": [],
    "processReport": "{SH_SUMMARYDATA}/IND/BALM/{ddmmyyyy}/a-summary-tblProductTotals-health-check-report.json",
    "processType": "LOADER",
    "infoLog": "{SH_LOGS}/IND/BALM/{ddmmyyyy}/log.txt",
    "errorLog": ""
  .........