PN85359: C-FTP SERVER INTERMITTENTLY RETURNS A NULL INDICATION (DASHES) INSTEAD OF THE REAL DATA WHEN FILETYPE=SQL IS USED.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• This problem is intermittent and affects all ftp clients using
  filetype=sql. Once an sql query is issued that correctly
  returns a null value (dashes) any subsequent queries may
  incorrectly return null values when actual data is present.
  This problem affects all subsequent sessions and users.
  
  To reproduce this problem follow these steps:
  .
  1. Create an object in DB2 with the following statements:
  =========================================================
     CREATE TABLESPACE FTSFTP
            IN FTDTEST
            USING STOGROUP G01
            PRIQTY 50
            SECQTY 50
            LOCKSIZE ANY
            CLOSE YES
            BUFFERPOOL BP1;
     CREATE TABLE FTTFTP
     (
      PRJ          CHAR    (  8    ) NOT NULL ,
      HPHASE       CHAR    (  1    ) NOT NULL ,
      POS          CHAR    (  3    ) NOT NULL ,
      BEZ          CHAR    ( 50    ) NOT NULL ,
      ARBNR        CHAR    (  4    )          ,
      PLBEG        DATE              NOT NULL ,
      PLEND        DATE              NOT NULL ,
      PLMM         DECIMAL (  7  , 2 )        ,
      PLMKO        DECIMAL (  7  , 0 )
     ) IN FTDTEST.FTSFTP;
     =========================================================
  .
  2. Insert this data into the table:
     =========================================================
     INSERT INTO FTTFTP VALUES (
       'P1111111','H','PO1','TEST TEST 111','A111',
       '01.01.0001','01.01.0001', 111.11, 1111);
     INSERT INTO FTTFTP VALUES (
       'P2222222','H','PO2','TEST TEST 222',NULL,
       '02.02.0002','02.02.0002', 222.22, 2222);
     =========================================================
  .
  3. Setup the following two queries as PDS-Members for use
     with FTP/DB2:
  .
     QUERY 1:
     =========================================================
     SELECT * FROM U8SD101.FTTFTP
     =========================================================
  .
     QUERY 2:
     =========================================================
     SELECT * FROM SYSIBM.SYSDATABASE
     =========================================================
  .
  4. Establish an FTP-Session to C-FTP server using
     filetype=sql and issue the following commands:
  .
      site db2=DB2x  (your DB2 subsystem name)
      site filetype=SQL
      get '<your.pds.name(QUERY1))' <local.fil>
      quit
  .
               The result looks like this:
  .
  s--------+---------+------(...)----------+---------+-----(...
  SELECT * FROM U8SD101.FTTFTP
  h--------+---------+------(...)----------+---------+-----(...
  PRJ      HPHASE POS BEZ   (...)  ARBNR PLBEG      PLEND  (...
  d--------+---------+------(...)----------+---------+-----(...
  P1111111 H      PO1 TEST T(...)  A111  01.01.0001 01.01.0(...
  P2222222 H      PO2 TEST T(...)  ----- 02.02.0002 02.02.0(...
  .
     THIS RESULT IS CORRECT!
     -----------------------
     (except that there are 5 (five) dashes for ARBNR to signal
     the NULL-Value, and the definition is only a CHAR(4).
  .
  5. With a new session and any userid, issue QUERY2 through
     FTP/DB2. The result looks like this (partially):
  .
  s--------+---------+---------+---------+---------+---------+
  SELECT * FROM SYSIBM.SYSDATABASE
  h--------+---------+---------+---------+---------+---------+
  NAME     CREATOR  STGROUP  BPOOL      DBID IBMREQD CREATEDBY
  d--------+---------+---------+---------+---------+---------+
  DSNDB07  U057501  SYSDEFLT BP0      ------ N       U057501
  DSNDB04  SYSIBM   SYSDEFLT BP0      ------ Y       SYSIBM
  DSNDB06  SYSIBM                     ------ Y       SYSIBM
  DBDPTEST Q057501  G01      BP0      ------ N       Q057501
  DBDPT    U0LFC01  DBGPT01  BP0      ------ N       U0LFC01
  U0147D02 U057501  G01      BP0      ------ N       U057501
  DBEDB1   U034601  DBESG1   BP0      ------ N       U034601
  .
  >>  THIS IS INCORRECT! DBID in SYSIBM.SYSDATABASE does
  >>  not contain the NULL-VALUE in the column DBID, and
  >>  cannot as it is defined as "NOT NULL".
  >>
  >>  This behavior then carries on for all users and the
  >>  only remedy is to restart the FTP address space.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of MVS TCP/IP C-FTP server SQL     *
  *                 function.                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: During 'filetype=sql' processing, the   *
  *                      C-FTP server intermittently returns a   *
  *                      null indication (dashes) instead of     *
  *                      the real data.                          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Failing scenario is: user selects data from a table that has
  (at least) one column containing nulls rather than data values.
  This query executes successfully. The key to making the
  next query fail is that the LAST row retrieved for the first
  query must contain a null value instead of a data value.
  User then does a second select, this time from a table that
  has all columns that do not accept null values. However, the
  data the user receives back from the second query contains all
  null values in the column that the previous query had a null
  value in.
  For example, column 4 from the first query had 3 rows of data,
  the first two containing either data or null values and the
  third row contains a null value; then the user runs a second
  query. This time the table contains all data values. But the
  fourth column results will indicate it contains all nulls,
  no data values.
  Investigation showed that storage was obtained at the start
  of SQL statement processing to store the settings of the
  SQL indicator variables returned from DB2. At the end of the
  first query, this storage contained a null indicator setting
  for one column (legitimately). The storage was freed. At the
  start of the second query, storage was again obtained and
  happened to start at the same location as the previous storage.
  This time, DB2 received the query, saw that none of the columns
  were null-accepting so ignored the indicator variables
  passed to it. However, it still contained the settings of the
  indicator variables from the previous query. So when the C-FTP
  server read these indicator variable settings, it incorrectly
  interpreted one of the columns as containing nulls instead of
  actual data.
  

Problem conclusion

• The module EZAFTSMQ acquires a storage area to save SQL
  indicator variable settings from DB2. The code was modified
  to clear this storage area at the start of every SQL
  statement processed by the C-FTP server's SQL function.
  

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:

  UN92389 PN89322

Modules/Macros

• EZAFTSMD EZAFTSMQ
  

Fix information

• Fixed component name

  TCP/IP V3 MVS

• Fixed component ID

  5655HAL00

Applicable component levels

• R310 PSY UN92389

     UP96/08/09 P F608

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.