Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Printing a formatted table and column list

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

Active User


Joined: 29 Oct 2010
Posts: 187
Location: Toronto, ON, Canada

PostPosted: Thu Aug 18, 2011 10:26 pm    Post subject: Printing a formatted table and column list
Reply with quote

I am an old fashioned programmer. I like to print things on paper so I can read and reference it. One thing I find useful is to print a list of the db2 tables and columns for the application I support. Below is the sql I use to produce this:
Code:
SELECT   SCOL.TBNAME, SCOL.NAME, SCOL.COLNO,
         CASE SCOL.KEYSEQ WHEN 0 THEN '' ELSE CHAR(KEYSEQ) END  AS KEYSEQ,
         SCOL.COLTYPE,
(SCALE)      ELSE        ' '      END   AS SCALE,
      CASE SCOL.COLTYPE
         WHEN 'DECIMAL' THEN STRIP(CHAR(SCOL.LENGTH-SCOL.SCALE)) || '.' || CHAR(SCOL.SCALE)
         ELSE CHAR(SCOL.LENGTH)
      END AS SIZE,      
         CASE SCOL.NULLS
               WHEN 'N' THEN ' '
               ELSE        NULLS     
         END   AS NULLS,
         SCOL.DEFAULT,
         CASE SCOL.PARTKEY_COLSEQ
          WHEN 0 THEN ''
          ELSE CHAR(PARTKEY_COLSEQ)
         END  AS PARTKEY
FROM     SYSIBM.SYSCOLUMNS SCOL,
         SYSIBM.SYSTABLES STAB
WHERE    SCOL.TBNAME=STAB.NAME
AND      SCOL.TBCREATOR=STAB.CREATOR
AND      SCOL.TBCREATOR = '?'
AND      STAB.TYPE = 'T'
ORDER BY 1, 3


I am looking for a way to print the results in a nice format. Ideally the table name should appear as a header. I could code a rexx to generate html script. I was hoping for something else. Currently I use an excel spreadsheet but it doesn't do headers.

Any ideas/suggestions?
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Thu Aug 18, 2011 10:33 pm    Post subject: Reply to: Printing a formatted table and column list
Reply with quote

I'm old enough to like things on print as well. However, when the print is on the other side of the ocean - how about letting us in on a secret: just a little peep at the output you are currently getting would help :-)

How are you getting the data into Excel? With delimeters? What do you mean by "it doesn't use headers"?
Back to top
View user's profile Send private message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 187
Location: Toronto, ON, Canada

PostPosted: Thu Aug 18, 2011 10:47 pm    Post subject:
Reply with quote

You should be able to run the sql to see a sample. Just substitute the table creator id into the sql.

To get the results into Exel I save the data as a text file. The fields are all fixed length so if viewing with a monospaced font (such as Courier) then everything lines up. When I open the file with Excel it will break it up into columns for me. Very handy. When I print the spreadsheet the table name appears on every row. I want it to appear once as a header.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Thu Aug 18, 2011 10:51 pm    Post subject: Reply to: Printing a formatted table and column list
Reply with quote

And I should be able to run the SQL why? I don't have any SQL on my PC, no access to mainframe or anything else with SQL on. It's why I asked :-)

Even if I did have, I'd prefer to see your output than to go with mine not knowing for certain that they are equivalent
Back to top
View user's profile Send private message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 187
Location: Toronto, ON, Canada

PostPosted: Fri Aug 19, 2011 12:02 am    Post subject:
Reply with quote

Below is a sample. I masked the table and column names. Sorry that the columns don't line up. Some values are blank if they are not relevant
Code:

Table   Column   COLNO   KEYSEQ   COLTYPE   SIZE   NULLS   DEFAULT   PARTKEY
Table1   Column1   1      CHAR   4      N   
Table1   Column2   2      CHAR   2      N   
Table1   Column3   3      CHAR   4      Y   
Table1   Column4   4      CHAR   4      Y   
Table2   Column1   1   1   DECIMAL   15.0      N   
Table2   Column2   2      CHAR   8      N   
Table2   Column3   3      TIMESTMP   10      Y   
Table2   Column4   4      CHAR   8      N   
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Aug 19, 2011 3:07 pm    Post subject:
Reply with quote

I use batch, run my sql under dsntep2.
though the original output is not pretty,
i use sort to reformat.

i have three jobs:
INDEXES
COLUMNS
SYSAUTH

I generate datasets from the sort and then use a panel to invoke a view
on these datasets.

Run the jobs anytime a table is changed, added (unfortunately we never delete tables - have about 800 now)

Found with the COLUMNs, I have the tables. just need to sort on colum seq number, table,
then delete all seq > 1.

SYSAUTH: this dataset gives me a 1 liner for every program table relationship with Select/Insert/Delete/Update indicator for the SQL in the program.
Used to be, one could do a search on copybooks that were INCLUDEd (SQL pre-compiler INCLUDE)
but, many of the rookie programmers were afraid to remove INCLUDEs
that were not used. So, short of the dbrm, we had no idea if a program
actually accessed a table or not - until I generated this listing.

you can always print the datasets if you have to have hard copy,
but since i am always cut&paste, i want it on the mainframe,
though one could cut&paste from the text (that was printed).

the >>>>>> indicates the contents of the member for that particular parm.


JCL for columns:
Code:

//XXXX79Q   JOB  BRENHOD,'DICK BRENHOLTZ',NOTIFY=BRENHOD,CLASS=I,
//          MSGLEVEL=(1,1),SCHENV=XXXX,MSGCLASS=X,REGION=9M
//*         RESTART=STEP030
//*
//*
//*--------------------------------------------------------------
//JOBLIB   DD DSN=SYSDB2.SDSNLOAD,
//            DISP=SHR
//*--------------------------------------------------------------
//*--------------------------------------------------------------
//STEP010  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//SYSTSIN  DD  DSN=XXXX.HUON36.UNIT.RUNLIB(DSNTEP2),
//             DISP=SHR
>>>>>>
>>>>>> DSN S(XXXX)
>>>>>> RUN PROGRAM(DSNTEP2)  PLAN(DSNTEP2)  -
>>>>>> LIB('SYSDB2.XXXX.RUNLIB.LOAD')
>>>>>> END
>>>>>>
//SYSPRINT DD  DSN=XXXX.WORK1.XREFCOLS,
//             DISP=(NEW,CATLG,DELETE),
//             UNIT=SYSDA,SPACE=(TRK,(1500,100),RLSE),
//             DCB=(RECFM=FBA,LRECL=133,BLKSIZE=0)
//SYSUDUMP DD  SYSOUT=*
//SYSPUNCH DD  SYSOUT=*
//SYSIN    DD  DSN=XXXX.BRENHOD.DB2SQL(COLUMNS),
//             DISP=SHR
>>>>>>
>>>>>>  SELECT SUBSTR(TBNAME,1,20) AS TBNAME
>>>>>>       , SUBSTR(NAME,1,16) AS COLNAME
>>>>>>       , COLNO
>>>>>>       , COLTYPE
>>>>>>       , LENGTH
>>>>>>       , SCALE
>>>>>>       , NULLS
>>>>>>  FROM SYSIBM.SYSCOLUMNS
>>>>>>  WHERE TBCREATOR = '<YOUR CREATOR NAME>'
>>>>>>  AND TBNAME NOT IN (
>>>>>>                     'DSN_FUNCTION_TABLE'
>>>>>>                    ,'DSN_STATEMENT_CACHE_AUX'
>>>>>>                    ,'DSN_STATEMENT_CACHE_TABLE'
>>>>>>                    ,'DSN_STATEMNT_TABLE'
>>>>>>                    ,'PLAN_HISTORY'
>>>>>>                    ,'PLAN_TABLE'
>>>>>>                    )
>>>>>>  ORDER BY 1,3
>>>>>>  WITH UR;
>>>>>>
/*
//STEP020  EXEC PGM=ICEMAN
//SORTIN   DD  DSN=XXXX.WORK1.XREFCOLS,
//             DISP=(OLD,DELETE,DELETE)
//SORTOUT  DD  DSN=XXXX.WORK2.XREFCOLS,
//             DISP=(NEW,CATLG,DELETE),
//             UNIT=SYSDA,SPACE=(TRK,(1500,100),RLSE),
//             DCB=(RECFM=FB,LRECL=96,BLKSIZE=0)
//SYSOUT   DD  SYSOUT=*
//SYSIN    DD  DSN=XXXX.BRENHOD.SORTPARM(INDEX1),
//             DISP=SHR
>>>>>>
>>>>>>   SORT FIELDS=COPY
>>>>>>   INCLUDE COND=(1,30,SS,EQ,C'_!')
>>>>>>   INREC PARSE=(%01=(STARTAFT=C'_! ',FIXLEN=118)),
>>>>>>         BUILD=(%01)
>>>>>>
//SORTDIAG DD  SYSOUT=*
//*
//STEP030  EXEC PGM=IEBGENER
//SYSPRINT DD  SYSOUT=A
//SYSUT1   DD  DSN=XXXX.XREF.HEADER.COLUMNS,
//             DISP=SHR
//         DD  DSN=XXXX.WORK2.XREFCOLS,
//             DISP=(OLD,DELETE,DELETE)
//SYSUT2   DD  DSN=XXXX.XREF.DB2.COLUMNS,
//             DISP=SHR
//SYSIN    DD  DUMMY



JCL for indexes:
Code:

//XXXX79Q   JOB  BRENHOD,'DICK BRENHOLTZ',NOTIFY=BRENHOD,CLASS=I,
//          MSGLEVEL=(1,1),SCHENV=XXXX,MSGCLASS=X,REGION=9M
//*         RESTART=STEP030
//*
//*
//*--------------------------------------------------------------
//JOBLIB   DD DSN=SYSDB2.SDSNLOAD,
//            DISP=SHR
//*--------------------------------------------------------------
//*--------------------------------------------------------------
//STEP010  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//SYSTSIN  DD  DSN=XXXX.HUON36.UNIT.RUNLIB(DSNTEP2),
//             DISP=SHR
>>>>>>>
>>>>>>>  DSN S(XXXX)
>>>>>>>  RUN PROGRAM(DSNTEP2)  PLAN(DSNTEP2)  -
>>>>>>>  LIB('SYSDB2.XXXX.RUNLIB.LOAD')
>>>>>>>  END
>>>>>>>
//SYSPRINT DD  DSN=XXXX.WORK1.XREFIDX,
//             DISP=(NEW,CATLG,DELETE),
//             UNIT=SYSDA,SPACE=(TRK,(1500,100),RLSE),
//             DCB=(RECFM=FBA,LRECL=133,BLKSIZE=0)
//SYSUDUMP DD  SYSOUT=*
//SYSPUNCH DD  SYSOUT=*
//SYSIN    DD  DSN=XXXX.BRENHOD.DB2SQL(XREFIDX),
//             DISP=SHR
>>>>>>>
>>>>>>>   SELECT SUBSTR(I.TBNAME,1,20) AS TBNAME
>>>>>>>        , SUBSTR(K.IXNAME,1,12) AS IXNAME
>>>>>>>        , SUBSTR(K.COLNAME,1,16) AS COLNAME
>>>>>>>        , K.COLSEQ AS SEQ
>>>>>>>        , REPLACE(REPLACE(SUBSTR(DIGITS(K.COLNO),2,4),'00','  ')
>>>>>>>            ,' 0', '  ') AS COLN
>>>>>>>        , I.UNIQUERULE AS U_D
>>>>>>>        , K.ORDERING AS ORD
>>>>>>>        , SUBSTR(COLTYPE,1,8) AS COLTYPE
>>>>>>>        , LENGTH AS LEN
>>>>>>>        , NULLS
>>>>>>>   FROM SYSIBM.SYSKEYS K
>>>>>>>      , SYSIBM.SYSINDEXES I
>>>>>>>      , SYSIBM.SYSCOLUMNS C
>>>>>>>   WHERE K.IXCREATOR = '<YOUR CREATOR NAME>'
>>>>>>>     AND I.TBCREATOR = '<YOUR CREATOR NAME>'
>>>>>>>     AND C.TBCREATOR = '<YOUR CREATOR NAME>'
>>>>>>>     AND I.NAME = K.IXNAME
>>>>>>>     AND C.NAME = K.COLNAME
>>>>>>>     AND I.TBNAME = C.TBNAME
>>>>>>>     AND I.TBNAME NOT IN (
>>>>>>>                         'DSN_FUNCTION_TABLE'
>>>>>>>                        ,'DSN_STATEMENT_CACHE_AUX'
>>>>>>>                        ,'DSN_STATEMENT_CACHE_TABLE'
>>>>>>>                        ,'DSN_STATEMNT_TABLE'
>>>>>>>                        ,'PLAN_HISTORY'
>>>>>>>                        ,'PLAN_TABLE'
>>>>>>>                       )
>>>>>>>   ORDER BY 1, 2, 4
>>>>>>>   WITH UR
>>>>>>>   ;
>>>>>>>
/*
//STEP020  EXEC PGM=ICEMAN
//SORTIN   DD  DSN=XXXX.WORK1.XREFIDX,
//             DISP=(OLD,DELETE,DELETE)
//SORTOUT  DD  DSN=XXXX.WORK2.XREFIDX,
//             DISP=(NEW,CATLG,DELETE),
//             UNIT=SYSDA,SPACE=(TRK,(1500,100),RLSE),
//             DCB=(RECFM=FB,LRECL=118,BLKSIZE=0)
//SYSOUT   DD  SYSOUT=*
//SYSIN    DD  DSN=XXXX.BRENHOD.SORTPARM(INDEX1),
//             DISP=SHR
>>>>>>>
>>>>>>>  SORT FIELDS=COPY
>>>>>>>  INCLUDE COND=(1,30,SS,EQ,C'_!')
>>>>>>>  INREC PARSE=(%01=(STARTAFT=C'_! ',FIXLEN=118)),
>>>>>>>        BUILD=(%01)
>>>>>>>
//SORTDIAG DD  SYSOUT=*
//*
//STEP030  EXEC PGM=IEBGENER
//SYSPRINT DD  SYSOUT=A
//SYSUT1   DD  DSN=XXXX.XREF.HEADER.INDEXES,
//             DISP=SHR
//         DD  DSN=XXXX.WORK2.XREFIDX,
//             DISP=(OLD,DELETE,DELETE)
//SYSUT2   DD  DSN=XXXX.XREF.DB2.INDEXES,
//             DISP=SHR
//SYSIN    DD  DUMMY


JCL for sysauth:
Code:

//XXXX79S   JOB  BRENHOD,'DICK BRENHOLTZ',NOTIFY=BRENHOD,CLASS=I,
//          MSGLEVEL=(1,1),SCHENV=XXXX,MSGCLASS=X,REGION=9M
//*         RESTART=STEP030
//*
//*
//STEP010  EXEC PGM=IEFBR14,REGION=4096K
//SORTOUT  DD  DSN=XXXX.XREF.SYSAUTH,
//             SPACE=(TRK,(1,1)),
//             DISP=(MOD,DELETE,DELETE)
//*
//*--------------------------------------------------------------
//*--------------------------------------------------------------
//STEP020  EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB  DD  DSN=SYSDB2.SDSNLOAD,
//             DISP=SHR
//SYSTSPRT DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//SYSTSIN  DD  DSN=XXXX.HUON36.UNIT.RUNLIB(DSNTEP2),
//             DISP=SHR
>>>>>>
>>>>>>   DSN S(XXXX)
>>>>>>   RUN PROGRAM(DSNTEP2)  PLAN(DSNTEP2)  -
>>>>>>   LIB('SYSDB2.XXXX.RUNLIB.LOAD')
>>>>>>   END
>>>>>>
//SYSPRINT DD  DSN=&&TEMP,
//             DISP=(NEW,PASS),
//             UNIT=SYSDA,SPACE=(TRK,(1500,100),RLSE),
//             DCB=(RECFM=FBA,LRECL=133,BLKSIZE=0)
//SYSUDUMP DD  SYSOUT=*
//SYSPUNCH DD  SYSOUT=*
//SYSIN    DD  DSN=XXXX.BRENHOD.DB2SQL(SYSAUTH),
//             DISP=SHR
>>>>>>
>>>>>>    SELECT  SUBSTR(GRANTEE,1,15) AS GRANTEE
>>>>>>          , SUBSTR(STNAME,1,20) AS STNAME
>>>>>>          , CASE DELETEAUTH
>>>>>>              WHEN 'Y' THEN 'D'
>>>>>>              ELSE ' '
>>>>>>            END AS DEL
>>>>>>          , CASE INSERTAUTH
>>>>>>              WHEN 'Y' THEN 'I'
>>>>>>              ELSE ' '
>>>>>>            END AS INS
>>>>>>          , CASE SELECTAUTH
>>>>>>              WHEN 'Y' THEN 'S'
>>>>>>              ELSE ' '
>>>>>>            END AS SEL
>>>>>>          , CASE UPDATEAUTH
>>>>>>              WHEN 'Y' THEN 'U'
>>>>>>              ELSE ' '
>>>>>>            END AS UPD
>>>>>>          , GRANTEDTS
>>>>>>    FROM SYSIBM.SYSTABAUTH
>>>>>>    WHERE COLLID IN ('<YOUR COLLID>'
>>>>>>                    ,'<A SECOND COLLID WHEN NECESSARY>'
>>>>>>                    )
>>>>>>      AND SUBSTR(GRANTEE,1,3) NOT IN ('DBZ', 'ERN')
>>>>>>    ORDER BY STNAME
>>>>>>           , GRANTEE
>>>>>>           , GRANTEDTS
>>>>>>    ;
>>>>>>
//*
//STEP030  EXEC PGM=ICETOOL
//TOOLMSG  DD  SYSOUT=*
//DFSMSG   DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//SORTIN   DD  DSN=&&TEMP,
//             DISP=(SHR,DELETE,DELETE)
//SORTOUT  DD  DSN=XXXX.XREF.SYSAUTH,
//             DISP=(NEW,CATLG,DELETE),
//             UNIT=SYSDA,SPACE=(TRK,(1000,100),RLSE)
//*            DCB=(RECFM=FB,LRECL=132,BLKSIZE=0)
//TOOLIN   DD  DSN=XXXX.BRENHOD.SORTPARM(SYSAUTH),
//             DISP=SHR
>>>>>>
>>>>>>   SELECT FROM(SORTIN) TO(SORTOUT) ON(1,65,CH) LAST USING(CTL1)
>>>>>>
//CTL1CNTL DD  DSN=XXXX.BRENHOD.SORTPARM(SYSAUTH1),
//             DISP=SHR
>>>>>>
>>>>>>   SORT FIELDS=(1,92,CH,A)
>>>>>>   INCLUDE COND=(1,30,SS,EQ,C'_!')
>>>>>>   INREC PARSE=(%01=(STARTAFT=C'_! ',FIXLEN=118)),
>>>>>>         BUILD=(%01)
>>>>>>
//* //*



when VIEWing the SYSAUTH dataset,
sort by table and you see which programs do what to which table.
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 Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts JES2 doesn't honor the priority of th... Aron Lendvai JCL & VSAM 8 Thu Jun 29, 2017 1:41 pm
No new posts member list compare jzhardy TSO/ISPF 6 Fri May 12, 2017 3:18 pm
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us