What is the best way to execute SQL statements in a CL program.

25 pts.
SQL statements
What is the best way to execute SQL statements in a CL program? We can run SQL statements in a RPG program. I want to delete some records from a file using the SQL delete statement then organize it.

Answer Wiki

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

Have the SQL statement as a txt member.
Use the RUNSQLSTM statement in your CL program.


Personally I use this approach when I want to execute SQL in batch.
This will work for SQL statements except for SELECTs.
If you need to run SELECT statements from a CL program, you can save the statement to a txt member. Run CRTQMQRY over the txt member to create a QMQRY object that you can run from a CL via STRQMQRY.
The “best” way is to call an embedded SQL program that runs the statements.
Next best is either RUNSQLSTM to execute SQL statements from a source member or STRQMQRY to execute compiled SQL statements. A RUNSQLSTM source member can have practically as many statements as you need, while STRQMQRY will consist of a single statement. However, a QM query can be very good at integrating CL variable values into the query statement. RUNSQLSTM allows for quick generation and execution; QM queries must be compiled. (Any system can compile them even without the SQL Dev Kit.)
If you don’t have the SQL Development Kit LPP, then embedded SQL programming is much more difficult. It must be done with the SQL CLI APIs. But if you’re already familiar with ODBC APIs, then you’ll be right at home.
If the SQL is complex and standard embedded SQL is unavailable, then REXX can run almost all useful SQL statements. REXX is always available and SQL can always be used with it.
There are many options. CL doesn’t support SQL directly, but any one or more of the options is almost always sufficient.

In i 7.1 (and i 6.1 with PTFs), the RUNSQL command can be used in CL to execute dynamic SQL statements.

Discuss This Question: 8  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.
  • DanTheMan 2
    I personnaly use STRQMQRY that let you pass variables to your SQL without editing a source and also let you use report form if any report should be printed in a more aesthetic way. To do it, you have to create a *QMQRY object by creating a source containing your SQL instructions. Have fun ! ______________________ Taz
    395 pointsBadges:
  • WoodEngineer
    A long time ago we found a neat command in one of the AS/400 trade journals - EXCSQLSTM. It even allows SQL statements to be executed on machines where the full SQL package is NOT installed. We have used it for over 10 years with great success. It works well in CL programs. Its also very handy when you want to execute a quick SQL statement from a command line. More info and code at http://www.mcpressonline.com/programming/cl/the-excsqlstm-utility.html
    8,245 pointsBadges:
  • Gilly400
    Hi, The article seems to have moved :- EXECSQL Regards, Martin Gilbert.
    23,730 pointsBadges:
  • Vatchy
    A long time ago I wrote a program that I call RunSQL - it consists of a command, a CL program and a QmQry. Using this you can run almost any SQL command including Select, Delete, Create, Drop, etc. If anybody is interested I'll be happy to send it to you for free. As long as you can create the objects on your system then you can run it - it doesn't matter if the SQL Development Kit is installed or not. It will let you enter up to ten SQL commands up to a length of 499 for each. It can be run interactively or in batch. What I will send will be the CMD and CLP source, along with instructions on creating the QmQry that is needed. These will be in a .ZIP file. I created a temporary e-mail address for this. It will only exist for a few weeks. jdog853-as400 @ yahoo.com
    1,415 pointsBadges:
  • DanTheDane
    Try this one (can be run from commandline or from a CL-pgm):
    STRQSH CMD('DB2 "SELECT * FROM mylib.myfile"') 
    happy coding DanF
    2,555 pointsBadges:
  • OLDAS400
    Here is the easiest way i have found. you can use the delete, update, select in this sort of format. this one has join with two files. SBMJOB CMD(DELETE SQL('FROM + *LIBL/PYMAST,*LIBL/PYEARNN PARTIAL OUTER + JOIN PYEMP#.PYMAST=ENEMP#.PYEARNN WHERE + ENRUN="2" AND ENCLAS="DETEN"'))
    10 pointsBadges:
  • TomLiotta
    SBMJOB CMD(DELETE... Unfortunately, that requires either purchasing a product to obtain a DELETE command or writing such a command yourself. It does remind me, though, that the Qshell db2 utility is another way to execute SQL through CL. Tom
    125,585 pointsBadges:
  • clam
    =================================================SQL        Use the SQL command in CL programs, examples: SQL        STM(‘UPDATE FILEABC SET FIELDA = ”ABC”, FIELDB = ”CDE” ‘) SQL        STM(‘SELECT * from FILEABC’) OUTPUT(*PRINT)       SQL        STM(‘SELECT * from FILEABC’) OUTPUT(*OUTFILE) +                            OUTFILE(TESTLIB/SQLOUTFILE)                  Cheng ============
    25 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: