Home > IT Answers > Development > Execute SQL SELECT via RUNSQLSTM error
DanTheDane
2,540 pts.
 Execute SQL SELECT via RUNSQLSTM error
SQL, RUNSQLSTM, 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
ASKED: Jan 11, 2012  4:42 PM GMT
UPDATED: March 17, 2012  5:46:54 AM GMT
RELATED QUESTIONS
CharlieBrowne
24,725 pts.
Answer Wiki:
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:

        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  * * * * *
Last Wiki Answer Submitted:  Jan 11, 2012  5:11 PM (GMT)  by  CharlieBrowne   24,725 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

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

DanTheDane
 2,540 pts.
 

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

DanTheDane
 2,540 pts.
 

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

TomLiotta
 66,925 pts.