Catalog tools in CSQL

Catalog is a tool in CSQL, which provides the information about system metadata and user metadata of tables stored in the CSQL database.It is also provides information about table,index,field etc

Usage: catalog [-u username] [-p passwd] [-l] [-i] [-d] [-T table] [-I index] [-D <lock|trans|proc|chunk>]
l -> list all table with field information
i -> reinitialize catalog tables. Drops all tables.
d -> print db usage statistics
T -> list table information
I -> list index information
D -> print debug information for system tables
Note: If multiple options are specified, last one will be considered.

If the user name is not mentioned then it will list all the tables with only their names.If multiple options are specified then only the last option is considered for processing.

Let us discuss above option briefly one by one with example

$ csql
CSQL> create table t1(f1 int, f2 char(20), f3 float);
Statement Executed
CSQL>create table emp(eid int, name char(20), sal float);
statement Executed
CSQL>quit;
$

We have created two tables with name as t1 and emp .then only catalog tool give the table information.

$ catalog

This is a default behavior as mentioned before since there is no username provided.
<TableNames>
<TableName> t1 </TableName>
<TableName> emp </TableName>
</TableNames>

$ catalog -u root -p manager -l

This will gives Table information of all the table .Information like field name,type,length,constraint info,default value etc. of all field of all tables.

<Table Information of all tables>
<TableInfo>
<TableName> t1 </TableName>
<FieldInfo>
<FieldName> f1 </FieldName>
<Type> 0 </Type>
<Length> 4 </Length>
<Primary> 0 </Primary>
<Null> 0 </Null>
<Default> 0 </Default>
<DefaultValue> </DefaultValue>
</FieldInfo>
<FieldInfo>
<FieldName> f2 </FieldName>
<Type> 30 </Type>
<Length> 21 </Length>
<Primary> 0 </Primary>
<Null> 0 </Null>
<Default> 0 </Default>
<DefaultValue> </DefaultValue>
</FieldInfo>
<FieldInfo>
<FieldName> f3 </FieldName>
<Type> 11 </Type>
<Length> 4 </Length>
<Primary> 0 </Primary>
<Null> 0 </Null>
<Default> 0 </Default>
<DefaultValue> </DefaultValue>
</FieldInfo>
</TableInfo>
<TableInfo>
<TableName> emp </TableName>
<FieldInfo>
<FieldName> eid </FieldName>
<Type> 0 </Type>
<Length> 4 </Length>
<Primary> 0 </Primary>
<Null> 0 </Null>
<Default> 0 </Default>
<DefaultValue> </DefaultValue>
</FieldInfo>
<FieldInfo>
<FieldName> name </FieldName>
<Type> 30 </Type>
<Length> 21 </Length>
<Primary> 0 </Primary>
<Null> 0 </Null>
<Default> 0 </Default>
<DefaultValue> </DefaultValue>
</FieldInfo>
<FieldInfo>
<FieldName> sal </FieldName>
<Type> 11 </Type>
<Length> 4 </Length>
<Primary> 0 </Primary>
<Null> 0 </Null>
<Default> 0 </Default>
<DefaultValue> </DefaultValue>
</FieldInfo>
</TableInfo>
</Table Information of all tables>

$ catalog -u root -p manager -d

This will gives database statistics of both system ae well as user database.For the system database it displays Max Size,First Page, Total Pages, Used Normal Pages, Used Merged Pages, Chunks Used, proc table info, Transaction table info and Lock table info etc. and in user database it displays Max Size,First Page, Total Pages, Used Normal Pages, Used Merged Pages, Chunks Used etc.

<Database Usage Statistics>
<DatabaseStatistics>
<Database Name> SYSTEMDB </Database Name>
<Max Size> 1048576 </Max Size>
<First Page> 17d84000 </First Page>
<Total Pages> 109 </Total Pages>
<Used Normal Pages> 11 </Used Normal Pages>
<Used Merged Pages> 2 </Used Merged Pages>
<Chunks Used> 13 </Chunks Used>
</DatabaseStatistics>
<ProcTable>
<UsedSlots> 1 </UsedSlots>
<FreeSlots> 99 </FreeSlots>
</ProcTable>
<TransactionTable>
<UsedSlots> 0 </UsedSlots>
<FreeSlots> 100 </FreeSlots>
<UndoLogs>
<TotalNodes> 0 </TotalNodes>
</UndoLogs>
</TransactionTable>
<LockTable>
<TotalBuckets> 2048 </TotalBuckets>
<UsedBuckets> 0 </UsedBuckets>
<TotalLockNodes> 0 </TotalLockNodes>
</LockTable>
<DatabaseStatistics>
<Database Name> userdb </Database Name>
<Max Size> 10485760 </Max Size>
<First Page> 17e7a000 </First Page>
<Total Pages> 1279 </Total Pages>
<Used Normal Pages> 2 </Used Normal Pages>
<Used Merged Pages> 0 </Used Merged Pages>
<Chunks Used> 2 </Chunks Used>
</DatabaseStatistics>
</Database Usage Statistics>

Let us insert some records into emp table.

$ csql
CSQL>insert into emp values (12,’jitu’,203.50);
Statement Executed: Rows Affected = 1
CSQL>insert into emp values (13,’papu’,451.75);
Statement Executed: Rows Affected = 1
CSQL>insert into emp values (14,’adi’,543.21);
Statement Executed: Rows Affected = 1

CSQL>quit;

$ catalog -u root -p manager -T emp

This will list Field and Index information of the table specified.It will show Table Name ,Tuple Count ,Pages Used,Space Used , Indexes ,Tuple Length ,no of Fields and index information as index name.

$ catalog -u root -p manager -T emp
<Table Info>
<TableName> emp </TableName>
<TupleCount> 3 </TupleCount>
<PagesUsed> 1 </PagesUsed>
<SpaceUsed> 136 </SpaceUsed>
<Indexes> 0 <Indexes>
<TupleLength> 36 </TupleLength>
<Fields> 3 </Fields>
<Indexes>
</Indexes>
</Table Info>

Let us create another table with a primary key .

CSQL>create table stu (roll int,name char(10),primary key(f1));
Statement Executed:

$ catalog -u root -p manager -I stu_idx1_Primary

This will list index information of the index specified.It alsi displays hash bucket info and index node info etc

<Index Info>
<IndexName> stu_idx1_Primary </IndexName>
<Unique> 1 </Unique>
<HashBucket>
<TotalPages> 1 </TotalPages>
<TotalBuckets> 1009 </TotalBuckets>
</HashBucket>
<IndexNodes>
<TotalPages> 1 </TotalPages>
<TotalNodes> 0 </TotalNodes>
<IndexNodes>
<Index Info>

$ catalog -u root -p manager -D proc

This will list process table information.It gives the information about process id,thread id ,mutex list ,trasaction list ,used and unused slot etc

<ProcTable>
<THREADINFO>
<PID> 7298 </PID>
<THRID> 0 </THRID>
<WAIT> 0 </WAIT>
<MUTEXLIST>
</MUTEXLIST>
<TRANSLIST>
</TRANSLIST>
</THREADINFO>
<UsedSlots> 1 </UsedSlots>
<FreeSlots> 99 </FreeSlots>
</ProcTable>

$ catalog -u root -p manager -D lock

This will list lock table information.It will show information like to no of bucket and total pages used for lock.

<LockTable>
<TotalUsedBuckets> 0 </TotalUsedBuckets>
<TotalPages> 1 </TotalPages>
</LockTable>

$ catalog -u root -p manager -D trans

This will list transaction table information and undo log information.

<TransactionTable>
<UsedSlots> 0 </UsedSlots>
<FreeSlots> 100 </FreeSlots>
<UndoLogs>
<TotalNodes> 0 </TotalNodes>
<TotalPages> 1 </TotalPages>
</UndoLogs>
</TransactionTable>

$ catalog -u root -p manager –ild

This give result same as -d option

<TableNames>
<TableName> t1 </TableName>
<TableName> emp </TableName>
<TableName> stu </TableName>
</TableNames>

$ catalog -u root -p manager -i

This will drop all the tables from the database.

<DropTable>
<TableName> t1 </TableName>
<TableName> emp </TableName>
<TableName> stu </TableName>
</DropTable>

$ catalog -u root -p manager -D chunk

This will show the information regarding chunk of the database. It shows chunk id ,chunk name,total no of data node, size of the data node and allocation type. Let says database is empty. The following information will display on screen.

<Chunk information>
<System Chunk >
<Chunk Id> 0 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > UserChunkTableId </ChunkName>
<TotalDataNodes> 0 </TotalDataNodes>
<SizeOfDataNodes> 112 </SizeOfDataNodes>
<Allocation Type>FixedSizeAllocator</Allocation Type>
<Chunk Id> 1 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > LockTableHashBucketId </ChunkName>
<TotalDataNodes> 1 </TotalDataNodes>
<SizeOfDataNodes> 57348 </SizeOfDataNodes>
<Allocation Type>FixedSizeAllocator</Allocation Type>
<Chunk Id> 2 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > LockTableMutexId </ChunkName>
<TotalDataNodes> 0 </TotalDataNodes>
<SizeOfDataNodes> 49156 </SizeOfDataNodes>
<Allocation Type>FixedSizeAllocator</Allocation Type>
<Chunk Id> 3 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > LockTableId </ChunkName>
<TotalDataNodes> 0 </TotalDataNodes>
<SizeOfDataNodes> 20 </SizeOfDataNodes>
<Allocation Type>FixedSizeAllocator</Allocation Type>
<Chunk Id> 4 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > TransHasTableId </ChunkName>
<TotalDataNodes> 0 </TotalDataNodes>
<SizeOfDataNodes> 12 </SizeOfDataNodes>
<Allocation Type>FixedSizeAllocator</Allocation Type>
<Chunk Id> 5 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > UndoLogTableId </ChunkName>
<TotalDataNodes> 0 </TotalDataNodes>
<SizeOfDataNodes> 0 </SizeOfDataNodes>
<Allocation Type>VariableSizeAllocator</Allocation Type>
<Chunk Id> 10 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > DatabaseTableId </ChunkName>
<TotalDataNodes> 0 </TotalDataNodes>
<SizeOfDataNodes> 148 </SizeOfDataNodes>
<Allocation Type> FixedSizeAllocator </Allocation Type>
<Chunk Id> 11 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > UserTableId </ChunkName>
<TotalDataNodes> 1 </TotalDataNodes>
<SizeOfDataNodes> 260 </SizeOfDataNodes>
<Allocation Type> FixedSizeAllocator </Allocation Type>
<Chunk Id> 12 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > TableTableId </ChunkName>
<TotalDataNodes> 0 </TotalDataNodes>
<SizeOfDataNodes> 152 </SizeOfDataNodes>
<Allocation Type> FixedSizeAllocator </Allocation Type>
<Chunk Id> 13 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > FieldTableId </ChunkName>
<TotalDataNodes> 0 </TotalDataNodes>
<SizeOfDataNodes> 196 </SizeOfDataNodes>
<Allocation Type> FixedSizeAllocator </Allocation Type>
<Chunk Id> 14 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > AccessTableId </ChunkName>
<TotalDataNodes> 0 </TotalDataNodes>
<SizeOfDataNodes> 260 </SizeOfDataNodes>
<Allocation Type> FixedSizeAllocator </Allocation Type>
<Chunk Id> 15 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > IndexTableId </ChunkName>
<TotalDataNodes> 0 </TotalDataNodes>
<SizeOfDataNodes> 164 </SizeOfDataNodes>
<Allocation Type> FixedSizeAllocator </Allocation Type>
<Chunk Id> 16 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > IndexFieldTableId </ChunkName>
<TotalDataNodes> 0 </TotalDataNodes>
<SizeOfDataNodes> 16 </SizeOfDataNodes>
<Allocation Type> FixedSizeAllocator </Allocation Type>
</System Chunk >
<User Chunk >
</User Chunk >
</Chunk information>

Lets create one table with primary key and one unique index. Lets see what happened in the chunk.

$ csql
CSQL> create table t2(f1 int, f2 char(20),primary key(f1));
Statement Executed
CSQL> quit;

You will see some changes in system chunk and new chunk is created in user chunk as follow.

$ catalog -u root -p manager -D chunk
<Chunk information>
<System Chunk >
<Chunk Id> 0 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > UserChunkTableId </ChunkName>
<TotalDataNodes> 3 </TotalDataNodes>
<SizeOfDataNodes> 112 </SizeOfDataNodes>

……………………
……………………
……………………

</System Chunk >
<User Chunk >
<Chunk Id> 101 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > t2 </ChunkName>
<TotalDataNodes> 0 </TotalDataNodes>
<SizeOfDataNodes> 36 </SizeOfDataNodes>
<Allocation Type> FixedSizeAllocator </Allocation Type>
<Chunk Id> 102 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > t2_idx1_Primary </ChunkName>
<TotalDataNodes> 1 </TotalDataNodes>
<SizeOfDataNodes> 28256 </SizeOfDataNodes>
<Allocation Type> FixedSizeAllocator </Allocation Type>
<Chunk Id> 103 </Chunk Id>
<TotalPages> 1 </TotalPages>
<ChunkName > t2_idx1_Primary </ChunkName>
<TotalDataNodes> 0 </TotalDataNodes>
<SizeOfDataNodes> 16 </SizeOfDataNodes>
<Allocation Type> FixedSizeAllocator </Allocation Type>
</User Chunk >
</Chunk information>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: