ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- ----- this test shows the system catalogs ----- ----- It is a goal of this test not to display information that ----- can (and will!) change from run to run, such as fields ----- that may eventually be UUIDs or UUID-like. ----- maximumdisplaywidth 500; ij> -- negative tests ----- verify no user ddl allowed on system tables ----- drop table drop table sys.systables; ERROR X0Y56: 'DROP TABLE' is not allowed on the System table 'SYS.SYSTABLES'. ij> -- drop index drop index sys.sysaliases_index2; ERROR 42X62: 'DROP INDEX' is not allowed in the 'SYS' schema. ij> -- create index create index trash on sys.systables(tableid); ERROR 42X62: 'CREATE INDEX' is not allowed in the 'SYS' schema. ij> -- system tables are not updateable autocommit off; ij> delete from sys.systables; ERROR 42Y25: 'SYS.SYSTABLES' is a system table. Users are not allowed to modify the contents of this table. ij> update sys.systables set tablename = tablename | | 'trash'; ERROR 42Y25: 'SYS.SYSTABLES' is a system table. Users are not allowed to modify the contents of this table. ij> insert into sys.systables select * from sys.systables; ERROR 42Y25: 'SYS.SYSTABLES' is a system table. Users are not allowed to modify the contents of this table. ij> get cursor c as 'select tablename from sys.systables for update of tablename'; ERROR 42Y90: FOR UPDATE is not permitted in this type of statement. ij> -- users not allowed to do ddl in sys schema create table sys.usertable(c1 int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYS' schema. ij> create view sys.userview as values 1; ERROR 42908: The CREATE VIEW statement does not include a column list. ij> rollback work; ij> autocommit on; ij> -- positive tests create function gatp(SCH VARCHAR(128), TBL VARCHAR(128)) RETURNS VARCHAR(1000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.TestPropertyInfo.getAllTableProperties' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> create function gaip(SCH VARCHAR(128), TBL VARCHAR(128)) RETURNS VARCHAR(1000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.TestPropertyInfo.getAllIndexProperties' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> -- get the properties for the heaps select tablename,gatp('SYS', tablename) from sys.systables order by tablename; TABLENAME |2 ----- SYSALIASES |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSCHECKS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSCOLPERMS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSCOLUMNS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSCONGLOMERATES |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSCONSTRAINTS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSDEPENDS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSDUMMY1 |{ } SYSFILES |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSFOREIGNKEYS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSKEYS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSROUTINEPERMS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSSCHEMAS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSSTATEMENTS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSSTATISTICS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSTABLEPERMS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSTABLES |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSTRIGGERS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } SYSVIEWS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } ij> -- get the properties for the indexes select conglomeratename, gaip('SYS', conglomeratename) from sys.sysconglomerates where isindex order by conglomeratename; CONGLOMERATENAME |2 ----- SYSALIASES_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSALIASES_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSALIASES_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSCHECKS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSCOLPERMS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSCOLPERMS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSCOLPERMS_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSCOLUMNS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSCOLUMNS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSCONGLOMERATES_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSCONGLOMERATES_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSCONGLOMERATES_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSCONSTRAINTS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSCONSTRAINTS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSCONSTRAINTS_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSDEPENDS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSDEPENDS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSFILES_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSFILES_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSFOREIGNKEYS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSFOREIGNKEYS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSKEYS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSROUTINEPERMS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSROUTINEPERMS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSROUTINEPERMS_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSSCHEMAS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSSCHEMAS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSSTATEMENTS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSSTATEMENTS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSSTATISTICS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSTABLEPERMS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSTABLEPERMS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSTABLEPERMS_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSTABLES_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSTABLES_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSTRIGGERS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSTRIGGERS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSTRIGGERS_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } SYSVIEWS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } ij> select TABLENAME, TABLETYPE from sys.systables order by tablename; TABLENAME |& ----- SYSALIASES |S SYSCHECKS |S SYSCOLPERMS |S SYSCOLUMNS |S SYSCONGLOMERATES |S SYSCONSTRAINTS |S SYSDEPENDS |S SYSDUMMY1 |S SYSFILES |S SYSFOREIGNKEYS |S SYSKEYS |S SYSROUTINEPERMS |S SYSSCHEMAS |S SYSSTATEMENTS |S SYSSTATISTICS |S SYSTABLEPERMS |S SYSTABLES |S SYSTRIGGERS |S SYSVIEWS |S ij> select TABLENAME, COLUMNNAME, COLUMNNUMBER, columndatatype from sys.systables t, sys.syscolumns c where t.TABLEID=c.REFERENCEID order by TABLENAME, COLUMNNAME; TABLENAME |COLUMNNAME |COLUMNNUMB& |COLUMNDATATYPE ----- SYSALIASES |ALIAS |2 |VARCHAR(128) NOT NULL SYSALIASES |ALIASID |1 |CHAR(36) NOT NULL SYSALIASES |ALIASINFO |8 |org.apache.derby.catalog.AliasInfo SYSALIASES |ALIASTYPE |5 |CHAR(1) NOT NULL SYSALIASES |JAVACLASSNAME |4 |LONG VARCHAR NOT NULL SYSALIASES |NAMESPACE |6 |CHAR(1) NOT NULL SYSALIASES |SCHEMAID |3 |CHAR(36) SYSALIASES |SPECIFICNAME |9 |VARCHAR(128) NOT NULL SYSALIASES |SYSTEMALIAS |7 |BOOLEAN NOT NULL SYSCHECKS |CHECKDEFINITION |2 |LONG VARCHAR NOT NULL SYSCHECKS |CONSTRAINTID |1 |CHAR(36) NOT NULL SYSCHECKS |REFERENCEDCOLUMNS |3 |org.apache.derby.catalog.ReferencedColumns NOT NULL SYSCOLPERMS |COLPERMSID |1 |CHAR(36) NOT NULL SYSCOLPERMS |COLUMNS |6 |org.apache.derby.iapi.services.io.FormatableBitSet NOT NULL SYSCOLPERMS |GRANTEE |2 |VARCHAR(128) NOT NULL SYSCOLPERMS |GRANTOR |3 |VARCHAR(128) NOT NULL SYSCOLPERMS |TABLEID |4 |CHAR(36) NOT NULL SYSCOLPERMS |TYPE |5 |CHAR(1) NOT NULL SYSCOLUMNS |AUTOINCREMENTINC |9 |BIGINT SYSCOLUMNS |AUTOINCREMENTSTART |8 |BIGINT SYSCOLUMNS |AUTOINCREMENTVALUE |7 |BIGINT SYSCOLUMNS |COLUMNDATATYPE |4 |org.apache.derby.catalog.TypeDescriptor NOT NULL SYSCOLUMNS |COLUMNDEFAULT |5 |java.io.Serializable SYSCOLUMNS |COLUMNDEFAULTID |6 |CHAR(36) SYSCOLUMNS |COLUMNNAME |2 |VARCHAR(128) NOT NULL SYSCOLUMNS |COLUMNNUMBER |3 |INTEGER NOT NULL SYSCOLUMNS |REFERENCEID |1 |CHAR(36) NOT NULL SYSCONGLOMERATES |CONGLOMERATEID |8 |CHAR(36) NOT NULL SYSCONGLOMERATES |CONGLOMERATENAME |4 |VARCHAR(128) SYSCONGLOMERATES |CONGLOMERATENUMBER |3 |BIGINT NOT NULL SYSCONGLOMERATES |DESCRIPTOR |6 |org.apache.derby.catalog.IndexDescriptor SYSCONGLOMERATES |ISCONSTRAINT |7 |BOOLEAN SYSCONGLOMERATES |ISINDEX |5 |BOOLEAN NOT NULL SYSCONGLOMERATES |SCHEMAID |1 |CHAR(36) NOT NULL SYSCONGLOMERATES |TABLEID |2 |CHAR(36) NOT NULL SYSCONSTRAINTS |CONSTRAINTID |1 |CHAR(36) NOT NULL SYSCONSTRAINTS |CONSTRAINTNAME |3 |VARCHAR(128) NOT NULL SYSCONSTRAINTS |REFERENCECOUNT |7 |INTEGER NOT NULL SYSCONSTRAINTS |SCHEMAID |5 |CHAR(36) NOT NULL SYSCONSTRAINTS |STATE |6 |CHAR(1) NOT NULL SYSCONSTRAINTS |TABLEID |2 |CHAR(36) NOT NULL SYSCONSTRAINTS |TYPE |4 |CHAR(1) NOT NULL SYSDEPENDS |DEPENDENTFINDER |2 |org.apache.derby.catalog.DependableFinder NOT NULL SYSDEPENDS |DEPENDENTID |1 |CHAR(36) NOT NULL SYSDEPENDS |PROVIDERFINDER |4 |org.apache.derby.catalog.DependableFinder NOT NULL SYSDEPENDS |PROVIDERID |3 |CHAR(36) NOT NULL SYSDUMMY1 |IBMREQD |1 |CHAR(1) SYSFILES |FILEID |1 |CHAR(36) NOT NULL SYSFILES |FILENAME |3 |VARCHAR(128) NOT NULL SYSFILES |GENERATIONID |4 |BIGINT NOT NULL SYSFILES |SCHEMAID |2 |CHAR(36) NOT NULL SYSFOREIGNKEYS |CONGLOMERATEID |2 |CHAR(36) NOT NULL SYSFOREIGNKEYS |CONSTRAINTID |1 |CHAR(36) NOT NULL SYSFOREIGNKEYS |DELETERULE |4 |CHAR(1) NOT NULL SYSFOREIGNKEYS |KEYCONSTRAINTID |3 |CHAR(36) NOT NULL SYSFOREIGNKEYS |UPDATERULE |5 |CHAR(1) NOT NULL SYSKEYS |CONGLOMERATEID |2 |CHAR(36) NOT NULL SYSKEYS |CONSTRAINTID |1 |CHAR(36) NOT NULL SYSROUTINEPERMS |ALIASID |4 |CHAR(36) NOT NULL SYSROUTINEPERMS |GRANTEE |2 |VARCHAR(128) NOT NULL SYSROUTINEPERMS |GRANTOPTION |5 |CHAR(1) NOT NULL SYSROUTINEPERMS |GRANTOR |3 |VARCHAR(128) NOT NULL SYSROUTINEPERMS |ROUTINEPERMSID |1 |CHAR(36) NOT NULL SYSSCHEMAS |AUTHORIZATIONID |3 |VARCHAR(128) NOT NULL SYSSCHEMAS |SCHEMAID |1 |CHAR(36) NOT NULL SYSSCHEMAS |SCHEMANAME |2 |VARCHAR(128) NOT NULL SYSSTATEMENTS |COMPILATIONSCHEMAID |8 |CHAR(36) NOT NULL SYSSTATEMENTS |LASTCOMPILED |7 |TIMESTAMP SYSSTATEMENTS |SCHEMAID |3 |CHAR(36) NOT NULL SYSSTATEMENTS |STMTID |1 |CHAR(36) NOT NULL SYSSTATEMENTS |STMTNAME |2 |VARCHAR(128) NOT NULL SYSSTATEMENTS |TEXT |6 |LONG VARCHAR NOT NULL SYSSTATEMENTS |TYPE |4 |CHAR(1) NOT NULL SYSSTATEMENTS |USINGTEXT |9 |LONG VARCHAR SYSSTATEMENTS |VALID |5 |BOOLEAN NOT NULL SYSSTATISTICS |COLCOUNT |7 |INTEGER NOT NULL SYSSTATISTICS |CREATIONTIMESTAMP |4 |TIMESTAMP NOT NULL SYSSTATISTICS |REFERENCEID |2 |CHAR(36) NOT NULL SYSSTATISTICS |STATID |1 |CHAR(36) NOT NULL SYSSTATISTICS |STATISTICS |8 |org.apache.derby.catalog.Statistics NOT NULL SYSSTATISTICS |TABLEID |3 |CHAR(36) NOT NULL SYSSTATISTICS |TYPE |5 |CHAR(1) NOT NULL SYSSTATISTICS |VALID |6 |BOOLEAN NOT NULL SYSTABLEPERMS |DELETEPRIV |6 |CHAR(1) NOT NULL SYSTABLEPERMS |GRANTEE |2 |VARCHAR(128) NOT NULL SYSTABLEPERMS |GRANTOR |3 |VARCHAR(128) NOT NULL SYSTABLEPERMS |INSERTPRIV |7 |CHAR(1) NOT NULL SYSTABLEPERMS |REFERENCESPRIV |9 |CHAR(1) NOT NULL SYSTABLEPERMS |SELECTPRIV |5 |CHAR(1) NOT NULL SYSTABLEPERMS |TABLEID |4 |CHAR(36) NOT NULL SYSTABLEPERMS |TABLEPERMSID |1 |CHAR(36) NOT NULL SYSTABLEPERMS |TRIGGERPRIV |10 |CHAR(1) NOT NULL SYSTABLEPERMS |UPDATEPRIV |8 |CHAR(1) NOT NULL SYSTABLES |LOCKGRANULARITY |5 |CHAR(1) NOT NULL SYSTABLES |SCHEMAID |4 |CHAR(36) NOT NULL SYSTABLES |TABLEID |1 |CHAR(36) NOT NULL SYSTABLES |TABLENAME |2 |VARCHAR(128) NOT NULL SYSTABLES |TABLETYPE |3 |CHAR(1) NOT NULL SYSTRIGGERS |ACTIONSTMTID |11 |CHAR(36) SYSTRIGGERS |CREATIONTIMESTAMP |4 |TIMESTAMP NOT NULL SYSTRIGGERS |EVENT |5 |CHAR(1) NOT NULL SYSTRIGGERS |FIRINGTIME |6 |CHAR(1) NOT NULL SYSTRIGGERS |NEWREFERENCINGNAME |17 |VARCHAR(128) SYSTRIGGERS |OLDREFERENCINGNAME |16 |VARCHAR(128) SYSTRIGGERS |REFERENCEDCOLUMNS |12 |org.apache.derby.catalog.ReferencedColumns SYSTRIGGERS |REFERENCINGNEW |15 |BOOLEAN SYSTRIGGERS |REFERENCINGOLD |14 |BOOLEAN SYSTRIGGERS |SCHEMAID |3 |CHAR(36) NOT NULL SYSTRIGGERS |STATE |8 |CHAR(1) NOT NULL SYSTRIGGERS |TABLEID |9 |CHAR(36) NOT NULL SYSTRIGGERS |TRIGGERDEFINITION |13 |LONG VARCHAR SYSTRIGGERS |TRIGGERID |1 |CHAR(36) NOT NULL SYSTRIGGERS |TRIGGERNAME |2 |VARCHAR(128) NOT NULL SYSTRIGGERS |TYPE |7 |CHAR(1) NOT NULL SYSTRIGGERS |WHENSTMTID |10 |CHAR(36) SYSVIEWS |CHECKOPTION |3 |CHAR(1) NOT NULL SYSVIEWS |COMPILATIONSCHEMAID |4 |CHAR(36) NOT NULL SYSVIEWS |TABLEID |1 |CHAR(36) NOT NULL SYSVIEWS |VIEWDEFINITION |2 |LONG VARCHAR NOT NULL ij> select TABLENAME, ISINDEX from sys.systables t, sys.sysconglomerates c where t.TABLEID=c.TABLEID order by TABLENAME, ISINDEX; TABLENAME |ISIND& ----- SYSALIASES |0 SYSALIASES |1 SYSALIASES |1 SYSALIASES |1 SYSCHECKS |0 SYSCHECKS |1 SYSCOLPERMS |0 SYSCOLPERMS |1 SYSCOLPERMS |1 SYSCOLPERMS |1 SYSCOLUMNS |0 SYSCOLUMNS |1 SYSCOLUMNS |1 SYSCONGLOMERATES |0 SYSCONGLOMERATES |1 SYSCONGLOMERATES |1 SYSCONGLOMERATES |1 SYSCONSTRAINTS |0 SYSCONSTRAINTS |1 SYSCONSTRAINTS |1 SYSCONSTRAINTS |1 SYSDEPENDS |0 SYSDEPENDS |1 SYSDEPENDS |1 SYSDUMMY1 |0 SYSFILES |0 SYSFILES |1 SYSFILES |1 SYSFOREIGNKEYS |0 SYSFOREIGNKEYS |1 SYSFOREIGNKEYS |1 SYSKEYS |0 SYSKEYS |1 SYSROUTINEPERMS |0 SYSROUTINEPERMS |1 SYSROUTINEPERMS |1 SYSROUTINEPERMS |1 SYSSCHEMAS |0 SYSSCHEMAS |1 SYSSCHEMAS |1 SYSSTATEMENTS |0 SYSSTATEMENTS |1 SYSSTATEMENTS |1 SYSSTATISTICS |0 SYSSTATISTICS |1 SYSTABLEPERMS |0 SYSTABLEPERMS |1 SYSTABLEPERMS |1 SYSTABLEPERMS |1 SYSTABLES |0 SYSTABLES |1 SYSTABLES |1 SYSTRIGGERS |0 SYSTRIGGERS |1 SYSTRIGGERS |1 SYSTRIGGERS |1 SYSVIEWS |0 SYSVIEWS |1 ij> create table t (i int, s smallint); 0 rows inserted/updated/deleted ij> select TABLENAME, TABLETYPE from sys.systables order by tablename; TABLENAME |& ----- SYSALIASES |S SYSCHECKS |S SYSCOLPERMS |S SYSCOLUMNS |S SYSCONGLOMERATES |S SYSCONSTRAINTS |S SYSDEPENDS |S SYSDUMMY1 |S SYSFILES |S SYSFOREIGNKEYS |S SYSKEYS |S SYSROUTINEPERMS |S SYSSCHEMAS |S SYSSTATEMENTS |S SYSSTATISTICS |S SYSTABLEPERMS |S SYSTABLES |S SYSTRIGGERS |S SYSVIEWS |S T |T ij> select TABLENAME, COLUMNNAME, COLUMNNUMBER, columndatatype from sys.systables t, sys.syscolumns c where t.TABLEID=c.REFERENCEID order by TABLENAME, COLUMNNAME; TABLENAME |COLUMNNAME |COLUMNNUMB& |COLUMNDATATYPE ----- SYSALIASES |ALIAS |2 |VARCHAR(128) NOT NULL SYSALIASES |ALIASID |1 |CHAR(36) NOT NULL SYSALIASES |ALIASINFO |8 |org.apache.derby.catalog.AliasInfo SYSALIASES |ALIASTYPE |5 |CHAR(1) NOT NULL SYSALIASES |JAVACLASSNAME |4 |LONG VARCHAR NOT NULL SYSALIASES |NAMESPACE |6 |CHAR(1) NOT NULL SYSALIASES |SCHEMAID |3 |CHAR(36) SYSALIASES |SPECIFICNAME |9 |VARCHAR(128) NOT NULL SYSALIASES |SYSTEMALIAS |7 |BOOLEAN NOT NULL SYSCHECKS |CHECKDEFINITION |2 |LONG VARCHAR NOT NULL SYSCHECKS |CONSTRAINTID |1 |CHAR(36) NOT NULL SYSCHECKS |REFERENCEDCOLUMNS |3 |org.apache.derby.catalog.ReferencedColumns NOT NULL SYSCOLPERMS |COLPERMSID |1 |CHAR(36) NOT NULL SYSCOLPERMS |COLUMNS |6 |org.apache.derby.iapi.services.io.FormatableBitSet NOT NULL SYSCOLPERMS |GRANTEE |2 |VARCHAR(128) NOT NULL SYSCOLPERMS |GRANTOR |3 |VARCHAR(128) NOT NULL SYSCOLPERMS |TABLEID |4 |CHAR(36) NOT NULL SYSCOLPERMS |TYPE |5 |CHAR(1) NOT NULL SYSCOLUMNS |AUTOINCREMENTINC |9 |BIGINT SYSCOLUMNS |AUTOINCREMENTSTART |8 |BIGINT SYSCOLUMNS |AUTOINCREMENTVALUE |7 |BIGINT SYSCOLUMNS |COLUMNDATATYPE |4 |org.apache.derby.catalog.TypeDescriptor NOT NULL SYSCOLUMNS |COLUMNDEFAULT |5 |java.io.Serializable SYSCOLUMNS |COLUMNDEFAULTID |6 |CHAR(36) SYSCOLUMNS |COLUMNNAME |2 |VARCHAR(128) NOT NULL SYSCOLUMNS |COLUMNNUMBER |3 |INTEGER NOT NULL SYSCOLUMNS |REFERENCEID |1 |CHAR(36) NOT NULL SYSCONGLOMERATES |CONGLOMERATEID |8 |CHAR(36) NOT NULL SYSCONGLOMERATES |CONGLOMERATENAME |4 |VARCHAR(128) SYSCONGLOMERATES |CONGLOMERATENUMBER |3 |BIGINT NOT NULL SYSCONGLOMERATES |DESCRIPTOR |6 |org.apache.derby.catalog.IndexDescriptor SYSCONGLOMERATES |ISCONSTRAINT |7 |BOOLEAN SYSCONGLOMERATES |ISINDEX |5 |BOOLEAN NOT NULL SYSCONGLOMERATES |SCHEMAID |1 |CHAR(36) NOT NULL SYSCONGLOMERATES |TABLEID |2 |CHAR(36) NOT NULL SYSCONSTRAINTS |CONSTRAINTID |1 |CHAR(36) NOT NULL SYSCONSTRAINTS |CONSTRAINTNAME |3 |VARCHAR(128) NOT NULL SYSCONSTRAINTS |REFERENCECOUNT |7 |INTEGER NOT NULL SYSCONSTRAINTS |SCHEMAID |5 |CHAR(36) NOT NULL SYSCONSTRAINTS |STATE |6 |CHAR(1) NOT NULL SYSCONSTRAINTS |TABLEID |2 |CHAR(36) NOT NULL SYSCONSTRAINTS |TYPE |4 |CHAR(1) NOT NULL SYSDEPENDS |DEPENDENTFINDER |2 |org.apache.derby.catalog.DependableFinder NOT NULL SYSDEPENDS |DEPENDENTID |1 |CHAR(36) NOT NULL SYSDEPENDS |PROVIDERFINDER |4 |org.apache.derby.catalog.DependableFinder NOT NULL SYSDEPENDS |PROVIDERID |3 |CHAR(36) NOT NULL SYSDUMMY1 |IBMREQD |1 |CHAR(1) SYSFILES |FILEID |1 |CHAR(36) NOT NULL SYSFILES |FILENAME |3 |VARCHAR(128) NOT NULL SYSFILES |GENERATIONID |4 |BIGINT NOT NULL SYSFILES |SCHEMAID |2 |CHAR(36) NOT NULL SYSFOREIGNKEYS |CONGLOMERATEID |2 |CHAR(36) NOT NULL SYSFOREIGNKEYS |CONSTRAINTID |1 |CHAR(36) NOT NULL SYSFOREIGNKEYS |DELETERULE |4 |CHAR(1) NOT NULL SYSFOREIGNKEYS |KEYCONSTRAINTID |3 |CHAR(36) NOT NULL SYSFOREIGNKEYS |UPDATERULE |5 |CHAR(1) NOT NULL SYSKEYS |CONGLOMERATEID |2 |CHAR(36) NOT NULL SYSKEYS |CONSTRAINTID |1 |CHAR(36) NOT NULL SYSROUTINEPERMS |ALIASID |4 |CHAR(36) NOT NULL SYSROUTINEPERMS |GRANTEE |2 |VARCHAR(128) NOT NULL SYSROUTINEPERMS |GRANTOPTION |5 |CHAR(1) NOT NULL SYSROUTINEPERMS |GRANTOR |3 |VARCHAR(128) NOT NULL SYSROUTINEPERMS |ROUTINEPERMSID |1 |CHAR(36) NOT NULL SYSSCHEMAS |AUTHORIZATIONID |3 |VARCHAR(128) NOT NULL SYSSCHEMAS |SCHEMAID |1 |CHAR(36) NOT NULL SYSSCHEMAS |SCHEMANAME |2 |VARCHAR(128) NOT NULL SYSSTATEMENTS |COMPILATIONSCHEMAID |8 |CHAR(36) NOT NULL SYSSTATEMENTS |LASTCOMPILED |7 |TIMESTAMP SYSSTATEMENTS |SCHEMAID |3 |CHAR(36) NOT NULL SYSSTATEMENTS |STMTID |1 |CHAR(36) NOT NULL SYSSTATEMENTS |STMTNAME |2 |VARCHAR(128) NOT NULL SYSSTATEMENTS |TEXT |6 |LONG VARCHAR NOT NULL SYSSTATEMENTS |TYPE |4 |CHAR(1) NOT NULL SYSSTATEMENTS |USINGTEXT |9 |LONG VARCHAR SYSSTATEMENTS |VALID |5 |BOOLEAN NOT NULL SYSSTATISTICS |COLCOUNT |7 |INTEGER NOT NULL SYSSTATISTICS |CREATIONTIMESTAMP |4 |TIMESTAMP NOT NULL SYSSTATISTICS |REFERENCEID |2 |CHAR(36) NOT NULL SYSSTATISTICS |STATID |1 |CHAR(36) NOT NULL SYSSTATISTICS |STATISTICS |8 |org.apache.derby.catalog.Statistics NOT NULL SYSSTATISTICS |TABLEID |3 |CHAR(36) NOT NULL SYSSTATISTICS |TYPE |5 |CHAR(1) NOT NULL SYSSTATISTICS |VALID |6 |BOOLEAN NOT NULL SYSTABLEPERMS |DELETEPRIV |6 |CHAR(1) NOT NULL SYSTABLEPERMS |GRANTEE |2 |VARCHAR(128) NOT NULL SYSTABLEPERMS |GRANTOR |3 |VARCHAR(128) NOT NULL SYSTABLEPERMS |INSERTPRIV |7 |CHAR(1) NOT NULL SYSTABLEPERMS |REFERENCESPRIV |9 |CHAR(1) NOT NULL SYSTABLEPERMS |SELECTPRIV |5 |CHAR(1) NOT NULL SYSTABLEPERMS |TABLEID |4 |CHAR(36) NOT NULL SYSTABLEPERMS |TABLEPERMSID |1 |CHAR(36) NOT NULL SYSTABLEPERMS |TRIGGERPRIV |10 |CHAR(1) NOT NULL SYSTABLEPERMS |UPDATEPRIV |8 |CHAR(1) NOT NULL SYSTABLES |LOCKGRANULARITY |5 |CHAR(1) NOT NULL SYSTABLES |SCHEMAID |4 |CHAR(36) NOT NULL SYSTABLES |TABLEID |1 |CHAR(36) NOT NULL SYSTABLES |TABLENAME |2 |VARCHAR(128) NOT NULL SYSTABLES |TABLETYPE |3 |CHAR(1) NOT NULL SYSTRIGGERS |ACTIONSTMTID |11 |CHAR(36) SYSTRIGGERS |CREATIONTIMESTAMP |4 |TIMESTAMP NOT NULL SYSTRIGGERS |EVENT |5 |CHAR(1) NOT NULL SYSTRIGGERS |FIRINGTIME |6 |CHAR(1) NOT NULL SYSTRIGGERS |NEWREFERENCINGNAME |17 |VARCHAR(128) SYSTRIGGERS |OLDREFERENCINGNAME |16 |VARCHAR(128) SYSTRIGGERS |REFERENCEDCOLUMNS |12 |org.apache.derby.catalog.ReferencedColumns SYSTRIGGERS |REFERENCINGNEW |15 |BOOLEAN SYSTRIGGERS |REFERENCINGOLD |14 |BOOLEAN SYSTRIGGERS |SCHEMAID |3 |CHAR(36) NOT NULL SYSTRIGGERS |STATE |8 |CHAR(1) NOT NULL SYSTRIGGERS |TABLEID |9 |CHAR(36) NOT NULL SYSTRIGGERS |TRIGGERDEFINITION |13 |LONG VARCHAR SYSTRIGGERS |TRIGGERID |1 |CHAR(36) NOT NULL SYSTRIGGERS |TRIGGERNAME |2 |VARCHAR(128) NOT NULL SYSTRIGGERS |TYPE |7 |CHAR(1) NOT NULL SYSTRIGGERS |WHENSTMTID |10 |CHAR(36) SYSVIEWS |CHECKOPTION |3 |CHAR(1) NOT NULL SYSVIEWS |COMPILATIONSCHEMAID |4 |CHAR(36) NOT NULL SYSVIEWS |TABLEID |1 |CHAR(36) NOT NULL SYSVIEWS |VIEWDEFINITION |2 |LONG VARCHAR NOT NULL T |I |1 |INTEGER T |S |2 |SMALLINT ij> select TABLENAME, ISINDEX from sys.systables t, sys.sysconglomerates c where t.TABLEID=c.TABLEID order by TABLENAME, ISINDEX; TABLENAME |ISIND& ----- SYSALIASES |0 SYSALIASES |1 SYSALIASES |1 SYSALIASES |1 SYSCHECKS |0 SYSCHECKS |1 SYSCOLPERMS |0 SYSCOLPERMS |1 SYSCOLPERMS |1 SYSCOLPERMS |1 SYSCOLUMNS |0 SYSCOLUMNS |1 SYSCOLUMNS |1 SYSCONGLOMERATES |0 SYSCONGLOMERATES |1 SYSCONGLOMERATES |1 SYSCONGLOMERATES |1 SYSCONSTRAINTS |0 SYSCONSTRAINTS |1 SYSCONSTRAINTS |1 SYSCONSTRAINTS |1 SYSDEPENDS |0 SYSDEPENDS |1 SYSDEPENDS |1 SYSDUMMY1 |0 SYSFILES |0 SYSFILES |1 SYSFILES |1 SYSFOREIGNKEYS |0 SYSFOREIGNKEYS |1 SYSFOREIGNKEYS |1 SYSKEYS |0 SYSKEYS |1 SYSROUTINEPERMS |0 SYSROUTINEPERMS |1 SYSROUTINEPERMS |1 SYSROUTINEPERMS |1 SYSSCHEMAS |0 SYSSCHEMAS |1 SYSSCHEMAS |1 SYSSTATEMENTS |0 SYSSTATEMENTS |1 SYSSTATEMENTS |1 SYSSTATISTICS |0 SYSSTATISTICS |1 SYSTABLEPERMS |0 SYSTABLEPERMS |1 SYSTABLEPERMS |1 SYSTABLEPERMS |1 SYSTABLES |0 SYSTABLES |1 SYSTABLES |1 SYSTRIGGERS |0 SYSTRIGGERS |1 SYSTRIGGERS |1 SYSTRIGGERS |1 SYSVIEWS |0 SYSVIEWS |1 T |0 ij> -- > 30 char table and column names create table t234567890123456789012345678901234567890 (c23456789012345678901234567890 int); 0 rows inserted/updated/deleted ij> select TABLENAME from sys.systables where length(TABLENAME) > 30 order by tablename; TABLENAME ----- T234567890123456789012345678901234567890 ij> select COLUMNNAME from sys.syscolumns where {fn length(COLUMNNAME)} > 30 order by columnname; COLUMNNAME ----- ij> -- primary key create table primkey1 (c1 int not null constraint prim1 primary key); 0 rows inserted/updated/deleted ij> select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where c.tableid = t.tableid and not t.tablename like 'UNNAMED%' order by c.constraintname; CONSTRAINTNAME |& ----- PRIM1 |P ij> create table unnamed_primkey2 (c1 int not null primary key); 0 rows inserted/updated/deleted ij> select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where c.tableid = t.tableid and not t.tablename like 'UNNAMED%' order by c.constraintname; CONSTRAINTNAME |& ----- PRIM1 |P ij> create table primkey3 (c1 int not null, c2 int not null, constraint prim3 primary key(c2, c1)); 0 rows inserted/updated/deleted ij> select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where c.tableid = t.tableid and not t.tablename like 'UNNAMED%' order by c.constraintname; CONSTRAINTNAME |& ----- PRIM1 |P PRIM3 |P ij> create table uniquekey1 (c1 int not null constraint uniq1 unique); 0 rows inserted/updated/deleted ij> select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where c.tableid = t.tableid and not t.tablename like 'UNNAMED%' order by c.constraintname; CONSTRAINTNAME |& ----- PRIM1 |P PRIM3 |P UNIQ1 |U ij> create table unnamed_uniquekey2 (c1 int not null unique); 0 rows inserted/updated/deleted ij> select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where c.tableid = t.tableid and not t.tablename like 'UNNAMED%' order by c.constraintname; CONSTRAINTNAME |& ----- PRIM1 |P PRIM3 |P UNIQ1 |U ij> create table uniquekey3 (c1 int not null, c2 int not null, constraint uniq3 unique(c2, c1)); 0 rows inserted/updated/deleted ij> select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where c.tableid = t.tableid and not t.tablename like 'UNNAMED%' order by c.constraintname; CONSTRAINTNAME |& ----- PRIM1 |P PRIM3 |P UNIQ1 |U UNIQ3 |U ij> -- views create view dummyview as select * from t, uniquekey3; 0 rows inserted/updated/deleted ij> select tablename from sys.systables t, sys.sysviews v where t.tableid = v.tableid order by tablename; TABLENAME ----- DUMMYVIEW ij> -- RESOLVE - add selects from sysdepends when simplified ----- verify the consistency of the indexes on the system catalogs select tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', tablename) from sys.systables where tabletype = 'S' and tablename != 'SYSDUMMY1' order by tablename; TABLENAME |2 ----- SYSALIASES |1 SYSCHECKS |1 SYSCOLPERMS |1 SYSCOLUMNS |1 SYSCONGLOMERATES |1 SYSCONSTRAINTS |1 SYSDEPENDS |1 SYSFILES |1 SYSFOREIGNKEYS |1 SYSKEYS |1 SYSROUTINEPERMS |1 SYSSCHEMAS |1 SYSSTATEMENTS |1 SYSSTATISTICS |1 SYSTABLEPERMS |1 SYSTABLES |1 SYSTRIGGERS |1 SYSVIEWS |1 ij> -- drop views drop view dummyview; 0 rows inserted/updated/deleted ij> -- added for bug 3544. make sure we can ship columndatatype across the wire. ----- (this test is also part of the rjconnmats suite and will run under rmijdbc). create table decimal_tab (dcol decimal(5,2), ncol numeric(5,2) default 1.0); 0 rows inserted/updated/deleted ij> select columnname, columnnumber, columndatatype from sys.syscolumns where columnname IN ('DCOL', 'NCOL') order by columnname; COLUMNNAME |COLUMNNUMB& |COLUMNDATATYPE ----- DCOL |1 |DECIMAL(5,2) NCOL |2 |NUMERIC(5,2) ij> -- now just for fun lets select some other stuff from the system catalogs ----- which is used by Cloudview and make sure we can ship it over the wire. create index decimal_tab_idx on decimal_tab(dcol); 0 rows inserted/updated/deleted ij> -- index descriptor. select conglomeratename, descriptor from sys.sysconglomerates where conglomeratename = 'DECIMAL_TAB_IDX' order by conglomeratename; CONGLOMERATENAME |DESCRIPTOR ----- DECIMAL_TAB_IDX |BTREE (1) ij> create trigger t1 after update on decimal_tab for each row mode db2sql values 1; 0 rows inserted/updated/deleted ij> -- referenced columns. select triggername, referencedcolumns from sys.systriggers order by triggername; TRIGGERNAME |REFERENCEDCOLUMNS ----- T1 |NULL ij> --confirm for DERBY-318 create table defaultAutoinc(autoinccol int generated by default as identity); 0 rows inserted/updated/deleted ij> select * from SYS.SYSCOLUMNS where COLUMNNAME = 'AUTOINCCOL'; REFERENCEID |COLUMNNAME |COLUMNNUMB& |COLUMNDATATYPE |COLUMNDEFAULT ----- xxxxFILTERED-UUIDxxxx |AUTOINCCOL |1 |INTEGER NOT NULL |GENERATED_BY_DEFAULT |xxxxFILTERED-UUIDxxxx |1 |1 |1 ij> -- drop tables drop table t; 0 rows inserted/updated/deleted ij> drop table t234567890123456789012345678901234567890; 0 rows inserted/updated/deleted ij> drop table primkey1; 0 rows inserted/updated/deleted ij> drop table unnamed_primkey2; 0 rows inserted/updated/deleted ij> drop table primkey3; 0 rows inserted/updated/deleted ij> drop table uniquekey1; 0 rows inserted/updated/deleted ij> drop table unnamed_uniquekey2; 0 rows inserted/updated/deleted ij> drop table uniquekey3; 0 rows inserted/updated/deleted ij> drop table defaultAutoinc; 0 rows inserted/updated/deleted ij> -- verify the consistency of the indexes on the system catalogs select tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', tablename) from sys.systables where tabletype = 'S' and tablename != 'SYSDUMMY1' order by tablename; TABLENAME |2 ----- SYSALIASES |1 SYSCHECKS |1 SYSCOLPERMS |1 SYSCOLUMNS |1 SYSCONGLOMERATES |1 SYSCONSTRAINTS |1 SYSDEPENDS |1 SYSFILES |1 SYSFOREIGNKEYS |1 SYSKEYS |1 SYSROUTINEPERMS |1 SYSSCHEMAS |1 SYSSTATEMENTS |1 SYSSTATISTICS |1 SYSTABLEPERMS |1 SYSTABLES |1 SYSTRIGGERS |1 SYSVIEWS |1 ij>