Skip to content
CA Test Data Manager - 4.2
Documentation powered by DocOps

Automating Database Activities

Last update September 8, 2017

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:

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.

Example: AccessElementsOfDatarow.vwf

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:

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

Was this helpful?

Please log in to post comments.

  1. Andrew Chen
    2018-03-12 01:04

    I am looking for documentation on how to store encrypted password in variable, and in which I can store in a CSV file that could be read into variable.

    1. Sonika Sinha
      2018-03-12 03:18

      Hi Andrew, thanks for the question. I am consulting with the engineering team and will follow up when I have a clear answer.

      Regards, Sonika