Show the Column Names in COLGROUPCOLNO

Michael Hannan

Show the Column Names in COLGROUPCOLNO

I see in 2016, Sam Baugh was asking how to show the Column names IN COLGROUPCOLNO column which occurs in SYSCOLDIST and other tables.

Many thought it was a bit hard so use a Program or a REXX, but SQL can do literally anything and Terry Purcell gave a good SQL back in 2016. I have a variation that uses some slightly simpler methods, that I knocked up last year.

I process COLGROUPCOLNO and other things in there quite a lot. So here is example of what I might use, without needing to use Recursive SQL (since I assume a limited number of Columns in there defined by NUMCOLUMNS). Instead use OLAP specs to calculate offsets when needed.  Some limiting predicates plugged in. Includes some code for extracting COLVALUE data (I have code for Float columns but not included) .

Sorry the website removed the multiple spaces :

 WITH DMY AS 
(SELECT DISTINCT SMALLINT(COLNO) N
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'SYSIBM'
AND TBNAME = 'SYSCOLUMNS'
AND COLNO <= 15 )

,COLDF AS
(SELECT C.TBNAME TB
,C.TBCREATOR CR
,D.NAME
,FREQUENCYF FRQ
-- N STEPS THROUGH EACH COLUMN OF A COLGROUPCOLNO
,N
-- NEXT 2 COLS ARE BIT DATA.
,COLGROUPCOLNO CGCN
,COLVALUE COLV
,LENGTH(COLVALUE) LENCV
, NUMCOLUMNS NUMC
,IFNULL(C2.NULLS, C.NULLS) NULLS
,IFNULL(C2.COLNO, C.COLNO) COLNO
,IFNULL(C2.NAME, C.NAME) CNM
,T.ENCODING_SCHEME ENC
,IFNULL(C2.FOREIGNKEY, C.FOREIGNKEY) SUBTY
,IFNULL(C2.LENGTH, C.LENGTH) LEN
,MIN(IFNULL(C2.LENGTH,C.LENGTH), 255) LENTR
,IFNULL(C2.COLTYPE,C.COLTYPE) CTYP
,IFNULL(C2.SCALE,C.SCALE) SC
,DECODE(IFNULL(C2.COLTYPE,C.COLTYPE)
, 'DECIMAL'
, IFNULL(C2.LENGTH/2+1, C.LENGTH/2 + 1)
, IFNULL(C2.LENGTH, C.LENGTH)
) BYTES
,SMALLINT(DECODE(IFNULL(C2.NULLS,C.NULLS), 'Y', 1, 0)) NULLIND
FROM SYSIBM.SYSCOLDIST D
JOIN SYSIBM.SYSCOLUMNS C
ON D.TBOWNER = C.TBCREATOR
AND D.TBNAME = C.TBNAME
AND D.TYPE = 'F'
AND D.NAME = C.NAME
JOIN SYSIBM.SYSTABLES T
ON T.CREATOR = C.TBCREATOR
AND T.NAME = C.TBNAME
AND T.TYPE = 'T'
LEFT JOIN DMY Z
ON D.NUMCOLUMNS > 1
AND Z.N <= D.NUMCOLUMNS
LEFT JOIN SYSIBM.SYSCOLUMNS C2
ON D.TBOWNER = C2.TBCREATOR
AND D.TBNAME = C2.TBNAME
AND D.TYPE = 'F'
AND D.NUMCOLUMNS > 1
-- 2 WAYS TO COMPARE A SMALLINT COLNO TO 2 BYTES IN COLGROUPCOLNO.
-- 1. COMPARE HEX ON EACH PART TO HEX OF THE OTHER.
AND HEX(C2.COLNO) = HEX(SUBSTR(D.COLGROUPCOLNO, N*2-1, 2))
-- 2. CAN CONVERT A 2 BYTE BIT STRING TO SMALLINT BUT TRICKY
AND C2.COLNO = 256*
ASCII(SUBSTR(D.COLGROUPCOLNO, N*2-1, 1))
+ ASCII(SUBSTR(D.COLGROUPCOLNO, N*2 , 1))
)
-- DEBUGGING SELECT FOLLOWS, NORMALLY COMMENTED OUT.
--SELECT * FROM COLDF
-- WHERE TB LIKE 'SYSSTR%'
-- FETCH FIRST 10 ROWS ONLY WITH UR; //
--
-- CALC OFFSET OF COLUMN VALUES WITHIN COLVALUE
,OFFSET AS
(SELECT V.*
-- CAN USE OLAP SPECS TO AVOID RECURSION.
,SMALLINT(
SUM(BYTES+NULLIND) OVER(PARTITION BY TB, CR, NAME, FRQ, COLV
,NUMC ,CGCN
ORDER BY N ) - BYTES) AS FM1
,SMALLINT(
SUM(BYTES+NULLIND) OVER(PARTITION BY TB, CR, NAME
,NUMC ,CGCN ,FRQ ,COLV ORDER BY N
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)) AS TTL
FROM COLDF V

WHERE 1=1
AND CR IN ('SYSIBM')
)
-- DEBUGGING SELECT FOLLOWS, NORMALLY COMMENTED OUT.
--SELECT * FROM OFFSET FETCH FIRST 10 ROWS ONLY WITH UR; //
--
-- FORMAT COLUMN FREQ VALUES INTO READABLE FORM.
-- FLOAT, REAL, DECFLOAT ARE NOT SUPPORTED IN THIS EXAMPLE
-- UNLIKELY TO BE INDEXED, NOR HAVE SPECIAL FREQ VALS COLLECTED.
SELECT SUBSTR(CTYP, 1, 5) CTYP
, SUBSTR(RIGHT(STRIP(CHAR(LEN)), 5), 1, 5) LEN
, ENC
, SUBTY STY
, SUBSTR(CNM, 1, 16) COL
, NULLS NUL
, NUMC
, DIGITS(DEC(N, 2)) N
,SUBSTR(TB, 1, 16) TBL
, CASE WHEN NULLS ='N' THEN '.'
WHEN SUBSTR(COLV, FM1, 1) = X'FF' THEN 'NU'
ELSE ' ' END NI
, SUBSTR(CASE
WHEN NULLS = 'Y' AND SUBSTR(COLV, 1, 1) = X'FF'
THEN CAST(' ' AS VARCHAR(21) CCSID EBCDIC FOR SBCS DATA)
WHEN CTYP IN ('CHAR','VARCHAR')
AND (ENC IN (' ','E') OR SUBTY = 'B')
THEN CAST(SUBSTR(COLV, 1+FM1, LENTR) AS VARCHAR(255)
CCSID EBCDIC FOR SBCS DATA)
WHEN CTYP IN ('CHAR','VARCHAR')
THEN CAST(
CAST(SUBSTR(COLV, 1+FM1, LENTR)
AS VARCHAR(255) CCSID UNICODE FOR SBCS DATA)
AS VARCHAR(255) CCSID EBCDIC FOR SBCS DATA)
WHEN CTYP = 'DECIMAL'
AND HEX(SUBSTR(COLV, 1+FM1, 1)) LIKE 'F%'
THEN ' ' CONCAT INSERT(RIGHT(
CAST(HEX(SUBSTR(COLV, 1+FM1, BYTES))
AS VARCHAR(33) CCSID EBCDIC FOR SBCS DATA), LEN)
, LEN+1-SC, 0, '.')
WHEN CTYP = 'DECIMAL'
AND HEX(SUBSTR(COLV, 1+FM1, 1)) LIKE '00%'
THEN
CAST('ERROR'
AS VARCHAR(33) CCSID EBCDIC FOR SBCS DATA)
WHEN CTYP = 'DECIMAL'
THEN '-' CONCAT INSERT(TRANSLATE(RIGHT(
CAST(HEX(SUBSTR(COLV, 1+FM1, BYTES))
AS VARCHAR(33) CCSID EBCDIC FOR SBCS DATA), LEN)
,'0123456789'
,'FEDCBA9876' )
, LEN+1-SC, 0, '.')
WHEN CTYP IN ('DATE','TIME','TIMESTMP')
THEN CAST(HEX(SUBSTR(COLV, 1+FM1, LEN))
AS VARCHAR(33) CCSID EBCDIC FOR SBCS DATA)
WHEN CTYP = 'SMALLINT'
THEN LPAD(STRIP(CAST(CHAR(
ASCII(SUBSTR(COLV, 1+FM1, 1))*256
+ ASCII(SUBSTR(COLV, 2+FM1, 1))
- 32768 ) AS VARCHAR(6) CCSID EBCDIC FOR SBCS DATA))
, 6)
WHEN CTYP = 'INTEGER'
THEN LPAD(STRIP(CAST(CHAR((((
ASCII(SUBSTR(COLV, 1+FM1, 1))-128)*256
+ ASCII(SUBSTR(COLV, 2+FM1, 1)))*256
+ ASCII(SUBSTR(COLV, 3+FM1, 1)))*256
+ ASCII(SUBSTR(COLV, 4+FM1, 1))
) AS VARCHAR(11) CCSID EBCDIC FOR SBCS DATA )), 11)
WHEN CTYP = 'BIGINT'
THEN LPAD(STRIP(CAST(CHAR((((((((
ASCII(SUBSTR(COLV, 1+FM1, 1))-BIGINT(128))*256
+ ASCII(SUBSTR(COLV, 2+FM1, 1)))*256
+ ASCII(SUBSTR(COLV, 3+FM1, 1)))*256
+ ASCII(SUBSTR(COLV, 4+FM1, 1)))*256
+ ASCII(SUBSTR(COLV, 5+FM1, 1)))*256
+ ASCII(SUBSTR(COLV, 6+FM1, 1)))*256
+ ASCII(SUBSTR(COLV, 7+FM1, 1)))*256
+ ASCII(SUBSTR(COLV, 8+FM1, 1))
) AS VARCHAR(21) CCSID EBCDIC FOR SBCS DATA )), 21)
ELSE CAST(' ' AS VARCHAR(1) CCSID EBCDIC FOR SBCS DATA)
END, 1, 30)
FMT
, SUBSTR(HEX(COLV), 1, 40) HEX
, REAL(FRQ) FRQ
,HEX(CGCN) ,LENCV ,TTL ,FM1
FROM OFFSET
WHERE 1=1
AND LENCV >= TTL
AND TB LIKE 'SYSSTR%'
ORDER BY CR DESC, TB, NAME, NUMC, CGCN, COLV, N
FETCH FIRST 3000 ROWS ONLY
WITH UR;


Some sample results (truncated and I could not work out how to do Courier format):

CTYP LEN ENC STY COL NUL NUMC N TBL NI FMT      HEX
INTEG 4 U OUTCCSID N 1 -- SYSSTRINGS . 37 80000025
INTEG 4 U OUTCCSID N 1 -- SYSSTRINGS . 500 800001F4
INTEG 4 U OUTCCSID N 1 -- SYSSTRINGS . 850 80000352
INTEG 4 U OUTCCSID N 1 -- SYSSTRINGS . 1140 80000474
INTEG 4 U OUTCCSID N 1 -- SYSSTRINGS . 1141 80000475
INTEG 4 U OUTCCSID N 1 -- SYSSTRINGS . 1142 80000476
INTEG 4 U OUTCCSID N 1 -- SYSSTRINGS . 1143 80000477
INTEG 4 U OUTCCSID N 1 -- SYSSTRINGS . 1144 80000478
INTEG 4 U OUTCCSID N 1 -- SYSSTRINGS . 1145 80000479
INTEG 4 U OUTCCSID N 1 -- SYSSTRINGS . 1148 8000047C
INTEG 4 U OUTCCSID N 2 01 SYSSTRINGS . 37 800000258000016F
INTEG 4 U INCCSID N 2 02 SYSSTRINGS . 367 800000258000016F
INTEG 4 U OUTCCSID N 2 01 SYSSTRINGS . 37 8000002580000341
INTEG 4 U INCCSID N 2 02 SYSSTRINGS . 833 8000002580000341
INTEG 4 U OUTCCSID N 2 01 SYSSTRINGS . 37 8000002580000344
INTEG 4 U INCCSID N 2 02 SYSSTRINGS . 836 8000002580000344
INTEG 4 U OUTCCSID N 2 01 SYSSTRINGS . 37 8000002580000352
INTEG 4 U INCCSID N 2 02 SYSSTRINGS . 850 8000002580000352
INTEG 4 U OUTCCSID N 2 01 SYSSTRINGS . 37 8000002580000388
INTEG 4 U INCCSID N 2 02 SYSSTRINGS . 904 8000002580000388
INTEG 4 U OUTCCSID N 2 01 SYSSTRINGS . 37 800000258000039B
INTEG 4 U INCCSID N 2 02 SYSSTRINGS . 923 800000258000039B
INTEG 4 U OUTCCSID N 2 01 SYSSTRINGS . 37 800000258000039C
INTEG 4 U INCCSID N 2 02 SYSSTRINGS . 924 800000258000039C
INTEG 4 U OUTCCSID N 2 01 SYSSTRINGS . 37 80000025800003A5
INTEG 4 U INCCSID N 2 02 SYSSTRINGS . 933 80000025800003A5
INTEG 4 U OUTCCSID N 2 01 SYSSTRINGS . 37 80000025800003A7
INTEG 4 U INCCSID N 2 02 SYSSTRINGS . 935 80000025800003A7
INTEG 4 U OUTCCSID N 2 01 SYSSTRINGS . 37 80000025800003A9
INTEG 4 U INCCSID N 2 02 SYSSTRINGS . 937 80000025800003A9
INTEG 4 U OUTCCSID N 3 01 SYSSTRINGS . 37 800000258000011159
INTEG 4 U INCCSID N 3 02 SYSSTRINGS . 273 800000258000011159
CHAR 1 U M IBMREQD N 3 03 SYSSTRINGS . Y 800000258000011159
INTEG 4 U OUTCCSID N 3 01 SYSSTRINGS . 37 800000258000011559
INTEG 4 U INCCSID N 3 02 SYSSTRINGS . 277 800000258000011559
CHAR 1 U M IBMREQD N 3 03 SYSSTRINGS . Y 800000258000011559
Etc.

Performance of the Stage 2 join predicate using HEX functions is not that wonderful, so alternative COLNO predicate is provided (that is indexable). That enabled me to get MC=3 on access path to SYSCOLUMNS index. Use Degree ANY for parallel. Query is not so fast if we want to do many tables in the query. However we won't be running with very high frequency. Response is fine for just a few tables for Auth='SYSIBM'.

There might also be a possibility to use the row UNPACK function after converting COLVALUE to VARBINARY, however UNPACK expects the coder to know how many columns will be returned. Therefore COLGROUPCOLNO might need to be padded out with dummy data to a fixed maximum length before using UNPACK. Quite tricky to code I think. I have used UNPACK to get binary sub fields out of a BLOB however (in SYSPACKSTMT table). I view UNPACK as a last resort type function, when no reasonable Cast options are available.  VARBINARY columns have very limited functions available and not really Castable. Fortunately SYSCOLDIST does not contain VARBINARY columns.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

 

Edited By:
Michael Hannan[Organization Members] @ Sep 01, 2019 - 01:56 PM (Europe/Berlin)