This month I wish to „rake over“ some hot coals from yesteryear…
The good ol‘ days!
Remember when we had just eight-byte CREATORs ? Prior to the „big bang“ DB2 V8 all the creators and table names were limited to eight and eighteen bytes respectively. This was then changed in DB2 V8 to both be VARCHAR(128). So far so good!
And?
What went wrong was the way IBM development handled the SYSIBM creator…
Why is this a problem?
Well if you are writing SQL to access the Db2 Catalog and you wish to use wild cards, E.g. % or _, then you must start taking care of your predicate texts!
Here are some examples of SQL that should do the same thing, but don’t!
Baseline counts
First, here’s a little SQL to simply show us some numbers:
SELECT COUNT(*), CREATOR
FROM SYSIBM.SYSINDEXES
WHERE (CREATOR LIKE 'SYSIB_'
OR CREATOR LIKE 'SYSIBM_'
OR CREATOR LIKE 'SYSIBM _'
OR CREATOR LIKE 'SYSIB%'
OR CREATOR LIKE 'SYSIBM%'
OR CREATOR LIKE 'SYSIBM %')
AND NOT CREATOR = 'SYSIBMTS'
GROUP BY CREATOR
FOR FETCH ONLY
WITH UR
;
When I run this in my Db2 13 system I get this result:
---------+---------+---------+---------
CREATOR
---------+---------+---------+---------
317 SYSIBM
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
and??
Now comes the interesting bit…
Here are six SQLs all nearly the same but look at the COUNT values.
SELECT COUNT(*)
FROM SYSIBM.SYSINDEXES
WHERE CREATOR LIKE 'SYSIB_'
AND NOT CREATOR = 'SYSIBMTS'
FOR FETCH ONLY
WITH UR
;
---------+---------+---------+---------+
---------+---------+---------+---------+
187
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
SELECT COUNT(*)
FROM SYSIBM.SYSINDEXES
WHERE CREATOR LIKE 'SYSIBM_'
AND NOT CREATOR = 'SYSIBMTS'
FOR FETCH ONLY
WITH UR
;
---------+---------+---------+---------+
---------+---------+---------+---------+
0
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
SELECT COUNT(*)
FROM SYSIBM.SYSINDEXES
WHERE CREATOR LIKE 'SYSIBM _'
AND NOT CREATOR = 'SYSIBMTS'
FOR FETCH ONLY
WITH UR
;
---------+---------+---------+---------+
---------+---------+---------+---------+
130
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
SELECT COUNT(*)
FROM SYSIBM.SYSINDEXES
WHERE CREATOR LIKE 'SYSIB%'
AND NOT CREATOR = 'SYSIBMTS'
FOR FETCH ONLY
WITH UR
;
---------+---------+---------+---------+
---------+---------+---------+---------+
317
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
SELECT COUNT(*)
FROM SYSIBM.SYSINDEXES
WHERE CREATOR LIKE 'SYSIBM%'
AND NOT CREATOR = 'SYSIBMTS'
FOR FETCH ONLY
WITH UR
;
---------+---------+---------+---------+
---------+---------+---------+---------+
317
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
SELECT COUNT(*)
FROM SYSIBM.SYSINDEXES
WHERE CREATOR LIKE 'SYSIBM %'
AND NOT CREATOR = 'SYSIBMTS'
FOR FETCH ONLY
WITH UR
;
---------+---------+---------+---------+
---------+---------+---------+---------+
130
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
You see the difference? The reason is clear as „some“ of the SYSIBM creators, 130 in this example, got the varchar length of *eight* when the change to V8 happened. However, 187 got the correct length of six.
This is normally never seen of course as it is SYSIBM stuff but if you write SQL against the catalog and you are using host variables then be careful when predicates use the CREATOR!
The same results come from Static SQL of course.
Is there a fix?
Now in SQL you can fix this by either adding a STRIP around the CREATOR -> WHERE STRIP(CREATOR) LIKE = :HOST-VAR or you can „cheat“ by *not* using a VARCHAR host variable and then the trailing two spaces and the extra two bytes in the length are then „ignored“ by predicate processing.
Which one to do?
You gotta CHEAT! Why? because STRIP is a non-indexable function! If the tables are very small probably ok but for normal ones a very bad idea!!!
Just for fun!
Here’s some „discovery“ SQL that builds a bunch of SELECT statements that you can then simply run in SPUFI. Take the output and then, after deleting the generator output SQL, the last sets of SPUFI output and adding at the front the
--#SET TERMINATOR $
Plus adding the
--#SET TERMINATOR ;
at the end – Just for good style! – You can then also execute it in SPUFI.
DECLARE GLOBAL TEMPORARY TABLE T (LINENO INTEGER NOT NULL)
;
-- INSERT SIX LINES
INSERT INTO SESSION.T VALUES 1
;
INSERT INTO SESSION.T VALUES 2
;
INSERT INTO SESSION.T VALUES 3
;
INSERT INTO SESSION.T VALUES 4
;
INSERT INTO SESSION.T VALUES 5
;
INSERT INTO SESSION.T VALUES 6
;
SELECT CASE LINENO
WHEN 1 THEN 'SELECT DISTINCT SUBSTR(' CONCAT SUBSTR(NAME, 1, 18)
CONCAT ' , 1 , 8) AS CREATOR '
WHEN 2 THEN ' ,LENGTH(' CONCAT SUBSTR(NAME, 1, 18)
CONCAT ') AS LEN'
WHEN 3 THEN 'FROM SYSIBM.' CONCAT SUBSTR(TBNAME , 1, 18)
WHEN 4 THEN 'WHERE '
CONCAT SUBSTR(NAME, 1, 18) CONCAT ' LIKE ''SYSIBM%'' '
WHEN 5 THEN 'AND NOT '
CONCAT SUBSTR(NAME, 1, 18) CONCAT ' = ''SYSIBMTS'' '
WHEN 6 THEN ' $'
END
FROM SYSIBM.SYSCOLUMNS
,SESSION.T
WHERE NAME LIKE '%CREATOR%'
AND TBCREATOR = 'SYSIBM'
AND COLTYPE = 'VARCHAR'
ORDER BY TBCREATOR
,TBNAME
,NAME
,LINENO
FOR FETCH ONLY
WITH UR
;
My output, after I have edited the start, looks like this:
EDIT BOXWELL.SPUFI.IN(AAA1) - 01.07 Co
Command ===>
****** ********************************* Top of Data ****************
000001 --#SET TERMINATOR $
000002 SELECT DISTINCT SUBSTR(CREATOR , 1 , 8) AS CREATOR
000003 ,LENGTH(CREATOR ) AS LEN
000004 FROM SYSIBM.DSNPROGAUTH
000005 WHERE CREATOR LIKE 'SYSIBM%'
000006 AND NOT CREATOR = 'SYSIBMTS'
000007 $
000008 SELECT DISTINCT SUBSTR(CREATOR , 1 , 8) AS CREATOR
000009 ,LENGTH(CREATOR ) AS LEN
000010 FROM SYSIBM.SYSCHECKS
000011 WHERE CREATOR LIKE 'SYSIBM%'
000012 AND NOT CREATOR = 'SYSIBMTS'
000013 $
000014 SELECT DISTINCT SUBSTR(CREATOR , 1 , 8) AS CREATOR
000015 ,LENGTH(CREATOR ) AS LEN
000016 FROM SYSIBM.SYSCOLAUTH
000017 WHERE CREATOR LIKE 'SYSIBM%'
000018 AND NOT CREATOR = 'SYSIBMTS'
000019 $
.
.
.
When you then run this, you will get a nice list of all tables with either zero, one or two rows of output. The ones with two rows are the dodgy CREATOR lengths! Here’s one from my system:
SELECT DISTINCT SUBSTR(TBCREATOR , 1 , 8) AS CREATOR
,LENGTH(TBCREATOR ) AS LEN
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR LIKE 'SYSIBM%'
AND NOT TBCREATOR = 'SYSIBMTS'
$
---------+---------+---------+---------+---------+---------+-
CREATOR LEN
---------+---------+---------+---------+---------+---------+-
SYSIBM 6
SYSIBM 8
DSNE610I NUMBER OF ROWS DISPLAYED IS 2
TTFN
Roy Boxwell