RtvSql-Retrieve SQL source statement
SQL Visual Explain tool, which comes with iSeries Navigator, is a very handy tool that helps you to improve the performance of your SQL statements. If you haven't had the opportunity to try it, I highly recommend it. You can find the Visual Explain tool here.
If you have used it, I'm confident that you found it a very convenient tool that is able to graphically explain an SQL sentence, to highlight overload process (i.e. when a subquery works thousands of records to return a dozen), to suggest missing index, and to create that index that misses to decrease your request answer time.
Up until that point, all goes well. But as soon as it is necessary to report the correction on several production machines, I believe it would be more convenient to have the Data Description Language (DDL) of that index.
The RTVSQL tool allows you to have DB2 export the source code DDL of an SQL object.
DB2 has the ability to rebuild the DDL of the alias, constraint, function, index, procedure, schema, table, trigger, type and view.
Here's a sample of DB2 requesting a DDL of object QSYS2/SYSTABLES *FILE:
And the corresponding DDL
Here's the last version: Right click and save as an IFS file.
RTVSQL-CMD.TXT
RTVSQLFM-DSPF.TXT
RTVSQLHP-PNLGRP.TXT
RTVSQLR-RPGLE.TXT
And then…
CPYFRMSTMF FROMSTMF('/IfsDir/Sample.txt') TOMBR('/qsys.lib/mylib.lib/qxxxsrc.file/sample.mbr') MBROPT(*REPLACE) STMFCODPAG(*PCASCII) CVTDTA(*AUTO) DBFCCSID(*FILE) TABEXPN(*NO)
CHGPFM FILE(mylib/QxxxSRC) MBR(sample) SRCTYPE(xxx) TEXT('description')
Note: The above source code is maintained at Jean-Paul Lamontre's site. If you have questions regarding this tip, visit Lamontre's site for contact information.
