Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
Posted: Tue Feb 08, 2011 4:38 am Post subject:
yes it can and be found easily in the manuals, just have to look.
following works for me
Code:
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
;
_________________ Dick Brenholtz
American living in Varel, Germany
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
Posted: Wed Feb 09, 2011 2:39 am Post subject:
running the sql via DSNTEP2 (batch job)
and then using the below DFSORT control statements to parse the output
will create a Reclen(118) PS that can be viewed.
Code:
SORT FIELDS=COPY
INCLUDE COND=(1,30,SS,EQ,C'_!')
INREC PARSE=(%01=(STARTAFT=C'_! ',FIXLEN=118)),
BUILD=(%01)
of course, if you use another utility, the output of the SQL will be different.
the following will create a RecLen(118) PS file with all columns for all tables:
Code:
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;
sort control statements same as for indexes
or you can modify the sort control statements and generate a RecLen(96) for the columns. _________________ Dick Brenholtz
American living in Varel, Germany
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Wed Feb 09, 2011 11:10 am Post subject:
dbzTHEdinosauer wrote:
running the sql via DSNTEP2 (batch job)
and then using the below DFSORT control statements to parse the output
will create a Reclen(118) PS that can be viewed.
dbzTHEdinosauer,
Why can't you just use DSNTIAUL and avoid the sort step completely? You can convert the integer and decimal columns to readable format using CHAR/DIGITS functions and convert varchar columns to fixed length char columns using CHAR function. _________________ Kolusu
www.linkedin.com/in/kolusu
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
