dbschema output wrong by Not Null Columns
Hello,
Ifx: 11.70.UC7W3 / 12.10.UC4W1 (Both Versions with same behaviour by the
following problem)
OS: SLES11 SP3
the dbschema-Tool delivers wrong output.
When we do a "dbschema -ss -d DB -t TAB" not all "not null-Columns" where
displayed with the "not null"-clause (All columns are displayed, but some
columns which are definitely "not null" were displayed "nullable").
After we investigate this Problem we see, that columns which have the value
"128" in syscolumns:colattr (independet which coltype it is), are not
displayed right by dbschema. All other columns in our whole database have the
value "0" in the columns syscolumns.colattr and are displayed correct.
Every Column with "128" is wrong in dbschema-output!
Fromwhere comes this Value "128" here ??
We`ve got ca. 900 Tables and ca. 140 are affected.
On Test-Environment we change this value in syscolumns.colattr from "128" to
"0" but then the dbschema-Tool shows nothing from the table (only the first
few lines with information about version,row size, etc., and nothing else), so
that could not be the solution.
Has anyone faced this problem, too ?
Any suggestions or a workaround ?
Thansk in advance.
Sascha
It is not a good idea to change catalog and system tables, Check this link:
http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqlr.doc/ids
_sqr_025.htm?lang=en
When syscolumns.colattr is 128 it means that it is NOT NULL because it is a
PK.
If you don't explicit says it is a NOT NULL column, but rather it is a PK,
dbschema will not show it, but it is there:
[infx1210@tardis ~]$ echo "CREATE TABLE tab_pk1( col1 INT NOT NULL, PRIMARY
KEY (col1) CONSTRAINT pk_tab1)" | dbaccess db1 | uniq
Database selected.
Table created.
Database closed.
[infx1210@tardis ~]$ echo "CREATE TABLE tab_pk2( col1 INT NOT NULL, PRIMARY
KEY (col1) CONSTRAINT pk_tab2)" | dbaccess db1 | uniq
Database selected.
Table created.
Database closed.
[infx1210@tardis ~]$ dbschema -d db1 -t tab_pk1 -q | uniq
{ TABLE "informix".tab_pk1 row size = 4 number of columns = 1 index size =
9 }
create table "informix".tab_pk1
(
col1 integer not null ,
primary key (col1) constraint "informix".pk_tab1
);
revoke all on "informix".tab_pk1 from "public" as "informix";
[infx1210@tardis ~]$ dbschema -d db1 -t tab_pk2 -q | uniq
{ TABLE "informix".tab_pk2 row size = 4 number of columns = 1 index size =
9 }
create table "informix".tab_pk2
(
col1 integer not null ,
primary key (col1) constraint "informix".pk_tab2
);
revoke all on "informix".tab_pk2 from "public" as "informix";
[infx1210@tardis ~]$
If you drop the PK you will see the NOT NULL on both:
[infx1210@tardis ~]$ echo "ALTER TABLE tab_pk1 DROP CONSTRAINT pk_tab1" |
dbaccess db1 | uniq
Database selected.
Table altered.
Database closed.
[infx1210@tardis ~]$ echo "ALTER TABLE tab_pk2 DROP CONSTRAINT pk_tab2" |
dbaccess db1 | uniq
Database selected.
Table altered.
Database closed.
[infx1210@tardis ~]$ dbschema -d db1 -t tab_pk1 -q | uniq
{ TABLE "informix".tab_pk1 row size = 4 number of columns = 1 index size =
0 }
create table "informix".tab_pk1
(
col1 integer not null
);
revoke all on "informix".tab_pk1 from "public" as "informix";
[infx1210@tardis ~]$ dbschema -d db1 -t tab_pk2 -q | uniq
{ TABLE "informix".tab_pk2 row size = 4 number of columns = 1 index size =
0 }
create table "informix".tab_pk2
(
col1 integer not null
);
revoke all on "informix".tab_pk2 from "public" as "informix";
[infx1210@tardis ~]$
Cheers.
On Wed, 19 Aug 2015 at 13:18 SASCHA KURATIS <sascha.kuratis@westfleisch.de>
wrote:
> Hello,
> Ifx: 11.70.UC7W3 / 12.10.UC4W1 (Both Versions with same behaviour by the
> following problem)
> OS: SLES11 SP3
>
> the dbschema-Tool delivers wrong output.
> When we do a "dbschema -ss -d DB -t TAB" not all "not null-Columns" where
> displayed with the "not null"-clause (All columns are displayed, but some
> columns which are definitely "not null" were displayed "nullable").
>
> After we investigate this Problem we see, that columns which have the value
> "128" in syscolumns:colattr (independet which coltype it is), are not
> displayed right by dbschema. All other columns in our whole database have
> the
> value "0" in the columns syscolumns.colattr and are displayed correct.
> Every Column with "128" is wrong in dbschema-output!
> Fromwhere comes this Value "128" here ??
> We`ve got ca. 900 Tables and ca. 140 are affected.
>
> On Test-Environment we change this value in syscolumns.colattr from "128"
> to
> "0" but then the dbschema-Tool shows nothing from the table (only the first
> few lines with information about version,row size, etc., and nothing
> else), so
> that could not be the solution.
>
> Has anyone faced this problem, too ?
> Any suggestions or a workaround ?
>
> Thansk in advance.
>
> Sascha
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c2280cf76c93051da97567
Sacha:
Colattr == 128 means that the column is not explicitely NOT NULL, but is
implied to be NOT NULL because it is part of a primary key. So, dbschema
is displaying the DDL that was used to create the table.
If you really want to see these columns declares explicitely as NOT NULL
then get my dbschema replacement utility, myschema which is included in the
package utils2_ak which you can download from the IIUG Software Repository (
www.iiug.org/software) or from my web site
(www.askdbmgt.commy-utilities.html). The most recent version is always the
one on my web site as I only upload it to the IIUG once several people have
downloaded it from my site and report no problems with the new features or
fixed it contains.
Myschema implements all of dbschema's features except the display of data
distributions (-hd) and adds many neat features. Here's the comparison of
output from both for a table like yours:
Ex:
> dbschema -ss -d sysadmin -t asktest
DBSCHEMA Schema Utility INFORMIX-SQL Version 12.10.FC4W1
{ TABLE "informix".asktest row size = 4 number of columns = 1 index size =
9 }
create table "informix".asktest
(
one integer,
primary key (one)
) extent size 32 next size 32 lock mode row;
revoke all on "informix".asktest from "public" as "informix";
> myschema -s -d sysadmin -t asktest
Writing full schema DDL to: stdout
{ TABLE "informix".asktest row size = 4 number of columns = 1 index size =
12 }
CREATE TABLE "informix".asktest (
one INTEGER NOT NULL
) IN rootdbs EXTENT SIZE 32 NEXT SIZE 32 LOCK MODE ROW;
{
Please review extent sizing and adjust to allow for growth.
}
REVOKE ALL ON "informix".asktest FROM public;-- Index < 3807_1798> is a constraint index. Creating normal index:
P3807_1798.
CREATE UNIQUE INDEX "informix".P3807_1798 ON "informix".asktest (
one ASC
) USING btree IN rootdbs;
GRANT SELECT, UPDATE, INSERT, DELETE, INDEX ON asktest TO "public" AS"informix";
ALTER TABLE asktest ADD CONSTRAINT PRIMARY KEY (
one
) ;
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Wed, Aug 19, 2015 at 7:18 AM, SASCHA KURATIS <
sascha.kuratis@westfleisch.de> wrote:
> Hello,
> Ifx: 11.70.UC7W3 / 12.10.UC4W1 (Both Versions with same behaviour by the
> following problem)
> OS: SLES11 SP3
>
> the dbschema-Tool delivers wrong output.
> When we do a "dbschema -ss -d DB -t TAB" not all "not null-Columns" where
> displayed with the "not null"-clause (All columns are displayed, but some
> columns which are definitely "not null" were displayed "nullable").
>
> After we investigate this Problem we see, that columns which have the value
> "128" in syscolumns:colattr (independet which coltype it is), are not
> displayed right by dbschema. All other columns in our whole database have
> the
> value "0" in the columns syscolumns.colattr and are displayed correct.
> Every Column with "128" is wrong in dbschema-output!
> Fromwhere comes this Value "128" here ??
> We`ve got ca. 900 Tables and ca. 140 are affected.
>
> On Test-Environment we change this value in syscolumns.colattr from "128"
> to
> "0" but then the dbschema-Tool shows nothing from the table (only the first
> few lines with information about version,row size, etc., and nothing
> else), so
> that could not be the solution.
>
> Has anyone faced this problem, too ?
> Any suggestions or a workaround ?
>
> Thansk in advance.
>
> Sascha
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7bdc05f207882f051daa458f