Running SQL Statements in a Batch Job

[Guest.Visitor]

Do a search on RUNSQLSTM. Chris.

[R.Khoury]

Running SQL Statements in a Batch Job

You can use RUNSQLSTM for just about everything but SELECT. If they have DB2 Query Mgr and SQL DevKit - then you can use Query Manager (STRQM). You can create the query with SQL and even pass it parameters. Or you can use RPG w/embedded SQL. So many choices, so little time. HTH

[Guest.Visitor]

Running SQL Statements in a Batch Job

There are a number of freeware products around that do exactly what RUNPDSQL does. Most are based on STRQMQRY. I believe a RUNSQL command is available from iSeries Network. EXCSQL is another name that comes to mind. Bob Cozzi used to make money off a product called SQL Lite. There may be other options. RUNSQLSTM will do most things except a SELECT statement, because it requires the output go somewhere. You can sometimes get around this by variations on: create table qtemp/mytable as (select * from somefile) with data or insert into qtemp/mytable select * from somefile Sam

[Guest.Visitor]

Running SQL Statements in a Batch Job

You can actually do this with STRQMQRY in the CL pgm. STRQMQRY QMQRY(nflib/nfq904) SETVAR((COCNUM &COCNUMPARM)) Create a source physical file called QMQRYSRC Create a member mine is called NFQ904 with type QMQRY this is NFQ904 which uses a varaible INSERT INTO &LIBFILE (FLD) SELECT (''||&COCNUM||'' ||CHAR(WABLNO)||'' ||CHAR(SUM(WANCRT))||'' ||CHAR(SUM(WATWGT))||''||CHAR(COUNT(*)) ||'') AS REC FROM NFP638 GROUP BY WABLNO ORDER BY REC

[R.Khoury]

Running SQL Statements in a Batch Job

I just remembered another method (forget where I found it so don't give me the credit for it), using an SQLRPGLE pgm that executes an immediate SQL statement. Call the pgm, passing the desired SQL statement (just not a SELECT statement). You can call it from CL w/the desired sql statement.

---

Code

[gotto@islandpacific.com]

Running SQL Statements in a Batch Job

I was going to suggest the same thing as Russ (RPG program with execute immediate statement). That is what someone did where I work. It works fine but you should code the RPG program to capture SQL errors (like syntax problems) otherwise the caller of the RPG program will never know the SQL had a problem. Garrett

[Guest.Visitor]

Running SQL Statements in a Batch Job

I learned all of my SQL utilizing the SQL4Less product from ProData. I've written many CL programs that refer to their command RunPDSQL which allows an SQL statement as the parameter. I'm now involved with a machine that does not plan on purchasing this product, so I'm going to have to work with IBM's SQL as provided. I'm familiar with StrSQL and entering statements interactively, but I'm not sure how to do it in a batch job. I think I remember previous conversations that I can put a number of SQL statements separated by semi-colons in a source member and then reference this source member with an SQL command. Is this correct? Bill

[mpavlak]

Running SQL Statements in a Batch Job

Two places to look before you leap! If you can run the STRSQL command on your i5 then you can also run the STRQM command. The interface takes some getting used to, but this allows you to create QMQRY objects and forms for creating great batch reports and running SQL in batch. The shop I am in started working with this years ago and now we have all of our reports and Business Intelligence ETL written in SQL's in Query manager. You can pass parameter's and dynamically construct SQL statemets, but are limited to a single SQL statement per QM object. The next place to look is iSeries Access for Web. This interface is the followon to Query manager and Query/400. Anything you do here is running in batch because of the way the subsystems are set up.