[MAPREDUCE-6246] DBOutputFormat.java appending extra semicolon to query which is incompatible with DB2

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.4.1
• Fix Version/s: 3.0.0-beta1, 2.8.2
• Component/s: mrv1, mrv2

Environment

OS: RHEL 5.x, RHEL 6.x, SLES 11.x
Platform: xSeries, pSeries
Browser: Firefox, IE
Security Settings: No Security, Flat file, LDAP, PAM
File System: HDFS, GPFS FPO

• Hadoop Flags: Reviewed
• Assignee: Gergely Novák
• Reporter: ramtin
• Due: 10/Feb/15
• Created: 09/Feb/15 18:54
• Updated: 07/Jul/17 21:25
• Resolved: 07/Jul/17 21:25

Description

DBoutputformat is used for writing output of mapreduce jobs to the database and when used with db2 jdbc drivers it fails with following error

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;,COUNT) VALUES (?,?);END-OF-STATEMENT, DRIVER=4.16.53 at com.ibm.db2.jcc.am.fd.a(fd.java:739) at com.ibm.db2.jcc.am.fd.a(fd.java:60) at com.ibm.db2.jcc.am.fd.a(fd.java:127)

In DBOutputFormat class there is constructQuery method that generates "INSERT INTO" statement with semicolon(";") at the end.

Semicolon is ANSI SQL-92 standard character for a statement terminator but this feature is disabled(OFF) as a default settings in IBM DB2.

Although by using -t we can turn it ON for db2. (http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0010410.html?cp=SSEPGG_9.7.0%2F3-6-2-0-2). But there are some products that already built on top of this default setting (OFF) so by turning ON this feature make them error prone.