RtvSql-Retrieve SQL source statement

page d'accueil
Boite à outils

Cette page a été mise à jour le 14 septembre 2009
Contact

Welcome page
Tools box

Versionning 

last version : How to upload to my 400

CHGPFM FILE(JPLTOOLS) MBR(RTVSQL ) SRCTYPE(CMD ) EXPDATE(*NONE) SHARE(*NO) TEXT('Retrieve DDL source for SQL objects ') 
CHGPFM FILE(JPLTOOLS) MBR(RTVSQLFM ) SRCTYPE(DSPF ) EXPDATE(*NONE) SHARE(*NO) TEXT('Retrieve SQL source in DDL for SQL objects ')
CHGPFM FILE(JPLTOOLS) MBR(RTVSQLHP ) SRCTYPE(PNLGRP ) EXPDATE(*NONE) SHARE(*NO) TEXT('Help for RTVSQL ')
CHGPFM FILE(JPLTOOLS) MBR(RTVSQLR ) SRCTYPE(RPGLE ) EXPDATE(*NONE) SHARE(*NO) TEXT('Retrieve SQL source in DDL for SQL objects ')
CHGPFM FILE(JPLTOOLS) MBR(SQLR0100 ) SRCTYPE(RPGLE ) EXPDATE(*NONE) SHARE(*NO) TEXT('sqlr0100 format for QSQGNDDL ')
CRTCMD CMD(RTVSQL) PGM(*LIBL/rtvsqlr) SRCFILE(JPLTOOLS) SRCMBR(RTVSQL) REPLACE(*YES)
CRTDSPF FILE(TVSQLFM) SRCFILE(JPLTOOLS) SRCMBR(RTVSQLFM) RSTDSP(*YES) REPLACE(*YES)
CRTPNLGRP PNLGRP(RTVSQLHP) SRCFILE(JPLTOOLS)
CRTSRCPF FILE(QTEMP/RTVSQLP) RCDLEN(112) TEXT('Retrieve SQL source in DDL for SQL objects')
CRTRPGMOD MODULE(RTVSQLR) SRCFILE(JPLTOOLS) OPTION(*NODEBUGIO *SRCSTMT *SHOWCPY *SECLVL *EXPDDS) DBGVIEW(*ALL)
CRTPGM PGM(RTVSQLR) BNDDIR(QC2LE) ACTGRP(*CALLER) USRPRF(*USER)
Avez vous déjà utilisé le "SQL Visual Explain" de iSeries Navigator ? 
Non ? vous devriez SQL Visual Explain
Oui ? Alors vous avez surement trouvé très pratique la possibilité de créer l'index qui manque pour diminuer le temps de réponse de vos requêtes.

Jusque là, tout va bien. Mais dès qu'il faut  reporter la correction sur plusieurs machines de production, il serait plus confortable de disposer du code source de cet index.

Cet outil permet d'interroger DB2 pour qu'il exporte le code source (DDL pour Data Description Langage) d'un objet SQL. DB2 sait reconstituer le DDL des ALIAS, CONSTRAINT, FUNCTION, INDEX, PROCEDURE, SCHEMA, TABLE, TRIGGER, TYPE, VIEW 

Did you already use the " SQL Visual Explain " of iSeries Navigator ? 
No? you would have to. here is a link to find SQL Visual Explain
Yes? Then you surely found it convenient to create the missing indexes to lessen your response time

Until there, all goes well. But as soon as it is necessary to report the correction on several production machines, it would be more comfortable to have the DDL of this index

This tool permits to ask DB2 to export the code source (DDL for Data Description Language) of a SQL object. DB2 knows how to rebuild the DDL of the ALIAS, CONSTRAINT, FUNCTION, INDEX, PROCEDURE, SCHEMA, TABLE, TRIGGER, TYPE, VIEW,, 

asking DB2 for DDL of object QSYS2/SYSTABLES *FILE


RTVSQLR Retrieve SQL source statement jp jpltools Object_Name SYSTABLES
Object_Library QSYS2
Object_Type VIEW ALIAS CONSTRAINT FUNCTION INDEX PROCEDURE SCHEMA TABLE
TRIGGER TYPE VIEW
Source_File_Name RTVSQLP
Source_File_Library JPL
Source_File_Member AAAAAAAAAA create member 1 0-1 Replace member 1 0-1

Statement_Formatting 0 0-1 Severity_Level
Date_Separator / /.,- Date_Format ISO ISO EUR JIS USA MDY DMY YMS JUL
Time_Separator : :., Time_Format ISO ISO EUR JIS USA HMS
Naming_Option SYS SQL-SYS Decimal_Point , .,
Standards_Option 0 012 Drop_Option 1 01 Message_Level
Comment_Option 1 Label_Option 1 01 Header_Option 1 01



F3=Quitter F12=Abandonner F5=Rafraichir

and the corresponding DDL


-- Générer SQL
-- Version : V5R2M0 020719 -- Base données relation : QTEMP
-- Option normes : DB2 UDB AS/400
DROP VIEW QSYS2/SYSTABLES ;
CREATE VIEW QSYS2/SYSTABLES (
TABLE_NAME FOR COLUMN NAME ,
TABLE_OWNER FOR COLUMN CREATOR ,
TABLE_TYPE FOR COLUMN "TYPE" ,
COLUMN_COUNT FOR COLUMN COLCOUNT ,
ROW_LENGTH FOR COLUMN RECLENGTH ,
TABLE_TEXT FOR COLUMN "LABEL" ,
LONG_COMMENT FOR COLUMN REMARKS ,
TABLE_SCHEMA FOR COLUMN DBNAME ,
LAST_ALTERED_TIMESTAMP FOR COLUMN ALTEREDTS ,
SYSTEM_TABLE_NAME FOR COLUMN SYS_TNAME ,
SYSTEM_TABLE_SCHEMA FOR COLUMN SYS_DNAME ,
FILE_TYPE FOR COLUMN FILETYPE ,
BASE_TABLE_SCHEMA FOR COLUMN TBDBNAME ,
BASE_TABLE_NAME FOR COLUMN TBNAME ,
BASE_TABLE_MEMBER FOR COLUMN TBMEMBER ,
SYSTEM_TABLE FOR COLUMN SYSTABLE ,
SELECT_OMIT FOR COLUMN SELECTOMIT ,
IS_INSERTABLE_INTO FOR COLUMN INSERTABLE ,
IASP_NUMBER FOR COLUMN IASPNUMBER )
AS
SELECT DBXLFI, VARCHAR(STRIP(DBXOWN, TRAILING), 128), SUBSTR(DBXATR, 1, 1), INTEGER(DBXNFL), INTEG
ER(DBXRDL), DBXTXT, DBXREM, DBXLB2, DBXATS, DBXFIL, DBXLIB, DBXTYP, DBXALB, DBXAFL, DBXAMB, DBXSYS,
DBXSO, CASE DBXINSERT WHEN 'Y' THEN 'YES' ELSE 'NO' END, DBX_PASP FROM QSYS/QADBXREF WHERE DBXREL =
'Y' AND DBXATR <> 'IX' ;
LABEL ON COLUMN QSYS2/SYSTABLES
( TABLE_NAME TEXT IS 'Long file name' ,
TABLE_TEXT TEXT IS 'File text' ,
LONG_COMMENT TEXT IS 'Long file description' ,
TABLE_SCHEMA TEXT IS 'Library name' ,
LAST_ALTERED_TIMESTAMP TEXT IS 'File change timestamp' ,
SYSTEM_TABLE_NAME TEXT IS 'File name' ,
SYSTEM_TABLE_SCHEMA TEXT IS 'Library name' ,
FILE_TYPE TEXT IS 'D-data file, S-source file' ,
BASE_TABLE_SCHEMA TEXT IS 'Alias Library name' ,
BASE_TABLE_NAME TEXT IS 'Alias File name' ,
BASE_TABLE_MEMBER TEXT IS 'Alias Member name' ,
SYSTEM_TABLE TEXT IS 'Y-System File' ,
SELECT_OMIT TEXT IS 'Y-S/O logical, D-DYNSLT, N-Not S/O Logical' ,
IASP_NUMBER TEXT IS 'Primary ASP' ) ;

remarks :

this tool permits you to retrieve the ddl of an index as well as a view from a logical file when iSeries navigator permits only to retrieve the view.

the QSQGNDDL api don't permit to retrieve the index from a physical file if it is not unique. (when unique, it retrieve the primary key). Problem submitted to Rochester.

in V5R2, CONCAT expression for logical files are not retrieved correctly. OK in V5R3

The source code is provided as an open source tool under the GNU General Public License, version 2.  In summary, this license specifies:

By clicking the download link you acknowledge and agree to the terms of the license.

Download the (zipped) SAVF containing the JPLTOOLS : the source file, the message file, the bnddir

THIS TOOLBOX IS PROVIDED ''AS IS'' AND ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE PROVIDER OR ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS TOOLBOX, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.