Re: Converting SQL scripts from iNavigator to Source files -- MIDRANGE-L

Darryl,

I missed the fact (from your other post) that you are on V 5.4, I think the
SRCSTMF parameter will not be there.
You can also use qshell to execute an IFS source.

QSH CMD('db2 -t -f ''/mgyrc/sqlsrc/modsrc/m00000116i.sql''
''MYDTALIB''')

But that will not give you the summary of RUNSQLSTM.

Dit you try Visual Explain in iNavigator?

Best regards,
Arci Simonse

2015-03-14 17:55 GMT+01:00 CRPence <CRPbottle@xxxxxxxxx>:

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. */

--
Regards, Chuck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.