Automating Database Activities

You can create Javelin workflows to automate various database activities. Javelin is compatible with both 32-bit and 64-bit databases, and supports remote database connections. You need database access to work with databases and database activities. 
tdm43
You can create Javelin workflows to automate various database activities. Javelin is compatible with both 32-bit and 64-bit databases, and supports remote database connections. You need database access to work with databases and database activities. 
Javelin supports the following database activities:
 
 
2
 
 
 
Note:
 Javelin supports Oracle, DB2, and Microsoft SQL Server. Subset also supports these data sources. Thus, Subset can generate Javelin workflows for Oracle, Microsoft SQL Server, and DB2. Javelin has been tested on Windows and Linux.
Database
Database functions let you automate various database operations in Javelin flows. The following functions are available:
Database Query to Load Table
Issues a query to return a database table. DataTable is a .NET object that is returned based on query results. It represents in-memory data.
  •  
    Query
     
For Each Row
Performs an instruction for the specified rows. Use variables to specify an expression that operates on a database row.
Cassandra - Execute Query
Runs a query on a Cassandra database.
  •  
    Contact Points
     — Defines a comma separated lists of host addresses of Cassandra nodes.
  •  
    Keyspace
     
  •  
    Username
     
  •  
    Password
     
  •  
    Query
     
  •  
    Output
     
Netezza - Execute Query
Runs a query on a Netezza database.
  •  
    OleDB Provider
     — Defines the server name.
  •  
    Data Source
     
  •  
    PersistSecurityInfo
     — Specifies whether to persist (true) or not (false).
  •  
    Port
     
  •  
    Username
     
  •  
    Password
     
  •  
    Query
     
  •  
    Output
     
Database Action API Reference
Database related actions. Note that in connection details related properties, you can either pass a full connection string or pass the connection field values separately (like server, username, password, etc). One of these two ways is mandatory and the other is optional. In the mandatory fields, this is listed as "Y -- or use connection string" or  "Y -- or configure individual values".
Database Query to load Data Table
Javelin contains dedicated actions for commonly used databases (Oracle, MS SQL Server, DB2, and so on). We recommend to use those dedicated actions for such databases. For other generic database connections, use this action.
Property Name
Mandatory
DataType
Description
ConnectionString(IN)
Y
string
Connection string to connect with database
ContinueOnError(IN)
N
Boolean
If the step encounters an error, should the workflow move on or stop. Set this to True if you want it to continue on error.
Display Name
N
string
Name or brief description of the activity that you perform
IsStoredProcedure(IN)
N
Boolean
Is it a stored proc. Values: yes/no
OutElement(OUT)
Y
datatable
Contains the query result data table
ProviderInvariantName(IN)
Y
string
Oracle/SQLServer who ever is provider
Query(IN)
Y
string
Query to execute
Timeout(IN)
N
int
Duration of the timeout in seconds
Oracle
Oracle functions let you automate various Oracle database operations in Javelin flows. 
Execute Query
OracleActivity executes a query against an Oracle database.OraclePLSQLActivity also supports Oracle PL/SQL queries.
 
Note:
 Install ODP.NET 4.0 (v 4.112.3.0) for this activity to work.
  •  
    Server
     
  •  
    Service Name
     
  •  
    Port
     
  •  
    Username
     
  •  
    Query
     — The query can be either Select, Insert, Update or Delete.
  •  
    Output
     — For Select queries, output rows are assigned to an output variable which is set in the OutDataTable property.
You can also execute Stored Procedures that exist on the server using OracleActivity.
Example: Use the following VB.net syntax in the query field to invoke a procedure with 4 parameters:
string.Format("begin schema.package.procedure_name (‘{0}‘, ‘{1}‘, ‘{2}‘,’{3}‘); end;", value1, value2, "test", "test2")
The first two parameters (value1 and value2) are Javelin variables. The second two parameters (test and test2) are hard-coded.
SQL Server
SQL Server functions let you automate various Microsoft SQL Server database operations in Javelin flows. The following functions are available:
Execute Query
SQLActivity executes a query against SQL Server database. You have the option to add parameters.
  •  
    Server
     
  •  
    Database Name
     
  •  
    Username
     
  •  
    Query
     — The query can be either Select, Insert, Update or Delete.
  •  
    Output
     — For Select queries, output rows are assigned to an output variable which is set in the OutDataTable property.
Examples: 
SQL Server Action API Reference
Execute Parameterized Query
Execute SQLServer query.
Property Name
Mandatory
DataType
Description
CommandTimeOut(IN)
N
int
Time for which connection should wait for command to start returning results.
ConnectionString(IN)
Y -- or configure individual values
string
SQLServer connection string.
ContinueOnError(IN)
N
Boolean
If the step encounters an error, should the workflow move on or stop. Set this to True if you want it to continue on error.
DatabaseName(IN)
Y -- or use connection string
string
Database name
DisplayName
string
Name or brief description of the activity that you perform
OutDataTable(OUT)
datatable
Contains resultant query data
Password(IN)
Y -- or use connection string 
string
Password for database connection
Query(IN)
string
Query to execute
Server(IN)
Y -- or use connection string
string
Database Server info
Timeout(IN)
N
int
Duration of the timeout in seconds
Username(IN)
Y -- or use connection string
string
Username for database connection
DB2
DB2 functions let you automate various database operations in Javelin flows. 
Execute Query
Db2Activity executes a query against an IBM DB2 database.
 
Note:
 Install the IBM DB2.NET Data Provider version 9.7.4.4 for this activity to work.
  •  
    Server
     
  •  
    Database Name
     
  •  
    Username
     
  •  
    Query
     — The query can be either Select, Insert, Update or Delete.
  •  
    Output
     — For Select queries, output rows are assigned to an output variable which is set in the OutDataTable property.
Bulk Copy
The Bulk Copy functions automate the bulk copy of data into a database. You can use these functions to automate the copy of a Data Subset extract into its target database. 
The following functions are available:
Data Reader/DB2 Data Reader
Connects to a database table to read its data. Using this we query a database and return a datatable that is held as an IDataReader object (This object must be created in the variables pane by the user). The IDataReader object can then be passed to 'DB2/Oracle/SQL Bulk Copy - Data Reader' actions to pull data from the source system, and push to the target system without loading in memory.
Specify the following required properties:
  •  
    Server
    Server name where the database is installed. Specify a server port if applicable.
  •  
    Database
    Specifies the database to connect to.
  •  
    Schema
    Specifies the schema if needed. Otherwise, leave this field empty.
  •  
    Username/Password
    Enter valid database credentials.
  •  
    Query
    Specifies the query to use to retrieve records from the database.
  •  
    OutDataReader
    Creates a variable of type IDataReader, with no default value specified, into which you can enter variable data for the bulk copy.
DB2BulkCopy, OracleBulkCopy, SQLBulkCopy - Data Reader
The IDataReader object holding the data table is passed to the relevant (DB2 or Oracle or SQL) action to pull data from the source system, and push to the target system. Drop the DB2/Oracle/SQL action inside the Data Reader Action. The Source and Target database can be different types and have different names, but the column count must be the same.
If the source and the target column names differ, specify column mappings in the column mapping field in the properties pane. Separate the column mapping with a colon, and separate multiple mappings with a comma, for example "
SourceColumn1:TargetColumn1,SourceColumn2:TargetColumn2
" . Alternatively, click the Map Columns button in the Bulk Copy action to provide column mappings.
Specify the following required properties:
  •  
    Batch Size
    Specifies how many rows are sent to the target database at once.
  •  
    Destination Table Name
    Specifies the table name to which the data needs to be transferred.
  •  
    InDataReader
    Specifies the IDataReader variable used in the OutDataReader property of Data Reader Activity.
  •  
    Service Name (Oracle Only)
    Specifies the service name of target database.
  •  
    Username/Password
    Enter valid credentials to connect to the target database.
TDM Data Subset provides an accelerator for large databases composed of many tables. For more information, see Javelin Example: Subset Bulk Copy.
InsertDataTableActivity
Reads a data table from a Data-source and inserts it into a Target table in a Database. Doesn't use the bulk copy protocol. Specify the following required properties:
  •  
    Datasource
    Specify the DataSource segment of the DB connection string, for Oracle this is just the ServerName see: https://docs.microsoft.com/en-us/sql/reporting-services/report-data/data-connections-data-sources-and-connection-strings-report-builder-and-ssrs for details about other DB types.
  •  
    InDataReader
    Specifies the IDataReader variable that holds the rows to be copied to the target.
  •  
    UserId/Password
    Enter valid credentials to connect to the target database.
  •  
    ProviderName
    Specifies the DataSourceAttribute.ProviderInvariantName property. For Sql Server this is system.data.sqlclient, for Oracle Oracle.DataAccess.Client or system.data.oracleclient
  •  
    TableName
    Name of the destination table in the target database.
Examples: 
Bulk Copy Action API Reference
The bulk copy utility in Javelin is a fast method of moving data from one database to another, or between database types.
Note that in connection details related properties, you can either pass a full connection string or pass the connection field values separately (like server, username, password, etc). One of these 2 ways is mandatory and the other would be optional. In the mandatory fields, this is listed as "Y -- or use connection string" or "Y -- or configure individual values".
DataReader
Reads data from ODBC or OLEDB connection.
Property Name
Mandatory
DataType
Description
AdditionalConnectionParameters(IN)
N
string
You can connect to other ODBC data sources through additional connection parameters
Children
N
collectionactivity
Ignore this property. It's auto managed and adds children into collection which are dropped inside DataReader activity
CommandTimeout(IN)
N
int
timeout for executing the command
ContinueOnError(IN)
N
Boolean
If the step encounters an error, should the workflow move on or stop. Set this to True if you want it to continue on error.
Database(IN)
string
Database name
DisplayName
N
string
Name or brief description of the activity that you perform
IntegratedSecurity
N
Boolean
Dependent on database configuration
OdbcConnectionString(IN)
Y -- or configure individual values
string
ODBC full connection string
OleDbConnectionString(IN)
Y -- or configure individual values
string
OLE DB full connection string
OutDataReader(OUT)
Y
DataReader
Contains the resultant DataReader
Password(IN)
Y -- or use connection string
string
Password
Provider(IN)
Y
string
Provider type – sqlserver, oracle,etc
Query(IN)
Y -- or use connection string
string
Query to execute on the database
Schema(IN)
Y -- or use connection string
string
Database schema
Server(IN)
Y -- or use connection string
string
Database server name
ServiceName(IN)
N
string
Dependent on database type
Timeout(IN)
N
int
Duration of the timeout in seconds
Username(IN)
Y
string
Username
DB2 DataReader - READ
Read data from DB2 connection
Property Name
Mandatory
DataType
Description
AdditionalConnectionParameters(IN)
N
string
You can connect to other ODBC data sources through additional connection parameters. (not recommended)
Children
collectionactivity
Ignore this property. It's auto managed and adds children into collection which are dropped inside DataReader activity
CommandTimeout(IN)
N
int
Not needed. Can be left empty
ConnectionString(IN)
Y -- or configure individual values
string
DB2 connection string
ContinueOnError(IN)
N
Boolean
If the step encounters an error, should the workflow move on or stop. Set this to True if you want it to continue on error.
DatabaseName(IN)
Y
string
Database name
DisplayName
N
string
Name or brief description of the activity that you perform
OutDataReader(OUT)
Y
DataReader
Contains the resultant DataReader
Password(IN)
Y -- or use connection string
string
Password
Provider(IN)
Y -- or use connection string
string
Database type
Query(IN)
Y
string
Query to execute on database
Server(IN)
Y -- or use connection string
string
Database server
Timeout(IN)
N
int
Duration of the timeout in seconds
Username(IN)
Y -- or use connection string
string
Username
DB2 Bulk Copy DataReader – WRITE
Writes data into DB2
Property Name
Mandatory
DataType
Description
AdditionalConnectionParameters(IN)
N
string
You can connect to other ODBC data sources through additional connection parameters. (not recommended)
BulkCopyTimeout(IN)
Y
int
Duration of the timeout for the bulk copy activity, in seconds
ColumnMappings(IN)
N
string
If source and target have different column names, then you need to specify col mappings like Sourcecol1:targetcol1,sourcecol2:targetcol2. Otherwise not needed.
ContinueOnError(IN)
N
Boolean
If the step encounters an error, should the workflow move on or stop. Set this to True if you want it to continue on error.
Database(IN)
Y
string
Target database name
DefinitionTableName(IN)
Y
string
Target table within the database
DisplayName(IN)
N
string
Name or brief description of the activity that you perform
InDataReader(IN)
Y
DataReader
DataReader that contains data from source
IntegratedSecurity(IN)
N
Boolean
Dependent on database configuration
NotifyAfter(IN)
N
int
Notify after n number of rows are copied, like a log.
OutRowsCopied(OUT)
N
int
Contains the number of rows copied in the target
Password(IN)
Y
string
Password
Schema(IN)
Y
string
Schema
Server(IN)
Y
string
Server
Timeout(IN)
N
int
Duration of the timeout in seconds
TrackRecordsCount(IN)
N
Boolean
Track the number of records which are inserted during bulk copy process. Note: Setting this option to true slows down the bulk copy performance.
UserName(IN)
Y
string
Username
Oracle Bulk Copy DataReader
Writes data into Oracle database.
Property Name
Mandatory
DataType
Description
AdditionalConnectionParameters(IN)
N
string
You can connect to other ODBC data sources through additional connection parameters. (not recommended)
AvoidOutOfMemoryIssue(IN)
N
Boolean
Check avoid out of memory issue (y/n). Sent batch size to smaller numbers for this
BatchSize(IN)
N
int
How many records to copy at a time. This option works in conjunction with setting the AvoidOutOfMemoryIssue property = true.
BulkCopyTimeout(IN)
Y
int
Duration of the timeout for the bulk copy activity, in seconds
ColumnMappings(IN)
N
string
If source and target have different column names, then you need to specify col mappings like Sourcecol1:targetcol1,sourcecol2:targetcol2. Otherwise not needed.
ContinueOnError(IN)
N
Boolean
If the step encounters an error, should the workflow move on or stop. Set this to True if you want it to continue on error.
DestinationTableName(IN)
Y
string
Target table within the database
DisplayName(IN)
N
string
Name or brief description of the activity that you perform
InDataReader(IN)
Y
DataReader
DataReader contains data from source
NotifyAfter(IN)
N
int
Notify after n number of rows are copied, like a log
OutRowsCopied(OUT)
N
int
Contains the number of rows copied in the target
Password(IN)
Y
string
Password
Server(IN)
Y
string
Server
ServiceName(IN)
Y
string
Service name
Timeout(IN)
N
int
Duration of the timeout in seconds
TrackRecordsCount(IN)
N
Boolean
Tracks the number of records which are inserted during bulk copy process. Note: Setting this option to true slows down the bulk copy performance.
UserName(IN)
Y
string
Username
SQL Bulk Copy DataReader
Writes data in SQL Server database.
Property Name
Mandatory
DataType
Description
AdditionalConnectionParameters(IN)
N
string
You can connect to other ODBC data sources through additional connection parameters. (not recommended)
BatchSize(IN)
N
int
How many records to copy at a time. This option works in conjunction with setting the AvoidOutOfMemoryIssue property = true.
BulkCopyTimeout(IN)
Y
int
Duration of the timeout for the bulk copy activity, in seconds
ColumnMappings(IN)
N
string
If source and target have differecnt column names, then you need to specify col mappings like Sourcecol1:targetcol1,sourcecol2:targetcol2. Otherwise not needed.
ContinueOnError(IN)
N
Boolean
If the step encounters an error, should the workflow move on or stop. Set this to True if you want it to continue on error.
Database(IN)
Y
string
Database name
DestinationTableName(IN)
Y
string
Target table within the database
DisplayName(IN)
N
string
Name or brief description of the activity that you perform
InDataReader(IN)
Y
DataReader
DataReader that contains data from source
IntegratedSecurity
N
Boolean
Dependent on database configuration
KeepIdentity(IN)
N
Boolean
While bulk insert if identify values should be used as is, otherwise new values are generated for identify columns.
NotifyAfter(IN)
N
int
Notify after n number of rows are copied, like a log.
OutRowsCopied(OUT)
N
int
Contains the number of rows copied in the target
Password(IN)
Y
string
Password
Schema(IN)
Y
string
Schema
Server(IN)
Y
string
Server name
Timeout(IN)
N
int
Duration of the timeout in seconds
UserName(IN)
Y
string
Username
Teradata Insert Data DataReader
Write data into Teradata database.
Property Name
Mandatory
DataType
Description
AdditionalConnectionParameters(IN)
N
string
You can connect to other ODBC data sources through additional connection parameters. (not recommended)
ContinueOnError(IN)
N
Boolean
If the step encounters an error, should the workflow move on or stop. Set this to True if you want it to continue on error.
DataSource(IN)
Y
string
Data source name
DisplayName(IN)
N
string
Name or brief description of the activity that you perform
InDataReader(IN)
Y
DataReader
DataReader contains data from source
IntegratedSecurity
N
Boolean
Dependent on database configuration
OutRowsCopied(OUT)
N
int
Contains the number of rows copied in the target
Password(IN)
Y
string
Password
RecordsToLoadInMemory(IN)
N
int
Number of records to load to avoid out of memory issues
TableName(IN)
Y
string
Table name to insert data
Timeout(IN)
N
int
Duration of the timeout in seconds
UpdateBatchSize(IN)
N
int
Number of commands to run in a batch
UserID(IN)
Y
string
Username