Joined: 29 Oct 2010 Posts: 202 Location: Toronto, ON, Canada
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.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
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"?
Joined: 29 Oct 2010 Posts: 202 Location: Toronto, ON, Canada
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.
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
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.
