JR58252: DB2 CONNECTOR IN SERVER JOB BULK LOADED DECIMAL COLUMNS AS NULL IF ASC FILE TYPE USED.

APAR status

• Closed as program error.

Error description

• DB2 Connector on server canvas bulk loaded DECIMAL columns with
  NULL if ASC  file type is used. DB2 load from the fixed length
  ASC input file expects the  data at the position (that
  specifies the start and end column numbers) from  which to load
  the data. When the data length was single digit or less then
  the maximum precision, the garbage characters with the actual
  decimal data was mixed and the NULL was loaded instead.
  DataStage job log contained:
  Item #: 406
  Event ID: 405
  Timestamp: 2017-07-08 18:20:46
  Type: Info
  User Name: dsadm
  Message Id: IIS-CONN-DB2-00314
  Message: DB2CC_bulk_asc..DB2_Connector_2: The DB2 Load API call
  failed with SQLCODE 3,107.
  Load messages are stored in message file:loadMsgs.asc.
  DB2 load message file contained:
  SQL3123W  The field value in row "F0-1" and column "3" cannot
  be converted to a PACKED DECIMAL value.  A null was loaded.
  

Local fix

• Use DEL file type in server job or use parallel job with ASC
  file type.
  

Problem summary

• ****************************************************************
  USERS AFFECTED:
  Users having problems with LOAD to DB2 on z/OS caused by FTP
  disconnection/timeout. The problem is visible in the job logs as
  a failed FTP transfer.
  ****************************************************************
  PROBLEM DESCRIPTION:
  When LOADing data to DB2 for z/OS using DB2 Connector with FTP
  transfer option, the transfer could time out or the connection
  could be terminated.
  
  There can be several reasons for FTP transfer to fail, most of
  them not related to DB2 connector, for example the file
  transferred could be so big or the transfer so slow, that the
  connection could timeout.
  
  
  ****************************************************************
  RECOMMENDATION:
  Apply patch JR58027.
  ****************************************************************
  

Problem conclusion

• As a fix for the problem, usage of LFTP client was implemented
  in DB2 Connector. LFTP among many options allows to specify
  network timeout, retry count and reconnect interval.
  
  To leverage the various options available in LFTP, user needs to
  select "lftp" from the "Transfer type" combo box. All the LFTP
  options can be provided in the "Transfer command" input field.
  
  Example options:
  set ftp:catch-size true; set net:timeout 60; set net:max-retries
  10; set net:reconnect-interval-base 5; set
  net:reconnect-interval-max 20; set
  net:reconnect-interval-multiplier 1.5;
  
  Prerequisites for LFTP "Transfer type"
  - lftp tool installed / lftp(.exe) on PATH
  - lftp version >= 4.5.0 (supporting --env-password, allows
  taking password from LFTP_PASSWORD environment variable)
  
  Comparing to FTP "Transfer type", LFTP does not use pipes, it's
  implementation is similar to SFTP (also FTP on Windows
  platform). First the data is stored in a file, which is later
  sent to the DB2 z/OS host. This allows transfer resume.
  LFTP is not run interactively (like FTP and SFTP), it's run as a
  one command, DB2 Connectors just runs it and waits for the
  process to finish. For SFTP/FTP the Connector monitors the
  output and sends new commands when needed.
  

Temporary fix

• To avoid FTP transfer timeout or disconnect users can:
  - use SFTP transfer type
  - increase timeout setting on the server side
  - increase network throughput
  - limit network usage during LOAD
  - split one big LOAD into several smaller ones
  - rerun the job hoping the timeout won't occur
  

Comments

APAR Information

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

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

Fix information

• Fixed component name

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• RB50 PSY

     UP