How to query the index create time in sybase IQ 12.7

[Former Member]

My sybase IQ version is 12.7, Now I want to know when the index be created?

In sybase IQ 15.2, I can query SYSOBJECT join with SYSIDX to know it,  But in sybase IQ 12.7, this two  views does not exist.

How can I to know it ?

Thanks!

[Former Member · Accepted solution]

In Sybase IQ 1270 , we can only know when the user table was created or modified ( alter table or create index , etc ) !!

There is no way to get when the index was created or modified !!

Please take reference on following example !!

Hope this will help !!

Regards,

Robert

For example:

A. create table tt5 and check create_time/update_time :

(DBA)> create table tt5(c1 int, c2 char(10));

Execution time: 0.176 seconds

(DBA)> select object_id('tt5');

Execution time: 0.443 seconds

object_id('tt5')

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

100570          

(First 1 rows)

(DBA)> select * from sysiqtable where table_id = 570;

Execution time: 0.698 seconds

table_id block_map                                                                                                                          block_map_size vdo                                                                                                                                                                                                                                                                                                                                                                                                vdoid_size info_location                      info_recid_size info_location_size commit_txn_id txn_id join_id create_time                update_time               

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

570      0x43e400000000000029e81f00000000000400000000000000edc10300000000000000000008007d01010000000c38000000000000010000000000000000000000 64             0x030000000000000001000000040010000400000000000000060000000000000000000000000000000000000000000000000000000000000001000501000000000100000000000000030000000000010001000500000000000000000000000000000000000000000001000501000000000100000000000000020000000000010004000000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 192        0x01000000100004000100000000000000 16              184                246254        246253 0       2012-04-03 10:58:23.376160 2012-04-03 10:58:23.501268

(First 1 rows)

B. alter table and confirm 'update_time' changed :

=======  alter table will update 'update_time' column ' =======

(DBA)> alter table tt5 add c3 char(10)

Execution time: 0.037 seconds

(DBA)> select * from sysiqtable where table_id = 570;

Execution time: 0.126 seconds

table_id block_map                                                                                                                          block_map_size vdo                                                                                                                                                                                                                                                                                                                                                                                                vdoid_size info_location                      info_recid_size info_location_size commit_txn_id txn_id join_id create_time                update_time               

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

570      0x43e400000000000017031000000000000400000000000000f1c10300000000000000000008007d01010000000c38000000000000010000000000000000000000 64             0x030000000000000001000000040010000400000000000000070000000000000000000000000000000000000000000000000000000000000001000501000000000100000000000000030000000000010001000500000000000000000000000000000000000000000001000501000000000100000000000000020000000000010004000000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 192        0x01000000100004000100000000000000 16              184                246258        246257 0       2012-04-03 10:58:23.376160 2012-04-03 10:59:37.082782

(First 1 rows)

C. create index and confirm update_time changed :

======  create index will update 'update_time' column ' ========

(DBA)> create HG index idx_HG_tt5_c1 on tt5(c1);

Execution time: 0.027 seconds

(DBA)> select * from sysiqtable where table_id = 570;

Execution time: 0.129 seconds

table_id block_map                                                                                                                          block_map_size vdo                                                                                                                                                                                                                                                                                                                                                                                                vdoid_size info_location                      info_recid_size info_location_size commit_txn_id txn_id join_id create_time                update_time               

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

570      0x43e400000000000029e81f00000000000400000000000000f6c10300000000000000000008007d01010000000c38000000000000010000000000000000000000 64             0x030000000000000001000000040010000400000000000000080000000000000000000000000000000000000000000000000000000000000001000501000000000100000000000000030000000000010001000500000000000000000000000000000000000000000001000501000000000100000000000000020000000000010004000000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 192        0x01000000100004000100000000000000 16              184                246263        246262 0       2012-04-03 10:58:23.376160 2012-04-03 11:02:20.431926

(First 1 rows)

(DBA)>

D. executed query to list user tables information :

Syntax:

select  t.table_name, it.table_id, it.create_time, it.update_time

from sysiqtable it, systable t

where it.table_id = t.table_id

and (it.table_id > 100 and it.table_id < 1000);

[Former Member · Accepted solution]

Hi  Robert,

Thanks for your suggestion!

Regards,

Rodman