IBM Support

An "SQL 418" error occurs when debugging an EGL get statement

Troubleshooting


Problem

An "SQL 418" error occurs in IBM® Rational® Business Developer while debugging or running an EGL [] get[] statement using a []#sql[] directive containing a call to the IBM DB2® []TIMESTAMP[] function. This technote explains this error's cause and suggests a couple of solutions.

Symptom

The following is a example of the error messages received when the problem occurs:


egl.io.sql.SQLException: EGL0504E GET: DB2 SQL error: SQLCODE: -418, SQLSTATE: 42610,
SQLERRMC: null[sqlstate:42610][sqlcode:-418]
EGL0002I The error occurred in the TestSQL program processing the main2 function.

Cause

This is a known problem when the TIMESTAMP function is used with Host variables, for example:


function main2()
        SysLib.writeStdout("start");
        rec MYTABLE;

        loc_DATE char(10);
        loc_DATE = "12.02.2009";
        get rec
                with #sql{
        select * from test.TABLE3
        WHERE COL4   > TIMESTAMP(:loc_DATE,'00.00.00')
      };
        SysLib.writeStdout("end, COL4=" + rec.COL4);
end

Diagnosing The Problem

Use the attached 72519.zip project interchange file to recreate the error.

Complete the following steps to test for the error.

  1. Start Rational Business Developer Extension 7.1.0.2
  2. Import the attached project. To import the attached project, do the following:
    1. Click Workspace > File > Import > Other > Project Interchange > Next > Browse.
    2. Select the 72519.zip file from download location
    3. Click All > Finish.
    4. Configure the DB2 libraries in according to location your file system.
      1. Click Workspace > Project(72519EGL).
      2. Right click Properties > Java Build Path > Libraries > db2jcc.jar, db2jcc_license_cisuz.jar.
    5. Build the project, if Build automatically is not switched on. Click Workspace > Project > Build Automatically.
    6. Test the EGL source by debugging. Click Workspace > Project(72519EGL) > EGL Source > test > TestSQL.egl. Right click Debug EGL Program.
    72519.zip

Resolving The Problem

To resolve the problem use one of the two following workarounds.

  • Use the prepare statement, for example:

prepare stmt1 from "select * from test.TABLE3  WHERE COL4  > TIMESTAMP('::loc_DATE::"', '00.00.00')";

get rec with stmt1;



OR
  • Use the EGL timestamp function instead of the DB2 timestamp function, for example:

loc_DATE timeStamp;


loc_DATE = dateTimeLib.timeStampValue("20090212000000");
get rec with #sql{ select * from test.TABLE3
   WHERE COL4   > :loc_Date};

[{"Product":{"code":"SSMQ79","label":"Rational Business Developer"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Debugger","Platform":[{"code":"PF033","label":"Windows"}],"Version":"7.1.0.2","Edition":"","Line of Business":{"code":"LOB35","label":"Mainframe SW"}}]

Document Information

Modified date:
02 August 2018

UID

swg21376221

Page Feedback