Introduction 

A An action plugin that can efficiently export data from DB2 to be used as a source 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.