notas:bases_de_datos:tsm_sql_hints
Diferencias
Muestra las diferencias entre dos versiones de la página.
Ambos lados, revisión anteriorRevisión previa | |||
notas:bases_de_datos:tsm_sql_hints [2012/04/10 13:06] – [How many tapes can I reclaim by changing the reclamation threshold?] cayu | notas:bases_de_datos:tsm_sql_hints [Fecha desconocida] (actual) – borrado - editor externo (Fecha desconocida) 127.0.0.1 | ||
---|---|---|---|
Línea 1: | Línea 1: | ||
- | ====== TSM SQL Hints ====== | ||
- | ===== SQL tips and syntax ===== | ||
- | The basic SQL syntax is\\ SELECT ALL | DISTINCT |columnname(, | ||
- | |||
- | The standard SQL operator, ' | ||
- | |||
- | The first thing you need, is to know what TSM tables are available to query. These are described in the next section. After that, the best way to learn SQL is to try it out. It's a read only query language! The examples below might help explain what the syntax means, otherwise, try the following sites.\\ \\ [[http:// | ||
- | |||
- | TSM SQL query command does not support the full SQL language syntax. The following operations will not work: | ||
- | |||
- | * UNION | ||
- | * INTERSECT | ||
- | * EXCEPT | ||
- | * subqueries that return multiple values | ||
- | * You cannot use a semicolon as a command terminator | ||
- | |||
- | You can do maths in SQL statements, for example | ||
- | |||
- | < | ||
- | SELECT AVG (total_mb/ | ||
- | AS " | ||
- | FROM auditocc | ||
- | </ | ||
- | |||
- | You can select several columns, or items from a table by separating them with commas, like | ||
- | |||
- | < | ||
- | | ||
- | </ | ||
- | |||
- | or you can join items together either of the two examples below will work. | ||
- | |||
- | < | ||
- | | ||
- | | ||
- | </ | ||
- | |||
- | You can combine two tables together and select columns from each like this | ||
- | |||
- | < | ||
- | SELECT nodes.domain_name, | ||
- | </ | ||
- | |||
- | or you can simplify the expression by giving the tables an alias | ||
- | |||
- | < | ||
- | SELECT nn.domain_name, | ||
- | </ | ||
- | |||
- | Note that the aliases have two characters. For some reason TSM does not always like a single character. It seems to really object if you abbreviate ' | ||
- | |||
- | If you invoke SQL from a script then it may ask for confirmation to proceed, for example if it may check that you are happy to process a lot of output. You can suppress the confirmation messages with the option -noconfirm | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== What are the TSM tables? ===== | ||
- | |||
- | To find out what TSM tables exist and what they contain, run the following queries\\ select * from syscat.tables\\ select * from syscat.columns\\ select * from syscat.enumtypes | ||
- | |||
- | The **SUMMARY** table contains a lot of useful entries for general statistics. A couple of fields are SUMMARY.ACTIVITY AND SUMMARY.SUCCESFUL. The activity field currently contains; 'TAPE MOUNT', | ||
- | |||
- | The DATE field in the **EVENTS** table does not support expressions like ' | ||
- | |||
- | < | ||
- | scheduled_start > ' | ||
- | </ | ||
- | |||
- | This is no good if you want to schedule a query, but in that case you can simply use a very early fixed timestamp date, for example | ||
- | |||
- | < | ||
- | | ||
- | from events - | ||
- | where scheduled_start >= ' | ||
- | | ||
- | </ | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== SQL output formatting ===== | ||
- | |||
- | If you enter SQL queries from the command line in the browser, you get the results in tabular format. It is possible to execute SQL from a host command line, and then you can pipe the command to a file and get the results in comma delimited format for importing to an Excel spreadsheet or similar.\\ The command is | ||
- | |||
- | < | ||
- | dsmadmc -id=adminid -password=adminpassword -commadelimited | ||
- | ' | ||
- | </ | ||
- | |||
- | Command output direction can be a bit complicated as it works differently for different operating systems. In general the '>' | ||
- | |||
- | So for example\\ select * from summary > summary.txt will direct lots of output text to a summary file, while\\ select * from summary where date> | ||
- | |||
- | If you run these commands in batch, the operating system might try to interpret the redirection command as greater than even if it is surrounded by spaces. In UNIX and LINUX you can put a slash before the command /> but the easier way is to put the whole command in quotes. " | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== Using Indexed Columns to speed up queries ===== | ||
- | |||
- | TSM SQL queries can run for a long time, and use up a lot of resource. This is usually because you are searching the whole database to get the data you want. You can reduce the amount of database searching by selecting specific data from an indexed column using a WHERE statement. To find out which columns are indexed, use the query | ||
- | |||
- | < | ||
- | | ||
- | </ | ||
- | |||
- | A partial result looks like | ||
- | |||
- | < | ||
- | | ||
- | TABNAME: MEDIA | ||
- | COLNAME: LRD | ||
- | COLNO: 9 | ||
- | | ||
- | INDEX_ORDER: | ||
- | | ||
- | | ||
- | SCALE: 0 | ||
- | NULLS: TRUE | ||
- | REMARKS: Last Reference Date | ||
- | |||
- | TABSCHEMA: ADSM | ||
- | TABNAME: MGMTCLASSES | ||
- | COLNAME: DOMAIN_NAME | ||
- | COLNO: 1 | ||
- | | ||
- | INDEX_ORDER: | ||
- | | ||
- | | ||
- | SCALE: 0 | ||
- | NULLS: FALSE | ||
- | REMARKS: Policy Domain Name | ||
- | </ | ||
- | |||
- | This tells you that the DOMAIN_NAME column in the MGMTCLASSES table is indexed, but the LRD column in the MEDIA table is not. So if you run a query like | ||
- | |||
- | < | ||
- | | ||
- | WHERE DOMAIN_NAME = ' | ||
- | </ | ||
- | |||
- | Then you can expect your query to be quite fast. | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== TSM SQL date formats ===== | ||
- | |||
- | The timestamp format is: | ||
- | |||
- | < | ||
- | ' | ||
- | |||
- | yyyy = year | ||
- | mm = month | ||
- | dd = day | ||
- | hh = hours | ||
- | mm = minutes | ||
- | ss = seconds | ||
- | nnnnnn = fraction of a second | ||
- | </ | ||
- | |||
- | ' | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== How to combine two TSM tables in one query ===== | ||
- | |||
- | It is possible to combine two TSM tables in one query, but be aware that the TSM database is not really relational, so table joins take ages and use a lot of resource. It may be faster to do two queries, copy out the results then combine the data with an external program.\\ The key to database joins is | ||
- | |||
- | * Both tables must have one column that contains the same data | ||
- | * You must give each table an alias name for reference purposes using 'table name alias' in the FROM statement | ||
- | * You select the common column in both tables using aliasname.table name | ||
- | * You then join the data with WHERE alias1.column2=alias2.column2 | ||
- | |||
- | For example | ||
- | |||
- | < | ||
- | | ||
- | vm.volume_name, | ||
- | FROM volumes vm, | ||
- | WHERE node_name=' | ||
- | AND vm.volume_name=vu.volume_name | ||
- | </ | ||
- | |||
- | This combines the percent volume utilised column from the volumes table with the nodename column in the volumeusage table, combined with the volume column from each. Be aware that this is a really CPU intensive query. | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== How many client nodes are registered by domain ===== | ||
- | |||
- | < | ||
- | select domain_name, | ||
- | from domains | ||
- | </ | ||
- | |||
- | Result - | ||
- | |||
- | < | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | </ | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== How many client nodes are registered by platform? ===== | ||
- | |||
- | < | ||
- | | ||
- | from nodes - | ||
- | group by platform_name | ||
- | </ | ||
- | |||
- | Result - | ||
- | |||
- | < | ||
- | | ||
- | | ||
- | 16 | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | SUN SOLARIS | ||
- | TDP Domino | ||
- | TDP Domino NT 2 | ||
- | TDP Oracle AIX 6 | ||
- | | ||
- | </ | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== How do I find locked nodes? ===== | ||
- | |||
- | < | ||
- | | ||
- | where locked=' | ||
- | </ | ||
- | |||
- | Result - | ||
- | |||
- | < | ||
- | | ||
- | | ||
- | | ||
- | </ | ||
- | |||
- | The following search should find them | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== Query all tapes for a node ===== | ||
- | |||
- | How do I find all the tape volsers associated with a specific node? | ||
- | |||
- | < | ||
- | | ||
- | select distinct node_name, | ||
- | from volumeusage - | ||
- | where node_name=' | ||
- | </ | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== What tapes were used today? ===== | ||
- | |||
- | How do you find out what tapes were used on a specific day. | ||
- | |||
- | < | ||
- | select volume_name, | ||
- | from volumes - | ||
- | order by last_write_date | ||
- | </ | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== Library inventory ===== | ||
- | |||
- | How can I display an inventory of my library in order of slot number | ||
- | |||
- | < | ||
- | select home_element, | ||
- | from libvolumes - | ||
- | order by home_element | ||
- | </ | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== Which volume has my file ===== | ||
- | |||
- | How can I find out which volume contains a specific file? | ||
- | |||
- | < | ||
- | select volume_name, | ||
- | from contents - | ||
- | where node_name=' | ||
- | and filespace_name=' | ||
- | and file_name=' | ||
- | </ | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== List all volumes that are not in READWRITE status ===== | ||
- | |||
- | select VOLUME_NAME, | ||
- | |||
- | < | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | </ | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== How many scratch tapes are there? ===== | ||
- | |||
- | How do I tell how many scratch tapes we have? | ||
- | |||
- | < | ||
- | select count(*) as Scratch_count - | ||
- | from libvolumes - | ||
- | where status=' | ||
- | </ | ||
- | |||
- | If you have more than 1 library, you can find all your scratch tapes using the query | ||
- | |||
- | < | ||
- | select LIBRARY_NAME, | ||
- | upper(status)=' | ||
- | </ | ||
- | |||
- | Thanks to Sven Neirynck of Compu-mark for that tip | ||
- | |||
- | TSM has a MAXSCRATCH parameter which is set independently for each storage pool. This defines the maximum number of tapes that each tape pool can contain. The following query will display how close each pool is to its limit. | ||
- | |||
- | < | ||
- | | ||
- | | ||
- | | ||
- | FROM STGPOOLS, | ||
- | WHERE (VOLUMES.STGPOOL_NAME = STGPOOLS.STGPOOL_NAME) - | ||
- | AND ((STGPOOLS.DEVCLASS=" | ||
- | GROUP BY STGPOOLS.STGPOOL_NAME, | ||
- | </ | ||
- | |||
- | Typical output looks like | ||
- | |||
- | < | ||
- | STGPOOL_NAME | ||
- | ------------------ | ||
- | ARCHTAPEPOOL | ||
- | CARTPOOL | ||
- | VIRTCARTPOOL | ||
- | </ | ||
- | |||
- | [[#top|back to top]] | ||
- | |||
- | ---- | ||
- | |||
- | ===== How many tapes can I reclaim by changing the reclamation threshold? ===== | ||
- | |||
- | < | ||
- | | ||
- | where stgpool_name=' | ||
- | and upper(status)=' | ||
- | and pct_utilized | ||
- | </ | ||
- | |||
- | ====== Fuente ====== | ||
- | http:// |