FreeTDS Development Group

[michael.peppler AT bnpparibas.com]

Hmmm...

Did you know that sysstat2 is actually documented?


Decimal
Hex
Status
Table 1-16: sysstat2 control bits in the sysobjects table 


1
0x1
Table has a referential constraint
2
0x2
Table has a foreign key constraint
4
0x4
Table has more than one check constraint
8
0x8
Table has a primary key constraint
16
0x10
Stored procedure can execute only in chained transaction mode
32
0x20
Stored procedure can execute in any transaction mode
64
0x40
Table has an IDENTITY field
128
0x80
Not used
256
0x100
Not used
512
0x200
Table does not contain variable-length columns
1024
0x400
Table is remote
2048
0x800
Table is a proxy table created with the existing keyword
4096
0x1000
Not used
8192
0x2000
Table uses allpages locking scheme
16384
0x4000
Table uses datapages locking scheme
32768
0x8000
Table uses datarows locking scheme
65536
0x10000
Table was created in a version 11.9 or later version of the server
131072
0x20000
Table has a clustered index
262144
0x40000
Object represents an Embedded SQL procedure
524288
0x80000
Not used
16777216
0x1000000
Object represents an access rule.
33554432
0x2000000
Object represents a SQLJ stored procedure.
67108864
0x4000000
Object represents an OR access rule.

(hopefully the above will be readable - I'm unfortunately using Lotus 
Notes at work...)

According to this - 0x2000 means that the table is an allpages locking 
table, and 0x200 is set if the table does NOT have any variable length 
columns.

Vis:

[497] DBA_SQL.testdb.1> create table bit_test(id int, a bit, b bit, c 
varchar(20), d bit);

[501] DBA_SQL.testdb.1> create table bit_test2(id int, a bit, b bit, c 
char(20), d bit);

[503] DBA_SQL.testdb.1> select inttohex(sysstat2) from sysobjects where id 
= object_id('bit_test2'); 
 --------
 00012200

(1 row affected)
[504] DBA_SQL.testdb.1> select inttohex(sysstat2) from sysobjects where id 
= object_id('bit_test');

 --------
 00012000

(1 row affected)
[505] DBA_SQL.testdb.1>

This is on ASE 15.0.1, but I don't think the definition of sysstat2 has 
changed in that release.

Michael




Internet
jklowden AT freetds.org@lists.ibiblio.org - 19.12.2006 04:50
 
Please respond to freetds AT lists.ibiblio.org
Sent by:        freetds-bounces AT lists.ibiblio.org
To:     freetds
cc: 
Subject:        [freetds] The Mystery of sysstat2

If you've ever wanted to contribute to FreeTDS but C wasn't your cup of
tea, your ship has come in.  I could use a little detective work, and the
answer might at least partly exist in your database.  Specifically, in
your sysobjects table.

The question is: what is the meaning of sysobjects.sysstat2 on Sybase?  In
particular, what does it tell us about how bits are packed in a bcp row
image?  We know Sybase uses this column to determine the packing of BIT
columns, but we don't know what the number means.

Now, I suppose we could call up Sybase and ask, but that might require
more social engineering skills than I've got, so I thought a little
sampling might be in order.

The question to you is: for tables having at least two BIT columns, what
value is in that tables's sysobjects.sysstat2?

I have the following so far:

[39] alex.FreeTDS.1> select count(*), min(cast(o.name as varchar(10))) as
tablename, o.sysstat2 from sysobjects o join syscolumns c on o.id = c.id
where c.type = 50 group by o.sysstat2
[39] alex.FreeTDS.2> go
tablename  sysstat2
----------- ---------- -----------
2 systypes             0
10 bittypes1        73728 (0x00012000)
17 bittypes         74240 (0x00012200)

There appears to be some significance in 2nd LSB, 20 vs. 22.

If you have Sybase (not Microsoft) tables with other values for sysstat2,
and your tables have more than one BIT column, I'd like to know that value
and the associated DDL.  You can use the defncopy utility that came with
your FreeTDS installation to make a nice script for me.  Attach the output
and post it here.  And thanks.

If you don't have anything unusual, please post the output of the above
query.  If 10 people all have the same two numbers, that would give me
more confidence that a partial solution would be useful.

Regards,

--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


This message and any attachments (the "message") is
intended solely for the addressees and is confidential. 
If you receive this message in error, please delete it and 
immediately notify the sender. Any use not in accord with 
its purpose, any dissemination or disclosure, either whole 
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message. 
BNP PARIBAS (and its subsidiaries) shall (will) not 
therefore be liable for the message if modified. 

                ---------------------------------------------

Ce message et toutes les pieces jointes (ci-apres le 
"message") sont etablis a l'intention exclusive de ses 
destinataires et sont confidentiels. Si vous recevez ce 
message par erreur, merci de le detruire et d'en avertir 
immediatement l'expediteur. Toute utilisation de ce 
message non conforme a sa destination, toute diffusion 
ou toute publication, totale ou partielle, est interdite, sauf 
autorisation expresse. L'internet ne permettant pas 
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce 
message, dans l'hypothese ou il aurait ete modifie.