Wednesday, March 19, 2014

Run SQL statements in your CL

cl clp sql runsql clle

IBM i 7.1 brought us the RUNSQL command. This allows me to run SQL statements within the CL.

Prior to IBM i 7.1 I would have to use the RUNSQLSTM command. To use this command I need to have the SQL statement in a source member. The way I knew to run build a SQL statement in CL program was to write it to a source member that could then be used by the RUNSQLSTM command. I always considered it too much work and either used a SQLRPGLE, RPGLE with embedded SQL, or a QMQRY, Query Management Query.

While I do not see the RUNSQL command replacing my use of SQLRPGLE, I do see it eliminating most of my use of QMQRYs.

You cannot execute all SQL statements using the RUNSQL command. Click on the link at the bottom of this post to the IBM website for a list of what statements are supported.

In this first example I want to create an outfile in QTEMP using the CREATE TABLE SQL statement I used in the post Creating a SQL table on the fly:

01   PGM

02   RUNSQL     SQL('Drop Table qtemp/temp_table') +
                  COMMIT(*NONE)
03   MONMSG     MSGID(SQL9010)
   
04   RUNSQL     SQL('CREATE TABLE QTEMP/TEMP_TABLE AS +
                     (SELECT FLD0,FLD1,FLD2 +
                     FROM TESTPF +          
                     WHERE FLD0 = ''1'') +
                     WITH DATA') +
                COMMIT(*NONE)

05   ENDPGM

The first RUNSQL command, line 2, executes the SQL DROP TABLE command to delete the outfile in QTEMP. This also shows that you can use both lower and upper case in the command. I do not want to use commitment control for any of my SQL statements, therefore, I have *NONE in the COMMIT parameter.

If an error is encounter, i.e. the table does not exist, then it is monitored by the use of the MONMSG for the message SQL9010. SQL9010 simply means “Error found in SQL statement".

The second RUNSQL command, line 4, creates the outfile using the CREATE TABLE statement.

It is possible to do database functions too:

01   PGM

02   DCL        VAR(&VALUE) TYPE(*CHAR) LEN(10) VALUE('TEST')
03   DCL        VAR(&PARM) TYPE(*CHAR) LEN(100)

03   RUNSQL     SQL('INSERT INTO MYLIB/TESTPF + 
                VALUES(''' || &VALUE |< ''', +
                       123,''ABC'')') +
                COMMIT(*NONE)

04   CHGVAR     VAR(&PARM) +
                  VALUE('DELETE FROM MYLIB/TESTPF +
                         WHERE FLD0 = ''' || &VALUE |< +
                         ''' ')

05   RUNSQL     SQL(&PARM) COMMIT(*NONE)

06   ENDPGM

Note: The || and |< are the shorthand equivalent of the *CAT and *BCAT functions in CL.

Line 3 shows how I could integrated a variable into the text string in the SQL parameter.

Line 4 and 5 show how I can use a variable in the SQL parameter.

 

You can learn more about the RUNSQL command on IBM's website here.

55 comments:

  1. While this makes running SQL statements in CL easier I still prefer to use the db2 pase command with QSH. Using that you can run any statement and pipe output from select statements to ifs files.

    ReplyDelete
    Replies
    1. Glad to see SQL is catching on. A few of us have used it since V1R3 of OS/400 back in 1990, but what the heck. Maybe this SQL thing may catch on.

      Delete
  2. Thanks for posting. But please be carefull with SQL commands built by concatenation of strings because this makes your code vulnerable for SQL injections.

    ReplyDelete
  3. I'm on V6R1 and still using the utility to run SQL in a CL that I picked up from an article in News3X/400 (a long time ago). I called my command RUNSQL with a parm SQLSTM so hopefully only minor changes will be required to make use of the IBM command when we finally upgrade to V7.

    ReplyDelete
  4. Nice overview, Simon. Thanks for sharing it here. I wasn't aware of the new command but after seeing how it's used I suspect I, too, will find it useful.

    ReplyDelete
  5. FYI: RUNSQL is also present in V6R1. It is not shown in the Help Center It was created (on my system) by *IBM on 8/28/12 and so may have crept in with a PTF.

    ReplyDelete
    Replies
    1. I cannot find any reference to it in the IBM Information Center for V6R1. I have assume that you are correct and it crept in with a PTF or a Technical Release update.

      Delete
    2. RUNSQL was released for V6.1 in PTF Group SF99601 level 25, (Ref: https://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/topic/rzaq9/rzaq9.pdf p. 32.).
      Btw this manual is a very good foundation to get up-to-date on many small changes and features that came with V6.1

      ALERT: Using the RUNSQL command be aware that SQL-string to be executed must not exceed 5000 chars in length.

      Delete
  6. It sure makes life simpler compared to RUNSQLSTM but it's a pain in a way because of having to escape all the quotes: get ready for constants like q ('''') and rpq ('('''), qcq ('''','''') & etc..

    ReplyDelete
  7. Can we use Select statement inside RUNSQL?

    ReplyDelete
    Replies
    1. I can do:
      RUNSQL SQL('insert into qtemp/outfile (select * from mylib/orddtl where ordnbr = ''SK280MM'')') COMMIT(*NONE)

      Delete
  8. Simon, this may be a silly question, but how do you "send" your table in qtemp to the user? thanks Ann

    ReplyDelete
    Replies
    1. I copy the file from QTEMP to a folder in the IFS.
      Then I could use the SNDSMTPEMM command to email a file from the IFS.
      You will find the post here.

      Delete
  9. A couple observations:

    1) SQL9010 is a generic error message, but it doesn't indicate the root cause of the failure. In this case, the assumption that the root cause is that the table/file does not yet exist may always work. But for other situations when multiple types of errors are possible for the SQL statement being run, the CL program could search messages in program message queues to find the reason for the SQL statement failure. For example a technique such as this could be used to find the SQxxxxx MSGID corresponding to the error :
    http://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/rbam6/rcvmsgpgm.htm

    2) For performance, instead of using RUNSQL to always run DROP TABLE, the CHKOBJ CL command could be used to see if the table/file already exists (assuming a table with system name, vs SQL name). If it does exist, and it typically contains only a small number of rows, RUNSQL could run a DELETE statement to clear any existing rows. Reusing the existing table in such cases (minimal no. of rows) should be quicker than always doing a DROP + CREATE. Of course if CHKOBJ determines the table doesn't exist, the CREATE TABLE as shown would be run.

    ReplyDelete
  10. Stupid line of questioning, please pardon my ignorance. I was able to get SQL in CL as of V4R5 and even accepting cl variables as parameters for selecting, I was using the query management package (I think) and some techniques described in a book called "SQL developers guide" by Conte. It seemed to work great for reading from tables, but, if you needed to update or insert into any table, you needed to establish a commitment control (journaling) environment over those files which did work 100% but was a royal pain. (Interactive STRSQL environment handled that for us automatically.) It was fun but I never did any serious production work with it.

    It sounds like what is going on above is a whole lot better, I am running 7.1 myself. If I do embed SQL as you all are describing into CL will the O/S automatically manage the commitment control environment for you ? If some one were to cancel the job that does inserts or updates in mid process will the O/S roll back the changes like STRSQL does ?

    Again pardon my ignorance .... I just wanted to use this for something important and do not have as much time as I would prefer to find the pitfalls.

    ReplyDelete
    Replies
    1. Joachim GuetzlaffMarch 21, 2014 at 3:22 PM

      Greg, the OS actually does not implicitly manage commitment control anywhere. The same is valid for RUNSQL command as it is for STRSQL, SQL modules, programs, serviceprograms, RUNSQLSTM and whatever else is out there. You have to use the COMMIT parameter right, to make commitment control work.

      Delete
    2. To add to Joachim's reply...
      The RUNSQL command does contain a COMMIT parameter, which can be used for commitment control. In my examples I chose not to.

      Delete
    3. Greg, I'm not sure the cause for your commitment control woes with QM - neither the CRTQMQRY nor the STRQMQRY command have a COMMIT parameter that RUNSQLSTM and RUNSQL and maybe STRSQL (I didn't look) have.

      Delete
    4. Joachim GuetzlaffMarch 22, 2014 at 7:43 PM

      Query/400 and QM Query don't provide any means to manipulate the data source. Therefore commitment control is not necessary. This is much different with the SQL related commands we are talking about. There is no need for commitment control, if you are using SQL just for querying purposes. Once transactions come in the game, it's a whole different story. (Still you can take your chances with COMMIT(*NONE)!)
      Using STRSQL you can set the isolation level either when issuing STRSQL or using F13 to set the session properties.

      Delete
    5. Thank you all so much for the quick responses, much appreciated.

      What was happening to me was if I journaled the file I could pass parameters into the QM query that updated or inserted and it ran well. If I was only selecting I did not need to do that. In STRSQL I could update or insert no problem .... if I did that in debug mode maximum message logging you could see the interactive STRSQL job creating and destroying a temporary journal.

      I was just wondering what this new technique was in this respect and you all nailed it ... thank you so much again, that obvious in retrospect parameter option. That gives me the extra warm fuzzies to do something important/urgent with it, but, with the normal due diligence in advance of anything new in production. I'll backup the files to a save file in the production job for a while and when time permits I'll set up a test job and cancel it immediately to see what happens.

      Thank you all again,
      GG

      Delete
    6. @Joachim:

      Query/400 is a "read-only" facility.

      Query Management is not.

      You can execute INSERT, UPDATE, and DELETE SQL statements (and DDL statements, for that matter - CREATE TABLE, etc.) that were compiled into a *QMQRY (CRTQMQRY / STRQMQRY). You can create QM procedures that start under various isolation levels, start connections to other systems, run multiple QM queries and nested QM procs, and issue COMMIT operations (STRQMPRC).

      Delete
    7. Interesting .... I dug deeper into my old code, Paul Conte's (albeit dated) book and was experimenting more this morning .... technically what I was doing under 4.5 was a normal batch or interactive OPM CL environment so apparently the use of the QM to receive parameters in that environment was limited to read only. Building the journal in the CL before the QM reference did allow updates and inserts. Regardless, the new 7.1 techniques and the commitment control specific parameter are a whole lot more concise. Best advice I have received in a very long time :)

      Delete
    8. Joachim GuetzlaffMarch 22, 2014 at 7:53 PM

      Gary, thanks for clearing that up. It's quite a while that I used QM and even then only occasionally. Pardon me - somehow I knew there could be the risk being off the track here ;-)

      Delete
    9. Joachim, you mentioned "OS actually does not implicitly manage commitment control anywhere". I believe it does - slightly - in the following circumstance: if you are currently journalling just "after" images [which I avoid by the way] instead of "both" before and after images; when any file journalling "after" images is opened under commitment control, the system automatically starts journalling "both" images as the "before" image is required to rollback, When commitment control is ended, the system reverts back to "before" images. This applies if running an SQL command under commit.

      Just checked and an extract from manual on Start Commitment Control: Before a commitment definition is established, the user must ensure that all database files that are to be opened under commitment control for a single commitment transaction are journaled. If only the after images are being journaled, the system implicitly begins journaling both the before and the after images for the duration of the changes being made to files opened under this commitment definition.

      Delete
    10. QMQRY has long been able to do updates.

      You could also create a imbedded SQL program of about 13 lines that would run the same kind of statements as RUNSQL; the code takes 15 minutes to write and can be located for download. This gets around QMQRY's 50 byte variable limit.

      All this is very easy to implement. What is more difficult is to get the data back into CL variables. Creating a table and reading it with DCLF is doing record level access from CL, not SQL. A Select into command or program that returns one row/one variable and a program that passes a cursor and reads back one variable for each row is not particularly difficult, but filling a structure is a lot more work.

      Delete
    11. Tony, what you mention is exactly what I meant by "implicitly". I am aware that the OS/DB takes care of the commitment handling once you as a devoloper/user decide to use it. I am not aware that the OS/DB will do commitment control if you decide to run everything under COMMIT(*NONE).

      Delete
  11. IBM's RUNSQL command was made available via PTF to 6.1 and 7.1. And as someone mentioned, this new command could cause confusion/problems if someone already has a RUNSQL command they created in the past. Here is a link to an article that discusses this issue:

    http://www.itjungle.com/fhg/fhg053012-story01.html

    If you have some pre-existing code that has RUNSQL REQUEST(...) then it could be that it is a wrapper for QM. Many people created a RUNSQL command to simplify running SQL from CL and therefore have programs with RUNSQL REQUEST(...). But the new IBM RUNSQL command does not have REQUEST as a valid keyword, so these programs will suddenly fail (unless the programs qualify the command with the library where their custom RUNSQL command is located) once PTFs are applied that gives them the new IBM RUNSQL command (since IBM's command is in QSYS and will probably be found first by the program.)

    So, if you already have programs with RUNSQL that pre-date IBM's RUNSQL command, the easiest fix might be to qualify that older RUNSQL command in each program where it is used.

    ReplyDelete
    Replies
    1. Hi,

      You mentioned Qualify the older RUNSQL command means.Using libraryname followed by command in CL program.Is this fix the issue for the older runsql command?
      LIB/RunSQL REQUES(....)

      Delete
  12. Amitava Sutradhar, MBAMarch 24, 2014 at 4:53 PM

    I have also used the RUNSQL Command, in a recent CL Program, and it's just an WOW. Before that I used to use QSH db2 utility to run dynamic SQL Command in CL. But having something as a command is Great...

    ReplyDelete
  13. When I make commands that I think IBM will very likely get around to writing in a few years, I generally try to name them something I don't think IBM will use. RUNSQL was just too likely. If I then had to go back and change all the programs that used it, I'd change the command to something quirkier instead. EXECDB2?

    This discussion was inspiring, and I wrote a select statement passing command that will process up to 8 concurrent cursors and fetch them into CL variables. Proved easier than I thought, if inelegant. However, while the V5R4 data structure support in CL helps a lot, it seems to give me only packed and integer numeric variables. It leaves me with defining zoned fields as character. Anyone have any insight? Or is that too far afield for this thread?

    ReplyDelete
    Replies
    1. Last year I came back into the 'AS400' fold after several years absence...programming life is good again. I've been using the RUNSQL statement on V6R1 for quite some time now...Lynne, I would be curious to see your code on how to fetch SQL data back into CL variables...I've struggled with that in the past.

      Delete
    2. I agree about the naming commands... but I go one step further since the command EXECDB2 "could" have been a name used by another third party product. I name my command as I would any CL program using the naming convention of the application. For example one of our applications prefixes all objects with 'GP' so our implementation of RUNSQL would have been GPRUNSQL. It's just one more level to help keep us from stepping on any toes...

      Delete
  14. A variation of my previous performance suggestion:
    Instead of using CHKOBJ, the RTVMBRD CL command could be used with the NBRCURRCD parameter. If the outfile currently exists, this returns the number of rows. If it doesn't exist, the CL can monitor for that, similar to CHKOBJ. The idea is to reuse the existing outfile only when it contains a small (by your definition) number of rows. Logic might be something like the below.
    NOTE: This is an incomplete example to show concept, and has not been tested in any way.

    DCL &CreateIt *LGL '0'
    DCL &Rows *DEC LEN(10 0)
    DCL &DropLmt *DEC LEN(10 0) VALUE(10000)
    DCL &Select *CHAR LEN(100) +
    VALUE(' SELECT FLD0,FLD1,FLD2 FROM TESTPF ...')
    DCL &SqlStmt *CHAR LEN(200)

    RTVMBRD ... NBRCURRCD(&Rows)
    MONMSG MSGID(CPF9812 CPF9815) EXEC( CHGVAR &CreateIt '1' )

    IF (&CreateIt *EQ '0') THEN(DO)
    IF (&Rows > &DropLmt) THEN(DO)
    /* Existing outfile has too many rows. Drop and recreate it. */
    RUNSQL SQL('DROP TABLE QTEMP/TEMP_TABLE ') ...
    CHGVAR &CreateIt '1'
    ENDDO
    ELSE DO
    /* Reuse existing outfile and repopulate it. */
    RUNSQL SQL('DELETE FROM QTEMP/TEMP_TABLE ') ...
    CHGVAR &SqlStmt +
    VALUE('INSERT INTO QTEMP/TEMP_TABLE ' *CAT &Select )
    RUNSQL SQL(&SqlStmt) ...
    ENDDO
    ENDDO

    IF (&CreateIt *EQ '1') THEN(DO)
    CHGVAR &SqlStmt +
    VALUE('CREATE TABLE QTEMP/TEMP_TABLE AS( ' *CAT &Select +
    *BCAT ') WITH DATA' )
    RUNSQL SQL(&SqlStmt) ...
    ENDDO

    ReplyDelete
  15. Thanks for revealing that. It's quite useful.

    ReplyDelete
  16. Hi.

    I have a SQL command that runs in the STRSQL window, but not thru RUNSQL. I wonder why? The SQL statement just adds a column containing the current date.

    Also, how can i save the output to a file? Thru STRSQL, i change the setting to output to file, then run the statement. But how can i do it using RUNSQL?

    Thanks.

    ReplyDelete
    Replies
    1. There are only certain SQL statements that you can run using the RUNSQL command. Here is a list of them here.

      If you are getting a message when you use the RUNSQL it would be a place to start to determine if RUNSQL is the problem.

      Delete
  17. Simon,

    Can you take a minute and let me know if you can tell what might be wrong with this?

    RUNSQL_X SQL('UPDATE mylib/myfile SET N53DSC = +
    ''' *CAT &TEXT *CAT ''' where ''' *CAT +
    &N53GIDCHAR *CAT ''' = ''' *CAT +
    &CHARID# *CAT ''')') COMMIT(*NONE)


    When I run this inside my CLLE program, I get the following message:

    Token ) was not valid. Valid tokens: OR USE SKIP WAIT WITH.

    Thanks,
    Barry

    ReplyDelete
    Replies
    1. Make the SQL statement in a variable then use that in the RUNSQL statement.

      Delete
    2. Thanks Simon. Good idea and not like I've never done that before. However I still get the same 'Toke ) was not valid' message.

      Delete
    3. The statement as you have it would produce the following string:

      UPDATE mylib/myfile SET N53DSC = '' WHERE '' = '')

      I am using to indicate this is the value that is contained with &TEXT, etc.

      1. The WHERE comparison field cannot be a variable. Should be: WHERE =

      2. There is a ) at the end that does not match with an opening (. Remove it.

      Delete
  18. Is it possible to have the end user enter the values without having a screen? Like you can do with CL prompting?

    ReplyDelete
    Replies
    1. If you mean CL's Command prompting I do not see why not.

      Delete
  19. One tip I would like to share. Sorry if someone suggested this already. Too lazy to read all of the posts. Anyhow - I use a variable to construct my SQL statement and then use the variable in the command - ie RUNSQL SQL(&SQLSTMT). By doing this I can then see the SQL if I am using ISDB by using "dsp (1:1200)sqlstmt". (assuming &SQLSTMT is 1,200 long. Makes it tons easier to see where I've messed up.

    ReplyDelete
  20. Can someone tell me (sorry if it's already been asked) is it possible to use a numeric variable in the CHGVAR build of the SQL statement? i use a CHAR field and I have no problem with my compile. The program ab-ends when it compares to a numeric field but it compiles. lol If I change the variable to DEC the CL will not compile. Is this possible? If so, what is the trick?

    ReplyDelete
    Replies
    1. It is not possible to use a numeric value when building a string that contains the SQL statement. It is only possible to concatenate alphanumeric fields together.

      The reason the program compiles is that it only knows that RUNSQL will use a variable for its statement, not what is in that string. Only when the program is executed does the RUNSQL get to "see" what is in the string.

      Delete
  21. Simon,

    we have so many programs which using older version of RUNSQL with parameter REUEST.After IBM release new RUNSQL program causing many batch jobs to fail....

    What are the ways i have to solve this issue..please let me know??

    ReplyDelete
  22. If you are using the non-IBM RUNSQL command & want to keep doing so then you are going to have to do some work.

    - You could put the library that contains the non-IBM RUNSQL at the top of the system library list. I would not do this as you will never be able to use the IBM RUNSQL, and could have problems with other commands.

    - Change all of the programs and qualify the call to the command. The library containing the non-IBM RUNSQL would be added to the bottom of your user library list.

    - Rename the non-IBM command to something different. Then change all of the programs to use renamed command name.

    - Replace the non-IBM RUNSQL with the IBM RUNSQL. As you are going to have to change all the programs anyway this would be my preferred choice.

    ReplyDelete
  23. Hi,
    I'm on 6.1 and I have RUNSQL in a CL program. The Statment is 'UPDATE ...'
    and is ok but I need to get the SQL Msg SQL7957 with the number of rows
    updated or the SQL0100 (row not found). I can get this with RCVMSG but only in debug mode. Can anyone help me?
    Rui

    ReplyDelete
  24. Following the manual it should be done with RCVF but I don't know how, I'm also looking for a solution

    ReplyDelete
  25. Still a bad practice: The files used in RUNSQL does not show up in DSPPGMREF.

    Example:

    CL PROGRAM TESTSQL:
    RUNSQL SQL('DROP TABLE QTEMP/MYTABLE') COMMIT(*NONE)
    MONMSG SQL9010
    RUNSQL SQL('CREATE TABLE QTEMP/MYTABLE AS ( +
    (SELECT * FROM PRDTABLE)) WITH DATA') COMMIT(*NONE)


    DSPPGREF:
    Object
    Program Referenced Library
    TESTSQL QLECWI QSYS
    TESTSQL QCLSRV QSYS
    TESTSQL QLEAWI QSYS




    RPG PROGRAM TESTSQL1:
    EXEC SQL
    Set Option Commit=*NONE,DatFmt=*ISO,CloSqlCsr=*ENDMOD,DlyPrp=*YES;
    EXEC SQL
    DROP TABLE QTEMP/TMPQRYF;
    EXEC SQL
    CREATE TABLE QTEMP/TMPQRYF AS (
    SELECT *
    FROM PRDTABLE A
    ) WITH DATA;
    *INLR=*ON;

    DSPPGMREF:
    Object
    Program Referenced
    TESTSQL1 QSQROUTE
    TESTSQL1 QRNXIE
    TESTSQL1 QLEAWI
    TESTSQL1 TMPQRYF
    TESTSQL1 PRDTABLE

    ReplyDelete
  26. A quick question. Can we use 'SET' SQL command to set or change a variable's value similar to CHGVAR command?

    For example...

    ChgVar &CurDate8 &Blanks (Initialize to blanks)

    ChgVar &SETSQL ('SET &CurDate8 =' *Bcat '(SELECT current date FROM sysibm/sysdummy1)')

    RUNSQL (&SETSQL)

    This may not be the correct representation, but I hope you get the idea of what I'm trying to accomplish here.

    Thanks in advance.

    ReplyDelete
    Replies
    1. The SET is not supported in the RUNSQL command.

      Here is a list of those SQL expressions that are recognized by this command here.

      Delete
  27. Clarification:

    *CAT (concatenation, symbol ||) operator
    *BCAT (concatenation with blank insertion, symbol |>) operator
    *TCAT (concatenation with trailing blank truncation, symbol |<) operator

    http://www.ibm.com/support/knowledgecenter/ssw_i5_54/rbam6/rbam6charstrngexp.htm

    ReplyDelete
  28. Hi Simon,
    Your second example, with passing parameters to a RUN SQL statement was very handy to me today.
    Thanks very much for such detailed description of the examples.

    ReplyDelete
    Replies
    1. I am glad you found this useful. It is good to get feedback, like this, from the readers of this blog.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.