EXCSQL - Execute SQL Statements

Details

EXCSQL - a command to run SQL statements. This relies on Query Management Queries rather than IBM's Interactive STRSQL utility, so this should run fine on any recent AS/400 / iSeries. It's a more comprehensive version of the SQL engine in DBG/400 that supports output to display, report or outfile. SQL statements can be up to 825 characters in length, but you'll only see 512 if you prompt the command (that's an IBM limitation). It's easy to stick the command in a CL program, or build it up as a command string to execute through QCMDEXC in CL, RPG or whatever. There are no dependencies on any of my other packages, so you can get this going in no time, even if you don't have DBG/400 installed (though I can't think why you wouldn't want it as well ).

An extra feature has been added to this tool to make SQL WHERE matches case-insensitive. You may (or may not) be aware that interactive SQL (STRSQL) has an option to change the sort sequence used in character string comparisons. The same option is available on the CHGJOB command. Changing from the default *HEX sequence to *LANGIDSHR generally gives case-insensitive matches. I've implemented this as a CHGJOB SRTSEQ() inside the EXCSQL CL control pgm, which resets to the job's prior value after the SQL statement completes (successfully or otherwise). You can of course do a manual CHGJOB before running EXCSQL, but then you'd have to remember to set it back again afterwards I read this sort sequence tip in the 29th Nov '01 Newswire Club Tech iSeries DB2 UDB & SQL/400 Newsletter - you might need to be a member to view it, though Associate membership is free.

Another extra feature (added 29/3/02) adds CPYF's *UPDADD member option to EXCSQL's *OUTFILE support. As QMQRY doesn't support this option I've implemented it as a CPYF in the main routine. If the outfile you've selected is an existing keyed physical, then matching key values in the result set from the SQL statement will update your data. New unique keyed data will be added to your file. I've not had the opportunity to give this enhancement a real thorough going over, so if you do find any problems please let me know.

23/09/08. There was a bug in the code that broke up the SQL request for use by QMQRY where multiple embedded blanks could be truncated. The code has been fixed - if you find any more issues let me know.

Download

excsql.zip (16Kb).

This is packaged in Craig Rutledge's XMLGEN xml installer. See installation instructions for details.