| TechTip: RUNSQLSTM Is Bigger and Better in V6R1 |
|
|
|
| Tips & Techniques - Programming | ||||||||||||||||||||||||||||||||
| Written by Kent Milligan | ||||||||||||||||||||||||||||||||
| Friday, 25 April 2008 | ||||||||||||||||||||||||||||||||
|
Support MC Press - Visit Our Sponsors
Search Sponsor
IBM continues to make life easier for SQL developers.
As SQL becomes more widely used by i5/OS developers, it's important for IBM to continue to enhance the tools and utilities supporting the SQL interface to increase the adoption of SQL. The Run SQL Statements (RUNSQLSTM) system command is one of those SQL utilities that is enhanced in V6R1 to make life easier for SQL developers.
RUNSQLSTM is widely used by developers for executing SQL scripts that create the database objects or SQL routines for their applications. In prior releases, this command was limited by the fact that it only supported source members that were 80 characters wide. Support for very large SQL scripts was also hampered by the fact that the source member had to be less than 16 MB in length. These limitations no longer exist as a result of the V6R1 enhancements.
The RUNSQLSTM now includes support for stream files. Stream files, by their nature, do not have a maximum line or record length. This means that developers creating SQL scripts in a stream file don't worry about running out of room on a line when typing in and formatting their SQL statements. In addition, the maximum size of a stream file is 1 TB, which is quite helpful when accommodating SQL scripts that are larger than 16 MB in size. Here's an example of the RUNSQLSTM command used to execute SQL statements stored in a stream file.
RUNSQLSTM SRCSTMF('/home/tests/script1.txt')
Those i5/OS developers who prefer to use source physical file members can also code wider SQL statements in V6R1. While it's not the unlimited record length that the stream file support offers, the RUNSQLSTM now supports a MARGINS parameter to allow developers to override the default length of 80 characters. The MARGINS parameter supports a maximum length of 32754. Here's an example of using this new parameter on the RUNSQLSTM command to accommodate wider SQL statements (120 characters per line in this example).
RUNSQLSTM SRCFILE(MYLIB/MYSRC) SRCMBR(TEST1) MARGINS(120)
The RUNSQLSTM command offers increased flexibility in V6R1 by adding support for CL commands. This new support mirrors the CL command support that's available with the System i Navigator Run SQL Scripts interface. The CL command in the SQL script needs to be prefixed with "CL:" and then a semi-colon after the CL command string. Here's an example of a script including CL and SQL statements that can be processed by RUNSQLSTM in V6R1.
CREATE TABLE tab1 (C1 INT, C2 CHAR(5)) ; CL: CRTDTAARA DTAARA(DTA1) TYPE(*CHAR) LEN(5); CREATE TABLE tab2 (col1 CHAR(1), col2 INT); CREATE INDEX ix2 ON tab2(col2);
No special parameters are needed on the RUNSQLSTM command to enable the CL command support. |
||||||||||||||||||||||||||||||||
|
Last Updated ( Thursday, 17 April 2008 ) |
||||||||||||||||||||||||||||||||
| efnkay |
Re:TechTip: RUNSQLSTM Is Bigger and Better in V6R1
Apr 30 2008 05:59:27 Other than maintaining the script member (without compiling) which is one sell-point for RUNSQLSTM...You do have the DECLARE GLOBAL TEMPORARY TABLE stmt which can and does use a SELECT statement to create temporary files (in Qtemp) and the script can have multiple SQL stmts...You can create a temp table, populated or not, insert into it, update it, delete from it...Am I missing anything you can do in interactive SQL otherwise...???
|
#121843 |
| strongdl |
Re:TechTip: RUNSQLSTM Is Bigger and Better in V6R1
Apr 28 2008 18:56:49 To execute CL commands in a select statement, we set up an SQL function to call the QCMDEXC API. I developed it on company time, so I'll just post this exerpt to get you started. The return value in my function is an error code, in case one is sent, or the statement executed if it was successful.
CREATE FUNCTION ROUTINES.QCMDEXC ( ... BEGIN DECLARE CMDLEN DEC ( 15 , 5 ) ; SET RETCMD = CMD ; SET CMDLEN = LENGTH ( TRIM ( CMD ) ) ; CALL QCMDEXC ( CMD , CMDLEN ) ; ... To execute the function you do something like: select QCMDEXC('DLTF '||trim(ODOBLM)||'/'||trim(ODOBNM)) from DLTLIST |
#121837 |
| tlaza |
Re:TechTip: RUNSQLSTM Is Bigger and Better in V6R1
Apr 26 2008 01:29:38 What I would like to see is the ability to code an SQL statement directly in the CL command using a literal or a variable. I realize this probably wouldn't work for complicated queries. But it would be nice to to code a short, relatively simple SQL statement without having to maintain a script in a different location. This would be helpful in situations where I need to create a temporary work file or insert records into an existing work file and then print a report from it.
|
#121836 |
 |
Re:TechTip: RUNSQLSTM Is Bigger and Better in V6R1
Apr 26 2008 01:04:54 RUNSQLSTM is a great idea that is "Dead on Arrival" when placed into practice. V6 does not look as if it is in meaningful "Upgrade Mode." I would like to see COMMON resolutions and a lot, a lot of customer comment, feedback and the above-mentioned resolutions tied-in with IBM announcements.
Finally, some upgrades are announced but not the ones that are relevant to the central thrust of SQL development in the Information technology Industry. Many basic SQL commands do not work on RUNSQLSTM. I notice from some responses how fragmented the user-base is. Many products announced, for example, do not support REXX or CODE400. Key upgrades have been deprecated for Domino IFS support from the System i. We see a ghetoization of current users where cutting-edge development is being done in windows and the scatter-shot IBM approach in the i-World is missing the point: IBM has to become integrated, focused. I hope Bill Zeitler's managers can get the upgrade paths into focus. With that, then maybe more IBM clients will stay current with the new Releases. V6 is irrelevant in my shop. Thank you, --John deCoville, Pinal County Government |
#121835 |
| DougCMH |
TechTip: RUNSQLSTM Is Bigger and Better in V6R1
Apr 25 2008 19:11:12 This thread discusses the Content article: TechTip: RUNSQLSTM Is Bigger and Better in V6R1
Oh look! REXX! I sometimes think REXX is a better alternative to RUNSQLSTM. REXX has all along allowed a mixture of SQL and CL, not to mention it's own syntax. And, it allows parameters, unlike RUNSQLSTM. It makes for a more dynamic solution. Not sure about performance, but REXX has been pretty danged fast for me. |
#121834 |
