On 14-Mar-2015 08:59 -0500, Darryl Freinkel wrote:
Does anyone know what the maximum width a source for SQL can be?
~32K bytes (minus 12), same as for any Source Physical File (SRC-PF)
Record Format. Apparently however, the unstated release of interest is
v5r4 [as inferred from the other recent post]. On v5r4 the effective
limit is 80 bytes, because by then there is not yet a parameter on the
Run SQL Statement (RUNSQLSTM) command for the SQL to process any data
beyond the /margin/ of 80 bytes. Since V6R1 there is the Source Margins
(MARGINS) parameter:
<
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_61/cl/runsqlstm.htm>
_Run SQL Statements_ (RUNSQLSTM)
"...
_Source margins_ (MARGINS)
Specifies the part of the source input record that contains source text.
The left margin is always position 1 of the input record. The right
margin defaults to 80.
If the Source stream file (SRCSTMF) parameter is specified, margins are
ignored. ...
..."
I need to isolate which statements are causing slow run times and
which part of a script is failing. Running the script in iNavigator
does not provide enough feedback even when including debug
facilities.
Using the RUNSQLSTMT command provides a nice audit trail which I want
to use.
As I recall, the same class of debug messages that would appear in a
batch or interactive joblog that issued the system-supplied RUNSQLSTM
command, should appear in the joblog of the server job that processes
the statement(s) from the Run SQL database feature of iNav.
I ask the question because my scripts go out to 200 characters wide
and I do not want to have to reformat a ton of code. If I could just
paste the code into RDI and then run it, we would all win.
Deferring to an alternate script processor [than RUNSQLSTM] or
reformatting the statements, are likely the only options :-( on v5r4.
Again, I am not sure why the debug feedback mechanism via the Run SQL is
insufficient; at least when deferring to the joblog of the server.
The following QSHell invocation should be able to take as input a
stream file named longSQL [in the current directory], and place the
wrapped [at 80-bytes] stream data records into the member named ONLY of
the source file named SRCLEN80; the database source physical file would
have been previously created with CRTSRCPF RCDLEN(92) MBR(ONLY) into a
permanent library in *LIBL:
qsh cmd('cat ./longSQL | rFile -wlQ "srclen80(only)"')
For reference:
IBM i V5R4-> i5/OS Information Center, Version 5 Release 4->
Programming-> Development tools-> Qshell-> Utilities-> Working with
files and directories-> Rfile
<
http://www.ibm.com/support/knowledgecenter/ssw_i5_54/rzahz/rfile.htm>
Rfile - Read or write record files
After that wrapping, copying the wrapped records into fixed-length
database records, the statements in the source should be capable of
being run instead, using RUNSQLSTM; e.g., after the above QSH request
successfully copies the wrapped data to the SRC-PF:
RUNSQLSTM SRCFILE(*LIBL/SRCLEN80) SRCMBR(ONLY) /* COMMIT(as_rqd)
NAMING(as_rqd) ERRLVL(as_rqd) etc. */
midrange.com
