PI52652: A SIMPLE WARNING FROM DB2 ABOUT A SEMI-COLON AT THE END OF THE SQL STATEMENT RESULTS IN A MAP FAILURE.

APAR status

• Closed as program error.

Error description

• Testing with a simple map doing a select from a table.
  
  When the JCL has an input override like this:
  
  -ID1 '-STMT SELECT * FROM TEST1'
  
  ...the map completes successfully.
  
  Hoeever, when it is changed to:
  
  -ID1 '-STMT SELECT * FROM TEST1;'    <--- (note the semi colon
                                               at the end)
  
  ...then DB2 issues a warning, and the map fails.  Audit shows:
  
  <ExecutionSummary MapStatus="Error" mapreturn="75"
  ElapsedSec="-0.7993" BurstRestartCount="0">
     <Message>Adapter failed to get data on input</Message>
     <CommandLine>MAPTORUN -AE -WH -TIO -ID1 '-STMT SELECT * FROM
  TEST1;' -OF1 OUTPUT</CommandLine>
  
  <SourceReport   card="1"   adapter="DB"   bytes="0"
  adapterreturn="-1006">
     <Message>Failed to prepare the SQL statement</Message>
     <Settings>0006106D4EF0</Settings>
  </SourceReport>
  
  JOBOUT shows:
  
   Starting a database unload...
   Query      : SELECT * FROM TEST1;
   Query size : 20
   Context: Input Card, Transaction scope: Map
   On Failure: Rollback, On Success: Keep
   TRACE command specified, file: //DD:DBTRACE
   DBS_ExecuteStmtDirect: EXEC SQL PREPARE Q(0-7) FROM :sqlst
   SELECT * FROM TEST1;
   DSNT404I SQLCODE = 098, WARNING:  A DYNAMIC SQL STATEMENT ENDS
   WITH A
   SEMICOLON
   DSNT418I SQLSTATE   = 01568 SQLSTATE RETURN CODE
   DSNT415I SQLERRP    = DSNHTOKR SQL PROCEDURE DETECTING ERROR
   DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00002710'
                         X'42DB09EB'  X'00000013'  X'00000322'
  

Local fix

• WTXCQ - WTX00047862
  PB / PB
  As a workaround, avoid SQL statements that produce warnings in
  DB2
  

Problem summary

• Users Affected:
  WebSphere Transformation Extender customers who use DB2 adapter
  on z/OS
  
  Problem Description:
  A simple warning from DB2 about a semi-colon at the end of the
  SQL statement appears to be the cause of a map failure, when it
  should not fail.
  
  Platforms Affected:
  z/OS
  

Problem conclusion

• Allow DB2 continue when we have a warning during the Prepare of
  the SQL statement. This is the way ODBC DB2 access works.
  Previous WTX would fail.
  
  Applies to:
  8.3.0.6, 8.4.0.5, 8.4.1.3
  
  Fixed in the next service packs and releases.
  
  To obtain the fix for this APAR:
  
  To see if the next service pack or product release is available,
  check the WebSphere Transformation Extender Downloads page:
  http://www.ibm.com/support/entry/portal/product/other_software/t
  ransformation_extender
  
  If the service pack or product release is available, download it
  from PassportAdvantage:
  http://www.ibm.com/software/howtobuy/passportadvantage/pao_custo
  mers.htm
  
  If the service pack or product release is not available and you
  require the APAR fix immediately, request a Limited Availability
  Interim Fix (LAIF) by opening a PMR:
  https://www.ibm.com/support/servicerequest/Home.action
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  PI52910 PI52913 PI52914 PI52915 PI52916

Fix information

• Fixed component name

  WTX COMMAND SER

• Fixed component ID

  5655R9501

Applicable component levels

• R831 PSY

     UP

• R841 PSY

     UP