I need to extract the whole create view statement (with all where condition: both on join colums and in data) to recreate some views in a new environment.
Since STATEMENT column in SYSIBM.SYSVIEWS is a CLOB, I actually got a rough result with the following query:
SELECT NAME, CAST(STATEMENT AS VARCHAR (32000) )
FROM SYSIBM.SYSVIEWS
WHERE NAME = view_name
but that way I just obtain a very long row which has to be manually splitted.
//*---------------------------------------------------
//* LIST SYSVIEWS
//*---------------------------------------------------
//SELECT EXEC PGM=SORT,PARM='DB2=DSN'
//SYSOUT DD SYSOUT=*
//SORTDBIN DD *
SELECT CAST( '*** ' || NAME AS CHAR(78) ),
CAST( STATEMENT AS VARCHAR (32000) )
FROM SYSIBM.SYSVIEWS
WHERE NAME LIKE 'SYS%'
//SORTOUT DD DSN=&&VIEWS,DISP=(NEW,PASS),
// SPACE=(TRK,(25,15),RLSE),DCB=(RECFM=FB,LRECL=32080)
//SYSIN DD *
SORT FIELDS=COPY
//*---------------------------------------------------
//* REFORMAT VIEWS
//*---------------------------------------------------
//FORMAT EXEC PGM=SYNCTOOL
//TOOLMSG DD SYSOUT=*
//SSMSG DD SYSOUT=*
//INPUT DD DISP=(OLD,DELETE),DSN=&&VIEWS
//OUTPUT DD SYSOUT=*
//TOOLIN DD *
RESIZE FROM(INPUT) TO(OUTPUT) TOLEN(80) USING(CTL1)
//CTL1CNTL DD *
OUTFIL FNAMES=OUTPUT,
OMIT=(1,5,CH,EQ,X'0000000000')
Notes:
Use any available utility at your site to fetch the rows.
NAME defined with a length of 78 so the length of the VARCHAR field becomes part of the 1st 80 characters.
When I ran this job, my output was:
Code:
*** SYSTABLES .Ï
CREATE VIEW RUNSTATS.SYSTABLES AS SELECT * FROM SYSIBM.SYSTABLES WHERE CREATOR I
N(CURRENT SQLID,USER) WITH CHECK OPTION.........................................
*** SYSTABLESPACE ."
CREATE VIEW RUNSTATS.SYSTABLESPACE AS SELECT * FROM SYSIBM.SYSTABLESPACE WHERE C
REATOR IN(CURRENT SQLID,USER) WITH CHECK OPTION.................................
*** SYSTABSTATS .`
CREATE VIEW RUNSTATS.SYSTABSTATS AS SELECT * FROM SYSIBM.SYSTABSTATS WHERE OWNER
IN(CURRENT SQLID,USER) WITH CHECK OPTION.......................................
*** SYSCHECKDEP .¢
CREATE VIEW SMTDB#IQ.SYSCHECKDEP AS ( SELECT * FROM SYSIBM . SYSCHECKDEP )......
*** SYSCHECKS .ã
CREATE VIEW SMTDB#IQ.SYSCHECKS AS ( SELECT * FROM SYSIBM . SYSCHECKS )..........
Notes:
Lines starting with '***' contain the name of the view.
To the right we can see the length of the VARCHAR. Should be no trouble removing it.
Other lines are the view definition. They are cut at a length of 80.
Last line is padded with low-values. Should be easy to replace with spaces.
Sorry, I didn't saw your reply because I'm on holiday this week and I can't check your last suggestion for the next two weeks.
Anyway it seems to me that the last outcome of your job is the create view written as plain text and with new lines at the end of the 80 characters written in the TOLEN parameter, regardless of the content of the text.
In this case a better result can be obtained tuning the "EDIT" field in the FORM.MAIN of QMF.
In both cases (your suggestion as it seems to me and my QMF test) I have to manually split each line to get the original DDL (or something very similar to it).
And (I didn't wrote it) I need to do this work for many views.
What I need as outcome is something similar to:
CREATE VIEW MYPROJECT.VIEWTEST AS
SELECT A.col1,
a.col2,
a.col3,
b.col4,
.......
c.col5
FROM ( SELECT E.col1,
F.col2
G.col3
...
A.col5 FROM ( SELECT H.colx,
...
FROM MYPROJECT.TAB1 EE
, MYPROJECT.TAB2 FF
...
WHERE EE.key1 = FF.key2
AND
.....
(this is a little example from a 275 statement CREATE VIEW I got in QMF, with high use of "select from select", inner, outer and left joins)
Thanks again for your suggestions, but I coundn't use them because I need an SQL-only solution: I need something I can insert into a QMF proc I use to retrieve the DDLs to recreate a DB on another DB2 instance when I don't have the original DDLs.
.jpg)
