Database – Generic
The Database (Generic) job steps support database operations that include connecting, disconnecting, looping through returned data (ForEachRow), and issuing a SQL Block.

This job step allows you to connect to a database and establish a handle. That handle can then be passed to many of the other database job steps. The advantage of this approach is that you can perform database-to-database operations concurrently.
Platforms Supported
Windows.
Software Pre-Requisites
Design and Windows: OLE/DB drivers are used as specified. For Oracle: Oracle .Net Provider version 2.102.2.20 (for 32-bit) and 2.102.3 (for 64-bit). For SQL: Any supported MS SQL-Server OLE/DB drivers.
Job Step Properties
ConnectionString – Windows: This property requires a Microsoft UDL connection string. To assist in building the connection string you can click on the Helper button to request the Microsoft UDL Editor and then complete the dialogs that appear. When you click OK, the connection string will appear in this property. This property also supports ActiveBatch variables (as in the example above which is referencing the Job Scheduler’s own database).
UserAccount – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the target database and tables for the SELECT statement to work properly.
Timeout – This property indicates the amount of time, in seconds, that the SELECT query must complete before it will timeout. The default value is 30 seconds.
Return Job Step
ConnectionHandle – This property represents the connection handle to the database. Use the Disconnect job step to disconnect from the database.

This job step is used with Connect to disconnect from an established connection. See previous Connect job step for example usage.
Platforms Supported
Windows.
Software Pre-Requisites
Design and Windows: OLE/DB drivers are used as specified. For Oracle: Oracle .Net Provider version 2.102.2.20 (for 32-bit) and 2.102.3 (for 64-bit). For SQL: Any supported MS SQL-Server OLE/DB drivers.
Job Step Properties
Connection Handle – This property represents the established connection handle to the database you wish to disconnect.

This job step allows you to iterate through a resultant record set. Each returned row can then be used in a nested job step. Iteration job steps can be nested as well (meaning you can have more than one active job step iteration at a time; similar to having more than one FOR loop at a time).
Platforms Supported
Windows, UNIX-based.
Software Pre-Requisites
Design and Windows: OLE/DB drivers are used as specified. For Oracle: Oracle .Net Provider version 2.102.2.20 (for 32-bit) and 2.102.3 (for 64-bit). For SQL: Any supported MS SQL-Server OLE/DB drivers.
UNIX: JDBC drivers are used as specified.
Job Step Properties
Connection Information – This set of properties indicates how the job step is to connect to the database.
ConnectionString
-
Connection String
-
Windows: This property requires a Microsoft UDL connection string. To assist in building the connection string you can click on the Helper button to request the Microsoft UDL Editor and then complete the dialogs that appear. When you click OK, the connection string will appear in this property.
-
UNIX: This property requires a JDBC connection URL. To assist in building the connection URL you can click on the Helper button to request the JDBC Editor and then complete the dialog that appears. When you click OK, the connection URL will appear in this property.
-
This property also supports ActiveBatch variables (as in the example above which is referencing the Job Scheduler’s own database).
-
Credentials – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the target database and tables for the SELECT statement to work properly.
-
-
Handle – Windows: This property represents a ConnectionHandle from an earlier successful Connect job step.
CommandText – This property must contain a valid SELECT statement. The SELECT statement, in addition to being properly formatted, must consider the database and database software being used in terms of what is supported. For example, the SELECT statement differs in functionality between SQL Server and Oracle.
Timeout – This property indicates the amount of time, in seconds, that the SELECT query must complete before it will timeout. The default value is 30 seconds.
ContextName – This property requires a unique name that will serve as the Execution Variable for this iteration. The Execution Variable will contain one (1) row of data (based on your SELECT statement). Each field that is returned in the row is accessible via the following syntax: %{ContextName.field}. ContextName’s scope is limited to those job steps within the iteration. In the above example, the job step Log simply writes a data value to the job’s log file. Note %{Value.hostname} references the ContextName variable Value and the single field hostname that is returned.

This job step allows you to execute a SQL block of code using any database software that is supported by either OLE/DB for Microsoft Windows or JDBC for UNIX-based systems. The only real difference between Generic SQL Block and T-SQL/PL-SQL are the built-in OLE/DB or JDBC drivers used and the overall presentation of input parameters (for example, Oracle’s Data Source versus SQL Server’s Server and Database).
Platforms Supported
Windows, UNIX-based.
Software Pre-Requisites
Windows: OLE/DB drivers are used as specified.
UNIX: JDBC drivers are used as specified (see Installation Manual for drivers tested).
Job Steps Properties
Connection Information – This set of properties indicates how the job step is to connect to the database.
ConnectionString
-
Connection String
-
Windows: This property requires a Microsoft UDL connection string. To assist in building the connection string you can click on the Helper button to request the Microsoft UDL Editor and then complete the dialogs that appear. When you click OK, the connection string will appear in this property.
-
UNIX: This property requires a JDBC connection URL. To assist in building the connection URL you can click on the Helper button to request the JDBC Editor and then complete the dialog that appears. When you click OK, the connection URL will appear in this property.
-
This property also supports ActiveBatch variables (as in the example above which is referencing the Job Scheduler’s own database).
-
Credentials – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the target database and tables for the SELECT statement to work properly.
-
-
Handle – Windows: This property represents a ConnectionHandle from an earlier successful Connect job step.
Block – This property represents the Generic SQL block of code. The content can be multi-line (clicking on the Helper button will cause an edit box to appear that will allow multi-line code entry or edit).
Timeout – This property indicates the amount of time, in seconds, that the code block must complete before it will timeout. The default value is 30 seconds (zero=infinite).
Credentials – The object path of a User Account object. Clicking on the “Helper” will cause a tree display of all ActiveBatch containers. You may then select a User Account object. The User Account credentials must have proper access to the target database. The credentials must be appropriate for the underlying database you will be accessing (i.e. database versus windows authentication).