1

It appears that only the first n characters is used as colvalue for varchar/vargraphic columns. From my test below it appears to be 33/16. Is the length documented somewhere? I tried to search the documentation, but can't find anything (probably searching for the wrong thing)

Simple test case:

DROP TABLE LEJO0004.T;
CREATE TABLE LEJO0004.T (
        ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY 
            ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 NO CYCLE CACHE 20 NO ORDER ),
        TCOL_C VARCHAR(64) NOT NULL,
        TCOL_G VARGRAPHIC(64) NOT NULL
);

insert into LEJO0004.T (tcol_c, tcol_g)
with n100 (n) as ( values 0 union all select n+1 from n100 where n<100 )
   , n10  (n) as ( values 0 union all select n+1 from n10 where n<10 )
   , n1   (n) as ( values 0 )
select 'commons.domain.regel.avbrottsregel'
     , 'commons.domain.regel.avbrottsregel'
from n100
union all
select 'commons.domain.regel.grupp.overfors.nya.endast.som.merit'
     , 'commons.domain.regel.grupp.overfors.nya.endast.som.merit'
from n10
union all
select 'commons.domain.regel.ingar.i.grupp.overfors.till.nya'
    ,  'commons.domain.regel.ingar.i.grupp.overfors.till.nya'
from n1;

runstats on table lejo0004.T with distribution on all columns;

select cast(colname as varchar(15))
    ,  type
    ,  seqno
    ,  cast(colvalue as varchar(40))
    ,  valcount
from sysstat.COLDIST
where tabschema = 'LEJO0004'
  and tabname = 'T'
  and colname in ('TCOL_C', 'TCOL_G')
  and colvalue is not null
  and type = 'F'
order by type, colname, seqno;

Result is:

TCOL_C  F 1 'commons.domain.regel.avbrottsrege' 101
TCOL_C  F 2 'commons.domain.regel.grupp.overfo'  11
TCOL_G  F 1 g'commons.domain.r'                 113

Appears to be the same for Q

values char_length(g'commons.domain.r' using codeunits32)
16

values char_length('commons.domain.regel.avbrottsrege' using codeunits32)
33

Tested on:

db2level
DB21085I  This instance or install (instance name, where applicable: 
"lejo0004") uses "64" bits and DB2 code release "SQL11050" with level 
identifier "0601010F".
Informational tokens are "DB2 v11.5.0.0", "s1906101300", "DYN1906101300AMD64", 
and Fix Pack "0".
Product is installed at "/home/lejo0004/sqllib".
3

It is "partially documented"/"not really documented" AFAIK. More specifically, there is APAR:

IT13369: SUB-OPTIMAL QUERY PERFORMANCE WHEN DISTRIBUTION STATS COLLECTED ON STRING COLUMN WITH A COMMON PREFIX LARGER THAN 32 BYTES

that mentions it. I assume what you actually care about are cardinality estimates for a query that has predicate on a column that has a common prefix in which case you might try the workaround from it (i.e. NOT collect distribution stats for that column)

  • Yes, I noticed a different plan for a table with varchar vs vargraphic with the same content so I drilled down to distribution statistics and found the difference. Thanks for the link, I will have look – Lennart Dec 4 '19 at 17:07
  • The symptom is pretty much as described in the link, but the recomendation is to uppgrade to 10.5 fixpak 7. I'm a bit confused since the observation is from 11.5 – Lennart Dec 4 '19 at 19:47
  • Yes, fix was to address the estimates based on COLCARD for columns with common prefixes, but it looks as if this was effective only for VARCHAR and not VARGRAPHIC, ref: db2 "select substr(colname,1,10) colname , colcard from syscat.columns where tabname = 'T'" COLNAME COLCARD ---------- -------------------- ID 113 TCOL_C 3 TCOL_G 1 – kkuduk Dec 5 '19 at 12:50
  • So basically the effect would be the same as without distribution, i.e. a uniform distribution is assumed? Anyhow, I'll accept this answer since it contributed a lot to my understanding of the problem, thanks. – Lennart Dec 5 '19 at 13:44
  • 1
    @Lennart, the problem you've raised is a real problem - colcard is incorrect for GRAPHIC / VARGRAPHIC column. APAR addresses the issue just for VARCHAR. The problem was actually noted recently and is going to be fixed in next fix packs for 11.1 and 11.5, although there is no APAR ID associated with it yet. – kkuduk Dec 5 '19 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.