Execute SQL SELECT via RUNSQLSTM error

2555 pts.
SELECT statement
Trying to execute SQL-statement "SELECT * FROM mylib/myfile" give errormsg SQL9010. Some say that SELECT is not supported with RUNSQLSTM, but IBM command parameter text indicates otherwise. Is SELECT supported and what am I doing wrong?

Software/Hardware used:
IBM Power System i, V6r1

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you are just selecting records from a file, you cannot use SELECT if you are using RUNSQLSTM.
If you are doing a different function such as INSERT or DELETE and basing this off of a selection from a different file, then you can use SELECT.

I put a simple statement in a soiurce member and did a RUNSQLSTM from a CMD line. The statement is SELECT * FROM DEV/FSRSHAREDB

The report is as follows:
* * * * * E N D O F S O U R C E * * * * *
5770SS1 V7R1M0 100423 Run SQL Statements A
Record *…+… 1 …+… 2 …+… 3 …+… 4 …+… 5 …+… 6 …+… 7 …
SQL0084 30 1 Position 1 SQL statement not allowed.
Message Summary
Total Info Warning Error Severe Terminal
1 0 0 0 1 0
30 level severity errors found in source
* * * * * E N D O F L I S T I N G * * * * *</pre>

Discuss This Question: 7  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • DanTheDane
    Charlie, ..exactly same situation I discovered. What puzzles me can be seen here: This url points to the documentation for command RUNSQLSTM. Looking at the text for parameter COMMIT with "*RR" you'll find this text: "All tables referred to in SELECT, UPDATE, DELETE, and INSERT statements are locked exclusively until the end of the unit of work (transaction). " This is why I ask if somebody knows for sure if SELECT is Not supported with RUNSQLSTM. DanF
    2,555 pointsBadges:
  • DanTheDane
    Oops.. the editor tricked me with the url (I used the link-button...). Here it is as text: http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fcl%2Frunsqlstm.htm Pls concatenate the two lines into one (the editor still puzzles me..) DanF
    2,555 pointsBadges:
  • TomLiotta
    Is SELECT supported and what am I doing wrong? Yes, but not as an executable statement. The problem is that SELECT doesn't actually do anything. In a sense, all it does is create an access path (incorrect, but it's good enough for a concept). Once that's done, you need to have something actually do something with it like FETCH rows, and then something has to do something with the rows. If SELECT could make sense in RUNSQLSTM, what would you expect to happen to the rows? I suspect that you'd like the rows to go to either a printer or a display. However, that kind of functionality is generally done by passing rows through a ReportWriter function. In place of RUNSQLSTM, the STRQMQRY command may be used. Alternatively, a VIEW over a SELECT can be created with RUNSQLSTM, and basic RUNQRY can do print/display. Tom
    125,585 pointsBadges:
  • hbrooks
    The SQL Select statement is not supported using RUNSQLSTM.
    10 pointsBadges:
  • WoodEngineer
    There is an old user-written command still floating around - EXCSQLSTM - which may do what you want.  We have used it a long time to display data using the Select statement.  Very handy.  Our version includes output to a printer.  We have had the command so long that I do not know if that option is part of the original command or something we added.  Either way, it is a very handle tool.
    7,265 pointsBadges:
  • azohawk
    I will admit that I am not as SQL knowledgable as I would like to be. So here is my question. If (as has been indicated) the SELECT statment doesn't actually do anything, then I would expect that to be true accross the table. But I can do a stand alone SELECT statement in STRSQL or QMQury and I get a listing of rows of data. Why?
    2,460 pointsBadges:
  • pdraebel
    I guess that in those interfaces the Output targets are defined.
    7,360 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: