Or: „Even Roy learns things from Listserv!“
While discussing negative DBIDs on Listserv the other day, another poster commented on my SQL Code snippet and said that it could well be better changing from LOCATE and HEX to using the ASCII Built-In Scalar function. But first, the old data must be able to understand the new data!
About IBM List Servers Discussions – Forums: http://www.vm.ibm.com/techinfo/listserv.html
Db2 z/OS „Listserv“:
IBM-MAIN newsgroup, mostly z/OS, OS/390, & MVS
DB2 Database discussion list
The DBID what?
The DBID, or DataBase object Identifier, is a SMALLINT number that is used internally for *all* objects in Db2. IBM has reserved the first 254 characters for their own use and we, as users, have the rest – up to 65,535. Now, if you are sharp you will have noticed that I wrote SMALLINT.A quick glance in the “limits of Db2” chapter of??? shows us:
Table 143. Numeric limits Item Limit Smallest SMALLINT value -32768 Largest SMALLINT value 32767
The trick is…
So how do they do it? Well, they stuff a quart into a pint pot by going negative on us!
If a DBID goes over the value 32511 it then switches to negative numbers.
This saved IBM two bytes of storage… of course it is two bytes that is *everywhere*, so it actually saves quite a bit of space (pardon the pun!)
The IDs have it… PSID – OBID – ISOBID…
The PSID or Page Set Identifier is also involved here, as that is the “count” of things within that Database and it cannot exceed 32767. Unless, of course, it goes negative! Why here? Well think CLONE, any CLONE objects have the high-bit set, thus flipping them over to be the “mirror image” of the base object.
To round this brief discussion of the IDs, we also have the OBID or OBject IDentifier and the ISOBID or Index Space OBject IDentifier. All of these are stored as SMALLINTs everywhere in the Catalog and Directory and, naturally, the absolute physical never-going-to-change-maximum number of things in a Database is 65535.
LOCATE and HEX
I referred back to one of my older newsletters: “Discovering hidden recovery problems in the SYSLGRNX” from October 2016, where I presented an SQL to “extract” the DSNDB01 SYSLGRNX table into a “usable” format. The SQL started like this:
SELECT COALESCE( CASE WHEN ((LOCATE(SUBSTR(HEX(A.LGRDBID), 1 , 1),'0123456789ABCDEF')-1)*4096) + ((LOCATE(SUBSTR(HEX(A.LGRDBID), 2 , 1),'0123456789ABCDEF')-1)*256) + ((LOCATE(SUBSTR(HEX(A.LGRDBID), 3 , 1),'0123456789ABCDEF')-1)*16) + ((LOCATE(SUBSTR(HEX(A.LGRDBID), 4 , 1),'0123456789ABCDEF')-1)) > 32767 THEN (((LOCATE(SUBSTR(HEX(A.LGRDBID), 1 , 1),'0123456789ABCDEF')-1)*4096) + ((LOCATE(SUBSTR(HEX(A.LGRDBID), 2 , 1),'0123456789ABCDEF')-1)*256) + ((LOCATE(SUBSTR(HEX(A.LGRDBID), 3 , 1),'0123456789ABCDEF')-1)*16) + ((LOCATE(SUBSTR(HEX(A.LGRDBID), 4 , 1),'0123456789ABCDEF')-1)) - 32768) * -1 ELSE ((LOCATE(SUBSTR(HEX(A.LGRDBID), 1 , 1),'0123456789ABCDEF')-1)*4096) + ((LOCATE(SUBSTR(HEX(A.LGRDBID), 2 , 1),'0123456789ABCDEF')-1)*256) + ((LOCATE(SUBSTR(HEX(A.LGRDBID), 3 , 1),'0123456789ABCDEF')-1)*16) + ((LOCATE(SUBSTR(HEX(A.LGRDBID), 4 , 1),'0123456789ABCDEF')-1)) END , 0 ) AS LGRDBID
I thought that was a neat way to change a CHAR(2) field into a real number and to also handle the problem of negative DBIDs when they “overflow” the SMALLINT boundary. (Remember, this happens if you have more than 32,511 Databases).
Problem one
First problem that jumped out at me, is the fact that the math is wrong! I should, of course, have subtracted 32767 from the DBID.
Where does ASCII fit?
Well, it came out in Db2 V8 and got reviewed by me in a very old newsletter called: “SOUNDEX and other “cool” features – part one for DB2 V8” in April of 2012 !
Starting off with DB2 V8 : ASCII function
V8 introduced us to the ASCII function:
SELECT ASCII('BOXWELL')
FROM SYSIBM.SYSDUMMY1 ;
66So it takes the leftmost character of any given string expression and returns the ASCII value, in this case 66 for B – I must admit that I have *not* yet found a use for this little gem…
Back to LISTSERV…
The poster wrote:
Roy,
Perhaps your query performs well enough already, so there is no need to change, and I have used LOCATE on a HEX digit myself for similar reasons on SYSPACKSTMT table. However I intend to change my query over to use ASCII function.
For anyone needing to convert a Char for BIT Data column, to an Integer in this case Char 2 bytes, based on the string actually containing a binary integer, you could look at the ASCII function to do it (more efficiently), I believe:
e.g. ASCII(SUBSTR(arg, 1, 1)) * 256 + ASCII(SUBSTR(arg, 2, 1))I believe ASCII function accepts Char for Bit Data without auto conversion to another CCSID. ASCII and a lot of other fancy functions arrived in Db2 V8. Slightly less of a Pain in the A***. Because of the fancy functions, CTEs, and Recursive SQL, V8 was possibly my favourite release of DB2, when SQL could do absolutely anything, so I did not need REXX post processors any more. I forgot to mention Native SQL Stored Procs, and Non-Padded VARCHAR in indexes, DPSIs. Wow, what a release!
Of course this is amusing, since the function ASCII is somewhat misnamed. What we might use it for has nothing to do with ASCII encoding at all.
Aha! Moment
I thought wow! That could indeed be a lot faster and easier than my code so I changed my SQL to look like:
SELECT COALESCE(
CASE WHEN ASCII(SUBSTR(A.LGRDBID, 1, 1)) * 256
+ ASCII(SUBSTR(A.LGRDBID, 2, 1)) > 32767 THEN
((ASCII(SUBSTR(A.LGRDBID, 1, 1)) * 256
+ ASCII(SUBSTR(A.LGRDBID, 2, 1))) - 32767) * -1
ELSE ASCII(SUBSTR(A.LGRDBID, 1, 1)) * 256
+ ASCII(SUBSTR(A.LGRDBID, 2, 1))
END , 0 ) AS LGRDBID
,COALESCE(
CASE WHEN ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256
+ ASCII(SUBSTR(A.LGRPSID, 2, 1)) > 32767 THEN
(ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256
+ ASCII(SUBSTR(A.LGRPSID, 2, 1))) - 32767
ELSE ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256
+ ASCII(SUBSTR(A.LGRPSID, 2, 1))
END , 0 ) AS LGRPSID
,TIMESTAMP(
CASE WHEN SUBSTR(A.LGRUCDT, 5 , 2 ) > '16' THEN '19'
ELSE '20'
END CONCAT
SUBSTR(A.LGRUCDT, 5 , 2 ) CONCAT '-' CONCAT
SUBSTR(A.LGRUCDT, 1 , 2 ) CONCAT '-' CONCAT
SUBSTR(A.LGRUCDT, 3 , 2 ) CONCAT '-' CONCAT
SUBSTR(A.LGRUCTM, 1 , 2 ) CONCAT '.' CONCAT
SUBSTR(A.LGRUCTM, 3 , 2 ) CONCAT '.' CONCAT
SUBSTR(A.LGRUCTM, 5 , 2 ) CONCAT '.' CONCAT
SUBSTR(A.LGRUCTM, 6 , 2 ) CONCAT '0000'
) AS LGRUCTS
,A.LGRSRBA
,A.LGRSPBA
,A.LGRPART
,A.LGRSLRSN
,A.LGRELRSN
,COALESCE(
ASCII(SUBSTR(A.LGRMEMB, 1, 1)) * 256
+ ASCII(SUBSTR(A.LGRMEMB, 2, 1))
, 0 ) AS LGRMEMB
,CASE WHEN ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256
+ ASCII(SUBSTR(A.LGRPSID, 2, 1)) > 32767 THEN 2
ELSE 1
END AS LGRINST
FROM SYSIBM.SYSLGRNX A
FOR FETCH ONLY
WITH UR
;Whoosh!
Yes indeed, over 30% faster than my code…
Now remember, you can only really do this if your CHAR data has the FOR BIT DATA attribute so that no code page conversion is done. The LGRMEMB column is ok because it contains the hex values 00 (For non-datasharing) or 01 to 32 (Maximum number of members in a data-sharing group). These do not convert so they don’t cause a problem.
Thanks again to LISTSERV and the Posters who keep it alive and help old dogs (like me) learn new tricks.
I might even update my old ASCII review to mention that I do indeed have a use for it now!
As always, any questions or comments would be most welcome!
TTFN,
Roy Boxwell