 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / December 2007where-Abfrage in SQL und AIX db2 Connect
Hi, folgende Konstellation: Ein z/OS-Rechner mit einer db2 v.8. Wenn ich mit User XY einen SQL-Befehl direkt auf die Datenbank absetze [code]db2 select BEISPIEL from TABELLE where SPALTE = 'TEXT' order by BEISPIEL[/code], dann bekomme ich wie gewünscht meine Ergebnisse aufgelistet. Ein zweiter Rechner im Netzwerk mit AIX als Betriebssystem und installiertem db2 Connect dient als Gateway für andere RTCL. Von diesem Rechner setze ich den gleichen SQL-Befehl auf die Datenbank ab: Fehler! [Leider habe ich die Fehlermeldung gerade nicht parat (bin schon im Wochenende ::)) ] Folgendes wurde schon getestet: [code]select BEISPIEL from TABELLE[/code] geht von dem AIX-Rechner. Des weiteren funktioniert auch ein [code]select SPALTE from TABELLE[/code] Der oben als Parameter angegebene Text existiert und wird mit dem select-Befehl ohne WHERE-Klausel angezeigt. Verschiedene Syntax mit " " und ' ' ohne Anführungszeichen ausprobiert - nix. Verschiedene andere Parameter aus der Spalte ausprobiert - nix. Leider habe ich nicht mehr ausprobieren können, ob eine where-Abfrage mit einem anderen Format außer Text (also eine Nummer) funktioniert. Sowohl auf dem AIX als auch direkt auf der Datenbank habe ich mit denselben Nutzer gearbeitet. Meine Fragen: 1.) Nur zum Verständnis: So ein Connect-Treiber leitet doch nur die Anfragen an die Datenbank weiter, die diese dann verarbeitet, oder?! Weshalb verarbeitet diese dann die direkten where-Abfragen - aber nicht die eingereichten? 2.) Wo muß ich nach was suchen? Danke für jeden Hinweis im voraus, Bernhard > Hi, > [quoted text clipped - 37 lines] > > Bernhard Bernhard, We need that error-code you can't remember... Your code looks harmless enough. Cheers Serge PS: To maximize the number of folks who can help you it pays to post in English. Bigger audience.  SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab Hi Serge, > PS: To maximize the number of folks who can help you it pays to post in Ooops, you're certainly right. I try to translate first and get that error message for you later: #### Hi, following situation setup: There's one z/OS-machine with an db2 v.8 on it. When I as User XY start a SQL-request [code]db2 select EXAMPLE from TABLE where COLUMN = 'TEXT' order by EXAMPLE[/code], I get the right return. Some data from that table in a couple of lines. This SQL-request works. But.... A second machine with AIX on it as OS and installed db2 Connect serves as gateway for other RTCL-clients. When I start the exactly same SQL-request from the AIX, I get an error message! I post this error messsage later, when I'm back at work. I tried the following: [code]select EXAMPLE from TABLE[/code] works from the AIX.[code]select COLUMN from TABLE[/code] works as well. The above given value TEXT does exist and is listed with the request above without the "where". I tried several syntaxes: " " and ' ' and with no quotes at all - nothing worked. I tried several other parameter - nothing. I missed to check, if a where-request with a number as value works. I worked with the same user XY on the AIX and direct on the database as well. My questions: 1.) Just to understand: Does a db2 Connect driver just pass the request? Or does it process it in any way? If it just passes the request, and the where-request gets processed. 2.) What do I have to look for? Thanks for any hints in advance. Regards, Bernhard Here is the error message. The message is in German, so I try to translate for you: SQL0206N "TEXT" is not valid in this context. SQLSTATE 42703. Thank you for any hints, where I have to look or what I have to look for. Bernhard Sorry for bumping, but I tried several things that need to be noticed. First I left out the ORDER BY to test if it is this parameter, but that doesn't look to be the case. Same error. I then put in the database-name and the tablename before the column-name in the where-request. [code]db2 select EXAMPLE from TABLE where DATABASE.TABLE.COLUMN = 'TEXT' order by EXAMPLE[/code] and [code]db2 select EXAMPLE from TABLE where TABLE.COLUMN = 'TEXT' order by EXAMPLE[/code] Same error. What I don't understand is, why I can fire up the where-request on the z/OS directly on the database? This works, from the AIX not. Can't see, what is going wrong there. The SQL is fairly easy and shouldnot be the problem, isn't it? It works directly on the database, why is it not passed correctly from the AIX? Or is this a problem of external user access rights? I have no clue anymore. Sorry for bumping, but I tried several things that need to be noticed. First I left out the ORDER BY to test if it is this parameter, but that doesn't look to be the case. Same error. I then put in the database-name and the tablename before the column-name in the where-request. [code]db2 select EXAMPLE from TABLE where DATABASE.TABLE.COLUMN = 'TEXT' order by EXAMPLE[/code] and [code]db2 select EXAMPLE from TABLE where TABLE.COLUMN = 'TEXT' order by EXAMPLE[/code] Same error. What I don't understand is, why I can fire up the where-request on the z/OS directly on the database? This works, from the AIX not. Can't see, what is going wrong there. The SQL is fairly easy and shouldnot be the problem, isn't it? It works directly on the database, why is it not passed correctly from the AIX? Or is this a problem of external user access rights? I have no clue anymore. [EDIT] Ok, I tried to get closer by playing with the parameter. I tried: [code]db2 select EXAMPLE from TABLE abc where abc.COLUMN = 'TEXT' order by EXAMPLE[/code] Didn't work. I then tried: [code]db2 select EXAMPLE from TABLE where COLUMN = COLUMN[/code] This works, because it is always true. I get the full list, without selection. What I want to try out is, if the WHERE is the problem, which is not the case (as it looks like). Are the quotes a problem? Or do I have to add the COLUMN into the select? Thanks. Sorry for bumping, but I tried several things that need to be noticed. First I left out the ORDER BY to test if it is this parameter, but that doesn't look to be the case. Same error. I then put in the database-name and the tablename before the column-name in the where-request. [code]db2 select EXAMPLE from TABLE where DATABASE.TABLE.COLUMN = 'TEXT' order by EXAMPLE[/code] and [code]db2 select EXAMPLE from TABLE where TABLE.COLUMN = 'TEXT' order by EXAMPLE[/code] Same error. What I don't understand is, why I can fire up the where-request on the z/OS directly on the database? This works, from the AIX not. Can't see, what is going wrong there. The SQL is fairly easy and shouldnot be the problem, isn't it? It works directly on the database, why is it not passed correctly from the AIX? Or is this a problem of external user access rights? I have no clue anymore. [EDIT] Ok, I tried to get closer by playing with the parameter. I tried: [code]db2 select EXAMPLE from TABLE abc where abc.COLUMN = 'TEXT' order by EXAMPLE[/code] Didn't work. I then tried: [code]db2 select EXAMPLE from TABLE where COLUMN = COLUMN[/code] This works, because it is always true. I get the full list, without selection. What I want to try out is, if the WHERE is the problem, which is not the case (as it looks like). Are the quotes a problem? Or do I have to add the COLUMN into the select? Thanks. [EDIT2] What I don't understand is, why the documentation of this error says, that the column is not found in the table specified. But TEXT is a parameter or value not a column! Why does the db2 Connect try to find a column named like the value? I could understand the error, if it would be something like " COLUMN is not a valid ...." but it says TEXT. (?) > Here is the error message. The message is in German, so I try to > translate for you: > > SQL0206N "TEXT" is not valid in this context. SQLSTATE 42703. > > Thank you for any hints, where I have to look or what I have to look for. You have become victim of your shell. The shell is stripping the single quote, so TEXT looks like a column rather than s string. Try this (note the double quotes bracing the statement). db2 "select BEISPIEL from TABELLE where SPALTE = 'TEXT' order by BEISPIEL" This has nothing to do with DB2 btw... Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab Hi Serge, thanks for your answer. > You have become victim of your shell. The thing is, that the AIX is used as a gateway. A Windows-client with db2 RTCL installed is using the gateway. A program uses the ODBC to fire SQLs to the database through that gateway. It get's the same error. As I worked from the server to the client to find the error, I stopped at the gateway AIX, where the error occured first in the chain. Does the db2 Connect on the AIX pass the SQL not correctly? Is that possible or am I on the wrong track? Do I have to pass the SQLs from the initial program with double quotes? In another setup with a Windows gateway with db2 Connect installed, it worked straight out of the box. Is there a difference? Probably in the configuration? Thanks, Bernhard Hi Serge, thanks for your answer. > You have become victim of your shell. The thing is, that the AIX is used as a gateway. A Windows-client with db2 RTCL installed is using the gateway. A program uses the ODBC to fire SQLs to the database through that gateway. It get's the same error. As I worked from the server to the client to find the error, I stopped at the gateway AIX, where the error occured first in the chain. Does the db2 Connect on the AIX pass the SQL not correctly? Is that possible or am I on the wrong track? Do I have to pass the SQLs from the initial program with double quotes? In another setup with a Windows gateway with db2 Connect installed, it worked straight out of the box. Is there a difference? Probably in the configuration? Thanks, Bernhard [edit] Alright, it worked with them two double quotes! Well, that is from the shell. From that program that uses the ODBC it still stops when it requests this SQL. But you're right: This is (probably) not a problem of db2. If I find out more, I'll let you know. (in case somebody is interested...[you never know ;o) ]) Thanks so far! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.