Can I write a program in CL to update a record on a database file?

AS/400 SQL
CL Program
CL programming
Hi, I am not familiar with RPG. Is it possible to write a program in CL to update records in a database file? I have thought about SQL, however I do not have any SQL front end installed on the AS/400.

Answer Wiki

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

There is no command in CL to allow you to update a DB,
There are a number of free RUNSQL commands that you can download fromj from the web. They allow you to build your SQL statment on the fly in CL and then run it.
Here is a link to one of them, there are others.


There are most certainly CL commands to update a database. The rather lengthy list of database related commands can be found at CL for Files. There are commands to Update (UPDRCDCLF)/Delete(DLTRCDCLF)/Write(WRTRCDCLF) database records (in addition to subfile support, printer support, etc). In fact you can do things in CL with database that is not available with RPG, COBOL, or C! You do need to be at V5R4 or later.

If you are not interested in spending any money, just use the free run-time support that can be downloaded from the referenced site. The commands supported can be found at the page referenced earlier under Using only Run-Time Commands.

Vendor response—

If you want to get the most productivity out of your development time, there is a fee-based precompiler that is packaged as a separate option of CL for Files. The precompiler provides, among other things, DCLF like commands to access the external descriptions of your files, commands named like RPG opcodes (CHAIN/UPDATE/etc — which it would appear doesn’t apply in your case), etc. The one time charge for the precompiler is $795 regardless of the size of your system.

As the developer of the product I do receive compensation for sales of the precompiler.

—End vendor response

Discuss This Question: 6  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.
  • TomLiotta
    First, if you are at V5R4 or later, it is possible to do it via API calls in CL. It would be made to work similar to how it could be done in C. But if you went that way, you'd be better off doing it in C anyway. Either some of the C native database I/O APIs or the SQL CLI APIs (ODBC) might be used. I am not familiar with RPG . If not RPG, what about C? Or COBOL? Or Java? If it absolutely must be done with CL, the most common way is to create a generic QM query that accepts a SQL UPDATE statement from your CL program and executes it. Any AS/400 (since somewhere around Version 2) can use that technique whether the SQL Development Kit is installed or not. As an alternative, all AS/400s (since around Version 2) have REXX; and REXX can run embedded SQL statements nicely. In general, CL is not intended as a database language. It's supposed to be a procedural control language, so database I/O isn't fully supported. But like almost everything else, it can be made to work even in earlier releases of the OS. It just isn't neatly provided for by the language. Tom
    125,585 pointsBadges:
  • DanTheDane
    Qshell may help you. Try out this little sample of running SQL :
    STRQSH CMD('db2 "select distinct dbxlib from qsys.qadbxref where dbxlib like ''%DATA%''"') 
    I have not tried UPDATE and INSERT via Qshell.. Btw: I agree with Tom - CL is not ment for db-maintenance. DanF
    2,555 pointsBadges:
  • Praveenask
    we can not update in clprogram
    10 pointsBadges:
  • philpl1jb
    I don't know what restrictions not having "SQL front end installed" will make on the decision but here how I do it. I write the SQL in QMQRY, create the QMQRY object with CRTQMQRY command and run it from the CL with STRQMQRY or SQLSTM and run it from the CL using RUNSQLSTM. I use QMQRY when I need to pass variable data from the CL to be used in the SQL. Phil
    54,090 pointsBadges:
  • TomLiotta
    I have not tried UPDATE and INSERT via Qshell. Those can work fine through the Qshell db2 utility. Good comment. I should've remembered that one. I don’t know what restrictions not having “SQL front end installed” will make... The SQL Dev Kit installs essentially all green-screen user interfaces into SQL. That is, in addition to STRSQL, the QM user interfaces are also installed. QM still works, and QM queries can still be created and used -- you just can't use the STRQM command nor, of course, the menu options off of the QM menu. That means that you need to create the QM SQL statement with SEU without prompting or formatting assistance. The fundamental CRTQMQRY command is part of the OS, so those queries can still be compiled without the SQL Dev Kit. Tom
    125,585 pointsBadges:
  • The Most-Watched IT Questions: August 30, 2011 - ITKE Community Blog
    [...] 3. BVining, CharlieBrowne, TomLiotta, and DanTheDane try to answer this question: Can I write a program in CL to update a record on a database file? [...]
    0 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: