|
View previous topic :: View next topic
|
| Author |
Message |
andrea
New User
Joined: 08 Jun 2012
Posts: 9
Location: Italia
|
|
|
|
Hi all.
I have a similar need for views.
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.
Do you have some suggestion?
Thanks in advance.
P.S.: DB2 version is 11.1
EDIT : Topic split from ibmmainframes.com/viewtopic.php?t=32427 . |
|
| Back to top |
|
 |
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1356
Location: Israel
|
|
|
|
I have tried this:
| Code: |
//*---------------------------------------------------
//* 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.
Not perfect but it is a start... |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1356
Location: Israel
|
|
|
|
I have replaced SORT by DSNTIAUL and got the same result (just in case you don't have SYNCSORT):
| Code: |
//*---------------------------------------------------
//* LIST SYSVIEWS
//*---------------------------------------------------
//SELECT2 EXEC DSNTIAUL,SYSTEM=DSN,SQLPARM=SQL
//SYSREC00 DD DSN=&&VIEWS,DISP=(NEW,PASS),
// SPACE=(TRK,(25,15),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT CAST( '*** ' || NAME AS CHAR(78) ),
CAST( STATEMENT AS VARCHAR (32000) )
FROM SYSIBM.SYSVIEWS
WHERE NAME LIKE 'SYS%';
//*---------------------------------------------------
//* 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') |
|
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1356
Location: Israel
|
|
|
|
| Did that help ? |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Some people just get the data and vanish..
Marso,
Definitely you did a great job and that will be useful at least for me  |
|
| Back to top |
|
|
andrea
New User
Joined: 08 Jun 2012
Posts: 9
Location: Italia
|
|
|
|
| Marso wrote: |
| Did that help ? |
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)  |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1209
Location: Bangalore,India
|
|
|
|
| WOW Marso ... I just luv your solution... |
|
| Back to top |
|
|
andrea
New User
Joined: 08 Jun 2012
Posts: 9
Location: Italia
|
|
|
|
| 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. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
