Syntax Errors

When you execute FOPFAD2P, FOPD2EXP, or FOPD2EXE, a syntax error may occur. When a syntax error is encountered, Access displays a message including the DB2® return code and messages. Additional information can be displayed.

Example

The following series of figures demonstrates handling a syntax error when FOPFAD2P is executed. (This sequence applies to FOPD2EXP and FOPD2EXE also.)

When a syntax error is encountered, a message is displayed, as shown in the following panel.

Figure 1. DB2 SQL Syntax Error Detected by FOPFAD2P


-------------------------- IBM's ACCESS for DB2 ---------------------
COMMAND ===>

An error occurred executing the extracted SQL statement shown below.
The DB2 return code/message is:
       DSNT408I SQLCODE = -204, ERROR:  FOPDEMO.@@@TOMERS IS AN UNDEFINED
                NAME

Enter HELP for more information, or END to return to the ISPF/PDF editor.

SELECT X.CUST_ID, X.CUSTNAME, X.STATE, Y.ORDER_ID, Y.ORDER_SALESMAN, 
    X.YTD_SALES, Y.ORDER_DATE, X.SALESMAN_ID
  FROM FOPDEMO.@@@TOMERS X, FOPDEMO.ORDERS Y 
  WHERE X.YTD_SALES > ? and Y.ORDER_DATE > ?
        AND Y.ORDER_SALESMAN = ?
  ORDER BY X.STATE

Note the question mark to represent the location of the host variables in the SQL statement.

You can use END to return to the ISPF editor or HELP to display the DB2 SQL Error panel, as shown in the following panel.

Figure 2. DB2 SQL Error Panel


-----------------------------------DB2 SQL Error-------------------------------
COMMAND ===>

INSTRUCTIONS:       

ACCESS for DB2 Encountered the SQL Error Described Below

 DSNT408I SQLCODE = -204, ERROR:  FOPDEMO.@@@TOMERS IS AN UNDEFINED
                NAME
 DSNT415I SQLERRP = DSNHPA SQL PROCEDURE DETECTING ERROR
 DSNT416I SQLERRD = 0 0 0 1 166 0 SQL DIAGNOSTIC INFORMATION
 DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
          X'000000A6' X'00000000' SQL DIAGNOSTIC INFORMATION

Extracted SQL is Invalid -- EXECUTE Prepare Failed
Enter SQL Command to View the SQL Statement in Error

The DB2 SQL Error panel contains the complete error messages generated by DB2.

Use END to return to the ISPF edit session. Edit the embedded SQL statement, re-select the SQL statement, and re-execute the Access FOPFAD2P command.

Use the Access SQL command to display the SQL statement containing the error, as shown in the following panel.

Figure 3. Display SQL Containing Error


-----------------------------------DB2 SQL Error-------------------------------
COMMAND ===>

INSTRUCTIONS:       

ACCESS for DB2 Encountered the SQL Error with the Following
    SQL Statement.  Operation: PREPARE

SELECT X.CUST_ID, X.CUSTNAME, X.STATE, Y.ORDER_ID, Y.ORDER_SALESMAN, 
    X.YTD_SALES, Y.ORDER_DATE, X.SALESMAN_ID
  FROM FOPDEMO.@@@TOMERS X, FOPDEMO.ORDERS Y 
  WHERE X.YTD_SALES > ? and Y.ORDER_DATE > ?
       AND Y.ORDER_SALESMAN = ?
  ORDER BY X.STATE

Enter UP and DOWN Commands to Scroll the Statement
Enter END Command to Return to the SQLCA Display

From this panel, use END to return to the previous panel.