Does anyone know what this error message is in showcase query Token ORDBPRPRT invalid?

This error mesaage is for one user, I don't get the error message when I run the query, I have checked security, I have un-installed showcase and re-installed it. I have submitted this issue to showcase and they are stumped. The user is on windows 2003 II'm running windows 2007.We are on an AS400 Iseries version v5r4, I have check the AS400 job description,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What is the SQL query that it is running ?
Gary PattersonVP Technology / Senior Consultant Commented:
In addition to posting the SQL:

Does the user enter any parameters that get used to build the query?  

Sometimes, if the user enters an unexpected value in a parameter, it can "break" the SQL query in this fashion.

It can also happen with very long SQL statements that get truncated due  to length limitations at some point between the client application and  the database server.
It would be handy to see the actual query getting sent to the DBMS.  

Do you know what interface ShowCase uses:  ODBC, JDBC, or OleDB?  Once we narrow that down, we can capture the SQL statement that is getting sent to the AS/400, and that usually is sufficient to diagnose the problem.

The error message says that when DB2 was parsing the SQL statement, it encountered the token "ORDBPRPRT" (which looks like a column name or a table name) where it expected to find a ")" character.

- Gary Patterson

In ShowCase past releases, they had a ShowCase server that the client connected to. This meant that "ODBC, JDBC, or OleDB" was mostly irrelevant in a precise sense. The query would be a SQL-based string that their server would interpret, converting any extensions to native queries. AFAIK, the dual client/server setup still exists; but I don't know how much (nor even if) it involves non-DB2 structure for query strings. I would suspect that now being owned by IBM (IIRC, who bought SPSS who earlier bought ShowCase) might mean that the ShowCase server could have some unusual qualities.


Gary PattersonVP Technology / Senior Consultant Commented:

Oh, that's good to know.  

I've seen Showcase in a few client shops, but I'm not a regular user, and I'm certainly not familiar with the application architecture.  Looking at the current SPSS web site, it looks like it is Java-based, so I thought JDBC was a likely interface.  Still might be - just host JDBC instead of client JDBC.

Adding a dedicated Showcase server into the mix makes things a bit more complicated, but still, DB2 is most likely doing the parsing and returning this error message - the trick is to still to figure out where.

One way would be to create a query in showcase that uses a dummy table created just for the test, and then run a DB monitor that catches all queries for a few seconds.  Scan the results for the dummy table, and now we've identified the job where the SQL is actually running. Might also be able to use the same technique to just capture the problem query (unless it is just being parsed using QSQCHKS, for example - don't think that would show up on DBMon output.)

Then we could monitor / debug /  trace the jobs that actually run the query and figure out how to grab the actual query string that is being executed.

If the query string is constructed on the client side, we might be able to trace / debug / sniff the string from there, or as it traverses the network, alternatively.

Lots of options - just don't know the Showcase architecture to know what is best.  

This is one of those that is much easier to track down first-person than third-person.

- Gary Patterson
The ShowCase server would (in past releases) also create at least one exit point upon installation. There isn't much that I can write about it, but it might be useful to know. AFAIK, the primary exit point and format is SC_QUERY_ROW_SEC format SCRS0100. I don't know if the exit point is *REGISTERED nor if the latest releases include additional exit points. ShowCase documentation should show exit program parameters or other related info.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Operating Systems

From novice to tech pro — start learning today.