Introduction 

An action plugin that can efficiently export data from DB2 to be in Hydrator.  DB2 includes command line tools to export data  that can be utilized to perform this task.    

User Stories

• As a Hydrator User I want to export data from DB2 to be used in my hydrator Pipeline.
• As a Hydrator User I want a DB2 plugin that exports data efficiently using existing DB2 tools. 
• As a Hydrator User I want the export data capability of the DB2 plugin to be based on a sql query that I issue.  
• User should be able to specify credentials.
• Passwords should not be viewable in plain text from inside pipeline viewer or hydrator studio.  
• User should be able to specify DB2 Instance
• User should be able to specify either DB2Move or DB2 CLI.
• User should be able to specify location of DB2Move Utility or path to CLI.   
• User should be able to specify type of output.
• User should be able to specify location of output files.  
• User should know of connectivity errors, or malformed queries/output identifier.   

Example

Implementation Tips

• DB2 Inclues the DB2 Command Line Utility that can be used to export date: DB2 Docs
• Sample Export Query: db2 export to myfile.ixf of ixf messages msgs.txt select * from staff
• DB2 Includes the DB2Move utility which can operate at a lower granularity to bulk export tables of data: DB2 Docs

Design 

Design:

Input Json Format:

{
  "name": "DB2ExportAction",
  "type": "action",
  "properties": {
        "dbUserName": "cdap",
        "dbPassword": "****",
        "DB2InstanceHost": "localhost",
		"DB2InstancePort": "***",
		"DB2InstanceUserName": "cdap",
        "DB2InstancePassword": "****",
        "exportUtilityPath": "/opt/db2",
		"exportUtility": "Client"
        "db": "user",
        "outputType": "csv",
        "delimiter": ",",
        "outputPath": "/home/cdap/db2",
        "sqlQuery": "select * from table_with_90_columns"
   }
}

Properties:

• dbUserName: User name for database.

• dbPassword: Password for database.
• DB2InstanceHost: Hostname or IP of the DB2 instance.
• DB2InstancePort: Port for the DB2 instance.
• DB2InstanceUserName: User name for the DB2 instance.
• DB2InstancePassword: Password for the DB2 instance.
• exportUtilityPath: Path of DB2Move or DB CLI utility.
• exportUtility: Utility to use while exporting data. It can be DB2Move or DB CLI utility.
• db: Name of the database on which sql query needs to be executed.
• outputType: Type of the output. It may be Flat file or External table.
• delimiter: Delimiter needs to be specified which can be used while writing records in a file.
• outputPath: Path of the output files.
• sqlQuery: Sql query which needs to be executed on the specified db name to export the data.