Topic
  • 9 replies
  • Latest Post - ‏2014-05-08T13:05:55Z by markevans
TuukkaIlomäki
TuukkaIlomäki
80 Posts

Pinned topic Retrieve SQL error

‏2014-05-06T08:22:55Z |

We have 6 identical RBD 9.0.1 installations connecting to iSeries V7R1 using default driver com.ibm.datatools.db2.iseries_3.2.700.v20131113_0832. Retriveve SQL works in all but one machine. Copying workspace and settings does not help. When I attempt to Retrieve SQL I get the following error:

IWN.VAL.4582.e 0/0 Retrieve SQL failed because table XXX.YYY is not defined in database or because the user ID lacks SELECT authority.

The same user ID and database connection is used in all installations. Nothing wrong with the ID or authorities. Ping succeeds fine Preferences -> EGL -> SQL Database Connections. Is there some setting I should check? There are also some unnecessary Derby  SQL connections, but  the interface does not have a delete button. Could they cause disturbance? This post mentions enabling trace. How can I do that?

  • ENO2010
    ENO2010
    136 Posts

    Re: Retrieve SQL error

    ‏2014-05-06T10:49:41Z  

    Hi,

     

    did you check the error messages in the log file located in the '.metadata" folder of your workspace

  • TuukkaIlomäki
    TuukkaIlomäki
    80 Posts

    Re: Retrieve SQL error

    ‏2014-05-06T12:14:43Z  
    • ENO2010
    • ‏2014-05-06T10:49:41Z

    Hi,

     

    did you check the error messages in the log file located in the '.metadata" folder of your workspace

    Good point. I get the following error in the log:

    !ENTRY org.eclipse.datatools.connectivity.sqm.core 4 4 2014-05-06 15:00:30.520
    !MESSAGE java.sql.SQLException
    !STACK 0
    java.sql.SQLException: [SQL0332] Merkkimuunto CCSID-tunnusten 0 ja 278 välillä ei ole mahdollista.
    

    In English the error means that conversion between CCSIDs 0 and 278 is not possible. We use CCSID 278 in our iSeries. EGL source files are typically Cp1252. What is baffling is that only on machine exhibits these issues.

  • markevans
    markevans
    3034 Posts

    Re: Retrieve SQL error

    ‏2014-05-06T13:51:20Z  

    Good point. I get the following error in the log:

    <pre dir="ltr">!ENTRY org.eclipse.datatools.connectivity.sqm.core 4 4 2014-05-06 15:00:30.520 !MESSAGE java.sql.SQLException !STACK 0 java.sql.SQLException: [SQL0332] Merkkimuunto CCSID-tunnusten 0 ja 278 välillä ei ole mahdollista. </pre>

    In English the error means that conversion between CCSIDs 0 and 278 is not possible. We use CCSID 278 in our iSeries. EGL source files are typically Cp1252. What is baffling is that only on machine exhibits these issues.

    Strange...

    Maybe something is set differently on the Windows machine (assuming using Windows). 

    Things to look at on a working and the non-working machine to see if they are different

    a.)  Got to command window and type in "chcp" (no quotes).  

    b.)  Values in the eclipse.ini (particularly if any "file.encoding" is specified

    c.)  Once RBD is started, go to: Help->About RBD->Installation Details->Configuration   - again check on encoding fields

    d.) Look at the properties on the connection to see if they are the same (JT400 is limited, so expect them to be).

    And

    Start up RBD with a -clean (if Windows 7 and using default install directories under Program files, this needs to be run "as Administrator).   Maybe some metadata is corrupted and the -clean usually resets this.

     

  • TuukkaIlomäki
    TuukkaIlomäki
    80 Posts

    Re: Retrieve SQL error

    ‏2014-05-07T07:00:18Z  
    • markevans
    • ‏2014-05-06T13:51:20Z

    Strange...

    Maybe something is set differently on the Windows machine (assuming using Windows). 

    Things to look at on a working and the non-working machine to see if they are different

    a.)  Got to command window and type in "chcp" (no quotes).  

    b.)  Values in the eclipse.ini (particularly if any "file.encoding" is specified

    c.)  Once RBD is started, go to: Help->About RBD->Installation Details->Configuration   - again check on encoding fields

    d.) Look at the properties on the connection to see if they are the same (JT400 is limited, so expect them to be).

    And

    Start up RBD with a -clean (if Windows 7 and using default install directories under Program files, this needs to be run "as Administrator).   Maybe some metadata is corrupted and the -clean usually resets this.

     

    Strange indeed. I observed that the driver definition had property sort=table. (Probably due to us trying to fix sort order som years ago). Removing that option removed the error. However, the result is now that operation succeeds but fields are not fetched. The only thing that happens is that attribute fieldsMatchColumns = yes is added. Nothing is written to the log.

    Starting with -clean does not help. Creating a new workspace and new connection do not help. The next step is probably wiping the machine clean and reinstalling software but as that takes quite a few hours (and does not add any understanding) I would rather not do it.

  • markevans
    markevans
    3034 Posts

    Re: Retrieve SQL error

    ‏2014-05-07T14:11:38Z  

    Strange indeed. I observed that the driver definition had property sort=table. (Probably due to us trying to fix sort order som years ago). Removing that option removed the error. However, the result is now that operation succeeds but fields are not fetched. The only thing that happens is that attribute fieldsMatchColumns = yes is added. Nothing is written to the log.

    Starting with -clean does not help. Creating a new workspace and new connection do not help. The next step is probably wiping the machine clean and reinstalling software but as that takes quite a few hours (and does not add any understanding) I would rather not do it.

    Are you using the DB2 JDBC drivers or the JT400 JDBC drivers? 

    Thanks.

  • TuukkaIlomäki
    TuukkaIlomäki
    80 Posts

    Re: Retrieve SQL error

    ‏2014-05-08T05:05:20Z  
    • markevans
    • ‏2014-05-07T14:11:38Z

    Are you using the DB2 JDBC drivers or the JT400 JDBC drivers? 

    Thanks.

    We use the latter, the connection editor names it as AS/400 Toolbox for Java. We have very restricted ODBC access to our iSeries and I would not want to change that.

  • markevans
    markevans
    3034 Posts

    Re: Retrieve SQL error

    ‏2014-05-08T11:35:48Z  

    We use the latter, the connection editor names it as AS/400 Toolbox for Java. We have very restricted ODBC access to our iSeries and I would not want to change that.

    Thanks.  Was not suggesting to change it.  Just gathering info to see if I can determine anything and which driver enters into it.

  • TuukkaIlomäki
    TuukkaIlomäki
    80 Posts

    Re: Retrieve SQL error

    ‏2014-05-08T12:59:57Z  
    • markevans
    • ‏2014-05-08T11:35:48Z

    Thanks.  Was not suggesting to change it.  Just gathering info to see if I can determine anything and which driver enters into it.

    In a desperate move, I upgraded the problematic machine from 9.0 to RBD 9.0.1. Retrieve SQL suddenly works just as advertised.

    I do not think that there is any relevant difference between 9.0 and 9.0.1 since we used to use 9.0 successfully (and still have one machine waiting to be updated). Probably reinstalling 9.0 would have solved the issue. Why this happened in the first place remains a mystery.

    In any case, thanks to all for suggesting solutions. I learned again quite a bit about the internal life of RBD.

  • markevans
    markevans
    3034 Posts

    Re: Retrieve SQL error

    ‏2014-05-08T13:05:55Z  

    In a desperate move, I upgraded the problematic machine from 9.0 to RBD 9.0.1. Retrieve SQL suddenly works just as advertised.

    I do not think that there is any relevant difference between 9.0 and 9.0.1 since we used to use 9.0 successfully (and still have one machine waiting to be updated). Probably reinstalling 9.0 would have solved the issue. Why this happened in the first place remains a mystery.

    In any case, thanks to all for suggesting solutions. I learned again quite a bit about the internal life of RBD.

    Glad it resolved it as well and have no explanation as I agree those two levels are very close and nothing that I know of changed in this area.

    Again, maybe something in the plugins needed to be re-cached...but would have thought the -clean at startup would have done this.

    thanks for letting us know what resolved it.