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
 
Extracting DDL for all list of interestred table

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

New User


Joined: 13 Oct 2017
Posts: 26
Location: India

PostPosted: Mon May 21, 2018 4:08 pm    Post subject: Extracting DDL for all list of interestred table
Reply with quote

Hi ,

We are trying to create virtual ZOS environment on D&T IBM ZOS product.
To do same, we need the DDL for all interested tables. I am able to see DDL for individual table through BMC tool. Is anyway I can see and extract DDL for all interested tables in one go.
Back to top
View user's profile Send private message

vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1590
Location: Chennai

PostPosted: Thu May 24, 2018 5:47 am    Post subject:
Reply with quote

Since you say you are using BMC Tools, here's how you list all DDL.

Go to CATALOG MANAGER

Code:
Action       0. (L) List using customizable lists                             
             1. (S) Search for catalog objects                                 
             2. (C) Create objects                                             
             3. (G) Grant privileges                                           
             4. (O) CATALOG MANAGER options processing                         
             5. (D) DB2 Commands                                               
             6. (M) Maintain logs menu                                         
             7. (Q) List SQL for edit & execution                             
             8. (R) About this Release/CATALOG MANAGER Quick Reference         
                                                                               
Obj type  TB 11. DB Database  16. TS Tablespace   21. PL Plan   26. ST Strings
             12. SG Stogroup  17. SU SysPrivUser  22. AL Alias  27. LO Location
             13. TB Table     18. SY Synonym      23. US User   28. CK Checks 
             14. VW View      19. PG Package      24. CO Column 29. PR Procs   
             15. IX Index     20. CI Collection   25. DM DBRM   30. XT Aux Tabl
Qualifier %.DOGE_WOW%     <------- Enter your wild card for the tables.   


Hit Enter

Code:
DOGE-R --------------------------  TABLE LIST  -------------------------------
Command ===> DDL BATCH ALL                                    Scroll ===> CSR 
                                                                            01
CMD will show commands for this list.  Type command and press ENTER           
Lists: ACCTB AL CA CD CK CL CO CP C2 DB DP DS DT FK IC IM IS IX KC KU LK MK   
LIKE %.DOGE_WOW%                                                           
C   Table Name                     Database Tblspace ColsPK Type  Rows  Pages 
----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v----
    SUCHNAME.DOGE_WOW            SUCHNAME  DOG0127    2  1 T       46      1 
    SUCHNAME.DOGE_WOW_USG        SUCHNAME  DOG0128    7  6 T     1341     10 
******************************  BOTTOM OF DATA  *******************************


Hit enter, this would show up a message,

Code:
  BMC14651 Use BATCH command to generate JCL for all saved commands.


Code:
DOGE-R --------------------------  TABLE LIST  -------------------------------
Command ===> BATCH                                            Scroll ===> CSR 
                                                                            01
CMD will show commands for this list.  Type command and press ENTER           
Lists: ACCTB AL CA CD CK CL CO CP C2 DB DP DS DT FK IC IM IS IX KC KU LK MK   
LIKE %.DOGE_WOW%                                                           
C   Table Name                     Database Tblspace ColsPK Type  Rows  Pages 
----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v----
*DDL FA01#0.DOGE_WOW            SUCHNAME  DOG0127    2  1 T       46      1 
*DDL FA01#0.DOGE_WOW_USG        SUCHNAME  DOG0128    7  6 T     1341     10 
******************************  BOTTOM OF DATA  *******************************


Code:
DOGE --------------------  CATALOG MANAGER Batch Job  ------------------------
Command ===>                                                                   
                                                                               
 JCL Dataset  . . .  'WELLS.PDS.FB80(GENJCL)'        <--- Location where JCL will be generated, pre-allocated through ISPF                         
                                                                               
Set JCL options. .  N     (Y/N - Change options for creating JCL)             
Build Job  . . . .  Y     (Y/N - Create JCL, save in JCL dataset)             
Edit Dataset . . .  Y     (Y/N - Edit JCL dataset)                           
Submit . . . . . .  N     (Y/N - Submit JCL dataset)                         
                                                                             
-------------------------------  Job Options  -------------------------------
                                                                             
Ssid . . . . . . .  DOGE     The ssid of the DB2 which batch is to connect to
Default options  .  ACTDOPDD The name of the default options to use in batch 
HDDL output dsn     &ZUSER..&SSID..HDDL                                                                                                                     



Hit Enter,

This should generate a job with
Code:
//CATBATCH EXEC PGM=ACTBMAIN,

scroll to the bottom (M+F8)

Check the SYSIN DD * cards, it should be something like below with the interested tables.

Code:
//SYSIN    DD *                   
DSN=WELLS.DOGE.HDDL       
DDL TB SUCHNAME.DOGE_WOW       
DDL TB SUCHNAME.DOGE_WOW_USG   


Submit the job and ACTPRINT SYSOUT would have DDL of all the tables.

Hope it helps.
Vasanth.S
Back to top
View user's profile Send private message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 26
Location: India

PostPosted: Thu May 24, 2018 4:15 pm    Post subject:
Reply with quote

Thanks Vasantha for inputs.

I am able to run first few steps successfully But getting an error 'Error in Edit' on screen after hitting batch option.

Code:
DD7G --------------------  CATALOG MANAGER Batch Job  ---------- ERROR IN EDIT

Command ===>

 

JCL Dataset  . . .  'QXS9894.QXS9894.BMCCAT.JCL(JCL)'

 

 

Set JCL options. .  N     (Y/N - Change options for creating JCL)

Build Job  . . . .  *     (Y/N - Create JCL, save in JCL dataset)

Edit Dataset . . .  *     (Y/N - Edit JCL dataset)

Submit . . . . . .  Y     (Y/N - Submit JCL dataset)

 

--------------------------------  Job Options  -------------------------------

 

Ssid . . . . . . .  DD7G     The ssid of the DB2 which batch is to connect to

Default options  .  ACTDDD7G The name of the default options to use in batch

HDDL output dsn     &ZUSER..&SSID..HDDL




Obvious thing I can see, its taking/creating own dataset.

So I tried using existing PDS but getting an below error.

Code:
IKJ56265I INPUT DATA SET  NOT USABLE+

IKJ56265I THE DATA SET IS EMPTY
Back to top
View user's profile Send private message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 26
Location: India

PostPosted: Thu May 24, 2018 6:59 pm    Post subject:
Reply with quote

Many thanks Vasanthaz ..Its working fine now..
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1590
Location: Chennai

PostPosted: Thu May 24, 2018 10:48 pm    Post subject:
Reply with quote

Code:
 'Error in Edit'


I did get that error so that's why mentioned the below on first post with the DCB :-)
Code:
 JCL Dataset  . . .  'WELLS.PDS.FB80(GENJCL)'        <--- Location where JCL will be generated, pre-allocated through ISPF


Glad you got it working. BTW it is just Vasanth. Vasantha sounds feminine.. Yuck!
Back to top
View user's profile Send private message
Ashishpanpaliya

New User


Joined: 13 Oct 2017
Posts: 26
Location: India

PostPosted: Fri May 25, 2018 1:30 pm    Post subject:
Reply with quote

Really apologies for misspelling your name. Thanks again for your response.
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 Facing Issue in Table Reorg satish.ms10 DB2 1 Tue Aug 07, 2018 2:44 am
No new posts Utility to list Data Set Information Auryn JCL & VSAM 4 Tue Jul 17, 2018 5:03 pm
No new posts LIST NODE DIRECTORY - db2 clp - tryin... elixir1986 IBM Tools 1 Sat Jul 07, 2018 6:15 pm
No new posts SQL IN predicate using COBOL table jerryte DB2 16 Fri Jun 29, 2018 10:30 pm
No new posts Varying List Select SQL Query In DB2 ... NikhilGuptaGaya DB2 5 Wed Jun 27, 2018 11:01 pm

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