JR58027: DB2 ON Z/OS BULK LOAD FTP TRANSFER METHOD USING LFTP TOOL

APAR status

• Closed as program error.

Error description

• Implemented new data transfer option for DB2 on z/OS using FTP
  protocol with lftp tool. This allows the end user to define
  timeout, retry count and other parameters related to FTP
  transfer.
  

Local fix

• n/a
  

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

• RB31 PSY

     UP

• RB50 PSY

     UP