Informix

nested-group-icon.png

DB2

Expand all | Collapse all

sysblobs.spacename: partition, dbspace, or family

  • 1.  sysblobs.spacename: partition, dbspace, or family

    Posted Fri February 28, 2020 02:57 PM
    Hi Y'all.

    My current client has a fair number of tables with text and binary blob columns.  However, no blob spaces; all blob column are in the TBLspace with the row.  (Of course on pages separate from the row.)  So I got curious about the way they are tracked and I ran this query:
    select t.tabname[1,18], b.tabid, c.colname[1,18], b.colno,
            b.type, b.spacename[1,10]
      from sysblobs b, systables t, syscolumns c
     where b.tabid = t.tabid
       and b.colno = c.colno
       and t.tabid = c.tabid
     order by tabname, colname
    ;
    Here is a partial result of the above, with names changed:
    Some blob columns

    Now, the system catalogs sysfragments and sysdirectives have 3 blob columns each and all the values for column spacename are null.  This makes sense because, of course, there was no blobspace designation when the catalog was created.  But all of the user-level blob columns are the same way - in TBLspace, as I mentioned above - are listed in the same DBspace, named abcxyz7.  (Name changed to avoid concerns of confidentiality.) That  DBspace exists but none of the tables returned by my query have any partitions in abcxyz7.  This value should also be null for these tables!

    According to the meager explanation in the knowledge base, the spacename column may be "Name of partition, dbspace, or family".  Since none of these blob-hosting tables are partitioned, the partition name would be irrelevant anyway.

    But what in blazes the this "family" the knowledge base refers to?  And what could it possible have to do with that totally disconnected DBspace?  I need a better explanation of how the value gets set in sysblobs.spacename.

    Anybody got one? :-)

    Thanks for some enlightenment. 



    ------------------------------
    Jacob Salomon
    ---
    Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
    ------------------------------


  • 2.  RE: sysblobs.spacename: partition, dbspace, or family

    Posted Fri February 28, 2020 10:19 PM
    Jacob:

    I did a simple test like below. As a result of testing, I could infer some facts.
    Please point out what is wrong or what I misunderstand.

    1. If a BLOBSpace is specified in the IN clause when defining a BYTE or TEXT column, the corresponding BLOBSpace name is displayed in the spacename column of the sysblobs table.

    2. If IN TABLE is used or omitted when defining BYTE or TEXT columns, the name of the DBSpace where the database to which the table belongs is created is displayed in the spacename column of the sysblobs table.

    3. If IN TABLE is used or omitted when defining a BYTE or TEXT column, BYTE or TEXT data is not stored in the DBSpace indicated in the spacename column of the sysblobs table, but in the DBSpace defined for the table.

    /work2/INFORMIX/1210FC13/skjeong]onstat -d update
    
    IBM Informix Dynamic Server Version 12.10.FC13WE -- On-Line -- Up 11 days 23:35:09 -- 218096 Kbytes
    
    Dbspaces
    address          number   flags      fchunk   nchunks  pgsize   flags    owner    name
    700000020478028  1        0x14020001 1        1        4096     N  BAE   informix rootdbs
    7000000205907a8  2        0x15000001 2        1        4096     N PBAE   informix plog
    7000000205909e8  3        0x14000001 3        1        4096     N  BAE   informix llog
    700000020590c28  4        0x14000001 4        1        4096     N  BAE   informix datadbs1
    7000000226fb028  5        0x14000001 5        1        4096     N  BAE   informix datadbs2
    7000000226fb268  6        0x14000001 6        1        4096     N  BAE   informix datadbs3
    7000000226fb4a8  7        0x14000001 7        1        8192     N  BAE   informix data8dbs1
    7000000226fb6e8  8        0x14000001 8        1        8192     N  BAE   informix data8dbs2
    7000000226fb928  9        0x14000001 9        1        8192     N  BAE   informix data8dbs3
    7000000226fbb68  10       0x14002001 10       1        4096     N TBAE   informix tmpdbspace
    7000000226fbda8  11       0x14008001 11       1        4096     N SBAE   informix sbspace1
    7000000226fd028  12       0x1400a001 12       1        4096     N UBAE   informix tmpsbspace
    7000000226fd268  13       0x14000001 13       1        4096     N  BAE   informix dbs1
    700000025c03d10  14       0x14020011 14       1        32768    N BBAE   informix blobdbs1
     14 active, 2047 maximum
    
    Waiting for server to update BLOB chunk statistics...
    
    Chunks
    address          chunk/dbs     offset     size       free       bpages     flags pathname
    700000020478268  1      1      0          38912      18864                 PO-B-C /work2/INFORMIX/1210FC13/storage/rootdbs
    7000000226fd4a8  2      2      0          18455      2071                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_plog_p_1
    7000000226fe028  3      3      0          56569      8900                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_llog_p_1
    7000000226ff028  4      4      0          16384      8328                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs1_p_1
    700000022700028  5      5      0          16384      8721                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs2_p_1
    700000022701028  6      6      0          16384      14112                 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs3_p_1
    700000022702028  7      7      0          8192       7221                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs1_p_1
    700000022706028  8      8      0          8192       7429                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs2_p_1
    700000022707028  9      9      0          8192       7437                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs3_p_1
    700000022708028  10     10     0          16384      16331                 PO-BE- /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_tmpdbspace_p_1
    700000022709028  11     11     0          16384      11822      11879      POSB-C /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_sbspace1_p_1
                                     Metadata 868        4138       868
    70000002270a028  12     12     0          16384      11879      11879      POSB-- /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_tmpsbspace_p_1
                                     Metadata 868        4138       868
    70000002270b028  13     13     0          25000      9617                  PO-B-C /work2/INFORMIX/1210FC13/storage/dbs1
    700000023285028  14     14     0          25000      3124       3125       POBB-C /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_blobdbs1_p_1
     14 active, 32766 maximum
    
    NOTE: The values in the "size" and "free" columns for DBspace chunks are
          displayed in terms of "pgsize" of the DBspace to which they belong.
    
    
    Expanded chunk capacity mode: always
    
    /work2/INFORMIX/1210FC13/skjeong]dbaccess - -
    > create database testdb in datadbs1 with log;
    
    Database created.
    
    > create table "informix".mycatalog
    > (
    >     catalog_num serial not null ,
    >     stock_num smallint,
    >     manu_code char(3),
    >     cat_descr text in table,
    >     cat_picture byte in blobdbs1,
    >     cat_advert varchar(255,65)
    > ) in data8dbs1;
    
    Table created.
    
    > select t.tabname[1,18], b.tabid, c.colname[1,18], b.colno,
    >        b.type, b.spacename[1,10]
    >   from sysblobs b, systables t, syscolumns c
    >  where b.tabid = t.tabid
    >    and b.colno = c.colno
    >    and t.tabid = c.tabid
    >  order by tabname, colname;
    
    
    tabname                  tabid colname             colno type spacename
    
    mycatalog                  100 cat_descr               4 M    datadbs1
    mycatalog                  100 cat_picture             5 M    blobdbs1
    sysdirectives               48 directive               3 M
    sysdirectives               48 directivecode           4 M
    sysdirectives               48 query                   2 M
    sysfragments                25 exprarr                12 M
    sysfragments                25 exprbin                11 M
    sysfragments                25 exprtext               10 M
    
    8 row(s) retrieved.
    
    > ^C/work2/INFORMIX/1210FC13/skjeong]onstat -d update
    
    IBM Informix Dynamic Server Version 12.10.FC13WE -- On-Line -- Up 11 days 23:36:04 -- 218096 Kbytes
    
    Dbspaces
    address          number   flags      fchunk   nchunks  pgsize   flags    owner    name
    700000020478028  1        0x14020001 1        1        4096     N  BAE   informix rootdbs
    7000000205907a8  2        0x15000001 2        1        4096     N PBAE   informix plog
    7000000205909e8  3        0x14000001 3        1        4096     N  BAE   informix llog
    700000020590c28  4        0x14000001 4        1        4096     N  BAE   informix datadbs1
    7000000226fb028  5        0x14000001 5        1        4096     N  BAE   informix datadbs2
    7000000226fb268  6        0x14000001 6        1        4096     N  BAE   informix datadbs3
    7000000226fb4a8  7        0x14000001 7        1        8192     N  BAE   informix data8dbs1
    7000000226fb6e8  8        0x14000001 8        1        8192     N  BAE   informix data8dbs2
    7000000226fb928  9        0x14000001 9        1        8192     N  BAE   informix data8dbs3
    7000000226fbb68  10       0x14002001 10       1        4096     N TBAE   informix tmpdbspace
    7000000226fbda8  11       0x14008001 11       1        4096     N SBAE   informix sbspace1
    7000000226fd028  12       0x1400a001 12       1        4096     N UBAE   informix tmpsbspace
    7000000226fd268  13       0x14000001 13       1        4096     N  BAE   informix dbs1
    700000025c03d10  14       0x14020011 14       1        32768    N BBAE   informix blobdbs1
     14 active, 2047 maximum
    
    Waiting for server to update BLOB chunk statistics...
    
    Chunks
    address          chunk/dbs     offset     size       free       bpages     flags pathname
    700000020478268  1      1      0          38912      18864                 PO-B-C /work2/INFORMIX/1210FC13/storage/rootdbs
    7000000226fd4a8  2      2      0          18455      2071                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_plog_p_1
    7000000226fe028  3      3      0          56569      8900                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_llog_p_1
    7000000226ff028  4      4      0          16384      7200                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs1_p_1
    700000022700028  5      5      0          16384      8721                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs2_p_1
    700000022701028  6      6      0          16384      14112                 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs3_p_1
    700000022702028  7      7      0          8192       7221                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs1_p_1
    700000022706028  8      8      0          8192       7429                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs2_p_1
    700000022707028  9      9      0          8192       7437                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs3_p_1
    700000022708028  10     10     0          16384      16331                 PO-BE- /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_tmpdbspace_p_1
    700000022709028  11     11     0          16384      11822      11879      POSB-C /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_sbspace1_p_1
                                     Metadata 868        4138       868
    70000002270a028  12     12     0          16384      11879      11879      POSB-- /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_tmpsbspace_p_1
                                     Metadata 868        4138       868
    70000002270b028  13     13     0          25000      9617                  PO-B-C /work2/INFORMIX/1210FC13/storage/dbs1
    700000023285028  14     14     0          25000      3124       3125       POBB-C /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_blobdbs1_p_1
     14 active, 32766 maximum
    
    NOTE: The values in the "size" and "free" columns for DBspace chunks are
          displayed in terms of "pgsize" of the DBspace to which they belong.
    
    
    Expanded chunk capacity mode: always
    
    /work2/INFORMIX/1210FC13/skjeong]dbaccess testdb -
    
    Database selected.
    
    > load from /work2/skjeong/1150scripts/catalog.unl insert into mycatalog;
    
    74 row(s) loaded.
    
    /work2/INFORMIX/1210FC13/skjeong]onmode -l;onmode -l;ontape -s -L 0 -t /dev/null
    Archive to tape device '/dev/null' is complete.
    
    Program over.
    /work2/INFORMIX/1210FC13/skjeong]onstat -d update
    
    IBM Informix Dynamic Server Version 12.10.FC13WE -- On-Line -- Up 11 days 23:36:45 -- 218096 Kbytes
    
    Dbspaces
    address          number   flags      fchunk   nchunks  pgsize   flags    owner    name
    700000020478028  1        0x14020001 1        1        4096     N  BAE   informix rootdbs
    7000000205907a8  2        0x15000001 2        1        4096     N PBAE   informix plog
    7000000205909e8  3        0x14000001 3        1        4096     N  BAE   informix llog
    700000020590c28  4        0x14000001 4        1        4096     N  BAE   informix datadbs1
    7000000226fb028  5        0x14000001 5        1        4096     N  BAE   informix datadbs2
    7000000226fb268  6        0x14000001 6        1        4096     N  BAE   informix datadbs3
    7000000226fb4a8  7        0x14000001 7        1        8192     N  BAE   informix data8dbs1
    7000000226fb6e8  8        0x14000001 8        1        8192     N  BAE   informix data8dbs2
    7000000226fb928  9        0x14000001 9        1        8192     N  BAE   informix data8dbs3
    7000000226fbb68  10       0x14002001 10       1        4096     N TBAE   informix tmpdbspace
    7000000226fbda8  11       0x14008001 11       1        4096     N SBAE   informix sbspace1
    7000000226fd028  12       0x1400a001 12       1        4096     N UBAE   informix tmpsbspace
    7000000226fd268  13       0x14000001 13       1        4096     N  BAE   informix dbs1
    700000025c03d10  14       0x14020011 14       1        32768    N BBAE   informix blobdbs1
     14 active, 2047 maximum
    
    Waiting for server to update BLOB chunk statistics...
    
    Chunks
    address          chunk/dbs     offset     size       free       bpages     flags pathname
    700000020478268  1      1      0          38912      18864                 PO-B-C /work2/INFORMIX/1210FC13/storage/rootdbs
    7000000226fd4a8  2      2      0          18455      2071                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_plog_p_1
    7000000226fe028  3      3      0          56569      8900                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_llog_p_1
    7000000226ff028  4      4      0          16384      7200                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs1_p_1
    700000022700028  5      5      0          16384      8721                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs2_p_1
    700000022701028  6      6      0          16384      14112                 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs3_p_1
    700000022702028  7      7      0          8192       7213                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs1_p_1
    700000022706028  8      8      0          8192       7429                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs2_p_1
    700000022707028  9      9      0          8192       7437                  PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs3_p_1
    700000022708028  10     10     0          16384      16331                 PO-BE- /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_tmpdbspace_p_1
    700000022709028  11     11     0          16384      11822      11879      POSB-C /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_sbspace1_p_1
                                     Metadata 868        4138       868
    70000002270a028  12     12     0          16384      11879      11879      POSB-- /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_tmpsbspace_p_1
                                     Metadata 868        4138       868
    70000002270b028  13     13     0          25000      9617                  PO-B-C /work2/INFORMIX/1210FC13/storage/dbs1
    700000023285028  14     14     0          25000      3050       3125       POBB-C /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_blobdbs1_p_1
     14 active, 32766 maximum
    
    NOTE: The values in the "size" and "free" columns for DBspace chunks are
          displayed in terms of "pgsize" of the DBspace to which they belong.
    
    
    Expanded chunk capacity mode: always
    
    /work2/INFORMIX/1210FC13/skjeong]​


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------