Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Create view statement from SYSIBM.SYSVIEWS

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
andrea

New User


Joined: 08 Jun 2012
Posts: 8
Location: Italia

PostPosted: Thu Aug 02, 2018 8:51 pm    Post subject: Create view statement from SYSIBM.SYSVIEWS
Reply with quote

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 http://ibmmainframes.com/viewtopic.php?t=32427 .
Back to top
View user's profile Send private message

Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1300
Location: Israel

PostPosted: Sun Aug 05, 2018 8:57 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1300
Location: Israel

PostPosted: Mon Aug 06, 2018 6:13 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1300
Location: Israel

PostPosted: Thu Aug 09, 2018 8:10 pm    Post subject:
Reply with quote

Did that help ?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1567
Location: Andromeda Galaxy

PostPosted: Thu Aug 09, 2018 10:30 pm    Post subject:
Reply with quote

Some people just get the data and vanish..

Marso,

Definitely you did a great job and that will be useful at least for me icon_smile.gif
Back to top
View user's profile Send private message
andrea

New User


Joined: 08 Jun 2012
Posts: 8
Location: Italia

PostPosted: Fri Aug 10, 2018 4:40 am    Post subject:
Reply with quote

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) icon_confused.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How can we create a flat file in JAVA... jasorn Java & MQSeries 2 Wed Aug 08, 2018 3:08 pm
No new posts Need Help to Create 4 Output Records ... satish.ms10 DFSORT/ICETOOL 5 Thu Jul 19, 2018 1:55 am
No new posts SYSIBM Catlog tables to see DDL chang... rakesh17684 DB2 2 Fri Jun 08, 2018 3:43 am
No new posts Compute statement with Cobol Z/os 5.2 Chetan Kumar COBOL Programming 2 Fri May 11, 2018 8:02 pm
No new posts Need better approach to create a sort... Vamshi Veludandi DFSORT/ICETOOL 8 Thu Mar 08, 2018 5:59 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us