page d'accueil
Boite à outils

Welcome page
Tools box

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 

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,, 

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 

-- 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' ) ;