Versions Compared

Version Old Version 5 New Version Current
Changes made by

Former user

wiki

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


Image Added


NOTE: for the activities, IBM DB2 V10.5 is used.

 

 


Connect to DB2 SQL

This activity connects to a DB2 SQL Host. The Output of this activity should be used as input for all Connection parameters used for activities placed under DB2 SQL category.

...

  • Database (text box editor type) (type: string): the database name to connect to.
  • Instance (text box editor type) (type: string): the database name to connect to. A DB2 instance is an environment in which you store data and run applications. In case of none defined, the field can remain empty (as seen in the screenshot above).
  • Password (password editor type) (type: string): the password for the provided username.
  • Password Binding (text box editor type) (type: string): bind to a variable containing the encrypted password for the DB2 server. (This parameter is used only when the user wants to send an encrypted password from outside the taskworkflow, for example Flowster Portal).
  • Port (text box editor type) (type: string): the database port to connect to. The default port is 50000, created when defining a new database.
  • Server Name (text box editor type) (type: string): the name or IP of the DB2 Server to connect to.
  • Username (text box editor type) (type: string): the the username of the DB2 Server.

...

  • Connection (type: object): outputs a DB2 connection. The Output of this activity should be used as input for all Connection parameters used for activities placed under DB2 category.
 

 

 

 

 





Disconnect from DB2 SQL

This activity disconnects from a DB2 SQL Host.

Activity Parameters

The Disconnect from Db2 DB2 SQL activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • Connection (text box editor type) (type: object): the output from a Connect to OracleSQL DB2 SQL activity.

 

 

 






Add User Access to Table

This activity adds privileges for an user to a database table.

...

The Add User Access to Table activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • Connection (text box editor type) (type: object): the connection object that was obtained as output from a Connect To OracleSQL DB2 SQL activity.
  • Table Name Hostname (text box editor type) (type: string): the host name that the user can connect from.
  • Table (text box editor type) (type: string): the database table that the rights will be granted on. This activity will grant permissions per table.
  • UserNameForAccess (text box editor type) (type: string): the name of the user that will receive permissions.
 

The Read Only Output variable are the possible output values that the activity will provide:

  • Output (type: boolean): outputs True or False if the execution was successful or not.

...


Execution: the activity will search for the user and for the table. If found, will attempt to grant permissions over the table to the provided user:

Image RemovedImage Added

 

 






Delete Row(s) from Table

This activity deletes Row(s) from an Oracle a DB2 Server database table.

Activity Parameters

The Delete Row(s) from Table activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • Table (text box editor type) (type: string): the table name where the row(s) will be deleted.
  • Where Statement (text box editor type) (type: string): the Where statement to filter the row(s) that will be deleted. For example: ID>2, where all rows with an ID higher than 2 will be deleted.

Execution: the activity will delete all the rows from a table according to the given Where statement:

  • the initial table content:
    Image Removed
    Image Added

  • the table content after deletion:
    Image Removed
 

  • Image Added




 

 

 

Insert Row(s) into Table

This activity inserts Row(s) on an Oracle a DB2 Server database table.

Activity Parameters

The Insert Row(s) into Table activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • Fields (text box editor type) (type: string): the column headers that you want to be returned with the select command. If you enter more than one you should separate them with ','. For example: ID,Name,Surname,Company.
  • Table (text box editor type) (type: string): the table name where the row(s) will be inserted.
  • Values (text box editor type) (type: string): the values for specified fields in Fields parameter. If you enter more than one you should separate them with ','. If you have more values each should be placed in the same order as you add the fields.For example: 3,May,James,Amazon Prime.

...

 

Execution: the activity will insert the new row into the selected table. In the database, the row can be seen with the ID 3:

 

Image Removed

 

 

 

 


Image Added





Remove User Access from Database

...

The Remove User Access from Database activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • Table Name (text box editor type) (type: string): the database table where the rights will be removed from.
  • Username to be Removed (text box editor type) (type: string): the name of the user that will have denied permissions.
 

The Read Only Output variable are the possible output values that the activity will provide:

  • Output (type: boolean): outputs True or False if the execution was successful or not.

 

 

 

 

 

 







Run query

This activity runs a query on an Oracle a DB2 Server database.

Activity Parameters

The Run Query activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • Query (text box editor type) (type: string): the SQL query string. For example: "SELECT Name, Company FROM FLOWSTERNAMESs NAMES where ID>1 and ID<4 order by name ASC"
    Image Removed

    Image Added

  • Script (text box editor type) (type: string): the query content if a binding to a variable is used ($using). If set and if the $using:variable syntax is used, this parameter will take precedence over the Query parameter. For example, the $using:DemoString syntax will take the value stored in the DemoString variable and will execute it:

    Image Added

The Read Only Output variable are the possible output values that the activity will provide:

...

 

Execution: In the Tracking Data there can be seen the returned results:

Image RemovedImage Added

 


The same result can be seen when executing the same SQL Select query from Oracle Application Express:

Image Removed

 

 

 

 DB2 (IBM Data Studio was used for the examples):

Image Added





Select Row(s) from Table

This activity selects and returns Row(s) from an Oracle a DB2 Server database table.

Activity Parameters

The Select Row(s) from Table activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • Distinct (combo box control)  (type: string): whether the returned values should be distinct or not.
  • Fields (text box editor type) (type: string): the column headers that you want to be returned with the select command. If you enter more than one you should separate them with ','. If all fields needs to be returned, then just use "*". The " * " has the same role as used in the SQL select all statement: select * from table
  • Order By (text box editor type) (type: string): the column header that you want to result to be ordered by. For example, if "ID" is inserted, then the values returned will be ordered depending on the value set in the ID column. If no value is provided, the activity will return the values in the same order as taken from the table. 
  • Order by Direction (combo box control) (type: string): choose to order the resulted rows in Ascending or Descending direction. It will be ignored if Order By is not set.
  • Table (text box editor type) (type: string): the table name on which to run the select.
  • WHERE (text box editor type) (type: string): the Where statement. For example: ID>2, where ID is a column header.

...


The Read Only Output variable are the possible output values that the activity will provide:

  • Output (type: collection<collection<string>>): outputs a collection of collections of strings containing the selected rows.To use the elements of the collection, use Get Element From Collection activity.
  • Output Columns (type: int32): outputs the number of columns returned.
  • Output Rows (type: int32): outputs the number of rows returned.
 

 


A more complex example, with fields, orders and statements can be seen in the image below:

Image RemovedImage Added

Execution: In the Tracking Data there can be seen the returned results:

Image Removed

 Image Added


The same result can be seen when executing the same SQL Select query from Oracle Application ExpressDB2 (IBM Data Studio was used for the examples):

Image RemovedImage Added