TSM 6.1 table list

[yquirion]

Dear all,

I would like to know where I can get a list including COLNAME, TYPENAME, LENGHT and SCALE field of all TSM 6.x tables.

There is something strange in TSM 6.1: If I'm doing the following command:
select COLNAME, TYPENAME, LENGTH, SCALE from syscat.columns where tabname = 'SUMMARY'

I'm getting an error, like if the table doesn't exists:
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.

But this table exists; if I'm doing a SELECT of that table, I'm getting data:

select * from SUMMARY

START_TIME: 2010-09-05 20:37:47.000000
END_TIME: 2010-09-05 20:37:48.000000
ACTIVITY: RECLAMATION
NUMBER: 811
ENTITY: DEDUPE-J1A (/tsm/pool02/postes/J1A_D-P_vol0004)
COMMMETH:
ADDRESS:
SCHEDULE_NAME:
EXAMINED: 0
AFFECTED: 0
FAILED: 0
BYTES: 0
IDLE: 0
MEDIAW: 0
PROCESSES: 1
SUCCESSFUL: NO
VOLUME_NAME:
DRIVE_NAME:
LIBRARY_NAME:
LAST_USE:
COMM_WAIT:
NUM_OFFSITE_VOLS: 0
...

So the question is: Why I cannot get the information on that table when I ask the syscat.columns table? This is not the only table I have problem with, but I don't remember the names.

At the begenning I make this command to get all SQL tables and fields:

select COLNAME, TYPENAME, LENGTH, SCALE from syscat.columns

and I thought I had all of them, but some are missing. So I would like to know if there is a place I can download a list of SQL tables into TSM 6.x in the form of:

select COLNAME, TYPENAME, LENGTH, SCALE from syscat.columns where tabname = 'NODES'

COLNAME TYPENAME LENGTH SCALE
------------------ ------------------ ----------- ------
NODE_NAME VARCHAR 64 0
PLATFORM_NAME VARCHAR 16 0
DOMAIN_NAME VARCHAR 30 0
PWSET_TIME TIMESTAMP 0 0
INVALID_PW_COUNT SMALLINT 0 0
CONTACT VARCHAR 255 0
...
Thank you all for your help.

Regards,
Yanick

 

[DanGiles]

"select distinct tabname from columns" shows there is no SUMMARY table information in these tables (not in syscat.tables either). These tables, however, are purely informational and are not actually used by TSM/DB2. It looks like this information was dropped during the port to DB2. (At least, that's the implication here!)

You'll have to call IBM support and bitch; if enough people do, they's put it back pronto. (Check the release notes -- it may already be back in the latest releases.)

 

[TonyB]

Query it from the db2 layer:

@PTSMP3D1 / @TSMDB1 > select char(COLNAME,24),char(TYPENAME,24),LENGTH,SCALE from syscat.columns where tabname='SUMMARY' and tabschema='TSMDB1'

1                        2                        LENGTH      SCALE 
------------------------ ------------------------ ----------- ------
ACTIVITY                 VARCHAR                          256      0
ADDRESS                  VARCHAR                          256      0
AFFECTED                 BIGINT                             8      0
BYTES                    BIGINT                             8      0
COMMMETH                 VARCHAR                           17      0
COMM_WAIT                INTEGER                            4      0
DRIVE_NAME               VARCHAR                           61      0
END_TIME                 TIMESTAMP                         10      0
ENTITY                   VARCHAR                           65      0
EXAMINED                 BIGINT                             8      0
FAILED                   BIGINT                             8      0
IDLE                     BIGINT                             8      0
LAST_USE                 VARCHAR                          256      0
LIBRARY_NAME             VARCHAR                           61      0
MEDIAW                   BIGINT                             8      0
NUMBER                   INTEGER                            4      0
NUM_OFFSITE_VOLS         INTEGER                            4      0
PROCESSES                INTEGER                            4      0
SCHEDULE_NAME            VARCHAR                           31      0
START_TIME               TIMESTAMP                         10      0
SUCCESSFUL               VARCHAR                            9      0
VOLUME_NAME              VARCHAR                         4096      0

  22 record(s) selected.

hth,

Tony