ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Are you using RUNSQL?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Are you using RUNSQL?

    I couldn't decide whether to put this post in the CL forum or the SQL forum. I don't think it matters.

    I was just wondering if anybody here is using the RUNSQL statement in CL programs. If so, what sorts of tasks are you using it for?

  • #2
    Re: Are you using RUNSQL?

    I would have put it in this forum given the nature of the question.

    I haven't used a tremendous amount of SQL in CL, but what I have has consisted of replacing OPNQRYF's. I've also written a couple purge or archive routines that use some SQL statements in a CL-driven application. I've also run across an occasional RPG call that was simply running an SQL statement that I moved into the CL it was called from, but don't recall specifically what any of those applications were doing off hand.

    Another shop I know does a lot of job run statistics in their batch CL environments, particularly in their nightly procedures, to benchmark performance and they use SQL inserts to update those tables.

    In all honesty, I haven't written a lot of new CL anyway with the ease running CL commands in RPG and monitoring for errors. But part of that is also due to the fact that I haven't been heads down coding for the past 5+ years and been in more of a system admin or management position.

    Comment


    • #3
      Re: Are you using RUNSQL?

      I use some but most are just Delete statements in work tables.
      Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply

      Comment


      • #4
        Re: Are you using RUNSQL?

        I use a few ........... mostly create alias for multi membered files

        GLS
        The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

        Comment


        • #5
          Re: Are you using RUNSQL?

          Ted,

          I'll throw in a few more.

          Create Alias (3 part naming)
          Create Alias (OVRDBF)
          Create Table
          Label On Table
          Label On Column
          Insert Into ... (values...)
          Insert Into ... Full Select
          Alter Table
          Drop Table (because of cascade)

          Jim

          Comment


          • #6
            Re: Are you using RUNSQL?

            I got a few times where I have to replace a table in batch. I use RUNSQL to Delete then Insert. Has the advantage over CLRPFM because it doesn't need excl lock (as long as there is a where clause)

            Comment


            • #7
              Re: Are you using RUNSQL?

              I will mention that I run the IBM command renamed as RUNSQLIBM.

              Comment


              • #8
                Re: Are you using RUNSQL?

                I use RUNSQL, but not from CL. I use it from an SQL stored procedure to submit a copy of itself to run in batch.

                Comment


                • #9
                  Re: Are you using RUNSQL?

                  Originally posted by Jim_IT View Post
                  I will mention that I run the IBM command renamed as RUNSQLIBM.
                  Not sure that's a good idea as PTFs may just recreate the RUNSQL command again. That did happen to us, but then we are on V6R1 and RUNSQL was introduced by a PTF.

                  Comment


                  • #10
                    Re: Are you using RUNSQL?

                    I created an import process for one of our systems, where we upload data from text files using CPYFRMIMPF to transfer the data into database files on the iSeries.
                    Because some of these text files contain blank records or other data that we want to manipulate or eliminate, I use RUNSQL in the CL as below:

                    RUNSQL SQL('delete from STKGRPC where +
                    GRGRP in (''16584'',''17297'')') +
                    COMMIT(*NONE)

                    It saves having to do this in an RPG program.
                    Definitely comes in very handy at times.
                    Poddys Rambles On

                    Comment


                    • #11
                      Re: Are you using RUNSQL?

                      Thanks for the information, everybody. Lots of good ideas here!

                      Comment


                      • #12
                        Re: Are you using RUNSQL?

                        Mostly I use RUNSQL in CL when writing utility or test pgms, and I want a dynamic SQL statement. (I only learned about QMQRY recently)

                        For programs that are an actual part of the application, we use a few simple RUNSQLs for file purging here and there (delete from file where date <= xyz), but that's about it. The person in charge of development on the box I work on does not know SQL and will never learn it, so we're not allowed to use very much of it.

                        Comment


                        • #13
                          Re: Are you using RUNSQL?

                          I would personally opt for using a high level language to run dynamic SQL over QMQRY (or any archaic query tool for that matter), but I still have nightmares of chasing down apostrophes...or maybe they were chasing me down, I digress.

                          Comment


                          • #14
                            Re: Are you using RUNSQL?

                            Originally posted by Vectorspace View Post
                            The person in charge of development on the box I work on does not know SQL and will never learn it, so we're not allowed to use very much of it.
                            What a shame. I hear this sort of thing too often.

                            Comment


                            • #15
                              Re: Are you using RUNSQL?

                              The person in charge of development on the box I work on does not know SQL and will never learn it, so we're not allowed to use very much of it.
                              I remember being very green and asking our development manager if they used SQL and could we get someone to demonstrate the uses of SQL the next time they were at our office.

                              This actually happened, what we were shown was SELECT * FROM TABLE, basically RUNQRY *N TABLE. When asked if there was more to SQL functionality, the response was well you can add a where statement.

                              This is when I started learning SQL and I learned it like I learned everything about iO/S by reading the manuals.

                              The funny thing is 20 years later the same people still are doing RLA for every task.

                              Jim

                              Comment

                              Working...
                              X