Execute SQL SELECT via RUNSQLSTM error

2,555 pts.
Tags:
RUNSQLSTM
SELECT statement
SQL
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
ASKED: January 11, 2012  4:42 PM
UPDATED: March 17, 2012  5:46 AM

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:
<pre>
1 SELECT * FROM DEV/FSRSHAREDB;
2
* * * * * 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 …
MSG ID SEV RECORD TEXT
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: 5  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 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:
    report
  • 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:
    report
  • 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:
    report
  • hbrooks
    The SQL Select statement is not supported using RUNSQLSTM.
    10 pointsBadges:
    report
  • 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.
    6,045 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following