Symptoms of SQL problems

Table 1 and Table 2 show some symptoms and possible causes of SQL problems. Table 1 shows problems that generate a reply beginning with 55x.
Table 1. SQL problems generating 55x replies (FTP Server)
Reply Output file Possible causes
Reply 551: Transfer aborted: SQL PREPARE/DESCRIBE failure The output file contains the SQL code and error message returned by the Db2® subsystem.
  • A syntax error in the SQL statement in the host file.
  • The time stamp in the load module is different from the BIND time stamp built from the DBRM (SQL code = -818). This occurs if a BIND was not done for the EZAFTPMQ DBRM that corresponds to the current load module, or if the server is not configured to use the correct Db2 plan name. If this is the problem, every SQL query submitted through the FTP server fails.
Reply 551: Transfer aborted: unsupported SQL statement No output is sent from the host.
  • The file type is SQL, but the host file being retrieved does not contain an SQL SELECT statement.
Reply 551: Transfer aborted: attempt to connect to db2name failed (code) No output is sent from the host.
  • The site db2name specifies a nonexistent Db2 subsystem.
  • The Db2 subsystem has not been started.
Reply 551: Transfer aborted: SQL not available. Attempt to open plan <planname> failed (DB2_reason_code). No output is sent from the host.
  • BIND was not done for the specified plan.
  • BIND was done for plan name other than EZAFTPMQ, but FTP.DATA does not contain a DB2PLAN statement to specify this planname.
  • User does not have execute privilege for the Db2 plan being used by the FTP server.
Reply 550: SQL query not available. Cannot load CAF routines. No output is sent from the host.
  • The DSNLOAD library is not in the link list or the FTP server STEPLIB.
Note: For more information about the messages, see z/OS Communications Server: IP and SNA Codes.
Table 2 shows other SQL problems.
Table 2. Other SQL problems (FTP Server)
Problem Possible causes
Output file contains only the SQL SELECT statement.
  • The file type is SEQ, rather than SQL. If the file type is SEQ, a retrieve is done, but the host file is just sent back to the client. The query is not submitted to the Db2 subsystem.
  • The SELECT is for a VIEW for which the user ID does not have Db2 select privilege. The Db2 subsystem returns an empty table.
Client closes the connection because server is not responding.
  • The processing time needed by Db2 and FTP or both for the SQL query has exceeded the client’s time limit for send or receive.
  • An FTP server trace with the options FSC and SQL indicates the amount of SQL activity through FTP and the approximate time when each query was processed.