Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

Need to run a CL from HLL to update some data for Crystal Reports

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

  • Need to run a CL from HLL to update some data for Crystal Reports

    > We are trying to set up a process so we can run a CL > command to run AS400 specific commands on the > AS400 before we use Crystal Reports to use an > OLE DB connection to extract the data. One way to do this is to create a stored procedure. Have OLEDB call the 'setup/extract' SP before doing the database access. The FAQ has some information on stored procedures http://faq.midrange.com and you can hit the IBM Infocentre for information as well http://www.iseries.ibm.com/infocenter The beauty of stored procedures on OS400 is that you can create a regular program and then use the SQL CREATE PROCEDURE command to 'register' your program as a SP. You can then call it from a command line or via SQL. --buck

  • #2
    Need to run a CL from HLL to update some data for Crystal Reports

    Good afternoon Starbuck5250! I appreciate the help, but these links took me to sites with the IBM DBU for the AS400. We are running ASC's version of DBU, so I don't think I can do this. I could be wrong, but from what I've read on the internet so far, most people are suggesting purchasing Client Access and IBM DBU. I was just wondering if the iSeries actually had a better interface for running jobs from another 4GL program. Currently, I wrote a Script and have used the RunScript command on the AS400. Now if I could only get to it to execute the RunScript command, I think the problem would be resolved.

    Comment


    • #3
      Need to run a CL from HLL to update some data for Crystal Reports

      Woops! The last line should have been: If I could only get this to execute from a 4GL program or even DOS, I could probably figure out how to get it working in other ways. Do you know of a way that I can get in to execute this? Maybe there is a software that we already have, like Visual Basic. Any suggestions are greatly appreciated.

      Comment


      • #4
        Need to run a CL from HLL to update some data for Crystal Reports

        Since Crystal has an OLEDB connection to your database, that means you can run SQL statements like 'SELECT this,that from thefile'. Is that right? If so, then you can use the exact same connection to run a stored procedure which looks like this 'CALL myproc'. If MYPROC is linked to a CL program, you can clearly run a CL program from Crystal. Look in the Infocenter for stored procedures, under the database topic. I don't know anything about the script you mention which runs a RunScript command. I have been able to run iSeries programs through stored procedures called from many PC applications though. Good luck --buck

        Comment


        • #5
          Need to run a CL from HLL to update some data for Crystal Reports

          i'm not sure if will help but if you are just trying to execute a iSeries program from a pc the ftp command "quote rcmd call pgm(xxx000cl)" will accomplish this. Of course you’ll need the ftp server running on the iSeries but the ftp commands can be written as a script on the pc.

          Comment


          • #6
            Need to run a CL from HLL to update some data for Crystal Reports

            It took a little bit of work, but I got it to work. I appreciate the help!

            Comment


            • #7
              Need to run a CL from HLL to update some data for Crystal Reports

              I still need to look into your suggestion, because we would like to use Stored Procedures for the long run. Currently, running an FTP in DOS has given us the quick-n-dirty program to give us the process we need. I've tried to use the following langauges CRTSQLCI CRTSQLCPPI CRTSQLRPG CRTSQLRPGI and receive the following error DB2 Query Manager and SQL Development Kit for AS/400 not available. I thought you need those two pieces in order to write stored procedures?

              Comment


              • #8
                Need to run a CL from HLL to update some data for Crystal Reports

                CRTSQLxxx requires the "DB2 Query Manager and SQL Dev Kit", 5722-ST1. I think it's free as of V5R2, maybe R1.

                Comment


                • #9
                  Need to run a CL from HLL to update some data for Crystal Reports

                  > I still need to look into your suggestion, because > we would like to use Stored Procedures for the long run. -snip- > DB2 Query Manager and SQL Development Kit for AS/400 not available. > I thought you need those two pieces in order to write stored procedures? Not to do what you are after here. If you need to return a result set, you will need the SQL Dev Kit. That's because you need to use the SQL 'set result sets' statement to return a result set. Because that is embedded SQL, you'll need the kit. If you just want to run a program you do not. The difference is that you can register ANY iSeries program as a stored procedure; even a very simple CL program which takes a command as it's parameter. Look in the SQL Reference under Create Procedure and you will see that CL is a valid external procedure language. If you're wondering how you can issue the SQL Create Procedure statement without the SQL Dev Kit, you may have IBM's RUNSQLSTM on your system. You can put an SQL statement into a source member and execute it via RUNSQLSTM. You can't do 'select' though. Alternately, look in the FAQ at http://faq.midrange.com Search for 'runsql' and you'll see a utility that will allow you to execute SQL statements from CL. This allows every SQL statement including select. --buck

                  Comment


                  • #10
                    Need to run a CL from HLL to update some data for Crystal Reports

                    We are trying to set up a process so we can run a CL command to run AS400 specific commands on the AS400 before we use Crystal Reports to use an OLE DB connection to extract the data. Here is the challenge: We are running ASC for our DBU, using HiT400 for creating the OLE DB connection and Crystal Reports enterprise. The programs that we do not have on our system are IBM's DBU, Client Access or Viewpoint which is an ASC product. The parts that we do not have were all suggested as ways to run a CL program on the AS400 to run the required job. I've personally used Client Access and IBM's DBU, but the set up here is unique. Is there some other way to execute the CL? (DOS, HTML, etc.?) Things I've looked at have been QCMDEXC and QCAPCMD, but can not find a good example of using them except for a C# example. Which of course we do not have C#. Maybe there is even a way to perform it right from Crystal or some command from one of the listed software's, that we have not found in the documentation. Any help would be greatly appreciated.

                    Comment


                    • #11
                      Need to run a CL from HLL to update some data for Crystal Reports

                      TTBOMK 5722-ST1 is not free. It is available from IBM, but should not be necessary to create stored procedures. AFAIK, and program can be turned into a stored procedure. Dave

                      Comment

                      Working...
                      X